Oracle® SQL Developer Microsoft SQL ServerおよびSybase Adaptive Serverからの移行のための追加情報 リリース3.0 B66848-01 |
|
前 |
次 |
この章では、Microsoft SQL ServerまたはSybase Adaptive Serverと、Oracleの違いを説明します。内容は次のとおりです。
Microsoft SQL ServerまたはSybase Adaptive Serverのデータベース・トリガーは、AFTERトリガーです。これは、特定の操作を実行した後にトリガーが起動されることを意味します。たとえば、INSERTトリガーは、データベースに行が挿入された後に起動されます。トリガーが正常に起動されない場合は、操作がロールバックされます。
Microsoft SQL ServerまたはSybase Adaptive Serverでは、INSERT、UPDATEおよびDELETEトリガーを使用できます。通常、トリガーは変更中のデータのビフォア・イメージおよびアフター・イメージにアクセスする必要があります。Microsoft SQL ServerまたはSybase Adaptive Serverでは、INSERTEDおよびDELETEDという2つの一時表を使用してこの処理が行われます。これらの2つの表はトリガーの実行中に存在します。これらの表と、トリガーが書き込まれる表は完全に同じ構造をしています。DELETED表にはUPDATE/DELETE操作のために変更されている行のビフォア・イメージが保持され、INSERTED表にはINSERT/UPDATE操作に対応する行のアフター・イメージが保持されます。エラーが発生した場合は、トリガーでロールバック文を発行できます。
Microsoft SQL ServerまたはSybase Adaptive Serverのほぼすべてのトリガーのコードは、参照整合性が適用されます。Microsoft SQL ServerまたはSybase Adaptive Serverのトリガーは、SQL文(INSERT、UPDATE、DELETEなど)を実行するたびに1回実行されます。SQL文の影響を受ける各行に対して特定のアクションが実行されるようにする場合は、INSERTEDおよびDELETED表を使用してそのアクションをコード化する必要があります。
Oracleでは、豊富なトリガーが提供されています。INSERT、UPDATE、DELETEなどのイベントに対して起動されるトリガーもあります。また、トリガー・アクションが実行される回数を指定することもできます。たとえば、トリガーを起動するイベント(多くの行を更新するUPDATEによって起動されるイベントなど)によって影響を受けるすべての行に対して1回、(影響を受ける文の数に関係なく)トリガーを起動する文に対して1回などと指定できます。
ROWトリガーは、トリガーを起動するイベントによって表が影響を受けるたびに起動されます。たとえば、UPDATE文で表の複数の行を更新する場合、ROWトリガーは、UPDATE文によって影響を受ける各行に対して1回起動されます。STATEMENTトリガーは、トリガーを起動する文によって影響を受ける表内の行の数に関係なく、トリガーを起動する文のかわりに1回起動されます。
Oracleのトリガーは、BEFOREトリガーまたはAFTERトリガーのいずれかとして定義できます。BEFOREトリガーは、トリガーを起動する文を完了できるかどうかをトリガー・アクションで判断する場合に使用されます。BEFOREトリガーを使用すると、トリガーを起動する文およびその最終的なロールバックが、例外が発生した場合に必要以上に実行されないようにすることができます。
Oracleには、これらを組み合せた次の4つのタイプのトリガーがあります。
BEFORE STATEMENTトリガー
BEFORE ROWトリガー
AFTER STATEMENTトリガー
AFTER ROWトリガー
ROWトリガーまたはSTATEMENTトリガーを作成して、Microsoft SQL ServerまたはSybase Adaptive Serverのトリガーと同じ機能を使用できるようにする必要がある場合があります。次に、その場合を示します。
トリガーを起動するコードがトリガー自体の表(変更表)から読取りを実行する場合
トリガーを起動するコードにグループ・ファンクションが含まれている場合
次の例では、グループ・ファンクションAVGを使用して平均給与を計算します。
SELECT AVG(inserted.salary) FROM inserted a, deleted b WHERE a.id = b.id;
これをOracleに変換するには、更新されたすべての値をパッケージに挿入するAFTER ROWトリガー、およびパッケージから読取りを行い、平均を計算するAFTER STATEMENTを作成します。
Oracleのトリガーの例については、『Oracle Database 2日で開発者ガイド』を参照してください。
Microsoft SQL ServerまたはSybase Adaptive Serverでは、トリガーおよびストアド・プロシージャがサーバーとともに格納されます。Oracleでは、トリガーおよびストアド・サブプログラムがサーバーとともに格納されます。Oracleには、3種類の異なるストアド・サブプログラム(ファンクション、ストアド・プロシージャおよびパッケージ)があります。これらすべてのオブジェクトの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
ストアド・プロシージャを使用すると、サーバーとともに格納可能なアプリケーション・ロジックを有効にコード化できます。ストアド・プロシージャは、Microsoft SQL ServerまたはSybase Adaptive ServerおよびOracleのすべてで利用できます。
これらのオブジェクトのコード化に使用する言語は、SQLに対するデータベース固有の手続き型拡張言語です。この言語は、OracleではPL/SQLで、Microsoft SQL ServerまたはSybase Adaptive ServerではTransact SQL(T-SQL)です。これらの言語は大幅に異なります。個々のSQL文およびプロシージャの構文(if-then-else
など)は、両方のバージョンの手続き型SQLで類似しています。この項では、両方のバージョンにおける次の項目の相違点を説明します。
また、この項では、変換時に問題が発生しないようにするために、Microsoft SQL ServerまたはSybase Adaptive Serverの通常のストアド・プロシージャの各種コンポーネントについて検討し、それらの設計方法を示します。この項で説明する標準をコーディングに適用すると、ストアド・プロシージャをMicrosoft SQL ServerまたはSybase Adaptive ServerからOracleに変換できます。
個々のSQL文は、可能なかぎり、ANSI規格のSQLに準拠させる必要があります。ただし、使用が簡単、コーディングが単純、パフォーマンスが向上などの理由から、データベース固有のSQL構文を使用する必要がある場合があります。たとえば、次のようなMicrosoft SQL ServerまたはSybase Adaptive Server構文は、SQL ServerまたはSybase Adaptive Server固有です。
update <table_name> set ... from <table1>, <table_name> where...
次の例には、自動変換ユーティリティによって実行されるこのような文の変換を示します。
DELETE sales FROM sales, titles WHERE sales.title_id = titles.title_id AND titles.type = 'business'
DELETE sales WHERE ROWID IN ( SELECT sales.ROWID FROM sales , titles WHERE sales.title_id = titles.title_id AND titles.TYPE = 'business' );
UPDATE titles SET price = price + author_royalty FROM titles, title_author WHERE titles.title.id = title_author.title_id
Microsoft SQL ServerまたはSybase Adaptive Serverでは、トランザクションの定義は明示的です。これは、個々のSQL文がデフォルトでは論理トランザクションの一部ではないことを意味します。SQL文は、ユーザーがBEGIN TRANSACTION(またはBEGIN TRAN)文を使用して明示的に開始したトランザクションが有効な場合、論理トランザクションに属します。論理トランザクションは、対応するCOMMIT TRANSACTION(またはCOMMIT TRAN)あるいはROLLBACK TRANSACTION(またはROLLBACK TRAN)文で終了します。論理トランザクションの一部ではない各SQL文は、完了時にコミットされます。
Oracleでは、トランザクションは、ANSI規格に準拠しているため暗黙的です。暗黙的トランザクション・モデルでは、各SQL文が論理トランザクションの一部である必要があります。COMMITまたはROLLBACKコマンドを実行すると、新しい論理トランザクションが自動的に開始されます。また、これは、個々のSQL文からのデータの変更が実行後データベースにコミットされないことを意味します。この変更は、COMMIT文を実行した場合にのみデータベースにコミットされます。トランザクション・モデルの違いは、アプリケーション・プロシージャのコーディングに影響します。
クライアント/サーバー・アプリケーションでは、トランザクション処理の構文をクライアント・プロシージャの一部にすることをお薦めします。論理トランザクションは、常に、クライアント・ユーザーが定義および制御する必要があります。この方針は、2フェーズ・コミット操作が必要な分散トランザクションにも適用されます。トランザクション処理文をクライアント・コードの一部にすることには、2つの目的があります。サーバー・コードの移植性を高めること、および分散トランザクションをサーバー・コードに依存しないようにすることです。BEGIN TRAN、ROLLBACK TRANおよびCOMMIT TRAN文は、ストアド・プロシージャでは使用しないようにしてください。Microsoft SQL ServerまたはSybase Adaptive Serverでは、トランザクションは明示的です。Oracleでは、トランザクションは暗黙的です。トランザクションがクライアントによって処理される場合は、サーバーに存在するアプリケーション・コードを、トランザクション・モデルに依存しないようにすることができます。
OracleのPL/SQLでは、次の文に進む前に各SQL文のエラーが確認されます。エラーが発生すると、すぐに制御が例外ハンドラに渡されます。これによって、すべてのSQL文の状態を確認する必要がなくなります。たとえば、SELECT文でデータベース内の行が検出されない場合は、例外が発生し、このエラーを処理するコードが実行されます。
Microsoft SQL ServerまたはSybase Adaptive Serverでは、SQL文を実行するたびにエラーは確認されません。前の文によって生成されたエラー状態に関係なく、制御が次の文に渡されます。そのため、SQL文を実行するたびに、ユーザー自身がエラーを確認する必要があります。確認しないと、誤った結果が戻される場合があります。
Oracleで、Microsoft SQL ServerまたはSybase Adaptive Serverの動作をシミュレートし、前のSQL文の実行状態に関係なく制御を次の文に渡すには、各SQL文を、同様に動作するPL/SQLブロックで囲む必要があります。このブロックによって、そのSQL文で発生する可能性があるすべての例外を処理する必要があります。このコーディング・スタイルは、Microsoft SQL ServerまたはSybase Adaptive Serverの動作をシミュレートする場合にのみ必要です。OracleのPL/SQLプロシージャに1つのみの例外ブロックを含め、そのブロックですべてのエラー状態を処理することをお薦めします。
Microsoft SQL ServerまたはSybase Adaptive Serverストアド・プロシージャで次のコードを実行するとします。
begin select @x = col1 from table1 where col2 = @y select @z = col3 from table2 where col4 = @x end
このコード例では、最初のSELECT文が行を戻さない場合、値@x
がUNDEFINED
になります。例外が発生せずに制御が次の文に渡されると、2番目の文では、最初の文によって値@x
が設定されている必要があるため、不適切な結果が戻されます。同様の状況で、最初の文が正常に実行されないと、OracleのPL/SQLではNO_DATA_FOUND例外が発生します。
Microsoft SQL ServerまたはSybase Adaptive ServerのRAISERROR文は、コール元のルーチンに戻されません。エラー・コードおよびエラー・メッセージがクライアントに渡され、ストアド・プロシージャは続行されます。OracleのRAISE_APPLICATION_ERROR文は、コール元のルーチンに戻されます。通常、RETURN文は、Microsoft SQL ServerまたはSybase Adaptive ServerではRAISERROR文の後に指定する必要があるため、OracleのRAISE_APPLICATION_ERROR文に変換できます。
Microsoft SQL ServerまたはSybase Adaptive Serverでは、システム表を使用してエラー・メッセージをカスタマイズできます。システム・プロシージャを使用して、エラー・メッセージをシステムに追加できます。Oracleシステムに同等の機能がないため、エラー・メッセージは、Microsoft SQL ServerまたはSybase Adaptive Serverのシステム表には追加しないことをお薦めします。中央データベースにあるユーザー定義のエラー・メッセージ表をメンテナンスすることで、この操作を行う必要がなくなります。標準ルーチンは、エラー・メッセージを表に追加して必要に応じて取得できるように作成できます。この方法には、2つの目的があります。異なるタイプのデータベース・サーバー間でのシステムの移植性を高めること、および管理者がエラー・メッセージを集中制御できるようにすることです。
T-SQLのローカル変数は、TEXTおよびIMAGE以外の任意のデータ型にできます。PL/SQLのローカル変数は、任意のデータ型にできます。次に例を示します。
BINARY_INTEGER
BOOLEAN
また、PL/SQLのローカル変数は、PL/SQLで使用可能な次の複合データ型のいずれかにできます。
RECORD
TABLE
Microsoft SQL ServerまたはSybase Adaptive Serverのデータ型およびそれらに相当するOracleのデータ型のリストは、「データ型」を参照してください。
Microsoft SQL ServerまたはSybase Adaptive Serverには、複合データ型はありません。
この項では、Microsoft SQL ServerまたはSybase Adaptive ServerとOracleの次のスキーマ・オブジェクトを比較します。
各スキーマ・オブジェクトの作成、削除、実行および変更(該当する場合)について個別の表で比較します。次のセクションに分類して説明します。
構文
説明
権限
例
一部の表では、変換による影響の重要な情報を含む推奨事項のセクションが後に続きます。
この項では、プロシージャの次の操作について説明します。
表3-2 OracleとMicrosoft SQL ServerまたはSybase Adaptive Serverでのプロシージャ・スキーマ・オブジェクトの作成方法の比較
Microsoft SQL ServerまたはSybase Adaptive Server | Oracle |
---|---|
構文: CREATE PROCEDURE procedure [@formal_parameter formal_parameter_data type [= default_value] [OUTPUT] [,@formal_parameter formal_parameter_data type [= default_value] [OUTPUT]] ... AS BEGIN] procedural_statements [END] |
構文: CREATE [OR REPLACE] PROCEDURE [schema.]procedure [(] [formal_parameter [IN | OUT | IN OUT] formal_parameter_data type] [DEFAULT default_value] [,formal_parameter [IN | OUT | IN OUT]formal_parameter_data type] [DEFAULT default_value]] ... [)] IS | AS [local_variable data type;]... BEGIN PL/SQL statements | PL/SQL blocks END; |
説明: CREATE PROCEDURE文は、名前付きのストアド・プロシージャをデータベースに作成します。 オプションで、プロシージャにOUTPUTとして渡されるパラメータを指定できます。OUTPUT変数の値は、プロシージャの実行後、コール元のルーチンで使用できます。OUTPUTキーワードなしで指定されたパラメータは、入力パラメータとみなされます。 キーワードASは、プロシージャ本体の開始点を示します。 ストアド・プロシージャ本体を囲むBEGINおよびENDキーワードはオプションです。ファイル内のASの後に含まれているすべてのプロシージャ文は、BEGINおよびENDがブロックのマークに使用されていない場合、ストアド・プロシージャの一部とみなされます。 T-SQLプロシージャで使用可能な構文の詳細は、この章の「T-SQLおよびPL/SQLの言語要素」の項を参照してください。 |
説明: OR REPLACEキーワードは、プロシージャがすでに存在している場合は、新しい定義で置き換えます。 PL/SQLプロシージャに渡されるパラメータは、IN(入力)、OUT(出力のみ)またはIN OUT(入出力)と指定できます。これらのキーワードを指定しない場合、このパラメータはINと想定されます。 キーワードISは、プロシージャの開始点を示します。ローカル変数は、キーワードISまたはASとキーワードBEGINの間で宣言します。 BEGINキーワードとENDキーワードは、プロシージャ本体を囲みます。 |
権限: ストアド・プロシージャを作成するには、CREATE PROCEDUREシステム権限が必要です。Microsoft SQL Server 2005の場合は、ファンクションが作成されているスキーマに対するALTER権限も必要です。 |
権限: 自分のスキーマにプロシージャを作成するには、CREATE PROCEDUREシステム権限が必要です。別のユーザーのスキーマにプロシージャを作成するには、CREATE ANY PROCEDUREシステム権限が必要です。 |
推奨事項:
T-SQLプロシージャとPL/SQLプロシージャの構造の機能的に同一の部分を識別することができます。したがって、ほぼすべての構文をMicrosoft SQL ServerまたはSybase Adaptive ServerからOracleへ自動的に変換できます。
OracleのCREATE PROCEDURE文にOR REPLACEキーワードを使用すると、効率的にプロシージャを再作成できます。Microsoft SQL ServerまたはSybase Adaptive Serverでは、プロシージャを明示的に削除した後で置き換える必要があります。
表3-3 OracleとMicrosoft SQL ServerまたはSybase Adaptive Serverでのプロシージャ・スキーマ・オブジェクトの削除方法の比較
Microsoft SQL ServerまたはSybase Adaptive Server | Oracle |
---|---|
構文: DROP PROCEDURE procedure |
構文: DROP PROCEDURE [schema.]procedure |
説明: プロシージャの定義がデータ・ディクショナリから削除されます。このプロシージャを参照するすべてのオブジェクトで、このプロシージャへの参照を削除する必要があります。 |
説明: プロシージャが削除されると、削除されたプロシージャを参照するすべてのローカル・オブジェクトが無効になります。 |
権限: プロシージャ所有者は、所有者自身のプロシージャを削除できます。DBOは、すべてのプロシージャを削除できます。 |
権限: このコマンドを実行するには、プロシージャがユーザーのスキーマに存在するか、またはユーザーがDROP ANY PROCEDUREシステム権限を所有している必要があります。 |
例: DROP PROCEDURE myproc |
例: DROP PROCEDURE sam.credit; |
推奨事項:
前述の文は、変換処理には影響しません。この情報は、参照用にのみ提供されています。
OracleとMicrosoft SQL ServerまたはSybase Adaptive Serverでのプロシージャ・スキーマ・オブジェクトの実行方法の比較
Microsoft SQL ServerまたはSybase Adaptive Server | Oracle |
---|---|
構文: EXEC [@return_value = ] procedure [[@formal_parameter = ] {@actual_parameter | constant_literal} [OUTPUT]] [,[[@formal_parameter = ] {@actual_parameter | constant_literal} [OUTPUT]]] ... |
構文: procedure [([{actual_parameter | constant_literal | formal_parameter => {actual_parameter | constant_literal} }] [,{actual_parameter | constant_literal | formal_parameter => {actual_parameter | constant_literal} }] .... )] |
説明: Microsoft SQL ServerまたはSybase Adaptive Serverのストアド・プロシージャは、RETURN文を使用して、コール元のルーチンに整数値のみを戻すことができます。RETURN文を使用しない場合でも、ストアド・プロシージャはコール元のルーチンに戻り状態を戻します。この値は、return_value変数で取得できます。 formal_parameterは、プロシージャの定義内のパラメータです。actual_parameterは、実パラメータの値をそれぞれの仮パラメータに代入してプロシージャをコールするローカル・ブロックに定義されます。実パラメータと仮パラメータの対応付けは、位置表記法または名前表記法のいずれかを使用して指定できます。 |
説明: OracleのPL/SQLプロシージャは、OUTパラメータによってコール元のルーチンにデータを戻します。Oracleでは、スキーマ・オブジェクトの型が異なるファンクションが提供されています。ファンクションは、RETURN文を使用してコール元のルーチンにアトム型の値を戻すことができます。RETURN文は、任意のデータ型の値を戻すことができます。 formal_parameterは、プロシージャの定義内のパラメータです。actual_parameterは、実パラメータの値をそれぞれの仮パラメータに代入してプロシージャをコールするローカル・ブロックに定義されます。実パラメータと仮パラメータの対応付けは、位置表記法または名前表記法のいずれかを使用して指定できます。 |
位置表記法: 実パラメータは、プロシージャの定義内の仮パラメータと同じ順序でプロシージャに適用されます。 名前表記法: 実パラメータは、仮パラメータの名前を次のように使用して、プロシージャの定義内の仮パラメータの順序とは異なる順序でプロシージャに適用されます。 @formal_parameter = @actual_parameter 定数リテラルは、次の位置に指定できます。 '@actual_parameter ' as: @formal_parameter = 10 @formal_parameter = 10 キーワードOUTPUTは、プロシージャでそのパラメータの値をコール元のルーチンにOUTPUTとして戻す必要がある場合に指定する必要があります。 |
位置表記法: 実パラメータは、プロシージャの定義内の仮パラメータと同じ順序でプロシージャに適用されます。 名前表記法: 実パラメータは、仮パラメータの名前を次のように使用して、プロシージャの定義内の仮パラメータの順序とは異なる順序でプロシージャに適用されます。 formal_parameter => actual_parameter 定数リテラルは、次の位置に指定できます。 as: formal_parameter => 10 formal_parameterがプロシージャの定義内にOUTまたはIN OUTとして指定されている場合は、プロシージャの実行後、その値をコール元のルーチンで使用できます。 |
権限: ユーザーは、ストアド・プロシージャに対するEXECUTE権限を所有している必要があります。ストアド・プロシージャ内で参照される、基礎となるオブジェクトにアクセスするための明示的な権限は必要ありません。 |
権限 ユーザーは、名前付きのプロシージャに対するEXECUTE権限を所有している必要があります。PL/SQLプロシージャ内で参照される、基礎となるオブジェクトにアクセスするための明示的な権限は必要ありません。 |
例: 位置表記法: EXEC GetEmplName @EmpID EXEC @status = GetAllDeptCodes EXEC @status = UpdateEmpSalary @EmpID, @EmpName EXEC UpdateEmpSalary 13000,'Joe Richards' 名前表記法: EXEC UpdateEmpSalary @Employee = @EmpName, @Employee_Id = @EmpID 混合表記法: EXEC UpdateEmpSalary @EmpName, @Employee_Id = @EmpID EXEC UpdateEmpSalary @Employee = @EmpName, @EmpID |
例: 位置表記法: credit (accno, accname, amt, retstat); 名前表記法: credit (acc_no => accno, acc => accname, amount => amt, return_status => retstat) 混合表記法(位置表記法を名前表記法より先に指定する必要があります): credit (accno, accname, amount => amt, return_status => retstat) |
表3-5 OracleとMicrosoft SQL ServerまたはSybase Adaptive Serverでのプロシージャ・スキーマ・オブジェクトの変更方法の比較
Microsoft SQL ServerまたはSybase Adaptive Server | Oracle |
---|---|
構文: Microsoft SQL Serverのみ。Sybase Adaptive Serverでは、ALTER PROCEDURE文は提供されません。 ALTER PROCEDURE procedure_name [ @parameter data_type [ = default ] [ OUTPUT ] [, @parameter data_type [ = default ] [ OUTPUT ]] … ] [ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ] [ FOR REPLICATION ] AS [BEGIN] Statements [END] システム・プロシージャSP_RECOMPILEは、名前付きのストアド・プロシージャを再コンパイルします。例: ALTER PROCEDURE update_employee_salary @employee_id INT, @salary FLOAT AS UPDATE employee SET salary = @salary WHERE employee_id = @employee_id; sp_recompileシステム・ストアド・プロシージャを使用してプロシージャを再コンパイルするには、次のようにします。 sp_recompile my_proc |
構文: ALTER PROCEDURE [schema.]procedure COMPILE |
説明: このコマンドを実行すると、プロシージャが再コンパイルされます。なんらかの理由で無効になっているプロシージャは、このコマンドを使用して明示的に再コンパイルする必要があります。 |
説明: このコマンドを実行すると、プロシージャが再コンパイルされます。なんらかの理由で無効になっているプロシージャは、このコマンドを使用して明示的に再コンパイルする必要があります。明示的に再コンパイルすることによって、暗黙的に再コンパイルする必要がなくなるため、それに関連する実行時コンパイル・エラーおよびパフォーマンス上のオーバーヘッドの発生を防止できます。 |
権限: このコマンドを発行できるのは、プロシージャの所有者です。 |
権限: このコマンドを使用するには、プロシージャがユーザーのスキーマに存在するか、またはユーザーがALTER ANY PROCEDURE権限を所有している必要があります。 |
例: sp_recompile my_proc |
例: ALTER PROCEDURE sam.credit COMPILE; |
この項では、ファンクションの次の操作について説明します。
表3-6 OracleとMicrosoft SQL ServerまたはSybase Adaptive Serverでのファンクション・スキーマ・オブジェクトの作成方法の比較
Microsoft SQL ServerまたはSybase Adaptive Server | Oracle |
---|---|
構文: Microsoft SQL ServerまたはSybase Adaptive Serverでは、ストアド・プロシージャでRETURN文を使用してコール元のルーチンに整数値を戻すことができるため、ストアド・プロシージャをOracleのファンクションに変換できます。ストアド・プロシージャは、RETURN文を使用しない場合でも、コール元のルーチンに状態値を戻します。戻される状態は、プロシージャが正常に実行され場合はZERO、なんらかの理由で正常に実行されなかった場合はNON-ZEROと同等になります。RETURN文は、整数値のみを戻すことができます。 |
構文: CREATE [OR REPLACE] FUNCTION [user.]function [(parameter [OUT] data type[,(parameter [IN OUT] data type]...)] RETURN data type {IS|AS} block |
なし |
説明: OR REPLACEキーワードは、ファンクションがすでに存在している場合は、新しい定義で置き換えます。 PL/SQLファンクションに渡されるパラメータは、IN(入力)、OUT(出力)またはIN OUT(入出力)と指定できます。これらのキーワードを指定しない場合、このパラメータはINと想定されます。 RETURNデータ型は、ファンクションの戻り値のデータ型を指定します。データ型には、PL/SQLでサポートされている任意のデータ型を指定できます。データ型の詳細は、「データ型」を参照してください。 |
なし |
権限: 自分のスキーマにファンクションを作成するには、CREATE PROCEDUREシステム権限が必要です。別のユーザーのスキーマにファンクションを作成するには、CREATE ANY PROCEDUREシステム権限が必要です。 |
なし |
例: CREATE FUNCTION get_bal (acc_no IN NUMBER) RETURN NUMBER IS acc_bal NUMBER(11,12); BEGIN SELECT balance INTO acc_bal FROM accounts WHERE account_id = acc_no; RETURN(acc_bal); END; |
表3-7 OracleとMicrosoft SQL ServerまたはSybase Adaptive Serverでのファンクション・スキーマ・オブジェクトの削除方法の比較
Microsoft SQL ServerまたはSybase Adaptive Server | Oracle |
---|---|
なし |
構文: DROP FUNCTION [schema.]function |
なし |
説明: ファンクションが削除されると、削除されたファンクションを参照するすべてのローカル・オブジェクトが無効になります。 |
なし |
権限: このコマンドを実行するには、ファンクションがユーザーのスキーマに存在するか、またはユーザーがDROP ANY PROCEDUREシステム権限を所有している必要があります。 |
なし |
例: DROP FUNCTION sam.credit; |
表3-8 OracleとMicrosoft SQL ServerまたはSybase Adaptive Serverでのファンクション・スキーマ・オブジェクトの実行方法の比較
Microsoft SQL ServerまたはSybase Adaptive Server | Oracle |
---|---|
なし |
構文: function [({actual_parameter | constant_literal}...)] |
なし |
説明: ファンクションは、RETURN文を使用してコール元のルーチンにアトム型の値を戻すことができます。 ファンクションは、式の一部としてコールできます。これは、非常に優れた概念です。Microsoft SQL ServerまたはSybase Adaptive Serverのすべての組込みファンクションは、PL/SQLを使用してコード化できます。これらのファンクションは、Oracleを使用して起動し、式内の他の組込みファンクション同様にコールできます。 |
なし |
権限: 名前付きのファンクションを実行するには、そのファンクションに対するEXECUTE権限が必要です。PL/SQLファンクション内で参照される、基礎となるオブジェクトにアクセスするための明示的な権限は必要ありません。 |
なし |
例: 1) IF sal_ok (new_sal, new_title) THEN .... END IF; 2) promotable:= sal_ok(new_sal, new_title) AND (rating>3); ここで、sal_okはBOOLEAN値を戻すファンクションです。 |
表3-9 OracleとMicrosoft SQL ServerまたはSybase Adaptive Server 7.0でのファンクション・スキーマ・オブジェクトの変更方法の比較
Microsoft SQL ServerまたはSybase Adaptive Server | Oracle |
---|---|
なし |
構文: ALTER FUNCTION [schema.]function COMPILE |
なし |
説明: このコマンドを実行すると、ファンクションが再コンパイルされます。ファンクションは、ファンクション内から参照されるオブジェクトが削除または変更されると無効になります。なんらかの理由で無効になっているファンクションは、このコマンドを使用して明示的に再コンパイルする必要があります。明示的に再コンパイルすることによって、暗黙的に再コンパイルする必要がなくなるため、それに関連する実行時コンパイル・エラーおよびパフォーマンス上のオーバーヘッドの発生を防止できます。 |
なし |
権限: このコマンドを使用するには、ファンクションがユーザーのスキーマに存在するか、またはユーザーがALTER ANY PROCEDURE権限を所有している必要があります。 |
なし |
例: ALTER FUNCTION sam.credit COMPILE |
この項では、パッケージの次の操作について説明します。
表3-10 OracleとMicrosoft SQL ServerまたはSybase Adaptive Serverでのパッケージ・スキーマ・オブジェクトの作成方法の比較
Microsoft SQL ServerまたはSybase Adaptive Server | Oracle |
---|---|
構文: パッケージは、Microsoft SQL ServerまたはSybase Adaptive Serverではサポートされていません。 |
構文: CREATE [OR REPLACE] PACKAGE [user.]package {IS | AS} {variable_declaration | cursor_specification | exception_declaration | record_declaration | plsql_table_declaration | procedure_specification | function_specification | [{variable_declaration | cursor_specification | exception_declaration | record_declaration | plsql_table_declaration | procedure_specification | function_specification}; ]...} END [package] |
なし |
説明: これは、パッケージの外部(公開部分)です。 CREATE PACKAGEは、プロシージャ、ファンクション、例外、変数、定数およびカーソルのグループとして構成できるPL/SQLパッケージの仕様部を設定します。 パッケージのファンクションおよびプロシージャは、変数、定数およびカーソルを介してデータを共有できます。 OR REPLACEキーワードは、パッケージがすでに存在している場合は、新しい定義で置き換えます。これを行うには、パッケージおよびパッケージ仕様部に依存するすべてのオブジェクトを再コンパイルする必要があります。 |
なし |
権限: 自分のスキーマにパッケージを作成するには、CREATE PROCEDUREシステム権限が必要です。別のユーザーのスキーマにパッケージを作成するには、CREATE ANY PROCEDUREシステム権限が必要です。 |
なし |
例: CREATE PACKAGE emp_actions AS -- specification TYPE EmpRecTyp IS RECORD (emp_id INTEGER, salary REAL); CURSOR desc_salary (emp_id NUMBER) RETURN EmpRecTyp; PROCEDURE hire_employee (ename CHAR, job CHAR, mgr NUMBER, sal NUMBER, comm NUMBER, deptno NUMBER); PROCEDURE fire-employee (emp_id NUMBER); END emp_actions; |
表3-11 OracleとMicrosoft SQL ServerまたはSybase Adaptive Serverでのパッケージ・スキーマ・オブジェクトの削除方法の比較
Microsoft SQL ServerまたはSybase Adaptive Server | Oracle |
---|---|
構文: パッケージは、Microsoft SQL ServerまたはSybase Adaptive Serverではサポートされていません。 |
構文: DROP PACKAGE [BODY] [schema.]package |
なし |
説明: BODYオプションを指定すると、パッケージ本体のみが削除されます。BODYを指定しない場合は、パッケージの本体と仕様部の両方が削除されます。パッケージの本体および仕様部を削除すると、パッケージ仕様部に依存するすべてのローカル・オブジェクトが無効になります。
パッケージが削除されると、削除されたパッケージを参照するすべてのローカル・オブジェクトが無効になります。 |
なし |
権限: このコマンドを実行するには、パッケージがユーザーのスキーマに存在するか、またはユーザーがDROP ANY PROCEDUREシステム権限を所有している必要があります。 |
なし |
例: DROP PACKAGE emp_actions; |
表3-12 OracleとMicrosoft SQL ServerまたはSybase Adaptive Serverでのパッケージ・スキーマ・オブジェクトの変更方法の比較
Microsoft SQL ServerまたはSybase Adaptive Server | Oracle |
---|---|
構文: パッケージは、Microsoft SQL ServerまたはSybase Adaptive Serverではサポートされていません。 |
構文: ALTER PACKAGE [user.]package COMPILE [PACKAGE | BODY] |
なし |
説明: なんらかの理由で無効になっているパッケージは、このコマンドを使用して明示的に再コンパイルする必要があります。 このコマンドを実行すると、すべてのパッケージ・オブジェクトがまとめて再コンパイルされます。ALTER PROCEDUREまたはALTER FUNCTIONコマンドを使用して、パッケージの一部であるプロシージャまたはファンクションを個々に再コンパイルすることはできません。 PACKAGE(デフォルトのオプション)を指定すると、パッケージの本体および仕様部が再コンパイルされます。 BODYを指定すると、パッケージ本体のみが再コンパイルされます。 明示的に再コンパイルすることによって、暗黙的に再コンパイルする必要がなくなるため、それに関連する実行時コンパイル・エラーおよびパフォーマンス上のオーバーヘッドの発生を防止できます。 |
なし |
権限: このコマンドを使用するには、パッケージがユーザーのスキーマに存在するか、またはユーザーがALTER ANY PROCEDURE権限を所有している必要があります。 |
なし |
例: ALTER PACKAGE emp_actions COMPILE PACKAGE |
この項では、パッケージ本体の次の操作について説明します。
表3-13 OracleとMicrosoft SQL ServerまたはSybase Adaptive Serverでのパッケージ本体スキーマ・オブジェクトの作成方法の比較
Microsoft SQL ServerまたはSybase Adaptive Server | Oracle |
---|---|
構文: パッケージは、Microsoft SQL ServerまたはSybase Adaptive Serverではサポートされていません。 |
構文: CREATE [OR REPLACE] PACKAGE BODY [schema.]package {IS | AS} pl/sql_package_body |
なし |
説明: これは、パッケージの内部(非公開部分)です。 CREATE PACKAGEは、ストアド・パッケージの本体を作成します。 OR REPLACEは、パッケージ本体がすでに存在する場合、再作成します。パッケージ本体は、変更すると再コンパイルされます。
packageは、作成されるパッケージです。
|
なし |
権限: 自分のスキーマにパッケージを作成するには、CREATE PROCEDURE権限が必要です。別のユーザーのスキーマにパッケージを作成するには、CREATE ANY PROCEDURE権限が必要です。 |
なし |
例: CREATE PACKAGE BODY emp_actions AS -- body CURSOR desc_salary (emp_id NUMBER) RETURN EmpRecTyp IS SELECT empno, sal FROM emp ORDER BY sal DESC; PROCEDURE hire_employee (ename CHAR, job CHAR, mgr NUMBER, sal NUMBER, comm NUMBER, deptno NUMBER) IS BEGIN INSERT INTO emp VALUES (empno_seq.NEXTVAL, ename, job, mgr, SYSDATE, sal, comm, deptno); END hire_employee; PROCEDURE fire_employee (emp_id NUMBER) IS BEGIN DELETE FROM emp WHERE empno = emp_id; END fire_employee; END emp_actions; |
表3-14 OracleとMicrosoft SQL ServerまたはSybase Adaptive Serverでのパッケージ本体スキーマ・オブジェクトの削除方法の比較
Microsoft SQL ServerまたはSybase Adaptive Server | Oracle |
---|---|
構文: パッケージは、Microsoft SQL ServerまたはSybase Adaptive Serverではサポートされていません。 |
構文: DROP PACKAGE [BODY] [schema.]package |
なし |
説明:
パッケージが削除されると、削除されたパッケージを参照するすべてのローカル・オブジェクトが無効になります。 |
なし |
権限: このコマンドを実行するには、パッケージがユーザーのスキーマに存在するか、またはユーザーがDROP ANY PROCEDUREシステム権限を所有している必要があります。 |
なし |
例: DROP PACKAGE BODY emp_actions; |
表3-15 OracleとMicrosoft SQL ServerまたはSybase Adaptive Serverでのパッケージ本体スキーマ・オブジェクトの変更方法の比較
Microsoft SQL ServerまたはSybase Adaptive Server | Oracle |
---|---|
構文: パッケージは、Microsoft SQL ServerまたはSybase Adaptive Serverではサポートされていません。 |
構文: ALTER PACKAGE [user.]package COMPILE [PACKAGE | BODY] |
なし |
説明: なんらかの理由で無効になっているパッケージは、このコマンドを使用して明示的に再コンパイルする必要があります。 このコマンドを実行すると、すべてのパッケージ・オブジェクトがまとめて再コンパイルされます。ALTER PROCEDUREまたはALTER FUNCTIONコマンドを使用して、パッケージの一部であるプロシージャまたはファンクションを個々に再コンパイルすることはできません。 PACKAGE(デフォルトのオプション)を指定すると、パッケージの本体および仕様部が再コンパイルされます。 BODYを指定すると、パッケージ本体のみが再コンパイルされます。 明示的に再コンパイルすることによって、暗黙的に再コンパイルする必要がなくなるため、それに関連する実行時コンパイル・エラーおよびパフォーマンス上のオーバーヘッドの発生を防止できます。 |
なし |
権限: このコマンドを使用するには、パッケージが自分のスキーマであるか、またはユーザーがALTER ANY PROCEDURE権限を所有している必要があります。 |
なし |
例: ALTER PACKAGE emp_actions COMPILE BODY |
この項では、Microsoft SQL ServerまたはSybase Adaptive Serverの構文、およびそれに相当し、SQL Developerで生成されるOracleの構文について説明します。次の構文の変換について説明します。
表には、Microsoft SQL ServerまたはSybase Adaptive Serverの構文、およびそれらに相当するOracleの構文を示します。また、変換に関する考慮点についてのコメントも示します。
Oracleの列に示すプロシージャは、SQL Developerによって直接出力されます。通常、SQL Developerは、次のいずれかの方法でMicrosoft SQL ServerまたはSybase Adaptive ServerのT-SQL構文を処理します。
ANSI規格のSQL文がサポートされているため、そのようなSQL文はPL/SQLに変換されます。
Microsoft SQL ServerまたはSybase Adaptive Server固有の構文は、PL/SQLで同等の構文が使用可能な場合、PL/SQL構文に変換されます。
一部のMicrosoft SQL ServerまたはSybase Adaptive Server固有の構文は無視され、適切なコメントが出力ファイルに組み込まれます。
手動で変換する必要がある構文は、出力ファイル内に適切なコメントでラップされます。
構文エラーが発生するMicrosoft SQL ServerまたはSybase Adaptive Server固有の構文の場合は、行番号を含む適切なエラー・メッセージが表示されます。
表3-16 OracleとMicrosoft SQL ServerまたはSybase Adaptive ServerのCREATE PROCEDURE文の比較
Microsoft SQL ServerまたはSybase Adaptive Server | Oracle |
---|---|
CREATE PROC proc1 AS RETURN 0 |
CREATE OR REPLACE FUNCTION proc1 RETURN NUMBER AS BEGIN RETURN 0; END; |
コメント
プロシージャ、ファンクションまたはパッケージがすでに存在している場合、REPLACEキーワードはそれらを置き換えるために追加されます。
プロシージャは、値を戻すため、Oracleのファンクションに変換されます。
表3-17 OracleとMicrosoft SQL ServerまたはSybase Adaptive Serverのパラメータの受渡しの比較
Microsoft SQL ServerまたはSybase Adaptive Server | Oracle |
---|---|
CREATE PROC proc1 @x int=-1, @y money, @z bit OUT, @a char(20) = 'TEST' AS RETURN 0 |
CREATE OR REPLACE FUNCTION proc1 ( v_x IN NUMBER DEFAULT -1, v_y IN NUMBER, v_z OUT NUMBER, v_a IN CHAR DEFAULT 'TEST' ) RETURN NUMBER AS BEGIN RETURN 0; END; |
コメント
パラメータの受渡しは、Microsoft SQL ServerまたはSybase Adaptive ServerとOracleでほぼ同じです。デフォルトでは、特別に指定されていないかぎり、すべてのパラメータがINPUTパラメータです。
INPUTパラメータは、PL/SQLプロシージャ内からは変更できません。このため、INPUTパラメータは、値を割り当てることも、別のプロシージャにOUTパラメータとして渡すこともできません。Oracleでは、INパラメータにのみデフォルトの値を割り当てることができます。
Oracleでは、パラメータ名宣言内の@マークは削除されます。
Oracleでは、パラメータのデータ型定義に長さ/サイズは含まれません。
Microsoft SQL ServerまたはSybase Adaptive Serverのデータ型は、Oracleベースのデータ型に変換されます。たとえば、Oracleでは、Microsoft SQL ServerまたはSybase Adaptive Serverのすべての数値データ型はNUMBERに、すべての英数字データはVARCHAR2およびCHARに変換されます。
表3-18 OracleとMicrosoft SQL ServerまたはSybase Adaptive ServerのDECLARE文の比較
Microsoft SQL ServerまたはSybase Adaptive Server | Oracle |
---|---|
CREATE PROC proc1 AS DECLARE @x int, @y money, @z bit, @a char(20) RETURN 0 GO |
CREATE OR REPLACE PROCEDURE proc1 AS v_x NUMBER(10,0); v_y NUMBER(19,2); v_z NUMBER(1,0); v_a CHAR(20); BEGIN RETURN; END; |
コメント
ローカル変数を宣言する場合、Microsoft SQL ServerまたはSybase Adaptive ServerとOracleは同様のルールに従います。
変数宣言の有効範囲規則は、SQL Developerによって上書きされます。そのため、Oracleでは、すべてのローカル変数はパッケージ本体の最上位に定義されます。
表3-19 OracleとMicrosoft SQL ServerまたはSybase Adaptive ServerのIF文の比較
Microsoft SQL ServerまたはSybase Adaptive Server | Oracle |
---|---|
例1: CREATE PROC proc1 @Flag int = 0 AS BEGIN DECLARE @x int IF ( @Flag=0 ) SELECT @x = -1 ELSE SELECT @x = 10 END |
例1: CREATE OR REPLACE PROCEDURE proc1 ( v_Flag IN NUMBER DEFAULT 0 ) AS v_x NUMBER(10,0); BEGIN IF ( v_Flag = 0 ) THEN v_x := -1; ELSE v_x := 10; END IF |
例2: CREATE PROC proc1 @Flag char(2) = '' AS BEGIN DECLARE @x int IF ( @Flag='' ) SELECT @x = -1 ELSE IF (@Flag = 'a') SELECT @x = 10 ELSE IF (@Flag = 'b') SELECT @x = 20 END |
例2: CREATE OR REPLACE PROCEDURE proc1 ( v_Flag IN CHAR DEFAULT '' ) AS v_x NUMBER(10,0); BEGIN IF ( v_Flag = '' ) THEN v_x := -1; ELSE IF ( v_Flag = 'a' ) THEN v_x := 10; ELSE IF ( v_Flag = 'b' ) THEN v_x := 20; END IF; END IF; END IF; END; |
例3: CREATE PROC proc1 AS BEGIN DECLARE @x int IF EXISTS ( SELECT * FROM table2 ) SELECT @x = -1 END |
例3: CREATE OR REPLACE PROCEDURE proc1 AS v_x NUMBER(10,0); v_temp NUMBER(1, 0) := 0; BEGIN SELECT 1 INTO v_temp FROM DUAL WHERE EXISTS ( SELECT * FROM table2 ); IF v_temp = 1 THEN v_x := -1; END IF; END; |
例4: CREATE PROC proc1 @basesal money, @empid int AS BEGIN IF (select sal from emp where empid = @empid) < @basesal UPDATE emp SET sal_flag = -1 WHERE empid = @empid END |
例4: CREATE OR REPLACE PROCEDURE proc1 ( v_basesal IN NUMBER, v_empid IN NUMBER ) AS v_temp NUMBER(1, 0) := 0; BEGIN SELECT 1 INTO v_temp FROM DUAL WHERE ( SELECT sal FROM emp WHERE empid = v_empid ) < v_basesal; IF v_temp = 1 THEN UPDATE emp SET sal_flag = -1 WHERE empid = v_empid; END IF; END; |
コメント
Microsoft SQL ServerまたはSybase Adaptive ServerとOracleのIF文は、次の2つの場合を除いてほぼ同じです。
表3-20 OracleとMicrosoft SQL ServerまたはSybase Adaptive ServerのRETURN文の比較
Microsoft SQL ServerまたはSybase Adaptive Server | Oracle |
---|---|
CREATE PROC proc1 @x int AS IF @x = -1 RETURN 25022 ELSE RETURN 25011 |
CREATE OR REPLACE FUNCTION proc1 ( v_x IN NUMBER ) AS BEGIN IF v_x = -1 THEN RETURN 25022; ELSE RETURN 25011; END IF; END; |
コメント
RETURN文は、コール元のプログラムに単一の値を戻すために使用され、両方のデータベースで同様に動作します。Microsoft SQL ServerまたはSybase Adaptive Serverでは数値データ型のみを戻すことができ、Oracleではサーバー・データ型またはPL/SQLデータ型のいずれかを戻すことができます。
PL/SQLプロシージャでは、RETURN文によって、データを戻さずにコール元のプログラムに制御のみを戻すことができます。SQL Developerは、値を戻すプロシージャを自動的にファンクションに変換します。
表3-21 OracleとMicrosoft SQL ServerまたはSybase Adaptive ServerのRAISERROR文の比較
Microsoft SQL ServerまたはSybase Adaptive Server | Oracle |
---|---|
CREATE PROC proc1 AS RAISERROR 12345 "No Employees found" |
CREATE OR REPLACE PROCEDURE PROC1 AS BEGIN raise_application_error(-20999, 'No Employees found'); END PROC1; |
コメント
Microsoft SQL ServerまたはSybase Adaptive Serverでは、RAISERRORを使用して、エラーが発生したことをクライアント・プログラムに通知します。この文はプロシージャの実行を終了しないため、次の文に制御が渡されます。
PL/SQLでは、RAISE_APPLICATION_ERROR文で同様の機能が提供されます。ただし、この文は、ストアド・サブプログラムの実行を終了し、コール元のプログラムに制御を戻します。RAISERROR文とRETURN文を組み合せた場合と同様に動作します。
表3-22 OracleとMicrosoft SQL ServerまたはSybase Adaptive ServerのEXECUTE文の比較
Microsoft SQL ServerまたはSybase Adaptive Server | Oracle |
---|---|
CREATE PROC proc1 AS EXEC SetExistFlag EXEC SetExistFlag yes=@yes, @Status OUT EXEC @Status = RecordExists EXEC SetExistFlag @yes |
CREATE OR REPLACE PROCEDURE proc1 AS BEGIN SetExistFlag; SetExistFlag(yes=>v_yes,Status); Status:=RecordExists; SetExistFlag(v_yes); END proc1; |
コメント
EXECUTE文は、プロシージャ内から別のストアド・プロシージャを実行するために使用されます。PL/SQLでは、プロシージャは、PL/SQLブロック内からその名前でコールされます。
SQL Developerは、パラメータをコールする場合の表記法を位置表記法、名前表記法または混合表記法に変換します。パラメータをコールする場合の表記法については、「スキーマ・オブジェクト」を参照してください。
表3-23 OracleとMicrosoft SQL ServerまたはSybase Adaptive ServerのWHILE文の比較
Microsoft SQL ServerまたはSybase Adaptive Server | Oracle |
---|---|
例1: CREATE PROC proc1 @i int AS WHILE @i > 0 BEGIN print 'Looping inside WHILE....' SELECT @i = @i + 1 END |
例1: CREATE OR REPLACE PROCEDURE proc1 ( iv_i IN NUMBER ) AS v_i NUMBER(10,0) :=iv_i; BEGIN WHILE v_i > 0 LOOP BEGIN DBMS_OUTPUT.PUT_LINE('Looping inside WHILE....'); v_i := v_i + 1; END; END LOOP; END; |
例2: CREATE PROC proc1 @i int, @y int AS WHILE @i > 0 BEGIN print 'Looping inside WHILE....' SELECT @i = @i + 1 END |
例2: CREATE OR REPLACE PROCEDURE proc1 ( iv_i IN NUMBER, v_y IN NUMBER ) AS v_i NUMBER(10,0):=iv_i; BEGIN WHILE v_i > 0 LOOP BEGIN DBMS_OUTPUT.PUT_LINE('Looping inside WHILE....'); v_i := v_i + 1; END; END LOOP; END; |
例3: CREATE PROC proc1 AS DECLARE @sal money SELECT @sal = 0 WHILE EXISTS(SELECT * FROM emp where sal < @sal ) BEGIN SELECT @sal = @sal + 99 DELETE emp WHERE sal < @sal END GO |
例3: CREATE OR REPLACE PROCEDURE proc1 AS v_sal NUMBER(19,2); v_temp NUMBER(1, 0) := 0; BEGIN v_sal := 0; LOOP v_temp := 0; SELECT 1 INTO v_temp FROM DUAL WHERE EXISTS ( SELECT * FROM emp WHERE sal < v_sal ); IF v_temp != 1 THEN EXIT; END IF; BEGIN v_sal := v_sal + 99; DELETE emp WHERE sal < v_sal; END; END LOOP; END; |
例4: CREATE PROC proc1 AS DECLARE @sal money WHILE (SELECT count (*) FROM emp ) > 0 BEGIN SELECT @sal = max(sal) from emp WHERE stat = 1 DELETE emp WHERE sal < @sal END GO |
例4: CREATE OR REPLACE PROCEDURE proc1 AS v_sal NUMBER(19,2); v_temp NUMBER(1, 0) := 0; BEGIN LOOP v_temp := 0; SELECT 1 INTO v_temp FROM DUAL WHERE ( SELECT COUNT(*) FROM emp ) > 0; IF v_temp != 1 THEN EXIT; END IF; BEGIN SELECT MAX(sal) INTO v_sal FROM emp WHERE stat = 1; DELETE emp WHERE sal < v_sal; END; END LOOP; END; |
コメント
SQL Developerを使用して、ほぼすべてのWHILE構文を変換できます。ただし、Microsoft SQL ServerまたはSybase Adaptive ServerのWHILEループ内のCONTINUEには、PL/SQLに同等の構文はありません。ラベルを指定したGOTO文を使用してシミュレートされます。
表3-24 OracleとMicrosoft SQL ServerまたはSybase Adaptive ServerのGOTO文の比較
Microsoft SQL ServerまたはSybase Adaptive Server | Oracle |
---|---|
CREATE PROC proc1 @Status int AS DECLARE @j int IF @Status = -1 GOTO Error SELECT @j = -1 Error: SELECT @j = -99 |
CREATE OR REPLACE PROCEDURE proc1 ( v_Status IN NUMBER ) AS v_j NUMBER(10,0); BEGIN IF v_Status = -1 THEN GOTO Error; END IF; v_j := -1; <<Error>> v_j := -99; END; |
コメント
GOTO <label>文は自動的に変換されます。手動で変更する必要はありません。
表3-25 OracleとMicrosoft SQL ServerまたはSybase Adaptive Serverでの@@Rowcountおよび@@Error変数の比較
Microsoft SQL ServerまたはSybase Adaptive Server | Oracle |
---|---|
CREATE PROC proc1 AS DECLARE @x int SELECT @x=count(*) FROM emp IF @@rowcount = 0 print 'No rows found.' IF @@error = 0 print 'No errors.' |
CREATE OR REPLACE PROCEDURE proc1 AS v_sys_error NUMBER := 0; v_x NUMBER(10,0); BEGIN BEGIN SELECT COUNT(*) INTO v_x FROM emp ; EXCEPTION WHEN OTHERS THEN v_sys_error := SQLCODE; END; IF SQL%ROWCOUNT = 0 THEN DBMS_OUTPUT.PUT_LINE('No rows found.'); END IF; IF v_sys_error = 0 THEN DBMS_OUTPUT.PUT_LINE('No errors.'); END IF; END; |
コメント
@@rowcountは、PL/SQLのカーソル属性SQL%ROWCOUNTに変換されます。
@@errorは、v_sys_errorに変換されます。この変数には、SQLCODEファンクションによって戻された値が含まれます。SQLCODEによって戻された値は、例外ブロック内でのみ割り当てる必要があります。そうでない場合は、値0が戻されます。
表3-26 OracleとMicrosoft SQL ServerまたはSybase Adaptive ServerのASSIGNMENT文の比較
Microsoft SQL ServerまたはSybase Adaptive Server | Oracle |
---|---|
CREATE PROC proc1 AS DECLARE @x int SELECT @x = -1 SELECT @x=sum(salary) FROM employee |
CREATE OR REPLACE PROCEDURE proc1 AS v_x NUMBER(10,0); BEGIN v_x := -1; SELECT SUM(salary) INTO v_x FROM employee ; END; |
コメント
Microsoft SQL ServerまたはSybase Adaptive Serverでの割当ては、SELECT文を使用して行います(表3-26を参照)。
PL/SQLでは、次の方法で値を変数に割り当てます。
割当て文を使用して、変数または式の値をローカル変数に割り当てます。SELECT..INTO
句を使用して、データベースから値を割り当てます。これを行うには、SQLによって1行のみが戻される必要があります。
表3-27 OracleとMicrosoft SQL ServerまたはSybase Adaptive ServerのSELECT文の比較
Microsoft SQL ServerまたはSybase Adaptive Server | Oracle |
---|---|
例1 CREATE PROC proc1 AS SELECT ename FROM employee |
例1 CREATE OR REPLACE PROCEDURE proc1 ( cv_1 IN OUT SYS_REFCURSOR ) AS BEGIN OPEN cv_1 FOR SELECT ename FROM employee ; END; |
例2 CREATE PROC proc1 AS DECLARE @name char(20) SELECT @id = id FROM employee RETURN id |
例2 CREATE OR REPLACE FUNCTION proc1 AS v_name CHAR(20); BEGIN SELECT id INTO v_id FROM employee ; RETURN v_id; END; |
コメント
それぞれのアーキテクチャの違いのため、Microsoft SQL ServerまたはSybase Adaptive Serverのストアド・プロシージャは、Oracleとは異なる方法でクライアント・プログラムにデータを戻します。
Microsoft SQL ServerまたはSybase Adaptive ServerとOracleでは、ストアド・プロシージャの出力パラメータを使用して、すべてのデータをクライアントに渡すことができます。
表3-28 OracleとMicrosoft SQL ServerまたはSybase Adaptive ServerでのGROUP BY句が指定されたSELECT文の比較
Microsoft SQL ServerまたはSybase Adaptive Server | Oracle |
---|---|
CREATE PROC proc1 AS DECLARE @ename char(20) DECLARE @salary int SELECT @ename=ename, @salary=salary FROM emp WHERE salary > 100000 GROUP BY deptno |
CREATE OR REPLACE PROCEDURE proc1 AS v_ename CHAR(20); v_salary NUMBER(10,0); BEGIN SELECT ename, salary INTO v_ename, v_salary FROM emp WHERE salary > 100000 GROUP BY deptno; END; |
コメント
T-SQLでは、GROUP BY句で使用される列をSELECT構文のリストの一部にする必要がないGROUP BY文を使用できます。PL/SQLでは、このタイプのGROUP BY句は使用できません。
SQL Developerは、このタイプのSELECT文をPL/SQLに変換します。ただし、Oracleで同様に動作するPL/SQL文はエラーを戻します。
表3-29 OracleとMicrosoft SQL ServerまたはSybase Adaptive Serverでの列別名の比較
Microsoft SQL ServerまたはSybase Adaptive Server | Oracle |
---|---|
CREATE PROC proc1 @Status int=0 AS SELECT x=sum(salary) FROM employee |
CREATE OR REPLACE PROCEDURE proc1 ( v_Status IN NUMBER DEFAULT 0, cv_1 IN OUT SYS_REFCURSOR ) AS BEGIN OPEN cv_1 FOR SELECT SUM(salary) x FROM employee ; END; |
コメント
SQL Developerを使用して、Microsoft SQL ServerまたはSybase Adaptive Server固有の列別名をOracle形式の列別名に変換できます。手動で変更する必要はありません。
表3-30 OracleとMicrosoft SQL ServerまたはSybase Adaptive ServerのFROMが指定されたUPDATE文の比較
Microsoft SQL ServerまたはSybase Adaptive Server | Oracle |
---|---|
CREATE PROC proc1 AS UPDATE table1 SET col1 = 1 FROM table1, table2 WHERE table1.id = table2.id |
CREATE OR REPLACE PROCEDURE proc1 AS BEGIN UPDATE table1 SET ( col1 ) = ( SELECT 1 FROM table1 , table2 WHERE table1.id = table2.id ); END; |
表3-31 OracleとMicrosoft SQL ServerまたはSybase Adaptive ServerのFROMが指定されたDELETE文の比較
Microsoft SQL ServerまたはSybase Adaptive Server | Oracle |
---|---|
CREATE PROC proc1 AS DELETE FROM table1 FROM table1, table2 WHERE table1.id = table2.id |
CREATE OR REPLACE PROCEDURE proc1 AS BEGIN DELETE FROM table1 WHERE ROWID IN (SELECT table1.ROWID FROM table1, table2 WHERE table1.id = table2.id); END; |
表3-32 OracleとMicrosoft SQL ServerまたはSybase Adaptive Serverの一時表の比較
Microsoft SQL ServerまたはSybase Adaptive Server | Oracle |
---|---|
CREATE PROC proc1 AS SELECT col1, col2 INTO #Tab FROM table1 WHERE table1.id = 100 |
CREATE OR REPLACE PROCEDURE proc1 AS BEGIN DELETE FROM tt_Tab; INSERT INTO tt_Tab ( SELECT col1, col2 FROM table1 WHERE table1.id = 100 ); END; |
コメント
一時表はOracleでサポートされています。SQL Developerでは、この機能を使用します。一時表のDDLが抽出され、スタンドアロン・オブジェクトとして生成されます。
表3-33 OracleとMicrosoft SQL ServerまたはSybase Adaptive Serverでのカーソル処理の結果セットの比較
Microsoft SQL ServerまたはSybase Adaptive Server | Oracle |
---|---|
CREATE PROC cursor_demo AS DECLARE @empno INT DECLARE @ename CHAR(100) DECLARE @sal FLOAT DECLARE cursor_1 CURSOR FOR SELECT empno, ename, sal FROM emp OPEN cursor_1 FETCH cursor_1 INTO @empno, @ename, @sal CLOSE cursor_1 DEALLOCATE CURSOR cursor_1 |
CREATE OR REPLACE PROCEDURE cursor_demo AS CURSOR cursor_1 IS SELECT empno, ename, sal FROM emp ; v_empno NUMBER(10,0); v_ename CHAR(100); v_sal NUMBER; BEGIN OPEN cursor_1; FETCH cursor_1 INTO v_empno,v_ename,v_sal; CLOSE cursor_1; END; |
コメント
Microsoft SQL ServerおよびSybase Adaptive Serverでは、T-SQLにカーソルが導入されました。Microsoft SQL ServerまたはSybase Adaptive ServerからOracleへのカーソルの構文変換は簡単に行うことができます。
注意: PL/SQLでは、カーソルの割当て解除は自動的に行われるため、実行する必要はありません。 |
T-SQLはMicrosoft SQL ServerまたはSybase Adaptive Serverの手続き型SQL言語で、PL/SQLはOracleの手続き型SQL言語です。この項では、次のT-SQLとPL/SQLの言語要素について説明します。
Microsoft SQL ServerまたはSybase Adaptive Server
Microsoft SQL ServerまたはSybase Adaptive Serverでは、ANSI規格の暗黙的トランザクション・モデルおよび明示的トランザクション・モデルという2つの異なるトランザクション・モデルが提供されています。
Microsoft SQL ServerまたはSybase Adaptive Serverでは、ANSI規格のトランザクションをサポートするオプションが提供されています。これらのオプションは、SETコマンドを使用して設定または設定解除できます。
次のSETコマンドを実行すると、暗黙的トランザクション・モデルが設定されます。
set chained on
次のSETコマンドを実行すると、分離レベルが必要なレベルに設定されます。
set transaction isolation level {1|3}
isolation level 1
を指定すると、内容を保証しない読取りが行われなくなります。isolation level 2
を指定すると、反復不能な読取りが行われなくなります。isolation level 3
を指定すると、仮読取りが行われなくなります。ANSI規格では、isolation level 3
を指定する必要があります。Microsoft SQL ServerまたはSybase Adaptive Serverの場合、デフォルトはisolation level 1
です。
Microsoft SQL ServerまたはSybase Adaptive Serverでは、isolation level 3
を実装するために、トランザクションに関連するすべての表にHOLDLOCKが適用されます。Microsoft SQL ServerまたはSybase Adaptive Serverでは、ページ・レベル・ロックとともに、HOLDLOCKを使用してユーザーを長時間ブロックできます。これを行うとレスポンス時間が長くなります。
Microsoft SQL ServerまたはSybase Adaptive Serverのアプリケーションで、isolation level 3
が指定されたANSI規格の連鎖(暗黙的)トランザクションを実装すると、Oracleでは(リピータブル・リードが保証される)ANSI規格の暗黙的トランザクション・モデルが実装されるため、このアプリケーションの移行が正常に行われます。
非ANSI規格のアプリケーションでは、Microsoft SQL ServerまたはSybase Adaptive Serverのトランザクションは明示的です。論理トランザクションは、BEGIN TRANSACTION文を使用して明示的に開始する必要があります。トランザクションは、COMMIT TRANSACTION文を使用してコミットするか、またはROLLBACK TRANSACTION文を使用してロールバックします。トランザクションには、名前を付けることができます。たとえば、次の文は、名前を付けてトランザクションを開始します。
account_tran. BEGIN TRANSACTION account_tran
明示的トランザクション・モードでは、ネストしたトランザクションを使用できます。ただし、可能なのは構文上のネストです。トランザクションは、一番外側のBEGIN TRANSACTIONおよびCOMMIT TRANSACTION文によってのみ実際に作成およびコミットされます。内側のCOMMIT TRANSACTIONは実際にはコミットされないため、この構文はわかりにくい場合があります。
次に、ネストしたトランザクションの例を示します。
BEGIN TRANSACTION /* T/SQL Statements */ BEGIN TRANSACTION /* T/SQL Statements */ BEGIN TRANSACTION account_tran /* T/SQL Statements */ IF SUCCESS COMMIT TRANSACTION account_tran ELSE ROLLBACK TRANSACTION account_tran END IF /* T/SQL Statements */ IF SUCCESS COMMIT TRANSACTION ELSE ROLLBACK TRANSACTION END IF /* T/SQL Statements */ COMMIT TRANSACTION
BEGIN TRANSACTIONおよびCOMMIT TRANSACTION文がネストされている場合は、一番外側の組によってトランザクションが作成およびコミットされ、内側の組ではネスト・レベルが追跡されるのみです。トランザクションは、一番外側のCOMMIT TRANSACTION文が実行されるまでコミットされません。通常、トランザクションのネストは、BEGIN TRANSACTION/COMMIT TRANSACTION文を含むストアド・プロシージャを使用して、トランザクション処理文を含む他のプロシージャをコールすると発生します。グローバル変数@@trancount
は、現行のユーザーに対して現在アクティブなトランザクションの数を追跡します。複数のトランザクションをオープンしている場合は、ROLLBACKを実行後、COMMITを実行する必要があります。
内側のCOMMIT TRANSACTION文は、名前の有無にかかわらず実行されません。名前が付いていない内側のROLLBACK TRANSACTION文は、一番外側のBEGIN TRANSACTION文にロールバックされ、現行のトランザクションを取り消します。名前が付いている内側のROLLBACK TRANSACTION文は、名前が付いている各トランザクションを取り消します。
Oracle
Oracleでは、ANSI規格の暗黙的トランザクション方法を使用できます。論理トランザクションは、COMMIT、ROLLBACKまたはデータベースへの接続を行った後の最初の実行SQL文で開始されます。トランザクションは、COMMIT、ROLLBACKまたはデータベースからの切断を行うと終了します。暗黙的COMMIT文は、各DDL文の実行前および実行後に発行されます。有効にできる論理トランザクションは1セッション当たり1つのみのため、暗黙的トランザクション・モデルを使用すると、手動でトランザクションをネストする必要がなくなります。ユーザーは、トランザクションにSAVEPOINTを設定し、一部のトランザクションをSAVEPOINTにロールバックできます。
次に例を示します。
UPDATE test_table SET col1='value_1'; SAVEPOINT first_sp; UPDATE test_table SET col1='value_2'; ROLLBACK TO SAVEPOINT first_sp; COMMIT; /* col1 is 'value_1'*/
論理トランザクションは、Microsoft SQL ServerまたはSybase Adaptive ServerとOracleでは異なる方法で処理されます。Microsoft SQL ServerまたはSybase Adaptive Serverでは、トランザクションはデフォルトで明示的です。Oracleでは、ANSI規格の暗黙的トランザクション方法が実装されます。これによって、T-SQLトランザクション処理文からPL/SQLトランザクション処理文に直接変換する必要がなくなります。
また、Microsoft SQL ServerまたはSybase Adaptive Serverでは、ストアド・プロシージャのトランザクションでネスト可能である必要がありますが、Oracleでは、トランザクションのネストはサポートされていません。
表3-34では、Microsoft SQL ServerまたはSybase Adaptive ServerとOracleのトランザクション処理文を比較します。
表3-34 OracleとMicrosoft SQL ServerまたはSybase Adaptive Serverのトランザクション処理文の比較
Microsoft SQL ServerまたはSybase Adaptive Server | Oracle |
---|---|
BEGIN TRAN |
|
BEGIN TRAN tran_1 |
SAVEPOINT tran_1 |
COMMIT TRAN (ネスト・レベル=1が指定されたトランザクション用) |
COMMIT |
COMMIT TRAN (ネスト・レベル>1が指定されたトランザクション用) |
|
COMMIT TRAN tran_1 (ネスト・レベル=1が指定されたトランザクション用) |
COMMIT |
COMMIT TRAN tran_1 (ネスト・レベル>1が指定されたトランザクション用) |
|
ROLLBACK TRAN |
ROLLBACK |
ROLLBACK TRAN tran_1 |
ROLLBACK TO SAVEPOINT tran_1 |
変換時、SQL Developerでは、現行のトランザクション処理文のネスト・レベルは判断できません。変数@@trancount
はランタイム環境変数です。
表3-35に、Oracleでのトランザクション処理文の変換方法に対して現在実装されているMicrosoft SQL ServerまたはSybase Adaptive Serverを示します。
表3-35 トランザクション処理文の変換方法
Microsoft SQL ServerまたはSybase Adaptive Server | Oracle |
---|---|
BEGIN TRAN |
/*BEGIN TRAN >>> statement ignored <<<*/ |
BEGIN TRAN tran_1 |
SAVEPOINT tran_1; |
COMMIT TRAN (ネスト・レベル=1が指定されたトランザクション用) |
COMMIT WORK; |
COMMIT TRAN (ネスト・レベル>1が指定されたトランザクション用) |
COMMIT WORK; |
COMMIT TRAN tran_1 (ネスト・レベル=1が指定されたトランザクション用) |
COMMIT WORK; |
COMMIT TRAN tran_1 (ネスト・レベル>1が指定されたトランザクション用) |
COMMIT WORK; |
ROLLBACK TRAN |
ROLLBACK WORK; |
ROLLBACK TRAN tran_1 |
ROLLBACK TO SAVEPOINT tran_1 |
SAVE TRAN tran_1 |
SAVEPOINT tran_1 |
2つのデータベースでトランザクションを処理する方法に違いがあるため、トランザクションの再編成を検討する必要がある場合があります。
トランザクション処理文がストアド・プロシージャのコードではなくクライアントのコードの一部となるように、クライアント/サーバー・アプリケーションを設計します。通常、論理トランザクションはユーザーが設計および制御する必要があるため、このように設計すると効果的です。
ストアド・プロシージャの変換の場合は、SAVEPOINTをプロシージャの先頭に設定し、SAVEPOINTにのみロールバックすることを検討してください。Microsoft SQL ServerまたはSybase Adaptive Serverでは、少なくとも外側のトランザクションがクライアント・アプリケーションで制御されるように変更します。
Microsoft SQL ServerまたはSybase Adaptive Server
Microsoft SQL ServerまたはSybase Adaptive Serverでは、前の文によって生成されたエラー状態に関係なく、制御が次の文に渡されるため、各SQL文の実行後にエラーを確認する必要があります。クライアントのERROR_HANDLERルーチンは、サーバー・エラーが発生した場合、コールバック・ルーチンとして起動されます。エラー状態はこのコールバック・ルーチンで処理できます。
ストアド・プロシージャは、RAISERROR文を使用してエラー状態をクライアントに通知します。この文を使用しても、制御はコール元のルーチンに戻されません。
Microsoft SQL ServerまたはSybase Adaptive Serverでは、システム表を使用してエラー・メッセージをカスタマイズできます。システム・プロシージャを使用して、エラー・メッセージをこの表に追加できます。
Oracle
Oracleでは、次の文に進む前に、各SQL文でエラーが自動的に確認されます。エラーが発生すると、すぐに制御が例外ハンドラ(存在する場合)に渡されます。これによって、すべてのSQL文の状態を確認する必要がなくなります。たとえば、SELECT文でデータベース内の行が検出されない場合は、例外が発生します。ブロックの対応する例外ハンドラ部分には、このエラーを処理するコードが含まれている必要があります。RAISE_APPLICATION_ERROR組込みプロシージャは、サーバー・エラー状態をクライアントに通知し、コール元のルーチンにすぐに戻します。
Oracleでは、暗黙的SAVEPOINTがプロシージャの先頭に指定されます。RAISE_APPLICATION_ERROR組込みプロシージャによって、このSAVEPOINT、またはプロシージャ内の最後にコミットされたトランザクションにロールバックされます。制御は、コール元のルーチンに戻されます。
OracleのRAISE_APPLICATION_ERROR文によって、ユーザーはエラー・メッセージをカスタマイズできます。例外が発生した場合は、PL/SQLによって、SQLCODEがコール元に自動的に戻されます。処理されるまで継続して伝播します。
推奨事項:
OracleでMicrosoft SQL ServerまたはSybase Adaptive Serverの動作をシミュレートするには、各SQL文を、同様に動作するPL/SQLブロックで囲む必要があります。このブロックで、SQL文用に検出する必要がある例外を処理する必要があります。
Microsoft SQL ServerまたはSybase Adaptive Serverの動作のシミュレートに必要な特別なコードの詳細は、「T-SQLとPL/SQLの構文」を参照してください。
Microsoft SQL ServerまたはSybase Adaptive Serverのストアド・プロシージャのRAISERROR文の直後にRETURN文を指定すると、これらの2つの文をOracleのRAISE_APPLICATION_ERROR文に変換できます。
ユーザー定義の表を使用してエラー・メッセージをカスタマイズできます。標準ルーチンを作成し、この表にエラー・メッセージを取得できます。この方法には、2つの目的があります。システムの移植性を高めること、および管理者がエラー・メッセージを集中制御できるようにすることです。
Microsoft SQL ServerまたはSybase Adaptive Server
Microsoft SQL ServerまたはSybase Adaptive Serveでは、次のグローバル変数は、変換処理で特に有効です。
@@error:
最後に実行したT-SQL文の実行状態を示すサーバー・エラー・コードです。コード例については、「@@Rowcountおよび@@Error変数」を参照してください。
@@identity:
文によって最後に生成された識別値を戻します。ROLLBACKSまたは他のトランザクションのため、前の設定には戻りません。
@@rowcount:
最後に実行したT-SQL文によって影響を受ける行の数です。コード例については、「@@Rowcountおよび@@Error変数」を参照してください。
@@servername:
Microsoft SQL ServerまたはSybase Adaptive Serverのローカル・サーバーの名前です。
@@sqlstatus:
最後のFETCH文の状態情報です。
@@tranchained:
T-SQLプロシージャの現行のトランザクション・モードです。@@tranchainedによって1が戻される場合、TL/SQLプロシージャは連鎖状態か、暗黙的トランザクション・モードです。
@@trancount:
現行のユーザーのネストしたトランザクションのネスト・レベルを追跡します。
@@transtate:
トランザクションの現行の状態です。
Oracle
SQLCODE:
最後に実行したPL/SQL文の実行状態を示すサーバー・エラー・コードです。
SQL%ROWCOUNT:
PL/SQLプロシージャ内から実行した各SQL文に対応付けられた暗黙カーソルに追加された変数です。この変数には、暗黙カーソルに追加されたSQL文を実行すると影響を受ける行の数が含まれます。
推奨事項:
Oracleには、@@error
変数と完全に同等の機能(SQLCODEファンクション)があります。SQLCODEファンクションは、サーバー・エラー・コードを戻します。
OracleのSQL%ROWCOUNT
変数は、機能的に@@rowcount
と同等です。
PL/SQLでは、さらに多くの特別なグローバル変数を使用できます。ここでは、一部のグローバル変数のみについて説明します。T-SQLでも、さらに多くの特別なグローバル変数を使用できます。ここでは、変換処理で重要な役割を果たす一部のグローバル変数についてのみ説明します。
Microsoft SQL ServerまたはSybase Adaptive ServerとOracleの演算子については、「データ操作言語」を参照してください。
Microsoft SQL ServerまたはSybase Adaptive ServerとOracleの組込みファンクションについては、「データ操作言語」を参照してください。
Microsoft SQL ServerまたはSybase Adaptive Serverでは、DDL構文をストアド・プロシージャの一部にできます。Oracleでは、DDL文を動的SQLの一部にできます。また、各DDL文の実行後にCOMMIT文が発行されます。
現在、SQL Developerは、ストアド・プロシージャ内のDDL構文をスタンドアロンのDDL構文に変換します。DDLは、ストアド・プロシージャの本体から削除され、プロシージャより前に作成されます。動的SQLのサポートは、将来のリリースで提供される予定です。