| Oracle Rdb SQLリファレンス・マニュアル リリース7.2 E06178-01 |
|
 戻る |
 次へ |
SQL> -- Declares default characteristics for transactions: SQL> -- SQL> DECLARE TRANSACTION READ WRITE; SQL> -- SQL> -- There is no transaction started; can start SQL> -- transaction with characteristics different SQL> -- from the declared characteristics using a SQL> -- SET TRANSACTION statement: SQL> -- SQL> SET TRANSACTION READ ONLY; SQL> -- SQL> -- Roll back the transaction started by SQL> -- the SET TRANSACTION statement: SQL> -- SQL> ROLLBACK; SQL> -- SQL> -- The default transaction characteristics are still those SQL> -- specified in the DECLARE TRANSACTION statement, and SQL> -- apply to the transaction started when this SELECT SQL> -- statement executes: SQL> -- SQL> SELECT * FROM EMPLOYEES; |
$ sql$ declare transaction batch update; %SQL-F-NOBATCHUPDATE, BATCH UPDATE is not allowed without setting of SQL$DISABLE_CONTEXT logical name |
2PCを無効にした後でこのDECLARE TRANSACTIONは受け入れられます。ただし、Oracle RdbではBATCH UPDATEトランザクションはほとんど使用せず、使用する場合も注意することをお薦めします。リカバリが不可能で、データベースが使用禁止状態になる可能性があるためです。
$ define SQL$DISABLE_CONTEXT TRUE $ sql$ declare transaction batch update; |
例1: DECLARE TRANSACTIONとSET TRANSACTIONの相違の説明次の例では、DECLARE TRANSACTION文に続く最初の実行可能文がトランザクションを開始しています。一方、以降のSET TRANSACTION文はそれ自体がトランザクションを開始します。
SQL> DECLARE TRANSACTION READ WRITE NOWAIT; SQL> -- SQL> -- Notice the "no transaction is in progress" message: SQL> -- SQL> SHOW TRANSACTION Transaction information: Statement constraint evaluation is off On the default alias Transaction characteristics: Nowait Read Write Transaction information returned by base system: no transaction is in progress - session ID number is 80 SQL> -- SQL> -- The first executable statement following the SQL> -- DECLARE TRANSACTION statement starts the transaction. SQL> -- In this case, SELECT is the first executable statement. SQL> -- SQL> SELECT LAST_NAME FROM CURRENT_SALARY; LAST_NAME Toliver Smith Dietrich . . . SQL> -- SQL> -- Note the transaction inherits the read/write characteristics SQL> -- specified in the DECLARE TRANSACTION statement: SQL> -- SQL> SHOW TRANSACTION; Transaction information: Statement constraint evaluation is off On the default alias Transaction characteristics: Nowait Read Write Transaction information returned by base system: a read-write transaction is in progress - updates have not been performed - transaction sequence number (TSN) is 416 - snapshot space for TSNs less than 416 can be reclaimed - session ID number is 80 SQL> -- SQL> ROLLBACK; SQL> -- SQL> -- Again, notice the "no transaction is in progress" message: SQL> -- SQL> SHOW TRANSACTION; Transaction information: Statement constraint evaluation is off On the default alias Transaction characteristics: Nowait Read Write Transaction information returned by base system: no transaction is in progress - transaction sequence number (TSN) 416 is reserved - snapshot space for TSNs less than 416 can be reclaimed - session ID number is 80 SQL> -- SQL> -- Unlike DECLARE TRANSACTION, the SET TRANSACTION statement SQL> -- immediately starts a transaction: SQL> -- SQL> SET TRANSACTION READ ONLY WAIT; SQL> -- SQL> -- Note the transaction characteristics show the SQL> -- read-only characteristics: SQL> -- SQL> SHOW TRANSACTION; Transaction information: Statement constraint evaluation is off On the default alias Transaction characteristics: Wait Read only Transaction information returned by base system: a snapshot transaction is in progress - all transaction sequence numbers (TSNs) less than 416 are visible - TSN 416 is invisible - all TSNs greater than or equal to 417 are invisible - session ID number is 80
例2: コンテキスト・ファイルでのDECLARE TRANSACTION文の使用
次の例は、コンテキスト・ファイルtest_declares.sqlを示しています。このファイルにはソース・ファイルtest.scoをプリコンパイルするための宣言が含まれています。
DECLARE ALIAS FOR FILENAME personnel; DECLARE TRANSACTION READ WRITE RESERVING EMPLOYEES FOR PROTECTED WRITE, JOB_HISTORY FOR PROTECTED WRITE, DEPARTMENTS FOR SHARED READ, JOBS FOR SHARED READ;
SQL文が含まれるプログラムをサポートするためにSQLコンテキスト・ファイルが必要となるタイミングは、『Oracle Rdb7 Guide to SQL Programming』のプログラムの移植性に関する項を参照してください。
例3: DECLARE TRANSACTION文での分離レベルの明示的な設定
この例では、読取り/書込みトランザクションのデフォルト特性を宣言しています。これには、デフォルトのISOLATION LEVEL SERIALIZABLEのISOLATION LEVEL REPEATABLE READへの変更も含まれます。
SQL> DECLARE TRANSACTION READ WRITE ISOLATION LEVEL REPEATABLE READ;
例4: パーティションの予約
SQL> -- Determine the ordinal position of the EMPLOYEES SQL> -- partitions. SQL> SELECT RDB$MAP_NAME, RDB$AREA_NAME, RDB$ORDINAL_POSITION cont> FROM RDB$STORAGE_MAP_AREAS cont> WHERE RDB$MAP_NAME='EMPLOYEES_MAP'; RDB$MAP_NAME RDB$AREA_NAME RDB$ORDINAL_POSITION EMPLOYEES_MAP EMPIDS_LOW 1 EMPLOYEES_MAP EMPIDS_MID 2 EMPLOYEES_MAP EMPIDS_OVER 3 3 rows selected SQL> -- SQL> -- Reserve EMPIDS_MID and EMPIDS_OVER for SQL> -- exclusive write. SQL> -- SQL> DECLARE TRANSACTION cont> RESERVING EMPLOYEES PARTITION (2,3) cont> FOR EXCLUSIVE WRITE;
対話型SQLおよび動的SQLでのストアド・プロシージャの起動、およびモジュールまたは埋込みSQLプログラムでのプロシージャのテストに使用できる変数を宣言します。複合文での変数宣言の詳細は、「複合文」を参照してください。
DECLARE文は次の環境で使用できます。
- 対話型SQL内
- 動的SQLで動的に実行される文として
CONSTANT
UPDATABLE
変数を、更新できない宣言定数に変更します。CONSTANTを指定する場合、変数に値が指定されるようにDEFAULT句も指定する必要があります。またCONSTANTは、変数を代入のターゲットとして使用できない、またはプロシージャのINOUTパラメータまたはOUTパラメータへ式として渡すことができないことを示します。UPDATABLEはデフォルトであり、変数を変更できます。変数の更新は、（INSERT文、UPDATE文またはSELECT文の一部として）SET代入、INTO代入、等価（=）比較により、またはプロシージャのOUTパラメータまたはINOUTパラメータとして使用できます。
data-type
変数に割り当てられるデータ型を指定します。データ型の詳細は、第2.3節を参照してください。default-clause
単純なリテラル値および他の宣言変数への参照のみをデフォルトとして使用できます。domain-name
変数に割り当てられるドメイン名を指定します。ドメインはデータ型と、対話型SQLに対しては変数の編集文字列を指定します。ドメイン名の詳細は、第2.2.9項を参照してください。
variable-name
ローカル変数を指定します。
- 複合文内部の変数はNULLに設定できます。対話型変数はホスト変数やパラメータに類似しています。標識変数を使用して対話型SQL変数をNULLに設定する必要があります。
- 変数は、セッションが終了するまで、またはUNDECLARE変数文が実行されるまで存在します。変数定義の削除の詳細は、「UNDECLARE変数文」を参照してください。
- SHOW VARIABLES文を使用して既存の変数定義を表示します。
- DEFAULT句が存在しない場合、宣言変数の初期値は未定義です。
- 変数のリストがまとめて宣言されている場合、DEFAULTはそれぞれの変数に適用されます。
- UPDATABLEはすべての宣言変数のデフォルトです。
例1: 対話型SQLでの変数の宣言
SQL> DECLARE :X INTEGER; SQL> DECLARE :Y CHAR(10); SQL> SQL> BEGIN cont> SET :X = 100; cont> SET :Y = 'Active'; cont> END; SQL> PRINT :X, :Y ; X Y 100 Active SQL> SHOW VARIABLES; X INTEGER Y CHAR(10)
例2: 対話型SQLスクリプトでのSQLSTATEの値の使用
次の単純なスクリプトでは、SIGNAL文で名前付きSQLSTATE変数を使用してスクリプトを読みやすくします。
@SYS$LIBRARY:SQLSTATE set verify; begin signal :SQLSTATE_DATA_ASSIGN ('Error in assignment'); end;
実行されると次のような出力が表示されます。
SQL> begin cont> signal :SQLSTATE_DATA_ASSIGN ('Error in assignment'); cont> end; %RDB-E-SIGNAL_SQLSTATE, routine "(unnamed)" signaled SQLSTATE "22005" -RDB-I-TEXT, Error in assignment SQL>
表またはビューから行を削除します。
DELETE文は次の環境で使用できます。
- 対話型SQL内
- プリコンパイル対象のホスト言語プログラムに埋め込まれる場合
- SQLモジュールのプロシージャの一部として
- 動的SQLで動的に実行される文として
correlation name
DELETE文の条件で表またはビューを識別する名前を指定します。相関名の詳細は、第2.2.4.1項を参照してください。CURRENT OF cursor-name
WHERE句でCURRENT OF cursor-nameを使用すると、指定したカーソルが配置されている行のみが削除されます。カーソルにはすでにDECLARE CURSOR文で名前が付けてあり、オープンされていて行に配置されている必要があります。また、DECLARE CURSOR文内のSELECT文のFROM句は、DELETE文のターゲットである表またはビューを参照する必要があります。
OPTIMIZE AS query-name
名前を問合せに割り当てます。OPTIMIZE FOR
OPTIMIZE FOR句は、選択式を指定する文の優先オプティマイザ計画を指定します。次のオプションを使用できます。
- FAST FIRST
FAST FIRST用に最適化された問合せが、これにより全体のスループットが低下しても、できるかぎり速やかにデータをユーザーに返します。
問合せが早期に取り消されることがある場合は、FAST FIRSTの最適化を指定する必要があります。FAST FIRST最適化の有力な候補は、レコードのグループをユーザーに表示する対話型アプリケーションです。このようなアプリケーションでは、ユーザーは最初の数画面の確認後、問合せを中断することもできます。たとえば、シングルトンSELECT文では、FAST FIRST最適化がデフォルトで設定されます。
最適化計画を明示的に設定しない場合は、FAST FIRSTがデフォルトとなります。- TOTAL TIME
アプリケーションをバッチで実行し、問合せのすべてのレコードにアクセスして更新またはレポートの書込みを行う場合は、TOTAL TIME最適化を指定する必要があります。TOTAL TIME最適化は、ほとんどの問合せに効果があります。- SEQUENTIAL ACCESS
順次アクセスの使用を強制します。このオプションは、厳密なパーティション化機能を使用する表では特に有用です。
OPTIMIZE USING outline-name
問合せのアウトラインIDとアウトラインのIDが異なっている場合でも、DELETE文で使用する問合せアウトラインに名前を付けます。問合せアウトラインとは、問合せを実装する方法に関する全体的な計画です。詳細は、「CREATE OUTLINE文」を参照してください。
OPTIMIZE WITH
以前のOracle Rdbバージョンで使用されていたDEFAULT、行数が少ない方が選択されると仮定するAGGRESSIVE、問合せにリテラルを使用して索引で予備評価を行うSAMPLEDの3つの最適化制御のいずれかを選択します。predicate
WHERE句に条件が含まれている場合、条件がTRUEとなるターゲット表のすべての行が削除されます。条件の詳細は、第2.7節を参照してください。table-name
view-name
行を削除するターゲット表またはターゲット・ビューの名前を指定します。WHERE
削除するターゲット表またはターゲット・ビューの行を指定します。WHERE句を省略した場合、SQLではターゲット表またはターゲット・ビューのすべての行が削除されます。WHERE句には条件またはカーソル名を指定できます。
- 列名を指定するときに、列名がパラメータと同じ場合は、列名とともに相関名または表名を使用してパラメータ名との混同を回避する必要があります。
- 埋込みDELETE文のCURRENT OF句では、動的SELECT文に基づいてカーソルを指定できません。CURRENT OF句で動的SELECT文に基づいてカーソルを参照するには、DELETE文も準備して動的に実行します。
- 埋込みDELETE文のCURRENT OFで句は、読取り専用カーソルを指定できません。読取り専用カーソルの詳細は、「DECLARE CURSOR文」の「使用方法」を参照してください。
- WHERE CURRENT OF句とともにOPTIMIZE USING句やOPTIMIZE AS句を指定することはできません。
- compound-use-statementにはアウトライン名を指定できません。複合文の詳細は、「複合文」を参照してください。
- アウトラインが存在する場合は、OPTIMIZE USING句で指定されているアウトラインが使用されます。ただし、アウトラインのディレクティブを1つ以上順守できない場合は除きます。既存のアウトラインを使用できない場合は、エラー・メッセージが発行されます。
存在しないアウトラインの名前を指定すると、問合せがコンパイルされ、そのアウトライン名は無視され、問合せと同じアウトラインIDを持つ既存のアウトラインが検索されます。同じアウトラインIDのアウトラインが検出されると、そのアウトラインのディレクティブを使用して問合せを実行しようとします。同じアウトラインIDを持つアウトラインが検出されない場合、問合せ実行のための計画がオプティマイザで選択されます。
問合せアウトラインの詳細は、『Oracle Rdb7 Guide to Database Performance and Tuning』を参照してください。
例1: 従業員に関するすべての情報の削除従業員に関するすべての情報を削除するには、1つのトランザクション内で複数の表から行を削除する必要があります。このプログラムの一部では、ある従業員に関する情報を格納するすべての結果表から行を削除します。すべてのDELETE操作が1つのトランザクションに組み込まれているため、部分的にのみ削除された従業員のレコードはありません。
DISPLAY "Enter the ID number of employee". DISPLAY "whose records you want to delete: " WITH NO ADVANCING. ACCEPT EMP-ID. EXEC SQL DECLARE TRANSACTION READ WRITE RESERVING EMPLOYEES FOR PROTECTED WRITE, JOB_HISTORY FOR PROTECTED WRITE, SALARY_HISTORY FOR PROTECTED WRITE, DEGREES FOR PROTECTED WRITE END-EXEC EXEC SQL DELETE FROM EMPLOYEES E WHERE E.EMPLOYEE_ID = :EMP-ID END-EXEC IF SQLCODE < 0 THEN EXEC SQL ROLLBACK END-EXEC GO TO ERROR-PAR END-IF EXEC SQL DELETE FROM JOB_HISTORY JH WHERE JH.EMPLOYEE_ID = :EMP-ID END-EXEC IF SQLCODE < 0 THEN EXEC SQL ROLLBACK END-EXEC GO TO ERROR-PAR END-IF EXEC SQL DELETE FROM SALARY_HISTORY SH WHERE SH.EMPLOYEE_ID = :EMP-ID END-EXEC IF SQLCODE < 0 THEN EXEC SQL ROLLBACK END-EXEC GO TO ERROR-PAR END-IF EXEC SQL DELETE FROM DEGREES D WHERE D.EMPLOYEE_ID = :EMP-ID END-EXEC IF SQLCODE < 0 THEN EXEC SQL ROLLBACK END-EXEC GO TO ERROR-PAR END-IF
例2: 表からの選択した行の削除
次の文は、従業員のSALARY_AMOUNTが75,000ドルを超えるEMPLOYEESからすべての行を削除しています。EMPLOYEES表とSALARY_HISTORY表はともに、別名PERSでデータベース内にあります。
SQL> ATTACH 'ALIAS PERS FILENAME personnel'; SQL> DELETE FROM PERS.EMPLOYEES E cont> WHERE EXISTS ( SELECT * cont> FROM PERS.SALARY_HISTORY S cont> WHERE S.EMPLOYEE_ID = E.EMPLOYEE_ID cont> AND S.SALARY_AMOUNT > 75000 cont> ) ; 7 rows deleted
例3: アウトライン名を指定する表からの行の削除
次の例は、DEL_EMP_75000アウトラインの定義に使用される構文を示しています。
SQL> CREATE OUTLINE DEL_EMP_75000 cont> FROM cont> (DELETE FROM EMPLOYEES E cont> WHERE EXISTS ( SELECT * cont> FROM SALARY_HISTORY S cont> WHERE S.EMPLOYEE_ID = E.EMPLOYEE_ID cont> AND S.SALARY_AMOUNT > 75000 cont> );
次の問合せはDEL_EMP_75000アウトラインを指定します。
SQL> DELETE FROM EMPLOYEES E cont> WHERE EXISTS ( SELECT * cont> FROM SALARY_HISTORY S cont> WHERE S.EMPLOYEE_ID = E.EMPLOYEE_ID cont> AND S.SALARY_AMOUNT > 75000 cont> ) cont> OPTIMIZE USING DEL_EMP_75000; ~S: Outline DEL_EMP_75000 used . . . 7 rows deleted
