java存储过程 函数_Java中执行存储过程和函数(web基础学习笔记十四)

一、概述

如果想要执行存储过程，我们应该使用 CallableStatement 接口。

CallableStatement 接口继承自PreparedStatement 接口。所以CallableStatement 接口包含有Statement 接口和PreparedStatement 接口定义的全部方法，但是并不是所有的方法我们都要使用，主要使用的方法有这样几个：

CallableStatement 常用方法：

返回类型

方法签名

说明

boolean

execute()

执行 SQL 语句，如果第一个结果是 ResultSet 对

象，则返回 true；如果第一个结果是更新计数或者没

有结果，则返回 false

void

registerOutParameter(int parameterIndex,int sqlType)

按顺序位置parameterIndex 将OUT 参数注册为

JDBC 类型sqlType，sqlType 为Types 类中的常量

Type

getType(int parameterIndex)

根据参数的序号获取指定的 JDBC 参数的值。第一

个参数是 1，第二个参数是 2，依此类推

我们可以使用execute()方法来执行存储过程。CallableStatement 为所有的数据库提供了一种统一的标准形式调用存储过程。所以，你将会看到我们使用execute()调用存储过程的语法与在Oracle 中会所有不同。

为了获得存储过程或函数的返回值，我们需要使用 registerOutParameter()方法将返回的参数注册为JDBC 的类型。 registerOutParameter()方法的第一个参数是参数的序号，第一个为1，第二个为2，以此类推。第二个参数需要一个int 值，用来标记JDBC 的类型，我们可以使用java.sql.Types 类中的常量来设置这个参数。比如VARCHAR、DOUBLE 等类型。如果类型不够用，也可以从具体数据库的驱动中寻找合适的类型常量。如果存储过程或函数有返回值，这个方法是必须要调用的，否则无法得到返回值，甚至会发生异常。

CallableStatement 接口中定义了很多get 方法，用于获取存储过程返回的值，根据值的类型不同，你可以使用不同get 方法，比如getInt()、getString()、getDouble()等等。

我们看一下使用CallableStatement 接口执行存储过程和函数的语法格式。

存储过程：{call [(,, ...)]}

函数：{?= call [(,, ...)]}

如果要调用存储过程，则使用第一种语法，就是开头不带问号的语法，call 后面是过程名，

如果没有参数，可以省略小括号。

如果要调用函数，则使用第二种语法，开头带有一个问号加等号，实际上这个问号就是一个占位符，这个问号总是调用函数的第一个占位符。其它部分与过程的语法相同

二、CallableStatement 执行存储过程

2.1、建立基类

packagecom.pb.emp.dao;importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.PreparedStatement;importjava.sql.ResultSet;importjava.sql.SQLException;importcom.pb.emp.untily.ConfigManager;public classBaseDao {protectedConnection conn;protectedPreparedStatement ps;protectedResultSet rs;//建立连接

public booleangetConnection(){

String driver=ConfigManager.getInstance().getString("jdbc.driver_class");

String url=ConfigManager.getInstance().getString("jdbc.connection.url");

String username=ConfigManager.getInstance().getString("jdbc.connection.username");

String password=ConfigManager.getInstance().getString("jdbc.connection.password");try{

Class.forName(driver);

conn=DriverManager.getConnection(url,username, password);

}catch(ClassNotFoundException e) {//TODO Auto-generated catch block

e.printStackTrace();return false;

}catch(SQLException e) {//TODO Auto-generated catch block

e.printStackTrace();return false;

}return true;

}//增加，修改，删除

public intexecuteUpdate(String sql, Object[] params){

getConnection();int updateRow=0;try{

ps=conn.prepareStatement(sql);//填充占位符

for(int i=0;i

ps.setObject(i+1, params[i]);

}

updateRow=ps.executeUpdate();

}catch(SQLException e) {//TODO Auto-generated catch block

e.printStackTrace();

}returnupdateRow;

}//

//查询

publicResultSet executeSQL(String sql, Object[] params){

getConnection();try{

ps=conn.prepareStatement(sql);//填充占位符

for(int i=0;i

ps.setObject(i+1, params[i]);

}

rs=ps.executeQuery();

}catch(SQLException e) {//TODO Auto-generated catch block

e.printStackTrace();

}returnrs;

}//关闭资源

public booleancloseResource() {if(rs!=null){try{

rs.close();

}catch(SQLException e) {//TODO Auto-generated catch block

e.printStackTrace();return false;

}

}if(ps!=null){try{

ps.close();

}catch(SQLException e) {//TODO Auto-generated catch block

e.printStackTrace();return false;

}

}if(conn!=null){try{

conn.close();

}catch(SQLException e) {//TODO Auto-generated catch block

e.printStackTrace();return false;

}

}return true;

}

}

2.2、执行不带参但是有返回值的存储过程

新建类来继承上面的类也可以继承,下面建立存储过程

--查询emp表记录数

CREATE OR REPLACE PROCEDURE getEmpCount(v_count OUT NUMBER)AS

BEGIN

SELECT COUNT(*) INTO v_count FROMemp;END;

调用

//执行不带参但是有返回值的存储过程获取emp表总记录数

public intgetTotalCountProc(){//定义一个变量来接收结果

int totalCount=0;//声明CallableStatement对象

CallableStatement proc=null;

String sql="{call getEmpCount(?)}";try{//建立连接

getConnection();//CallableStatement对象

proc=conn.prepareCall(sql);//将数据库对象数据类型注册为java中的类型

proc.registerOutParameter(1, Types.INTEGER);//执行

proc.execute();//接收返回值

totalCount=proc.getInt(1);

}catch(SQLException e) {//TODO Auto-generated catch block

e.printStackTrace();

}returntotalCount;

}

2.3、执行带参带返回值的存储过程

--根据部门编号和姓名查询人数

CREATE OR REPLACE PROCEDURE getEmpCount(v_deptno NUMBER, v_ename VARCHAR2,v_count OUT NUMBER)AS

BEGIN

SELECT COUNT(*) INTO v_count FROMempWHERE ename LIKE '%'||v_ename||'%' AND deptno=v_deptno;END;

//执行带参带返回值的存储过程

public int getTotalCountProc1(intdeptno,String ename){//定义一个变量来接收结果

int totalCount=0;//声明CallableStatement对象

CallableStatement proc=null;

String sql="{call getEmpCount(?,?,?)}";//建立连接

getConnection();//CallableStatement对象

try{

proc=conn.prepareCall(sql);//设置占位符//Object [] params={deptno,ename};//只设置输入参数即可

proc.setInt(1, deptno);

proc.setString(2, ename);//proc.setInt(3, totalCount);

将数据库对象数据类型注册为java中的类型，将输出参数转换

proc.registerOutParameter(3, Types.INTEGER);//执行

proc.execute();//获取结果

totalCount=proc.getInt(3);

}catch(SQLException e) {//TODO Auto-generated catch block

e.printStackTrace();

}finally{this.closeResource();if(proc!=null){try{

proc.close();

}catch(SQLException e) {//TODO Auto-generated catch block

e.printStackTrace();

}

}

}returntotalCount;

}

2.3、执行返回值为游标的存储过程

--查询员工所有信息

CREATE OR REPLACE PROCEDUREemp_cur(emp_cur OUT SYS_REFCURSOR)AS

BEGIN

OPEN emp_cur FOR SELECT * FROMemp;END;

//执行返回值为游标的存储过程 游标名emp_curpublic ListgetempProc1(){

List emplist=new ArrayList();

String sql="{call emp_cur(?) }";//声明CallableStatement对象

CallableStatementproc=null;//建立连接

getConnection();

try {//执行proc=conn.prepareCall(sql);//注册类型为数据库游标类型proc.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);//接收结果集proc.execute();//获取结果第一个对象

rs=(ResultSet) proc.getObject(1);while(rs.next()){int empno=rs.getInt("empno");

String ename=rs.getString("ename");

String job=rs.getString("job");int mgr=rs.getInt("mgr");

Date hiredate=rs.getDate("hiredate");double sal=rs.getDouble("sal");double comm=rs.getDouble("comm");int deptno=rs.getInt("deptno");//声明Emp对象

Emp emp=new Emp();//将得到的值添加到对象中

emp.setEmpno(empno);

emp.setEname(ename);

emp.setJob(job);

emp.setMgr(mgr);

emp.setHiredate(hiredate);

emp.setSal(sal);

emp.setComm(comm);

emp.setDeptno(deptno);//将对象添加到集合

emplist.add(emp);

}

} catch (SQLException e) {// TODO Auto-generated catch block

e.printStackTrace();

}finally{

this.closeResource();if(proc!=null){

try {proc.close();

} catch (SQLException e) {// TODO Auto-generated catch block

e.printStackTrace();

}

}

}returnemplist;

}

以上看出，需要将输出的参数，和结果注册，输入的参数不要注册,

但输入参数需要设置占位符

三、执行函数

3.1 、函数功能为根据雇员id 返回姓名

CREATE OR REPLACE FUNCTION getename(v_empno NUMBER)RETURN VARCHAR2

ASv_enameVARCHAR2(20);BEGIN

SELECT ename INTO v_ename FROM emp WHERE empno=v_empno;RETURNv_ename;END;

public void getenamefun(intempno){//sql

String ename="";

String sql="{?=call getename(?)}";

CallableStatement fun=null;

getConnection();

try {

fun=conn.prepareCall(sql);

fun.setInt(2, empno);

fun.registerOutParameter(1, Types.VARCHAR);

fun.execute();

ename=fun.getString(1);

System.out.println(ename);

} catch (SQLException e) {// TODO Auto-generated catch block

e.printStackTrace();

}

}

其它的方法与过程一样，只是多了个返回值类型