从一个Oracle关联数组复制值到另一个Oracle关联数组

Question

我有两个pl/sql关联数组array1和array2

TYPE T_ARRAY1_REC IS RECORD(COL1 NUMBER,COL2 NUMBER,COL3 NUMBER,COL4 varchar2(1));    
TYPE T_ARRAY1 IS TABLE OF T_ARRAY1_REC INDEX BY BINARY_INTEGER;
array1 T_ARRAY1;
TYPE T_ARRAY2_REC IS RECORD(COL1 NUMBER,COL2 NUMBER,COL3 NUMBER);    
TYPE T_ARRAY2 IS TABLE OF T_ARRAY2_REC INDEX BY BINARY_INTEGER;
array2 T_ARRAY2;

如何将array2的col3的值复制到array1的col3中?Col1和col2是两个数组

中的公共列。数组1的数据

col1 col2 col3 col4
1     4         P
2     5         T
3     6         P

数据数组2

col1 col2 col3 
1     4   56
2     5   67
3     6   89

复制完array1中的数据后应该是这样的

col1 col2 col3 col4 
1     4   56   P
2     5   67   T
3     6   89   P

Answer 1

如果您假设关联数组可能不是相同的顺序，并且可以是稀疏的(因此您不能使用FOR循环)，那么您可以在关联数组上使用WHILE循环和FIRST和NEXT循环遍历数组:

DECLARE
TYPE T_ARRAY1_REC IS RECORD(COL1 NUMBER,COL2 NUMBER,COL3 NUMBER,COL4 varchar2(1));    
TYPE T_ARRAY1 IS TABLE OF T_ARRAY1_REC INDEX BY BINARY_INTEGER;
array1 T_ARRAY1;
TYPE T_ARRAY2_REC IS RECORD(COL1 NUMBER,COL2 NUMBER,COL3 NUMBER);    
TYPE T_ARRAY2 IS TABLE OF T_ARRAY2_REC INDEX BY BINARY_INTEGER;
array2 T_ARRAY2;
i PLS_INTEGER;
j PLS_INTEGER;

FUNCTION findArray1Index(i_col1 NUMBER, i_col2 NUMBER)
RETURN PLS_INTEGER
IS
i PLS_INTEGER := array1.FIRST;
BEGIN
WHILE i IS NOT NULL LOOP
IF array1(i).col1 = i_col1 AND array1(i).col2 = i_col2 THEN
RETURN i;
END IF;
i := array1.NEXT(i);
END LOOP;
RETURN NULL;
END;
BEGIN
array1(1).col1 := 1;
array1(1).col2 := 4;
array1(1).col4 := 'P';

array1(2).col1 := 2;
array1(2).col2 := 5;
array1(2).col4 := 'T';
array1(7).col1 := 3;
array1(7).col2 := 6;
array1(7).col4 := 'p';

array2(1).col1 := 1;
array2(1).col2 := 4;
array2(1).col3 := 56;
array2(7).col1 := 2;
array2(7).col2 := 5;
array2(7).col3 := 67;
array2(4).col1 := 3;
array2(4).col2 := 6;
array2(4).col3 := 89;
i := array2.FIRST;
WHILE i IS NOT NULL LOOP
j := findArray1Index(array2(i).col1, array2(i).col2);
IF j IS NOT NULL THEN
array1(j).col3 := array2(i).col3;
END IF;
i := array2.NEXT(i);
END LOOP;
i := array1.FIRST;
WHILE i IS NOT NULL LOOP
DBMS_OUTPUT.PUT_LINE(
array1(i).col1 || ', ' ||
array1(i).col2 || ', ' ||
array1(i).col3 || ', ' ||
array1(i).col4
);
i := array1.NEXT(i);
END LOOP;
END;
/

db<此处小提琴>