Oracle存储过程和自定义函数详解(2)-木庄网络博客

根据官方提供的API，我们可以看到：

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;

import org.junit.Test;

import com.turing.oracle.dbutil.DBUtils;

import oracle.jdbc.OracleTypes;


public class TestProcedure {

 @Test
 public void callProcedure(){
  // {call <procedure-name>[(<arg1>,<arg2>, ...)]}

  Connection conn = null ;
  CallableStatement callableStatement = null ;

  /**
   *
   根据员工姓名，查询员工的全部信息
   create or replace procedure QueryStaffInfo(staffName in xgj_test.username%type,
              pSal out number,
              pComm out xgj_test.comm%type,
              pJob out xgj_test.job%type) 
   is
   begin
    --查询该员工的薪资，奖金和职位
    select t.sal,t.comm,t.job into pSal,pComm,pJob from xgj_test t where t.username=staffName;
   end QueryStaffInfo;
   */
  // 我们可以看到该存过 4个参数 1个入参 3个出参
  String sql = "{call QueryStaffInfo(?,?,?,?)}";

  try {
   // 获取连接
   conn = DBUtils.getConnection();
   // 通过连接获取到CallableStatement
   callableStatement = conn.prepareCall(sql);

   // 对于in 参数，需要赋值
   callableStatement.setString(1, "xiao");
   // 对于out 参数，需要声明
   callableStatement.registerOutParameter(2, OracleTypes.NUMBER); // 第二个 ？
   callableStatement.registerOutParameter(3, OracleTypes.NUMBER);// 第三个 ？
   callableStatement.registerOutParameter(4, OracleTypes.VARCHAR);// 第四个 ？

   // 执行调用
   callableStatement.execute();

   // 取出结果
   int salary = callableStatement.getInt(2);
   int comm = callableStatement.getInt(3);
   String job = callableStatement.getString(3);

   System.out.println(salary + "\t" + comm + "\t" + job);

  } catch (SQLException e) {
   e.printStackTrace();
  }finally {
   DBUtils.cleanup(conn, callableStatement, null);
  }


 }
}

在应用程序中访问存储函数

根据官方提供的API，我们可以看到：

import java.sql.CallableStatement;
import java.sql.Connection;

import org.junit.Test;

import com.turing.oracle.dbutil.DBUtils;

import oracle.jdbc.OracleTypes;

public class TestFuction {

 @Test
 public void callFuction(){
  //{?= call <procedure-name>[(<arg1>,<arg2>, ...)]}
  Connection conn = null;
  CallableStatement call = null;
  /**
   * create or replace function querySalaryInCome(staffName in varchar2)
     return number as
     --定义变量保存员工的工资和奖金
     pSalary xgj_test.sal%type;
     pComm xgj_test.comm%type;

    begin
     --查询员工的工资和奖金
     select t.sal, t.comm
     into pSalary, pComm
     from xgj_test t
     where t.username = staffName;
     --直接返回年薪
     return pSalary * 12 + nvl(pComm,0);
    end querySalaryInCome;
   */

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

  try {
   // 获取连接
   conn = DBUtils.getConnection();
   // 通过conn获取CallableStatement
   call = conn.prepareCall(sql);

   // out 参数，需要声明
   call.registerOutParameter(1, OracleTypes.NUMBER);
   // in 参数，需要赋值
   call.setString(2, "gong");

   // 执行
   call.execute();
   // 取出返回值 第一个？的值
   double income = call.getDouble(1);
   System.out.println("该员工的年收入：" + income);
  } catch (Exception e) {
   e.printStackTrace();
  }finally {
   DBUtils.cleanup(conn, call, null);
  }
 }

}

在out参数中访问光标

在out参数中使用光标

我们之前抛出的两个思考问题：

查询员工的所有信息–> out参数太多怎么办？
查询某个部门中所有员工的信息–> out中返回集合？

我们可以通过返回Cursor的方式来实现。

在out参数中使用光标的步骤：

申明包结构
包头
包体

包头：

create or replace package MyPackage is

 -- Author : ADMINISTRATOR
 -- Created : 2016-6-4 18:10:42
 -- Purpose : 

 -- 使用type关键字 is ref cursor说明是cursor类型
 type staffCursor is ref cursor;

 procedure queryStaffJob(pJob   in xgj_test.job%type,
       jobStaffList out staffCursor);

end MyPackage;

创建完包头之后，创建包体，包体需要实现包头中声明的所有方法。

包体

create or replace package body MyPackage is

 procedure queryStaffJob(pJob   in xgj_test.job%type,
       jobStaffList out staffCursor)

 as
 begin
  open jobStaffList for select * from xgj_test t where t.job=pJob;
 end queryStaffJob;

end MyPackage;

事实上，通过plsql工具创建包头，编译后，包体的框架就会自动的生成了。

在应用程序中访问包下的存储过程

在应用程序中访问包下的存储过程，需要带包名

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;

import org.junit.Test;

import com.turing.oracle.dbutil.DBUtils;

import oracle.jdbc.OracleTypes;
import oracle.jdbc.driver.OracleCallableStatement;

public class TestCursor {

 @Test
 public void testCursor(){
  /**
   * 
   * create or replace package MyPackage is
     type staffCursor is ref cursor;

     procedure queryStaffJob(pJob   in xgj_test.job%type,
           jobStaffList out staffCursor);

    end MyPackage;
   */
  String sql = "{call MyPackage.queryStaffJob(?,?)}" ;

  Connection conn = null;
  CallableStatement call = null ;
  ResultSet rs = null;

  try {
   // 获取数据库连接
   conn = DBUtils.getConnection();
   // 通过conn创建CallableStatemet
   call = conn.prepareCall(sql);

   // in 参数 需要赋值
   call.setString(1, "Staff");
   // out 参数需要声明
   call.registerOutParameter(2, OracleTypes.CURSOR);

   // 执行调用
   call.execute();

   // 获取返回值
   rs = ((OracleCallableStatement)call).getCursor(2);
   while(rs.next()){
    // 取出值
    String username = rs.getString("username");
    double sal = rs.getDouble("sal");
    double comm = rs.getDouble("comm");

    System.out.println("username:" + username + "\t sal:" + sal + "\t comm:" + comm);
   }
  } catch (Exception e) {
   e.printStackTrace();
  }finally {
   DBUtils.cleanup(conn, call, rs);
  }
 }

}

原文链接：http://blog.csdn.net/yangshangwei/article/details/51581952

标签：Oracle

返回前面的内容