埋め込み SQL

Caché SQL は、Caché ObjectScript コードに SQL 文を埋め込む機能をサポートします。これらの埋め込み SQL 文は、コンパイル時に最適化された実行可能なコードに変換されます。

Note:

埋め込み SQL は Basic ではサポートされていません。Basic コードで SQL を使用するには、ダイナミック SQL を使用するか、Caché ObjectScript メソッドで埋め込み SQL を使用し、そのメソッドを Basic から呼び出してください。

埋め込み SQL は、(Caché ObjectScript を定義するために提供された) メソッド内で、あるいは Caché ObjectScript の .MAC ルーチン内で使用できます。.MAC ルーチン (あるいは、Caché ObjectScript を使用するメソッド) は、Caché マクロ・プリプロセッサによって処理され、.INT (中間) コードに変換された後、実行可能なコードにコンパイルされます。マクロ・プリプロセッサは、すべての埋め込み SQL 文を、実際に SQL を実行するコードに置換します。

ここでは、埋め込み SQL の構文、および使用法について説明します。

埋め込み SQL は &sql() 指示文により、他のコードから区別されています。以下はその例です。

Method CountStudents() As %Integer
{
	&sql(SELECT COUNT(*) INTO :count 
		FROM MyApp.Student)

	Quit count
}

&sql 指示文は大文字、小文字を区別しないため、&sql、&SQL、&Sql などすべて使用できます。

&sql 指示文は、有効な SQL 文を含む必要があります。SQL 文は、自由にフォーマットして利用することができますが、SQL は、スペースと新規の行を無視します。Caché スタジオは、&sql 指示文を認識し、SQL 認識色づけ機能を使用して SQL 文を色づけします。

マクロ・プリプロセッサが &sql 指示文を検出すると、SQL クエリ・プロセッサに引用符付きの SQL 文を渡します。クエリ・プロセッサは、クエリの実行に必要なコードを (Caché ObjectScript の INT 形式で) 返します。その後、マクロ・プリプロセッサは &sql 指示文をこのコード (あるいは、コードを含むラベルへの呼び出し) に置換します。生成されたコードを見るには、Caché スタジオで、クラス、またはルーチンに生成された INT コードをご覧ください ([表示] メニューの [他の表示] より)。

&sql 指示文に無効な SQL 文が含まれる場合 (構文エラーや、コンパイル時に存在しないテーブルや列を参照している場合など)、マクロ・プリプロセッサはコンパイル・エラーを生成します。

埋め込み SQL の中のテーブル名には、スキーマ・ネームが含まれることがあります。含まれない場合は、SQL 文を含むクラス、またはルーチンで Import 文を使用して、テーブル名が決定されます (正しいスキーマ・ネームを見つけます)。

詳細は、Caché オブジェクトの使用法のパッケージの章を参照してください。

埋め込み SQL クエリには、リテラル値 (文字列、数字、日付) が含まれる場合があります。文字列は、引用符 (’) によって囲む必要があります (二重引用符は SQL の区切り識別子を表します)。

 &sql(SELECT 'Dr.' || Name INTO :name 
		FROM MyApp.Doctor
		WHERE State = 'NY')

数値は (引用符で囲まずに) 直接使用できます。

 &sql(SELECT Name INTO :name 
		FROM MyApp.Person
		WHERE Age > 50)

埋め込み SQL 文は、リテラル値を使用できる場所、または INTO 節の中で、ホスト変数を含むことができます。ホスト変数は、ローカル変数の名前で、“:” で始まります。これは、SQL 文が使用する (返す) 値を提供します (受け取ります)。

例えば、以下の文は Person の名前を見つけ、ローカル変数の name に返します。

 &sql(SELECT Name INTO :name 
		FROM MyApp.Person
		WHERE %ID = 1)

ホスト変数は、WHERE 節の中でも使用できます。

 Set minval = 10000
 Set maxval = 50000		 &sql(SELECT Name,Salary INTO :name, :salary
		FROM MyApp.Employee
		WHERE Salary > :minval AND Salary < :maxval)

配列をホスト変数として使用できます。

 &sql(SELECT Name, Title INTO :val(1), :val(2)
		FROM MyApp.Employee
		WHERE %ID = :emp("ID") )

バージョン 5.0 からは、ホスト変数としてオブジェクト･プロパティを使用することもできるようになりました。

 &sql(SELECT Name, Title INTO :obj.Name, :obj.Title
		FROM MyApp.Employee
		WHERE %ID = :id )

この場合、obj は、変更可能なプロパティ Name および Title を持つオブジェクトへの、有効な参照である必要があります。

埋め込み SQL では、データ値は “論理モード”、つまり SQL クエリ・プロセッサによって使用されるネイティブ形式の値です。これは、LogicalToODBC または LogicalToDisplay 変換を定義していない文字列、整数やその他のデータ型に対しては無効です。データ形式は、特に日付データ型で重要です。Caché の %Date および %Time データ型は、論理形式として Caché の内部日付表示 ($Horolog 形式) を使用します。%TimeStamp データタイプは、論理、表示、ODBC 形式に対して、ODBC 日付形式 (YYYY-MM-DD HH:MM:SS) を使用します。

例えば、以下のクラス定義について考えてみます。

Class MyApp.Patient Extends %Persistent [ClassType = persistent]
{
/// Patient name
Property Name As %String(MAXLEN = 50);

/// Date of birth
Property DOB As %String;

/// Date and time of last visit
Property LastVisit As %TimeStamp;
}

このテーブルに対する単純な SQL クエリは、論理モードで値を返します。例えば、

 &sql(SELECT Name, DOB, LastVisit
		INTO :name, :dob, :visit
 		FROM Patient
		WHERE %ID = :id)

このクエリは、ホスト変数の name、dob (生年月日)、visit の 3 つのプロパティに対する論理値を返します。

ホスト変数	値
name	"Weiss,Blanche"
dob	44051
visit	"2001-03-15 11:11:00"

dob は、$Horolog 形式で表されています。$ZDateTime 関数により、これを表示形式に変換することができます。

 Set dob = 44051
 Write $ZDT(dob,3),!

WHERE 節でも、同様です。例えば、任意の誕生日の Patient (患者) すべてを検索するには、WHERE 節の論理値を使用します。

 &sql(SELECT Name INTO :name
		FROM Patient
		WHERE DOB = 43023)

または、ホスト変数を使用します。

 Set dob = $ZDTH("01/02/1999",1)

 &sql(SELECT Name INTO :name
		FROM Patient
		WHERE DOB = :dob)

この場合、$ZDTH 関数を使用して、表示形式データを論理 $Horolog 対応に変換します。

単純な SQL 文 (1 つの埋め込み SQL 文) を使用して、以下のようなさまざまな演算を実行できます。

単純な SQL 文は、非カーソル・ベース SQL 文とも呼ばれます。

例えば、以下の文は、ID 番号が 43 の Patient の名前のみを検索します。

 &sql(SELECT Name INTO :name
	FROM Patient
	WHERE %ID = 43)

複数の行を返すことのできる、クエリの単純文を使用する場合、最初の行のみが返されます。

 &sql(SELECT Name INTO :name
	FROM Patient
	WHERE Age = 43)

クエリによっては、実際にどの行が最初に返されるかの保証はありません。

複数の行を返すクエリを実行するための埋め込み SQL を使用したい場合は、SQL カーソルを使用する必要があります。SQL カーソルを使用するには、まず DECLARE で名前を設定します。この名前は、カーソルの OPEN、FETCH (データの取り出し)、CLOSE の際に使用します。

カーソル名は、クラスまたはルーチンの中で一意である必要があります。また、DECLARE 文は、そのカーソルを使用する文より前のルーチンにある必要があります。

以下の例では、カーソルを使用してクエリを実行し、主デバイスへ結果を表示します。

 &sql(DECLARE C1 CURSOR FOR
	SELECT %ID,Name
	INTO :id, :name
	FROM Sample.Person
	ORDER BY Name
 )

 &sql(OPEN C1)
 &sql(FETCH C1)

 While (SQLCODE = 0) {
 	Write id, ":  ", name,!			&sql(FETCH C1)
 }
	 &sql(CLOSE C1)

この例は、以下を実行します。

DECLARE 文は、名前と、カーソルを定義する SQL SELECT 文の両方を指定します。例えば、以下の文は MyCursor と呼ばれるカーソルを宣言します。

 &sql(DECLARE MyCursor CURSOR FOR
	SELECT Name, DOB
	FROM Sample.Person
	WHERE Home_State = :state
	ORDER BY Name
 )

DECLARE 文は、オプションの INTO 節を含む場合もあります。INTO 節は、カーソルが走査されたときにデータを受け取るローカル・ホスト変数の名前を指定します。例えば、上記の例に INTO 節を追加すると、以下になります。

 &sql(DECLARE MyCursor CURSOR FOR
	SELECT Name, DOB
	INTO :name, :dob
	FROM Sample.Person
	WHERE Home_State = :state
	ORDER BY Name
 )

INTO 節のホスト変数の数は、カーソルの SELECT リストの中の列数と正確に一致している必要があります。一致していない場合、文がコンパイルされたときに “カーディナリティ・ミスマッチ・エラー” が生成されます。

DECLARE 文に INTO 節が含まれない場合、INTO 節は FETCH 文の中に表す必要があります。

OPEN 文は、カーソルより後の部分を実行するために、カーソルを作成します。

 &sql(OPEN MyCursor)

OPEN 呼び出しに成功すると、SQLCODE 変数が 0 に設定されます。

最初に OPEN 呼び出しを実行せずに、カーソルからデータを FETCH することはできません。

カーソルに使用された実際のクエリによっては、OPEN 文はほとんど何も実行しない場合や、クエリのための初期化作業を実行する場合もあります。

FETCH 文は、(カーソル・クエリで定義されているように) カーソルの次の行のデータをフェッチします。

 &sql(FETCH MyCursor)

FETCH 呼び出しの前に、カーソルの DECLARE と OPEN を実行する必要があります。

FETCH 文は、 INTO 節を含む場合があります。INTO 節は、カーソルが走査されたときにデータを受け取るローカル・ホスト変数の名前を指定します。例えば、上記の例に INTO 節を追加すると、以下になります。

 &sql(FETCH MyCursor INTO :a, :b)

DECLARE 文と FETCH 文の両方に INTO 節が含まれる場合、FETCH 文により指定されたホスト変数を使用します。

FETCH の呼び出しに成功すると、SQLCODE 変数は 0 に設定されます。それ以上 FETCH するデータがない場合は、SQLCODE は 100 に設定されます (これ以上データがないことを意味します)。

クエリによっては、最初の FETCH 呼び出しが別のタスク (一時的なデータ構造内の値の並べ替えなど) を実行する場合もあります。

CLOSE 文は、カーソルの実行を終了します。

 &sql(CLOSE MyCursor)

CLOSE 文は、クエリの実行に使用したテンポラリ･ストレージをクリーンアップします。CLOSE 呼び出しに失敗したプログラムでは、リソース・リークが起こる可能性があります (テンポラリ･データベースの CACHETEMP の不必要な増加など)。

CLOSE 呼び出しに成功すると、SQLCODE 変数が 0 に設定されます。

埋め込み SQL 文は、変数 SQLCODE が成功か、失敗かを示すように設定します。SQLCODE が 0 である場合、成功を意味します。SQL エラー・コードの一覧は、SQL エラー・コードリファレンスを参照してください。

埋め込み SQL 文は、変数 %ROWCOUNT が、特定の文により影響を受けた行の数を示すように設定します。

例えば、以下のコードは年収 $50,000 以下の社員の給料を更新し、その変化の影響を受けた社員の数を表示します。

 &sql(UPDATE MyApp.Employee 
 	Set Salary = (Salary * 1.1)
 	WHERE Salary < 50000)

 Write "Employees: ", %ROWCOUNT,!

(任意のプロセス内の) 埋め込み SQL 文はすべて、%ROWCOUNT 変数を変更します。%ROWCOUNT により得られた値が必要な場合は、次の埋め込み SQL 文を実行する前にその値を取得してください。

%ROWCOUNT の値は、明示的なトランザクションのロール・バックによる影響を受けません。例えば、以下はロール・バックされているにもかかわらず、変更があったことをレポートします。

 TSTART // start an explicit transaction
 &sql(UPDATE MyApp.Employee 
 	Set Salary = (Salary * 1.1)
 	WHERE Salary < 50000)

 TROLLBACK // force a rollback; this will NOT modify %ROWCOUNT
 Write "Employees: ", %ROWCOUNT,!

暗黙のトランザクション (UPDATE が制約のチェックに失敗した場合など) は、%ROWCOUNT により反映されます。

埋め込み SQL INSERT 文は、変数 %ROWID が、新規の行に割り当てられた行 ID (オブジェクト ID) の値を示すように設定します。

例えば、以下のコードはデータベースに新規の Person を挿入します。

 &sql(INSERT INTO Sample.Person 
	(Name,SSN) 
	VALUES ('Swift,Jonathan','111-22-3333'))

 If (SQLCODE = 0) {
 	Write "New Person inserted with ID: ", %ROWID,!
 }

埋め込み SQL では、SQL NULL 値は、ホスト変数内で空文字列として表されます。例えば、ID 番号が 43 の Person が NULL の Occupation (職業) を持つとします。以下のクエリを実行すると、

 &sql(SELECT Name, Occupation
	INTO :name, :occupation
	FROM Person
	WHERE %ID = 43)

ホスト変数 occupation は、NULL 値を表す "" (空文字列) に設定されます。

Occupation に空文字列が含まれる (アプリケーションが明示的にフィールドを空文字に設定するなど) 場合、ホスト変数は特別なマーカー値 $C(0) (ASCII の 0 の文字を 1 つのみを含む文字列) を含みます。

この NULL の振る舞いは、ホスト・モードのクエリに固有のものであり、ODBC や JDBC では、NULL 値は ODBC と JDBC の仕様によって定義された特別な値を使用して表されます。