Shulou(Shulou.com)06/01 Report--

Sample Code:

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;

Attention:

SQLCODE must be defined and must be defined below the outermost BEGIN.

2, you must determine whether SQLCODE is equal to 100, equal to 100 when exiting CURSORLOOP, otherwise it will be endless loop.

3. Don't forget "CURSORLOOP:" after "OPEN curs".

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.