Javaストアド・プロシージャは、ロードして公開した後にコールできます。この章では、Javaストアド・プロシージャを様々なコンテキストでコールする方法について説明します。Oracle JVMによるSQL例外の処理方法についても説明します。
この章の内容は、次のとおりです。
SQLのCALL
文を使用すると、トップレベルで公開されたJavaメソッドを、PL/SQLパッケージまたはSQLオブジェクト型でコールできます。SQL*Plusでは、次の構文を使用してCALL
文を対話形式で実行できます。
CALL [schema_name.][{package_name | object_type_name}][@dblink_name] { procedure_name ([param[, param]...]) | function_name ([param[, param]...]) INTO :host_variable};
param
は次の構文で表されます。
{literal | :host_variable}
ホスト変数は、ホスト環境で宣言された変数です。このホスト変数には接頭辞としてコロンを付ける必要があります。次の例は、同じCALL
文ではホスト変数を2度使用できないこと、およびパラメータのないサブプログラムは空のパラメータ・リストを使用してコールする必要があることを示しています。
CALL swap(:x, :x); -- illegal, duplicate host variables CALL balance() INTO :current_balance; -- () required
この項の内容は、次のとおりです。
サーバーでのデフォルトの出力デバイスは、ユーザー・スクリーンではなくトレース・ファイルです。このため、System.out
およびSystem.err
は、現行のトレース・ファイルに出力されます。出力をSQL*Plusテキスト・バッファにリダイレクトするには、次のようにDBMS_JAVA
パッケージのset_output()
プロシージャをコールする必要があります。
SQL> SET SERVEROUTPUT ON SQL> CALL dbms_java.set_output(2000);
最小バッファ・サイズは2,000バイト(デフォルト・サイズ)で、最大バッファ・サイズは1,000,000バイトです。次の例では、バッファ・サイズを5,000バイトに増やしています。
SQL> SET SERVEROUTPUT ON SIZE 5000 SQL> CALL dbms_java.set_output(5000);
出力はストアド・プロシージャの終了時に表示されます。
この項では、次の例を示します。
例7-1 簡単なJDBCストアド・プロシージャ
次の例にあるmain()
メソッドは、データベース表の名前(employees
など)、および条件(salary > 1500
など)を指定するオプションのWHERE
句を受け入れます。条件を省略した場合、メソッドは表からすべての行を削除し、条件を指定した場合は、その条件を満たす行のみを削除します。
import java.sql.*; import oracle.jdbc.*; public class Deleter { public static void main (String[] args) throws SQLException { Connection conn = DriverManager.getConnection("jdbc:default:connection:"); String sql = "DELETE FROM " + args[0]; if (args.length > 1) sql += " WHERE " + args[1]; try { Statement stmt = conn.createStatement(); stmt.executeUpdate(sql); stmt.close(); } catch (SQLException e) { System.err.println(e.getMessage()); } } }
main()
メソッドには、1つまたは2つの引数を指定できます。通常は、DEFAULT
句を使用して、PL/SQLサブプログラムに渡す引数の数を変更します。ただし、この句はコール仕様では使用できません。そのため、次のように2つのパッケージ・プロシージャをオーバーロードする必要があります。
CREATE OR REPLACE PACKAGE pkg AS PROCEDURE delete_rows (table_name VARCHAR2); PROCEDURE delete_rows (table_name VARCHAR2, condition VARCHAR2); END; CREATE OR REPLACE PACKAGE BODY pkg AS PROCEDURE delete_rows (table_name VARCHAR2) AS LANGUAGE JAVA NAME 'Deleter.main(java.lang.String[])'; PROCEDURE delete_rows (table_name VARCHAR2, condition VARCHAR2) AS LANGUAGE JAVA NAME 'Deleter.main(java.lang.String[])'; END;
これで、次のようにdelete_rows
プロシージャをコールできます。
SQL> CALL pkg.delete_rows('employees', 'salary > 1500'); Call completed. SQL> SELECT first_name, salary FROM employees; FIRST_NAME SALARY --------- -------- SMITH 800 WARD 1250 MARTIN 1250 TURNER 1500 ADAMS 1100 JAMES 950 MILLER 1300 7 rows selected.
注意: トップレベルのプロシージャはオーバーロードできません。 |
例7-2 フィボナッチ数列
次のJavaクラスの実行可能ファイルがOracle Databaseに格納されているとします。
public class Fibonacci { public static int fib (int n) { if (n == 1 || n == 2) return 1; else return fib(n - 1) + fib(n - 2); } }
Fibonacci
クラスにはfib()
という名前のメソッドが1つあり、このメソッドはn番目のフィボナッチ数を戻します。フィボナッチ数列(1、1、2、3、5、8、13、21、...)は、再帰的です。数列の各項目(2番目の項目より後)は、直前の2つの項目の合計値です。fib()
は値を戻すため、次のようにファンクションとして公開する必要があります。
CREATE OR REPLACE FUNCTION fib (n NUMBER) RETURN NUMBER AS LANGUAGE JAVA NAME 'Fibonacci.fib(int) return int';
次に、2つのSQL*Plusホスト変数を宣言し、最初の変数を初期化します。
SQL> VARIABLE n NUMBER SQL> VARIABLE f NUMBER SQL> EXECUTE :n := 7; PL/SQL procedure successfully completed.
これで、fib()
ファンクションをコールできます。CALL
文では、ホスト変数に接頭辞としてコロンを付ける必要があります。このファンクションは次のようにコールできます。
SQL> CALL fib(:n) INTO :f; Call completed. SQL> PRINT f F ---------- 13
データベース・トリガーは、特定の表またはビューに関連付けられたストアド・プログラムです。Oracle Databaseでは、データ操作言語(DML)操作によって表またはビューが影響されると、トリガーが自動的に実行されます。
トリガー・イベントが発生すると、トリガーが実行され、PL/SQLブロックまたはCALL
文によってアクションが実行されます。トリガー・イベントの前または後に、文トリガーが1回実行されます。トリガー・イベントの影響を受けた各行に対して、行トリガーが1回実行されます。
データベース・トリガー内では、相関名new
およびold
を使用して、変更する行の新旧の値を参照できます。トリガー・アクション・ブロックまたはCALL
文では、列名に:new
または:old
を接頭辞として付ける必要があります。
次に、データベース・トリガーからのJavaストアド・プロシージャのコールの例を示します。
例7-3 データベース・トリガーからのJavaストアド・プロシージャのコール - I
次のJavaクラスを使用して、一定以上の給与増加を記録するデータベース・トリガーを作成するとします。
import java.sql.*; import java.io.*; import oracle.jdbc.*; public class DBTrigger { public static void logSal (int empID, float oldSal, float newSal) throws SQLException { Connection conn = DriverManager.getConnection("jdbc:default:connection:"); String sql = "INSERT INTO sal_audit VALUES (?, ?, ?)"; try { PreparedStatement pstmt = conn.prepareStatement(sql); pstmt.setInt(1, empID); pstmt.setFloat(2, oldSal); pstmt.setFloat(3, newSal); pstmt.executeUpdate(); pstmt.close(); } catch (SQLException e) { System.err.println(e.getMessage()); } } }
DBTrigger
クラスには、sal_audit
表に行を挿入するlogSal()
メソッドが1つあります。logSal()
はvoid
メソッドであるため、次のようにプロシージャとして公開する必要があります。
CREATE OR REPLACE PROCEDURE log_sal ( emp_id NUMBER, old_sal NUMBER, new_sal NUMBER ) AS LANGUAGE JAVA NAME 'DBTrigger.logSal(int, float, float)';
次に、sal_audit
表を次のように作成します。
CREATE TABLE sal_audit ( empno NUMBER, oldsal NUMBER, newsal NUMBER );
最後に、給与の増加が20パーセントを超えたときに起動するデータベース・トリガーを作成します。
CREATE OR REPLACE TRIGGER sal_trig AFTER UPDATE OF salary ON employees FOR EACH ROW WHEN (new.salary > 1.2 * old.salary) CALL log_sal(:new.employee_id, :old.salary, :new.salary);
次のUPDATE
文を実行すると、employees
表のすべての行が更新されます。
SQL> UPDATE employee SET salary = salary + 300;
トリガーのWHEN
句の条件セットを満たす各行に対してトリガーが実行され、Javaメソッドがsal_audit
表に行を挿入します。
SQL> SELECT * FROM sal_audit; EMPNO OLDSAL NEWSAL ---------- ---------- ---------- 7369 800 1100 7521 1250 1550 7654 1250 1550 7876 1100 1400 7900 950 1250 7934 1300 1600 6 rows selected.
例7-4 データベース・トリガーからのJavaストアド・プロシージャのコール - II
次のように定義されたデータベース・ビューに行を挿入するトリガーを作成するとします。
CREATE VIEW emps AS SELECT empno, ename, 'Sales' AS dname FROM sales UNION ALL SELECT empno, ename, 'Marketing' AS dname FROM mktg;
データベース表sales
およびmktg
は次のように定義されています。
CREATE TABLE sales (empno NUMBER(4), ename VARCHAR2(10)); CREATE TABLE mktg (empno NUMBER(4), ename VARCHAR2(10));
UNION ALL
などの集合演算子を使用するビューには行を挿入できないため、INSTEAD OF
トリガーを作成する必要があります。このトリガーを作成しないと、複数の行が実表に挿入されます。
最初に、次のJavaメソッドを例7-3で定義したDBTrigger
クラスに追加します。
public static void addEmp (int empNo, String empName, String deptName) throws SQLException { Connection conn = DriverManager.getConnection("jdbc:default:connection:"); String tabName = (deptName.equals("Sales") ? "sales" : "mktg"); String sql = "INSERT INTO " + tabName + " VALUES (?, ?)"; try { PreparedStatement pstmt = conn.prepareStatement(sql); pstmt.setInt(1, empNo); pstmt.setString(2, empName); pstmt.executeUpdate(); pstmt.close(); } catch (SQLException e) { System.err.println(e.getMessage()); } }
addEmp()
メソッドは、deptName
パラメータの値に応じて、sales
表またはmktg
表に行を挿入します。このメソッドのコール仕様は、次のように作成します。
CREATE OR REPLACE PROCEDURE add_emp ( emp_no NUMBER, emp_name VARCHAR2, dept_name VARCHAR2 ) AS LANGUAGE JAVA NAME 'DBTrigger.addEmp(int, java.lang.String, java.lang.String)';
次に、INSTEAD OF
トリガーを次のように作成します。
CREATE OR REPLACE TRIGGER emps_trig INSTEAD OF INSERT ON emps FOR EACH ROW CALL add_emp(:new.empno, :new.ename, :new.dname);
次の各INSERT
文を実行すると、トリガーが実行され、Javaメソッドによって、行が適切な実表に挿入されます。
SQL> INSERT INTO emps VALUES (8001, 'Chand', 'Sales'); SQL> INSERT INTO emps VALUES (8002, 'Van Horn', 'Sales'); SQL> INSERT INTO emps VALUES (8003, 'Waters', 'Sales'); SQL> INSERT INTO emps VALUES (8004, 'Bellock', 'Marketing'); SQL> INSERT INTO emps VALUES (8005, 'Perez', 'Marketing'); SQL> INSERT INTO emps VALUES (8006, 'Foucault', 'Marketing'); SQL> SELECT * FROM sales; EMPNO ENAME ---------- ---------- 8001 Chand 8002 Van Horn 8003 Waters SQL> SELECT * FROM mktg; EMPNO ENAME ---------- ---------- 8004 Bellock 8005 Perez 8006 Foucault SQL> SELECT * FROM emps; EMPNO ENAME DNAME ---------- ---------- --------- 8001 Chand Sales 8002 Van Horn Sales 8003 Waters Sales 8004 Bellock Marketing 8005 Perez Marketing 8006 Foucault Marketing
ファンクションとして公開するJavaメソッドは、SQLのSELECT
、INSERT
、UPDATE
、DELETE
、CALL
、EXPLAIN PLAN
、LOCK TABLE
およびMERGE
の各文からコールできます。たとえば、次のJavaクラスの実行可能ファイルがOracle Databaseに格納されているとします。
public class Formatter { public static String formatEmp (String empName, String jobTitle) { empName = empName.substring(0,1).toUpperCase() + empName.substring(1).toLowerCase(); jobTitle = jobTitle.toLowerCase(); if (jobTitle.equals("analyst")) return (new String(empName + " is an exempt analyst")); else return (new String(empName + " is a non-exempt " + jobTitle)); } }
Formatter
クラスには、formatEmp()
メソッドがあり、これは、従業員の名前と役職を含むフォーマットされた文字列を戻します。このメソッドのコール仕様は、次のように作成します。
CREATE OR REPLACE FUNCTION format_emp (ename VARCHAR2, job VARCHAR2) RETURN VARCHAR2 AS LANGUAGE JAVA NAME 'Formatter.formatEmp (java.lang.String, java.lang.String) return java.lang.String';
次に、format_emp
ファンクションをコールして従業員のリストをフォーマットします。
SQL> SELECT format_emp(first_name, job_id) AS "Employees" FROM employees 2 WHERE job_id NOT IN ('AC_MGR', 'AD_PRES') ORDER BY first_name; Employees -------------------------------------------- Adams is a non-exempt clerk Allen is a non-exempt salesman Ford is an exempt analyst James is a non-exempt clerk Martin is a non-exempt salesman Miller is a non-exempt clerk Scott is an exempt analyst Smith is a non-exempt clerk Turner is a non-exempt salesman Ward is a non-exempt salesman
Javaメソッドは次の規則に準拠する必要があります。これらの規則は、副作用を制御することを意図しています。
SELECT
文またはパラレル化したINSERT
、UPDATE
またはDELETE
の各文からコールしたメソッドでは、データベース表を変更できません。
INSERT
、UPDATE
またはDELETE
の各文からコールしたメソッドでは、その文で変更されたデータベース表に対する問合せや変更はできません。
SELECT
、INSERT
、UPDATE
またはDELETE
の各文からコールしたメソッドでは、SQLトランザクション制御文(COMMIT
など)、セッション制御文(SET ROLE
など)またはシステム制御文(ALTER SYSTEM
など)を実行できません。また、このメソッドでは、CREATE
などのデータ定義言語(DDL)文の後に自動コミットが続くため、この文も実行できません。
メソッド内のSQL文が前述の規則に違反している場合は、実行時にエラーが発生します。
Javaストアド・プロシージャは、あらゆるPL/SQLブロック、サブプログラムまたはパッケージからコールできます。たとえば、次のJavaクラスの実行可能ファイルがOracle Databaseに格納されているとします。
import java.sql.*; import oracle.jdbc.*; public class Adjuster { public static void raiseSalary (int empNo, float percent) throws SQLException { Connection conn = DriverManager.getConnection("jdbc:default:connection:"); 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()); } } }
Adjuster
クラスには、指定した割合で従業員の給与を上げるメソッドが1つあります。raiseSalary()
はvoid
メソッドであるため、次のようにプロシージャとして公開する必要があります。
CREATE OR REPLACE PROCEDURE raise_salary (empno NUMBER, pct NUMBER) AS LANGUAGE JAVA NAME 'Adjuster.raiseSalary(int, float)';
次の例では、無名PL/SQLブロックからraise_salary
プロシージャをコールします。
DECLARE emp_id NUMBER; percent NUMBER; BEGIN -- get values for emp_id and percent raise_salary(emp_id, percent); ... END;
次の例では、スタンドアロンPL/SQLストアド・プロシージャから例6-3で定義したrow_count
ファンクションをコールします。
CREATE PROCEDURE calc_bonus (emp_id NUMBER, bonus OUT NUMBER) AS emp_count NUMBER; ... BEGIN emp_count := row_count('employees'); ... END;
次の例では、無名PL/SQLブロックから「オブジェクト型メソッドの実装」で定義したオブジェクト型Employee
のraise_sal
メソッドをコールします。
DECLARE emp_id NUMBER(4); v emp_type; BEGIN -- assign a value to emp_id SELECT VALUE(e) INTO v FROM emps e WHERE empno = emp_id; v.raise_sal(500); UPDATE emps e SET e = v WHERE empno = emp_id; ... END;
Java Database Connectivity(JDBC)およびSQLJを使用すると、PL/SQLストアド・ファンクションおよびPL/SQLストアド・プロシージャをコールできます。たとえば、指定した銀行口座の残高を戻す、次のストアド・ファンクションをコールするとします。
FUNCTION balance (acct_id NUMBER) RETURN NUMBER IS acct_bal NUMBER; BEGIN SELECT bal INTO acct_bal FROM accts WHERE acct_no = acct_id; RETURN acct_bal; END;
JDBCプログラムでは、balance
ファンクションのコールを次のように作成できます。
... CallableStatement cstmt = conn.prepareCall("{? = CALL balance(?)}"); cstmt.registerOutParameter(1, Types.FLOAT); cstmt.setInt(2, acctNo); cstmt.executeUpdate(); float acctBal = cstmt.getFloat(1); ...
SQLJプログラムでは、このコールを次のように作成できます。
... #sql acctBal = {VALUES(balance(:IN acctNo))}; ...
Java例外はオブジェクトであり、ネーミングと継承の階層があります。そのため、サブ例外(例外クラスのサブクラス)をスーパー例外(例外クラスのスーパークラス)のかわりに使用できます。
すべてのJava例外オブジェクトはtoString()
メソッドをサポートしており、これは、オプションの文字列に連結した例外クラスの完全修飾名を戻します。一般に、この文字列には、例外条件に関するデータ依存情報が含まれています。通常、例外を構成するコードは、この文字列を例外に対応付けます。
Javaストアド・プロシージャでSQL文が実行されると、スローされた例外は、そのプロシージャに対してjava.sql.SQLException
のサブクラスとして作成されます。このクラスには、getErrorCode()
およびgetMessage()
メソッドがあり、これらのメソッドは、それぞれOracleエラー・コードおよびエラー・メッセージを戻します。
SQLまたはPL/SQLからコールされたストアド・プロシージャがJavaで捕捉されない例外をスローした場合は、次のエラー・メッセージが表示されます。
ORA-29532 Java call terminated by uncaught Java exception
このエラー・メッセージによって、捕捉されない例外が(SQL以外の例外を含めて)すべてレポートされます。