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?

CREATE QUERY

クエリを作成します。

Synopsis

CREATE QUERY queryname(parameter_list) [characteristics] 
   [ LANGUAGE SQL ]
   BEGIN
code_body ;
   END

CREATE QUERY queryname(parameter_list) [characteristics] 
    LANGUAGE OBJECTSCRIPT
   { code_body }

引数

queryname 作成するクエリの名前。識別子です。このクエリ名には、修飾なし (StoreName) と修飾付き (Patient.StoreName) のどちらでも使用できます。パラメータを指定しない場合でも、queryname の後には括弧を付加する必要があります。詳細は、"Caché SQL の使用法" の “識別子” の章を参照してください。
parameter_list オプション — クエリに渡すパラメータのリスト。パラメータのリストは括弧で囲み、パラメータはコンマで区切ります。パラメータを指定しない場合でも、括弧は必須です。
characteristics オプション — クエリの特性を指定する 1 つ以上のキーワード。使用可能なキーワードは、RESULTS、CONTAINID、FOR、FINAL、ROCEDURE、SELECTMODE です。複数の特性は空白 (スペースまたは改行) で区切ります。特性は任意の順序で指定できます。

LANGUAGE OBJECTSCRIPT

LANGUAGE SQL

オプションcode_body に使用するプログラミング言語を指定するキーワード節。LANGUAGE OBJECTSCRIPT (ObjectScript の場合) または LANGUAGE SQL を指定します。LANGUAGE 節が省略される場合は、SQL が既定です。
code_body

クエリのプログラム・コード

SQL プログラム・コードの開始には BEGIN キーワードを使用し、終了には END キーワードを使用します。クエリの code_body は、1 つの完結した SQL 文 (1 つの SELECT 文) のみで構成します。SELECT 文は、セミコロン (;) で終わります。

ObjectScript プログラム・コードは中括弧で囲みます。ObjectScript のコード行は、インデントする必要があります。

概要

CREATE QUERY 文は、クラスにクエリを生成します。既定では、MySelect と命名したクエリが、User.queryMySelect または SQLUser.queryMySelect として保存されます。

CREATE QUERY で作成されるクエリは、ストアド・プロシージャとして公開される場合とそうでない場合があります。ストアド・プロシージャとして公開するクエリを生成するには、characteristics の 1 つとして PROCEDURE キーワードを指定する必要があります。CREATE PROCEDURE 文を使用して、ストアド・プロシージャとして公開されるクエリを生成することも可能です。

クエリの生成には、GRANT コマンドで指定された %CREATE_QUERY 管理者特権が必要です。定義された所有者を持つ既存のクラスのクエリを作成しようとする場合、クラスの所有者としてログインする必要があります。そうでない場合、操作は SQLCODE -99 エラーで失敗します。

引数

queryname

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

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

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

指定した queryname が現在のネームスペース内に既に存在する場合は、SQLCODE -361 エラーが生成されます。

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

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

characteristics キーワード 概要
CONTAINID integer フィールドが存在する場合は、どのフィールドが ID を返すかを指定します。CONTAINID を ID を返す列の番号に設定するか、または ID を返す列が存在しない場合は 0 を設定します。Caché では、指定されたフィールドが実際に 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 のいずれかに設定できます。これについては、"Caché SQL の使用法" の “ダイナミック SQL の使用法” の章で説明しています。RUNTIME モードの既定は LOGICAL です。SelectMode オプションの詳細は、"Caché SQL の使用法" の “Caché SQL の基礎” の章にある “データ表示オプション” を参照してください。SELECTMODE に指定した値は、#SQLCompile SELECT=mode のように ObjectScript クラス・メソッド・コードの最初に追加されます。詳細は、"Caché ObjectScript の使用法" の “ObjectScript マクロとマクロ・プリプロセッサ” の章にある "#SQLCompile Select" を参照してください。

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 が既定です。

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 の場合、Caché が提供する %Library.QueryOpens in a new tab クラスの Execute()Opens in a new tab クラス・メソッドと Fetch()Opens in a new tab クラス・メソッドの呼び出しを含める必要があります。また、Close()Opens in a new tabFetchRows()Opens in a new tabGetInfo()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 として設定することによって、エラー・コンテキストを設定することができます。

Caché は、クエリの実際のコードを生成するために提供されたコードを使用します。

以下の埋め込み SQL の例は、DocTestPersonState という名前のクエリを作成します。このクエリでは、パラメータは宣言されず、SELECTMODE characteristic が設定されます。また、LANGUAGE に既定 (SQL) が使用されます。

  &sql(CREATE QUERY DocTestPersonState() SELECTMODE RUNTIME
       BEGIN
       SELECT Name,Home_State FROM Sample.Person ;
       END)
  IF SQLCODE=0 { WRITE !,"Created a query" }
  ELSEIF SQLCODE=-361 { WRITE !,"Query exists: ",%msg }
  ELSE { WRITE !,"CREATE QUERY error: ",SQLCODE }

管理ポータルで、[クラス] オプションを選択し、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 qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  IF SQLCODE=0 { WRITE !,"Created a query",! }
  ELSEIF SQLCODE=-361 { WRITE !,"Query exists: ",%msg }
  ELSE { WRITE !,"CREATE QUERY error: ",SQLCODE }
  /* Calling the Query */
  WRITE !,"Calling a class query",!
  SET cqStatus = tStatement.%PrepareClassQuery("User.queryDocTest","DocTest")
    IF cqStatus'=1 {WRITE "%PrepareClassQuery failed:" DO $System.Status.DisplayError(cqStatus)}
  SET rset = tStatement.%Execute()
  WRITE "Query data",!,!
  WHILE rset.%Next() {
     DO rset.%Print() } 
  WRITE !,"End of data"
  /* Deleting the Query */
  &sql(DROP QUERY DocTest)
  IF SQLCODE=0 { WRITE !,"Deleted the query" }

詳細は、"Caché SQL の使用法" の "ダイナミック SQL" の章を参照してください。

関連項目

FeedbackOpens in a new tab