一,数据库中存储过程
(1)建立包
(2)存储过程主体
二.程序中调用
(1)建立包
create or replace package t_allpackage is
type mycursor is ref cursor;
end t_allpackage;
(2)存储过程主体
create or replace procedure tuser_all(user_no in varchar2,mycur out t_allpackage.mycursor,myid in number)
as
begin
open mycur for select * from tuserinfo where userno=user_no;
begin
insert into student(userid,username) values('6','回家');
insert into my(username)values('456');
if myid>0 then
commit ;
else
rollback;
end if;
exception
when others then
rollback;
end;
end tuser_all;
二.程序中调用
public void callAll(){
Conn getcon=new Conn();
Connection conn = null;
try{
conn=getcon.getConnection();
String sql="{call tuser_all(?,?,?)}";
CallableStatement call=conn.prepareCall(sql);
call.setString(1, "00059905");
call.registerOutParameter(2, OracleTypes.CURSOR);
call.setInt(3, 1);
call.execute();
ResultSet rs=(ResultSet) call.getObject(2);
if(rs.next()){
System.out.println(rs.getString(1)+"_"+rs.getString(2));
}
}catch(Exception e){
e.printStackTrace();
}finally{
try {
if(conn!=null)
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}