| Oracle® Database Java開発者ガイド 11gリリース2 (11.2) B56280-05 |
|
 前 |
 次 |
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()メソッドは、データベース表の名前(empなど)、および条件(sal > 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('emp', 'sal > 1500');
Call completed.
SQL> SELECT ename, sal FROM emp;
ENAME SAL
--------- --------
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 sal ON emp FOR EACH ROW WHEN (new.sal > 1.2 * old.sal) CALL log_sal(:new.empno, :old.sal, :new.sal);
次のUPDATE文を実行すると、emp表のすべての行が更新されます。
SQL> UPDATE emp SET sal = sal + 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(ename, job) AS "Employees" FROM emp
2 WHERE job NOT IN ('MANAGER', 'PRESIDENT') ORDER BY ename;
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 emp SET sal = sal * ? WHERE empno = ?";
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('emp');
...
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以外の例外を含めて)すべてレポートされます。
