存储过程例子

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                                                         --加工资之后