oracle 如何在 PLSQL 中填充内存表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12186991/
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 can I populate in memory tables, in PLSQL?
提问by mjsr
I don't know if it is the correct terminology but I call "in memory tables" to the objects created like this:
我不知道这是否是正确的术语，但我将“内存表”称为这样创建的对象：
create type InMemReg is object (field1 varchar2(10), field2 varchar2(20), field3 number);
create type InMemTab is table of InMemReg;
In this case my "in memory table" is "InMemTab". My question is how can I populate this kind of object, when i don't know previously the numbers of elements? I have seen in some places this type of initialization:
在这种情况下，我的“内存表”是“InMemTab”。我的问题是，当我以前不知道元素的数量时，如何填充这种对象？我在某些地方看到过这种类型的初始化：
declare
v_uno InMemReg := InMemReg('a','b',1999);
v_dos InMemReg := InMemReg('A','Z',2000);
t_tres InMemTab := InMemTab();
begin
t_tres := InMemTab(v_uno, v_dos);
In this situation I have explicitly 2 objects before initialize "t_tres", but in a dynamic scenario where I could have n numbers of elements I don't know how to populate it.
在这种情况下，我在初始化“t_tres”之前明确有 2 个对象，但是在我可以拥有 n 个元素的动态场景中，我不知道如何填充它。
In another OO language could be something like this:
在另一种面向对象语言中可能是这样的：
t_tres.add(OtherObject)
回答by Justin Cave
The type InMemTabis a nested table in Oracle parlance.
该类型InMemTab是 Oracle 用语中的嵌套表。
The equivalent to the addmethod would be to call the extendmethod and then to assign OtherObjectto the last position in the nested table.
与该add方法等效的是调用该extend方法，然后分配OtherObject给嵌套表中的最后一个位置。
SQL> ed
Wrote file afiedt.buf
1 declare
2 v_uno InMemReg := InMemReg('a','b',1999);
3 v_dos InMemReg := InMemReg('A','Z',2000);
4 t_tres InMemTab := InMemTab();
5 begin
6 t_tres.extend;
7 t_tres( t_tres.count ) := v_uno;
8 t_tres.extend;
9 t_tres( t_tres.count ) := v_dos;
10 dbms_output.put_line( 't_tres has ' || t_tres.count || ' elements.' );
11* end;
12 /
t_tres has 2 elements.
PL/SQL procedure successfully completed.
You can factor that out into an addprocedure as well
您也可以将其考虑到add程序中
SQL> ed
Wrote file afiedt.buf
1 declare
2 v_uno InMemReg := InMemReg('a','b',1999);
3 v_dos InMemReg := InMemReg('A','Z',2000);
4 t_tres InMemTab := InMemTab();
5 procedure add( p_nt IN OUT InMemTab,
6 p_elem IN InMemReg )
7 as
8 begin
9 p_nt.extend;
10 p_nt( p_nt.count ) := p_elem;
11 end;
12 begin
13 add( t_tres, v_uno );
14 add( t_tres, v_dos );
15 dbms_output.put_line( 't_tres has ' || t_tres.count || ' elements.' );
16* end;
17 /
t_tres has 2 elements.
PL/SQL procedure successfully completed.
回答by tbone
It is common to populate the collection from the data itself, meaning you are not explicitly adding sets of strings and numbers, you're pulling the data in from other tables. Because this is a common and natural thing to do with collections, Oracle made it easy via "BULK COLLECT INTO" clause in pl/sql. For example:
从数据本身填充集合是很常见的，这意味着您没有明确添加字符串和数字集，而是从其他表中提取数据。由于这是与集合有关的常见且自然的事情，因此 Oracle 通过 pl/sql 中的“BULK COLLECT INTO”子句使其变得容易。例如：
DECLARE
TYPE EmployeeSet IS TABLE OF employees%ROWTYPE;
underpaid EmployeeSet;
-- Holds set of rows from EMPLOYEES table.
CURSOR c1 IS SELECT first_name, last_name FROM employees;
TYPE NameSet IS TABLE OF c1%ROWTYPE;
some_names NameSet;
-- Holds set of partial rows from EMPLOYEES table.
BEGIN
-- With one query,
-- bring all relevant data into collection of records.
SELECT * BULK COLLECT INTO underpaid FROM employees
WHERE salary < 5000 ORDER BY salary DESC;
-- Process data by examining collection or passing it to
-- eparate procedure, instead of writing loop to FETCH each row.
DBMS_OUTPUT.PUT_LINE
(underpaid.COUNT || ' people make less than 5000.');
FOR i IN underpaid.FIRST .. underpaid.LAST
LOOP
DBMS_OUTPUT.PUT_LINE
(underpaid(i).last_name || ' makes ' || underpaid(i).salary);
END LOOP;
-- You can also bring in just some of the table columns.
-- Here you get the first and last names of 10 arbitrary employees.
SELECT first_name, last_name
BULK COLLECT INTO some_names
FROM employees
WHERE ROWNUM < 11;
FOR i IN some_names.FIRST .. some_names.LAST
LOOP
DBMS_OUTPUT.PUT_LINE
('Employee = ' || some_names(i).first_name
|| ' ' || some_names(i).last_name);
END LOOP;
END;
/
You don't typically need to worry about extending or how many elements you'll have, you can usually slurp it in and then use the built in features of the collection as you like (counts, loop through, compare different collections, set operations, etc)
您通常不需要担心扩展或您将拥有多少元素，您通常可以将其放入，然后根据需要使用集合的内置功能（计数、循环、比较不同的集合、设置操作， 等等）
