文件jdbc调用存储过程游标

1、1.JDBC调用存储过程CREATE OR REPLACE PROCEDURE pro_query_dept ( p_deptno IN dept.deptno%TYPE, p_dname OUT dept.dname%TYPE) ISBEGIN 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); END; public

2、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 = admin123; Connection conn = DriverManager.getConnection(url, user, password); String sql = call pro_query_dept(?,?); / 创建一个过程的分析容器

3、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(); 2.调用函数CRE

4、ATE OR REPLACE FUNCTION func_query_dept (p_dname OUT dept.dname%TYPE, p_deptno IN dept.deptno%TYPE ) RETURN VARCHAR2 ISBEGIN 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; END; public void test2() try Cla

5、ss.forName(oracle.jdbc.driver.OracleDriver); String url = jdbc:oracle:thin:127.0.0.1:1521:orcl; String user = scott; String password = admin123; Connection con = DriverManager.getConnection(url, user, password); String sql = ?= call FUNC_QUERY_DEPT(?,?); CallableStatement cst = con.prepareCall(sql);

6、 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.测试游标-首先建立一个包：CREATE OR REPLACE PACKAGE pack_dept I

7、S TYPE cur IS REF CURSOR; END; -然后有两种方式返回一个游标-方式一：CREATE OR REPLACE PROCEDURE pro_find_dept (my_cur OUT pack_dept.cur) IS BEGIN OPEN my_cur FOR SELECT * FROM dept; END; -方式二：CREATE OR REPLACE PROCEDURE pro_find_dept (my_cur OUT sys_refcursor) IS BEGIN OPEN my_cur FOR SELECT * FROM dept; END; public

8、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 = admin123; 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(