java连orcale增删改查,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 http://www.doczj.com/doc/631786294b73f242336c5f6e.htmlerDao;

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