Hitachi

ノンストップデータベース HiRDB Version 10 SQLリファレンス


3.4.1 ALTER ROUTINEの形式と規則

〈この項の構成〉

(1) 機能

SQL関数,SQL手続き,及びトリガのSQLオブジェクトを再作成します。また,Java関数及びJava手続きのコンパイルオプションを変更します。

(2) 使用権限

関数,手続き,及びトリガの所有者

自分が所有する関数,手続き(自分が定義したパブリック関数,パブリック手続きを含む),及びトリガのSQLオブジェクトを再作成できます。

  • AUTHORIZATION句には,自分の認可識別子だけ指定できます。

  • AUTHORIZATION句を省略した場合,エラーとなります。

DBA権限を持つユーザ

自分が所有する関数,手続き(自分が定義したパブリック関数,パブリック手続きを含む),及びトリガ,並びに他ユーザが所有する関数,手続き(他ユーザが定義したパブリック関数,パブリック手続きを含む),及びトリガのSQLオブジェクトを再作成できます。

  • AUTHORIZATION句には,自分及び他ユーザの認可識別子を指定できます。

  • AUTHORIZATION句を省略することで,システム内のすべての関数及び手続きのSQLオブジェクトを再作成できます。

(3) 形式

 ALTER ROUTINE〔〔AUTHORIZATION 認可識別子〕〔ALL〕〕
   〔SQLコンパイルオプション〔SQLコンパイルオプション〕…〕
 
 SQLコンパイルオプション::={ISOLATION データ保証レベル 〔FOR UPDATE {EXCLUSIVE|COMPATIBLE}〕
                |OPTIMIZE LEVEL SQL最適化オプション
                         〔,SQL最適化オプション〕…
                |ADD OPTIMIZE LEVEL SQL拡張最適化オプション
                         〔,SQL拡張最適化オプション〕…
                |SUBSTR LENGTH 文字の最大長 }

(4) オペランド

(a) 〔AUTHORIZATION 認可識別子〕 〔ALL〕

再作成する関数,手続き,及びトリガを,所有者の認可識別子と,関数,手続き,及びトリガの状態で指定します。

AUTHORIZATION 認可識別子

関数,手続き,及びトリガの所有者(パブリック関数,パブリック手続きの定義者を含む)の認可識別子を指定して,そのユーザが所有するすべての関数,手続き,及びトリガのSQLオブジェクトを再作成します。このオペランドを省略した場合,システム内のすべての関数,手続き,及びトリガのSQLオブジェクトを再作成します。ただし,実際にSQLオブジェクトを再作成するかどうかは,ALL句の指定との組み合わせによって決まります。

認可識別子

再作成する関数,手続き,及びトリガの,所有者の認可識別子を指定します。

ALL

指定した関数,手続き,及びトリガのSQLオブジェクトが有効か無効かに関係なく,すべて再作成する場合に指定します。

このオペランドを省略した場合,SQLオブジェクトが無効な関数,手続き,及びトリガだけ再作成します。

(b) SQLコンパイルオプション

::={ISOLATION データ保証レベル 〔FOR UPDATE {EXCLUSIVE|COMPATIBLE}〕
    |OPTIMIZE LEVEL SQL最適化オプション〔,SQL最適化オプション〕…
    |ADD OPTIMIZE LEVEL SQL拡張最適化オプション〔,SQL拡張最適化オプション〕…
    |SUBSTR LENGTH 文字の最大長 }

SQLコンパイルオプションにはISOLATION,OPTIMIZE LEVEL,ADD OPTIMIZE LEVEL,SUBSTR LENGTHをそれぞれ1回しか指定できません。

〔ISOLATION データ保証レベル 〔FOR UPDATE {EXCLUSIVE|COMPATIBLE}〕〕

SQLのデータ保証レベルを指定します。

データ保証レベル

データ保証レベルとは,トランザクションのどの時点までデータの内容を保証するかのレベルです。次に示すデータ保証レベルを指定できます。

  • 0

    データの内容を保証しない場合に指定します。

    0レベルを指定すると,ほかのユーザが更新中のデータでも,更新完了を待たないで参照できます。ただし,参照する表が共用表の場合,ほかのユーザが排他モードでLOCK文を実行しているときには,排他解除待ちとなります。

  • 1

    検索処理の終了までデータの内容を保証したい場合に指定します。

    1レベルを指定すると,検索処理が終了するまで(HiRDBがページ,又は行を見終わるまで)一度検索したデータをほかのユーザは更新できません。

  • 2

    トランザクションの終了まで一度検索したデータの内容を保証したい場合に指定します。

    2レベルを指定すると,トランザクションが終了するまで一度検索したデータをほかのユーザは更新できません。

〔FOR UPDATE {EXCLUSIVE|COMPATIBLE}〕

手続き中で,FOR UPDATE句を指定した(FOR UPDATEが仮定される場合を含む)カーソル又は問合せに対して,SQLコンパイルオプションで指定したデータ保証レベルに関係なく,常にWITH EXCLUSIVE LOCKを仮定する場合に指定します。

このオペランドを省略した場合,EXCLUSIVEを仮定します。ただし,0904互換モードを適用している場合はCOMPATIBLEを仮定します。

09-50より前のHiRDBでFOR UPDATE EXCLUSIVEを省略した関数,手続き,及びトリガと同じ動作をさせたい場合はCOMPATIBLEを指定します。

このオペランドと,ISOLATIONデータ保証レベルの関係から決まるFOR UPDATEの排他オプションを次に示します。

ISOLATION データ保証レベル

FOR UPDATE EXCLUSVIE

FOR UPDATE COMPATIBLE

0

WITH EXCLUSIVE LOCK

WITHOUT LOCK WAIT

1

WITH EXCLUSIVE LOCK

WITHOUT LOCK WAIT

2

WITH EXCLUSIVE LOCK

WITH EXCLUSIVE LOCK

《クライアント環境定義との関係》

ALTER ROUTINEに対して,PDISLLVL,PDFORUPDATEEXLOCKの指定は無効となります。

《SQLとの関係》

手続き中のSQL文に排他オプションを指定している場合はSQLコンパイルオプションで指定したデータ保証レベル,FOR UPDATE EXCLUSIVE,及びFOR UPDATE COMPATIBLEから仮定する排他オプションよりSQL文に指定した排他オプションが優先されます。

《システム定義との関係》

ALTER ROUTINEに対して,pd_isolation_levelオペランドの指定は無効となります。

このオペランドを省略した場合,前回のSQLオブジェクト作成時(CREATE PROCEDURE,ALTER PROCEDURE,CREATE TYPE,ALTER ROUTINE,CREATE TRIGGER,又はALTER TRIGGERの実行時)に指定した値が仮定されます。

データ保証レベルについては,マニュアル「HiRDB UAP開発ガイド」を参照してください。

〔OPTIMIZE LEVEL SQL最適化オプション〔,SQL最適化オプション〕…〕

データベースの状態を考慮して,最も効率的なアクセスパスを決定するための最適化の方法を指定します。

SQL最適化オプションの指定値及びその内容については,マニュアル「HiRDB UAP開発ガイド」の「PDSQLOPTLVL」を参照してください。

SQL最適化オプションは,識別子(文字列)で指定する方法と,数値で指定する方法がありますが,通常時は識別子で指定する方法をお勧めします。

省略した場合,前回のSQLオブジェクト作成時(CREATE PROCEDURE,ALTER PROCEDURE,CREATE TYPE,ALTER ROUTINE,CREATE TRIGGER,又はALTER TRIGGER)に採用した値が仮定されます。

識別子で指定する場合:
OPTIMIZE LEVEL "識別子"〔,"識別子"〕…

<指定例>

  • ネストループジョイン優先とグループ分け高速化処理を適用する場合

    OPTIMIZE LEVEL "PRIOR_NEST_JOIN","RAPID_GROUPING"

  • すべての最適化を適用しない場合

    OPTIMIZE LEVEL "NONE"

<規則>

  1. 識別子は一つ以上指定してください。

  2. 識別子を二つ以上指定する場合は,コンマ(,)で区切ってください。

  3. すべての最適化を適用しない場合は,識別子に"NONE"を指定してください。ただし,同時に"NONE"以外の識別子を指定すると,"NONE"は無効になります。

  4. 識別子は大文字及び小文字で指定できます。

  5. 同じ識別子を二つ以上指定しても,一つ指定したものとみなされますが,なるべく同じ識別子は指定しないようにしてください。

数値で指定する場合:
OPTIMIZE LEVEL 符号なし整数〔,符号なし整数〕…

<指定例>

  • 複数のSQLオブジェクト作成,ANDの複数インデクス利用の抑止,及び複数インデクス利用の強制を適用する場合

    符号なし整数をコンマで区切って指定する場合:

    OPTIMIZE LEVEL 4,10,16

    符号なし整数の和を指定する場合:

    OPTIMIZE LEVEL 30

  • 既に14(4+10)を指定していて,新たに16を追加する場合

    OPTIMIZE LEVEL 14,16

  • すべての最適化を適用しない場合

    OPTIMIZE LEVEL 0

<規則>

  1. バージョン06-00より前のHiRDBから,バージョン06-00以降のHiRDBにバージョンアップする場合,バージョン06-00より前の合計値指定も有効となります。最適化オプションを変更する必要がない場合は,バージョン06-00以降のHiRDBにバージョンアップしたときにこのオペランドの指定値を変更する必要はありません。

  2. 符号なし整数は一つ以上指定してください。

  3. 符号なし整数を二つ以上指定する場合は,コンマ(,)で区切ってください。

  4. すべての最適化を適用しない場合は,符号なし整数に0を指定してください。ただし,同時に0以外の識別子を指定すると,0は無効になります。

  5. 同じ符号なし整数を二つ以上指定しても,一つ指定したものとみなされますが,なるべく同じ符号なし整数は指定しないようにしてください。

  6. 複数の最適化方法を指定する場合,その符号なし整数の和を指定することもできます。ただし,同じ最適化方法の値は二つ以上足さないでください(足した結果が別の最適化方法とみなされることもあるため)。

  7. 複数の最適化方法の値を足して指定する場合,どの最適方法を指定しているのか分かりにくくなるため,コンマで区切って指定する方法をお勧めします。また,既に複数の最適化方法の値を足して指定している場合で,新たに別の最適化方法が必要になったときは,追加する値をコンマで区切って後ろに指定できます。

《システム定義との関係》
  1. ALTER ROUTINEに対して,システム定義のpd_optimize_levelオぺランドの指定は無効となります。

  2. システム定義のpd_floatable_besオペランド,又はpd_non_floatable_besオペランドを指定している場合,「フロータブルサーバ対象拡大(データ取り出しバックエンドサーバ)」及び「フロータブルサーバ対象限定(データ取り出しバックエンドサーバ)」の指定は無効となります。

  3. システム定義のpd_indexlock_modeオペランドにKEYを指定している場合(インデクスキー値排他の場合),「更新SQLの作業表作成抑止」の指定は無効になります。

《クライアント環境定義との関係》

ALTER ROUTINEに対して,PDSQLOPTLVLの指定は無効となります。

《SQLとの関係》

SQL文中にSQL最適化指定を指定している場合は,SQL最適化オプションよりもSQL最適化指定が優先されます。SQL最適化指定については,「SQL最適化指定」を参照してください。

〔ADD OPTIMIZE LEVEL SQL拡張最適化オプション〔,SQL拡張最適化オプション〕…〕

データベースの状態を考慮して,最も効率的なアクセスパスを決定するための最適化の方法を指定します。

SQL拡張最適化オプションの指定値及びその内容については,マニュアル「HiRDB UAP開発ガイド」の「PDADDITIONALOPTLVL」を参照してください。

SQL拡張最適化オプションは,識別子(文字列)で指定する方法と,数値で指定する方法があります。

省略した場合,前回のSQLオブジェクト作成時(CREATE PROCEDURE,ALTER PROCEDURE,CREATE TYPE,ALTER ROUTINE,CREATE TRIGGER,又はALTER TRIGGER)に採用した値が仮定されます。

識別子で指定する場合:
ADD OPTIMIZE LEVEL "識別子"〔,"識別子"〕…

<指定例>

  • 「コストベース最適化モード2の適用」及び「ハッシュジョイン,副問合せのハッシュ実行」を適用する場合

    ADD OPTIMIZE LEVEL "COST_BASE_2","APPLY_HASH_JOIN"

  • すべての最適化を適用しない場合

    ADD OPTIMIZE LEVEL "NONE"

<規則>

  1. 識別子は一つ以上指定してください。

  2. 識別子を二つ以上指定する場合は,コンマ(,)で区切ってください。

  3. すべての最適化を適用しない場合は,識別子に"NONE"を指定してください。

  4. 識別子は大文字及び小文字で指定できます。

  5. 同じ識別子を二つ以上指定しても,一つ指定したものとみなされますが,なるべく同じ識別子は指定しないようにしてください。

数値で指定する場合:
ADD OPTIMIZE LEVEL 符号なし整数〔,符号なし整数〕…

<指定例>

  • 「コストベース最適化モード2の適用」及び「ハッシュジョイン,副問合せのハッシュ実行」を適用する場合

    ADD OPTIMIZE LEVEL 1,2

  • すべての最適化を適用しない場合

    ADD OPTIMIZE LEVEL 0

<規則>

  1. 符号なし整数は一つ以上指定してください。

  2. 符号なし整数を二つ以上指定する場合は,コンマ(,)で区切ってください。

  3. すべての最適化を適用しない場合は,符号なし整数に0を指定してください。

  4. 同じ符号なし整数を二つ以上指定しても,一つ指定したものとみなされますが,なるべく同じ符号なし整数は指定しないようにしてください。

《システム定義との関係》

ALTER ROUTINEに対して,システム定義のpd_optimize_levelオぺランドの指定は無効となります。

《クライアント環境定義との関係》

ALTER PROCEDUREに対して,PDADDITIONALOPTLVLの指定は無効となります。

《SQLとの関係》

SQL文中にSQL最適化指定を指定している場合は,SQL最適化オプションよりもSQL最適化指定が優先されます。SQL最適化指定については,「SQL最適化指定」を参照してください。

〔SUBSTR LENGTH 文字の最大長〕

1文字を表現する最大バイト数を指定します。

文字の最大長に指定できる値は,3〜6(pdntenvコマンド(UNIX版の場合はpdsetupコマンド)で文字コード種別にutf-8_ivsを指定した場合は3〜10)です。

pdntenvコマンド(UNIX版の場合はpdsetupコマンド)で文字コード種別にutf-8,又はutf-8_ivsを指定した場合にだけ有効となり,スカラ関数SUBSTRの結果の長さに影響します。SUBSTRについては,「SUBSTR」を参照してください。

<規則>

バージョン08-00より前のHiRDBからバージョン08-00以降のHiRDBにバージョンアップする場合は,3を仮定します。文字の最大長を変更する必要がない場合は,バージョン08-00以降のHiRDBにバージョンアップしたときにこのオペランドを指定する必要はありません。

《システム定義との関係》

ALTER ROUTINEに対して,システム定義のpd_substr_lengthオぺランドの指定は無効となります。pd_substr_lengthオぺランドについては,マニュアル「HiRDB システム定義」を参照してください。

《クライアント環境定義との関係》

ALTER ROUTINEに対して,PDSUBSTRLENの指定は無効となります。PDSUBSTRLENについては,マニュアル「HiRDB UAP開発ガイド」を参照してください。

《pdntenvコマンド又はpdsetupコマンドで指定した文字コード種別との関係》

文字コード種別にutf-8,又はutf-8_ivsを指定した場合だけ有効になります。

そのほかの文字コード種別の場合は,構文チェックだけ行い,指定を無視します。

このオペランドを省略した場合,前回のSQLオブジェクト作成時(CREATE PROCEDURE,ALTER PROCEDURE,CREATE FUNCTION,CREATE TYPE,ALTER ROUTINE,CREATE TRIGGER,又はALTER TRIGGERの実行時)に指定した値が仮定されます。

(5) 共通規則

  1. ALTER ROUTINEでSQLコンパイルオプションを指定する場合,再作成するルーチンの元のCREATE PROCEDURE,CREATE FUNCTION,CREATE TYPE,又はCREATE TRIGGERにSQLコンパイルオプションを反映してできるSQL文は,SQL文の最大長を超えないようにしてください。

  2. SQLコンパイルオプションの指定は手続き及びトリガに対してだけ有効となり,関数に対しては無効となります。

  3. 次の条件では,Java手続きからALTER ROUTINEを実行できません。

    • 実行中のSQLオブジェクトが再作成になる場合

(6) 留意事項

  1. ALTER ROUTINEは,OLTP下のX/Openに従ったUAPから指定できません。

  2. 複数の関数,手続き,及びトリガのSQLオブジェクトを再作成する場合,関数,手続き,及びトリガごとに自動的にCOMMIT文又はROLLBACK文が実行されます。

  3. ALTER ROUTINE実行直後にGET DIAGNOSTICS文を実行すると,ALTER ROUTINEの診断情報を取得できます。このとき,再作成が正常終了した関数,手続き,及びトリガのSQLコードは0となります。

  4. データ保証レベル,SQL最適化オプション,SQL拡張最適化オプション,及び文字の最大長は,ルーチン及びトリガの定義時,又は再作成時の指定で決まり,手続き呼出し時,関数呼出し時,及びトリガ動作実行時のシステム定義やクライアント環境定義の影響を受けません。

  5. 手続き及びトリガで使用している表(そのトリガを定義した表を除く)に対してインデクスの追加又は削除をした場合,手続き及びトリガのSQLオブジェクト中のインデクス情報が無効となります。この場合,このトリガは実行できません。また,手続き又はトリガからこの手続きを実行できないため,ALL指定でSQLオブジェクトを再作成してください。

  6. Java手続き及びJava関数はSQLオブジェクトが作成されないため,SQLオブジェクトの再作成はしません。SQLコンパイルオプションの更新だけできます。

  7. ネストするトリガがある場合,次のことに注意してください。

    • ネストするトリガの一部,又はすべてが無効状態となっている場合,1回のALTER ROUTINE実行ではすべてのトリガが有効にならないことがあります(ALTER ROUTINEはKFPA11528-Eエラーとなります)。この場合,ALTER ROUTINEが正常終了するまで,ALTER ROUTINEを繰り返し実行してください。

  8. ネストするトリガがある場合,かつトリガ動作中の探索条件に関数を指定している場合は,次のことに注意してください。

    • この関数を削除した場合,ALTER ROUTINEが正常終了しても,トリガ実行時にKFPA11529-Eエラーとなることがあります。この場合,実行時エラーとなったトリガ又はルーチンを再作成(ALTER TRIGGER又はALTER PROCEDURE)して再実行してください。実行時エラーとなるトリガ又はルーチンは,関数を削除したトリガの呼び出し元のトリガ又はルーチンです。

  9. ループするトリガがある場合,次のことに注意してください。

    • ループするトリガがすべて無効状態になっている場合,ALTER ROUTINEでの再作成はできません。この場合,ループするトリガ及びこれらのトリガを定義している表にあるすべてのトリガを削除し,再定義してください。

  10. 他ユーザが定義したパブリック関数,パブリック手続きのSQLオブジェクトを再作成した場合も,そのSQLオブジェクトの定義者は変わりません。

(7) 使用例

  1. すべての関数,手続き,及びトリガの中で,SQLオブジェクトが無効となっている関数及び手続きを再作成します。

      ALTER ROUTINE
  2. ユーザ(USER1)の関数,手続き,及びトリガをすべて再作成します。

      ALTER ROUTINE
          AUTHORIZATION USER1 ALL