CREATE PROCEDURE (SQL)

procname

ストアド・プロシージャとして生成するメソッドまたはクエリの名前です。パラメータを指定しない場合でも、procname の後には括弧を付加する必要があります。プロシージャ名は以下の形式のいずれかで指定することができます。

• 未修飾 ： 既定のスキーマ名が使用されます。例えば、MedianAgeProc() です。

• 修飾 ： スキーマ名を指定します。例えば、Patient.MedianAgeProc() です。

• 複数レベル ： 対応するクラス・パッケージ・メンバに一致させるために 1 つ以上のスキーマ・レベルで修飾します。この場合、procname に含めることができるピリオド文字は 1 つだけです。対応するクラス・メソッド名の他のピリオドは下線に置き換えられます。ピリオドは、最下位レベルのクラス・パッケージ・メンバの前に指定します。例えば、%SYSTEM.SQL_GetROWID() や %SYS_PTools.StatsSQL_Export() のように指定します。

未修飾 ： procname では 既定のスキーマ名が使用されます。$SYSTEM.SQL.Schema.Default()Opens in a new tab メソッドを使用して、現在のシステム全体の既定のスキーマ名を確認できます。初期のシステム全体の既定のスキーマ名は、クラス・パッケージ名 User に対応する SQLUser です。

FOR 特性 (後述) は、procname で指定されたクラス名をオーバーライドすることに注意してください。この名前のプロシージャが既に存在する場合、この操作は失敗して SQLCODE -361 エラーが発行されます。

InterSystems SQL は SQL procname を使用して、対応するクラス名を生成します。この名前は、スキーマ名に対応するパッケージ名と、その後に順に続くドット、“proc”、および指定のプロシージャ名で構成されます。例えば、未修飾のプロシージャ名 RandomLetter() で既定のスキーマ SQLUser が使用される場合、生成されるクラス名は User.procRandomLetter() となります。詳細は、"InterSystems SQL の使用法" の “ストアド・プロシージャの定義と使用” の章の "SQL からクラス名への変換" を参照してください。

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

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

オプションのキーワード OR REPLACE を指定して、エラーが発生することなく、既存のプロシージャを変更または置換します。CREATE OR REPLACE PROCEDURE には、DROP PROCEDURE を呼び出して古いバージョンのプロシージャを削除し、続いて CREATE PROCEDURE を呼び出す操作と同じ効果があります。

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 のように InterSystems ObjectScript のクラス・メソッド・コードの先頭に追加されます。詳細は、"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 (すべての InterSystems SQL 実行インタフェースの既定値) の場合のみ適用されます。

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

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

LANGUAGE

プロシージャ・コード言語を指定するキーワード節です。利用可能なオプションは以下のとおりです。

• LANGUAGE OBJECTSCRIPT (ObjectScript の場合) または LANGUAGE SQL。プロシージャ・コードは code_body で指定します。

• LANGUAGE JAVA、LANGUAGE PYTHON、または LANGUAGE DOTNET (これらのいずれかの言語で外部ストアド・プロシージャを呼び出す SQL プロシージャの場合)。外部ストアド・プロシージャの構文は、以下のようになります。

  LANGUAGE langname EXTERNAL NAME external-routine-name

  langname は JAVA、PYTHON、または DOTNET で、external-routine-name は指定した言語の外部ルーチン名を含む引用符付き文字列です。SQL プロシージャは既存のルーチンを呼び出します。これらの言語のコードを CREATE PROCEDURE 文内に記述することはできません。これらの言語のストアド・プロシージャ・ライブラリは IRIS の外部に保存されているため、IRIS 内でパッケージ、インポート、コンパイルする必要はありません。以下に、既存の JAVA 外部ストアド・プロシージャを呼び出す CREATE PROCEDURE の例を示します。

  CREATE PROCEDURE updatePrice (item_name VARCHAR, new_price INTEGER)
  LANGUAGE JAVA
  EXTERNAL NAME 'Orders.updatePrice'
  

  

LANGUAGE 節を省略する場合は、SQL が既定です。

code_body

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

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

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

   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"
  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 コードがあります。