摘要： DB2中经常会使用到CURSOR，这篇文章举几个例子说明cursor的用法。

准备工作：

$ db2 "select * from t2"

ID -----------           1         100         200         300        2000   5 record(s) selected.

测试1，CURSOR基本操作。

包括cursor的declare、open、fetch与close:

1.) declare cursor $ db2 +c "declare c2 cursor for select * from t2" DB20000I The SQL command completed successfully. 2.) open cursor $ db2 +c "open c2" DB20000I The SQL command completed successfully. 3.) Fetch from cursor $ db2 +c "fetch c2" ID ----------- 1 1 record(s) selected. $ db2 +c "fetch c2" ID ----------- 100 1 record(s) selected. $ db2 +c "fetch c2" ID ----------- 200 1 record(s) selected. $ db2 +c "fetch c2" ID ----------- 300 1 record(s) selected. $ db2 +c "fetch c2" ID ----------- 2000 1 record(s) selected. $ db2 +c "fetch c2" ID ----------- 0 record(s) selected.

4.) Close cursor

$ db2 +c "close c2" DB20000I The SQL command completed successfully. $ db2 commit DB20000I The SQL command completed successfully.

============

测试2 Cursor load

通过load from cursor完成LOAD操作，这个可以跨数据库LOAD: $ db2 "connect to resdb" Database Connection Information Database server = DB2/NT64 10.5.6 SQL authorization ID = MIAOQING... Local database alias = RESDB $ db2 "create table t3(id int)" DB20000I The SQL command completed successfully. $ db2 "declare c2 cursor database sample user miaoqingsong using mypwd for select * from miaoqingsong.t2" DB20000I The SQL command completed successfully. $ db2 "load from c2 of cursor insert into t3" SQL3253N The utility is beginning to load data from the SQL statement " select * from miaoqingsong.t2" in database "sample". SQL3500W The utility is beginning the "LOAD" phase at time "2016-09-13 15:29:59.315294". SQL3519W Begin Load Consistency Point. Input record count = "0". SQL3520W Load Consistency Point was successful. SQL3110N The utility has completed processing. "5" rows were read from the input file. SQL3519W Begin Load Consistency Point. Input record count = "5". SQL3520W Load Consistency Point was successful. SQL3515W The utility has finished the "LOAD" phase at time "2016-09-13 15:30:00.199938". Number of rows read = 5 Number of rows skipped = 0 Number of rows loaded = 5 Number of rows rejected = 0 Number of rows deleted = 0 Number of rows committed = 5 $ db2 "select * from t3" ID ----------- 1 100 200 300 2000 5 record(s) selected.   

测试3：存储过程。

这个测试中，在存储过程中，通过cursor的操作，读取表t2的数据，并求它们的和 $ db2 -td@ -vf c:\createprocedure.txt CREATE OR REPLACE PROCEDURE sum_t2() LANGUAGE SQL BEGIN DECLARE p_sum INTEGER; DECLARE p_sal INTEGER; DECLARE SQLSTATE CHAR(5) DEFAULT '00000'; DECLARE c CURSOR FOR SELECT ID FROM T2; SET p_sum = 0; OPEN c; FETCH FROM c INTO p_sal; WHILE(SQLSTATE = '00000') DO SET p_sum = p_sum + p_sal; FETCH FROM c INTO p_sal; END WHILE; CLOSE c; return p_sum; END DB20000I The SQL command completed successfully. $ db2 "select sum(id) from t2" 1 ----------- 2601 1 record(s) selected. $ db2 "call sum_t2()" Return Status = 2601