存储过程如何返回结果集？

[tmagain]

从sql server移植一存储过程，很简单，跑一条sql,返回一个结果集，仿照sql server 写成：
create or replace procedure se_samelevel(menuId in varchar2, sameMenus in varchar2) is
v_sql varchar(1000);
begin

v_sql := 'SELECT MenuIndex help_index, MenuLevel help_level,Caption name, '||
'(decode (MenuIndex,'||menuId||',''true'',''false'')) selected, '||
'Methods eventhandler, DisplayMode displaymode, '||
'UpFileName upfilename, DownFileName downfilename, '||
'ParentIndex help_parent '||
'FROM SYSMENUS '||
'WHERE MenuIndex IN ('||sameMenus ||') '||
'ORDER BY displaymode, menuindex ';

execute immediate v_sql;
end se_samelevel;

然后，执行
declare
-- Local variables here
i integer;
begin
se_samelevel('21','21');

end;

没有返回预期结果集，所有操作在sqlserver中都可以

[beckhambobo]

CREATE OR REPLACE PACKAGE pkg_test
AS
TYPE myrctype IS REF CURSOR;
END pkg_test;
/

create or replace procedure se_samelevel(menuId in varchar2, sameMenus in varchar2,p_rc out pkg_test.myrctype) is
v_sql varchar2(1000);
begin
v_sql := 'SELECT MenuIndex help_index, MenuLevel help_level,Caption name, (decode (MenuIndex,'||menuId||',''true'',''false'')) selected,Methods eventhandler, DisplayMode displaymode, UpFileName upfilename, DownFileName downfilename, ParentIndex help_parent FROM SYSMENUS WHERE MenuIndex IN ('||sameMenus ||') ORDER BY displaymode, menuindex ';

open p_rc for v_sql;
end se_samelevel;
/

declare
v_rc pkg_test.myrctype;
v1 ..
v2 ..
....
begin
se_samelevel('21','21',v_rc);
loop
fetch v_rc into v1,v2,....;
exit when v_rc%notfound;
dbms_output.put_line(v1||v2||...);
end loop;
end;
/

[borrowheart]

创建一个游标就可以反回了

[hbu-dragon]

oracle和m$sql不一样，搜索一下历史帖子，有很多。

[jiezhi]

create or replace package pkg_test
as
type cur_test is ref cursor; -- 定義一個cursor的type
end pkg_test;
/
create or replace procedure p_test
(
v_cur out pkg_test.cur_test
)
as
v_sql varchar2(100); --
begin
v_sql := 'select a1,a2 from test';
OPEN v_cur FOR v_sql; --
exception
when others then
DBMS_OUTPUT.PUT_LINE('Error ---------------' || sqlcode || ' : ' || sqlerrm );
end p_test;
/
Java程式:
……
CallableStatement call = conn.prepareCall("{ call p_test(?) }");
call.registerOutParameter(1, OracleTypes.CURSOR);// 註冊out參數的SQL數據類型
call.execute();
ResultSet rs=(ResultSet)call.getObject(1);// 取得得數据結果集合
while(rs.next())
……

[joachern]

up