Oracle 存储过程

Oracle 存储过程

   简要记录存储过程语法与Java程序的调用方式

　　一 存储过程

　　　　首先，我们建立一个简单的表进行存储过程的测试

      
      

       
       create
       
        
       
       table
       
        
xuesheng(id 
       
       integer
       
       , xing_ming 
       
       varchar2
       
       (
       
       25
       
       ), yu_wen 
       
       number
       
       , shu_xue 
       
       number
       
       );


       
       insert
       
        
       
       into
       
        xuesheng 
       
       values
       
       (
       
       1
       
       ,
       
       ’
       
       zhangsan
       
       ’
       
       ,
       
       80
       
       ,
       
       90
       
       )

       
       insert
       
        
       
       into
       
        xuesheng 
       
       values
       
       (
       
       2
       
       ,
       
       ’
       
       lisi
       
       ’
       
       ,
       
       85
       
       ,
       
       87
       
       )
      
      

1）无返回值的存储过程

      
      

       
       create
       
        
       
       or
       
        
       
       replace
       
        
       
       procedure
       
        xs_proc_no 
       
       is
       
       

       
       begin
       
       
  
       
       insert
       
        
       
       into
       
        xuesheng 
       
       values
       
        (
       
       3
       
       , 
       
       ’
       
       wangwu
       
       ’
       
       , 
       
       90
       
       , 
       
       90
       
       );
  
       
       commit
       
       ;

       
       end
       
        xs_proc_no;
      
      

2）有单个数据值返回的存储过程

      
      

       
       create
       
        
       
       or
       
        
       
       replace
       
        
       
       procedure
       
        xs_proc(temp_name 
       
       in
       
        
       
       varchar2
       
       ,
                                    temp_num  out 
       
       number
       
       ) 
       
       is
       
       
  num_1 
       
       number
       
       ;
  num_2 
       
       number
       
       ;

       
       begin
       
       
  
       
       select
       
        yu_wen, shu_xue
    
       
       into
       
        num_1, num_2
    
       
       from
       
        xuesheng
   
       
       where
       
        xing_ming 
       
       =
       
        temp_name;
  
       
       --
       
       dbms_output.put_line(num_1 + num_2);
       
       

       
         temp_num :
       
       =
       
        num_1 
       
       +
       
        num_2;

       
       end
       
       ;
      
      

其中，以上两种与sql server基本类似，而对于返回数据集时，上述方法则不能满足我们的要求。在Oracle中，一般使用ref cursor来返回数据集。示例代码如下：

3）有返回值的存储过程(列表返回)

首先，建立我们自己的包。并定义包中的一个自定义ref cursor

      
      

       
       create
       
        
       
       or
       
        
       
       replace
       
        package mypackage 
       
       as
       
       
  type my_cursor 
       
       is
       
        ref 
       
       cursor
       
       ;

       
       end
       
        mypackage;
      
      

在定义了ref cursor后，可以书写我们的程序代码

      
      

       
       create
       
        
       
       or
       
        
       
       replace
       
        
       
       procedure
       
        xs_proc_list(shuxue   
       
       in
       
        
       
       number
       
       ,
                                         p_cursor out mypackage.my_cursor) 
       
       is
       
       

       
       begin
       
       
  
       
       open
       
        p_cursor 
       
       for
       
       
    
       
       select
       
        
       
       *
       
        
       
       from
       
        xuesheng 
       
       where
       
        shu_xue 
       
       >
       
        shuxue;

       
       end
       
        xs_proc_list;
      
      

 二、程序调用

在本节中，我们使用java语言调用存储过程。其中，关键是使用CallableStatement这个对象，代码如下：

?

String oracleDriverName = "oracle.jdbc.driver.OracleDriver" ;            // 以下使用的Test就是Oracle里的表空间          String oracleUrlToConnect = "jdbc:oracle:thin:@127.0.0.1:1521:orcl" ;          Connection myConnection = null ;          try {              Class.forName(oracleDriverName);          } catch (ClassNotFoundException ex) {              ex.printStackTrace();          }          try {              myConnection = DriverManager.getConnection(oracleUrlToConnect,                      "xxxx" , "xxxx" ); //此处为数据库用户名与密码            } catch (Exception ex) {              ex.printStackTrace();          }          try {                            CallableStatement proc= null ;              proc=myConnection.prepareCall( "{call xs_proc(?,?)}" );              proc.setString( 1 , "zhangsan" );              proc.registerOutParameter( 2 , Types.NUMERIC);              proc.execute();              String teststring=proc.getString( 2 );              System.out.println(teststring);            } catch (Exception ex) {              ex.printStackTrace();          }

对于列表返回值的存储过程，在上述代码中做简单修改。如下

      
      

       
       CallableStatement proc
       
       =
       
       null
       
       ;
            proc
       
       =
       
       myConnection.prepareCall(
       
       "
       
       {call getdcsj(?,?,?,?,?)}
       
       "
       
       );
            proc.setString(
       
       1
       
       , strDate);
            proc.setString(
       
       2
       
       , jzbh);
            proc.registerOutParameter(
       
       3
       
       , Types.NUMERIC);
            proc.registerOutParameter(
       
       4
       
       , OracleTypes.CURSOR);
            proc.registerOutParameter(
       
       5
       
       , OracleTypes.CURSOR);
            proc.execute();
            ResultSet rs
       
       =
       
       null
       
       ;
            
       
       int
       
        total_number
       
       =
       
       proc.getInt(
       
       3
       
       );
            rs
       
       =
       
       (ResultSet)proc.getObject(
       
       4
       
       );
      
      

上述存储过程修改完毕。另外，一个复杂的工程项目中的例子：查询一段数据中间隔不超过十分钟且连续超过100条的数据。即上述代码所调用的getdcsj存储过程

?

create or replace procedure getDcsj(var_flag     in varchar2,                                      var_jzbh     in varchar2,                                      number_total out number,                                      var_cursor_a out mypackage.my_cursor,                                      var_cursor_b out mypackage.my_cursor) is    total number;    cursor cur is      select sj, flag        from d_dcsj       where jzbh = var_jzbh       order by sj desc         for update ;    last_time date ; begin    for cur1 in cur loop      if last_time is null or cur1.sj >= last_time - 10 / 60 / 24 then        update d_dcsj set flag = var_flag where current of cur;        last_time := cur1.sj;      else        select count (*) into total from d_dcsj where flag = var_flag;        dbms_output.put_line(total);        if total < 100 then          update d_dcsj set flag = null where flag = var_flag;          last_time := null ;          update d_dcsj set flag = var_flag where current of cur;        else          open var_cursor_a for            select *              from d_dcsj             where flag = var_flag               and jzbh = var_jzbh               and zh = 'A'             order by sj desc ;          number_total := total;          open var_cursor_b for            select *              from d_dcsj             where flag = var_flag               and jzbh = var_jzbh               and zh = 'B'             order by sj desc ;          number_total := total;          exit;        end if;      end if;    end loop;    select count (*) into total from d_dcsj where flag = var_flag;    dbms_output.put_line(total);    if total < 100 then      open var_cursor_a for        select * from d_dcsj where zh = 'C' ;      open var_cursor_b for        select * from d_dcsj where zh = 'C' ;    else      open var_cursor_a for        select *          from d_dcsj         where flag = var_flag           and jzbh = var_jzbh           and zh = 'A'         order by sj desc ;      number_total := total;      open var_cursor_b for        select *          from d_dcsj         where flag = var_flag           and jzbh = var_jzbh           and zh = 'B'         order by sj desc ;      number_total := total;    end if;    commit ; end ; /