Skip to main content

This is documentation for Caché & Ensemble. See the InterSystems IRIS version of this content.Opens in a new tab

For information on migrating to InterSystems IRISOpens in a new tab, see Why Migrate to InterSystems IRIS?

ストアド・プロシージャの定義と使用

この章では、Caché SQL でストアド・プロシージャを定義および使用する方法について説明します。項目は以下のとおりです。

概要

ほとんどのリレーショナル・データベース・システムと同様に、Caché では、SQL ストアド・プロシージャを作成できます。ストアド・プロシージャ (SP) は、データベースに保存され、SQL コンテキスト内で (CALL 文の使用、または ODBC や JDBC から) 呼び出し可能なルーチンを提供します。

Caché は、ストアド・プロシージャがクラスのメソッドであると定義できるという点で、従来のリレーショナル・データベースより優れています。実際、ストアド・プロシージャは、SQL でも使用できるようにしたクラス・メソッドに過ぎません。ストアド・プロシージャでは、Caché のオブジェクト・ベースのフル装備の機能を利用できます。

  • ストアド・プロシージャは、データベースを問い合わせてデータの単一の結果セットを返すクエリとして定義できます。

  • ストアド・プロシージャは、単一の値を返すユーザ定義関数として機能する関数プロシージャとして定義できます。

  • ストアド・プロシージャは、データベースのデータに変更を加えて 1 つの値または 1 つ以上の結果セットを返すメソッドとして定義できます。

$SYSTEM.SQL.ProcedureExists()Opens in a new tab メソッドを使用して、プロシージャが既に存在するかどうかを確認できます。このメソッドは、プロシージャ・タイプ (“function” または “query”) も返します。

ストアド・プロシージャの定義

Caché SQL のほとんどの機能と同様に、ストアド・プロシージャを定義するには、DDL を使用する場合とクラスを使用する場合の 2 種類の方法があります。これらは以下のセクションで説明しています。

DDL を使用したストアド・プロシージャの定義

Caché SQL がサポートするクエリを作成するためのコマンドは以下のとおりです。

  • CREATE PROCEDURE では、常にストアド・プロシージャとして投影されるクエリを作成できます。クエリは、単一の結果セットを返すことができます。

  • CREATE QUERY では、必要に応じてストアド・プロシージャとして投影できるクエリを作成します。クエリは、単一の結果セットを返すことができます。

Caché SQL がサポートするメソッドまたは関数を作成するためのコマンドは以下のとおりです。

  • CREATE PROCEDURE では、常にストアド・プロシージャとして投影されるメソッドを作成できます。メソッドは、単一の値、または 1 つ以上の結果セットを返すことができます。

  • CREATE METHOD では、必要に応じてストアド・プロシージャとして投影できるメソッドを作成できます。メソッドは、単一の値、または 1 つ以上の結果セットを返すことができます。

  • CREATE FUNCTION では、必要に応じてストアド・プロシージャとして投影できる関数プロシージャを作成できます。関数は、単一の値を返すことができます。

これらのコマンド内に指定する実行可能コードのブロックは、Caché SQL または ObjectScript で記述できます。ObjectScript コード・ブロックには、埋め込み SQL を含めることができます。

SQL からクラス名への変換

ストアド・プロシージャの作成に DDL を使用すると、指定した名前はクラス名に変換されます。そのクラスが存在しない場合は、作成されます。

  • 名前が修飾されておらず、FOR 節の指定がない場合は、システム全体の既定のスキーマ名がパッケージ名として使用され、その名前の後にドットが続き、その後に生成されたクラス名が続きます。このクラス名は、‘func’、‘meth’、‘proc’、または ‘query’ という文字列の後に指定した名前を組み合わせたものになります。指定した名前は、必要に応じて有効なオブジェクト名に変換されます。例えば、未修飾のプロシージャ名 StoreName は、User.procStoreName のようなクラス名になります。このプロシージャ・クラスには、StoreName() メソッドが含まれます。

  • 名前が修飾されていて、FOR 節の指定がない場合は、そのスキーマの名前がパッケージ名に変換され、その名前の後にドットが続き、その後に ‘func’、‘meth’、‘proc’、または ‘query’ という文字列が続き、その後に指定した名前が続きます。指定したパッケージ名は、必要に応じて有効なパッケージ名に変換されます。指定した名前は、必要に応じて有効なオブジェクト名に変換されます。

  • FOR 節が指定されている場合は、その FOR 節で指定した修飾されたクラス名により、関数名、メソッド名、プロシージャ名、またはクエリ名で指定したスキーマ名が上書きされます。

有効なオブジェクト名への SQL 関数名、メソッド名、プロシージャ名、およびクエリ名の変換は、以下に示すルールで制御されます。

  • 句読点文字は削除されます。句読点のみが異なる名前は有効です。Caché は、名前の最後の文字を 0 から始まる整数の接尾辞に置き換えることで、その名前に対応する一意の名前を生成します。例えば、mynamemy_name は、mynamemynam0 を生成します。生成される一意の名前の数が 10 個 (mynam9) を超えるときには、大文字の A から G に置き換えることで名前が作成されます (mynamA)。

  • 最初の文字が句読点で、2 番目の文字が数字の名前は無効です。

  • 句読点文字のみで構成された識別子、2 つのアンダースコア文字で始まる識別子 (__name)、または連続する 2 つのポンド記号を含む識別子 (nn##nn) は、通常、SQL エンティティ名としては無効です。すべてのコンテキストで使用を避ける必要があります。

有効なパッケージ名へのスキーマ名の変換は、以下のルールで制御されます。

  • スキーマ名にアンダースコアが含まれている場合、この文字はサブパッケージを示すドットに変換されます。例えば、修飾名 myprocs.myname により、パッケージ myprocs が作成されます。修飾名 my_procs.myname により、サブパッケージ procs を含むパッケージ my が作成されます。

以下の例では、クラス名とその SQL 呼び出しにおける句読点の違いを示します。ここでは、クラス名に 2 つのドットを含んでいるメソッドを定義します。この例では、SQL から呼び出したときは、1 つ目のドットがアンダースコア文字に置換されます。

Class tmp.test.sql Extends %RegisteredObject 
 {  ClassMethod myfunc(dummy As %String) As %String [ SqlProc ] 
    { /* method code */
      Quit "abc" }
 }  
SELECT tmp_test.sql_myfunc(Name)
FROM Sample.Person

クラスを使用したメソッド・ストアド・プロシージャの定義

クラス・メソッドはストアド・プロシージャとして公開されます。このようなメソッドは、値を計算して、それを返さずにデータベースに保存するストアド・プロシージャのような動作に対して理想的です。ほぼすべてのクラスで、メソッドをストアド・プロシージャとして公開できます。この例外は、データ型クラス ([ClassType = datatype]) などのジェネレータ・クラスです。ジェネレータ・クラスには実行時コンテキストがありません。プロパティなど、他の一部のエンティティの実行時において、データ型コンテキストを使用するためのみに有効です。

メソッド・ストアド・プロシージャを定義するには、クラス・メソッドを定義し、その SqlProc キーワードを設定します。

Class MyApp.Person Extends %Persistent [DdlAllowed]
{

/// This procedure finds total sales for a territory
ClassMethod FindTotal(territory As %String) As %Integer [SqlProc]
{
    // use embedded sql to find total sales
    &sql(SELECT SUM(SalesAmount) INTO :total 
            FROM Sales
            WHERE Territory = :territory
    )

    Quit total
}
}

このクラスがコンパイルされた後、ストアド・プロシージャ MyApp.Person_FindTotal() として FindTotal() メソッドが SQL に投影されます。メソッドの SqlName キーワードを使用すると、SQL がプロシージャに使用している名前を変更できます。

このメソッドは、プロシージャ・コンテキスト・ハンドラを使用して、プロシージャとその呼び出し元 (ODBC サーバなど) 間でプロシージャ・コンテキストの受け渡しを行います。このプロシージャ・コンテキスト・ハンドラは、Caché によって (%qHandle:%SQLProcContext として) %sqlcontext オブジェクトを使用して自動生成されます。

%sqlcontext は、SQLCODE エラー・ステータス、SQL 行カウント、エラー・メッセージなどのプロパティで構成されます。それらのプロパティは、次のように、対応する SQL 変数を使用して設定されます。

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

これらの値に関しては何も行う必要はありませんが、クライアントによって解読されます。%sqlcontext オブジェクトは、実行される前に毎回リセットされます。

メソッドは値を返しません。

1 つのクラスのユーザ定義メソッドの最大数は 2000 です。

例えば、CalcAvgScore() メソッドがあるとします。

ClassMethod CalcAvgScore(firstname As %String,lastname As %String) [sqlproc]
{
  New SQLCODE,%ROWID
  &sql(UPDATE students SET avgscore = 
    (SELECT AVG(sc.score) 
     FROM scores sc, students st
     WHERE sc.student_id=st.student_id 
       AND st.lastname=:lastname
       AND st.firstname=:firstname)
     WHERE students.lastname=:lastname
       AND students.firstname=:firstname)

  IF ($GET(%sqlcontext)'= "") {
    SET %sqlcontext.%SQLCODE = SQLCODE
    SET %sqlcontext.%ROWCOUNT = %ROWCOUNT
  }
  QUIT
}

クラスを使用したクエリ・ストアド・プロシージャの定義

データベースからデータを返す多くのストアド・プロシージャは、標準クエリ・インタフェースで実装されます。この方法は、プロシージャが埋め込み SQL で記述されている限りうまくいきます。以下の例では、WHERE 節に値を提供する埋め込み SQL ホスト変数の使用法に注意してください。

Class MyApp.Person Extends %Persistent [DdlAllowed]
{

    /// This procedure result set is the persons in a specified Home_State, ordered by Name
    Query ListPersons(state As %String = "") As %SQLQuery [ SqlProc ]
    {
        SELECT ID,Name,Home_State
        FROM Sample.Person
        WHERE Home_State = :state
        ORDER BY Name
    }
}

クエリをストアド・プロシージャとして公開するには、スタジオ・インスペクタで、公開するクエリのエントリの SQLProc フィールドの値を True に変更するか、以下の “[ SqlProc ]” 文字列をクエリ定義に追加します。

Query QueryName() As %SQLQuery( ... query definition ... ) 
    [ SqlProc ]

このクラスがコンパイルされた後、ストアド・プロシージャ MyApp.Person_ListPersons として ListPersons クエリが SQL に投影されます。クエリの SqlName キーワードを使用すると、SQL がプロシージャに使用している名前を変更できます。

SQL から MyApp.Person_ListPersons が呼び出されると、クエリの SQL 文によって定義されている結果セットが自動的に返されます。

次の例は、結果セットを使用したストアド・プロシージャです。

Class apc.OpiLLS.SpCollectResults1 [ Abstract ]
{

/// This SP returns a number of rows (pNumRecs) from WebService.LLSResults, and updates a property for each record
Query MyQuery(pNumRecs As %Integer) As %Query(ROWSPEC = "Name:%String,DOB:%Date") [ SqlProc ]
{
}

/// You put initial code here in the Execute method
ClassMethod MyQueryExecute(ByRef qHandle As %Binary, pNumRecs As %Integer) As %Status
{
    SET mysql="SELECT TOP ? Name,DOB FROM Sample.Person"       
    SET rset=##class(%SQL.Statement).%ExecDirect(,mysql,pNumRecs)
            IF rset.%SQLCODE'=0 {QUIT rset.%SQLCODE}
    SET qHandle=rset
    QUIT $$$OK
}

/// This code is called by the SQL framework for each row, until no more rows are returned
ClassMethod MyQueryFetch(ByRef qHandle As %Binary, ByRef Row As %List, 
                         ByRef AtEnd As %Integer = 0) As %Status [ PlaceAfter = NewQuery1Execute ]
{
     SET rset=qHandle
     SET tSC=$$$OK 
      
     FOR {
        ///Get next row, quit if end of result set
        IF 'rset.%Next() {
                SET Row = "", AtEnd = 1
                SET tSC=$$$OK
                QUIT
                }
        SET name=rset.Name
        SET dob=rset.DOB
        SET Row = $LISTBUILD(name,dob)
        QUIT
        }         
        QUIT tSC
}

ClassMethod MyQueryClose(ByRef qHandle As %Binary) As %Status [ PlaceAfter = NewQuery1Execute ]
{
        KILL qHandle   //probably not necesary as killed by the SQL Call framework
        QUIT $$$OK
}

}

単純な SQL 文としてクエリを記述し、それをクエリ・ウィザードで作成することが可能な場合、クエリを実装する基本的なメソッドに関する知識は必要ありません。

内部で、各クエリに対して、クラス・コンパイラはストアド・プロシージャの名前を基にしてメソッドを生成します。以下のものが含まれます。

  • stored-procedure-nameExecute()

  • stored-procedure-nameFetch()

  • stored-procedure-nameFetchRows()

  • stored-procedure-nameGetInfo()

  • stored-procedure-nameClose()

クエリのタイプが %SQLQuery の場合、クラス・コンパイラは、生成されたメソッドに自動的にいくつかの埋め込み SQL を挿入します。Execute() は、SQL のストアド・カーソルを宣言して開きます。Fetch() は、それが空の行 (SET Row="") を返すまで繰り返し呼び出されます。オプションで、Fetch() を AtEnd=1 のブーリアン・フラグを返すようにすることもできます。これは、現在の Fetch が最後の行を取り、次の Fetch で空白の行を返すようにすることを示します。ただし、空白行 (Row="") は、結果セットの終了を判断するためのテスト用として常に使用する必要があります。AtEnd=1 を設定するときには、常に Row="" を設定する必要があります。

FetchRows() は、Fetch() を繰り返し呼び出すことと論理的に同等です。GetInfo() は、ストアド・プロシージャに対するシグニチャの詳細を返すために呼び出されます。Close() は、カーソルをクローズします。

これらすべてのメソッドは、クライアントからストアド・プロシージャが呼び出されるたびに自動的に呼び出されますが、論理的には、サーバで起動している ObjectScript から直接呼び出されることもあります。

オブジェクトを Execute() から Fetch() に渡すために、また Fetch() から次の Fetch() の呼び出しに渡すために、クエリ・ハンドラをそのオブジェクトのオブジェクト参照 (oref) に設定することができます。複数のオブジェクトを渡す場合、qHandle を配列として設定できます。

  SET qHandle(1)=oref1,qHandle(2)=oref2

ユーザが記述したコード (SQL 文ではありません) に基づいて結果セット・ストアド・プロシージャを生成することもできます。

1 つのクラスのユーザ定義クエリの最大数は 200 です。

カスタマイズされたクラス・クエリ

クエリ・モデルに一致しない複雑なクエリやストアド・プロシージャに対しては、多くの場合で、そのメソッドの一部、またはすべてを置き換えることでクエリをカスタマイズする必要があります。このセクションで説明するように、%Library.QueryOpens in a new tab を使用できます。

多くの場合、%SQLQuery (%Library.SQLQueryOpens in a new tab) タイプの代わりに %Query (%Library.QueryOpens in a new tab) タイプを選択する方が、クエリの実装が簡単になります。これによって、同じ 5 つのメソッドが生成されますが、FetchRows() は単純に Fetch() の呼び出しを繰り返します (%SQLQuery には他の動作を引き起こすいくつかの最適化機能があります)。GetInfo() は単純にシグニチャから情報を得るため、コードを変更する必要が生じることはほとんどありません。これによって、他の 3 つのメソッドそれぞれに対し、クラス・メソッドを作成する上での問題が削減されます。クラスがコンパイルされるとき、コンパイラはこれらのメソッドの存在を検出します。上書きすることはありません。

メソッドは特定のシグニチャを必要とします。それらはすべて %Binary タイプの Qhandle (クエリ・ハンドラ) を使用します。これは、クエリの特性と現状を維持する構造に対するポインタです。Execute()Fetch() に対する参照と、Close() に対する値によって渡されます。

ClassMethod SP1Close(qHandle As %Binary) As %Status
{
   // ... 
}

ClassMethod SP1Execute(ByRef qHandle As %Binary,
    p1 As %String) As %Status
{
   // ...
}

ClassMethod SP1Fetch(ByRef qHandle As %Binary, 
    ByRef Row As %List, ByRef AtEnd As %Integer=0) As %Status
{
   // ...
}

Query SP1(p1 As %String) 
   As %Query(CONTAINID=0,ROWSPEC="lastname:%String") [sqlproc ]
{
}

コードは通常、宣言を含み、SQL カーソルを使用します。%SQLQuery タイプのクエリから生成されたカーソルは、自動的に Q14 などの名前を持ちます。クエリに個別の名前が与えられていることを確認してください。

クラス・コンパイラは、カーソルを使用する前に、カーソル宣言を見つける必要があります。したがって、DECLARE 文 (通常は Execute 内) は、Close や Fetch と同じ MAC ルーチン内にあるべきで、Close や Fetch よりも先に来る必要があります。ソースを直接編集するには、カーソル宣言が先に来るように Close 定義と Fetch 定義の両方で PLACEAFTER メソッド・キーワードを使用します。

エラー・メッセージは、通常、桁を 1 桁余分に持っている内部カーソル名を参照します。したがって、カーソル Q140 に対するエラー・メッセージは、おそらく Q14 を参照しています。

ストアド・プロシージャの使用法

ストアド・プロシージャを使用するには、以下のように 2 種類の異なる方法があります。

  • SQL CALL 文を使用してストアド・プロシージャを呼び出すことができます。詳細は、"Caché SQL リファレンス" の CALL 文を参照してください。

  • ストアド関数 (単一の値を返すメソッドベースのストアド・プロシージャ) は、SQL クエリ内で組み込み関数であるかのように使用できます。

ストアド関数

ストアド関数は、単一の値を返すメソッド・ベースのストアド・プロシージャです。例えば、以下のクラスは、指定の値の 2 乗を返すストアド関数 Square を定義します。

Class MyApp.Utils Extends %Persistent [DdlAllowed]
{
ClassMethod Square(val As %Integer) As %Integer [SqlProc]
{
    Quit val * val
}
}

ストアド関数は単に、SqlProc キーワードが指定されたクラス・メソッドです。

Note:

ストアド関数の場合は、ReturnResultsets キーワードを未指定にするか (既定)、このキーワードの前に Not キーワードを指定する必要があります。

SQL クエリ内で、ストアド関数を組み込みの SQL 関数であるかのように使用できます。この場合の関数の名前は、ストアド関数の SQL 名 (この場合は “Square”) を、その関数が定義されているスキーマ (パッケージ) 名 (この場合は “MyApp”) で修飾したものになります。

以下のクエリは、Square 関数を使用します。

SELECT Cost, MyApp.Utils_Square(Cost) As SquareCost FROM Products

1 つのパッケージ (スキーマ) に複数のストアド関数を定義する場合は、それぞれが一意の SQL 名を持つ必要があります。

次の例では、Sample.Wages という名前のテーブルを定義しており、このテーブルでは 2 つのデータ・フィールド (プロパティ) と 2 つのストアド関数 (TimePlus と DTime) が定義されています。

Class Sample.Wages Extends %Persistent [ DdlAllowed ]
{  
  Property Name As %String(MAXLEN = 50) [ Required ];
  Property Salary As %Integer;
  ClassMethod TimePlus(val As %Integer) As %Integer [ SqlProc ]
  {
   QUIT val * 1.5
  }
  ClassMethod DTime(val As %Integer) As %Integer [ SqlProc ]
  {
   QUIT val * 2
  }
} 

次のクエリでは、これらのストアド・プロシージャを使用して、同じ Sample.Wages テーブル内の各従業員の本給、5 割増賃金、および倍額賃金を返します。

SELECT Name,Salary,
       Sample.Wages_TimePlus(Salary) AS Overtime,
       Sample.Wages_DTime(Salary) AS DoubleTime FROM Sample.Wages

次のクエリでは、これらのストアド・プロシージャを使用して、異なる Sample.Employee テーブル内の各従業員の本給、5 割増賃金、および倍額賃金を返します。

SELECT Name,Salary,
       Sample.Wages_TimePlus(Salary) AS Overtime,
       Sample.Wages_DTime(Salary) AS DoubleTime FROM Sample.Employee

特権

プロシージャを実行するには、ユーザはそのプロシージャに対する EXECUTE 特権が必要です。GRANT コマンドまたは %SYSTEM.SQLOpens in a new tab GrantObjPriv()Opens in a new tab メソッドを使用して、指定のプロシージャの EXECUTE 特権を指定のユーザに割り当てます。

指定ユーザが指定プロシージャの EXECUTE 特権を所有しているかどうかを判断するには、$SYSTEM.SQL.CheckPriv()Opens in a new tab メソッドを呼び出します。

ユーザが EXECUTE 特権を持っているすべてのプロシージャをリストするには、管理ポータルに移動します。[システム管理] から [セキュリティ] を選択し、[ユーザ] (システム, セキュリティ管理, ユーザ) または[ロール] (システム, セキュリティ管理, ロール) を選択します。目的のユーザまたはロールの [編集] を選択し、[SQL プロシージャ] タブを選択します。ドロップダウン・リストから目的の [ネームスペース] を選択します。

プロシージャのリスト

INFORMATION.SCHEMA.ROUTINESOpens in a new tab 永続クラスは、現在のネームスペース内のすべてのルーチンとプロシージャに関する情報を表示します。これは多数のプロパティを提供します。

埋め込み SQL で指定される場合、INFORMATION.SCHEMA.ROUTINESOpens in a new tab には #include %occInclude マクロ・プリプロセッサ指示文が必要です。この指示文は、ダイナミック SQL では必要ありません。

以下の例は、現在のネームスペース内にあるスキーマ “MyApp” のすべてのルーチンについて、ルーチン・タイプ (PROCEDURE または FUNCTION)、スキーマ名、およびルーチン名を返します。

SELECT Routine_Type,Routine_Schema,Routine_Name FROM INFORMATION_SCHEMA.ROUTINES WHERE Routine_Schema='MyApp'

管理ポータル SQL インタフェースの [カタログの詳細] タブを使用して、1 つのプロシージャについて、INFORMATION.SCHEMA.ROUTINESOpens in a new tab とほぼ同じ情報を表示できます。プロシージャの [カタログの詳細] には、プロシージャ・タイプ (クエリまたは関数)、クラス名、メソッド名またはクエリ名、説明、および入力パラメータと出力パラメータの数が含まれます。[カタログの詳細] の [ストアド・プロシージャ情報] 画面には、ストアド・プロシージャを実行するためのオプションも用意されています。

FeedbackOpens in a new tab