db2界面调用存储过程_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、SQLCODE必须要定义，且必须定义在最外层的BEGIN的下面。

2、必须要判断SQLCODE是否等于100，等于100时退出CURSORLOOP，否则会死循环。

3、“OPEN curs”之后不要忘记“CURSORLOOP:”。