oracle存储过程_1

--添加信息

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)

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;

-----------------------------------------
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://oraclex.iteye.com/blog/814442