Oracle存储过程详解

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

--添加信息--
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代码如下：

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();
    }
  }
}

 

 

评论

1 楼 754731046 2012-08-10  

很适合对初学者适用，谢谢