Java调用存储过程

Java调用存储过程

 * 调用存储过程和存储函数前过程和函数都需要先编译成功
 * 调用存储过程和存储函数都是用CallableStatement
 * 调用存储过程的sql语法格式：{call <procedure-name>[(<arg1>,<arg2>,...)]}
 * 调用存储函数的sql语法格式：{?=call <procedure-name>[(<arg1>,<arg2>,...)]}
 *                          ?：表示是返回值，且只有一个，存储函数只能有一个返回值

• 调用存储过程

     测试调用存储过程
           -- 存储过程返回多个值
       -- 查询某个员工的姓名，职位和月薪
       create or replace procedure queryEmpInfo(eno    in  number,
                                                pename out varchar2,
                                                pjob   out varchar2,
                                                psal   out number)
       as
       begin
         select ename,empjob,sal into pename,pjob,psal from emp where empno = eno;
       end;
       如上存储过程在Java调用时只需看创建语句就可以了，存储函数也是一样
       @Test
   public void testProcedures() {
       // {call <procedure-name>[<arg1>,<arg2>,...]}
       String sql = "{call queryEmpInfo(?,?,?,?)}";
       Connection conn = null;
       CallableStatement call = null;
       try {
           conn = JDBCUtils.getConnection();
           call = conn.prepareCall(sql);
           // 对于in参数，赋值
           call.setInt(1, 7839);// 1:表示第一个？号
           // 对于out参数，声明
           call.registerOutParameter(2, OracleTypes.VARCHAR);
           call.registerOutParameter(3, OracleTypes.VARCHAR);
           call.registerOutParameter(4, OracleTypes.NUMBER);
           // 执行调用
           call.execute();
           // 调用完就有返回值了，取出返回值
           String name = call.getString(2);
           String job = call.getString(3);
           double sal = call.getDouble(4);
           System.out.println(name + "\t" + job + "\t" + sal);
       } catch (SQLException e) {
           e.printStackTrace();
       } finally {
           JDBCUtils.release(conn, call, null);
       }
   }

• 调用存储函数

/**
     * 测试调用存储函数
     *  -- 存储函数
        -- 查询某个元的年收入
        create or replace function queryEmpTotalSal(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 * 12 + nvl(pcomm,0);-- 奖金可能为null
        end;
     */
    @Test
    public void testFunction() {
        // {?=call <procedure-name>[(<arg1>,<arg2>,...)]}
        String sql = "{?=call queryEempTotalSal(?)}";
        // 这里第一个 ? 就相当于 out 参数，第二个 ? 就相当于 in 参数
        Connection conn = null;
        CallableStatement call = null;
        try {
            conn = JDBCUtils.getConnection();
            call = conn.prepareCall(sql);
            // 对于out参数，声明
            call.registerOutParameter(1, OracleTypes.NUMBER);
            // 对于in参数，赋值
            call.setInt(2, 7839);
            // 执行调用
            call.execute();
            // 取出返回结果
            double totalSal = call.getDouble(1);
            System.out.println("7839号员工的年收入为：" + totalSal);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(conn, call, null);
        }

• cursor作为out参数返回

     /**
      测试返回一个list集合，使用cursor作为out参数
      例：查询某个部门所有员工的所有信息
     */
    @Test
    public void testCursor() {
        // 此查询其实就是调用存储过程，因此语法是一样。{call <procedure-name> [(<arg1>,<arg2>,...]}
        // 但是要加上调用方法所在的包名，如下
        String sql = "{call testOutByCursor.queryEmpList(?,?)}";
        Connection conn = null;
        CallableStatement call = null;
        ResultSet resultSet = null;
        try {
            conn = JDBCUtils.getConnection();
            call = conn.prepareCall(sql);
            // 赋值，输入查询的部门
            call.setInt(1, 10);// 查10号部门
            // 申明out参数
            call.registerOutParameter(2, OracleTypes.CURSOR);
            // 执行
            call.execute();
            // 得到结果,此时要将call转成Oracle的CallableStatement
            resultSet = ((OracleCallableStatement) call).getCursor(2);
            while (resultSet.next()) {
                int empNo = resultSet.getInt("empno");
                String name = resultSet.getString("ename");
                String job = resultSet.getString("empjob");
                int mgr = resultSet.getInt("mgr");
                Date hireDate = resultSet.getDate("hiredate");
                double sal = resultSet.getDouble("sal");
                double comm = resultSet.getDouble("comm");
                int deptno = resultSet.getInt("deptno");
                System.err.println(empNo + "  " + name + "  " + job + "  " + mgr
                        + "  " + hireDate.toString() + "  " + sal + "  " + comm
                        + "  " + deptno + "  ");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(conn, call, resultSet);
        }
    }