一，数据库中存储过程

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

}

}

}