Oracle存储过程创建及调用

在大型数据库系统中，有两个很重要作用的功能，那就是存储过程和触发器。在数据库系统中无论是存储过程还是触发器，都是通过SQL 语句和控制流程语句的集合来完成的。相对来说，数据库系统中的触发器也是一种存储过程。存储过程在数据库中运算时自动生成各种执行方式，因此，大大提高了对其运行时的执行速度。在大型数据库系统如Oracle、SQL Server中都不仅提供了用户自定义存储过程的功能，同时也提供了许多可作为工具进行调用的系统自带存储过程。
   所谓存储过程(Stored Procedure)，就是一组用于完成特定数据库功能的SQL 语句集，该SQL语句集经过编译后存储在数据库系统中。在使用时候，用户通过指定已经定义的存储过程名字并给出相应的存储过程参数来调用并执行它，从而完成一个或一系列的数据库操作。
   由于J2EE体系一般建立大型的企业级应用系统，而一般都配备大型数据库系统如Oracle或者SQL Server，在本文《JAVA与Oracle存储过程》中将介绍JAVA跟Oracle存储过程之间的相互应用跟相互间的各种调用。
   一、JAVA调用Oracle存储过程
   JAVA跟Oracle之间最常用的是JAVA调用Oracle的存储过程，以下简要说明下JAVA如何对Oracle存储过程进行调用。
   Ⅰ、不带输出参数情况
      过程名称为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(); 
}  
}  

   Ⅱ、带输出参数的情况
   过程名称为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);
    //执行Oracle存储过程 
    c.execute(); 
      //得到存储过程的输出参数值并打印出来
      System.out.println (c.getInt(2));   
    conn.close(); 
} 
} 

 

 

Oracle存储过程包含三部分：过程声明，执行过程部分，存储过程异常。

Oracle存储过程可以有无参数存储过程和带参数存储过程。 
一、无参程序过程语法

 

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

        二、带参存储过程实例

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

    三、 带参数存储过程含赋值方式

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

  四、在Oracle中对存储过程的调用
  过程调用方式一

 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;  //过程调用结束
12 

  过程调用方式二

 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(sname=>realname,isal=>realsal,sjob=>realjob);  －－指定值对应变量顺序可变
10       DBMS_OUTPUT.PUT_LINE(REALNAME||'   '||REALJOB);
11 END;  //过程调用结束
12 

 至此，有关ORACLE的基本存储过程以及对Oracle存储过程的调用方式介绍完毕。

出处：http://www.cnblogs.com/chinafine/articles/1776094.html

================================================================================

刚去到新公司几天，昨天老大让我想一下，现在有50多个存储，需要通过输入的参数不同，调用不用的存储过程。

开始的时候，想都没想直接用了if-then-elsif-then-else-end if,后来被说了，太不专业了，因为有五十多个，所以整个版面看上去，相当的没有可观度且相当的不专业。 

后来，经提点，把这个存储过程的名字跟参数名存到一个表中，然后根据传入的参数查找相应的存储过程再调用存储过程即可。回家做了下实验，如下：

create table p_proc

(pid       number(2),

pname          varchar2(20),

pname_class    varchar2(20));  --用于存储存储过程和参数名的表并插入四条数据，

 

insert into p_proc values(1,'A','V1');

insert into p_proc values(2,'B','V2');

insert into p_proc values(3,'C','V3'); 

insert into p_proc values(4,'D','V4');  

四个存储过程的的状况为：

CREATE OR REPLACE PROCEDURE V1(v_para varchar2)

AS

BEGIN

     DBMS_OUTPUT.put_line(v_para);

END;

 

CREATE OR REPLACE PROCEDURE V2(v_para varchar2)

AS

BEGIN

     DBMS_OUTPUT.put_line(v_para);

END;

 

CREATE OR REPLACE PROCEDURE V3(v_para varchar2)

AS

BEGIN

     DBMS_OUTPUT.put_line(v_para);

END;

 

CREATE OR REPLACE PROCEDURE V4(v_para varchar2)

AS

BEGIN

     DBMS_OUTPUT.put_line(v_para);

END; 

如下调用存储过程的存储为（请忽略一些测试输出）：

CREATE OR REPLACE PROCEDURE PRE1(v_para varchar2)

AS

v_pname_class varchar2(20);

v_message varchar2(30);

v_str_proc varchar2(50);

BEGIN

     SELECT pname_class into v_pname_class

     FROM P_PROC WHERE PNAME=v_para;

     BEGIN

     IF SQL%FOUND THEN

        v_message := 'aaa';

     END IF;

     EXCEPTION

              WHEN OTHERS THEN

              v_pname_class := null;

     END;

     dbms_output.put_line(v_pname_class||'abc');

     v_str_proc := 'BEGIN ' ||v_pname_class || '('''|| v_para || '''); end;';  --构造匿名块的执行存储过程

     dbms_output.put_line(v_str_proc);

     dbms_output.put_line(v_message);

     execute immediate v_str_proc;  --主要是执行存储过程

END PRE1;

 

使用一个匿名块调用

BEGIN

     PRE1('A'); --PRE1('B')/PRE1('C')/PRE1('D');

END;

 

上面主要的地方我已经加粗和红色标记了，代码很简单，我就不解释了。

好了，写完了，今天中秋，明天开始国庆，祝大家双节嗨皮~~ 

 出处：oracle中通过传入的参数调用表中存存放的存储过程名