oracle数据库存储过程,带commit,rollback OracleSQL
程序员文章站
2024-02-26 11:19:16
...
一,数据库中存储过程
(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(); } } }
上一篇: Java微信公众平台开发(11) 微信三大平台的关联
下一篇: Java计算两个程序运行时间的实例