--可以使用存贮过程实现,给个例子:sql>select select * from test; A B ---------- ---------- 1 1 2 2 3 3 3 10sql>create or replace procedure p_test(in_var number,out_var out sys_refcursor) 2 as 3 begin 4 open out_var for select * from test where aaa=in_var; 5 end; 6 /Procedure created.sql>var abc refcursor sql>exec p_test(3,:var_test)PL/SQL procedure successfully completed.sql>print :var_test A B ---------- ---------- 3 3 3 10
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 sqlstr := 'select * from emp'; OPEN p_rc FOR sqlstr USING p_id; END get; END pkg_test; /
--可以使用存贮过程实现,给个例子:sql>select select * from test; A B
---------- ----------
1 1
2 2
3 3
3 10sql>create or replace procedure p_test(in_var number,out_var out sys_refcursor)
2 as
3 begin
4 open out_var for select * from test where aaa=in_var;
5 end;
6 /Procedure created.sql>var abc refcursor
sql>exec p_test(3,:var_test)PL/SQL procedure successfully completed.sql>print :var_test A B
---------- ----------
3 3
3 10
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
sqlstr := 'select * from emp';
OPEN p_rc FOR sqlstr USING p_id;
END get;
END pkg_test;
/