SQLのfetchについてまとめています。サンプルのストアドプロシジャを使って解説。
目次
fetchは、select結果を順に取り出すSQL
fetchは、ストアドプロシジャ内で指定したSQLを実行して一件づつデータを取得し変数に格納するために使われるSQLコードです。
以下のSQLは、２つのテーブルから値を一件づつfetchして取得し、別のテーブルに値をinsertするMySQL用のサンプルです。
DELIMITER //
CREATE PROCEDURE curdemo()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE a CHAR(16);
DECLARE b, c INT;
DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur1;
OPEN cur2;
read_loop: LOOP
FETCH cur1 INTO a, b;
FETCH cur2 INTO c;
IF done THEN
LEAVE read_loop;
END IF;
IF b < c THEN
INSERT INTO test.t3 VALUES (a,b);
ELSE
INSERT INTO test.t3 VALUES (a,c);
END IF;
END LOOP;
CLOSE cur1;
CLOSE cur2;
END;
//
DELIMITER ;
declareでselect文から一件づつ取得するためのカーソルを定義し、ループ処理中でカーソルからローカル変数a,b,cに値を取得しています。
MySQLでは、データが見つからなかった場合にNOT FOUNDのステータスを返します。以下のdeclareで継続条件を指定しています。
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
データが見つからなかった場合の判定方法は、データベースによって異なるようです。
【関連記事】
▶SQLのdeclareは、ストアドプロシジャ内のローカル変数宣言をおこなう
fetchは、標準SQLで、カーソル定義・操作用のSQLとして定められています。カーソルは、データベースサーバ側にある結果集合と取得する行の位置を示すもので、主にアプリケーションプログラムなどの手続き型言語からSQL実行用に利用します。
fetchを実行すると、カーソルのポインタが示す位置の行データを取得し、ポインタを一行文進めます。
主要なデータベースでは、ストアドプロシジャ内で使用することも多いようです。
【関連記事】
▶SQLとはどういうもの？ 独自拡張と標準SQLの大きな違いって、何？
OracleのFetchの注意点
以下は、Oracleでfetchを使ってデータを順次読み出しするサンプルです。fetchでデータが見つからなかったときの処理に注意が必要です。
DECLARE
l_total INTEGER := 10000;
CURSOR employee_id_cur
IS
SELECT employee_id
FROM plch_employees
ORDER BY salary ASC;
l_employee_id employee_id_cur%ROWTYPE;
BEGIN
OPEN employee_id_cur;
LOOP
FETCH employee_id_cur INTO l_employee_id;
EXIT WHEN employee_id_cur%NOTFOUND;
assign_bonus (l_employee_id, l_total);
EXIT WHEN l_total <= 0;
END LOOP;
CLOSE employees_cur;
END;
Oracleでは、問い合わせで1行も見つからなかったときに、NO_DATA_FOUNDが発生しません。代わりにcursor_name%NOTFOUND属性がTRUEになります。
このため、データが見つからずループを抜ける処理で、以下のように記述しています。
EXIT WHEN employee_id_cur%NOTFOUND;
SQL ServerのFetchの注意点
SQL Serverのfetchサンプルは以下の通りです。
DECLARE vend_cursor CURSOR
FOR SELECT * FROM Purchasing.Vendor
OPEN vend_cursor
FETCH NEXT FROM vend_cursor;
SQL Serverには、カーソル定義の書式に2種類あります。標準SQLに準拠した書式と、Transact-SQLの独自拡張の書式です。
//標準SQLの書式
DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR
FOR select_statement
[ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]
[;]
//Transact-SQL の拡張書式
DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ]
[ FORWARD_ONLY | SCROLL ]
[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
[ TYPE_WARNING ]
FOR select_statement
[ FOR UPDATE [ OF column_name [ ,...n ] ] ]
[;]
参考）DECLARE CURSOR (Transact-SQL) – SQL Server | Microsoft Docs
PostgreSQLのFetchの注意点
PostgreSQLのFetchの書式は以下の通りです。
FETCH [ 方向 [ FROM | IN ] ] cursor_name
指定したカーソル(cur1)から最初の5行を取り出すには、以下のように指定します。
FETCH FORWARD 5 FROM cur1;
なお、FORWARDやBACKWORDなどの方向指定はPostgreSQLの拡張です。fetchのIN指定もPostgreSQLの拡張になります。標準SQLでは、FETCHのカーソル名の前に指定できるのはFROMのみです。
互換性に配慮してSQLコードを記述する際は注意しましょう。
まとめ
- fetchは、selectの結果集合を一件づつ読み出すSQLコード。
- fetch結果、データが存在しない場合の判定方法は、データベースによって異なる。
- fetchは標準SQLに定義されているが、データベースにより独自拡張されているケース有り。
[/commen]
