jdbc存储过程调用
1.调用存储过程
首先建立名为pro_query_dept的存储过程
Java代码
CREATE OR REPLACE PROCEDURE pro_query_dept (
p_deptno IN dept.deptno%TYPE,
p_dname OUT dept.dname%TYPE
)
--声明区
AS
--执行区
BEGIN
SELECT dname
INTO p_dname
FROM dept
WHERE deptno = p_deptno;
IF p_dname = 'SALES'
THEN
p_dname := '销售部门';
ELSE
p_dname := '未知部门';
END IF;
DBMS_OUTPUT.put_line (p_dname);
--异常处理
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLCODE || SQLERRM);
END;
测试存储过程：
Sql代码
--测试
DECLARE
v_dname dept.dname%TYPE;
BEGIN
pro_query_dept (30, v_dname);
END;
java类源代码：
Java代码
package com.sun.myjdbc;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Types;
public class TestC {
public static void test1(){
try {
// 加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
String user = "scott";
String password = "tiger";
Connection conn = DriverManager.getConnection(url, user, password);
String sql = "{call pro_query_dept(?,?)}";
// 创建一个过程的分析容器
CallableStatement cst = conn.prepareCall(sql);
// 设置输入参数
cst.setInt(1, 30);
// 定义输出类型
cst.registerOutParameter(2, Types.VARCHAR);
// 执行存储过程
cst.execute();
// 提取存储过程输出
String value = cst.getString(2);
System.out.println(value);
cst.close();
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
public static void main(String[] args) {
test1();
}
}
2.调用函数
建立名为func_query_dept的函数
Java代码
CREATE OR REPLACE FUNCTION func_query_dept (
p_dname OUT dept.dname%TYPE,
p_deptno IN dept.deptno%TYPE
)
RETURN VARCHAR2
--声明区
AS
--执行区
BEGIN
SELECT dname
INTO p_dname
FROM dept
WHERE deptno = p_deptno;
IF p_dname = 'SALES'
THEN
p_dname := '销售部门';
ELSE
p_dname := '未知部门';
END IF;
RETURN 'OK';
--异常处理
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLCODE || SQLERRM);
RETURN 'ERROR';
END;
测试代码：
Java代码
DECLARE
v_dname dept.dname%TYPE;
v_result varchar2(20);
BEGIN
v_result := func_query_dept (v_dname, 30);
dbms_output.put_line(v_result);
END;
java类调用函数
Java代码
public void test2(){
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
String user = "scott";
String password = "tiger";
Connection con = DriverManager.getConnection(url, user, password);
String sql = "{?= call FUNC_QUERY_DEPT(?,?)}";
CallableStatement cst = con.prepareCall(sql);
cst.registerOutParameter(1,Types.VARCHAR);
cst.registerOutParameter(2,Types.VARCHAR);
cst.setInt(3, 30);
cst.execute();
System.out.println(cst.getString(1));
System.out.println(cst.getString(2));
con.close();
}catch(Exception e){
e.printStackTrace();
}
}
3.测试游标
首先建立一个包：
Java代码
CREATE OR REPLACE PACKAGE pack_dept
IS
TYPE cur IS REF CURSOR;
END;
然后有两种方式返回一个游标
方式一：
Java代码
CREATE OR REPLACE PROCEDURE pro_find_dept (my_cur OUT pack_dept.cur)
IS
BEGIN
OPEN my_cur FOR SELECT * FROM dept;
END;
方式二：
Java代码
CREATE OR REPLACE PROCEDURE pro_find_dept (my_cur OUT sys_refcursor)
IS
BEGIN
OPEN my_cur FOR SELECT * FROM dept;
END;
最后我们的java调用类
Java代码
public void test3(){
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@127.0.0.1:1521:ORCL";
String user = "scott";
String password = "tiger";
Connection con = DriverManager.getConnection(url, user, password);
String sql = "{call pro_find_dept(?)}";
CallableStatement cst = con.prepareCall(sql);
cst.registerOutParameter(1, OracleTypes.CURSOR);
cst.execute();
//如何拿到输出的值
ResultSet rs = (ResultSet)cst.getObject(1);
while(rs.next()){
System.out.println(rs.getString(2));
}
rs.close();
cst.close();
con.close();
}catch(Exception e){
e.printStackTrace();
}
}
