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 SQL
BEGIN
  --从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@