又出现下面的错误提示:ORA-06531: 引用未初始化的集合 ORA-06512: 在"KTB.TEST", line 13 ORA-06512: 在line 4
SQL> create type t_tb is table of varchar2(10);Type createdSQL> create procedure get_tb(v_tb in out t_tb) 2 as 3 num number:=0; 4 begin 5 for i in 1..3 loop 6 num:=num+1; 7 v_tb.extend; 8 v_tb(num):=i; 9 end loop; 10 end; 11 /Procedure createdSQL> set serveroutput on SQL> SQL> declare 2 v1 t_tb:=t_tb(); 3 begin 4 get_tb(v1); 5 for i in 1..3 loop 6 dbms_output.put_line(v1(i)); 7 end loop; 8 end; 9 / 1 2 3PL/SQL procedure successfully completed
create or replace package test as
type test_id is table of kogdb.staff.id%type;
procedure getStaffInfo ( myid in out test_id ) ; end test; create or replace package body test as procedure getStaffInfo ( myid in out test_id ) as cursor mycursor is select staff.id from staff order by id asc; counter number default 1; begin for c in mycursor loop myid.extend; myid(counter) := c.id; counter := counter + 1; end loop; end;end test; declare v_id test.test_id:=test.test_id(); begin test.getstaffinfo(v_id); end;
嵌套表在使用前一般需要初始化,也可以用下面的方法实现你的需求,此时不需要初始化。(查查PL/SQL的帮助就知道了,我也是照搬的 )create or replace package body test as procedure getStaffInfo ( myid out test_id ) as cursor mycursor is select staff.id from staff order by id asc; counter number default 1; begin open c; FETCH c BULK COLLECT INTO myid; end; end test;或 create or replace package body test as procedure getStaffInfo ( myid out test_id ) as begin --但我不知道是否能保证顺序,若不能就用上面的方式好了。 SELECT staff.id BULK COLLECT INTO myid from staff order by id asc;end test;
ORA-06512: 在"KTB.TEST", line 13
ORA-06512: 在line 4
2 as
3 num number:=0;
4 begin
5 for i in 1..3 loop
6 num:=num+1;
7 v_tb.extend;
8 v_tb(num):=i;
9 end loop;
10 end;
11 /Procedure createdSQL> set serveroutput on
SQL>
SQL> declare
2 v1 t_tb:=t_tb();
3 begin
4 get_tb(v1);
5 for i in 1..3 loop
6 dbms_output.put_line(v1(i));
7 end loop;
8 end;
9 /
1
2
3PL/SQL procedure successfully completed
type test_id is table of kogdb.staff.id%type;
procedure getStaffInfo
(
myid in out test_id
) ;
end test;
create or replace package body test as procedure getStaffInfo
(
myid in out test_id
)
as
cursor mycursor is select staff.id from staff order by id asc;
counter number default 1;
begin
for c in mycursor loop
myid.extend;
myid(counter) := c.id;
counter := counter + 1;
end loop;
end;end test;
declare
v_id test.test_id:=test.test_id();
begin
test.getstaffinfo(v_id);
end;
procedure getStaffInfo
(
myid out test_id
)
as
cursor mycursor is select staff.id from staff order by id asc;
counter number default 1;
begin
open c;
FETCH c BULK COLLECT INTO myid;
end;
end test;或
create or replace package body test as
procedure getStaffInfo
(
myid out test_id
)
as
begin
--但我不知道是否能保证顺序,若不能就用上面的方式好了。
SELECT staff.id BULK COLLECT INTO myid from staff order by id asc;end test;