DB2存储过程实现查询表数据，生成动态SQL，并执行

一、动态执行SQL

PREPARE S1 FROM 'delete from test';
EXECUTE S1;

 

二、使用游标

DECLARE V_CURSOR CURSOR FOR
SELECT DELETESQL,INSERTSQL FROM FJDC.V_I_DG_DM_ZY_WL_ZBHZ_ATTR T;
                
OPEN V_CURSOR;

FETCH V_CURSOR INTO V_DELETESQL,V_INSERTSQL;

CLOSE V_CURSOR;

 

三、WHILE循环　　

WHILE true DO
    表达式
END WHILE;

 

四、DB2查询表数据，生成动态SQL，并执行

CREATE PROCEDURE "TEST"."TEST" ()
BEGIN
        DECLARE V_DELETESQL VARCHAR(2000);
        DECLARE V_INSERTSQL VARCHAR(2000);
        DECLARE I_COUNT INT;
        
        --确定循环次数
        SELECT COUNT(INSERTSQL) INTO I_COUNT FROM FJDC.V_I_DG_DM_ZY_WL_ZBHZ_ATTR T;
        IF I_COUNT > 0 THEN
                --游标
                DECLARE V_CURSOR CURSOR FOR 
                SELECT DELETESQL,INSERTSQL FROM FJDC.V_I_DG_DM_ZY_WL_ZBHZ_ATTR T;
                
                OPEN V_CURSOR;
                WHILE I_COUNT > 0 DO
                        SET I_COUNT = I_COUNT-1;
                        FETCH V_CURSOR INTO V_DELETESQL,V_INSERTSQL;
                        
                        --执行动态SQL
                        PREPARE S1 FROM V_DELETESQL;
                        EXECUTE S1;
                        
                        PREPARE S1 FROM V_INSERTSQL;
                        EXECUTE S1;
        
                END WHILE;
                CLOSE V_CURSOR;
        
        END IF;
END