Oracle存储过程和自定义函数详解

create or replace procedure PrintStudents(p_staffName in xgj_test.username%type) as

  cursor c_testData is

  select t.sal, t.comm from xgj_test t where t.username = p_staffName;

begin

  for v_info in c_testData loop

  DBMS_OUTPUT.PUT_LINE(v_info.sal || ' ' || v_info.comm);

  end loop;

end PrintStudents;

begin

  PrintStudents( 'Computer Science' );

  PrintStudents( 'Match' );

end ;

/

exec PrintStudents( 'Computer Science' );

exec PrintStudents( 'Match' );

create [ or replace ] procedure procedure_name

[( argument [ { IN | OUT | IN OUT }] type,

......

argument [ { IN | OUT | IN OUT }] type ) ] { IS | AS }

procedure_body

/**

  无参数的存过

  打印hello world

  调用存储过程：

  1. exec sayhelloworld();

  2 begin

  sayhelloworld();

  end ;

  /

*/

create or replace procedure sayhelloworld

as

--说明部分

begin

  dbms_output.put_line( 'hello world' );

end sayhelloworld;

SQL> set serveroutput on ;

SQL> exec sayhelloworld();

hello world

PL/SQL procedure successfully completed

SQL> begin

  2 sayhelloworld();

  3 sayhelloworld();

  4 end ;

  5 /

hello world

hello world

PL/SQL procedure successfully completed

/**

创建一个带参数的存储过程

给指定的员工增加工资，并打印增长前后的工资

*/

create or replace procedure addSalary(staffName in xgj_test.username%type )

as

--定义一个变量保存调整之前的薪水

oldSalary xgj_test.sal%type;

begin

  --查询员工涨之前的薪水

  select t.sal into oldSalary from xgj_test t where t.username=staffName;

  --调整薪水

  update xgj_test t set t.sal = sal+1000 where t.username=staffName ;

  --输出

  dbms_output.put_line( '调整之前的薪水:' || oldSalary || ' ,调整之后的薪水:' || (oldSalary + 1000));

end addSalary;

begin

addSalary( 'xiao' );

addSalary( 'gong' );

commit ;

end ;

/

create [ or replace ] function function_name

[( argument [ { IN | OUT | IN OUT }] type,

......

argument [ { IN | OUT | IN OUT }] type ) ]

RETURN { IS | AS }

function_body

/**

查询员工的年薪 （月工资*12 + 奖金）

*/

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 + pComm;

end querySalaryInCome;

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;

/**

根据员工姓名，查询员工的全部信息

*/

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;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

public class DBUtils {

  // 设定数据库驱动，数据库连接地址端口名称，用户名，密码

  private static final String driver = "oracle.jdbc.driver.OracleDriver" ;

  private static final String url = "jdbc:oracle:thin:@ip:xxxx" ;

  private static final String username = "xxxx" ;

  private static final String password = "xxxx" ;

  /**

   * 注册数据库驱动

   */

  static {

   try {

    Class.forName(driver);

   } catch (ClassNotFoundException e) {

    throw new ExceptionInInitializerError(e.getMessage());

   }

  }

  /**

   * 获取数据库连接

   */

  public static Connection getConnection() {

   try {

    Connection connection = DriverManager.getConnection(url, username, password);

    // 成功，返回connection

    return connection;

   } catch (SQLException e) {

    e.printStackTrace();

   }

   // 获取失败，返回null

   return null ;

  }

  /**

   * 释放连接

   */

  public static void cleanup(Connection conn, Statement st, ResultSet rs) {

   if (rs != null ) {

    try {

     rs.close();

    } catch (SQLException e) {

     e.printStackTrace();

    } finally {

     rs = null ;

    }

   }

   if (st != null ) {

    try {

     st.close();

    } catch (SQLException e) {

     e.printStackTrace();

    } finally {

     st = null ;

    }

   }

   if (conn != null ) {

    try {

     conn.close();

    } catch (SQLException e) {

     e.printStackTrace();

    } finally {

     conn = null ;

    }

   }

  }

}

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 );

   }

  }

}

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 );

   }

  }

}

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;

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);

   }

  }

}