Oracle 存储过程

来源：互联网发布：阿里云 cdn节点数量编辑：程序博客网时间：2024/06/15 23:31

简要记录存储过程语法与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 isbegin  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) isbegin  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 proceduregetDcsj(var_flag    invarchar2, 
                                    var_jzbh    invarchar2, 
                                    number_totaloutnumber, 
                                    var_cursor_aoutmypackage.my_cursor,
                                    var_cursor_boutmypackage.my_cursor)is
  total number;
  cursorcuris
    selectsj, flag
      fromd_dcsj
     wherejzbh = var_jzbh
     orderbysj desc
       forupdate;
  last_timedate;
begin
  forcur1in cur loop 
    if last_timeisnull or cur1.sj >= last_time - 10 / 60 / 24 then
      updated_dcsjset flag = var_flagwhere currentof cur; 
      last_time := cur1.sj;
    else
      selectcount(*)intototal from d_dcsj whereflag = var_flag;
      dbms_output.put_line(total);
      if total < 100then
        updated_dcsjset flag = null whereflag = var_flag;
        last_time :=null;
        updated_dcsjset flag = var_flagwhere currentof cur; 
      else
        openvar_cursor_afor
          select*
            fromd_dcsj
           whereflag = var_flag
             andjzbh = var_jzbh
             andzh ='A'
           orderbysj desc;
        number_total := total;
        openvar_cursor_bfor
          select*
            fromd_dcsj
           whereflag = var_flag
             andjzbh = var_jzbh
             andzh ='B'
           orderbysj desc;
        number_total := total;
        exit;
      endif;
    endif;
  endloop;
  selectcount(*)intototal from d_dcsj whereflag = var_flag;
  dbms_output.put_line(total);
  if total < 100then
    openvar_cursor_afor
      select*from d_dcsj where zh ='C';
    openvar_cursor_bfor
      select*from d_dcsj where zh ='C';
  else
    openvar_cursor_afor
      select*
        fromd_dcsj
       whereflag = var_flag
         andjzbh = var_jzbh
         andzh ='A'
       orderbysj desc;
    number_total := total;
    openvar_cursor_bfor
      select*
        fromd_dcsj
       whereflag = var_flag
         andjzbh = var_jzbh
         andzh ='B'
       orderbysj desc;
    number_total := total;
  endif;
  commit;
end;
/