Oracle中存储过程

来源：互联网发布：pptv聚力网络电视官网编辑：程序博客网时间：2024/05/18 02:53

--转

/****************存储过程的定义********************/

--包头

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;

/****************函数的定义********************/

--包头
create   or   replace   package   pkg_test
as
      type   myCursor   is   ref   cursor;
      function   get(p_id   number)   return   myCursor;
end   pkg_test;


--包体
create   or   replace   package   body   pkg_test
as
--******************************************************************
      --输入ID   返回记录集的函数
      function   get(p_id   number)   return   myCursor   is
            rc   myCursor;
            strsql   varchar2(200);
      begin
            if   p_id=0   then
                  open   rc   for   select   a.user_name   from   fnd_user   a   ;
            else
                  strsql:='select   a.user_name   from   fnd_user   a   where   a.user_id=:p_id';
                  open   rc   for   strsql   using   p_id;
            end   if;
            return   rc;
      end   get;

end   pkg_test;

--调用
set   serverout   on
declare
      w_rc   pkg_test.myCursor;
      w_name   varchar2(100);
begin
      w_rc:=pkg_test.get(0);
      loop
      fetch   w_rc   into   w_name;
                  exit   when   w_rc%notfound;
      dbms_output.put_line(w_name);
      end   loop;
end;