InterSystems SQL のほとんどの機能と同様に、ストアド・プロシージャを定義するには、DDL を使用する場合とクラスを使用する場合の 2 種類の方法があります。これらは以下のセクションで説明しています。
クラスを使用したメソッド・ストアド・プロシージャの定義
クラス・メソッドはストアド・プロシージャとして公開されます。このようなメソッドは、値を計算して、それを返さずにデータベースに保存するストアド・プロシージャのような動作に対して理想的です。ほぼすべてのクラスで、メソッドをストアド・プロシージャとして公開できます。この例外は、データ型クラス ([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 サーバなど) 間でプロシージャ・コンテキストの受け渡しを行います。このプロシージャ・コンテキスト・ハンドラは、%sqlcontext オブジェクトを使用して、InterSystems IRIS によって (%qHandle:%SQLProcContext として) 自動的に生成されます。
%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 を参照しています。