CREATE QUERY (SQL)
構文
CREATE [OR REPLACE] QUERY queryname(parameter_list)
[ characteristics ]
[ LANGUAGE SQL ]
BEGIN code_body ;
END
CREATE QUERY queryname(parameter_list) [characteristics]
LANGUAGE OBJECTSCRIPT
{ code_body }
説明
CREATE QUERY 文は、クラスにクエリを生成します。既定では、MySelect と命名したクエリが、User.queryMySelect または SQLUser.queryMySelect として保存されます。
CREATE QUERY で作成されるクエリは、ストアド・プロシージャとして公開される場合とそうでない場合があります。ストアド・プロシージャとして公開するクエリを生成するには、characteristics の 1 つとして PROCEDURE キーワードを指定する必要があります。CREATE PROCEDURE 文を使用して、ストアド・プロシージャとして公開されるクエリを生成することも可能です。
クエリの生成には、GRANT コマンドで指定された %CREATE_QUERY 管理者特権が必要です。定義された所有者を持つ既存のクラスのクエリを作成しようとする場合、クラスの所有者としてログインする必要があります。そうでない場合、操作は SQLCODE -99 エラーで失敗します。
クラス定義が導入済みのクラスの場合、クラスでクエリを作成することはできません。この操作は SQLCODE -400 エラーで失敗し、%msg が “導入済みのクラス classname を変更する DDL を実行できません” に設定されます。
引数
queryname
ストアド・プロシージャ・クラスに作成するクエリの名前。パラメータを指定しない場合でも、queryname は有効な識別子である必要があり、後には括弧を付加する必要があります。このプロシージャ名は、未修飾 (StoreName) にして既定のスキーマ名を使用しても、スキーマ名を指定して修飾 (Patient.StoreName) してもかまいません。$SYSTEM.SQL.Schema.Default()Opens in a new tab メソッドを使用して、現在のシステム全体の既定のスキーマ名を確認できます。初期のシステム全体の既定のスキーマ名は、クラス・パッケージ名 User に対応する SQLUser です。
FOR 特性 (後述) は、queryname で指定されたクラス名をオーバーライドすることに注意してください。この名前のメソッドが既に存在する場合、この操作は失敗して SQLCODE -361 エラーが発行されます。
生成されるクラスの名前は、スキーマ名に対応するパッケージ名の後にドット、“query”、指定の queryname が順に続いたものになります。例えば、未修飾のクエリ名 RandomLetter で初期の既定のスキーマ SQLUser が使用される場合、生成されるクラス名は User.queryRandomLetter となります。詳細は "SQL からクラス名への変換" を参照してください。
InterSystems SQL では、大文字/小文字が異なるだけの queryname を指定することは許可されていません。既存のクエリ名と大文字/小文字が異なるだけの queryname を指定した場合は、SQLCODE -400 エラーが生成されます。
指定した queryname が現在のネームスペース内に既に存在する場合は、SQLCODE -361 エラーが生成されます。
オプションのキーワード OR REPLACE を指定して、エラーが発生することなく、既存のクエリを変更または置換します。CREATE OR REPLACE QUERY には、DROP QUERY を呼び出して古いバージョンのクエリを削除し、続いて CREATE QUERY を呼び出す操作と同じ効果があります。
parameter-list
値をクエリに渡すために使用するパラメータのパラメータ宣言リストです。パラメータのリストは括弧で囲み、リストのパラメータ宣言はコンマで区切ります。パラメータを指定しない場合も括弧は必須です。
リスト内の各パラメータ宣言は、(先頭から順番に) 以下の要素で構成します。
-
パラメータ・モードが IN (入力値)、OUT (出力値)、または INOUT (変更値) のいずれであるかを指定するオプションのキーワード。省略した場合、既定のパラメータ・モードは IN です。
-
パラメータ名。パラメータ名では、大文字と小文字が区別されます。
-
パラメータのデータ型。
-
オプション : パラメータの既定値。DEFAULT キーワードの後ろに既定値を付けて指定できます。DEFAULT キーワードはオプションです。既定値が指定されていない場合、既定値は NULL であると見なされます。
以下の例では、入力パラメータが 2 つあるストアド・プロシージャとして公開されるクエリを作成します。これらの入力パラメータのどちらにも既定値があります。topnum 入力パラメータではオプションの DEFAULT キーワードを指定し、minage 入力パラメータではこのキーワードを省略します。
CREATE QUERY AgeQuery(IN topnum INT DEFAULT 10,IN minage INT 20)
PROCEDURE
BEGIN
SELECT TOP :topnum Name,Age FROM Sample.Person
WHERE Age > :minage ;
END
CALL AgeQuery(6,65)、CALL AgeQuery(6)、CALL AgeQuery(,65)、CALL AgeQuery() はすべて、このクエリで有効な CALL 文です。
characteristics
クエリの特性を指定する 1 つ以上のキーワードを示す引数 (オプション)。特性は任意の順序で指定できます。有効な characteristics キーワードは、以下のとおりです。
characteristics キーワード | 概要 |
---|---|
CONTAINID integer | フィールドが存在する場合は、どのフィールドが ID を返すかを指定します。CONTAINID を ID を返す列の番号に設定するか、または ID を返す列が存在しない場合は 0 を設定します。InterSystems IRIS では、指定されたフィールドが実際に ID を含んでいるかどうかの検証が行われないため、ユーザの入力の誤りによってデータの不一致が起こる可能性があります。 |
FOR className | メソッドを生成するクラス名を指定します。そのクラスが存在しない場合は新規作成します。メソッド名を認証することによりクラス名を指定することもできます。FOR 節で指定されたクラス名の方が、メソッド名の修飾により指定されたクラス名よりも優先されます。 |
FINAL | サブクラスがメソッドをオーバーライドできないように指定します。既定では、メソッドは Final ではありません。FINAL キーワードはサブクラスによって継承されます。 |
PROCEDURE | クエリが SQL ストアド・プロシージャであることを指定します。ストアド・プロシージャはサブクラスに継承されます(このキーワードは、PROC と略すことができます)。 |
RESULTS (result_set) |
データ・フィールドをクエリで返された順序で指定します。RESULTS 節を指定する場合、クエリによって返されるすべてのフィールドを括弧で囲まれたコンマ区切りリストとしてリストする必要があります。クエリによって返されるよりも少ないか多いフィールドを指定した場合は、カーディナリティの不一致エラー SQLCODE -76 が生成されます。 各フィールドについて、列名 (列ヘッダとして使用されます) とデータ型を指定します。 LANGUAGE SQL の場合は、RESULTS 節を省略できます。RESULTS 節を省略した場合、ROWSPEC がクラス・コンパイル時に自動生成されます。 |
SELECTMODE mode | クエリをコンパイルするために使用するモードを指定します。可能な値は、LOGICAL、ODBC、RUNTIME、DISPLAY です。既定は RUNTIME です。 |
クエリに対して有効でないメソッド・キーワード (PRIVATE や RETURNS など) を指定する場合、システムは SQLCODE -47 エラーを発行します。characteristics を重複して指定すると、SQLCODE -44 エラーが発生します。
SELECTMODE 節は、データを返すモードを指定します。mode 値が LOGICAL の場合は、論理 (内部保存) 値が返されます。例えば、日付は $HOROLOG 形式で返されます。mode 値が ODBC の場合、論理から ODBC への変換が適用され、ODBC 形式値が返されます。mode 値が DISPLAY の場合、論理から表示への変換が適用され、表示形式値が返されます。mode 値が RUNTIME の場合、%SQL.StatementOpens in a new tab クラスの %SelectModeOpens in a new tab プロパティを設定することによって、実行時にモードを LOGICAL、ODBC、DISPLAY のいずれかに設定できます。これについては、"ダイナミック SQL の使用法" で説明しています。RUNTIME モードの既定は LOGICAL です。SelectMode オプションの詳細は、"データ表示オプション" を参照してください。SELECTMODE に指定した値は、#SQLCompile SELECT=mode のように InterSystems ObjectScript クラス・メソッド・コードの最初に追加されます。詳細は、"#sqlcompile select" を参照してください。
RESULTS 節は、クエリの結果を指定します。RESULTS 節の SQL データ型パラメータが、クエリの ROWSPEC の対応する InterSystems IRIS データ型パラメータに変換されます。例えば、RESULTS 節の RESULTS ( Code VARCHAR(15) ) では、ROWSPEC = "Code:%Library.String(MAXLEN=15)" という ROWSPEC 仕様が生成されます。
LANGUAGE
code_body に使用している言語を指定するキーワード節 (オプション)。使用可能な節は、LANGUAGE OBJECTSCRIPT か、LANGUAGE SQL です。LANGUAGE 節を省略する場合は、SQL が既定です。
LANGUAGE が SQL の場合、%Library.SQLQueryOpens in a new tab 型のクラス・クエリが生成されます。LANGUAGE が OBJECTSCRIPT の場合、%Library.QueryOpens in a new tab 型のクラス・クエリが生成されます。
code_body
生成されるクエリのプログラム・コード。このコードは SQL または ObjectScript で指定します。使用する言語は LANGUAGE 節と一致させる必要があります。ただし、ObjectScript のコードには埋め込み SQL を記述できます。
指定したコードが SQL の場合、1 つの SELECT 文で構成する必要があります。SQL クエリのプログラム・コードは BEGIN キーワードで開始し、その後に実際のプログラム・コード (1 つの SELECT 文) を続ける必要があります。プログラム・コードの末尾にはセミコロン (;) を付け、END キーワードで終了します。
指定したコードが OBJECTSCRIPT の場合、InterSystems IRIS が提供する %Library.QueryOpens in a new tab クラスの Execute()Opens in a new tab クラス・メソッドと Fetch()Opens in a new tab クラス・メソッドの呼び出しを含める必要があります。また、Close()Opens in a new tab、FetchRows()Opens in a new tab、GetInfo()Opens in a new tab の各メソッド呼び出しを含めることも可能です。ObjectScript コードは中括弧で囲みます。Execute() または Fetch() が見つからない場合は、コンパイル時に SQLCODE -46 エラーが生成されます。
ObjectScript コード・ブロックがデータをローカル変数 (例えば、Row) に取得する場合は、コード・ブロックを SET Row="" 行で終了して、データの終了 (end-of-data) 条件を示す必要があります。
クエリをストアド・プロシージャとして公開すると (characteristics に PROCEDURE キーワードを指定)、プロシージャ・コンテキスト・ハンドラの使用によって、プロシージャとその呼び出し元間でプロシージャ・コンテキストの相互受け渡しが行われます。
ストアド・プロシージャが呼び出されると、クラス %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 として設定することによって、エラー・コンテキストを設定することができます。
InterSystems IRIS は、クエリの実際のコードを生成するために提供されたコードを使用します。
例
以下の例は、DocTestPersonState という名前のクエリを作成します。このクエリでは、パラメータは宣言されず、SELECTMODE characteristic が設定されます。また、LANGUAGE に既定 (SQL) が使用されます。
CREATE QUERY DocTestPersonState() SELECTMODE RUNTIME
BEGIN
SELECT Name,Home_State FROM Sample.Person ;
END
管理ポータルで、[クラス] オプションを選択し、SAMPLES ネームスペースを選択します。上の例で作成したクエリの User.queryDocTestPersonState.cls を見つけます。上のプログラム例を再実行する前に、ここでこのクエリを削除できます。もちろん、DROP QUERY を使用してクエリを削除することも可能です。
以下の埋め込み SQL の例は、SQLCODE とその説明のリストを取得する DocTestSQLCODEList という名前のメソッド・ベースのクエリを作成します。このクエリでは、RESULTS 結果セット特性を設定し、LANGUAGE を ObjectScript に設定して、Execute()、Fetch()、Close() の各メソッドを呼び出します。
&sql(CREATE QUERY DocTestSQLCODEList()
RESULTS (SQLCODE SMALLINT,Description VARCHAR(100))
PROCEDURE
LANGUAGE OBJECTSCRIPT
Execute(INOUT QHandle BINARY(255))
{
SET QHandle=1,%i(QHandle)=""
QUIT ##lit($$$OK)
}
Fetch(INOUT QHandle BINARY(255), INOUT Row %List, INOUT AtEnd INT)
{
SET AtEnd=0,Row=""
SET %i(QHandle)=$o(^%qCacheSQL("SQLCODE",%i(QHandle)))
IF %i(QHandle)="" {SET AtEnd=1 QUIT ##lit($$$OK) }
SET Row=$lb(%i(QHandle),^%qCacheSQL("SQLCODE",%i(QHandle),1,1))
QUIT ##lit($$$OK)
}
Close(INOUT QHandle BINARY(255))
{
KILL %i(QHandle)
QUIT ##lit($$$OK)
}
)
IF SQLCODE=0 { WRITE !,"Created a query" }
ELSEIF SQLCODE=-361 { WRITE !,"Query exists: ",%msg }
ELSE { WRITE !,"CREATE QUERY error: ",SQLCODE }
管理ポータルで、[クラス] オプションを選択し、SAMPLES ネームスペースを選択します。上の例で作成したクエリの User.queryDocTestSQLCODEList.cls を見つけます。上のプログラム例を再実行する前に、ここでこのクエリを削除できます。もちろん、DROP QUERY を使用してクエリを削除することも可能です。
以下のダイナミック SQL の例では、DocTest という名前のクエリを作成して、%SQL.StatementOpens in a new tab クラスの %PrepareClassQuery()Opens in a new tab メソッドを使用してこのクエリを実行しています。
/* Creating the Query */
set myquery=4
set myquery(1)="CREATE QUERY DocTest() SELECTMODE RUNTIME "
set myquery(2)="BEGIN "
set myquery(3)="SELECT TOP 5 Name,Home_State FROM Sample.Person ; "
set myquery(4)="END"
set tStatement = ##class(%SQL.Statement).%New()
set qStatus = tStatement.%Prepare(.myquery)
if $$$ISERR(qStatus) {write "%Prepare failed:" do $SYSTEM.Status.DisplayError(qStatus) quit}
set rset = tStatement.%Execute()
if (rset.%SQLCODE '= 0) {write "%Unable to call query", !, "SQLCODE ", rset.%SQLCODE, ": ", rset.%Message quit}
/* Calling the Query */
write !,"Calling a class query",!
set cqStatus = tStatement.%PrepareClassQuery("User.queryDocTest","DocTest")
if $$$ISERR(cqStatus) {write "%PrepareClassQuery failed:" do $SYSTEM.Status.DisplayError(cqStatus) quit}
set rset = tStatement.%Execute()
if (rset.%SQLCODE '= 0) {write "Unable to call class query", !, "SQLCODE ", rset.%SQLCODE, ": ", rset.%Message quit}
write "Query data",!,!
while rset.%Next()
{
do rset.%Print()
}
if (rset.%SQLCODE < 0) {write "%Next failed:", !, "SQLCODE ", rset.%SQLCODE, ": ", rset.%Message quit}
write !,"End of data"
/* Deleting the Query */
&sql(DROP QUERY DocTest)
if SQLCODE = 0 {write !,"Deleted the query"}
詳細は、"ダイナミック SQL の使用" を参照してください。
セキュリティおよび特権
CREATE QUERY コマンドは、ユーザに %Development:USE 権限が必要な特権操作です。このような権限は管理ポータルを介して付与できます。これらの特権なしで CREATE QUERY コマンドを実行すると、SQLCODE -99 エラーが発生し、コマンドは失敗します。
適切な特権を持たないユーザでも、以下の 2 つの状況のいずれかではこのコマンドを実行できます。
-
埋め込み SQL を介してコマンドを実行する場合。この場合は特権が確認されません。
-
特権を確認しないことをユーザが明示的に指定する場合。例えば、checkPriv 引数を 0 に設定して %Prepare() を呼び出すか、%SQL.StatementOpens in a new tab に対して %ExecDirectNoPriv() を呼び出します。