返回結果集合在oracle里面比較麻煩,例子:
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;
/
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;
/
不妨用function试试:
create or replace package pkg_test
as
type cur_test is ref cursor; -- 定義一個cursor的type
/*
*返回结果集
*/
function getResult(input1 in number,input2 in varchar2)
return cur_test;
end pkg_test;
/
create or replace package body pkg_test is
/*
*返回结果集
*/
function getResult(input1 in number,input2 in varchar2)
return cur_test
is
cur_return cur_test;
begin
open cur_return for
SELECT *
FROM TEST
WHERE AA=INPUT1
AND BB LIKE '%'||INPUT2||'%';
return cur_return;
exception
when others then
if cur_return%isopen
then
close cur_return;
end if;
end getResult;end pkg_test;
本人不懂ORACLE!
在SQL SERVER 中这种存储过程很常见,很简单
请问类似的存储过程在ORACLE 中怎么处理的?
向楼上[jiezhi(浪子) ]那样处理?给个模版我套吧!或者把我的那个改成ORACLE的,我就会套了!
AS
TYPE myrctype IS REF CURSOR;
END pkg_test;
/CREATE PROCEDURE PRO_SLE(INPUT1 in integer,INPUT2 in VARCHAR2,p_rc out pkg_test.myrctype)
AS
begin
open p_rc for 'SELECT * FROM TEST WHERE AA='||INPUT1||' AND instr(BB,'||INPUT2||')>0';
end;
/
v_col1 TEST.col1%type;
v_col1 test.col1%type;
....
v_rc pkg_test.myrctype;
begin
PRO_SLE(1,'1',v_rc);
loop
fetch v_rc into v_col1,v_col2,...;
exit when v_rc%notfound;
dbms_output.put_line(v_col1||v_col2||...);
end loop;
end;
/