SQLのカーソルとは ― 埋込みSQLで1行ずつ読む仕組み

カーソルとは、SELECT文が返した複数の行を、プログラムが1行ずつ受け取るための仕組みです。カーソルは結果の表の中の位置を持ち、FETCH文を実行するたびに次の行へ進みます。

C言語などのプログラムにSQLを書き込む埋込みSQLで、SELECT文の結果が複数行になるときに使います。プログラムの変数には1行分の値しか入らないため、結果を1行ずつ取り出す仕組みが必要です。データベースの中で動くストアドプロシージャでも、同じようにカーソルで1行ずつ読めます。

例には、INSERT・UPDATE・DELETE文の記事と同じ社員表を使います。社員IDが主キーで、101から104の4行が入っています。プログラムは、この表から社員ID・氏名・給与を読みます。

CREATE TABLE 社員 (
  社員ID  INTEGER PRIMARY KEY,
  氏名    VARCHAR(20) NOT NULL,
  部署ID  CHAR(2) REFERENCES 部署 (部署ID),
  給与    INTEGER
);
-- 101 佐藤 D1 300000／102 鈴木 D2 280000
-- 103 高橋 D1 320000／104 田中 D3 250000

SELECT文は、条件に合う行をまとめて1つの表として返します。社員表を全部読むSELECT文なら、結果は4行です。一方で、C言語のふつうの変数には1つの値しか入りません。

SQLの文の中で使うプログラムの変数を、ホスト変数と呼びます。ホスト変数を3つ用意しても、入るのは1行分の社員ID・氏名・給与だけです。そこで、結果の表の中の1行を指す位置を用意し、1行ずつ変数に渡します。この位置がカーソルです。

PostgreSQLの埋込みSQLの文書も、複数行の結果はカーソルで扱うと書いています。配列のホスト変数で複数行をまとめて受け取る方法は、例外として別に挙げられています。1行だけを返す問合せなら、カーソルは不要です。この書き方は、非カーソル操作の節で説明します。

SQLの使い方には、2つの方式があります。独立言語方式は、SQLだけを単独で使う方式です。親言語方式は、C言語やCOBOLなど、ほかのプログラム言語からSQLを使う方式です。

会話型SQLは、独立言語方式の例です。人が端末からSQL文を1つずつ打ち込み、結果の行を画面でまとめて見ます。PostgreSQLのpsqlのようなツールが、この使い方にあたります。

埋込みSQLは、親言語方式の代表です。プログラムの中に、EXEC SQLで始まるSQL文を書き込みます。このときSQLを受け入れる側のプログラム言語を、親言語と呼びます。

埋込みSQLを含むプログラムは、そのままではコンパイルできません。先にプリプロセッサと呼ぶ前処理のプログラムが、EXEC SQLの部分をDBMSとやり取りする関数の呼び出しに置き換えます。PostgreSQLでは、ECPGがこの役を受け持ち、ふつうのC言語のプログラムに変換します。

カーソルで行を読むときは、宣言・開始・読込み・終了の順に4つの文を実行します。次の例は、C言語の中に書いた埋込みSQLで、社員表の行を社員IDの順に読んで表示します。書き方はPostgreSQLのECPGの文書に合わせています。

EXEC SQL BEGIN DECLARE SECTION;
int  id;
char name[64];
int  pay;
EXEC SQL END DECLARE SECTION;

EXEC SQL DECLARE c_emp CURSOR FOR
  SELECT 社員ID, 氏名, 給与 FROM 社員 ORDER BY 社員ID;

EXEC SQL OPEN c_emp;
EXEC SQL WHENEVER NOT FOUND DO BREAK;
while (1) {
  EXEC SQL FETCH c_emp INTO :id, :name, :pay;
  printf("%d %s %d\n", id, name, pay);
}
EXEC SQL CLOSE c_emp;

DECLAREは、カーソルにc_empという名前を付け、読む問合せを決めます。この時点では、まだ問合せは実行されません。ECPGでは、OPENを実行したときに初めて問合せがサーバに送られます。

OPENの直後、カーソルは1行目の前にあります。まだどの行も指していないので、変数には何も入りません。FETCHを1回実行すると、カーソルが1行目に進み、その行の値がINTOの後のホスト変数に入ります。

SQL文の中のホスト変数には、:idのように先頭にコロンを付けます。INTOに並べる変数は、SELECT文の列と同じ数と順番にします。読み終えたらCLOSEでカーソルを閉じ、DBMSが確保していた資源を解放します。

行が尽きると、FETCHはデータなし（SQLSTATE 02000）を返す

最後の行を読んだ後にFETCHを実行すると、カーソルは最後の行の後に移ります。読む行が無いので、結果はデータなしです。データなしはエラーではなく、SQLSTATEは02000になります。

SQLSTATEは、直前のSQL文の結果を表す5文字のコードです。プログラムは、FETCHのたびにこのコードを確かめ、02000なら読む繰り返しを終えます。上の例のWHENEVER NOT FOUND DO BREAKは、データなしのときに繰り返しを抜ける指定です。

古いプログラムでは、SQLCODEという数値で判定していることもあります。その場合、データなしはSQLCODEの100です。PostgreSQLの文書は、SQLCODEはSQL-92で非推奨になったとして、SQLSTATEを使うよう勧めています。

次の図では、上のプログラムと同じc_empのカーソルを、ボタンで1文ずつ動かせます。カーソルの位置、ホスト変数の中身、プログラムが受け取った行が、文を実行するたびに変わります。結果は、PostgreSQLで同じ問合せを実行して確かめたものです。

EXEC SQL DECLARE c_emp CURSOR FOR ...;

EXEC SQL OPEN c_emp;

EXEC SQL FETCH c_emp INTO :id, :name, :pay;

EXEC SQL FETCH c_emp INTO :id, :name, :pay;

EXEC SQL FETCH c_emp INTO :id, :name, :pay;

EXEC SQL FETCH c_emp INTO :id, :name, :pay;

EXEC SQL FETCH c_emp INTO :id, :name, :pay;

EXEC SQL CLOSE c_emp;

EXEC SQL FETCH c_emp INTO :id, :name, :pay;

EXEC SQL CLOSE c_emp;

EXEC SQL OPEN c_emp;

CLOSEの後にFETCHを実行すると、開いていないカーソルからは読めないのでエラーになります。閉じたカーソルは、もう一度OPENすれば1行目の前から読み直せます。

カーソルを使わずに実行する埋込みSQLの操作を、非カーソル操作と呼びます。結果が必ず1行になる問合せは、SELECT ... INTOで直接ホスト変数に入れられます。主キーで1行に絞る問合せが、その例です。

EXEC SQL SELECT 氏名, 給与 INTO :name, :pay
  FROM 社員 WHERE 社員ID = 102;
/* :name に 鈴木、:pay に 280000 が入る */

結果が2行以上になる問合せに、この書き方は使えません。ECPGでは、1行分の変数に複数の行が返るとエラーになります。何行返るか分からない問合せは、カーソルで読みます。

INSERT・UPDATE・DELETE文も、行を受け取らないのでカーソルなしで実行します。値はホスト変数で渡します。次の例は、社員ID 102の給与を変数payの値に書き換えます。

EXEC SQL UPDATE 社員 SET 給与 = :pay
  WHERE 社員ID = :id;

ホスト変数を使うと、値を文字列としてSQL文に貼り込まずに済みます。利用者の入力をSQL文の文字列に貼り込む書き方の危険は、SQLインジェクションとXSSの違いの記事で説明しています。

カーソルで読んだ行を見ながら、その行を書き換えたいことがあります。UPDATE文やDELETE文のWHERE句にCURRENT OFとカーソル名を書くと、そのカーソルで直前にFETCHした1行だけが対象になります。次の例は、部署D1の社員を1行ずつ読み、読んだ行の給与を10000増やします。

EXEC SQL DECLARE c_d1 CURSOR FOR
  SELECT 社員ID, 給与 FROM 社員
  WHERE 部署ID = 'D1' ORDER BY 社員ID
  FOR UPDATE;

EXEC SQL OPEN c_d1;
EXEC SQL WHENEVER NOT FOUND DO BREAK;
while (1) {
  EXEC SQL FETCH c_d1 INTO :id, :pay;
  EXEC SQL UPDATE 社員 SET 給与 = 給与 + 10000
    WHERE CURRENT OF c_d1;
}
EXEC SQL CLOSE c_d1;
EXEC SQL COMMIT;
/* 101 は 310000、103 は 330000 になる */

WHERE CURRENT OFには、ほかの条件を並べて書けません。カーソルが行を指していないときは、エラーになります。PostgreSQLでは、最初のFETCHの前と、行が尽きた後に実行するとエラーになりました。

例のDECLAREに付けたFOR UPDATEは、読んだ行にロックをかける指定です。PostgreSQLでは、行を初めてFETCHした時点でその行がロックされます。読んでから書き換えるまでの間に、ほかの利用者がその行を変えられなくなります。FOR UPDATEで先にロックを取るやり方と、バージョン番号で更新の衝突を見つけるやり方の違いは、楽観ロックと悲観ロックの記事で比べています。

PostgreSQLの文書は、WHERE CURRENT OFを使うカーソルにはFOR UPDATEを付けるよう勧めています。FOR UPDATEが無いと、カーソルを開いた後にほかの利用者が変えた行は、WHERE CURRENT OFで書き換えられません。ORDER BYを含む問合せなどでは、WHERE CURRENT OFがエラーになることもあります。ロックの仕組みと、ロックの待ち合いで起きる問題は、排他制御とデッドロックの記事にまとめました。

PostgreSQLでは、カーソルは開いたトランザクションの中でだけ使えます。COMMITやROLLBACKでトランザクションが終わると、開いていたカーソルは自動で閉じます。読む繰り返しの途中でCOMMITすると、その後のFETCHはエラーになります。

EXEC SQL OPEN c_emp;
EXEC SQL FETCH c_emp INTO :id, :name, :pay;  /* 101 を読む */
EXEC SQL COMMIT;                             /* c_emp は閉じる */
EXEC SQL FETCH c_emp INTO :id, :name, :pay;  /* エラー */

DECLAREでWITH HOLDを指定すると、COMMITの後もカーソルが開いたまま残ります。上の例をWITH HOLDのカーソルで実行すると、COMMITの後のFETCHは102の行を返しました。WITH HOLDのカーソルは、CLOSEを実行するか接続を切るまで残ります。

ただし、WITH HOLDのカーソルでも、作ったトランザクションをROLLBACKすると閉じます。PostgreSQLでは、WITH HOLDとFOR UPDATEは同時に指定できません。ここまではPostgreSQLの動きです。ほかの製品で使うときは、その製品のマニュアルで確かめる必要があります。

ECPGの初期設定では、EXEC SQL COMMITを実行するまで変更は確定しません。コミットとロールバックの意味は、トランザクションの記事で説明しています。ほかのトランザクションがコミットした変更が、同じトランザクションの次の問合せで見えるかどうかは、分離レベルで決まります。

• PostgreSQL公式ドキュメント「Running SQL Commands」「Using Host Variables」「Error Handling」（ECPG）
• PostgreSQL公式ドキュメント「DECLARE」「FETCH」「CLOSE」「UPDATE」