ヘッダーをスキップ
Oracle Database Java開発者ガイド
10gリリース2(10.2)
B19189-01
  目次へ
目次
索引へ
索引

前へ
前へ
次へ
次へ
 

7 ストアド・プロシージャのコール

Javaストアド・プロシージャは、ロードして公開した後にコールできます。 この章では、Javaストアド・プロシージャを様々なコンテキストでコールする方法について説明します。OracleのJava仮想マシン(JVM)によるSQL例外の処理方法についても説明します。

この章の内容は、次のとおりです。

7.1 トップレベルからのJavaのコール

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

この項の内容は、次のとおりです。

7.1.1 出力のリダイレクト

サーバーでのデフォルトの出力デバイスは、ユーザー・スクリーンではなくトレース・ファイルです。このため、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.2 トップレベルからのJavaストアド・プロシージャのコールの例

この項では、次の例を示します。

例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

7.2 データベース・トリガーからのJavaのコール

データベース・トリガーは、特定の表またはビューに関連付けられたストアド・プログラムです。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

7.3 SQL DMLからのJavaのコール

ファンクションとして公開するJavaメソッドは、SQLのSELECTINSERTUPDATEDELETECALLEXPLAIN PLANLOCK 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メソッドは次の規則に準拠する必要があります。これらの規則は、副作用を制御することを意図しています。

メソッド内のSQL文が前述の規則に違反している場合は、実行時にエラーが発生します。

7.4 PL/SQLからのJavaのコール

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ブロックから「オブジェクト型メソッドの実装」で定義したオブジェクト型Employeeraise_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;

7.5 JavaからのPL/SQLのコール

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

7.6 Oracle JVMによる例外の処理方法

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以外の例外を含めて)すべてレポートされます。