DB2嵌套循环

ezeke

测试环境搭建：
1.测试表：年级、班级、学生、临时表

CREATE TABLE HTSAP.TESTSTU(
ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE),
CLASSNAME VARCHAR(15),
NAME VARCHAR(15),
AGE INTEGER,
SEX CHAR(4)
);
CREATE TABLE HTSAP.TESTCLASS(
ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE),
NAME VARCHAR(15),
GREADNAME VARCHAR(15)
);
CREATE TABLE HTSAP.TESTGREAD(
ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE),
NAME VARCHAR(15)
);
INSERT INTO HTSAP.TESTSTU(CLASSNAME,NAME,AGE,SEX)
VALUES('一班','XIAOMING1',15,'男'),('一班','XIAOMING2',15,'男'),('一班','XIAOMING3',15,'男'),
('二班','XIAOMING1',15,'男'),('二班','XIAOMING2',15,'男'),('二班','XIAOMING3',15,'男')
;
INSERT INTO HTSAP.TESTCLASS(NAME,GREADNAME)
VALUES('一班','一年级'),('二班','二年级');
INSERT INTO HTSAP.TESTGREAD(NAME)
VALUES('一年级'),('二年级');
DROP TABLE SESSION.TEMP_TEST;
DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_TEST
(
ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE),
NAME VARCHAR(15),
AGE INTEGER,
SEX CHAR(4)
)ON COMMIT PRESERVE ROWS;

测试存储过程：

CREATE PROCEDURE LOOP_TEST (  )
DYNAMIC RESULT SETS 1
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN
-- Declare cursor
DECLARE STUNAME VARCHAR(15);
DECLARE AGE INTEGER;
DECLARE SEX CHAR(4);
DECLARE CLASSNAME VARCHAR(15);
DECLARE GREADNAME VARCHAR(15);
DECLARE STMT VARCHAR(120);
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE HSQLCODE INTEGER DEFAULT 0;
DECLARE C1 CURSOR WITH RETURN FOR SELECT * FROM SESSION.TEMP_TEST;
DECLARE C2 CURSOR  FOR S2;
DECLARE C3 CURSOR  FOR S3;
DECLARE C4 CURSOR  FOR S4;
DECLARE CONTINUE HANDLER FOR NOT FOUND
BEGIN
SET HSQLCODE = SQLCODE;
END;
SET STMT = 'SELECT G.NAME FROM HTSAP.TESTGREAD AS G';
PREPARE S2 FROM STMT;
OPEN C2;
LOOP_C2:
LOOP
FETCH C2 INTO GREADNAME;
IF HSQLCODE <> 0 THEN
SET HSQLCODE = 0;
LEAVE LOOP_C2;
END IF;
SET STMT = 'SELECT C.NAME FROM HTSAP.TESTCLASS AS C WHERE C.GREADNAME = '''||CHAR(GREADNAME)||'''';
PREPARE S3 FROM STMT;
OPEN C3;
LOOP_C3:
LOOP
FETCH C3 INTO CLASSNAME;
IF HSQLCODE <> 0 THEN
SET HSQLCODE = 0;
LEAVE LOOP_C3;
END IF;
SET STMT = 'SELECT S.NAME,S.AGE,S.SEX FROM HTSAP.TESTSTU AS S WHERE S.CLASSNAME = '''||CHAR(CLASSNAME)||'''';
PREPARE S4 FROM STMT;
OPEN C4;
LOOP_C4:
LOOP
FETCH C4 INTO STUNAME,AGE,SEX;
IF HSQLCODE <> 0 THEN
SET HSQLCODE = 0;
LEAVE LOOP_C4;
END IF;
INSERT INTO SESSION.TEMP_TEST(NAME,AGE,SEX) VALUES(STUNAME,AGE,SEX);
END LOOP;
CLOSE C4;
END LOOP;
CLOSE C3;
END LOOP;
CLOSE C2;
OPEN C1;
END P1

问题与解决：
问题：
DB2动态SQL语句变量赋值字符串无法识别问题。

SET STUNAME = 'XIAOMING2';
SET CLASSNAME = '一班';
SET STMT = 'SELECT S.NAME,S.AGE,S.SEX FROM HTSAP.TESTSTU AS S WHERE S.NAME = '||CHAR(STUNAME)||' AND S.CLASSNAME = '||CHAR(CLASSNAME);

在网上的资料中队字符串变量的处理都是这样就可以了，但是在实际使用中，变量没有单引号，无法被DB2识别。
解决：

SET STMT = 'SELECT S.NAME,S.AGE,S.SEX FROM HTSAP.TESTSTU AS S WHERE S.NAME = '''||CHAR(STUNAME)||''' AND S.CLASSNAME = '''||CHAR(CLASSNAME)||'''';

第一个引号：转义
第二个引号：表示引号
第三个引号：返回sql语句
||CHAR(STUNAME)||：为sql语句赋值。
最后面的第四个引号：结束sql语句