JDBC调用存储过程之实例讲解
【说明】：本文主要讲解使用JDBC调用存储过程的各种方案，数据库使用Oracle（其他数据库类似）涉及到的数据表均为Oracle自带的Scott帐号的数据表。
【引言】：存储过程是数据库使用的重要技术之一，以其高效率、高安全性见长，而JDBC调用存储过程也是Java程序员必掌握的技能之一。JDBC调用存储过程主要使用CallableStatement接口，而对于输入（in）和输出（out）参数的处理也比较复杂，本文使用案例并有详细注解来说明各种情况。
一、调用带输入\输出参数的存储过程
1. 建立存储过程
|
-- 输入职工号(zgh)、输出姓名(xm)和工资(gz) create or replace procedure getNameSalByNo(zgh in emp.empno%type,xm out emp.ename%type,gz out emp.sal%type) is begin select ename,sal into xm,gz from emp where empno=zgh; end; |
2. JDBC的调用
|
package com.tjxz.proc; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Types; public class CallProcedure { /** * 调用带普通参数的存储过程 * @author icer * @web http://www.tjxz.com */ public static void main(String[] args) { // 设置JDBC参数信息 String url = "jdbc:oracle:thin:@localhost:1521:orcl"; String uid = "scott"; String pwd = "triger"; try { // 创建接受返回值的变量（姓名：xm，工资：gz） String xm=""; float gz=0.0f; /* * 准备SQL语句 |
|
* 格式为： {call 存储过程名{?,?,?}} * 括号中的问号和存储过程参数进行匹配 */ String sql = "{call getNameSalByNo(?,?,?)}"; // 加载驱动程序 Class.forName("oracle.jdbc.driver.OracleDriver"); // 获取连接对象 Connection con = DriverManager.getConnection(url, uid, pwd); // 获取执行对象 CallableStatement cst = con.prepareCall(sql); // 执行之前要使用setXXX来替换SQL语句中的问号参数 cst.setInt(1, 7788); // 注册输出参数类型（注意索引要和问号的位置对应） cst.registerOutParameter(2, Types.VARCHAR); cst.registerOutParameter(3, Types.FLOAT); // 执行SQL命令 cst.execute(); // 提取输出参数 xm = cst.getString(2); gz = cst.getFloat(3); // 控制台输出 System.out.println("姓名：" + xm); System.out.println("工资：" + gz); // 关闭相关对象 cst.close(); con.close(); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } } } |
3. 输出结果
|
姓名：SCOTT 工资：3000.0 |
二、调用带参数及返回值的函数
1. 建立函数
|
-- 输入职工号(zgh)，输出工资(gz) create or replace function getSalByNo(zgh in emp.empno%type) return emp.sal%type is |
|
gz emp.sal%type; begin select sal into gz from emp where empno=zgh; return gz; exception when others then return -1; end; |
2. JDBC调用
|
package com.tjxz.proc; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Types; public class CallFunction { /** * 调用带参数及返回值的函数 * @author icer * @web http://www.tjxz.com */ public static void main(String[] args) { // 设置JDBC参数信息 String url = "jdbc:oracle:thin:@localhost:1521:orcl"; String uid = "scott"; String pwd = "triger"; try { // 创建接受返回值的变量（工资：gz） float gz=0.0f; /* * 准备SQL语句 * 格式为： {?=call 函数名{?,?,?}} * 括号中的问号和函数参数进行匹配，使用?=接受返回值 */ String sql = "{?=call getSalByNo(?)}"; // 加载驱动程序 Class.forName("oracle.jdbc.driver.OracleDriver"); // 获取连接对象 Connection con = DriverManager.getConnection(url, uid, pwd); // 获取执行对象 CallableStatement cst = con.prepareCall(sql); |
|
// 执行之前要使用setXXX来替换SQL语句中的问号参数 cst.setInt(2, 7788); // 注册输出参数类型（注意索引要和问号的位置对应） cst.registerOutParameter(1, Types.FLOAT); // 执行SQL命令 cst.execute(); // 提取输出参数 gz = cst.getFloat(1); // 控制台输出 System.out.println("工资：" + gz); // 关闭相关对象 cst.close(); con.close(); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } } } |
3. 输出结果
|
工资：3000.0 |
三、调用输出参数为游标的存储过程
1. 创建存储过程
|
-- 输入部门编号(dno)，输出此部门的所职工信息 /*说明：sys_refcursor为系统已定义的动态游标类型声明*/ create or replace procedure getEmpByDeptno(dno in emp.deptno%type,emps out sys_refcursor) is begin open emps for select * from emp where deptno=dno; end; |
2. JDBC调用
|
package com.tjxz.proc; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import oracle.jdbc.internal.OracleTypes; |
|
public class CallProcedureOfCursor { /** * 调用输出参数为游标的存储过程 * @author icer * @web http://www.tjxz.com */ public static void main(String[] args) { // 设置JDBC参数信息 String url = "jdbc:oracle:thin:@localhost:1521:orcl"; String uid = "scott"; String pwd = "triger"; try { /* * 准备SQL语句 格式为： {call 存储过程名{?,?,?}} * 括号中的问号和存储过程参数进行匹配 */ String sql = "{call getEmpByDeptno(?,?)}"; // 加载驱动程序 Class.forName("oracle.jdbc.driver.OracleDriver"); // 获取连接对象 Connection con = DriverManager.getConnection(url, uid, pwd); // 获取执行对象 CallableStatement cst = con.prepareCall(sql); // 执行之前要使用setXXX来替换SQL语句中的问号参数 cst.setInt(1, 10); // 注册输出参数类型（注意索引要和问号的位置对应） cst.registerOutParameter(2, OracleTypes.CURSOR); // 执行SQL命令 cst.execute(); // 提取输出参数(输出游标使用ResultSet类型接收) ResultSet rst = (ResultSet) cst.getObject(2); // 控制台输出 System.out.println("EMPNO\tENAME"); System.out.println("----------------------"); while (rst.next()) { System.out.println(rst.getInt("empno") + "\t" + rst.getString("ename")); } // 关闭相关对象 rst.close(); cst.close(); con.close(); } catch (ClassNotFoundException e) { |
|
e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } } } |
3. 输出结果
|
EMPNO ENAME ---------------------- 7782 CLARK 7839 KING 7934 MILLER |
四、 调用返回值为游标的函数
1. 创建函数
|
-- 输入部门编号(dno)，返回此部门的所职工信息 /*说明：sys_refcursor为系统已定义的动态游标类型声明*/ create or replace function getEmpsByDeptno(dno in emp.deptno%type) return sys_refcursor is emps sys_refcursor; begin open emps for select * from emp where deptno=dno; return emps; end; |
2. JDBC调用
|
package com.tjxz.proc; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import oracle.jdbc.internal.OracleTypes; public class CallFunctionOfCursor { /** * 调用返回值为游标的函数 * * @author icer * @web http://www.tjxz.com */ |
|
public static void main(String[] args) { // 设置JDBC参数信息 String url = "jdbc:oracle:thin:@localhost:1521:orcl"; String uid = "scott"; String pwd = "triger"; try { /* * 准备SQL语句，格式为： {?=call 函数名{?,?,?}} * 括号中的问号和函数参数进行匹配，使用?=接受返回值 */ String sql = "{?=call getEmpsByDeptno(?)}"; // 加载驱动程序 Class.forName("oracle.jdbc.driver.OracleDriver"); // 获取连接对象 Connection con = DriverManager.getConnection(url, uid, pwd); // 获取执行对象 CallableStatement cst = con.prepareCall(sql); // 执行之前要使用setXXX来替换SQL语句中的问号参数 cst.setInt(2, 10); // 注册输出参数类型（注意索引要和问号的位置对应） cst.registerOutParameter(1, OracleTypes.CURSOR); // 执行SQL命令 cst.execute(); // 提取输出参数(输出游标使用ResultSet类型接收) ResultSet rst = (ResultSet) cst.getObject(1); // 控制台输出 System.out.println("EMPNO\tENAME"); System.out.println("----------------------"); while (rst.next()) { System.out.println(rst.getInt("empno") + "\t" + rst.getString("ename")); } // 关闭相关对象 rst.close(); cst.close(); con.close(); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } } } |
3. 输出结果同上
五、 调用自声明游标类型的存储过程
1. 创建存储过程
|
/*不使用系统已声明的动态游标类型，在程序包中自己声明动态游标类型*/ -- 创建程序包首部，声明游标和存储过程 create or replace package pk_scott as -- 声明动态游标类型 type dcur is ref cursor; -- 声明存储过程 procedure getEmpByDeptno(dno in emp.deptno%type,emps out dcur); end; -- 创建程序包体，并实现存储过程 create or replace package body pk_scott as -- 实现存储过程，输入部门号返回此部门所有雇员信息 procedure getEmpByDeptno(dno in emp.deptno%type,emps out dcur) is begin open emps for select * from emp where deptno=dno; end; end; |
2. JDBC调用：同上
3. 显示结果：同上
补充：如果调用不带任何参数的存储过程格式为｛call 存储过程名｝。