【IRIS/Cache】【SQL】ロジックからSQLを実行する

2024.05.05

はじめに

IRIS/Cacheで初めてSQLの実行処理を書く方、久し振り過ぎて実行方法忘れた方に向けて、簡単なサンプルで書き方をご紹介しつつ、ゆっくりまったり思い出していければと思います。

これを読んだら、きっと明日からバリバリSQLの実行ロジックが書けるようになります。

はじめに

IRIS/CacheでSQLを実行する方法は２種類あります。
特徴も異なっているので、臨機応変に使い分けて良ければ良いかと思います。

  • ダイナミックSQL　→　汎用性があるけど、速度は埋め込みSQLに劣る
  • 埋め込みSQL　→　速度が速いが、汎用性に劣る

SQLを実行するために、サンプルデータクラスを作成しました。
10,000件のデータを作成したので、このデータクラスを取得するSQLを実行していきたいと思います。

developer.data.DupChk.cls
プロパティ名データ型説明
code%Integerコード(社員コード)
name%String人名
birthDate%Date生年月日
city%String都市名
street%String番地
company%String会社名
title%String役職
mission%String企業の使命ステートメント
ssn%String米国社会保障番号

ダイナミックSQL

ダイナミッククエリは、「%SQL.Statement.cls」をインスタンス化する事から始まります。

ClassMethod dynamicSQL()
{
	s query = "select top 10 ID,birthDate,city,code,company,mission,name,ssn,street,title from developer_data.DupChk"
	s stms = ##class(%SQL.Statement).%New()
	d stms.%Prepare(query)
	s res = stms.%Execute()
	while( res.%Next() ){
		w !,res.%Get("ID"),",",res.%GetData(2)
	}
}

※エラー処理等、割愛しているので適宜追加して下さい。

「%SQL.Statement.cls」をインスタンス化して、「%Prepare()」「%Execute()」の流れです。

値の受け取り方は、「%Get([select句名])」か「%GetData([select句番号])」になります。
　※番号は、select句の左からID=1, birthDate=2,city=3…と登場順に振られます。

結果をターミナルで見てみましょう。

SAMPLE>d ##class(developer.ODBC).dynamicSQL()
 
1,47998
2,40490
3,42657
4,52173
5,52456
6,51614
7,65190
8,43630
9,41552
10,37548

「レコードID」と「birthDate(生年月日)」を取得してみました。
birthDateは「%Date型」なので、$horologの日付数値として取得しています。

表示モードを切り替える

$horologの日付として取得しても、人にはそれが何日か判断できません。
そのため、表示モードをODBCモードで表示してみます。

ODBCモードへの切り替えは、「%SQL.Statement.cls」に引数を与えます。

  • ##class(%SQL.Statement).%New(0)　→　論理モード(規定値)
  • ##class(%SQL.Statement).%New(1)　→　ODBCモード
  • ##class(%SQL.Statement).%New(2)　→　表示モード


管理ポータルでは、下記に該当します。

では、切り替えて出力してみましょう。
ODBCモードとして「s stms = ##class(%SQL.Statement).%New(1)」にしています。

ClassMethod dynamicSQL()
{
	s query = "select top 10 ID,birthDate,city,code,company,mission,name,ssn,street,title from developer_data.DupChk"
	s stms = ##class(%SQL.Statement).%New(1)
	d stms.%Prepare(query)
	s res = stms.%Execute()
	while( res.%Next() ){
		w !,res.%Get("ID"),",",res.%GetData(2)
	}
}
SAMPLE>d ##class(developer.ODBC).dynamicSQL()
 
1,1972-05-31
2,1951-11-10
3,1957-10-16
4,1983-11-05
5,1984-08-14
6,1982-04-25
7,2019-06-26
8,1960-06-15
9,1954-10-07
10,1943-10-21

日付が「yyyy-mm-dd」となり、判別しやすくなりました。

変数「query」が長くなり、可読性が悪いと判断した場合

複数行に分割する事が可能です。
その場合は、変数「query」を配列にし、「%Prepare()」へ渡すとき「.(ドット)query」と記述して下さい。

ClassMethod dynamicSQL()
{
	s query(1) = "select top 10 ID,birthDate,city,code,company,mission"
	, query(2) = ",name,ssn,street,title"
	, query(3) = "from developer_data.DupChk"
	, query    = 3
	
	s stms = ##class(%SQL.Statement).%New(1)
	d stms.%Prepare(.query)
	s res = stms.%Execute()
	while( res.%Next() ){
		w !,res.%Get("ID"),",",res.%GetData(2)
	}
}

where句を指定する場合

where句を設定する場合、直接queryに組み込んでも問題はありませんが、「%Execute()」に渡した方が汎用性が高まります。
　※安全性も高まります。

「%Execute()」に渡すときは、where句には「?」を記述します。
「?」の出現順に「%Execute()」に条件を記載していきます。

サンプルでは、titleに「Hygienist」が含まれており、生年月日が「1970-01-01～1999-12-31」生まれを条件にします。

ClassMethod dynamicSQL()
{
	s query(1) = "select top 10 ID,birthDate,city,code,company,mission"
	, query(2) = ",name,ssn,street,title"
	, query(3) = "from developer_data.DupChk"
	, query(4) = "where title like ? & birthDate between ? and ?"
	, query    = 4
	
	s stms = ##class(%SQL.Statement).%New(1)
	d stms.%Prepare(.query)
	s res = stms.%Execute("%Hygienist","1970-01-01","1999-12-31")
	while( res.%Next() ){
		w !,res.%Get("ID"),",",res.%GetData(2),",",res.%GetData(10)
	}
}

では、実行してみましょう。

SAMPLE>d ##class(developer.ODBC).dynamicSQL()
 
1,1972-05-31,Global Hygienist
67,1989-12-09,Strategic Hygienist
123,1973-06-28,Global Hygienist
130,1975-08-14,Associate Hygienist
136,1984-08-08,Senior Hygienist
207,1984-05-05,Global Hygienist
278,1998-04-25,Strategic Hygienist
420,1977-12-04,Executive Hygienist
484,1979-11-18,Strategic Hygienist
537,1979-12-13,Global Hygienist

条件通りのデータが取得できました。

引数の渡し方を「arg…」構文で渡す事も可能です。
この渡し方で、さらに汎用性が高くなりますね。

ClassMethod dynamicSQL()
{
	// クエリ
	s query(1) = "select top 10 ID,birthDate,city,code,company,mission"
	, query(2) = ",name,ssn,street,title"
	, query(3) = "from developer_data.DupChk"
	, query(4) = "where title like ? & birthDate between ? and ?"
	, query    = 4
	
	// 引数
	s arg(1) = "%Hygienist"
	, arg(2) = "1970-01-01"
	, arg(3) = "1999-12-31"
	, arg    = 3
	
	s stms = ##class(%SQL.Statement).%New(1)
	d stms.%Prepare(.query)
	s res = stms.%Execute(arg...)
	while( res.%Next() ){
		w !,res.%Get("ID"),",",res.%GetData(2),",",res.%GetData(10)
	}
}

少しだけ短く記述したい

「%ExecDirect()」を使う事で、少しだけ記述が短くなります。
まぁ、１行くらい短くなったかな・・・

ClassMethod dynamicSQL()
{
	s query(1) = "select top 10 ID,birthDate,city,code,company,mission"
	, query(2) = ",name,ssn,street,title"
	, query(3) = "from developer_data.DupChk"
	, query(4) = "where title like ? & birthDate between ? and ?"
	, query    = 4
	
	s arg(1) = "%Hygienist"
	, arg(2) = "1970-01-01"
	, arg(3) = "1999-12-31"
	, arg    = 3
	
	s stms = ##class(%SQL.Statement).%New(1)
	s res = stms.%ExecDirect(.stms,.query,arg...)
	while( res.%Next() ){
		w !,res.%Get("ID"),",",res.%GetData(2),",",res.%GetData(10)
	}
}

埋め込みSQL

埋め込みSQLは、「%SQL.Statement.cls」をインスタンス化する等の処理がなく、ストレートにSQLを記述する事が可能です。
　→ダイナミックSQLより早い理由がここにあります。

ただ埋め込みSQLは、コンパイル後にクエリの変更が行えません。
汎用的に検索を行いたい場合は、ダイナミックSQLを利用した方が良いと思います。

埋め込みSQLのサンプル

select句で指定したフィルードは、「into」で「:(コロン)」を付けた変数で受け取ります。
　※select句で指定したフィールドは、全てintoで記載する必要があります。

ClassMethod embeddedSQL()
{
	&sql(
		select top 10
			ID,birthDate,title
			into :id,:bDate,:title
		from
			developer_data.DupChk
	)
	i (SQLCODE=0){
		w !,id,",",bDate
	}
}

結果を見てみましょう。

SAMPLE>d ##class(developer.ODBC).embeddedSQL()
 
1,47998

この記述方法では、１レコードしか表示されません。

複数レコードを取得する方法

埋め込みSQLで複数レコードを取得するには、カーソルの設定が必要になります。
サンプルでのカーソルは「C1」としております。
　→カーソルの命名は任意です。

ClassMethod embeddedSQL()
{
	&sql(
		declare C1 cursor for
		select top 10
			ID,birthDate,title
			into :id,:bDate,:title
		from
			developer_data.DupChk
	)
	&sql(open C1)
	f {
		&sql(fetch C1)
		q:(SQLCODE'=0)
		w !,id,",",bDate
	}
	&sql(close C1)
}

では、実行してみます。

SAMPLE>d ##class(developer.ODBC).embeddedSQL()
 
1,47998
2,40490
3,42657
4,52173
5,52456
6,51614
7,65190
8,43630
9,41552
10,37548

ダイナミックSQLと同じ結果になりました。

埋め込みSQLは、ダイナミックSQLと異なりモード切替が無い為、％Date型のプロパティは「yyyy-mm-dd」への変換は自分で行う必要があります。

where句の指定方法

where句への指定も、select句と同様に「:変数名」で記述します。
ODBCモードが無い為、日付の指定は$horologの日付になります。
　※今回から、出力の際に$zdate()を行い「yyyy-mm-dd」形式で日付を出力します。

ClassMethod embeddedSQL()
{
	s title = "%Hygienist"
	, start = $zdh("1970-01-01",3)
	, end   = $zdh("1999-12-31",3)
	&sql(
		declare C1 cursor for
		select top 10
			ID,birthDate,title
			into :id,:bDate,:title
		from
			developer_data.DupChk
		where
			title like :title & birthDate between :start and :end
	)
	&sql(open C1)
	f {
		&sql(fetch C1)
		q:(SQLCODE'=0)
		w !,id,",",$zd(bDate,3)
	}
	&sql(close C1)
}

では実行します。

SAMPLE>d ##class(developer.ODBC).embeddedSQL()
 
1,1972-05-31
67,1989-12-09
123,1973-06-28
130,1975-08-14
136,1984-08-08
207,1984-05-05
278,1998-04-25
420,1977-12-04
484,1979-11-18
537,1979-12-13

ダイナミッククエリと同じ結果となりました。

ダイナミッククエリの記述と比較してみると、若干埋め込みSQLの方が煩雑に見えるような気がします。

おわりに

いかがだったでしょうか。
だいたいのクエリはこれで実行できると思います。

ダイナミックSQLも埋め込みSQLも、一長一短でどちらが優位かを競う事が難しいです。
汎用性を取るか、速さを取るか。

状況に併せて活用できれば良いかと思います。