[求助]如何调用包中的存储过程？？？在线等待!

[neil2000]

下面是我写得包和调用，总是出错!

create or replace package test as

type test_id is table of kogdb.staff.id%type;

procedure getStaffInfo
(
myid out test_id
) ;
end test;

************************************************************

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
for c in mycursor loop
myid(counter) := c.id;
counter := counter + 1;
end loop;
end;

end test;

************************************************************

调用：
declare
type ddd is table of staff.id%type ;
kk ddd;
begin
test.getstaffinfo(kk);
end;

总是出现下面的错误，请帮忙解决

ORA-06550: 第 5 行, 第 6 列:
PLS-00306: 调用 'GETSTAFFINFO' 时参数数量或类型错误
ORA-06550: 第 5 行, 第 6 列:
PL/SQL: Statement ignored

[beckhambobo]

SQL> create type t_tb is table of varchar2(10);

Type created

SQL> 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 created

SQL> 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
3

PL/SQL procedure successfully completed

[beckhambobo]

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;

[Lastdrop]

嵌套表在使用前一般需要初始化，也可以用下面的方法实现你的需求，此时不需要初始化。(查查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;

[linazhu]

学习

[Lastdrop]

这样调用试试

declare
kk test.test_id;
begin
test.getstaffinfo(kk);
end;

[neil2000]

又出现下面的错误提示:

ORA-06531: 引用未初始化的集合
ORA-06512: 在"KTB.TEST", line 13
ORA-06512: 在line 4