请问：存储过程如何返回一个游标？

[gogowhy]

1：我想用返回一个记录集，返回一个游标要怎么做呢？

create or replace function test_cursor
(
v_id in number
)
return *****; --这里要怎么写？
is
m_name varchar2(10);

TYPE REF_CURSOR IS REF CURSOR;
cursor_test REF_CURSOR;

begin
OPEN cursor_test FOR SELECT m_name FROM table1; --我的游标

return ***** ---这里要怎么写呢？
end;



2：如果我要返回2个（或者2个以上）游标要怎么做呢？有没有办法？

3：如果不用游标，可以返回一个数组吗？要怎么做呢？

谁能给个例子，谢谢！！

[gogowhy]

不好意思，写错了~
m_name 不是变量，是表的字段

谁帮帮我呀~~

[lialin]

你的游标在包里定义，不要在这里定义，在这里只能是一个私有变量，到时没有办法调用呀
return直接是一个游标名就可以了！
return cursor_test;

[LGQDUCKY]

pl/sql怎么返回数据集?
CREATE OR REPLACE PACKAGE pkg_test
AS
TYPE myrctype IS REF CURSOR;

PROCEDURE get (p_id NUMBER, p_rc OUT myrctype);
END pkg_test;
/

CREATE OR REPLACE PACKAGE BODY pkg_test
AS
PROCEDURE get (p_id NUMBER, p_rc OUT myrctype)
IS
sqlstr VARCHAR2 (500);
BEGIN
IF p_id = 0 THEN
OPEN p_rc FOR
SELECT ID, NAME, sex, address, postcode, birthday
FROM student;
ELSE
sqlstr :=
'select id,name,sex,address,postcode,birthday
from student where id=:w_id';
OPEN p_rc FOR sqlstr USING p_id;
END IF;
END get;
END pkg_test;
/

[gogowhy]

哦~~~~~~~~~

恍然大悟状~~~~~

呵呵

谢谢 lialin(阿林) 的提醒，更加感谢 LGQDUCKY(飘) 的例子
等我测试通过了再结帖~~
明天吧

[gogowhy]

补足，呵呵

--------------------------------------------
LGQDUCKY(飘)

pl/sql怎么返回数据集?
CREATE OR REPLACE PACKAGE pkg_test
AS
TYPE myrctype IS REF CURSOR;

PROCEDURE get (p_id NUMBER, p_rc OUT myrctype);
END pkg_test;
/

CREATE OR REPLACE PACKAGE BODY pkg_test
AS
PROCEDURE get (p_id NUMBER, p_rc OUT myrctype)
IS
sqlstr VARCHAR2 (500);
BEGIN
IF p_id = 0 THEN
OPEN p_rc FOR
SELECT ID, NAME, sex, address, postcode, birthday
FROM student;
ELSE
sqlstr :=
'select id,name,sex,address,postcode,birthday
from student where id=:w_id';
OPEN p_rc FOR sqlstr USING p_id;
END IF;
END get;
END pkg_test;
/
create or replace package pkg_test
as
type myCursor is ref cursor;
function get(p_id number) return myCursor;
end pkg_test;

create or replace package body pkg_test
as
--输入ID 返回记录集的函数
function get(p_id number) return myCursor is
rc myCursor;
strsql varchar2(200);
begin
if p_id=0 then
open rc for select a.user_name from fnd_user a ;
else
strsql:='select a.user_name from fnd_user a where a.user_id=:p_id';
open rc for strsql using p_id;
end if;
return rc;
end get;
end pkg_test;


--上面是一个返回结果集的函数,下面调用.
create or replace procedure pro_test as
v_out pkg_test%myCursor;
v_name varchar2(100);
begin
v_out:=pkg_test.get(0); --得到结果集
loop
fetch v_out into v_name;
exit when v_out%notfound;
--这里进行处理,想要处理哪一行或进行什么处理在这里进行
end loop;
......
end;