欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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

程序员文章站 2024-02-26 11:19:16
...
一,数据库中存储过程
(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();
				}
		}
	}
相关标签: Oracle SQL