| Pro*COBOL®プログラマーズ・ガイド 11gリリース2(11.2) E50141-01 |
|
 前 |
 次 |
この章では、CONNECT文およびそのオプション、Oracle Netおよびネットワーク接続に関連する文を説明します。トランザクション処理についても解説します。Oracleデータに対する変更の確定または取消しを制御する方法を含めて、データベースの整合性を保つための基本的な技術を学習します。
Pro*COBOLプログラムは、データの問合せや操作を行う前に、Oracleにログインする必要があります。ログインするには、次に示すようにCONNECT文を使用します。
EXEC SQL
CONNECT :USERNAME IDENTIFIED BY :PASSWD
END-EXEC.
USERNAMEおよびPASSWDはPIC X(n)またはPIC X(n) VARYINGホスト変数です。また、次の文も使用できます。
EXEC SQL
CONNECT :USR-PWD
END-EXEC.
この場合、ホスト変数USR-PWDには、スラッシュ(/)で区切られたユーザー名およびパスワードに続き、オプションのtnsnames.oraエイリアス(@TNSALIAS)が含まれます。
CONNECT文の構文には、オプションのALTER AUTHORIZATION句を使用できます。次がCONNECT文の構文です。
EXEC SQL
CONNECT { :user IDENTIFIED BY :oldpswd | :usr_psw }
[[AT { dbname | :host_variable }] USING :connect_string ]
[ {ALTER AUTHORIZATION :newpswd | IN {SYSDBA | SYSOPER} MODE} ]
END-EXEC.
ALTER AUTHORIZATION句の詳細は、「実行時のパスワード変更」を参照してください。SYSDBAおよびSYSOPERオプションの詳細は、「SYSDBA権限またはSYSOPER権限」を参照してください。
CONNECT文は、プログラムが実行する最初のSQL文であることが必要です。したがって、別の実行SQL文を、位置的にはCONNECT文の前に記述できますが、論理的にはCONNECT文の前に記述できません。プリコンパイラ・オプションAUTO_CONNECT=YESの場合、CONNECT文は必要ありません。
ユーザー名およびパスワードを別々に指定する場合は、2つのホスト変数を文字列またはVARCHAR変数として定義します。ユーザー名およびパスワードの両方を含んだユーザーIDを指定する場合は、必要なホスト変数は1つのみです。
CONNECTを実行する前に、ユーザー名とパスワードの変数を設定する必要があります。設定しない場合、CONNECTは失敗します。プログラムのプロンプトで値の入力を求めることも、次のように値をハードコードすることもできます。
WORKING STORAGE SECTION.
...
01 USERNAME PIC X(10).
01 PASSWD PIC X(10).
...
...
PROCEDURE DIVISION.
LOGON.
EXEC SQL WHENEVER SQLERROR GOTO LOGON-ERROR END-EXEC.
MOVE "SCOTT" TO USERNAME.
MOVE "TIGER" TO PASSWD.
EXEC SQL
CONNECT :USERNAME IDENTIFIED BY :PASSWD
END-EXEC.
ただし、ユーザー名およびパスワードはCONNECT文にハードコードできません。また、二重引用符付きのリテラルも使用できません。たとえば、次の2つの文は無効です。
EXEC SQL
CONNECT SCOTT IDENTIFIED BY TIGER
END-EXEC.
EXEC SQL
CONNECT "SCOTT" IDENTIFIED BY "TIGER"
END-EXEC.
1つのPro*COBOLプログラムの中で複数のデータベース接続を同時に保持できます。
Pro*COBOLは、Oracle Netを介した分散処理に対応しています。アプリケーションは、ローカル・データベースおよびリモート・データベースの任意の組合せに同時にアクセスしたり、同じデータベースへの複数の接続を確立できます。図3-1では、アプリケーション・プログラムは1つのローカルOracle Databaseおよび3つのリモートOracle Databaseと通信しています。ORA2、ORA3およびORA4はCONNECT文中で使用される論理名です。
Oracle Netは、ネットワーク上の異なるマシン間およびオペレーティング・システム間に存在する境界を排除することによって、Oracleのツール製品に分散処理環境を提供します。この項では、Oracle Netを介した分散処理がPro*COBOLでどのようにサポートされているかを説明します。さらに、アプリケーションで次の処理を行う方法を学びます。
他のデータベースへの直接または間接アクセス
ローカルおよびリモート・データベースの任意の組合せへの同時アクセス
同一のデータベースへの複数接続
通常、EXEC SQL CONNECT :USR-PWD END-EXECで確立した1つの接続で十分です。接続先のデータベースはUSR-PWD句の指定によって決まります。デフォルトのデータベースのユーザー名およびパスワードが含まれる場合は、セッションのデフォルトとして定義されたデータベースに接続されます。「ユーザー名/パスワード@REMDB」が含まれる場合は、Oracle Net構成で定義されたように、Oracle Netを介してREMDBデータベースに接続されます。(USING句を使用してOracle Net接続文字列を指定する方法もあります。)これがデフォルトの接続です。
同じデータベースまたは別のデータベースへの同時接続の追加には、AT句、つまりEXEC SQL AT DB1 CONNECT :USR-PWD END-EXECを使用します。AT句の後の名前は、非デフォルト接続として一意に識別され、AT句の後にある同じ名前のSQL文がその接続に対して実行されます。SQL文の中にAT句の指定がない場合は、デフォルトの接続に対して実行されます。
データベース名は一意にする必要がありますが、2つ以上のデータベース名で同じ接続を指定できます。したがって、任意のノード上のデータベースに対して複数の接続を確立できます。
EXEC SQL CONNECT :USERNAME IDENTIFIED BY :PASSWORD END-EXEC.
また、次のような指定もできます。
EXEC SQL CONNECT :USR-PWD END-EXEC.
USR-PWDには有効なOracle接続文字列が含まれます。
「自動ログイン」に示すように、自動的にログインすることもできます。
これらは、単純化したCONNECT文のサブセットです。詳細は、この章の以降の項および「CONNECT (実行可能埋込みSQL拡張機能)」を参照してください。
次は、名前指定データベースに接続する例です。通常は、名前指定データベース接続を使用するのは同時接続が複数ある場合のみです。次の例では、単一接続の構文を示しています。
* -- Declare necessary host variables
WORKING-STORAGE SECTION.
...
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 USERNAME PIC X(10) .
01 PASSWORD PIC X(10) .
01 DB-STRING PIC X(20) .
...
EXEC SQL END DECLARE SECTION END-EXEC.
...
PROCEDURE DIVISION.
MOVE "scott" TO USERNAME.
MOVE "tiger" TO PASSSWORD.
MOVE "nyremote" TO DB-STRING.
...
* -- Assign a unique name to the database connection.
EXEC SQL DECLARE DBNAME DATABASE END-EXEC.
* -- Connect to the nondefault database
EXEC SQL
CONNECT :USERNAME IDENTIFIED BY :PASSWORD
AT DBNAME USING :DB-STRING
END-EXEC.
この例の識別子は、次の目的で使用されています。
ホスト変数USERNAMEおよびPASSWORDは、有効ユーザーを識別します。
ホスト変数DB-STRINGには、リモート・ノードの非デフォルト・データベースにログインするためのOracle Netの構文を含めます。
宣言されていない識別子DBNAMEにより、非デフォルトの接続に名前を付けます。これはOracleが使用する識別子であり、ホスト変数でもプログラム変数でもありません。
USING句は、DBNAMEに対応付けるネットワーク、マシンおよびデータベースを指定します。その後、(DBNAMEを指定した) AT句を使用したSQL文が、DB-STRINGで指定されたデータベースで実行されます。
もう1つの方法として、次の例に示すように、AT句で文字ホスト変数を使用できます。
* -- Declare necessary host variables
WORKING-STORAGE SECTION.
...
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 USERNAME PIC X(10).
01 PASSWORD PIC X(10).
01 DB-NAME PIC X(10).
01 DB-STRING PIC X(20).
...
EXEC SQL END DECLARE SECTION END-EXEC.
...
PROCEDURE DIVISION.
MOVE "scott" TO USERNAME.
MOVE "tiger" TO PASSSWORD.
MOVE "oracle1" TO DB-NAME.
MOVE "nyremote" TO DB-STRING.
...
* -- Connect to the nondefault database
EXEC SQL
CONNECT :USERNAME IDENTIFIED BY :PASSWORD
AT :DB-NAME USING :DB-STRING
END-EXEC.
DB-NAMEがホスト変数の場合は、DECLARE DATABASE文は必要ありません。DBNAMEが宣言されていない識別子の場合にのみ、CONNECT... AT DBNAME文を実行する前にDECLARE DBNAME DATABASE文を実行する必要があります。
SQL操作。権限を付与されている場合は、非デフォルトの接続で任意のSQL DML文を実行できます。たとえば、次のように入力します。
EXEC SQL AT DBNAME SELECT ...
EXEC SQL AT DBNAME INSERT ...
EXEC SQL AT DBNAME UPDATE ...
次の例では、DB-NAMEはホスト変数です。
EXEC SQL AT :DB-NAME DELETE ...
カーソルの制御。OPEN、FETCHおよびCLOSEなどのカーソルの制御文は例外で、AT句は使用しません。カーソルと明示的に識別されたデータベースを対応付ける場合は、次に示すとおり、DECLARE CURSOR文でAT句を使用してください。
EXEC SQL AT :DB-NAME DECLARE emp_cursor CURSOR FOR ...
EXEC SQL OPEN emp_cursor ...
EXEC SQL FETCH emp_cursor ...
EXEC SQL CLOSE emp_cursor END-EXEC.
DB-NAMEがホスト変数の場合、その宣言は、宣言したカーソルを参照するすべてのSQL文のスコープ内にあることが必要です。たとえば、あるサブプログラムでカーソルをオープンし、それを別のサブプログラムでフェッチする場合は、DB-NAMEをグローバルに宣言するか、それぞれのサブプログラムに渡す必要があります。
カーソルからのオープン、クローズまたはフェッチには、AT句は使用しません。SQL文は、DECLARE CURSOR文のAT句で名前を付けられたデータベースか、カーソルの宣言でAT句が使用されていない場合はデフォルトのデータベースにおいて実行されます。
AT :ホスト変数句を使用して、カーソルに対応付けられた接続を変更できます。ただし、カーソルがオープンされているときは対応付けを変更できません。次の例を考えてみます。
EXEC SQL AT :DB-NAME DECLARE emp_cursor CURSOR FOR ...
MOVE "oracle1" TO DB-NAME.
EXEC SQL OPEN emp_cursor END-EXEC.
EXEC SQL FETCH emp_cursor INTO ...
MOVE "oracle2" TO DB-NAME.
* -- illegal, cursor still open
EXEC SQL OPEN emp_cursor END-EXEC.
EXEC SQL FETCH emp_cursor INTO ...
この例は、2番目のOPEN文を実行するときにemp_cursorがまだオープンされているため、無効となります。異なる接続に対して別々のカーソルが維持されることはありません。emp_cursorは1つのみ存在できます。別の接続のために再オープンするには、その前にクローズする必要があります。最後の例をデバッグするには、次のように、単にカーソルをクローズしてから再オープンします。
* -- close cursor first
EXEC SQL CLOSE emp_cursor END-EXEC.
MOVE "oracle2" TO DB-NAME.
EXEC SQL OPEN EMP-CUROR END-EXEC.
EXEC SQL FETCH emp_cursor INTO ...
動的SQL。動的SQL文は、文中ではAT句が使用されないカーソル制御文に類似しています。動的SQL方法1では、非デフォルトの接続で文を実行する場合は、AT句を使用する必要があります。次に、例を示します。
EXEC SQL AT :DB-NAME EXECUTE IMMEDIATE :SQL-STMT END-EXEC.
方法2、3および4で非デフォルトの接続で文を実行する場合は、DECLARE STATEMENT文でのみAT句を使用します。PREPARE、DESCRIBE、OPEN、FETCHおよびCLOSEなど、その他の動的SQL文はAT句を使用しません。次の例に方法2を示します。
EXEC SQL AT :DB-NAME DECLARE SQL-STMT STATEMENT END-EXEC.
EXEC SQL PREPARE SQL-STMT FROM :SQL-STRING END-EXEC.
EXEC SQL EXECUTE SQL-STMT END-EXEC.
次の例は方法3を示します。
EXEC SQL AT :DB-NAME DECLARE SQL-STMT STATEMENT END-EXEC.
EXEC SQL PREPARE SQL-STMT FROM :SQL-STRING END-EXEC.
EXEC SQL DECLARE emp_cursor CURSOR FOR SQL-STMT END-EXEC.
EXEC SQL OPEN emp_cursor ...
EXEC SQL FETCH emp_cursor INTO ...
EXEC SQL CLOSE emp_cursor END-EXEC.
複数の接続を同時にオープンする場合(アクティブな接続の識別にAT句が必要)でなければ、リモート・データベースに接続する際にAT句を使用する必要はありません。リモート・データベースにデフォルト接続を行うには、次の構文を使用します。
EXEC SQL
CONNECT :USERNAME IDENTIFIED BY :PASSWORD USING :DB-STRING
END-EXEC.
次のようなユーザーIDを使用すると、Oracleに自動的にログインできます。
<prefix><username>
prefixにはOracle初期化パラメータOS_AUTHENT_PREFIXの値(デフォルト値はOPS$)を指定し、usernameには使用しているオペレーティング・システムのユーザー名またはタスク名を指定します。たとえば、接頭辞がOPS$でユーザー名がTBARNESの場合、OPS$TBARNESがOracleの有効なユーザーIDであれば、ユーザーOPS$TBARNESとしてOracleにログインします。
自動ログイン機能を利用するには、次のようにPro*COBOLにスラッシュ(/)文字を渡すことが必要です。
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 ORACLEID PIC X.
...
EXEC SQL END DECLARE SECTION END-EXEC.
...
MOVE '/' TO ORACLEID.
EXEC SQL CONNECT :ORACLEID END-EXEC.
これによって、自動的にOPS$usernameというユーザーとして接続します。たとえば、オペレーティング・システムのユーザー名がRHILLで、OPS$RHILLがOracleの有効なユーザー名の場合、スラッシュ(/)を使用して接続すると、ユーザーOPS$RHILLとして自動的にログインします。
Pro*COBOLに文字列を渡すこともできます。ただし、その文字列の後続にブランクを入れないでください。たとえば、次のCONNECT文は失敗します。
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 ORACLEID PIC X(5).
...
EXEC SQL END DECLARE SECTION END-EXEC.
...
MOVE '/ ' TO ORACLEID.
EXEC SQL CONNECT :ORACLEID END-EXEC.
Pro*COBOLでは、プログラムはCONNECT文を使用せずにデフォルトのデータベースにログインできます。このためには、コマンドラインにプリコンパイラ・オプションAUTO_CONNECTを指定することが必要です。
OS_AUTHENT_PREFIXのデフォルト値がOPS$、ユーザー名がTBARNESであり、OPS$TBARNESがOracleの有効なユーザーIDであるとします。AUTO_CONNECT=YESの場合、Pro*COBOLが実行SQL文を検出すると、ユーザー・プログラムはOPS$TBARNESのユーザーIDで自動的にOracleにログインします。
AUTO_CONNECT=NO (デフォルト)の場合は、OracleにログインするにはCONNECT文を使用する必要があります。
Pro*COBOLでは、オプションのALTER AUTHORIZATION句によって、実行時のユーザー/パスワードをクライアント・アプリケーションで簡単に変更できます。
ALTER AUTHORIZATION句の構文は、次のとおりです。
EXEC SQL CONNECT .. ALTER AUTHORIZATION :NEWPSWD END-EXEC.
この句を使用すると、アカウントのパスワードが、NEWPSWDで指定された値に変更されます。パスワードを変更すると、USER/NEWPSWDとして接続が試行されます。次の結果が予想されます。
アプリケーションが問題なく接続されます。
アプリケーションが接続に失敗します。次のどちらかの原因が考えられます。
なんらかの理由でパスワードを認識できませんでした。パスワードは元のままです。
アカウントがロックされています。パスワードは変更できません。
この項では、別の種類のCONNECT文で考えられる結果を説明します。
次の文がアプリケーションから発行されるとします。
EXEC SQL CONNECT ... /* No ALTER AUTHORIZATION clause */
通常の接続が実行されます。予想される結果は次のとおりです。
アプリケーションが問題なく接続されます。
アプリケーションは接続できるが、パスワードに関する警告を受ける。この警告は、パスワードの期限は切れているが、まだログインできる猶予期間であることを示しています。この時点で、ユーザーは、アカウントがロックされる前にパスワードを変更するように求められます。
アプリケーションが接続に失敗します。次の原因が考えられます。
パスワードが間違っています。
アカウントが期限切れになっているか、またはロック状態です。
Oracle8.1より前のリリースでは、SYSOPERまたはSYSDBAシステム権限を得るために次の句を使用する必要はありませんでしたが、今回のリリースでは使用する必要があります。
SYSDBAまたはSYSOPERのシステム権限でログインするには、CONNECT文で他のすべての句の後に次のオプション文字列を追加します。
IN { SYSDBA | SYSOPER } MODE
次に例を示します。
EXEC SQL CONNECT ... IN SYSDBA MODE END-EXEC.
このオプションには次の制限があります。
AUTO_CONNECT=YESのプリコンパイラ・オプション設定を使用しているときは、このオプションはサポートされません。
CONNECT文でALTER AUTHORIZATIONキーワードを使用しているときは、このオプションは使用できません。
データベース・リンクは、Oracle分散データベース・オプションを介してサポートされます。たとえば、分散問合せでは、単一のSELECT文で1つ以上の非デフォルト・データベース上のデータにアクセスできます。
分散問合せ機能はデータベース・リンクに依存しており、リンクにより接続事態ではなく、CONNECT文に名前が割り当てられます。実行時には、指定したデータベースサーバーにより埋込みSELECT文が実行され、非デフォルトのデータベースに暗黙的に接続されて、必要なデータが取得されます。
詳細は、『Oracle Database Net Services管理者ガイド』を参照してください。
トランザクションの説明に入る前に、この項で定義されている用語に慣れる必要があります。
データベースが管理するジョブおよびタスクをセッションと呼びます。ユーザー・セッションは、アプリケーション・プログラムまたはOracle FormsなどのOracleのツール製品を実行してデータベースに接続すると開始されます。Oracleでは、複数のユーザー・セッションを同時に動作させ、コンピュータ・リソースを共有できます。このためには、Oracleが同時実行性、つまり多数のユーザーによる同一データへのアクセスを制御する必要があります。同時実行性を適切に制御しないと、データの整合性が損なわれることがあります。つまり、データまたは構造への変更が誤った順序で行われるおそれがあります。
Oracleでは、ロックを使用してデータへの同時アクセスを制御します。ロックにより、データの表や行などのデータベース・リソースのユーザーに一時的な所有権が与えられます。つまり、このユーザーがデータの変更を終了するまで他のユーザーは同じデータを変更できません。表のデータおよび構造はデフォルトのロック機構によって保護されるため、リソースを明示的にロックする必要はありません。ただし、デフォルトのロックをオーバーライドするときは、表または行単位でデータ・ロックを要求できます。行の共有および排他など、ロックのモードを選択できます。
複数のユーザーが同じデータベース・オブジェクトへのアクセスを試みると、デッドロックが発生する可能性があります。たとえば、同じ表を更新するユーザーが2人いる場合、それぞれ相手が現在ロックしている行の更新を試みると待機状態になります。それぞれのユーザーが、相手が使用中のリソースを待つことになるため、サーバーによりデッドロックが解除されるまで、どちらも処理を続行できません。サーバーからは、最小量の作業を完了した関連トランザクションに対してエラー信号が送られ、「リソース待機の間にデッドロックが検出されました」というエラー・コードがSQLCAのSQLCODEに戻されます。
1つの表に対しユーザーが問い合せ、同時に別のユーザーが更新している場合、データベースは問合せについて、その表のデータの読込み一貫性ビューを生成します。つまり、問合せが開始されて処理がそのまま続行しているときは、問合せによって読み込まれるデータは変更されません。更新アクティビティが続行している間、データベースは表のデータのスナップショットをとり、変更内容をロールバック・セグメントに記録します。データベースは、このロールバック・セグメント内の情報に基づいて読込み一貫性のある問合せ結果を作成し、必要に応じて変更を取り消します。
データベースはトランザクション指向です。つまり、トランザクションを使用してデータの整合性を保証します。トランザクションとは、あるタスクを完了するために定義する1つ以上の論理的に関連付けられたSQL文です。データベースでは、一連のSQL文を一単位として扱い、それらの文によって実行されたすべての変更が、同時にコミット(確定)されるか、ロールバック(取消し)されます。トランザクションの途中でアプリケーション・プログラムに障害が発生すると、データベースは自動的にトランザクション前の状態にリストアされます。
次項では、トランザクションの設計および制御方法について説明します。具体的には、次の方法について説明します。
トランザクションの開始および終了
COMMIT文を使用したトランザクションの確定
ROLLBACK TO文とともにSAVEPOINT文を使用したトランザクションの部分的な取消し
ROLLBACK文を使用したトランザクション全体の取消し
RELEASEオプションを指定したリソースの解放およびデータベースのログオフ
SET TRANSACTION文を使用した読取り専用トランザクションの設定
FOR UPDATE句またはLOCK TABLE文を使用したデフォルトのロックのオーバーライド
この章で説明するSQL文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
プログラムの最初の実行SQL文(CONNECT以外)によりトランザクションを開始します。1つのトランザクションが終了すると、次の実行SQL文により別のトランザクションが自動的に開始します。このように、すべての実行文はトランザクションの一部です。宣言SQL文はロールバックされません。また、コミットする必要もないため、トランザクションの一部とみなされません。
COMMITまたはROLLBACK文を記述します。RELEASEオプションは、付けても付けなくてもかまいません。これにより、データベースへの変更を明示的に確定または取り消します。
実行の前と後の両方で自動コミットを発行するデータ定義文(ALTER、CREATEまたはGRANTなど)を記述します。これはデータベースへの変更を暗黙的に確定します。
システム障害が発生した場合、ソフトウェア上の問題、ハードウェア上の問題または強制割込みなどが原因で予期しないセッション停止が発生した場合にも、トランザクションは終了します。
トランザクションの途中でプログラムに障害が発生すると、Oracleは発生したエラーを検出し、そのトランザクションをロールバックします。オペレーティング・システムに障害が発生した場合は、データベースは元の状態(トランザクション処理前の状態)にリストアされます。
COMMIT文を使用すると、データベースへの変更を確定できます。変更がコミットされるまで、他のユーザーは変更されたデータにアクセスできず、表示されるのはトランザクション開始前の状態のデータです。COMMIT文は、ホスト変数の値にも、プログラムの制御フローにも影響しません。具体的には、COMMIT文により次の処理が実行されます。
カレント・トランザクションのデータベースに対する変更をすべて確定します。
これらの変更を他のユーザーが参照できるようにします。
すべてのセーブポイントを消去します(SAVEPOINT文の使用に関する次の項を参照)。
解析ロック以外の行および表のロックをすべて解除します。
FOR UPDATE句を使用して宣言されているカーソルをクローズするか、CURRENT OF句を使用してコード内の別の場所で参照されているカーソルをクローズします。MODE=ANSI | ANSI14またはCLOSE_ON_COMMIT=YESを使用した場合は、明示カーソルがすべてクローズされます。
トランザクションを終了します。
MODE={ANSI13 | ORACLE}のときは、CURRENT OF句で参照されていない明示カーソルはコミット後もオープン状態となります。これによってパフォーマンスが向上します。「コミット時のフェッチ」の例を参照してください。
これらの処理は通常の処理の一部分であるため、COMMIT文はプログラムのメイン・パスにインラインで設定する必要があります。プログラムを終了する前に、保留中の変更を明示的にコミットしてください。コミットしない場合、保留中の変更はロールバックされます。次の例では、トランザクションをコミットして切断します。
EXEC SQL COMMIT WORK RELEASE END-EXEC.
オプションのキーワードWORKには、ANSI互換性があります。RELEASEオプションを指定すると、プログラムが使用しているリソース(ロックおよびカーソル)がすべて解放され、データベースからログオフされます。
データ定義文は実行の前と後の両方で自動コミットを発行するため、データ定義文の後にCOMMIT文を記述する必要はありません。したがってデータ定義文が正常終了しても異常終了しても、その前のトランザクションがコミットされます。
CURSORの後にWITH HOLD句を付けて宣言されているカーソルは、COMMIT後もオープン状態となります。この句の使用方法は、次の例のとおりです。
EXEC SQL
DECLARE C1 CURSOR WITH HOLD
FOR SELECT ENAME FROM EMP
WHERE EMPNO BETWEEN 7600 AND 7700
END-EXEC.
UPDATEの場合は、カーソルを宣言しないでください。DB2では、デフォルト(コミット時に全カーソルをクローズする)を変更するためにWITH HOLD句が使用されます。Pro*COBOLでは、DB2からOracleへのアプリケーションの移行を簡単に行えるようにするために、この句が用意されています。MODE=ANSIと指定されているとき、OracleではDB2のデフォルトが使用されますが、ホスト変数はすべて宣言部で宣言する必要があります。宣言部を省略するには、次の項で説明するプリコンパイラ・オプションCLOSE_ON_COMMITを使用します。「DECLARE CURSOR (埋込みSQLディレクティブ)」を参照してください。
プリコンパイラ・オプションCLOSE_ON_COMMITを使用すると、MODE=ANSIのデフォルト動作をオーバーライドできます(コマンドラインにMODE=ANSIを指定した場合、WITH HOLD句で宣言されていないカーソルはコミット時にクローズされます)。
CLOSE_ON_COMMIT = {YES | NO}
デフォルトはNOです。このオプションは、コマンドラインまたは構成ファイルで入力する必要があります。
|
注意: このオプションは注意して使用してください。カーソルのオープン/クローズを何度も行うと、各OPEN文で再解析を行う必要があるためアプリケーションの処理速度が低下する可能性があります。「CLOSE_ON_COMMIT」を参照してください。 |
ROLLBACK文を使用すると、保留状態のデータベースへの変更を取り消せます。たとえば表から行を誤って削除したときなどは、ROLLBACK文を使用して元のデータをリストアできます。ROLLBACK文は、ホスト変数の値やプログラム内の制御の流れには影響を与えません。ROLLBACK文は、次の操作を行います。
カレント・トランザクションで実行されたデータベースの変更を取り消します。
すべてのセーブポイントを消去します。
トランザクションを終了します。
解析ロック以外の行および表のロックをすべて解除します。
FOR UPDATE句を使用して宣言されているカーソルをクローズするか、CURRENT OF句を使用してコード内の別の場所で参照されているカーソルをクローズします。MODE={ANSI | ANSI14}が指定されている場合は、すべての明示カーソルがクローズされます。
MODE={ANSI13 | ORACLE}のときは、CURRENT OF句で参照されていない明示カーソルはロールバック後もオープン状態となります。
ROLLBACK文は例外処理の一部になっているため、プログラムのメイン・パスではなくエラー処理ルーチン内に指定する必要があります。次の例では、トランザクションをロールバックして切断します。
EXEC SQL ROLLBACK WORK RELEASE END-EXEC.
オプションのキーワードWORKには、ANSI互換性があります。RELEASEオプションを指定すると、プログラムが使用しているリソースがすべて解放され、データベースからログオフされます。
WHENEVER SQLERROR GOTO文からROLLBACK文が記述されているエラー処理ルーチンに分岐したときに、ロールバックでエラーが発生すると、プログラムが無限にループする可能性があります。このような事態を避けるには、ROLLBACK文の前にWHENEVER SQLERROR CONTINUEを記述します。
EXEC SQL
WHENEVER SQLERROR GOTO SQL-ERROR
END-EXEC.
...
DISPLAY 'Employee number? '.
ACCEPT EMP-NUMBER.
DISPLAY 'Employee name? '.
ACCEPT EMP-NAME.
EXEC SQL INSERT INTO EMP (EMPNO, ENAME)
VALUES (:EMP-NUMBER, :EMP-NAME)
END-EXEC.
...
SQL-ERROR.
EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC.
DISPLAY 'PROCESSING ERROR.'.
DISPLAY 'ERROR CODE : ', SQLCODE.
DISPLAY 'MESSAGE :', SQLERRMC.
EXEC SQL ROLLBACK WORK RELEASE END-EXEC.
STOP RUN.
プログラムが異常終了すると、Oracleはトランザクションをロールバックします。
Oracleは、SQL文を実行する前に、暗黙的なセーブポイント(ユーザーは操作できません)を設定します。SQL文でエラーが発生すると、Oracleは自動的にその文をロールバックし、該当するエラー・コードをSQLCA内のSQLCODEに戻します。たとえば、INSERT文が一意の索引内に同じ値を挿入しようとしたためエラーが発生すると、この文はロールバックの対象になります。
失敗したSQL文によって開始された作業のみが失われます。現在のトランザクションでその文の前に行われた作業は保持されます。このため、データ定義文が失敗した場合、それに先行する自動コミットは取り消されません。
|
注意: SQL文は実行前に必ず解析され、構文規則に従っているか、有効なデータベース・オブジェクトを参照しているかが検証されます。SQL文の実行中にエラーが検出されると、ロールバックが発生しますが、解析中にエラーが検出されても、ロールバックは発生しません。 |
Oracleは、デッドロックを解除するために単一のSQL文をロールバックすることもあります。デッドロックの原因となっているトランザクションの1つにエラーを通知して、そのトランザクションの現行の文をロールバックします。
SAVEPOINT埋込みSQL文を使用すると、トランザクションの処理の現時点にマークを設定し名前を指定できます。マークを設定したそれぞれの点をセーブポイントと呼びます。たとえば、次の文によりstart_deleteというセーブポイントを設定します。
EXEC SQL SAVEPOINT start_delete END-EXEC.
セーブポイントによってロング・トランザクションを分割できるため、より複雑なプロシージャを制御できるようになります。たとえば、単一のトランザクションが複数のファンクションを実行しているときに、それぞれのファンクションの前にセーブポイントを設定できます。この結果、あるファンクションでエラーが発生しても、簡単にデータを元の状態にリストアし、リカバリして、そのファンクションを再実行できます。
トランザクションの一部を取り消すには、ROLLBACK文およびそのTO SAVEPOINT句によってセーブポイントを指定します。TO SAVEPOINT句を使用すると、カレント・トランザクションの途中の文までロールバックできます。そのため、変更をすべて取り消す必要はありません。ROLLBACK TO SAVEPOINT文は、次の操作を行います。
指定したセーブポイントがマークされた以降のデータベースへの変更を取り消します。
指定したセーブポイント以降のセーブポイントをすべて消去します。
指定したセーブポイントがマークされた以降に取得された行および表のロックをすべて解除します。
次の例は、MAIL_LIST表にアクセスして、新しいリストの挿入、古いリストの更新、(少数の)使用されていないリストの削除を行います。削除後、SQLCAのSQLERRD(3)をチェックして、削除された行数を調べます。削除された行数が必要以上に大きいときは、セーブポイントのstart_deleteまでロールバックしてこの削除を取り消します。
* -- For each new customer
DISPLAY 'New customer number? '.
ACCEPT CUST-NUMBER.
IF CUST-NUMBER = 0
GO TO REV-STATUS
END-IF.
DISPLAY 'New customer name? '.
ACCEPT CUST-NAME.
EXEC SQL INSERT INTO MAIL-LIST (CUSTNO, CNAME, STAT)
VALUES (:CUST-NUMBER, :CUST-NAME, 'ACTIVE').
END-EXEC.
...
* -- For each revised status
REV-STATUS.
DISPLAY 'Customer number to revise status? '.
ACCEPT CUST-NUMBER.
IF CUST-NUMBER = 0
GO TO SAVE-POINT
END-IF.
DISPLAY 'New status? '.
ACCEPT NEW-STATUS.
EXEC SQL UPDATE MAIL-LIST
SET STAT = :NEW-STATUS WHERE CUSTNO = :CUST-NUMBER
END-EXEC.
...
* -- mark savepoint
SAVE-POINT.
EXEC SQL SAVEPOINT START-DELETE END-EXEC.
EXEC SQL DELETE FROM MAIL-LIST WHERE STAT = 'INACTIVE'
END-EXEC.
IF SQLERRD(3) < 25
* -- check number of rows deleted
DISPLAY 'Number of rows deleted is ', SQLERRD(3)
ELSE
DISPLAY 'Undoing deletion of ', SQLERRD(3), ' rows'
EXEC SQL
WHENEVER SQLERROR GOTO SQL-ERROR
END-EXEC
EXEC SQL
ROLLBACK TO SAVEPOINT START-DELETE
END-EXEC
END-IF.
EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC.
EXEC SQL COMMIT WORK RELEASE END-EXEC.
STOP RUN.
* -- exit program.
...
SQL-ERROR.
EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC.
EXEC SQL ROLLBACK WORK RELEASE END-EXEC.
DISPLAY 'Processing error'.
* -- exit program with an error.
STOP RUN.
ROLLBACK TO SAVEPOINT文では、RELEASEオプションを指定できないことに注意してください。
あるセーブポイントまでロールバックすると、そのセーブポイント以降のすべてのセーブポイントが消去されます。ただし、ロールバックしたセーブポイントはそのまま残ります。たとえば、5つのセーブポイントを設定しているときに3番目のセーブポイントまでロールバックすると、4番目と5番目のセーブポイントのみ消去されます。COMMIT文またはROLLBACK文を実行すると、すべてのセーブポイントが消去されます。
プログラムが異常終了すると、Oracleは自動的に変更をロールバックします。異常終了は、プログラムが作業を明示的にコミットもロールバックもしないまま、RELEASE埋込みSQL文を使用して接続を切断した場合に発生します。
これに対し、プログラムが所定作業を実行し、オープンしているカーソルをクローズし、明示的に作業をコミットまたはロールバックし、接続を切断して、制御をユーザーに戻した場合には、プログラムは正常に終了します。実行される最後のSQL文が次のいずれかのときにプログラムは正常終了します。
EXEC SQL COMMIT WORK RELEASE END-EXEC.
または
EXEC SQL ROLLBACK WORK RELEASE END-EXEC.
トークンWORKはオプションです。最後のSQL文が上のどちらでもない場合は、そのユーザー・セッションで取得したロックおよびカーソルはプログラムの終了後も解放されず、ユーザー・セッションがアクティブでなくなったことをOracleが認識するまで保持されます。この結果、マルチユーザー環境では、他のユーザーはロックされたリソースへのアクセスを必要以上に長く待たされる場合があります。
SET TRANSACTION文を使用すると、読取り専用または読取り/書込み両用のトランザクションを開始したり、カレント・トランザクションを特定のロールバック・セグメントに割り当てることができます。読取り専用トランザクションは、COMMIT文またはROLLBACK文、データ定義文によって終了します。
読取り専用トランザクションでは反復可能読取りが行えるため、他のユーザーが更新中の1つ以上の表に対して、複数の問合せを実行する場合に便利です。読取り専用トランザクション中、複数の表と複数の問合せで構成された読取り一貫性ビューが作成され、すべての問合せがデータベースの同じスナップショットを参照します。他のユーザーは、通常の方法でデータの問合せや更新ができます。次にSET TRANSACTION文の例を示します。
EXEC SQL SET TRANSACTION READ ONLY END-EXEC.
SET TRANSACTION文は、読取り専用トランザクションの最初のSQL文である必要があり、1つのトランザクションで1回しか使用できません。READ ONLYパラメータは必須です。これを使用しても、他のトランザクションには影響がありません。読取り専用トランザクションで使用できるのは、SELECT文(FOR UPDATEなし)、LOCK TABLE文、SET ROLE文、ALTER SESSION文、ALTER SYSTEM文、COMMIT文およびROLLBACK文のみです。
次に示すのは、あるストア・マネージャが、読取り専用トランザクションを使用してその日の販売活動、過去1週間の販売活動および過去1か月間の販売活動を調べて、サマリー・レポートを作成する例です。このレポートは、このトランザクションの実行中にデータベースを更新する他のユーザーによる影響を受けません。
EXEC SQL SET TRANSACTION READ ONLY END-EXEC.
EXEC SQL SELECT SUM(SALEAMT) INTO :DAILY FROM SALES
WHERE SALEDATE = SYSDATE END-EXEC.
EXEC SQL SELECT SUM(SALEAMT) INTO :WEEKLY FROM SALES
WHERE SALEDATE > SYSDATE - 7 END-EXEC.
EXEC SQL SELECT SUM(SALEAMT) INTO :MONTHLY FROM SALES
WHERE SALEDATE > SYSDATE - 30 END-EXEC.
EXEC SQL COMMIT WORK END-EXEC.
* -- simply ends the transaction since there are no changes
* -- to make permanent
* -- format and print report
デフォルトでは、Oracleによって多数のデータ構造が暗黙的に(自動的に)ロックされます。ただし、デフォルトのロックをオーバーライドして、別のロックを有効にする場合は、行または表を特定して、そこにデータ・ロックを要求できます。明示的なロックを行うと、トランザクション実行中に表へのアクセスを共有または拒絶でき、また、マルチ表および多重問合せの読込み一貫性を確保できます。
SELECT FOR UPDATE OF文を使用すると、表の特定の行を明示的にロックして、更新または削除が実行されるまでその行が変更されないようにできます。ただし、Oracleでは、更新時または削除時には自動的に行レベルのロックが行われます。したがって、UPDATEまたはDELETEの前に行をロックするときにのみFOR UPDATE OF句を使用してください。
LOCK TABLE文を使用すると、表全体を明示的にロックできます。
カーソルをDECLAREするときは、カーソルで定義されるすべての行に排他ロックを取得する効果のあるFOR UPDATE句をオプションで指定できます。これは、たとえば更新をある表内の既存の行に対して行う場合に、更新中に他のユーザーによって行が変更されるのを防止するのに便利です。
CURRENT OF句でカーソルを参照した場合、プリコンパイラは自動的にFOR UPDATE句をカーソル定義に追加するため、OF句は省略可能です。たとえば、次のような文があるとします。
EXEC SQL DECLARE emp_cursor CURSOR FOR
SELECT ENAME, JOB, SAL FROM EMP WHERE DEPTNO = 20
FOR UPDATE OF SAL
END-EXEC.
OFを削除して、次のように簡潔に記述します。
EXEC SQL DECLARE emp_cursor CURSOR FOR
SELECT ENAME, JOB, SAL FROM EMP WHERE DEPTNO = 20
FOR UPDATE
END-EXEC.
「CURRENT OF句の使用」の例を参照してください。
コミットとフェッチを併用する場合は、CURRENT OF句を使用しないでください。そのかわりに各行のROWIDを選択し、更新または削除するときにその値を使用してカレント行を識別してください。次の例を考えてみます。
EXEC SQL DECLARE emp_cursor CURSOR FOR
SELECT ENAME, SAL, ROWID FROM EMP WHERE JOB = 'CLERK'
END-EXEC.
...
EXEC SQL OPEN emp_cursor END-EXEC.
EXEC SQL WHENEVER NOT FOUND GOTO ...
PERFORM
EXEC SQL
FETCH emp_cursor INTO :EMP_NAME, :SALARY, :ROW-ID
END-EXEC
...
EXEC SQL UPDATE EMP SET SAL = :NEW-SALARY
WHERE ROWID = :ROW-ID
END-EXEC
EXEC SQL COMMIT END-EXEC
END-PERFORM.
ただし、フェッチされた行はロックされないので注意してください。つまり、ある行を読み込んでも、その行を更新または削除する前に別のユーザーがその行を変更してしまうと、結果に矛盾が生じる可能性があります。
LOCK TABLE文を使用すると、指定したロック・モードで1つ以上の表をロックできます。たとえば、次の文は行共有モードでEMP表をロックします。行共有ロックでは、表への同時アクセスが可能になります。他のユーザーがその表全体をロックして排他的に使用することはできなくなります。
EXEC SQL
LOCK TABLE EMP IN ROW SHARE MODE NOWAIT
END-EXEC.
ロック・モードによって、その表に設定できる他のロックが決定されます。たとえば、同時に多数のユーザーが1つの表に対して行共有ロックを取得できる一方で、排他ロックを取得できるのは一度に1ユーザーのみです。あるユーザーが表を排他ロックしている間は、他のユーザーはその表内の行の挿入、更新または削除を行えません。ロック・モードの詳細は、『Oracle Databaseアドバンスト・アプリケーション開発者ガイド』を参照してください。
オプションのキーワードNOWAITを指定すると、他のユーザーが表をロックしている場合は、その表の解放を待機しないようにOracleに対して指示できます。制御はただちにプログラムに戻されるため、プログラムではロックの取得を再度試みるまでの間に別の作業ができます。(SQLCA内のSQLCODEをチェックすると、表ロックが失敗したか確認できます。)NOWAITを省略すると、表が利用可能になるまで、Oracleは待機します。待機の時間制限は設定されていません。
表がロックされていても、他のユーザーは表に対して問い合せできますが、問合せを実行しても表のロックを取得できません。したがって、問合せが他の問合せや更新を妨げることはなく、更新が問合せを妨げることもありません。2つの異なるトランザクションで同じ行の更新が試みられる場合にのみ、一方のトランザクションが他方のトランザクションの完了まで待機の状態になります。表のロックは、トランザクションがCOMMITまたはROLLBACKを発行すると解除されます。
分散データベースとは、異なるノード上の複数の物理データベースで構成される単一の論理データベースです。分散型の文とは、データベース・リンクによってリモート・ノードにアクセスする任意のSQL文です。分散トランザクションには、分散データベースの複数のノードでデータを更新するための分散型の文が、1つ以上設定されています。その更新が1つのノードのみに影響するときは、そのトランザクションは分散型ではありません。
コミットを発行すると、分散トランザクションによる影響を受ける各データベースの変更が確定されます。かわりにROLLBACKを発行すると、すべての変更が取り消されます。ただし、コミットまたはロールバック中にネットワークやマシンで障害が発生すると、分散トランザクションの状態は不明またはインダウトになることがあります。そのような場合、FORCE TRANSACTIONシステム権限があれば、FORCE句を使用して、ローカル・データベースでトランザクションを手動でコミットまたはロールバックできます。このトランザクションは、データ・ディクショナリ・ビューDBA_2PC_PENDINGにあるトランザクションIDを引用符で囲んだリテラルで指定する必要があります。次に、いくつかの例を示します。
EXEC SQL COMMIT FORCE '22.31.83' END-EXEC.
...
EXEC SQL ROLLBACK FORCE '25.33.86'END-EXEC.
FORCEは指定されたトランザクションのみコミットまたはロールバックするため、カレント・トランザクションには影響しません。インダウトのトランザクションは、手動ではセーブポイントにロールバックできないことに注意してください。
COMMIT文中のCOMMENT句を使用すると、分散トランザクションと対応付けるためのコメントを指定できます。トランザクションがインダウトの場合、サーバーはデータ・ディクショナリ・ビューDBA_2PC_PENDINGのCOMMENTで指定されたテキストをトランザクションIDとともに格納します。テキストは、引用符の付いた50文字 以下のリテラルであることが必要です。次に、例を示します。
EXEC SQL
COMMIT COMMENT 'In-doubt trans; notify Order Entry'
END-EXEC.
分散トランザクションの詳細は、『Oracle Database概要』を参照してください。
次のガイドラインに従うと、いくつかの問題を回避できます。
アプリケーションを設計するときは、論理的に関連する処理を1つのトランザクション内にグループ化してください。正しく設計されたトランザクションには、与えられた作業を完了するために必要なステップが、すべて過不足なく含まれています。
表を参照するデータは一貫している必要があります。したがって、トランザクション内のSQL文は一貫した方法に従ってデータを変更する必要があります。たとえば2種類の銀行預金口座間の資金の送金取引の場合は、一方の口座に対する借方記帳および他方の口座に対する貸方記帳の処理がトランザクションに含まれている必要があります。どちらの処理も、正常終了または失敗が同時であることが必要です。一方の口座への新規預金など、この取引とは無関係な更新取引をトランザクションに取り込まないでください。
アプリケーション・プログラム内にSQLのロック文がある場合、ロックを要求するユーザーはそのロックを獲得する権限が必要です。データベース管理者(DBA)は、どの表でもロックできます。それ以外のユーザーは、自分が所有する表または権限を持つ表(ALTER、SELECT、INSERT、UPDATEおよびDELETEなど)のみロックできます。
