将循环中的值添加到记录集合中

Question

我来自MS SQL Server背景，所以如果我完全错了，请告诉我。

我有一个列表表，每个列表都有任务。这些任务相互依赖。我们想要复制一个列表及其任务和那些任务依赖关系。每个Task还有其他相关记录。

以前的Oracle开发人员创建了一个过程来执行复制。与其他内容一起，还有一个来自Cursor的循环，它可以复制列表中的所有任务。

它在光标中循环，创建新任务以及基于旧任务与之相关的任何任务。我最近介绍了依赖关系表。

一行可以是：Id、SuccessorId、PrecisionId、DependencyType。

现在，如果我在SQL Server中，我将创建一个OldId、NewId的表变量，并在旧任务的基础上插入每个新任务，我还将在表变量中插入一行。然后，我可以使用表变量INSERT SELECT旧的依赖项来获得新的Id。

在Oracle中，我在包级别有这样的功能：

TYPE old_id_new_id_row       IS RECORD(old_id list_tasks.id%TYPE, new_id list_tasks.id%TYPE);
TYPE t_old_task_ids_new_task_ids IS TABLE OF old_id_new_id_row INDEX BY BINARY_INTEGER;

然后在过程声明中

lt_old_ids_to_new_ids  t_old_task_ids_new_task_ids;

我正在尝试向其中添加一条记录。我遍历了循环，但似乎没有办法做到这一点。我可以用BULK COLLECT INTO来表示依赖项的旧Id的值，但我不确定如何更新每行的new_id值。

这里的示例示例5-21是我所能找到的最接近的示例，但我很难看到一个一次添加一个记录的示例。所以它可能是：

SELECT lt.id AS old_id, -1 AS new_id
BULK COLLECT INTO lt_old_ids_to_new_ids
FROM list_tasks lt
WHERE lt.list_id = 96;

但是，当我插入新的Task记录并获取它们的Id时，我如何使新Id与旧Id匹配？

正如我所说，我对完全不同的、聪明的方法持开放态度，但我正在使用我现有的代码。这感觉应该比迄今为止证明的要简单得多。

编辑：很抱歉我的问题不清楚。这不是有问题的程序，因为它太复杂了，有太多不相关的东西在发生，这是我试图理解这些概念。希望能大致说明我想做什么。我已经根据目前给出的一个答案进行了更新。由于TABLE(mycoll(，我发誓它以前使用过TableType集合，所以目前不会编译它。

PROCEDURE test_temp_table(list_id       IN lists.id%TYPE
, io_cursor   IN OUT t_ref_cursor) IS

CURSOR lt (pc_list_id    lists.id%TYPE) IS
SELECT id
FROM list_tasks
WHERE list_id = pc_list_id
;        

TYPE old_id_new_id_row2 IS RECORD(old_id NUMBER, new_id NUMBER);
TYPE t_old_task_ids_new_task_ids2 IS TABLE OF old_id_new_id_row2 INDEX BY PLS_INTEGER;
tt_old_task_ids_new_task_ids t_old_task_ids_new_task_ids2;
c PLS_INTEGER;

BEGIN
DBMS_OUTPUT.ENABLE;

FOR ltr IN lt(list_id) LOOP  
tt_old_task_ids_new_task_ids(tt_old_task_ids_new_task_ids.count +1).old_id := ltr.id;
--This would be set the new Id for an inserted task. 
tt_old_task_ids_new_task_ids(tt_old_task_ids_new_task_ids.count +1).new_id := ltr.id + 100;

END LOOP;

FOR i IN tt_old_task_ids_new_task_ids.FIRST..tt_old_task_ids_new_task_ids.LAST
LOOP
--This works, could insert in loop but seems very inefficient and messy?
DBMS_OUTPUT.PUT_LINE(tt_old_task_ids_new_task_ids(i).old_id || ' ' || tt_old_task_ids_new_task_ids(i).new_id);      
END LOOP;

--This would be the Inset Select rather than the returned cursor. 
--INSERT INTO list_task_dependencies(
--predecessor_task_id
--, successor_task_id
--, dependency_type
--)    
OPEN io_cursor FOR
--Bits commented out that don't compile...
SELECT DISTINCT --predecessor.new_id
--, successor.new_id
--, 
ltd.dependency_type
FROM list_task_dependencies ltd
--TABLE(tt_old_task_ids_new_task_ids) ERRORS =
--Error: PLS-00382: expression is of wrong type
--Error: PL/SQL: ORA-22905: cannot access rows from a non-nested table item
--JOIN TABLE(tt_old_task_ids_new_task_ids) predecessor ON predecessor.old_id = ltd.predecessor_task_id
--JOIN TABLE(tt_old_task_ids_new_task_ids) successor ON successor.old_id = ltd.successor_task_id
;
END test_temp_table;  

为了帮助您了解我的来龙去脉，看看这里可以了解表变量在MS-sql服务器中的工作方式。本质上，我想要一个包含关系数据的变量，我可以像表一样查询这些数据。很明显，我知道全局临时表，但对我来说，这似乎是一个非常混乱的解决方案

Answer 1

我没有了解您试图构建的所有细节，但向关联记录数组添加元素的简化示例如下：

declare
type old_id_new_id_row is record(old_id number, new_id number);
type t_old_task_ids_new_task_ids is table of old_id_new_id_row index by pls_integer;
mycoll t_old_task_ids_new_task_ids;
c pls_integer;
begin
mycoll(1).old_id := 1;
mycoll(1).new_id := 2;
-- Or instead of specifying the array index, use the current count +1:
mycoll(mycoll.count +1).old_id := 3;
mycoll(mycoll.count).old_id := 4;
-- Or a variable:
c := mycoll.count +1;
mycoll(c).old_id := 5;
mycoll(c).new_id := 6;

-- From 18c:
mycoll(mycoll.count +1) := old_id_new_id_row(7,8);
end;

18c中的伪构造函数被称为合格表达式(它们不是严格意义上的构造函数，因为记录类型不是对象类型，也没有对象特性，但编译器也为您做了同样的工作(。

我个人更喜欢pls_integer而不是binary_integer，尽管它们是一样的东西，因为很长一段时间以来，binary_integer一直被弃用，而pls_integer是替代品。最终，Oracle将两者合并，因此您可以使用任何您喜欢的声音。

Answer 2

事实证明，您不能将TABLE TYPE集合中的SELECT插入到TABLE中。我不知道为什么，但你不能。也许我应该使用全局临时表，这对我来说不太合适，但在Oracle中可能更容易接受。

因此，我不得不一行接一行地循环插入表类型，这在Oracle中可能不会像在MS SQL Server中那样糟糕。

我不能使用在过程级别声明的表类型，必须在包级别完成。在我上面的第一次尝试中还有其他一些错误。

这不是proc，而是给你一个想法。我基本上花了一天以上的时间才到达这里，我认为这最多需要几个小时：(.

TYPE old_id_new_id_row       IS RECORD(old_id list_tasks.id%TYPE, new_id list_tasks.id%TYPE);
TYPE t_old_task_ids_new_task_ids IS TABLE OF old_id_new_id_row INDEX BY PLS_INTEGER;

PROCEDURE test_temp_table(list_id       IN lists.id%TYPE
, io_cursor   IN OUT t_ref_cursor) IS

CURSOR lt (pc_list_id    lists.id%TYPE) IS
SELECT id
FROM list_tasks
WHERE list_id = pc_list_id
;        

lt_old_task_ids_new_task_ids t_old_task_ids_new_task_ids;
BEGIN
DBMS_OUTPUT.ENABLE;

FOR ltr IN lt(list_id) LOOP  
tt_old_task_ids_new_task_ids(tt_old_task_ids_new_task_ids.count +1).old_id := ltr.id;
--This would be set the new Id for an inserted task. 
tt_old_task_ids_new_task_ids(tt_old_task_ids_new_task_ids.count).new_id := ltr.id + 100;

END LOOP;

FOR new_dependency IN 
(
SELECT DISTINCT predecessor.new_id AS predecessor_task_id
, successor.new_id AS successor_task_id
, ltd.dependency_type
FROM list_task_dependencies ltd
JOIN TABLE(lt_old_task_ids_new_task_ids) predecessor ON predecessor.old_id = ltd.predecessor_task_id
JOIN TABLE(lt_old_task_ids_new_task_ids) successor ON successor.old_id = ltd.successor_task_id
)
LOOP
INSERT INTO list_task_dependencies
(
successor_task_id
, predecessor_task_id
, dependency_type
)
VALUES
(
new_dependency.successor_task_id
, new_dependency.predecessor_task_id
, new_dependency.dependency_type
);
END LOOP;
END test_temp_table;