说明:table()必须使用schema级别的集合对象
SQL> Declare
2 Type t_nstbl Is Table Of Varchar2(20); --本地
3 v_nstbl t_nstbl;
4 csr Sys_Refcursor;
5 vlu Varchar2(20);
6 Begin
7 v_nstbl:=t_nstbl('a','b','c');
8 Open csr For Select * From (Table(v_nstbl));
9 Loop
10 Fetch csr Into vlu;
11 Exit When csr%Notfound;
12 dbms_output.put_line(vlu);
13 End Loop;
14 End;
15 /
Declare
Type t_nstbl Is Table Of Varchar2(20);
v_nstbl t_nstbl;
csr Sys_Refcursor;
vlu Varchar2(20);
Begin
v_nstbl:=t_nstbl('a','b','c');
Open csr For Select * From (Table(v_nstbl));
Loop
Fetch csr Into vlu;
Exit When csr%Notfound;
dbms_output.put_line(vlu);
End Loop;
End;
ORA-06550: 第 8 行, 第 37 列:
PLS-00642: 在 SQL 语句中不允许使用本地收集类型
ORA-06550: 第 8 行, 第 31 列:
PL/SQL: ORA-22905: 无法从非嵌套表项访问行
ORA-06550: 第 8 行, 第 16 列:
PL/SQL: SQL Statement ignored
SQL> Create Or Replace Type t_nstbl Is Table Of Varchar2(20); --schema级别
2 /
Type created
SQL> Declare
2 -- Type t_nstbl Is Table Of Varchar2(20);
3 v_nstbl t_nstbl;
4 csr Sys_Refcursor;
5 vlu Varchar2(20);
6 Begin
7 v_nstbl:=t_nstbl('a','b','c');
8 Open csr For Select * From (Table(v_nstbl));
9 Loop
10 Fetch csr Into vlu;
11 Exit When csr%Notfound;
12 dbms_output.put_line(vlu);
13 End Loop;
14 End;
15 /
a
b
c
PL/SQL procedure successfully completed
SQL> Declare
2 Type t_nstbl Is Table Of Varchar2(20); --本地
3 v_nstbl t_nstbl;
4 csr Sys_Refcursor;
5 vlu Varchar2(20);
6 Begin
7 v_nstbl:=t_nstbl('a','b','c');
8 Open csr For Select * From (Table(v_nstbl));
9 Loop
10 Fetch csr Into vlu;
11 Exit When csr%Notfound;
12 dbms_output.put_line(vlu);
13 End Loop;
14 End;
15 /
Declare
Type t_nstbl Is Table Of Varchar2(20);
v_nstbl t_nstbl;
csr Sys_Refcursor;
vlu Varchar2(20);
Begin
v_nstbl:=t_nstbl('a','b','c');
Open csr For Select * From (Table(v_nstbl));
Loop
Fetch csr Into vlu;
Exit When csr%Notfound;
dbms_output.put_line(vlu);
End Loop;
End;
ORA-06550: 第 8 行, 第 37 列:
PLS-00642: 在 SQL 语句中不允许使用本地收集类型
ORA-06550: 第 8 行, 第 31 列:
PL/SQL: ORA-22905: 无法从非嵌套表项访问行
ORA-06550: 第 8 行, 第 16 列:
PL/SQL: SQL Statement ignored
SQL> Create Or Replace Type t_nstbl Is Table Of Varchar2(20); --schema级别
2 /
Type created
SQL> Declare
2 -- Type t_nstbl Is Table Of Varchar2(20);
3 v_nstbl t_nstbl;
4 csr Sys_Refcursor;
5 vlu Varchar2(20);
6 Begin
7 v_nstbl:=t_nstbl('a','b','c');
8 Open csr For Select * From (Table(v_nstbl));
9 Loop
10 Fetch csr Into vlu;
11 Exit When csr%Notfound;
12 dbms_output.put_line(vlu);
13 End Loop;
14 End;
15 /
a
b
c
PL/SQL procedure successfully completed