DB2 多游标存储过程测试

--DB2多游标存储过程测试--彭建军--2005-11-25

--创建测试环境

CREATE TABLE T1( C1 INT, C2 VARCHAR(10));

CREATE TABLE T2( C1 INT, C2 VARCHAR(10));

INSERT INTO T1 VALUES (1,'TEST1'),(2,'TEST2'),(3,'TEST3'),(4,'TEST4');

SELECT * FROM T1;

SELECT * FROM T2;

CREATE PROCEDURE P1()LANGUAGE SQLBEGIN  --从T1中插入数据到T2中  FOR v1 AS Cursor1 CURSOR FOR    SELECT C1 v_C1,C2 v_C2 from T1  DO    INSERT INTO T2      SELECT C1,C2 FROM T1 WHERE C1 = v_C1 AND C2 = v_C2;  END FOR;    --删除T1表中的数据，这些数据必须满足也存在于T2中的条件  FOR v2 AS Cursor2 CURSOR FOR    SELECT C1 v_C1,C2 v_C2 from T2  DO    DELETE FROM T1 WHERE C1 = v_C1 AND C2 = v_C2;  END FOR;END@