外部サブプログラム

Cプロシージャ、JavaメソッドまたはJavaScriptファンクションがデータベースに格納されている場合、それを外部サブプログラムとして公開し、PL/SQLから起動できます。

外部サブプログラムを公開するには、コール仕様を使用して、ストアドPL/SQLサブプログラムを定義します。コール仕様は、外部サブプログラムの名前、パラメータ型および戻り型をPL/SQLの同等要素にマップします。公開した外部サブプログラムは、そのPL/SQL名によって起動します。

たとえば、Adjusterという次のJavaクラスがデータベースに格納されているとします。 

import java.sql.*;
import oracle.jdbc.driver.*;
public class Adjuster {
  public static void raiseSalary (int empNo, float percent)
  throws SQLException {
    Connection conn = new OracleDriver().defaultConnection();
    String sql = "UPDATE employees SET salary = salary * ?
                    WHERE employee_id = ?";
    try {
      PreparedStatement pstmt = conn.prepareStatement(sql);
      pstmt.setFloat(1, (1 + percent / 100));
      pstmt.setInt(2, empNo);
      pstmt.executeUpdate();
      pstmt.close();
    } catch (SQLException e)
          {System.err.println(e.getMessage());}
    }
}

JavaクラスAdjusterには、指定の従業員の給与を指定のパーセンテージ分のみ増やすraiseSalaryというメソッドがあります。raiseSalaryは、voidメソッドであるため、(ファンクションではなく)PL/SQLプロシージャとして公開します。

例9-46では、格納されたJavaメソッドAdjuster.raiseSalaryをPL/SQLスタンドアロン・プロシージャとして公開するため、Javaメソッド名のAdjuster.raiseSalaryをPL/SQLプロシージャ名のraise_salaryに、Javaデータ型のintおよびfloatをPL/SQLデータ型のNUMBERにマップしています。その後、無名ブロックでraise_salaryを起動します。

例9-47では、格納されたJavaメソッドjava.lang.Thread.sleepをPL/SQLスタンドアロン・プロシージャとして公開するため、Javaメソッド名をPL/SQLプロシージャ名のjava_sleepに、Javaデータ型のlongをPL/SQLデータ型のNUMBERにマップしています。PL/SQLスタンドアロン・プロシージャのsleepで、java_sleepを起動します。

例9-48では、Javaアジャスタの例の機能をJavaScriptで実装しています。JavaScriptファンクションraiseSalはPL/SQLプロシージャjs_raise_salにマップされ、その後無名PL/SQLブロックを使用して起動されます。

関連項目:

例9-46 PL/SQL無名ブロックによる外部プロシージャの起動

-- Publish Adjuster.raiseSalary as standalone PL/SQL procedure:

CREATE OR REPLACE PROCEDURE raise_salary (
  empid NUMBER,
  pct   NUMBER
) AS
  LANGUAGE JAVA NAME 'Adjuster.raiseSalary (int, float)';  -- call specification
/

BEGIN
  raise_salary(120, 10);  -- invoke Adjuster.raiseSalary by PL/SQL name
END;
/

例9-47 PL/SQLスタンドアロン・プロシージャによる外部プロシージャの起動

-- Java call specification:

CREATE PROCEDURE java_sleep (
  milli_seconds IN NUMBER
) AS LANGUAGE JAVA NAME 'java.lang.Thread.sleep(long)';
/

CREATE OR REPLACE PROCEDURE sleep (
  milli_seconds IN NUMBER
) AUTHID DEFINER IS
BEGIN
  DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.get_time());
  java_sleep (milli_seconds);
  DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.get_time());
END;
/

例9-48 JavaScript外部プロシージャの実装

CREATE OR REPLACE MLE MODULE js_adjuster LANGUAGE JAVASCRIPT AS

import oracledb from "mle-js-oracledb";

/**
* Give an employee a raise
* @param {number} empNo - ID of the employee to give a raise
* @param {number} percent - the raise in percent (0 - 100)
* @returns {number} the new salary
*/

export function raiseSal(empNo, percent) {
  if (empNo === undefined || percent === undefined) {
    throw "provide the employee ID and the raise percentage";
  }

  if(percent < 0 || percent > 100){
    throw new Error("raise must be greater than 0 and less than 100");
  }

  const result = session.execute(
    `UPDATE hr.employees
       SET salary = salary * (1 + (:percent / 100))
     WHERE employee_id = :empNo
     RETURNING new salary into :newSal`,
    {
      percent: {
        type: oracledb.NUMBER,
        val: percent,
        dir: oracledb.BIND_IN,
      },
      empNo: {
        type: oracledb.NUMBER,
        val: empNo,
        dir: oracledb.BIND_IN,
      },
      newSal: {
        type: oracledb.NUMBER,
        dir: oracledb.BIND_OUT,
      },
    },
  );

  //report an error in case the update did not affect any rows
  if(result.rowsAffected !== 1){
    throw new Error(`error updating the salary for employee ${empNo}`);
  }

  //outBinds contain the new salary returned by the RETURNING clause
  //the first element indicates the first new salary (there is only 1)
  return result.outBinds.newSal[0];
}
/

次のコール仕様では、JavaScriptファンクションraiseSalをスタンドアロンPL/SQLファンクションとして公開します。 

CREATE OR REPLACE FUNCTION js_raise_sal(
  p_empno NUMBER, 
  p_percent NUMBER
) RETURN NUMBER 
AS MLE MODULE js_adjuster
SIGNATURE 'raiseSal';
/

PL/SQLプロシージャjs_raise_salは、次の無名ブロックによって起動されます。 

SET SERVEROUTPUT ON;
DECLARE
  l_new_sal NUMBER;
  l_old_sal NUMBER;
  l_empNo NUMBER := 100;
BEGIN
  SELECT salary
  INTO l_old_sal
  FROM hr.employees
  WHERE employee_id = l_empNo;

DBMS_OUTPUT.PUT_LINE('Current salary for employee ' || l_empNo
                          || ' amounts to ' || l_old_sal);

l_new_sal := js_raise_sal(
  p_empno => l_empNo,
  p_percent => 10
);
 
DBMS_OUTPUT.PUT_LINE('New salary for employee ' || l_empNo
                          || ' increased to ' || l_new_sal);
END;
/

結果:

Current salary for employee 100 amounts to 24000
New salary for employee 100 increased to 26400