DB2嵌套循环
测试环境搭建：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 cursorDECLARE 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 CURSORFOR S2;DECLARE C3 CURSORFOR S3;DECLARE C4 CURSORFOR S4;DECLARE CONTINUE HANDLER FOR NOT FOUNDBEGINSET HSQLCODE = SQLCODE;END;SET STMT = 'SELECT G.NAME FROM HTSAP.TESTGREAD AS G';PREPARE S2 FROM STMT;OPEN C2;LOOP_C2:LOOPFETCH C2 INTO GREADNAME;IF HSQLCODE <> 0 THENSET 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:LOOPFETCH C3 INTO CLASSNAME;IF HSQLCODE <> 0 THENSET 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:LOOPFETCH C4 INTO STUNAME,AGE,SEX;IF HSQLCODE <> 0 THENSET 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语句
页:
[1]