JAVA调用存储过程、存储函数

接口 CallableStatement

JDK文档对改接口的说明：

public interface CallableStatement
      extends 
      PreparedStatement

用于执行 SQL 存储过程的接口。JDBC API 提供了一个存储过程 SQL 转义语法，该语法允许对所有 RDBMS 使用标准方式调用存储过程。此转义语法有一个包含结果参数的形式和一个不包含结果参数的形式。如果使用结果参数，则必须将其注册为 OUT 参数。其他参数可用于输入、输出或同时用于二者。参数是根据编号按顺序引用的，第一个参数的编号是 1。

   {?= call <procedure-name>[(<arg1>,<arg2>, ...)]}  -------存储函数的sql，第一个？代表返回类型
   {call <procedure-name>[(<arg1>,<arg2>, ...)]}     -------存储过程的sql

IN 参数值是使用继承自 PreparedStatement 的 set 方法设置的。在执行存储过程之前，必须注册所有 OUT 参数的类型；它们的值是在执行后通过此类提供的 get 方法获取的。

CallableStatement 可以返回一个 ResultSet 对象或多个 ResultSet 对象。多个 ResultSet 对象是使用继承自 Statement 的操作处理的。

为了获得最大的可移植性，某一调用的 ResultSet 对象和更新计数应该在获得输出参数的值之前处理。

例子程序

对oracle的scott/tiger用户的emp表操作

存储过程，查询员工信息

create or replace procedure queryEmpInfo(eno in number,
                               pename out varchar2,
                               psal out number,
                               pjob out varchar2)
as
begin
  select ename,sal,job into pename,psal,pjob from emp where empno=eno;
end;

create or replace function queryEmpImcome(eno in number)
return number
as

  --变量
  psal emp.sal%type;
  pcomm emp.comm%type;
begin
  select sal,comm into psal,pcomm from emp where empno=eno;
  return (psal+nvl(pcomm,0))*12;

end;

jdbc工具类:

package com.lhy.util;
import java.sql.*;

/**
 * JDBC工具类 ，一般工具类final。
 * 工具类一般不需要new，不需要构造实例。（把构造方法私有）别人就new不了了。
 * 此时使用类的方法：
 * 1是单例模式（复杂点）
 * 2是提供静态的public方法。（简单）
 * 本例子是简单的提供public方法实现的。需要静态方法
 * 
 * @author hy
 * 
 */
public final class JdbcUtils {
    private static String  url ="jdbc:oracle:thin:@127.0.0.1:1521:ORCL";
    private static String user ="scott";
    private static  String passWord = "tiger"; 

    //构造方法私有，别人不能构造，不会有实例出来.
    private JdbcUtils(){
        
    }
    
    /**
     * 静态代码块，类加载到虚拟机是只执行一次。
     */
    static {
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
        } catch (ClassNotFoundException e) {
            
            e.printStackTrace();
        }

    }

    public static Connection getConnection() {
        Connection conn = null;
        try {
            conn =  DriverManager.getConnection(url,user,passWord);
        } catch (SQLException e) {
            
            e.printStackTrace();
        }
        return conn;
        
             
        
    }
    
    //释放资源，重载方法。
    public static void close(Connection conn) {
        try {
            if(conn != null){
                conn.close();
                conn = null;
            }
        }catch(SQLException e){
            e.printStackTrace();
        } 
    }
    public static void close(Statement stmt){
        try{
            if(stmt != null){
                stmt.close();
                stmt = null;
            }
        }catch(SQLException e){
            e.printStackTrace();
        }
    }
    public static void close(ResultSet  rs){
        try{
            if(rs != null){
                rs.close();
                rs = null;
            }
        }catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

测试程序：

public class OracleTest {

    /**
     * 测试存储过程
     * create or replace procedure queryEmpInfo(eno in number,
                               pename out varchar2,
                               psal out number,
                               pjob out varchar2)
    as

    begin
      select ename,sal,job into pename,psal,pjob from emp where empno=eno;
    end;
     */
    @Test
    public void testProcedure(){
        //格式 {call <procedure-name>[(<arg1>,<arg2>, ...)]}
        String sql = "{call queryEmpInfo(?,?,?,?)}";
        Connection conn = null;
        CallableStatement call = null;
        
        try {
            conn = JdbcUtils.getConnection();
            call = conn.prepareCall(sql);
            //赋值
            call.setInt(1, 7839);
            //对于out参数，声明
            call.registerOutParameter(2, OracleTypes.VARCHAR);
            call.registerOutParameter(3, OracleTypes.NUMBER);
            call.registerOutParameter(4, OracleTypes.VARCHAR);
            
            //调用
            call.execute();
            
            //取值
            String name = call.getString(2);
            double sal = call.getDouble(3);
            String job = call.getString(4);
            System.err.println(name);
            System.err.println(sal);
            System.err.println(job);
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            JdbcUtils.close(call);
            JdbcUtils.close(conn);
        }
    }
    
    /**
     * 测试 存储函数
     * create or replace function queryEmpImcome(eno in number)
        return number
        as
        
          --变量
          psal emp.sal%type;
          pcomm emp.comm%type;
        begin
          select sal,comm into psal,pcomm from emp where empno=eno;
          return (psal+nvl(pcomm,0))*12;
        
        end;
     */
    @Test
    public void testFunction(){
        //{?= call <procedure-name>[(<arg1>,<arg2>, ...)]}
        //第一个？--> 输出参数，返回值，    第二个 ？-->输入参数
        String sql = "{?= call queryEmpImcome(?)}";
        Connection conn = null;
        CallableStatement call = null;
        
        try {
            conn = JdbcUtils.getConnection();
            call = conn.prepareCall(sql);
            
            //注册输出参数
            call.registerOutParameter(1, OracleTypes.NUMBER);
            //输入参数
            call.setInt(2, 7839);
            
            //执行
            call.execute();
            //取出年收入，注意是get 1
            double income = call.getDouble(1);
            System.err.println("年收入："+income);
            
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            JdbcUtils.close(call);
            JdbcUtils.close(conn);
        }
    }
}

运行结果：

每个人身上都有太阳，主要是如何让它发光——苏格拉底