【转】JDBC调用存储过程之实例讲解

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)

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

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)

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

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

-- 声明动态游标类型

type dcur is ref cursor;

-- 声明存储过程

procedure getEmpByDeptno(dno in emp.deptno%type,emps out dcur);

end;

-- 创建程序包体，并实现存储过程

create or replace package body pk_scott

-- 实现存储过程，输入部门号返回此部门所有雇员信息

procedure getEmpByDeptno(dno in emp.deptno%type,emps out dcur)

begin

open emps for select * from emp where deptno=dno;

end;

2. JDBC调用：同上

3. 显示结果：同上

补充：如果调用不带任何参数的存储过程格式为｛call 存储过程名｝。