oracle存储过程_1

[color=red]--添加信息[/color]

[color=blue]create or replace procedure[/color] 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;


[color=red]--删除信息[/color]

[color=blue]create or replace procedure[/color] 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;

[color=red]--查询所有信息[/color]

[color=blue]create or replace procedure[/color] 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