DB2 存储过程中执行动态SQL的两种写法
DROP PROCEDURE QUOTATION.COPY_SAMPLE; CREATE PROCEDURE QUOTATION.COPY_SAMPLE (IN tableNameFrom VARCHAR(30)
, IN tableNameTo VARCHAR(30)
, INOUT copyResult INTEGER)
BEGIN
DECLARE SQLCODE INTEGER DEFAULT 0;
SET copyResult = 0;
-- Proecss 1
BEGIN
DECLARE fromSql VARCHAR(32672);
DECLARE toSql VARCHAR(32672);
DECLARE seqTo VARCHAR(30);
DECLARE templateParserId INTEGER;
DECLARE uuid VARCHAR(36);
DECLARE stmt STATEMENT;
DECLARE curs CURSOR FOR stmt;
SET seqTo = 'SEQ_' || tableNameTo;
SET fromSql = 'SELECT MAX(TEMPLATE_PARSER_ID), UUID FROM QUOTATION.' || tableNameFrom || ' GROUP BY UUID';
PREPARE stmt FROM fromSql;
OPEN curs;
CURSORLOOP:
LOOP
FETCH curs INTO templateParserId, uuid;
-- Do nothing if no data or processed all datas.
IF SQLCODE = 100 THEN LEAVE CURSORLOOP;
END IF;
SET uuid = (SELECT CONCAT(HEX(RAND()), HEX(RAND())) FROM SYSIBM.SYSDUMMY1);
SET toSql = 'INSERT INTO QUOTATION.' || tableNameTo || ' (TEMPLATE_PARSER_ID, UUID) VALUES (NEXTVAL FOR QUOTATION.' || seqTo || ',''' || uuid || ''')';
PREPARE s FROM toSql;
EXECUTE s;
END LOOP;
CLOSE curs;
END;
-- Proecss 2
BEGIN
-- ......
END;
SET copyResult = 1;
END;
页:
[1]