Table of Contents
PostgreSQL の PLpg/SQL での基本のカーソル操作についてまとめました。
環境
- psql 9.6
- PostgreSQL 9.3
カーソル
ざっくりと説明すると、 SELECT で取得した全レコードそれぞれについて逐次処理をしたい場合に使用するものです。
カーソルを定義して使う
カーソルは書き方がいくつかあるのですが、まずは基本から。 Oracle では 明示カーソル と呼ばれます。 PostgreSQL のドキュメントでは Bound Cursor と書かれています。
CURSOR でカーソルを定義して、 RECORD型の変数を用意しておきます。 LOOP を使用して、 SELECT の結果全行に対して処理を行います。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
DO $$ DECLARE C CURSOR FOR SELECT * FROM table_name WHERE some_condition = TRUE; CR RECORD; BEGIN OPEN C; LOOP FETCH C INTO CR; IF NOT FOUND THEN EXIT; END IF; RAISE NOTICE '%', CR.id; END LOOP; CLOSE C; END$$; |
CURSOR の後にある FOR は IS でも可です。 公式ドキュメントには FOR の記述しかありませんが、 IS に変更しても動きます。
カーソルを開き、クエリ結果の行を変数(ここではCR)に入れて全行の処理を行います。 カーソルが最終行まで処理をしたら
EXIT でループを抜けるようにしています。 最後、開いたカーソルは閉じておきます。
EXIT のところは IF を使って条件分岐していますが、 EXIT を実行したいだけなら もっと短く書くこともできます。 EXIT WHEN NOT FOUND; という行を入れます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
DO $$ DECLARE C CURSOR FOR SELECT * FROM table_name WHERE some_condition = TRUE; CR RECORD; BEGIN OPEN C; LOOP FETCH C INTO CR; EXIT WHEN NOT FOUND; RAISE NOTICE '%', CR.id; END LOOP; CLOSE C; END$$; |
また、 FETCH のタイミングが単純ですから、もっと短縮して書くことができます。
|
1 2 3 4 5 6 7 8 9 10 11 |
DO $$ DECLARE C CURSOR FOR SELECT * FROM table_name WHERE condition = true; BEGIN FOR CR IN C LOOP RAISE NOTICE '%', CR.id; END LOOP; END$$; |
この場合、レコードを格納する変数 CR はループ内でしか使えません。
カーソルでパラメータを使う
カーソルは、プログラミング言語でいう関数のように、引数をとることができます。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
DO $$ DECLARE C CURSOR( I_MIN_ID INTEGER, I_MAX_ID INTEGER) FOR SELECT * FROM table_name WHERE id BETWEEN I_MIN_ID AND I_MAX_ID; BEGIN FOR CR IN C(10, 20) LOOP RAISE NOTICE '%', CR.id; END LOOP; END$$; |
カーソルを宣言せずに使う
カーソルの応用編といってもいいと思います。 私はあまり使いません。 Oracle では 暗黙カーソル と呼ばれます。
|
1 2 3 4 5 6 7 8 |
DO $$ DECLARE CR RECORD; BEGIN FOR CR IN (SELECT * FROM MEMBERS WHERE ID BETWEEN 1 AND 10) LOOP RAISE NOTICE '%', CR.id; END LOOP; END$$; |
カーソル変数を宣言していません。 レコードの変数は宣言が必要です。 (Oracle PL/SQL では レコードの変数は宣言しなくてもよかったですね。)
