CREATE PROCEDURE

procname

ストアド・プロシージャとして生成するメソッドまたはクエリの名前です。この名前は、非修飾 (StoreName) にして既定のスキーマ名を使用しても、スキーマ名を指定して修飾 (Patient.StoreName) してもかまいません。_CURRENT_USER をスキーマ名として指定した場合、Caché は既定のスキーマ名を使用します。$SYSTEM.SQL.DefaultSchema()Opens in a new tab メソッドを使用して、既定のスキーマ名を確認できます。既定のスキーマ名 SQLUser は、クラス・パッケージ名 User に対応しています。

procname が非修飾の場合は、生成されるクラスの名前は、既定のスキーマ名の後に、ドット、“proc”、指定の name が順に続いたものになります。したがって、非修飾名 StoreName からは、User.procStoreName のようなクラス名が得られます。詳細は、"Caché SQL の使用法" の “ストアド・プロシージャの定義と使用” の章の "SQL からクラス名への変換" を参照してください。

Caché SQL では、大文字/小文字が異なるだけの procname を指定することは許可されていません。既存のプロシージャ名と大文字/小文字が異なるだけの procname を指定した場合は、SQLCODE -400 エラーが生成されます。

指定した procname が現在のネームスペース内に既に存在する場合は、SQLCODE -361 エラーが生成されます。指定の procname が現在のネームスペースに既に存在するかどうかを確認するには、$SYSTEM.SQL.ProcedureExists()Opens in a new tab メソッドを使用します。

プロシージャ名の後には、パラメータの括弧が必要です。

parameter_list

値をメソッドまたはクエリに渡すために使用するパラメータのリストです。パラメータのリストは括弧で囲み、リストのパラメータ宣言はコンマで区切ります。パラメータを指定しない場合も括弧は必須です。

リスト内の各パラメータ宣言は、(先頭から順番に) 以下の要素で構成します。

• パラメータ・モードが IN (入力値)、OUT (出力値)、または INOUT (変更値) のいずれであるかを指定するオプションのキーワード。省略した場合、既定のパラメータ・モードは IN です。

• パラメータ名。パラメータ名では、大文字と小文字が区別されます。

• パラメータのデータ型。

• オプション ： パラメータの既定値。DEFAULT キーワードの後ろに既定値を付けて指定できます。DEFAULT キーワードはオプションです。既定値が指定されていない場合、既定値は NULL であると見なされます。

以下の例では、入力パラメータが 2 つある (どちらにも既定値がある) ストアド・プロシージャを作成します。一方の入力パラメータではオプションの DEFAULT キーワードを指定し、もう一方の入力パラメータではこのキーワードを省略します。

CREATE PROCEDURE AgeQuerySP(IN topnum INT DEFAULT 10,IN minage INT 20)
   BEGIN
   SELECT TOP :topnum Name,Age FROM Sample.Person
   WHERE Age > :minage ;
   END

以下の例は、前述の例と機能的に同じです。オプションの DEFAULT キーワードは省略されます。

CREATE PROCEDURE AgeQuerySP(IN topnum INT 10,IN minage INT 20)
   BEGIN
   SELECT TOP :topnum Name,Age FROM Sample.Person
   WHERE Age > :minage ;
   END

CALL AgeQuerySP(6,65)、CALL AgeQuerySP(6)、CALL AgeQuerySP(,65)、CALL AgeQuerySP() はすべて、このプロシージャで有効な CALL 文です。

以下の例では、3 つのパラメータを持つストアド・プロシージャとして公開されるメソッドを作成します。

CREATE PROCEDURE UpdatePaySP
  (IN Salary INTEGER DEFAULT 0,
   IN Name VARCHAR(50), 
   INOUT PayBracket VARCHAR(50) DEFAULT 'NULL')
BEGIN
   UPDATE Sample.Person SET Salary = :Salary
   WHERE Name=:Name ;
END

ストアド・プロシージャはパラメータの自動形式変換を実行しません。例えば、ODBC 形式や表示形式の入力パラメータは、その形式を保持します。アプリケーションに適した形式で入出力値を処理して必要な変換を実行するのは、プロシージャを呼び出すコードと、そのプロシージャ・コード自体の責任になります。

メソッドまたはクエリがストアド・プロシージャとして公開されるため、プロシージャ・コンテキスト・ハンドラによって、プロシージャとその呼び出し元間でプロシージャ・コンテキストの相互受け渡しが行われます。ストアド・プロシージャが呼び出されると、クラス %Library.SQLProcContextOpens in a new tab のオブジェクトが %sqlcontext 変数でインスタンス化されます。これを使用して、プロシージャとその呼び出し元 (ODBC サーバなど) 間でプロシージャ・コンテキストの受け渡しが行われます。

%sqlcontext は、エラー・オブジェクト、SQLCODE エラー・ステータス、SQL 行カウント、およびエラー・メッセージを含む、いくつかのプロパティで構成されます。以下の例は、これらプロパティの設定に使用される値を示しています。

  SET %sqlcontext.%SQLCODE=SQLCODE
  SET %sqlcontext.%ROWCOUNT=%ROWCOUNT
  SET %sqlcontext.%Message=%msg

SQLCODE と %ROWCOUNT の値は、SQL 文の実行によって自動的に設定されます。%sqlcontext オブジェクトは、実行される前に毎回リセットされます。

または、%SYSTEM.Error オブジェクトをインスタンス化し、%sqlcontext.Error として設定することによって、エラー・コンテキストを設定することができます。

characteristics

メソッドの生成にはクエリの生成とは異なる characteristics を使用します。

指定した characteristics の値が無効な場合は、SQLCODE -47 エラーが生成されます。characteristics を重複して指定すると、SQLCODE -44 エラーが発生します。

有効なメソッド characteristics キーワードは、以下のとおりです。

有効なクエリ characteristics キーワードは、以下のとおりです。

SELECTMODE 節は、SELECT クエリ操作、および INSERT と UPDATE 操作で使用します。これは、コンパイル時の選択モードを指定します。SELECTMODE に指定した値は、#SQLCompile Select=mode のように ObjectScript クラス・メソッド・コードの最初に追加されます。詳細は、"Caché ObjectScript の使用法" の “ObjectScript マクロとマクロ・プリプロセッサ” の章にある "#SQLCompile Select" を参照してください。

• SELECT クエリでは、SELECTMODE はデータを返すモードを指定します。mode 値が LOGICAL の場合は、論理 (内部保存) 値が返されます。例えば、日付は $HOROLOG 形式で返されます。mode 値が ODBC の場合、論理から ODBC への変換が適用され、ODBC 形式値が返されます。mode 値が DISPLAY の場合、論理から表示への変換が適用され、表示形式値が返されます。mode 値が RUNTIME の場合、実行時に表示モードを (LOGICAL、ODBC、または DISPLAY に) 設定できます。

• INSERT または UPDATE 操作では、SELECTMODE RUNTIME オプションで、表示形式 (DISPLAY または ODBC) から論理格納形式への入力データ値の自動変換がサポートされています。このコンパイルされた表示データから論理データへの変換コードは、SQL コード実行時の選択モード設定が LOGICAL (すべての Caché SQL 実行インタフェースの既定値) の場合のみ適用されます。

"Caché SQL の使用法" の “ダイナミック SQL の使用法” の章に説明されているように、SQL コードが実行されると、%SQL.StatementOpens in a new tab クラスの %SelectModeOpens in a new tab プロパティが、実行時の選択モードを指定します。SelectMode オプションの詳細は、"Caché SQL の使用法" の “Caché SQL の基礎” の章にある “データ表示オプション” を参照してください。

RESULTS 節は、クエリの結果を指定します。RESULTS 節の SQL データ型パラメータが、クエリの ROWSPEC の対応する Caché データ型パラメータに変換されます。例えば、RESULTS 節の RESULTS ( Code VARCHAR(15) ) では、ROWSPEC = “Code:%Library.String(MAXLEN=15)” という ROWSPEC 仕様が生成されます。

LANGUAGE

code_body に使用している言語を指定するキーワード節。使用可能な節は、LANGUAGE OBJECTSCRIPT (ObjectScript の場合) か、LANGUAGE SQL です。LANGUAGE 節を省略する場合は、SQL が既定です。

code_body

生成されるメソッドまたはクエリのプログラム・コード。このコードは SQL または ObjectScript で指定します。使用する言語は LANGUAGE 節と一致させる必要があります。ただし、ObjectScript のコードには埋め込み SQL を記述できます。Caché は、メソッドまたはクエリの実際のコードを生成するために提供されたコードを使用します。

• SQL プログラム・コードの開始には BEGIN キーワードを使用し、その後に SQL コード自体を続けます。完結した各 SQL 文の末尾では、セミコロン (;) を指定します。1 つのクエリに含めることができるのは、1 つの SQL 文、つまり 1 つの SELECT 文のみです。データの挿入、更新または削除を行うプロシージャを作成することもできます。SQL プログラム・コードの終了には、END キーワードを使用します。

  入力パラメータは、SQL 文内で :name の形式でホスト変数として指定されます(SQL コード内で疑問符 (?) を使用して入力パラメータを指定しないでください。プロシージャは正常に構築されますが、呼び出されたときに、これらのパラメータを渡すことができないか、既定値が使用されます)。

• ObjectScript プログラム・コードは、{ code } のように中括弧で囲みます。コード行はインデントする必要があります。ラベルまたは #Include プリプロセッサ・コマンドを指定する場合、そのラベルまたはコマンドは、次の例に示すようにコロンで始まり、1 列目に配置される必要があります。

  CREATE PROCEDURE SP123()
    LANGUAGE OBJECTSCRIPT 
  {
  :Top
  :#Include %occConstant
    WRITE "Hello World"
    IF 0=$RANDOM(2) { GOTO Top }
    ELSE {QUIT $$$OK }
  }

  

  システムによって、%occInclude が自動的に組み込まれます。プログラム・コードに Caché マクロ・プリプロセッサ文 (# コマンド、## 関数、または $$$ マクロ参照) が含まれている場合、これらの文の処理と展開はプロシージャのメソッド定義の一部であり、これらの文はこのメソッドのコンパイル時に処理および展開されます。プリプロセッサ・コマンドの詳細は、"Caché ObjectScript の使用法" の "ObjectScript マクロとマクロ・プリプロセッサ" を参照してください。

SQL を ObjectScript “ラッパ” に埋め込んだプロシージャを生成するとき、Caché によって、プロシージャ・コンテキスト・ハンドラを提供し、返り値を処理するコード行が追加されます。以下は この Caché から生成されたラッパ・コードの例です。

   NEW SQLCODE,%ROWID,%ROWCOUNT,title
   &sql(
        -- code_body
       )
   QUIT $GET(title)

指定するコードが OBJECTSCRIPT の場合、“ラッパ” (変数を NEW で処理して、QUIT val を使用して完了時に値を返す) を明示的に定義する必要があります。

SQL コードの使用例

以下の例は、ストアド・プロシージャとして公開される、PersonStateSP という名前のシンプルなクエリを作成します。このクエリでは、パラメータは宣言されず、characteristics および LANGUAGE に既定値が使用されます。

  WRITE !,"Creating a procedure"
  &sql(CREATE PROCEDURE PersonStateSP() BEGIN
       SELECT Name,Home_State FROM Sample.Person ;
       END)
  IF SQLCODE=0 { WRITE !,"Created a procedure" }
  ELSEIF SQLCODE=-361 { WRITE !,"Procedure already exists" }
  ELSE { WRITE !,"SQL error: ",SQLCODE }

管理ポータルで、[クラス] オプションを選択し、SAMPLES ネームスペースを選択します。上の例で作成したストアド・プロシージャの User.procPersonStateSP.cls を見つけます。上のプログラム例を再実行する前に、ここでこのプロシージャを削除できます。もちろん、DROP PROCEDURE を使用してプロシージャを削除できます。

  WRITE !,"Deleting a procedure"
  &sql(DROP PROCEDURE SAMPLES.PersonStateSP)
  IF SQLCODE=0 { WRITE !,"Deleted a procedure" }
  ELSEIF SQLCODE=-362 { WRITE !,"Procedure did not exist" }
  ELSE { WRITE !,"SQL error: ",SQLCODE }

以下の例は、データを更新するプロシージャを作成します。この例は、CREATE PROCEDURE を使用して、Sample.Employee クラスに UpdateSalary メソッドを生成します。

CREATE PROCEDURE UpdateSalary ( IN SSN VARCHAR(11), IN Salary INTEGER )
   FOR Sample.Employee
   BEGIN
     UPDATE Sample.Employee SET Salary = :Salary WHERE SSN = :SSN;
   END

ObjectScript コードの使用例

以下の例では、ランダムな大文字を生成する RandomLetterSP() ストアド・プロシージャ・メソッドを作成します。その後、このメソッドを関数として SELECT 文内で呼び出すことができます。RandomLetterSP() メソッドを削除するため、DROP PROCEDURE が指定されています。

CREATE PROCEDURE RandomLetterSP()
RETURNS INTEGER
LANGUAGE OBJECTSCRIPT
{
:Top
 SET x=$RANDOM(90)
 IF x<65 {GOTO Top}
 ELSE {QUIT $CHAR(x)}
}

SELECT Name FROM Sample.Person
WHERE Name %STARTSWITH RandomLetterSP()

DROP PROCEDURE RandomLetterSP

以下の CREATE PROCEDURE の例は、Execute()、Fetch()、Close() の各メソッドに対する ObjectScript による呼び出しを使用します。このようなプロシージャには、FetchRows() メソッド呼び出しおよび GetInfo() メソッド呼び出しを含めることも可能です。

CREATE PROCEDURE GetTitle()
    FOR Sample.Employee
    RESULTS (ID %Integer)
    CONTAINID 1
    LANGUAGE OBJECTSCRIPT
    Execute(INOUT qHandle %Binary)
    {  QUIT 1 }
    Fetch(INOUT qHandle %Binary, INOUT Row %List, INOUT AtEnd %Integer)
    {  QUIT 1 }
    Close(INOUT qHandle %Binary)
    {  QUIT 1 }

以下の CREATE PROCEDURE の例は、%SQL.StatementOpens in a new tab 結果セット・クラスに対する ObjectScript による呼び出しを使用します。

CREATE PROCEDURE Sample_Employee.GetTitle(
    INOUT Title VARCHAR(50) )
    RETURNS VARCHAR(30)
    FOR Sample.Employee
    LANGUAGE OBJECTSCRIPT
  {
  SET myquery="SELECT TOP 10 Name,Title FROM Sample.Employee"
  ZNSPACE "SAMPLES"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(myquery)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  DO rset.%Display()
  WRITE !,"End of data"
  }

ObjectScript コード・ブロックがデータをローカル変数 (例えば、Row) に取得する場合は、コード・ブロックを SET Row="" 行で終了して、データの終了 (end-of-data) 条件を示す必要があります。

以下の例は、ObjectScript コードで CREATE PROCEDURE を使用して、埋め込み SQL を呼び出します。このプロシージャは、Sample.Employee クラスに GetTitle メソッドを生成し、パラメータとして Title 値を渡します。

CREATE PROCEDURE Sample_Employee.GetTitle(
   IN SSN VARCHAR(11), 
   INOUT Title VARCHAR(50) )
    RETURNS VARCHAR(30)
    FOR Sample.Employee
    LANGUAGE OBJECTSCRIPT
    {
        NEW SQLCODE,%ROWCOUNT
        &sql(SELECT Title INTO :Title FROM Sample.Employee 
             WHERE SSN = :SSN)
        IF $GET(%sqlcontext)'= "" {
           SET %sqlcontext.%SQLCODE=SQLCODE
           SET %sqlcontext.%ROWCOUNT=%ROWCOUNT }
           QUIT
     }

%sqlcontext オブジェクトを使用します。対応する SQL 変数を使用して、その %SQLCODE プロパティおよび %ROWCOUNT プロパティを設定します。プロシージャの LANGUAGE OBJECTSCRIPT キーワードに続く ObjectScript コードを中括弧で囲むことに注意してください。ObjectScript コードには、&sql でマークされ、角括弧で囲まれた埋め込み SQL コードがあります。