Hitachi

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


3.14.1 CREATE PROCEDURE(手続き定義)

〈この項の構成〉

(1) 機能

手続きを定義します。

(2) 使用権限

スキーマを所有するユーザ

自分が所有する手続きを定義できます。

(3) 形式

 CREATE 手続き本体
 手続き本体::=PROCEDURE 〔認可識別子.〕ルーチン識別子
         (〔{IN|OUT|INOUT} SQLパラメタ名 データ型
            〔,{IN|OUT|INOUT} SQLパラメタ名 データ型〕…〕)
    〔DYNAMIC RESULT SETS 結果集合数〕
    〔LANGUAGE句〕
    〔SQLコンパイルオプション〔SQLコンパイルオプション〕…〕
    {SQL手続き文|外部ルーチン指定}
 
 LANGUAGE句::=LANGUAGE{SQL|JAVA|C}
 SQLコンパイルオプション::={ISOLATION データ保証レベル 〔FOR UPDATE {EXCLUSIVE|COMPATIBLE}〕
                |OPTIMIZE LEVEL SQL最適化オプション
                         〔,SQL最適化オプション〕…
                |ADD OPTIMIZE LEVEL SQL拡張最適化オプション
                         〔,SQL拡張最適化オプション〕…
                |SUBSTR LENGTH 文字の最大長 }
 外部ルーチン指定::=EXTERNAL NAME {外部Javaルーチン名|外部Cストアドルーチン名}
            PARAMETER STYLE パラメタスタイル
 パラメタスタイル::={JAVA|RDSQL}

(4) オペランド

(a) 〔認可識別子.〕ルーチン識別子

認可識別子

定義する手続きの所有者の認可識別子を指定します。

ルーチン識別子

定義する手続きのルーチンの名前を指定します。

(b) (〔{IN|OUT|INOUT} SQLパラメタ名 データ型〔,{IN|OUT|INOUT} SQLパラメタ名 データ型〕…〕)

{IN|OUT|INOUT}

手続きのパラメタの入出力モード(パラメタモード)を指定します。

IN

入力パラメタの場合に指定します。

OUT

出力パラメタの場合に指定します。

INOUT

入力及び出力パラメタの場合に指定します。

SQLパラメタ名

手続きのパラメタの名称を指定します。一つの手続き中で,SQLパラメタ名は重複して指定できません。

データ型

手続きのパラメタのデータ型を指定します。

指定するデータ型が抽象データ型で,認可識別子を省略した場合,省略時に仮定される認可識別子に同一名称の抽象データ型がないときは,認可識別子'MASTER'に同一名称の抽象データ型があれば,その抽象データ型を指定したものとします。

次のデータ型は指定できません。

  • LANGUAGE句でJAVA又はCを指定した場合,抽象データ型

  • LANGUAGE句でCを指定した場合,BINARY型及びBLOB型

指定できるデータ型については,「型マッピング」又は「CREATE 〔PUBLIC〕FUNCTION(関数定義,パブリック関数定義)」を参照してください。

(c) DYNAMIC RESULT SETS 結果集合数

定義する手続きが返す,結果集合の最大数を整数値で指定します。

指定できる値は0〜1023です。

結果集合数に0を指定するか,又はこのオペランドを省略した場合,結果集合を返さない手続きとみなされます。

ただし,LANGUAGE句でCを指定した場合,このオペランドの指定を無視します。

(d) LANGUAGE{SQL|JAVA|C}

定義する手続きの記述言語を指定します。

外部ルーチン指定を記述する場合,記述言語にSQLは指定しないでください。

SQL

手続きの処理部分をSQL手続き文にする場合に指定します。

JAVA

手続きの処理部分を外部ルーチン指定にする場合で,手続きをJavaクラスのメソッドで実装するときに指定します。

C

手続きの処理部分を外部ルーチン指定にする場合で,手続きをC言語で記述されたモジュールによって実装するときに指定します。Cを指定した場合,SQLパラメタの最大個数が128に制限されます。

このオペランドの指定によって,他オペランドの指定要否が変わります。LANGUAGE句指定による他オペランドの指定要否を次の表に示します。

表3‒20  CREATE PROCEDUREのLANGUAGE句指定による他オペランドの指定要否

他オペランド

LANGUAGE句

SQL

JAVA

C

EXTERNAL NAME

×

PARAMETER STYLE

×

JAVA

RDSQL

SQL手続き文

×

×

(凡例)

○:指定してください。

×:指定しないでください。

JAVA:JAVAを指定してください。

RDSQL:RDSQLを指定してください。

(e) 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がページ,又は行を見終わるまで)1度検索したデータをほかのユーザは更新できません。

  • 2

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

〔FOR UPDATE{EXCLUSIVE|COMPATIBLE}〕

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

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

バージョン09-50より前のHiRDBでFOR UPDATE EXCLUSIVEを省略した手続きと同じ動作をさせたい場合はCOMPATIBLEを指定します。

バージョン09-50より前のHiRDBから,09-50以降のHiRDBにバージョンアップする場合の注意事項を次に示します。

  • 手続きを再定義する場合で,バージョン09-50より前のHiRDBでFOR UPDATE EXCLUSIVEを省略した手続きと同じ動作をさせたい場合は次のどちらかの対処をしてください。

    ・ALTER PROCEDURE又はALTER ROUTINEで手続きを再定義してください。

    ・手続きを削除し,CREATE PROCEDUREで手続きを再定義する場合は,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

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

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

《SQLとの関係》

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

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

データ保証レベルを省略すると,pd_isolation_levelオぺランドの指定値が仮定されます。pd_isolation_levelオぺランドについては,マニュアル「HiRDB システム定義」を参照してください。

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

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

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

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

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

識別子で指定する場合:
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. SQL最適化オプションを省略すると,システム定義のpd_optimize_levelオぺランドの指定値が仮定されます。pd_optimize_levelオペランドについては,マニュアル「HiRDB システム定義」を参照してください。

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

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

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

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

《SQLとの関係》

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

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

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

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

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

識別子で指定する場合:
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. 同じ符号なし整数を二つ以上指定しても,一つ指定したものとみなされますが,なるべく同じ符号なし整数は指定しないようにしてください。

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

SQL拡張最適化オプションを省略すると,システム定義のpd_additional_optimize_levelオぺランドの指定値が仮定されます。pd_additional_optimize_levelオペランドについては,マニュアル「HiRDB システム定義」を参照してください。

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

CREATE 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」を参照してください。

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

SUBSTR LENGTHを省略すると,システム定義のpd_substr_lengthオぺランドの指定値が仮定されます。pd_substr_lengthオペランドについては,マニュアル「HiRDB システム定義」を参照してください。

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

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

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

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

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

(f) SQL手続き文

手続きで実行するSQL手続き文を指定します。SQL手続き文については,「7. ルーチン制御SQL」の「全般規定」を参照してください。

(g) EXTERNAL NAME {外部Javaルーチン名|外部Cストアドルーチン名}

外部Javaルーチン名

Java言語で記述したJavaメソッドを外部ルーチンとして指定します。外部Javaルーチン名の指定方法については,「外部Javaルーチン名」を参照してください。

外部Cストアドルーチン名

C言語で記述したC関数を外部ルーチンとして指定します。外部Cストアドルーチン名の指定方法については,「外部Cストアドルーチン名」を参照してください。

(h) PARAMETER STYLE パラメタスタイル

外部ルーチンを呼び出す場合,パラメタとして引き渡される内容を指定します。

パラメタスタイルがJAVAの場合

SQLデータ型で定義している外部Java手続きのパラメタは,SQLデータ型に対応したJavaデータ型のJavaメソッドのパラメタとして渡されます。

SQLデータ型で定義されている外部Java手続きのOUT及びINOUTパラメタが,SQLデータ型に対応するJavaデータ型であり,要素数1の配列であるJavaメソッドのパラメタとして渡されます。Javaメソッド終了後,Javaメソッドが書き込んだ配列は,外部Java手続きの出力パラメタとして扱われます。

パラメタスタイルがRDSQLの場合

SQLパラメタ数をnとすると,外部C関数のSQLパラメタは,次の表に示すように,C関数のパラメタとして渡されます。

表3‒21 C関数のパラメタとして渡される内容

パラメタ番号

(nはSQL

パラメタ数)

内容

内容の詳細

データ型

1番目〜n番目

SQLパラメタのデータ部

SQLパラメタのデータ部に対応するパラメタです。各パラメタの入出力モードは,対応するSQLパラメタの入出力モードと同じです。

SQLパラメタのデータ型に対応するデータ型※1

n+1番目〜2n番目

SQLパラメタの標識部

SQLパラメタの標識部に対応するパラメタです。各パラメタの入出力モードは,対応するSQLパラメタの入出力モードと同じです。

入力パラメタである場合,データがナル値ならば,負の値を設定してC関数に渡します。

出力パラメタである場合,外部Cストアドルーチンを実装したC関数内では,次の説明に従って標識部を設定してください。

  • 出力値がナル値の場合

    設定値:−1

  • 出力値が非ナル値の場合

    設定値:0

short*

2n+1番目

SQLSTATE

外部Cストアドルーチンを実装したC関数が,SQLSTATEの値を設定するための出力パラメタです。領域長は6バイトです。領域の先頭から5バイト目までにSQLSTATEの値を設定してください。外部Cストアドルーチンを実装したC関数内では,次の説明に従って,SQLSTATEの値を設定してください。

  • C関数が正常終了したとき

    設定値:‘00000’

    SQL実行結果:正常終了します。

  • C関数が異常終了したとき

    設定値:形式’38XYY’の値

    X,Yは,それぞれ次の範囲の値です。

    X:’I’〜’Z’

    Y:’0’〜’9’又は’A’〜’Z’

    (例)’38I01’,’38ZCD’

    SQL実行結果:SQLエラーになります。

  • C関数が任意の状態で終了したとき

    設定値:‘00000’,形式’38XYY’の値以外

    SQL実行結果:SQLエラーになります。

char*

2n+2番目

ルーチン名

ルーチン名を表す入力パラメタです。

struct{

short 変数名1;

char変数名2[30];

}*※2

2n+3番目

特定名

手続きを特定するための特定名を表す入力パラメタです。

struct{

short 変数名1;

char変数名2[30];

}*※2

2n+4番目

メッセージテキスト

外部Cストアドルーチンを実装したC関数内でエラーが発生した場合,エラーが発生した詳細な理由を設定するための出力パラメタです。

外部Cストアドルーチンの実行終了時,SQLSTATEにクラス38の値が設定されていると,メッセージテキストを埋め込んだエラーメッセージが出力されます。

なお,設定できるメッセージテキストの長さは,最大80バイトです。

struct{

short 変数名1;

char変数名2[80];

}*※2

注※1

外部C手続き定義時に指定できるSQLパラメタのデータ型と,外部Cストアドルーチンを実装したC関数に渡すパラメタのデータ型の対応関係については,外部C関数定義時の対応関係と同じです。詳細は,「CREATE 〔PUBLIC〕FUNCTION(関数定義,パブリック関数定義)」を参照してください。

注※2

変数名1に文字列長(バイト数),変数名2に内容を表す文字列を設定します。

(5) 共通規則

  1. ルーチン識別子は,以下に示す手続きの識別子と同じ識別子を指定できません。

    • 所有者の手続き

    • パブリック手続きの手続き

  2. 入力パラメタは,CALL文の対象となるルーチンの引数の入出力パラメタがOUT又はINOUTで定義されたもの,FETCH文,及び1行SELECT文のINTO句,並びに代入文の代入先には指定できません。

  3. 出力パラメタは,CALL文の対象となるルーチンの引数の入出力パラメタがOUT又はINOUTで定義されたもの,FETCH文のINTO句,1行SELECT文のINTO句,代入文の代入先,及びWRITE LINE文の値式以外では指定できません。

  4. 手続きのパラメタは,30,000個(LANGUAGE句がCの場合は128個)以下でなければなりません。ただし,LANGUAGE句にJavaを指定した場合,30,000個以下でも,使用するJavaVMによっては,その仕様上の制限によって実行時にエラーになる場合があります。

  5. 手続きを特定するための特定名は,〔認可識別子.〕ルーチン識別子と同じです。

  6. 入力パラメタ,及び出力パラメタには,BOOLEAN型を指定できません。

  7. 手続き本体中に指定したCALL文の手続き名が認可識別子で修飾されている場合,現在定義しようとしている手続きと,認可識別子,ルーチン識別子が一致する手続きは指定できません。

    手続き本体中に指定したCALL文の手続き名が認可識別子で修飾されていない場合,現在定義しようとしている手続きと,ルーチン識別子が一致する手続きは指定できません。

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

  9. SQL手続き文中の問合せ指定でWRITE指定は指定できません。

  10. LANGUAGE句にCを指定している場合,結果集合を返却することはできません。

(6) 留意事項

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

  2. SQLパラメタは,ナル値が持てます。

  3. SQLルーチン中で複数のSQL文を実行するためには,複合文などのルーチン制御SQLを使用します。

  4. SQL手続きを定義すると,それを実行するためのアクセス手順を記述したSQLオブジェクトが作成されます。外部JAVA手続きを定義した場合,手続き中のSQL文を実行するためのアクセス手順を記述したオブジェクトは作成されません。

  5. 手続き中のSQL文のデータ型のデータ保証レベル,SQL最適化オプション, SQL拡張最適化オプション,及び文字の最大長は,手続きの定義時,又は変更時の指定で決まり,手続き呼び出し時のシステム定義やクライアント環境定義の影響を受けません。

  6. ルーチン内でのルーチンの呼び出しを多数又は無限に繰り返す場合,OSのスタックがオーバフローすることがあります。

  7. 外部Java手続きから次のSQLは実行できません。

    • COMMIT文,LOCK文,ROLLBACK文以外の制御系SQL

    • ルーチン制御SQL

  8. SQL手続き中では,結果集合を受け取ることはできません。

  9. 外部Java手続き中で取得したDatabaseMetaDataクラスの,メソッドが返却する結果集合(ResultSet)は,動的結果集合として返却できません。外部Java手続きのコール元のコネクションのメタデータを使用して,情報を取得してください。