Java调用Oracle的增删改查存储过程(供初学者学习)(精)

------创建表 create table t_user ( id number(4 primary key, uname varchar2(20, age number(3 ; ((注意下面的语句结束后须加/才能执行 -----添加的存储过程 create or replace procedure tu_save ( id in number, uname in varchar, age in number as begin insert into t_user(id,uname,age values(id,uname,age; end tu_save; -----删除的存储过程create or replace procedure tu_del ( b_id in number as begin delete from t_user where id = b_id; end tu_del; -----修改的存储过程 create or replace procedure tu_update ( b_id in number, b_uname varchar, b_age number as begin update t_user set uname =

b_uname,age = b_age where id = b_id; end tu_update; -----查询一条数据的存储过程create or replace procedure tu_query ( b_id in number, b_uname out varchar, b_age out number as begin select uname,age into b_uname,b_age from t_user where id = b_id; end tu_query; -----查询所有的存储过程(查询多个数据,步骤1，3,查询一条数据，步骤1，2 1.先创建程序包 create or replace package tu_queryAll as type aa_all is ref cursor; end tu_queryAll; 2.创建查询一条数据的查询 create or replace procedure tu_query1 ( b_id in number, b_all out tu_queryAll.aa_all as begin open b_all for select * from

t_user where id = b_id; end tu_query1; 3.利用程序包创建多查询 create or replace procedure tu_queryAlls ( b_all out tu_queryAll.aa_all as begin open b_all for select * from t_user; end tu_queryAlls; Java调用Oracle存储过程 ---1.创建连接数据库类import java.sql.*; public class UserDao { /** * 创建连接 */ public static Connection getConn( { Connection conn = null; try

{ Class.forName("oracle.jdbc.driver.OracleDriver"; conn =

DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:aptech","scott","tiger"; } catch (Exception e { // TODO Auto-generated catch block e.printStackTrace(; } return conn; } /** * 测试连接是否成功 */ public static void main(String[] args { Connection con = new UserDao(.getConn(; System.out.println(con; } } ---2.操作存储过程 import erDao; import java.sql.*; public class UserDaoImpl extends UserDao { private Connection conn = null; ResultSet res = null; CallableStatement cstm = null; /** * 增加一条数据 * @param id * @param uname * @param age */ public void save(int id,String uname,int age { try { conn = this.getConn(; cstm =

conn.prepareCall("{call tu_save(?,?,?}"; cstm.setInt(1, id; cstm.setString(2, uname; cstm.setInt(3, age; cstm.execute(; } catch (Exception e { // TODO: handle exception

e.printStackTrace(; }finally{ try { cstm.close(; conn.close(;

} catch (Exception e { // TODO: handle exception e.printStackTrace(; } } } /** * 删除一条数据 * @param id */ public void del(int id { try { conn = this.getConn(; cstm = conn.prepareCall("{call tu_del_pro(?}"; cstm.setInt(1, id; cstm.execute(; } catch (Exception e { // TODO: handle exception e.printStackTrace(; }finally{ try { cstm.close(; conn.close(; } catch (Exception e { // TODO: handle exception e.printStackTrace(; } } } /** * 修改一条数据 * @param id * @param uname * @param age */ public void update(int id,String uname,int age { try { conn = this.getConn(; cstm =

conn.prepareCall("{call tu_update(?,?,?}"; cstm.setInt(1, id; cstm.setString(2, uname; cstm.setInt(3, age; cstm.execute(; } catch (Exception e { // TODO: handle exception

e.printStackTrace(; }finally{ try { cstm.close(; conn.close(; } catch (Exception e { // TODO: handle exception e.printStackTrace(; } } } /** * 根据Id查询语句 * @param id */ public void query(int id { try { conn = this.getConn(; cstm = conn.prepareCall("{call tu_query(?,?,?}"; cstm.setInt(1, id; cstm.registerOutParameter(2, Types.VARCHAR; cstm.registerOutParameter(3, Types.INTEGER; cstm.execute(; String name =

cstm.getString(2; int age = cstm.getInt(3; System.out.println(name+","+age; } catch (Exception e { // TODO: handle exception e.printStackTrace(; }finally{ try { cstm.close(; conn.close(; } catch (Exception e { // TODO: handle exception e.printStackTrace(; } } } /** * 根据游标查询一条数据 */ public void query1(int id { try { conn = this.getConn(; cstm = conn.prepareCall("{call tu_query1(?,?}"; cstm.setInt(1, id;

cstm.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR; cstm.execute(; res = (ResultSetcstm.getObject(2;

if(res.next( { System.out.println("id="+res.getInt(1+",uname="+res.getString(2+",age="+ res.getInt(3; } } catch (Exception e { // TODO: handle exception

e.printStackTrace(; }finally{ try { res.close(; cstm.close(; conn.close(; } catch (Exception e { // TODO: handle exception e.printStackTrace(; } } } /** * 查询所有数据 */ public