oracle存储过程

oracle存储过程

http://owftc.iteye.com/blog/90032

Java代码 
create or replace procedure GetRecords(name_out out varchar2,age_in in varchar2) as   
begin   
  select NAME into name_out from test where AGE = age_in;   
end;   
 
create or replace procedure insertRecord(UserID in varchar2, UserName in varchar2,UserAge in varchar2) is  
begin  
  insert into test values (UserID, UserName, UserAge);  
end;  

create or replace procedure GetRecords(name_out out varchar2,age_in in varchar2) as
begin
  select NAME into name_out from test where AGE = age_in;
end;

create or replace procedure insertRecord(UserID in varchar2, UserName in varchar2,UserAge in varchar2) is
begin
  insert into test values (UserID, UserName, UserAge);
end;

Java代码 
- 

-

首先，在Oracle中创建了一个名为TEST_SEQ的Sequence对象，SQL语句如下：

Java代码 
create sequence TEST_SEQ   
minvalue 100   
maxvalue 999   
start with 102   
increment by 1   
nocache;  

create sequence TEST_SEQ
minvalue 100
maxvalue 999
start with 102
increment by 1
nocache;

语法应该是比较易懂的，最小最大值分别用minvalue,maxvalue表示，初始值是102（这个数字是动态变化的，我创建的时候设的是100，后因插入了2条数据后就自动增加了2），increment当然就是步长了。在PL/SQL中可以用test_seq.nextval访问下一个序列号，用test_seq.currval访问当前的序列号。

    定义完了Sequence，接下来就是创建一个存储过程InsertRecordWithSequence：
--这次我修改了test表的定义，和前面的示例不同。其中，UserID是PK。
Java代码 
create or replace procedure InsertRecordWithSequence(UserID   out number,UserName in varchar2,UserAge  in number)   
is   
begin insert into test(id, name, age) --插入一条记录，PK值从Sequece获取   
values(test_seq.nextval, UserName, UserAge);   
/*返回PK值。注意Dual表的用法*/   
select test_seq.currval into UserID from dual;      
end InsertRecordWithSequence;  

create or replace procedure InsertRecordWithSequence(UserID   out number,UserName in varchar2,UserAge  in number)
is
begin insert into test(id, name, age) --插入一条记录，PK值从Sequece获取
values(test_seq.nextval, UserName, UserAge);
/*返回PK值。注意Dual表的用法*/
select test_seq.currval into UserID from dual;   
end InsertRecordWithSequence;

为了让存储过程返回结果集，必须定义一个游标变量作为输出参数。这和Sql Server中有着很大的不同！并且还要用到Oracle中“包”（Package）的概念，似乎有点繁琐，但熟悉后也会觉得很方便。
关于“包”的概念，有很多内容可以参考，在此就不赘述了。首先，我创建了一个名为TestPackage的包，包头是这么定义的：
Java代码 
    create or replace package TestPackage is   
    type mycursor is ref cursor; -- 定义游标变量   
     procedure GetRecords(ret_cursor out mycursor); -- 定义过程，用游标变量作为返回参数   
end TestPackage;     
包体是这么定义的：   
create or replace package body TestPackage is   
/*过程体*/   
          procedure GetRecords(ret_cursor out mycursor) as   
          begin   
              open ret_cursor for select * from test;   
          end GetRecords;   
end TestPackage;  

    create or replace package TestPackage is
    type mycursor is ref cursor; -- 定义游标变量
     procedure GetRecords(ret_cursor out mycursor); -- 定义过程，用游标变量作为返回参数
end TestPackage;  
包体是这么定义的：
create or replace package body TestPackage is
/*过程体*/
          procedure GetRecords(ret_cursor out mycursor) as
          begin
              open ret_cursor for select * from test;
          end GetRecords;
end TestPackage;


小结：
    包是Oracle特有的概念，Sql Server中找不到相匹配的东西。在我看来，包有点像VC++的类，包头就是.h文件，包体就是.cpp文件。包头只负责定义，包体则负责具体实现。如果包返回多个游标，则DataReader会按照您向参数集合中添加它们的顺序来访问这些游标，而不是按照它们在过程中出现的顺序来访问。可使用DataReader的NextResult()方法前进到下一个游标。

Java代码 
- 

-

Java代码 
create or replace package TestPackage is   
     type mycursor is ref cursor;   
     procedure UpdateRecords(id_in in number,newName in varchar2,newAge in number);   
     procedure SelectRecords(ret_cursor out mycursor);   
     procedure DeleteRecords(id_in in number);   
     procedure InsertRecords(name_in in varchar2, age_in in number);   
end TestPackage;  

create or replace package TestPackage is
     type mycursor is ref cursor;
     procedure UpdateRecords(id_in in number,newName in varchar2,newAge in number);
     procedure SelectRecords(ret_cursor out mycursor);
     procedure DeleteRecords(id_in in number);
     procedure InsertRecords(name_in in varchar2, age_in in number);
end TestPackage;

包体如下：
Java代码 
create or replace package body TestPackage is  
    procedure UpdateRecords(id_in in number, newName in varchar2, newAge  in number) as  
    begin  
     update test set age = newAge, name = newName where id = id_in;  
    end UpdateRecords;  
 
    procedure SelectRecords(ret_cursor out mycursor) as  
    begin  
       open ret_cursor for select * from test;  
    end SelectRecords;  
 
    procedure DeleteRecords(id_in in number) as  
    begin  
       delete from test where id = id_in;  
    end DeleteRecords;  
    procedure InsertRecords(name_in in varchar2, age_in in number) as  
    begin  
       insert into test values (test_seq.nextval, name_in, age_in);   
--test_seq是一个已建的Sequence对象，请参照前面的示例   
end InsertRecords;  
    end TestPackage;  

create or replace package body TestPackage is
    procedure UpdateRecords(id_in in number, newName in varchar2, newAge  in number) as
    begin
     update test set age = newAge, name = newName where id = id_in;
    end UpdateRecords;

    procedure SelectRecords(ret_cursor out mycursor) as
    begin
       open ret_cursor for select * from test;
    end SelectRecords;

    procedure DeleteRecords(id_in in number) as
    begin
       delete from test where id = id_in;
    end DeleteRecords;
    procedure InsertRecords(name_in in varchar2, age_in in number) as
    begin
       insert into test values (test_seq.nextval, name_in, age_in);
--test_seq是一个已建的Sequence对象，请参照前面的示例
end InsertRecords;
    end TestPackage;



--------------------华丽的分割线-----------------------


http://oraclex.iteye.com/blog/814442

实现存储过程必须先在oracle建立相应的Procedures，如下所示：

Sql代码 
--添加信息--  
create or replace procedure insert_t_test(  
p_id in number,  
p_name in varchar2,  
p_password in varchar2  
) is 
begin 
insert into t_test(id,name,password) values(p_id,p_name,p_password);  
end;  
-------------------------  
--删除信息--  
create or replace procedure del_t_test(  
p_id                 in number,  
x_out_record out number) is 
begin 
 
    delete t_test where id = p_id;  
    x_out_record := 0;  
exception  
    when others then 
        x_out_record := -1;  
end;  
-----------------------------  
--查询所有信息--  
create or replace procedure all_t_test(  
x_out_record out number,  
x_out_cursor out sys_refcursor) is 
begin 
    open x_out_cursor for 
        select * from t_test;  
    x_out_record := 0;  
exception  
    when others then 
        x_out_record := -1;  
end; 

--添加信息--
create or replace procedure insert_t_test(
p_id in number,
p_name in varchar2,
p_password in varchar2
) is
begin
insert into t_test(id,name,password) values(p_id,p_name,p_password);
end;
-------------------------
--删除信息--
create or replace procedure del_t_test(
p_id                 in number,
x_out_record out number) is
begin

    delete t_test where id = p_id;
    x_out_record := 0;
exception
    when others then
        x_out_record := -1;
end;
-----------------------------
--查询所有信息--
create or replace procedure all_t_test(
x_out_record out number,
x_out_cursor out sys_refcursor) is
begin
    open x_out_cursor for
        select * from t_test;
    x_out_record := 0;
exception
    when others then
        x_out_record := -1;
end;

其中的存储过程名字（就是加粗部分）必须要和java代码中的相对应
Java代码如下：
Java代码 
package com.procedure.db;  
 
import java.sql.CallableStatement;  
import java.sql.Connection;  
import java.sql.DriverManager;  
import java.sql.ResultSet;  
import java.sql.SQLException;  
import java.sql.Types;  
 
import oracle.jdbc.OracleTypes;  
 
 
public class ConnDB {  
 
  private String url="jdbc:oracle:thin:@localhost:1521:orcl";  
    
  private String driverClass="oracle.jdbc.driver.OracleDriver";  
    
  private String username="scott";  
    
  private String password="hello";  
    
    
  public    Connection getConn(){  
    Connection conn=null;  
    try {  
      Class.forName(driverClass);  
      conn=DriverManager.getConnection(url,username,password);  
    } catch (ClassNotFoundException e) {  
      e.printStackTrace();  
    } catch (SQLException e) {  
      e.printStackTrace();  
    }  
    return conn;  
  }  
    
  public static void main(String args[]){  
    ConnDB cd=new ConnDB();  
    Connection conn=cd.getConn();  
    /** 
     * 添加 有输入参数    无输出参数 
     */ 
    /*try { 
      CallableStatement call=conn.prepareCall("{call insert_t_test(?,?,?)}"); 
      call.setInt(1, 66); 
      call.setString(2, "小猫"); 
      call.setString(3, "8989"); 
      Boolean b=call.execute(); 
      System.out.println("b="+b); 
    } catch (SQLException e) { 
      e.printStackTrace(); 
    }*/ 
      
    /** 
     * 删除 有输入参数    得到输出参数 
     */ 
    /*try { 
      CallableStatement    call=conn.prepareCall("{call del_t_test(?,?)}"); 
      call.setInt(1, 66); 
      call.registerOutParameter(2, Types.INTEGER); 
      call.execute(); 
      Integer result=call.getInt(2); 
      System.out.println("执行结果为0正常，执行结果为-1不正常"+result); 
    } catch (SQLException e) { 
      e.printStackTrace(); 
    }*/ 
      
    /** 
     * 使用游标查询所有信息 无输入参数 有输出参数 
     */ 
    try {  
      CallableStatement call=conn.prepareCall("{call all_t_test(?,?)}");  
      call.registerOutParameter(1, Types.INTEGER);  
      call.registerOutParameter(2, OracleTypes.CURSOR);  
      call.execute();  
      Integer result=call.getInt(1);  
      ResultSet rs=(ResultSet) call.getObject(2);  
      while(rs.next()){  
        System.out.println(rs.getInt(1));  
        System.out.println(rs.getString(2));  
        System.out.println(rs.getString(3));  
      }  
    } catch (SQLException e) {  
      e.printStackTrace();  
    }  
  }  
} 

package com.procedure.db;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

import oracle.jdbc.OracleTypes;


public class ConnDB {

  private String url="jdbc:oracle:thin:@localhost:1521:orcl";
 
  private String driverClass="oracle.jdbc.driver.OracleDriver";
 
  private String username="scott";
 
  private String password="hello";
 
 
  public    Connection getConn(){
    Connection conn=null;
    try {
      Class.forName(driverClass);
      conn=DriverManager.getConnection(url,username,password);
    } catch (ClassNotFoundException e) {
      e.printStackTrace();
    } catch (SQLException e) {
      e.printStackTrace();
    }
    return conn;
  }
 
  public static void main(String args[]){
    ConnDB cd=new ConnDB();
    Connection conn=cd.getConn();
    /**
     * 添加 有输入参数    无输出参数
     */
    /*try {
      CallableStatement call=conn.prepareCall("{call insert_t_test(?,?,?)}");
      call.setInt(1, 66);
      call.setString(2, "小猫");
      call.setString(3, "8989");
      Boolean b=call.execute();
      System.out.println("b="+b);
    } catch (SQLException e) {
      e.printStackTrace();
    }*/
   
    /**
     * 删除 有输入参数    得到输出参数
     */
    /*try {
      CallableStatement    call=conn.prepareCall("{call del_t_test(?,?)}");
      call.setInt(1, 66);
      call.registerOutParameter(2, Types.INTEGER);
      call.execute();
      Integer result=call.getInt(2);
      System.out.println("执行结果为0正常，执行结果为-1不正常"+result);
    } catch (SQLException e) {
      e.printStackTrace();
    }*/
   
    /**
     * 使用游标查询所有信息 无输入参数 有输出参数
     */
    try {
      CallableStatement call=conn.prepareCall("{call all_t_test(?,?)}");
      call.registerOutParameter(1, Types.INTEGER);
      call.registerOutParameter(2, OracleTypes.CURSOR);
      call.execute();
      Integer result=call.getInt(1);
      ResultSet rs=(ResultSet) call.getObject(2);
      while(rs.next()){
        System.out.println(rs.getInt(1));
        System.out.println(rs.getString(2));
        System.out.println(rs.getString(3));
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }
}


-----------------------------------华丽的分割线------------------------

http://www.cnblogs.com/liliu/archive/2011/06/22/2087546.html

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 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 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;
/