| Oracle® SQL Developer MySQLからの移行のための追加情報 リリース2.1 B63054-01 |
|
 前へ |
 次へ |
この章では、MySQLとOracleのトリガーおよびストアド・プロシージャを比較します。(この章で説明する情報は、MySQLリリース5にのみ適用され、それより前のリリースには適用されません。) Oracleのトリガーおよびストアド・プロシージャの詳細は、『PL/SQLユーザーズ・ガイドおよびリファレンス』を参照してください。この章の内容は次のとおりです。
トリガーは、トリガー・イベントが発生すると暗黙的に起動される名前付きデータベース・オブジェクトです。トリガー・アクションは、トリガー・イベントの前後に実行できます。トリガーとストアド・プロシージャは似ていますが、起動方法が異なります。
MySQLでは、トリガーは、リリース5.0.2以上でのみサポートされています。トリガーは、表に関連付けて、表でINSERT、DELETEまたはUPDATE文が実行された場合に起動するようにのみ定義できます。MySQLでは、1つの表に対して、起動のタイミング(BEFOREまたはAFTER)と、トリガー・イベントまたは文(INSERT、DELETE、UPDATE)が同じ2つのトリガーを定義することはできません。たとえば、1つの表に対して、2つのBEFORE INSERTトリガーや2つのAFTER UPDATEトリガーを定義することはできません。MySQLで定義されるトリガーはすべて行トリガーです。つまり、トリガーに定義付けられたアクションは、トリガーを起動する文の対象となる行ごとに実行されます。
トランザクション表の場合、トリガーの実行中のエラー処理では、トリガーを起動する文とトリガー・アクションの両方が正常に実行されるか、またはいずれも実行されません(実行済のすべての変更が失敗の際にロールバックされます)。非トランザクション表の場合、エラーが発生する前に行われたすべての変更は引き続き有効です。
次の例は、MySQLでトリガーを作成する構文を示しています。
CREATE TRIGGER <trigger name>
{ BEFORE | AFTER }
{ INSERT | UPDATE | DELETE }
ON <table name>
FOR EACH ROW
<triggered action>
Oracleでは、次のいずれかの操作が発生するとトリガーが起動します。
表またはビューのデータを変更するDML文(INSERT、DELETEまたはUPDATE)
DDL文
ユーザー・イベント(ログオン、ログオフなど)
システム・イベント(起動、停止、エラー・メッセージなど)
Oracleでは、1つの表に対して、起動のタイミングとトリガー・イベントが同じである複数のトリガーを定義できます。ただし、トリガーを実行する順序は保証されません。トリガーは、行トリガーまたは文トリガーとして定義できます。文トリガーは、トリガーを起動する文の対象となる表内の行数にかかわらず、トリガーを起動する文ごとに1回起動されます。たとえば、DELETE文によって表内の複数の行が削除されても、文トリガーは1回のみ起動されます。
Oracleでは、トリガーの実行モデルはトランザクションです。トリガーを起動する文によって実行されるすべてのアクション(起動されたトリガーによって実行されるアクションを含む)が正常に実行される必要があります。そうでない場合は、すべてのアクションがロールバックされます。
ストアド・プロシージャは、サーバーに格納できるアプリケーション・ロジックを記述するのに有効な手段を提供します。ストアド・プロシージャおよびストアド・ファンクションは、MySQLとOracleの両方で使用されています。ストアド・ファンクションは、プロシージャと似ていますが、ファンクションはコールした環境に値を戻す点で異なります。MySQLでは、ストアド・プロシージャおよびストアド・ファンクションを、まとめてルーチンと呼びます。
次の各項では、MySQLとOracleのストアド・プロシージャを比較します。
この項では、次の文または要素に関する考慮事項について説明します。
MySQLのREPLACE文は、2つの目的を兼ね備えています。主キーまたは一意索引の新しいレコードと同じ値を持つレコードが表内に存在しない場合は、INSERT文のように機能します。存在する場合は、UPDATE文のように機能します。
Oracleには、MySQLのREPLACE文の機能をサポートするSQL文が組み込まれていません。この文をOracle用に変換するには、INSERT文とUPDATE文の両方を使用する、エミュレートされた関数を作成する必要があります。最初に、INSERT文を使用して表へのデータの挿入が試行されます。挿入に失敗すると、UPDATE文を使用して表のデータが更新されます。
MySQLのDO文は、名前のとおり何らかの処理を行いますが、結果は戻しません。具体的には、パラメータとして指定された式のカンマ区切りのリストを実行します。Oracleでは、DO文はSELECT expr1 [, expr2,…] INTO … FROM DUAL文に変換されます。
MySQLのDECLARE文は、ストアド・プロシージャでローカル変数を宣言するために使用します。PL/SQLでは、複数の宣言は許可されていません。このため、各宣言を個別に行う必要があります。複合DECLARE文を同等に機能するPL/SQLコードに変換するには、MySQLの複数の宣言文を各宣言に1文ずつ、論理的に同等な個別の文に変換する必要があります。
たとえば、次のようなMySQLの1つの宣言文および複数の宣言文について考えてみます。
/* Simple declaration */ DECLARE a INT; /* Compound declaration */ DECLARE a, b INT DEFAULT 5;
同等に機能するPL/SQL文は、次のようになります。
/* Simple declaration */ a INT; /* Multiple declarations */ a INT := 5; b INT := 5;
この例では、MySQLのDECLARE文に含まれる各宣言に対してPL/SQLでそれぞれ1つの宣言文が使用され、MySQLの元の2つのDECLARE文は、PL/SQLで論理的に同等な3つの宣言文に変換されています。
MySQLのSET文は、変数(ユーザー変数またはシステム変数)に値を代入するために使用します。MySQLでは、同じ文内で複数の変数に値を代入する複合文が許可されています。PL/SQLでは、1つの変数に1つの値を代入する単純な代入のみが許可されています。複合SET文を同等に機能するPL/SQLコードに変換するには、MySQLの複数の代入文を論理的に同等の単純な代入文に分割する必要があります。
たとえば、次のようなMySQLの1つの代入文および複数の代入文について考えてみます。
/* Simple statement */ SET a:=1; /* Compound statement*/ SET x:=1, y:=0;
同等に機能するPL/SQL文は、次のようになります。
/* Simple statement */ a:=1; /* Multiple statements */ x:=1; y:=0;
この例では、MySQLのSET文に含まれる各代入文に対してPL/SQLでそれぞれ1つの代入文が使用され、MySQLの元の2つのSET文は、PL/SQLで論理的に同等な3つの代入文に変換されています。
MySQLのストアド・プロシージャでは、ローカル変数、ユーザー変数およびシステム変数という3つのタイプの変数がサポートされています。
ローカル変数はストアド・プロシージャ内で宣言され、宣言が行われたBEGIN…ENDブロック内でのみ有効です。ローカル変数は、BEGIN…ENDブロック(ネストされたBEGIN…ENDブロックを含む)内の他の文によって参照される前に、このブロック内で宣言される必要があります。ネストされたBEGIN…ENDブロック内で宣言されたローカル変数が、そのブロックを囲むBEGIN…ENDブロック内で宣言されたローカル変数と同じ名前を持つ場合、ネストされたBEGIN…ENDブロックでローカル変数が参照される際は常に、ネストされたブロックのローカル変数が優先されます。ローカル変数には、任意のSQLデータ型を使用できます。次の例は、ストアド・プロシージャでのローカル変数の使用を示しています。
CREATE PROCEDURE p1()
BEGIN
/* declare local variables */
DECLARE x INT DEFAULT 0;
DECLARE y, z INT;
/* using the local variables */
SET x := x + 100;
SET y := 2;
SET z := x + y;
BEGIN
/* local variable in nested block */
DECLARE z INT;
SET z := 5;
/* local variable z takes precedence over the one of the
same name declared in the enclosing block. */
SELECT x, y, z;
END;
SELECT x, y, z;
END;
mysql> call p1();
+-----+---+---+
| x | y | z |
+-----+---+---+
| 100 | 2 | 5 |
+-----+---+---+
1 row in set (0.00 sec)
+-----+---+-----+
| x | y | z |
+-----+---+-----+
| 100 | 2 | 102 |
+-----+---+-----+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
ユーザー変数はユーザー・セッション固有であるため、他のユーザーが参照または使用することはできません。ユーザー変数は、ユーザー・セッションの存続中にのみ有効で、ユーザー・セッションが終了すると自動的に解放されます。ユーザー変数には、セッション・スコープが存在します。このため、セッション内では、同じ名前のユーザー変数に対する参照は、すべて同じ変数を参照します。MySQLストアド・プロシージャでは、ユーザー変数は、ユーザー変数名にアンパサンド(@)の接頭辞を付けて参照されます(@xや@yなど)。次の例は、2つのストアド・プロシージャでのユーザー変数の使用を示しています。
CREATE PROCEDURE p2()
BEGIN
SET @a = 5;
SET @b = 5;
SELECT @a, @b;
END;
CREATE PROCEDURE p3()
BEGIN
SET @a = @a + 10;
SET @b = @b - 5;
SELECT @a, @b;
END;
mysql> call p2();
+------+------+
| @a | @b |
+------+------+
| 5 | 5 |
+------+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> call p3();
+------+------+
| @a | @b |
+------+------+
| 15 | 0 |
+------+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
前述の例に示した2つ目のプロシージャ(p3)では、代入文の右側でユーザー変数aおよびbを使用する際に、1つ目のプロシージャで変数が任意の値に初期化されていることを前提にしています。1つ目のプロシージャで変数が初期化されていないと、変数はNULL値になるため、代入の結果もNULL値になります。
MySQLのストアド・プロシージャでは、システム変数も参照できます。MySQLには、グローバル・システム変数およびセッション・システム変数という2つのシステム変数があります。グローバル・システム変数は、サーバー全体の操作に影響します。セッション・システム変数は、個々のユーザー・セッションに影響します。MySQLのストアド・プロシージャでは、動的システム変数を変更することもできます。
SET文では、グローバル変数の場合は変数名の前にGLOBALまたは@@global.を付けます。セッション変数の場合はオプションで変数名の前にSESSION、@@session.、LOCALまたは@@local.を付けます。システム変数は、@@[global.|session.|local.]var_name構文を使用して、SELECT文で参照できます。global.、session.またはlocal.が存在しないと、MySQLでは、SESSION変数が存在する場合はSESSION変数が、存在しない場合はGLOBAL変数が戻されます。次の例は、構文オプションを示しています。
CREATE PROCEDURE p4()
BEGIN
/* setting the value of a (dynamic) global variable */
SET GLOBAL sort_buffer_size := 10000;
/* retrieving the value of a global variable */
SELECT @@global.sort_buffer_size;
/* setting the value of a (dynamic) session variable */
SET max_join_size := DEFAULT;
/* retrieving the value of a session variable, shown using
different syntax */
SELECT @@session.max_join_size;
SELECT @@local.max_join_size;
SELECT @@max_join_size;
END;
OracleのPL/SQLでも、ストアド・プロシージャで変数を宣言および使用できます。MySQLと同様に、PL/SQLの変数は、PL/SQLブロックの他の文によって参照される前に、このブロックの宣言部で宣言される必要があります。
PL/SQLのローカル変数のスコープは、MySQLのストアド・プロシージャのローカル変数と同じです。ローカル変数は、宣言が行われたPL/SQLブロック(ネストされたPL/SQLブロックを含む)内で有効です。ネストされたPL/SQLブロック内の変数名は、そのブロックを囲むPL/SQLブロック内に同じ変数名があっても優先されます。
MySQLのローカル変数と同様に、PL/SQLの変数には、任意のSQLデータ型(NUMBER、VARCHAR2など)を使用できます。また、PL/SQLの変数には、PL/SQLデータ型(BOOLEAN、PLS_INTEGERなど)を使用したり、特別な修飾子%TYPEおよび%ROWTYPEを使用して表の列または表の行を保持するように宣言することもできます。
次の例は、PL/SQLブロックでの変数宣言を示しています。
DECLARE
/* variables of SQL data-type */
wages NUMBER;
hours_worked NUMBER := 40;
hourly_salary NUMBER := 22.50;
bonus NUMBER := 150;
country VARCHAR2(128);
counter NUMBER := 0;
/* variables of PL/SQL data-types */
done BOOLEAN;
valid_id BOOLEAN;
/* variables declared to hold table rows */
emp_rec1 employees%ROWTYPE;
emp_rec2 employees%ROWTYPE;
BEGIN
wages := (hours_worked * hourly_salary) + bonus;
country := 'France';
country := UPPER('Canada');
done := (counter > 100);
valid_id := TRUE;
emp_rec1.first_name := 'Antonio';
emp_rec1.last_name := 'Ortiz';
emp_rec1 := emp_rec2;
END;
OracleのPL/SQLでは、ストアド・プロシージャで定数を宣言することもできます。変数と同様に、定数は、PL/SQLブロック(ネストされたPL/SQLブロックを含む)内の他の文によって参照される前に、このブロックの宣言部で宣言される必要があります。定数は、CONSTANTキーワードで宣言されます。定数は宣言の中で初期化する必要があり、これ以降に値を代入することはできません。次の例は、PL/SQLでの定数の宣言を示しています。
credit_limit CONSTANT NUMBER := 5000.00;
MySQLのストアド・プロシージャのユーザー変数は、パッケージで変数を定義することによってOracleでエミュレートできます。パッケージ仕様部では、MySQLのユーザー変数がセッションごとにエミュレートされます。ユーザーは、パッケージで定義済の変数を使用できます。次の例は、MySQLのストアド・プロシージャおよびOracle用に変換された同等の文を示しています。次のようなMySQLのストアド・プロシージャを考えてみます。
CREATE PROCEDURE p2() BEGIN SET @a = 5; SET @b = 5; SELECT @a, @b; END;
Oracleでの同等の文は次のようになります。
CREATE OR REPLACE PACKAGE root.globalPkg AS a NUMBER; b NUMBER; END globalPkg; CREATE OR REPLACE PROCEDURE root.p2 AS BEGIN globalPkg.a := 5; globalPkg.b := 5; DBMS_OUTPUT.PUT_LINE(globalPkg.a || ',' || globalPkg.b); END p2; CREATE OR REPLACE PROCEDURE root.p3 AS BEGIN globalPkg.a := globalPkg.a + 10; globalPkg.b := globalPkg.b - 5; DBMS_OUTPUT.PUT_LINE(globalPkg.a || ',' || globalPkg.b); END p3;
ストアド・プロシージャでのエラー処理メカニズムは、OracleのPL/SQLとMySQLの両方で実装されています。ストアド・プロシージャでは、次の文を処理する前に、各SQL文に対してエラーの有無が確認されます。エラーが発生するとすぐに、制御がエラー・ハンドラに渡されます。たとえば、SELECT文によってデータベースの行が検出されないと、エラーが発生し、このエラーを処理するコードが実行されます。
MySQLのストアド・プロシージャでは、ストアド・プロシージャ内でSQL文を実行することによって発生するエラーまたは警告を処理するようにハンドラを定義できます。MySQLでは、CONTINUEハンドラおよびEXITハンドラという2つのタイプのハンドラを使用できます。2つのタイプのハンドラは、ハンドラの実行後のストアド・プロシージャでの次の実行ポイントが異なります。CONTINUEハンドラの場合、エラーを発生させた文の次の文で実行が継続されます。EXITハンドラの場合、BEGIN文およびEND文で囲まれている現行の複合文は終了し、実行はこの複合文の次の文(存在する場合)で継続されます。
ハンドラは、1つ以上の条件を処理するように定義できます。条件は、SQLSTATE値、MySQLのエラー・コード、または事前定義済の条件のいずれかです。事前定義済の条件には、SQLWARNING(警告または注意)、NOT FOUND(行がない)およびSQLEXCEPTION(エラー)があります。条件は名前を使用して個別に定義でき、定義後はハンドラ文で参照できます。すべてのハンドラは、複合文ブロックの開始時に定義されます。
OracleのPL/SQLのストアド・プロシージャでは、エラー状態は例外と呼ばれます。例外には、(ランタイム・システムによって)内部的に定義された例外と、ユーザーが定義する例外があります。内部例外には、名前が事前定義されています(ZERO_DIVIDE、NO_DATA_FOUNDなど)。内部例外は、ランタイム・システムによって暗黙的に(自動的に)呼び出されます。ユーザー定義の例外には名前を付ける必要があり、ストアド・プロシージャのRAISE文によって明示的に呼び出す必要があります。呼び出された例外は、例外ハンドラによって処理されます。
例外ハンドラは、PL/SQLブロックで宣言できます。例外ハンドラはBEGIN文およびEND文で囲まれ、(サブ・ブロックを含む)PL/SQLブロック内の文によって例外が呼び出されると、これを処理します。PL/SQLブロックは、MySQLの複合文ブロックと似ています。例外は、PL/SQLブロックの宣言部でのみ宣言でき、そのブロックに対してはローカルであり、そのサブブロックすべてに対してはグローバルです。このため、外側のブロックは、サブブロックで呼び出された例外がローカルである場合も、これを処理できません。サブブロックで呼び出された例外は、サブブロックで定義された例外ハンドラがこれを処理し、例外ハンドラで再度呼び出されない場合、外側のブロックに伝播されません。例外ハンドラを実行した後、現行のブロックでは実行が停止され、外側のブロックの次の文で再開されます。
次の例は、MySQLおよびOracleのストアド・プロシージャでのエラー処理メカニズムの使用を示しています。次のようなMySQLのストアド・プロシージャを考えてみます。
CREATE PROCEDURE adjust_emp_salary ()
BEGIN
DECLARE job_id INT;
DECLARE employee_id INT DEFAULT 115;
DECLARE sal_raise DECIMAL(3,2);
DECLARE EXIT HANDLER FOR 1339;
SELECT job_id INTO jobid FROM employees WHERE employee_id = empid;
CASE
WHEN jobid = 'PU_CLERK' THEN
SET sal_raise := .09;
WHEN jobid = 'SH_CLERK' THEN
SET sal_raise := .08;
WHEN jobid = 'ST_CLERK' THEN
SET sal_raise := .07;
END CASE;
END;
Oracleでは、同等のPL/SQLは次のようになります。
CREATE OR REPLACE PROCEDURE adjust_emp_salary ()
AS
jobid employees.job_id%TYPE;
empid employees.employee_id%TYPE := 115;
sal_raise NUMBER(3,2);
BEGIN
SELECT job_id INTO jobid from employees
WHERE employee_id = empid;
CASE
WHEN jobid = 'PU_CLERK' THEN
sal_raise := .09;
WHEN jobid = 'SH_CLERK' THEN
sal_raise := .08;
WHEN jobid = 'ST_CLERK' THEN
sal_raise := .07;
END CASE;
EXCEPTION
WHEN CASE_NOT_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee salary not adjusted.');
END;
