在 PL/SQL 中创建或模拟二维数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9155742/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Creating or simulating two dimensional arrays in PL/SQL
提问by Jemru
Can you please help me how can I create two dimensional array in PL/SQL for Stored Procedure? The columns are dynamic so it can grow and change in types also. Any help is appreciated. Thank you in advance!
你能帮我如何在 PL/SQL 中为存储过程创建二维数组吗？列是动态的，因此它也可以增长和改变类型。任何帮助表示赞赏。先感谢您！
I have the following code:
我有以下代码：
Type CAR_TABLE_ARRAY is varray(2) of varchar2(255);
TYPE CAR_TABLE_TYPE IS TABLE OF CAR_TABLE_ARRAY;
CAR_TABLE CAR_TABLE_TYPE;
CAR_TABLE := CAR_TABLE_TYPE();
CAR_TABLE.EXTEND(10);
CAR_TABLE(1)(1) := 'DODGE';
CAR_TABLE(2)(1) := 'FORD';
CAR_TABLE(3)(1) := 'MUSTANG';
CAR_TABLE(4)(1) := 'EDSEL';
CAR_TABLE(5)(1) := 'STUDEBAKER';
DBMS_OUTPUT.put_line( '1 ' || CAR_TABLE(1)(1) );
DBMS_OUTPUT.put_line( '2 ' || CAR_TABLE(2)(1) );
DBMS_OUTPUT.put_line( '3 ' || CAR_TABLE(3)(1) );
DBMS_OUTPUT.put_line( '4 ' || CAR_TABLE(4)(1) );
DBMS_OUTPUT.put_line( '5 ' || CAR_TABLE(5)(1) );
When I run I get the following error:
当我运行时，出现以下错误：
ORA-06531: Reference to uninitialized collection
ORA-06531：对未初始化集合的引用
回答by tbone
Here's an example of using an multidimensional array in pl/sql. Here I use an array containing an array.
这是在 pl/sql 中使用多维数组的示例。这里我使用了一个包含数组的数组。
declare
type t_features is table of varchar(100) index by pls_integer;
type t_car_rec is record
(
make varchar2(50),
model varchar2(50),
features t_features
);
type t_car_tab is table of t_car_rec index by pls_integer;
car_tab t_car_tab;
procedure show_detail is
car_idx pls_integer;
features_idx pls_integer;
begin
car_idx := car_tab.first;
loop
exit when car_idx is null;
dbms_output.put_line('Details for ' || car_tab(car_idx).make || ' ' || car_tab(car_idx).model);
features_idx := car_tab(car_idx).features.first;
loop
exit when features_idx is null;
dbms_output.put_line(' =>' || car_tab(car_idx).features(features_idx));
features_idx := car_tab(car_idx).features.next(features_idx);
end loop;
car_idx := car_tab.next(car_idx);
end loop;
end;
begin
-- using sequential index values
car_tab(1).make := 'Ferrari';
car_tab(1).model := 'Testarossa';
car_tab(1).features(1) := 'Fast';
car_tab(1).features(2) := 'Looks cool';
car_tab(1).features(3) := 'Expensive';
-- using random index values (sparse)
car_tab(2).make := 'Acura';
car_tab(2).model := 'TSX';
car_tab(2).features(14) := 'Small';
car_tab(2).features(200) := 'Good MPG';
car_tab(2).features(36) := 'Inexpensive';
show_detail;
end;
Output would be:
输出将是：
Details for Ferrari Testarossa
=>Fast
=>Looks cool
=>Expensive
Details for Acura TSX
=>Small
=>Inexpensive
=>Good MPG
Hope that helps
希望有帮助
回答by Allan
VARRAY and nested table user-defined datatypes always have to be initialized using a constructor. You're doing that correctly for the nested table, but not for the VARRAYs that it contains. The simplest fix is to call the constructor in the assignment lines:
VARRAY 和嵌套表用户定义的数据类型始终必须使用构造函数进行初始化。您对嵌套表正确执行此操作，但对它包含的 VARRAY 却没有正确执行此操作。最简单的解决方法是在赋值行中调用构造函数：
declare
Type CAR_TABLE_ARRAY is varray(2) of varchar2(255);
TYPE CAR_TABLE_TYPE IS TABLE OF CAR_TABLE_ARRAY;
CAR_TABLE CAR_TABLE_TYPE;
begin
CAR_TABLE := CAR_TABLE_TYPE();
CAR_TABLE.EXTEND(10);
CAR_TABLE(1) := CAR_TABLE_ARRAY('DODGE',null);
CAR_TABLE(2) := CAR_TABLE_ARRAY('FORD',null);
CAR_TABLE(3) := CAR_TABLE_ARRAY('MUSTANG',null);
CAR_TABLE(4) := CAR_TABLE_ARRAY('EDSEL',null);
CAR_TABLE(5) := CAR_TABLE_ARRAY('STUDEBAKER',null);
DBMS_OUTPUT.put_line( '1 ' || CAR_TABLE(1)(1) );
DBMS_OUTPUT.put_line( '2 ' || CAR_TABLE(2)(1) );
DBMS_OUTPUT.put_line( '3 ' || CAR_TABLE(3)(1) );
DBMS_OUTPUT.put_line( '4 ' || CAR_TABLE(4)(1) );
DBMS_OUTPUT.put_line( '5 ' || CAR_TABLE(5)(1) );
end;
回答by A.B.Cade
That's because you're referencing the inner arrays which are not initialized.
Either add something like:
那是因为您正在引用未初始化的内部数组。
要么添加如下内容：
CAR_TABLE(1) := CAR_TABLE_ARRAY();
CAR_TABLE(1).EXTEND(1);
CAR_TABLE(2) := CAR_TABLE_ARRAY();
CAR_TABLE(2).EXTEND(1);
...
Or make the inner arrays (CAR_TABLE_ARRAY) as asociative arrays:
或者将内部数组 (CAR_TABLE_ARRAY) 设为关联数组：
Type CAR_TABLE_ARRAY is TABLE of varchar2(255) index by binary_integer;
