本文主要是總結 如何實作 JDBC調用Oracle的存儲過程,從以下情況分别介紹:
[1]、隻有輸入IN參數,沒有輸出OUT參數
[2]、既有輸入IN參數,也有輸出OUT參數,輸出是簡單值(非清單)
[3]、既有輸入IN參數,也有輸出OUT參數,輸出是清單
[4]、輸入輸出參數是同一個(IN OUT)
[5]、存儲過程中 使用 truncate 截斷表中的資料
【準備工作】
建立一個測試表TMP_MICHAEL ,并插入資料,SQL如下:
create table TMP_MICHAEL
(
USER_ID VARCHAR2(20),
USER_NAME VARCHAR2(10),
SALARY NUMBER(8,2),
OTHER_INFO VARCHAR2(100)
)
insert into TMP_MICHAEL (USER_ID, USER_NAME, SALARY, OTHER_INFO)
values ('michael', 'Michael', 5000, 'http://sjsky.iteye.com');
insert into TMP_MICHAEL (USER_ID, USER_NAME, SALARY, OTHER_INFO)
values ('zhangsan', '張三', 10000, null);
insert into TMP_MICHAEL (USER_ID, USER_NAME, SALARY, OTHER_INFO)
values ('aoi_sola', '蒼井空', 99999.99, 'twitter account');
insert into TMP_MICHAEL (USER_ID, USER_NAME, SALARY, OTHER_INFO)
values ('李四', '李四', 2500, null);
Oracle jdbc 常量:
private final static String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
private final static String DB_CONNECTION = "jdbc:oracle:thin:@127.0.0.1:1521:Ora11g";
private final static String DB_NAME = "mytest";
private final static String DB_PWd = "111111";
[一]、隻有輸入IN參數,沒有輸出OUT參數
CREATE OR REPLACE PROCEDURE TEST_MICHAEL_NOOUT(P_USERID IN VARCHAR2,
P_USERNAME IN VARCHAR2,
P_SALARY IN NUMBER,
P_OTHERINFO IN VARCHAR2) IS
BEGIN
INSERT INTO TMP_MICHAEL
(USER_ID, USER_NAME, SALARY, OTHER_INFO)
VALUES
(P_USERID, P_USERNAME, P_SALARY, P_OTHERINFO);
END TEST_MICHAEL_NOOUT;
調用代碼如下:
/**
* 測試調用存儲過程:無傳回值
* @blog http://sjsky.iteye.com
* @author Michael
* @throws Exception
*/
public static void testProcNoOut() throws Exception {
System.out.println("------- start 測試調用存儲過程:無傳回值");
Connection conn = null;
CallableStatement callStmt = null;
try {
Class.forName(DB_DRIVER);
conn = DriverManager.getConnection(DB_CONNECTION, DB_NAME, DB_PWd);
// 存儲過程 TEST_MICHAEL_NOOUT 其實是向資料庫插入一條資料
callStmt = conn.prepareCall("{call TEST_MICHAEL_NOOUT(?,?,?,?)}");
// 參數index從1開始,依次 1,2,3...
callStmt.setString(1, "jdbc");
callStmt.setString(2, "JDBC");
callStmt.setDouble(3, 8000.00);
callStmt.setString(4, "http://sjsky.iteye.com");
callStmt.execute();
System.out.println("------- Test End.");
} catch (Exception e) {
e.printStackTrace(System.out);
} finally {
if (null != callStmt) {
callStmt.close();
}
if (null != conn) {
conn.close();
}
}
}
[二]、既有輸入IN參數,也有輸出OUT參數,輸出是簡單值(非清單)
CREATE OR REPLACE PROCEDURE TEST_MICHAEL(P_USERID IN VARCHAR2,
P_SALARY IN NUMBER,
P_COUNT OUT NUMBER) IS
V_SALARY NUMBER := P_SALARY;
BEGIN
IF V_SALARY IS NULL THEN
V_SALARY := 0;
END IF;
IF P_USERID IS NULL THEN
SELECT COUNT(*)
INTO P_COUNT
FROM TMP_MICHAEL T
WHERE T.SALARY >= V_SALARY;
ELSE
SELECT COUNT(*)
INTO P_COUNT
FROM TMP_MICHAEL T
WHERE T.SALARY >= V_SALARY
AND T.USER_ID LIKE '%' || P_USERID || '%';
END IF;
DBMS_OUTPUT.PUT_LINE('v_count=:' || P_COUNT);
END TEST_MICHAEL;
調用程式如下
/**
* 測試調用存儲過程:傳回值是簡單值非清單
* @blog http://sjsky.iteye.com
* @author Michael
* @throws Exception
*/
public static void testProcOutSimple() throws Exception {
System.out.println("------- start 測試調用存儲過程:傳回值是簡單值非清單");
Connection conn = null;
CallableStatement stmt = null;
try {
Class.forName(DB_DRIVER);
conn = DriverManager.getConnection(DB_CONNECTION, DB_NAME, DB_PWd);
stmt = conn.prepareCall("{call TEST_MICHAEL(?,?,?)}");
stmt.setString(1, "");
stmt.setDouble(2, 3000);
// out 注冊的index 和取值時要對應
stmt.registerOutParameter(3, Types.INTEGER);
stmt.execute();
// getXxx(index)中的index 需要和上面registerOutParameter的index對應
int i = stmt.getInt(3);
System.out.println("符号條件的查詢結果 count := " + i);
System.out.println("------- Test End.");
} catch (Exception e) {
e.printStackTrace(System.out);
} finally {
if (null != stmt) {
stmt.close();
}
if (null != conn) {
conn.close();
}
}
}
測試程式就是查詢薪水3000以上人員的數量 ,運作結果如下:
------- start 測試調用存儲過程:傳回值是簡單值非清單
符号條件的查詢結果 count := 4
------- Test End.
[三]、既有輸入IN參數,也有輸出OUT參數,輸出是清單
首先需要建立PACKAGE TEST_PKG_CURSOR 的SQL如下:
首先需要建立PACKAGE TEST_PKG_CURSOR 的SQL如下:
Sql代碼
CREATE OR REPLACE PACKAGE TEST_PKG_CURSOR IS
-- Author : MICHAEL http://sjsky.iteye.com
TYPE TEST_CURSOR IS REF CURSOR;
END TEST_PKG_CURSOR;
再建立存儲過程 TEST_P_OUTRS 的SQL如下:
Sql代碼
CREATE OR REPLACE PROCEDURE TEST_P_OUTRS(P_SALARY IN NUMBER,
P_OUTRS OUT TEST_PKG_CURSOR.TEST_CURSOR) IS
V_SALARY NUMBER := P_SALARY;
BEGIN
IF P_SALARY IS NULL THEN
V_SALARY := 0;
END IF;
OPEN P_OUTRS FOR
SELECT * FROM TMP_MICHAEL T WHERE T.SALARY > V_SALARY;
END TEST_P_OUTRS;
調用存儲過程的代碼如下:
/**
* 測試調用存儲過程:有傳回值且傳回值為清單的
* @blog http://sjsky.iteye.com
* @author Michael
* @throws Exception
*/
public static void testProcOutRs() throws Exception {
System.out.println("------- start 測試調用存儲過程:有傳回值且傳回值為清單的");
Connection conn = null;
CallableStatement stmt = null;
ResultSet rs = null;
try {
Class.forName(DB_DRIVER);
conn = DriverManager.getConnection(DB_CONNECTION, DB_NAME, DB_PWd);
stmt = conn.prepareCall("{call TEST_P_OUTRS(?,?)}");
stmt.setDouble(1, 3000);
stmt.registerOutParameter(2, OracleTypes.CURSOR);
stmt.execute();
// getXxx(index)中的index 需要和上面registerOutParameter的index對應
rs = (ResultSet) stmt.getObject(2);
// 擷取列名及類型
int colunmCount = rs.getMetaData().getColumnCount();
String[] colNameArr = new String[colunmCount];
String[] colTypeArr = new String[colunmCount];
for (int i = 0; i < colunmCount; i++) {
colNameArr[i] = rs.getMetaData().getColumnName(i + 1);
colTypeArr[i] = rs.getMetaData().getColumnTypeName(i + 1);
System.out.print(colNameArr[i] + "(" + colTypeArr[i] + ")"
+ " | ");
}
System.out.println();
while (rs.next()) {
StringBuffer sb = new StringBuffer();
for (int i = 0; i < colunmCount; i++) {
sb.append(rs.getString(i + 1) + " | ");
}
System.out.println(sb);
}
System.out.println("------- Test Proc Out is ResultSet end. ");
} catch (Exception e) {
e.printStackTrace(System.out);
} finally {
if (null != rs) {
rs.close();
}
if (null != stmt) {
stmt.close();
}
if (null != conn) {
conn.close();
}
}
}
運作結果如下:
------- start 測試調用存儲過程:有傳回值且傳回值為清單的
USER_ID(VARCHAR2) | USER_NAME(VARCHAR2) | SALARY(NUMBER) | OTHER_INFO(VARCHAR2) |
michael | Michael | 5000 | null |
zhangsan | 張三 | 10000 | null |
aoi_sola | 蒼井空 | 99999.99 | null |
jdbc | JDBC | 8000 | http://sjsky.iteye.com |
------- Test Proc Out is ResultSet end.
[四]、輸入輸出參數是同一個(IN OUT)
CREATE OR REPLACE PROCEDURE TEST_P_INOUT(P_USERID IN VARCHAR2,
P_NUM IN OUT NUMBER) IS
V_COUNT NUMBER;
V_SALARY NUMBER := P_NUM;
BEGIN
IF V_SALARY IS NULL THEN
V_SALARY := 0;
END IF;
SELECT COUNT(*)
INTO V_COUNT
FROM TMP_MICHAEL
WHERE USER_ID LIKE '%' || P_USERID || '%'
AND SALARY >= V_SALARY;
P_NUM := V_COUNT;
END TEST_P_INOUT;
調用存儲過程的代碼:
/**
* 測試調用存儲過程: INOUT同一個參數:
* @blog http://sjsky.iteye.com
* @author Michael
* @throws Exception
*/
public static void testProcInOut() throws Exception {
System.out.println("------- start 測試調用存儲過程:INOUT同一個參數");
Connection conn = null;
CallableStatement stmt = null;
try {
Class.forName(DB_DRIVER);
conn = DriverManager.getConnection(DB_CONNECTION, DB_NAME, DB_PWd);
stmt = conn.prepareCall("{call TEST_P_INOUT(?,?)}");
stmt.setString(1, "michael");
stmt.setDouble(2, 3000);
// 注意此次注冊out 的index 和上面的in 參數index 相同
stmt.registerOutParameter(2, Types.INTEGER);
stmt.execute();
// getXxx(index)中的index 需要和上面registerOutParameter的index對應
int count = stmt.getInt(2);
System.out.println("符号條件的查詢結果 count := " + count);
System.out.println("------- Test End.");
} catch (Exception e) {
e.printStackTrace(System.out);
} finally {
if (null != stmt) {
stmt.close();
}
if (null != conn) {
conn.close();
}
}
}
運作結果如下:
------- start 測試調用存儲過程:INOUT同一個參數
符号條件的查詢結果 count := 1
------- Test End.
[五] 存儲過程中使用 truncate 清空表中的資料
create or replace procedure PROC_INSERT_BLDAREN(rownums in number) is
begin
EXECUTE IMMEDIATE 'TRUNCATE TABLE BI_BAOLIAO_DAREN';
insert into BI_BAOLIAO_DAREN (ID,USERID,USERNAME,BAOLIAONUM,CREDITS) select bi_baoliao_sequence.nextval,bl.* from (select b.userid,b.username,count(b.id),sum(b.credits) credits from bi_baoliao b group by b.userid,b.username order by credits desc) bl where rownum <=rownums;
end PROC_INSERT_BLDAREN;
java 調用
/**
* 使用 truncate 先清空表中的資料
* 然後 插入資料
*/
public static boolean updateData1(int rownum){
boolean result=true;
Connection conn=null;
CallableStatement cs=null;
try {
Date stime=new Date();
conn=DBConnection.getConnection();
cs=conn.prepareCall("{call PROC_INSERT_BLDAREN(?)}");
cs.setInt(1, rownum);
result=cs.execute();
Date etime=new Date();
System.out.println(etime.getTime()-stime.getTime());
}catch(Exception e){
e.printStackTrace();
}finally{
DBConnection.cleanUp(null, null, cs, null);
}
return result;
}