外部サブプログラム
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ブロックを使用して起動されます。
関連項目:
- 外部プログラムのコールの詳細は、『Oracle Database開発ガイド』を参照してください。
- コール仕様を使用してJavaScriptファンクションを公開する方法の詳細は、『Oracle Database JavaScript開発者ガイド』を参照してください
例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