oracle 如何初始化驻留在 PL/SQL 记录中的嵌套表集合？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4717439/
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 initialise a nested table collection which resides inside a PL/SQL Record?
提问by Ian Carpenter
Friends,
朋友们，
Hope you can help.
希望你能帮忙。
What I am trying to achieve is to use a collection type(s) that can be accessed either inside and outside of PL/SQL so that an external program can declare a type of this collection and work with it's contents.
我想要实现的是使用可以在 PL/SQL 内部和外部访问的集合类型，以便外部程序可以声明此集合的类型并处理它的内容。
The collection will contain some scaler and one composite datatype.
该集合将包含一些缩放器和一种复合数据类型。
Using the scott schema as an example, the goal is that each record within the collection should contain the department information and within the same record a collection containing the employee information for that department.
以 scott 模式为例，目标是集合中的每条记录都应包含部门信息，而同一记录中的集合应包含该部门的员工信息。
I have got the output I require when using PL/SQL associative arrays but they can only be used from with PL/SQL.
我得到了使用 PL/SQL 关联数组时所需的输出，但它们只能与 PL/SQL 一起使用。
When I convert the code to use another type of collection, nested table, I receive a ORA-06531: Reference to uninitialized collectionWhich is because I haven't initialised the collection held within the record.
当我将代码转换为使用另一种类型的集合（嵌套表）时，我收到一个ORA-06531: Reference to uninitialized collection这是因为我尚未初始化记录中保存的集合。
Is it possible to achieve this using this design? Or (as I increasing feel!) have I gone down the wrong path?
使用这种设计是否可以实现这一目标？或者（因为我越来越觉得！）我走错了路？
Two code samples follow.
下面是两个代码示例。
Firstly the one that works with PL/SQL associative arrays:
首先是与 PL/SQL 关联数组一起使用的那个：
DECLARE
TYPE emp_tab_type IS TABLE OF emp%ROWTYPE
INDEX BY BINARY_INTEGER;
TYPE dept_emp_rec IS RECORD (dept_id dept.deptno%TYPE,
dept_name dept.dname%TYPE,
dept_loc dept.loc%TYPE,
emp_data emp_tab_type);
TYPE dept_emp_tab_type IS TABLE OF dept_emp_rec
INDEX BY BINARY_INTEGER;
l_dept_emp_tab dept_emp_tab_type;
CURSOR dept_cur IS
SELECT d.*
FROM dept d
ORDER BY d.deptno;
CURSOR emps_cur (p_dept_id IN NUMBER ) IS
SELECT e.*
FROM emp e
WHERE e.deptno = p_dept_id
ORDER BY e.ename;
j PLS_INTEGER := 1;
k PLS_INTEGER;
BEGIN
FOR dept_rec IN dept_cur
LOOP
-- populate dept data
l_dept_emp_tab(j).dept_id := dept_rec.deptno;
-- other assignment statements
dbms_output.put_line('dept no ' || l_dept_emp_tab(j).dept_id);
-- populate emp data
k := 1;
FOR emp_row_rec IN emps_cur(dept_rec.deptno)
LOOP
l_dept_emp_tab(j).emp_data(k).empno := emp_row_rec.empno;
-- other assignment statements
dbms_output.put_line( l_dept_emp_tab(j).emp_data(k).empno);
k := k + 1;
END LOOP;
j := j + 1;
END LOOP;
END;
This is the example using nested tables that DOESN'T work
这是使用不起作用的嵌套表的示例
DECLARE
TYPE emp_tab_type IS TABLE OF emp%ROWTYPE;
--INDEX BY BINARY_INTEGER;
TYPE dept_emp_rec IS RECORD (dept_id dept.deptno%TYPE,
dept_name dept.dname%TYPE,
dept_loc dept.loc%TYPE,
emp_data emp_tab_type);
TYPE dept_emp_tab_type IS TABLE OF dept_emp_rec;
--INDEX BY BINARY_INTEGER;
l_dept_emp_tab dept_emp_tab_type := dept_emp_tab_type();
CURSOR dept_cur IS
SELECT d.*
FROM dept d
ORDER BY d.deptno;
CURSOR emps_cur (p_dept_id IN NUMBER ) IS
SELECT e.*
FROM emp e
WHERE e.deptno = p_dept_id
ORDER BY e.ename;
j PLS_INTEGER := 1;
k PLS_INTEGER;
BEGIN
FOR dept_rec IN dept_cur
LOOP
l_dept_emp_tab.EXTEND;
-- populate dept data
l_dept_emp_tab(j).dept_id := dept_rec.deptno;
-- other assignment statements
dbms_output.put_line('dept no ' || l_dept_emp_tab(j).dept_id);
-- populate emp data
k := 1;
FOR emp_row_rec IN emps_cur(dept_rec.deptno)
LOOP
l_dept_emp_tab(j).emp_data(k).empno := emp_row_rec.empno;
-- other assignment statements
dbms_output.put_line( l_dept_emp_tab(j).emp_data(k).empno);
k := k + 1;
END LOOP;
j := j + 1;
END LOOP;
END;
I am using Oracle Enterprise Edition 10.2.0.4
我使用的是 Oracle 企业版 10.2.0.4
Thanks
谢谢
回答by Luke Woodward
You are indeed getting the ORA-06531error because you haven't initialised the collections within each record. To do this, try adding the line
您确实收到了ORA-06531错误，因为您尚未初始化每条记录中的集合。为此，请尝试添加该行
l_dept_emp_tab(j).emp_data := emp_tab_type();
to the other assignments to fields of l_dept_emp_tab(j).
到 的字段的其他分配l_dept_emp_tab(j)。
You'll also need to add a call to l_dept_emp_tab(j).emp_data.EXTENDwithin the inner loop, to make space for the new entry about to be added. Insert this above all the assignments within the inner loop. If you don't add this, you'll get an ORA-06533: Subscript beyond counterror.
您还需要l_dept_emp_tab(j).emp_data.EXTEND在内循环中添加一个调用，为即将添加的新条目腾出空间。将此插入到内部循环中的所有分配之上。如果你不添加这个，你会得到一个ORA-06533: Subscript beyond count错误。
You seem to be handling the outer nested table type (dept_emp_tab_type) correctly, by calling its constructor (in the DECLAREsection) and by calling EXTENDto grow the nested table. All you need to do is to do the same for each instance of the inner nested table type,emp_tab_type.
您似乎正确处理了外部嵌套表类型 ( dept_emp_tab_type)，方法是调用其构造函数（在DECLARE部分中）并调用EXTEND来扩展嵌套表。您需要做的就是对内部嵌套表类型的每个实例执行相同的操作，emp_tab_type.
回答by Harrison
Here is a different way, this accomplishes everything pretty much within a query (do note it requires the types to be created outside of the block)
这是一种不同的方式，这在查询中几乎完成了所有事情（请注意，它需要在块之外创建类型）
http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96624/05_colls.htm
http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96624/05_colls.htm
Creation and cleanup of table and types used
创建和清理使用的表和类型
/*
CREATE TABLE EMP (ENAME VARCHAR2(50) , DEPTNO NUMBER, empno number);
INSERT INTO EMP VALUES('m1e',1,1);
INSERT INTO EMP VALUES('m2e',1,2);
insert into emp values('m3e',2,3);
INSERT INTO EMP VALUES('m2e',2,4);
insert into emp values('m3e',3,5);
CREATE TABLE DEPT(deptno NUMBER, dname VARCHAR2(50), loc VARCHAR2(50));
INSERT INTO DEPT VALUES(1 ,'portland','tt');
INSERT INTO DEPT VALUES(2 ,'astoria','tt');
INSERT INTO DEPT VALUES(3 ,'eugene','tt');
Creation of types (note this is not within the package/block so that it is available to SQL)
类型的创建（注意这不在包/块中，因此它可用于 SQL）
---
drop type emptable force;
DROP TYPE EMP_TAB_TYPE force;
drop type emptable ;
DROP TYPE DEPT_EMP_REC force;
drop type dep_emp_rec_table force;
DROP TABLE DEPT;
drop table emp;
*/
Now create the types outside the package/block so the types are available to SQL
现在在包/块之外创建类型，以便 SQL 可以使用这些类型
create or replace TYPE emp_tab_type as object (ENAME VARCHAR2(50) , DEPTNO NUMBER);
create or replace type emptable as table of emp_tab_type ;
CREATE OR REPLACE TYPE DEPT_EMP_REC AS OBJECT (
DEPT_ID NUMBER,
dept_name varchar2(50),
dept_loc varchar2(50),
emp_data emptable);
create or replace type dep_emp_rec_table as table of dept_emp_rec;
Now we can directly select the types into the query (note the use of the cast/MULTISET)
现在我们可以直接选择类型到查询中（注意cast/MULTISET 的使用）
SELECT
DEPT_EMP_REC(
deptno,
dname ,
loc ,
CAST(MULTISET(SELECT ENAME, DEPTNO
FROM EMP e
WHERE e.DEPTNO = d.deptno)
AS emptable))
FROM DEPT D ;
/
DEPT_EMP_REC(DEPTNO,DNAME,LOC,CAST(MULTISET(SELECTENAME,DEPTNOFROMEMPEWHEREE.DEPTNO=D.DEPTNO)ASEMPTABLE)) DEPT_EMP_REC(1,'portland','tt',EMPTABLE(EMP_TAB_TYPE('m1e',1),EMP_TAB_TYPE('m2e',1)))
DEPT_EMP_REC(2,'astoria','tt',EMPTABLE(EMP_TAB_TYPE('m3e',2),EMP_TAB_TYPE('m2e',2)))
DEPT_EMP_REC(3,'eugene','tt',EMPTABLE(EMP_TAB_TYPE('m3e',3)))
Now the block is a bit simpler (putting it all together)
现在这个块有点简单（把它们放在一起）
set serveroutput on
DECLARE
p_dep_emp_rec_table dep_emp_rec_table;
BEGIN
SELECT
DEPT_EMP_REC(
DEPTNO,
DNAME,
LOC,
CAST( MULTISET
(
SELECT
ENAME,
DEPTNO
FROM EMP E
WHERE E.DEPTNO = D.DEPTNO
) AS EMPTABLE )
)
BULK COLLECT INTO p_dep_emp_rec_table
FROM
DEPT d ;
FOR I IN P_DEP_EMP_REC_TABLE.FIRST..P_DEP_EMP_REC_TABLE.LAST LOOP
DBMS_OUTPUT.PUT_LINE(I || ':' || P_DEP_EMP_REC_TABLE(I).DEPT_ID || '|' || P_DEP_EMP_REC_TABLE(I).DEPT_NAME || '|' || P_DEP_EMP_REC_TABLE(I).DEPT_LOC);
DBMS_OUTPUT.PUT_LINE('-----------------------');
FOR J IN P_DEP_EMP_REC_TABLE(I).EMP_DATA.FIRST..P_DEP_EMP_REC_TABLE(I).EMP_DATA.LAST LOOP
NULL;
dbms_output.put_line(P_DEP_EMP_REC_TABLE(i).emp_data(j).ENAME || '/' || P_DEP_EMP_REC_TABLE(i).emp_data(j).DEPTNO);
end loop;
END LOOP;
END;
anonymous block completed
1:1|portland|tt
-----------------------
m1e/1
m2e/1
2:2|astoria|tt
-----------------------
m3e/2
m2e/2
3:3|eugene|tt
-----------------------
m3e/3
