Java調(diào)用存儲(chǔ)過程
* 調(diào)用存儲(chǔ)過程和存儲(chǔ)函數(shù)前過程和函數(shù)都需要先編譯成功
* 調(diào)用存儲(chǔ)過程和存儲(chǔ)函數(shù)都是用CallableStatement
* 調(diào)用存儲(chǔ)過程的sql語法格式：{call <procedure-name>[(<arg1>,<arg2>,...)]}
* 調(diào)用存儲(chǔ)函數(shù)的sql語法格式：{?=call <procedure-name>[(<arg1>,<arg2>,...)]}
* ?：表示是返回值，且只有一個(gè)，存儲(chǔ)函數(shù)只能有一個(gè)返回值
測(cè)試調(diào)用存儲(chǔ)過程
-- 存儲(chǔ)過程返回多個(gè)值
-- 查詢某個(gè)員工的姓名，職位和月薪
create or replace procedure queryEmpInfo(eno in number,
pename out varchar2,
pjob out varchar2,
psal out number)
as
begin
select ename,empjob,sal into pename,pjob,psal from emp where empno = eno;
end;
如上存儲(chǔ)過程在Java調(diào)用時(shí)只需看創(chuàng)建語句就可以了，存儲(chǔ)函數(shù)也是一樣
@Test
public void testProcedures() {
// {call <procedure-name>[<arg1>,<arg2>,...]}
String sql = "{call queryEmpInfo(?,?,?,?)}";
Connection conn = null;
CallableStatement call = null;
try {
conn = JDBCUtils.getConnection();
call = conn.prepareCall(sql);
// 對(duì)于in參數(shù)，賦值
call.setInt(1, 7839);// 1:表示第一個(gè)？號(hào)
// 對(duì)于out參數(shù)，聲明
call.registerOutParameter(2, OracleTypes.VARCHAR);
call.registerOutParameter(3, OracleTypes.VARCHAR);
call.registerOutParameter(4, OracleTypes.NUMBER);
// 執(zhí)行調(diào)用
call.execute();
// 調(diào)用完就有返回值了，取出返回值
String name = call.getString(2);
String job = call.getString(3);
double sal = call.getDouble(4);
System.out.println(name + "\t" + job + "\t" + sal);
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.release(conn, call, null);
}
}
/**
* 測(cè)試調(diào)用存儲(chǔ)函數(shù)
* -- 存儲(chǔ)函數(shù)
-- 查詢某個(gè)元的年收入
create or replace function queryEmpTotalSal(eno in number)
return number -- 定義返回類型
as
-- 月薪和獎(jiǎng)金
psal emp.sal%type;
pcomm emp.comm%type;
begin
-- 得到月薪和獎(jiǎng)金
select sal,comm into psal,pcomm from emp where empno = eno;
-- 返回年收入
return psal * 12 + nvl(pcomm,0);-- 獎(jiǎng)金可能為null
end;
*/
@Test
public void testFunction() {
// {?=call <procedure-name>[(<arg1>,<arg2>,...)]}
String sql = "{?=call queryEempTotalSal(?)}";
// 這里第一個(gè) ? 就相當(dāng)于 out 參數(shù)，第二個(gè) ? 就相當(dāng)于 in 參數(shù)
Connection conn = null;
CallableStatement call = null;
try {
conn = JDBCUtils.getConnection();
call = conn.prepareCall(sql);
// 對(duì)于out參數(shù)，聲明
call.registerOutParameter(1, OracleTypes.NUMBER);
// 對(duì)于in參數(shù)，賦值
call.setInt(2, 7839);
// 執(zhí)行調(diào)用
call.execute();
// 取出返回結(jié)果
double totalSal = call.getDouble(1);
System.out.println("7839號(hào)員工的年收入為：" + totalSal);
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.release(conn, call, null);
}
/**
測(cè)試返回一個(gè)list集合，使用cursor作為out參數(shù)
例：查詢某個(gè)部門所有員工的所有信息
*/
@Test
public void testCursor() {
// 此查詢其實(shí)就是調(diào)用存儲(chǔ)過程，因此語法是一樣。{call <procedure-name> [(<arg1>,<arg2>,...]}
// 但是要加上調(diào)用方法所在的包名，如下
String sql = "{call testOutByCursor.queryEmpList(?,?)}";
Connection conn = null;
CallableStatement call = null;
ResultSet resultSet = null;
try {
conn = JDBCUtils.getConnection();
call = conn.prepareCall(sql);
// 賦值，輸入查詢的部門
call.setInt(1, 10);// 查10號(hào)部門
// 申明out參數(shù)
call.registerOutParameter(2, OracleTypes.CURSOR);
// 執(zhí)行
call.execute();
// 得到結(jié)果,此時(shí)要將call轉(zhuǎn)成Oracle的CallableStatement
resultSet = ((OracleCallableStatement) call).getCursor(2);
while (resultSet.next()) {
int empNo = resultSet.getInt("empno");
String name = resultSet.getString("ename");
String job = resultSet.getString("empjob");
int mgr = resultSet.getInt("mgr");
Date hireDate = resultSet.getDate("hiredate");
double sal = resultSet.getDouble("sal");
double comm = resultSet.getDouble("comm");
int deptno = resultSet.getInt("deptno");
System.err.println(empNo + " " + name + " " + job + " " + mgr
+ " " + hireDate.toString() + " " + sal + " " + comm
+ " " + deptno + " ");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.release(conn, call, resultSet);
}
}
