Oracle存储过程

3、在Oracle中对存储过程的调用 （1）过程调用方式一 Sql代码 declare realsalemp.sal%type; realname varchar (40); realjob varchar (40); begin //过程调用开始 realsal:=1100; realname:= '' ; realjob:= 'CLERK' ; runbyparmeters(realsal,realname,rea

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');