Oracle存储过程

一、 存储过程

1、定义
所谓存储过程(Stored Procedure)，就是一组用于完成特定数据库功能的SQL语句集，该SQL语句集经过
编译后存储在数据库系统中。在使用时候，用户通过指定已经定义的存储过程名字并给出相应的存储过程参数
来调用并执行它，从而完成一个或一系列的数据库操作。

2、存储过程的创建
Oracle存储过程包含三部分：过程声明，执行过程部分，存储过程异常。
（1）无参存储过程语法

Sql代码  

1. create or replace procedure NoParPro  
2.  as  //声明  
3.  ;  
4.  begin // 执行  
5.  ;  
6.  exception//存储过程异常  
7.  ;  
8.  end;  

 

（2）带参存储过程实例

Sql代码  

1. create or replace procedure queryempname(sfindno emp.empno%type)   
2. as  
3.    sName emp.ename%type;  
4.    sjob emp.job%type;  
5. begin  
6.        ....  
7. exception  
8.        ....  
9. end;  

 

（3）带参数存储过程含赋值方式

Sql代码  

1. create or replace procedure runbyparmeters    
2.     (isal in emp.sal%type,   
3.      sname out varchar,  
4.      sjob in out varchar)  
5.  as   
6.     icount number;  
7.  begin  
8.       select count(*) into icount from emp where sal>isal and job=sjob;  
9.       if icount=1 then  
10.         ....  
11.       else  
12.        ....  
13.      end if;  
14. exception  
15.      when too_many_rows then  
16.      DBMS_OUTPUT.PUT_LINE('返回值多于1行');  
17.      when others then  
18.      DBMS_OUTPUT.PUT_LINE('在RUNBYPARMETERS过程中出错！');  
19. end;  

 

其中参数IN表示输入参数，是参数的默认模式。
OUT表示返回值参数，类型可以使用任意Oracle中的合法类型。
OUT模式定义的参数只能在过程体内部赋值，表示该参数可以将某个值传递回调用他的过程
IN OUT表示该参数可以向该过程中传递值，也可以将某个值传出去。

 

（4）存储过程中游标定义使用

Sql代码  

1. as //定义(游标一个可以遍历的结果集)   
2. CURSOR cur_1 IS   
3.   SELECT area_code,CMCODE,SUM(rmb_amt)/10000 rmb_amt_sn,  
4.          SUM(usd_amt)/10000 usd_amt_sn   
5.   FROM BGD_AREA_CM_M_BASE_T   
6.   WHERE ym >= vs_ym_sn_beg   
7.        AND ym <= vs_ym_sn_end   
8.   GROUP BY area_code,CMCODE;   
9.       
10. begin //执行（常用For语句遍历游标）       
11. FOR rec IN cur_1 LOOP   
12.   UPDATE xxxxxxxxxxx_T   
13.    SET rmb_amt_sn = rec.rmb_amt_sn,usd_amt_sn = rec.usd_amt_sn   
14.    WHERE area_code = rec.area_code   
15.    AND CMCODE = rec.CMCODE   
16.    AND ym = is_ym;   
17. END LOOP;  

（5）游标的定义

Sql代码  

1. --显示cursor的处理
2. declare  
3. ---声明cursor,创建和命名一个sql工作区
4. cursor cursor_name is  
5.     select real_name from account_hcz;
6.     v_realname varchar2(20);
7. begin 
8.     open cursor_name;---打开cursor,执行sql语句产生的结果集
9.     fetch cursor_name into v_realname;--提取cursor,提取结果集中的记录
10.     dbms_output.put_line(v_realname);
11.     close cursor_name;--关闭cursor
12. end;

3、在Oracle中对存储过程的调用 

（1）过程调用方式一

Sql代码  

1. declare  
2.       realsal emp.sal%type;  
3.       realname varchar(40);  
4.       realjob varchar(40);  
5. begin   //过程调用开始  
6.       realsal:=1100;  
7.       realname:='';  
8.       realjob:='CLERK';  
9.       runbyparmeters(realsal,realname,realjob);－－必须按顺序  
10.       DBMS_OUTPUT.PUT_LINE(REALNAME||'   '||REALJOB);  
11. END;  //过程调用结束  

 

（2）过程调用方式二

Sql代码  

1. declare  
2.      realsal emp.sal%type;  
3.      realname varchar(40);  
4.      realjob varchar(40);  
5. begin    //过程调用开始  
6.      realsal:=1100;  
7.      realname:='';  
8.      realjob:='CLERK';  
9.      －－指定值对应变量顺序可变  
10.      runbyparmeters(sname=>realname,isal=>realsal,sjob=>realjob);           
11.     DBMS_OUTPUT.PUT_LINE(REALNAME||'   '||REALJOB);  
12. END;  //过程调用结束    

 

（3）过程调用方式三（SQL命令行方式下）

Sql代码  

1. 1、SQL>exec  proc_emp('参数1','参数2');//无返回值过程调用  
2. 2、SQL>var vsal number  
3.      SQL> exec proc_emp ('参数1',:vsal);// 有返回值过程调用  
4.       或者：call proc_emp ('参数1',:vsal);// 有返回值过程调用  

 

4、JAVA调用Oracle存储过程

 

（1）不带输出参数情况，过程名称为pro1，参数个数1个，数据类型为整形数据

Java代码  

1. import  java.sql. * ;   
2.  public   class  ProcedureNoArgs{      
3.      public   static   void  main(String args[])  throws  Exception{   
4.          //加载Oracle驱动    
5.          DriverManager.registerDriver( new  oracle.jdbc.driver.OracleDriver());   
6.          //获得Oracle数据库连接    
7.          Connection conn = DriverManager.getConnection  
8.           ("jdbc:oracle:thin:@MyDbComputerNameOrIP:1521:ORCL", sUsr, sPwd " );  
9.          //创建存储过程的对象    
10.          CallableStatement c = conn.divpareCall( " {call pro1(?)} " );        
11.          //给Oracle存储过程的参数设置值 ，将第一个参数的值设置成188    
12.           c.setInt( 1 , 188 );        
13.          // 执行Oracle存储过程    
14.           c.execute();   
15.           conn.close();   
16.      }    
17. }  

 

（2）带输出参数的情况,过程名称为pro2，参数个数2个，数据类型为整形数据，返回值为整形类型。

Java代码  

1. import java.sql.*;   
2. public class ProcedureWithArgs {     
3.     public static void main(String args[]) throws Exception{       
4.        //加载Oracle驱动   
5.        DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());   
6.        //获得Oracle数据库连接   
7.        Connection conn = DriverManager.getConnection  
8.        ("jdbc:oracle:thin:@MyDbComputerNameOrIP:1521:ORCL", sUsr, sPwd ");   
9.        //创建Oracle存储过程的对象，调用存储过程   
10.        CallableStatement c=conn.divpareCall("{call pro2(?,?)}");    
11.        //给Oracle存储过程的参数设置值 ，将第一个参数的值设置成188   
12.        c.setInt(1,188);   
13.        //注册存储过程的第二个参数   
14.        c.registerOutParameter(2,java.sql.Types.INTEGER);      
15.        c.execute(); //执行Oracle存储过程   
16.        //得到存储过程的输出参数值并打印出来  
17.        System.out.println (c.getInt(2));   
18.        conn.close();   
19.     }   
20. }    

 

二、 函数

1、基本语法规则

 

Sql代码  

1. create or replace function (Name in type, Name in type, ...)   
2.     return number   
3.   is  
4.     Result number;  
5.  begin    
6.     return (Result);  
7.  end ;  

 

2、具体事例（查询出empno=7935的sal值）

Sql代码  

1. create or replace function ret_emp_sal(v_ename varchar2)  
2. return number  
3.  is  
4. v_sal number(7,2);  
5.  begin  
6. select nvl(sal,0) into v_sal from emp where lower(ename)=lower(v_ename);  
7. return v_sal;  
8.  end;  

 

3、函数调用：

Sql代码  

1. SQL> var vsla number  
2. SQL> call ret_emp_sal('7935') into :vsal;  

 

4、与存储过程的区别

（1）返回值的区别,函数有1个返回值,而存储过程是通过参数返回的,可以有多个或者没有
（2）调用的区别,函数可以在查询语句中直接调用,而存储过程必须单独调用.
（3）使用场景的区别，函数一般情况下是用来计算并返回一个计算结果
 而存储过程一般是用来完成特定的数据操作（比如修改、插入数据库表或执行某些DDL语句等等）

 

三、包
    包用于组合逻辑相关的过程和函数，它由包规范和包体两个部分组成。包规范用于定义公用的常量、变量、过程
和函数，创建包规范可以使用CREATE PACKAGE命令，创建包体可以使用CREATE PACKAGE BODY.

1、创建包规范

Sql代码  

1. create package emp_pkg is  
2.     procedure emp_update_ename(v_empno varchar2,v_ename varchar2);  
3.     function emp_get_sal(v_empno varchar2) return number;  
4. end;  

 

2、创建包体

Sql代码  

1. create or replace package body emp_pkg  
2. is  
3.     // 存储过程  
4. procedure emp_update_ename  
5. (  
6.     v_empno varchar2,  
7.     v_ename varchar2  
8. )  
9. is  
10.     vename varchar2(32);  
11. begin   
12.     update emp set ename=v_ename where empno=v_empno;  
13.     commit;  
14.     select ename into vename from emp where empno=v_empno;     
15.     dbms_output.put_line('雇员名称：'||vename);      
16. end;  
17.    // 函数  
18.    function emp_get_sal  
19.    (  
20.         v_empno varchar2  
21.    )  
22.    return number is  
23.     vsal number(7,2);  
24.    begin  
25.     select sal into vsal from emp where empno=v_empno;  
26.    return vsal;  
27.    end;  
28. nd;  

 

3、包调用
   在没有创建包规范就创建包体，会失败，要使用包，必须先创建包规范，然后在创建包体。
当要调用包的过程和函数时，在过程和函数的名称前加上包名作为前缀（包名.子程序名称），
而如果要访问其他方案的包时需要在包的名称前加上方案的名称（方案名称.包名.子程序名称）。

（1）调用包函数

Sql代码  

1. SQL> var vsla number  
2. SQL> call emp_pkg.emp_get_sal('7935') into :vsal;  

 

（2）调用包存储过程

Sql代码  

1. SQL> exec emp_pkg.emp_update_ename('7935','helong');