本文为学习笔记
Test.java:
package bing.oracleprocedure;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Types;
import oracle.jdbc.OracleTypes;
/**
* 练习存储过程的调用
* @author bing
* @version 2011-07-09
*/
public class Test {
public static void test(){
System.out.println("=====Test.test=====");
Connection conn = new Conn().getConnection(); // 获得数据连接对象
CallableStatement cstmt = null;
ResultSet rs = null;
try{
/*
-- 创建练习用的表t_test
create table t_test(
t_id number(4) not null,
t_name varchar2(20),
t_msg varchar2(100)
);
alter table t_test add constraint pk_t_test primary key(t_id);
-- 编写练习用的过程up_insert_test
-- 插入数据到表t_test
create or replace procedure up_insert_test(v_id in number, v_name in varchar2, v_msg in varchar2)
is
begin
insert into t_test(t_id,t_name,t_msg) values(v_id,v_name,v_msg);
end up_insert_test;
/
*/
// 过程调用,无返回值
cstmt = conn.prepareCall("call up_insert_test(?,?,?)");
cstmt.setInt(1, 1);
cstmt.setString(2, "bing");
cstmt.setString(3, "super man");
cstmt.execute();
cstmt.setInt(1, 2);
cstmt.setString(2, "admin");
cstmt.setString(3, "a worker");
cstmt.execute();
cstmt.setInt(1, 3);
cstmt.setString(2, "user");
cstmt.setString(3, "a user");
cstmt.execute();
System.out.println("插入成功");
/*
-- 编写练习用的存储过程up_select_test_1
-- 输入id,输出name,查询t_test中是单条记录
create or replace procedure up_select_test_1
(v_id in number, v_name out varchar2)
is
begin
select t_name into v_name from t_test where t_id = v_id;
end up_select_test_1;
/
*/
// 过程调用,返回单条记录
// oracle过程没有返回值,所有返回值都是通过out参数来替代的
cstmt = conn.prepareCall("call up_select_test_1(?,?)");
cstmt.setInt(1, 1);
cstmt.registerOutParameter(2, Types.VARCHAR);// 注册out参数,注意序号对应过程的参数序号
cstmt.execute();
String name = cstmt.getString(2);// 获取out参数,注意序号对应过程的参数序号
System.out.println("查询成功");
System.out.println("name = " + name);
/*
-- 编写包upk_select_test,为存储过程up_select_test_2准备 --
create or replace package upk_select_test
as type uc_test is ref cursor;
end upk_select_test;
/
-- 编写存储过程up_select_test_2
-- 查询表t_test中的所有记录
create or replace procedure up_select_test_2
(uc_result out upk_select_test.uc_test)
is
begin
open uc_result for select * from t_test;
end up_select_test_2;
/
*/
// 过程调用,返回多条记录
// 集合不能用一般的参数,必须要用pagkage,从上面的注释可以看到游标作为out参数,过程返回的是一个游标
cstmt = conn.prepareCall("call up_select_test_2(?)");
cstmt.registerOutParameter(1, OracleTypes.CURSOR);
cstmt.execute();
rs = (ResultSet) cstmt.getObject(1);
System.out.println("查询成功");
while(rs.next()){
System.out.println("id = " + rs.getString(1)
+ " name = " + rs.getString(2)
+ " msg = " + rs.getString(3));
}
rs.close();
cstmt.close();
conn.close();
}catch(Exception e){
System.out.println("=====Test.test=====\n操作失败");
e.printStackTrace();
}
}
public static void main(String[] args) {
new Test().test();
/* 控制台输出:
=====Test.test=====
插入成功
查询成功
name = bing
查询成功
id = 3 name = user msg = a user
id = 1 name = bing msg = super man
id = 2 name = admin msg = a worker
*/
}
}
Conn.java:
package bing.oracleprocedure;
import java.sql.Connection;
import java.sql.DriverManager;
/**
* 数据库连接类
* @author bing
* @version 2011-07-09
*
*/
public class Conn {
private static String DRIVER = "oracle.jdbc.driver.OracleDriver";
private static String URL = "jdbc:oracle:thin:@127.0.0.1:1521:oracledbtest";
private static String USER = "bing";
private static String PASSWORD = "bing";
private Connection conn = null;
/**
* 获得连接对象
* @return 连接对象
*/
public Connection getConnection(){
try{
if(conn==null||conn.isClosed()){
Class.forName(DRIVER);
conn = DriverManager.getConnection(URL, USER, PASSWORD);
}
}catch(Exception e){
e.printStackTrace();
}
return conn;
}
}