Oracle存储过程

一、 存储过程

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

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

create or replace procedure NoParPro  
 as  //声明  
 ;  
 begin // 执行  
 ;  
 exception//存储过程异常  
 ;  
 end;  

（2）带参存储过程实例

create or replace procedure queryempname(sfindno emp.empno%type)   
as  
   sName emp.ename%type;  
   sjob emp.job%type;  
begin  
       ....  
exception  
       ....  
end;  

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

create or replace procedure runbyparmeters    
    (isal in emp.sal%type,   
     sname out varchar,  
     sjob in out varchar)  
 as   
    icount number;  
 begin  
      select count(*) into icount from emp where sal>isal and job=sjob;  
      if icount=1 then  
        ....  
      else  
       ....  
     end if;  
exception  
     when too_many_rows then  
     DBMS_OUTPUT.PUT_LINE('返回值多于1行');  
     when others then  
     DBMS_OUTPUT.PUT_LINE('在RUNBYPARMETERS过程中出错！');  
end;  

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

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

as //定义(游标一个可以遍历的结果集)   
CURSOR cur_1 IS   
  SELECT area_code,CMCODE,SUM(rmb_amt)/10000 rmb_amt_sn,  
         SUM(usd_amt)/10000 usd_amt_sn   
  FROM BGD_AREA_CM_M_BASE_T   
  WHERE ym >= vs_ym_sn_beg   
       AND ym <= vs_ym_sn_end   
  GROUP BY area_code,CMCODE;   
      
begin //执行（常用For语句遍历游标）       
FOR rec IN cur_1 LOOP   
  UPDATE xxxxxxxxxxx_T   
   SET rmb_amt_sn = rec.rmb_amt_sn,usd_amt_sn = rec.usd_amt_sn   
   WHERE area_code = rec.area_code   
   AND CMCODE = rec.CMCODE   
   AND ym = is_ym;   
END LOOP;  

（5）游标的定义

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

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

（1）过程调用方式一

declare  
      realsal emp.sal%type;  
      realname varchar(40);  
      realjob varchar(40);  
begin   //过程调用开始  
      realsal:=1100;  
      realname:='';  
      realjob:='CLERK';  
      runbyparmeters(realsal,realname,realjob);－－必须按顺序  
      DBMS_OUTPUT.PUT_LINE(REALNAME||'   '||REALJOB);  
END;  //过程调用结束  

（2）过程调用方式二

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

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

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

4、JAVA调用Oracle存储过程

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

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

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

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

二、 函数

1、基本语法规则

create or replace function (Name in type, Name in type, ...)   
    return number   
  is  
    Result number;  
 begin    
    return (Result);  
 end ;  

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

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

3、函数调用：

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

4、与存储过程的区别

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

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

1、创建包规范

create package emp_pkg is  
    procedure emp_update_ename(v_empno varchar2,v_ename varchar2);  
    function emp_get_sal(v_empno varchar2) return number;  
end;  

2、创建包体

create or replace package body emp_pkg  
is  
    // 存储过程  
procedure emp_update_ename  
(  
    v_empno varchar2,  
    v_ename varchar2  
)  
is  
    vename varchar2(32);  
begin   
    update emp set ename=v_ename where empno=v_empno;  
    commit;  
    select ename into vename from emp where empno=v_empno;     
    dbms_output.put_line('雇员名称：'||vename);      
end;  
   // 函数  
   function emp_get_sal  
   (  
        v_empno varchar2  
   )  
   return number is  
    vsal number(7,2);  
   begin  
    select sal into vsal from emp where empno=v_empno;  
   return vsal;  
   end;  
nd;  

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

（1）调用包函数

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

（2）调用包存储过程

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

[plain] view plain copy