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; /
--包头定义 as type persontyp is record (name varchar2(30),num number); type persontbtyp is table of persontyp;--记录表类型 type programmertb is table of programer%rowtype;--元组表类型 type mycurtyp is ref cursor;--游标变量类型,若类型 type nametbtyp is table of varchar(30); function rtresultset return programmertb; /* function <function name>(<argument name> <in out nocopy> <argument datatype> <default value>) return <return_datatype>; procedure <procedure name>(<argument name> <in out nocopy> <argument datatype> <default value>); */ end;--函数定义 --参数输入表名,返回游标结果集 ( v_tablenm in varchar default 'programer' ) return pppkg.mycurtyp as v_cur pppkg.mycurtyp; sqlstr varchar2(500); begin dbms_output.put_line(v_tablenm); sqlstr := 'select * from '||v_tablenm; open v_cur for sqlstr ; return v_cur; end; 该函数在sql*plus上测试通过,而且在powerbuilder上能够直接用来生成数据窗口。
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;
/
as
type persontyp is record (name varchar2(30),num number);
type persontbtyp is table of persontyp;--记录表类型
type programmertb is table of programer%rowtype;--元组表类型
type mycurtyp is ref cursor;--游标变量类型,若类型
type nametbtyp is table of varchar(30);
function rtresultset
return programmertb;
/* function <function name>(<argument name> <in out nocopy> <argument datatype> <default value>)
return <return_datatype>;
procedure <procedure name>(<argument name> <in out nocopy> <argument datatype> <default value>);
*/
end;--函数定义
--参数输入表名,返回游标结果集
(
v_tablenm in varchar default 'programer'
)
return pppkg.mycurtyp
as
v_cur pppkg.mycurtyp;
sqlstr varchar2(500);
begin
dbms_output.put_line(v_tablenm);
sqlstr := 'select * from '||v_tablenm;
open v_cur for sqlstr ;
return v_cur;
end;
该函数在sql*plus上测试通过,而且在powerbuilder上能够直接用来生成数据窗口。