oracle 如何在 PL/SQL 中手动初始化一组 RECORD？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3707140/
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
How to manually initialize a collection of RECORDs in PL/SQL?
提问by Kirill Leontev
guys. Here's a simple sample two-dimensional array in PL/SQL, which is working perfectly.
伙计们。这是 PL/SQL 中的一个简单示例二维数组，它运行良好。
declare
type a is table of number;
type b is table of a;
arr b := b(a(1, 2), a(3, 4));
begin
for i in arr.first .. arr.last loop
for j in arr(i).first .. arr(i).last loop
dbms_output.put_line(arr(i) (j));
end loop;
end loop;
end;
What I need to do, is to create something similar for a table of RECORDS. Like this:
我需要做的是为一个RECORDS. 像这样：
type a is record(a1 number, a2 number);
type b is table of a;
The question is, can I manually initialize this kind of array, or it is supposed to be filled by bulk collectsor similar? The same syntax as above doesn't seem to work, and I wasn't able to find any initialization sample in manuals.
问题是，我可以手动初始化这种数组，还是应该填充bulk collects或类似的？与上述相同的语法似乎不起作用，而且我在手册中找不到任何初始化示例。
回答by Tony Andrews
There is no "constructor" syntax for RECORDs, so you have to populate them like this:
RECORD 没有“构造函数”语法，因此您必须像这样填充它们：
declare
type a is record(a1 number, a2 number);
type b is table of a;
arr b := b();
begin
arr.extend(2);
arr(1).a1 := 1;
arr(1).a2 := 2;
arr(2).a1 := 3;
arr(2).a2 := 4;
end;
回答by Shallow
This works without objects, but you have to declare a constructor function for type 'a' values.
这在没有对象的情况下工作，但您必须为类型 'a' 值声明一个构造函数。
declare
type a is record(a1 number, a2 number);
type b is table of a;
arr b;
--Constructor for type a
function a_(a1 number, a2 number) return a is
r_a a;
begin
r_a.a1 := a1;
r_a.a2 := a2;
return(r_a);
end;
begin
arr := b(a_(1, 2), a_(3, 4), a_(5, 6), a_(7, 8));
for i in arr.first .. arr.last loop
dbms_output.put_line(arr(i).a1||', '||arr(i).a2);
end loop;
end;
回答by 0xdb
Since release 18c Qualified Expressionsprovides an alternative way to define the values of complex data types. Quote:
自 18c 版以来，限定表达式提供了一种定义复杂数据类型值的替代方法。引用：
Starting with Oracle Database Release 18c, any PL/SQL value can be provided by an expression (for example for a record or for an associative array) like a constructor provides an abstract datatype value. In PL/SQL, we use the terms "qualified expression" and "aggregate" rather than the SQL term "type constructor", but the functionality is the same.
从 Oracle 数据库第 18c 版开始，任何 PL/SQL 值都可以由表达式（例如对于记录或关联数组）提供，就像构造函数提供抽象数据类型值一样。在 PL/SQL 中，我们使用术语“限定表达式”和“聚合”而不是 SQL 术语“类型构造函数”，但功能是相同的。
Here's an working example:
这是一个工作示例：
declare
type a is record (a1 number, a2 number);
type b is table of a index by varchar2 (16);
arr b := b ('key1' => a (1, 2), 'key2' => a (3, 4));
begin
declare key varchar2 (16) := arr.first; begin
<<foreach>> loop
dbms_output.put_line (arr(key).a1||','||arr (key).a2);
key := arr.next (key);
exit foreach when key is null;
end loop; end;
end;
/
PL/SQL procedure successfully completed.
1,2
3,4
