如何使用动态sql和游标的基本示例。

BEGIN
  DECLARE V_STMT VARCHAR(200);
  DECLARE V_CREATOR VARCHAR(128);
  DECLARE V_NAME VARCHAR(128);
  DECLARE V_COLCOUNT INT;
  DECLARE SQLSTATE CHAR(5);

  DECLARE C1 CURSOR FOR S1;

  SET V_CREATOR = 'SYSIBM';
  SET V_STMT = 'SELECT NAME, COLCOUNT FROM SYSIBM.SYSTABLES WHERE CREATOR = ?';
  PREPARE S1 FROM V_STMT;
  OPEN C1 USING V_CREATOR;
  L1: LOOP
    FETCH C1 INTO V_NAME, V_COLCOUNT;
    IF SQLSTATE = '02000' THEN LEAVE L1; END IF;
    --Some processing here
  END LOOP;
  CLOSE C1;
END
@

至少在db2 for ibm i上， values into 可以动态地准备和运行，从而代替 select into 您需要知道返回了多少列。。。

SET V_SELECT =                   
  'values (SELECT ' || SELECT_FIELDS ||    
  ' FROM '  || TABLE_NAME    ||  
  ' WHERE ' || WHERE_CLAUSE  ||  
  ' ) into ?,?,?' ||
  ' WITH UR';    

Prepare wSelect from :V_SELECT;
//should be checking SQLSTATE/SQLCODE here for errors

execute wSelect using :col1, :col2, col3;
//should be checking SQLSTATE/SQLCODE here for errors

如果返回的列数是动态的，则可以考虑使用 LISTAGG() 函数返回一个逗号分隔的字符串，其中所有值都在一列中。

我用了下面的代码，它工作良好。我可以使用动态运行多个select。

DECLARE DESC_CSR CURSOR WITH HOLD FOR V_SQL;  

  SET V_SELECT =                              
  'SELECT ' || SELECT_FIELDS ||               
  ' FROM '  || TABLE_NAME    ||               
  ' WHERE ' || WHERE_CLAUSE  ||               
  ' WITH UR';                                 

  PREPARE V_SQL FROM V_SELECT;  

  OPEN DESC_CSR USING HOST_VAR_B;

  FETCH DESC_CSR INTO HOST_VAR_A;

我通过的参数如下。使用where子句host\u var\u b作为参数标记“？”，并在open处指定字段。

SELECT_FIELDS = B.COLUMN_A  INTO HOST_VAR_A
TABLE_NAME =  TABLE_A A   INNER JOIN TABLE_B B      
              ON A.ROW_ID = B.ROW_ID
WHERE_CLAUSE = A.COLUMN_B = ?