oracle数据库存储过程，带commit,rollback OracleSQL

一，数据库中存储过程
(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();
				}
		}
	}