存储过程例子

1. 存储过程返回记录集：
   
   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; 
   /  
     
   函数返回记录集: 
   建立带ref cursor定义的包和包体及函数： 
   CREATE   OR   REPLACE  
   package pkg_test  as  
   /**/ /* 定义ref cursor类型 
       不加return类型，为弱类型，允许动态sql查询， 
       否则为强类型，无法使用动态sql查询; 
   */  
      type myrctype  is  ref  cursor ;  
      
   -- 函数申明 
       function  get(intID  number )  return  myrctype; 
   end  pkg_test; 
   /  
      
   CREATE   OR   REPLACE  
   package body pkg_test  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  id,name,sex,address,postcode,birthday  from  student; 
          else  
             -- 动态sql赋值，用:w_id来申明该变量从外部获得 
            sqlstr : =   ' select id,name,sex,address,postcode,birthday from student where id=:w_id ' ; 
             -- 动态测试，用sqlstr字符串返回结果，用using关键词传递参数 
             open  rc  for  sqlstr using intid; 
          end   if ; 
      
          return  rc; 
        end  get; 
      
   end  pkg_test;
   
   
2. 一个简单的应用,带参数的PROCEDURE,隐式的打开CURSOR.

-- 工资加500，OLD。SAL>2500 不加，NEW。SAL>2500 则改为2500；

SELECT   *   FROM  EMP                                                                 -- 加工资之前

CREATE   OR   REPLACE   PROCEDURE  SP_ADD ( add   IN   number )  is        -- add参数接收传入的工资增幅

      -- DECLARE
      -- CURSOR emp_cur IS SELECT * FROM emp;
     
BEGIN
     
      FOR   sal_sum  IN  ( SELECT   *   FROM  emp) LOOP          --使用循环游标隐式打开游标emp_cur或

                                                                                  -- (SELECT * FROM emp)(在FOR循环中不需要声明此游标)
                                                                                 --自动从活动集获取行，然后在处理完所有行时关闭游标     
           IF  sal_sum.sal  >   2500   THEN                                               -- 逐行进行比较赋值
                 UPDATE  emp  SET  sal  =  sal_sum.sal  WHERE  empno  =  sal_sum.empno;
                
          ELSIF (sal_sum.sal  +   add )  >   2500   THEN
                 UPDATE  emp  SET  sal  =   2500   WHERE  empno  =  sal_sum.empno;
                
           ELSE               
                 UPDATE  emp  SET  sal  =  sal_sum.sal  +   add    WHERE  empno  =  sal_sum.empno;
           END   IF ;
          
      END  LOOP;
     
END ;


BEGIN
     SP_ADD( 500 );                                                                  -- 输入增资的幅度                                                                                                                     
END ;

SELECT   *   FROM  EMP                                                          -- 加工资之后