要创建包头 ---------------- 参与如下: create or replace package bao1 is /* 定义ref cursor类型 不加return类型,为弱类型,允许动态sql查询, 否则为强类型,无法使用动态sql查询; */ type myrctype is ref cursor; --函数申明 function get(intID number) return myrctype; procedure p1; end bao1; / create or replace package body bao1 as function get(intID number) return myrctype is rc myrctype; --定义ref cursor变量 sqlstr varchar2(500); begin if intID=0 then --静态测试,直接用select语句直接返回结果 open rc for select name from stuff; else --动态sql赋值,用:w_id来申明该变量从外部获得 sqlstr := 'select name from stuff where id=:w_id'; --动态测试,用sqlstr字符串返回结果,用using关键词传递参数 open rc for sqlstr using intid; end if; return rc;
end get; procedure p1 as k varchar(200); begin select sysdate into k from dual; end p1; end bao1; /
第二行的begin删掉
----------------
参与如下: create or replace package bao1
is
/* 定义ref cursor类型
不加return类型,为弱类型,允许动态sql查询,
否则为强类型,无法使用动态sql查询;
*/
type myrctype is ref cursor; --函数申明
function get(intID number) return myrctype; procedure p1; end bao1;
/ create or replace package body bao1
as
function get(intID number) return myrctype is
rc myrctype; --定义ref cursor变量
sqlstr varchar2(500);
begin
if intID=0 then
--静态测试,直接用select语句直接返回结果
open rc for select name from stuff;
else
--动态sql赋值,用:w_id来申明该变量从外部获得
sqlstr := 'select name from stuff where id=:w_id';
--动态测试,用sqlstr字符串返回结果,用using关键词传递参数
open rc for sqlstr using intid;
end if; return rc;
end get; procedure p1
as
k varchar(200);
begin
select sysdate into k from dual;
end p1;
end bao1;
/