HANA dynamic SQL 实例

带有的入出参的动态sql。

---

参考若禹的动态sql 有感而发
在网上苦苦搜索了许久都没看见一个可以给出实质性帮助的 HANA SQL 动态实例。自己慢慢摸索出一个实现实际功能的procedure。

PROCEDURE "_SYS_BIC"."procedurename"( 
                                                    IN  IN_POSID VARCHAR(200),
                                                    IN  IN_TRANSFER_FLAG VARCHAR(10),
                                                    IN  IN_PROVINCE VARCHAR(50),
                                                    IN  IN_INVEST VARCHAR(50),
                                                    IN  IN_BUDGET VARCHAR(50),
                                                    IN  IN_BPOST  VARCHAR(20),
                                                    IN  IN_BILLNO VARCHAR(128),

                                                    OUT GT_WBS table_type
                                                    ) 
        LANGUAGE SQLSCRIPT
        SQL SECURITY DEFINER 
        --DEFAULT SCHEMA <default_schema_name>
        --READS SQL DATA 
        --HANA的procedure中动态sql不能为read only
        AS
    BEGIN
    /***************************** 
        Write your procedure logic 
     *****************************/
      DECLARE LV_PRART NVARCHAR(2);
      DECLARE LV_TRANSFER_FLAG VARCHAR(10);
      DECLARE LV_INVEST VARCHAR(50);
      DECLARE LV_BUDGET VARCHAR(50);
      DECLARE LV_BPOST VARCHAR(20);
      DECLARE LV_BILLNO VARCHAR(128);
      DECLARE LV_POSID VARCHAR(200);
      DECLARE LV_QUERY1 STRING;
      DECLARE LV_QUERY2 STRING;
      DECLARE LV_TABNAME VARCHAR(100);
      CREATE GLOBAL TEMPORARY TABLE LT_TABLE LIKE table_type; --于程序最后删除

      --判断入参是否为空
      IF IN_INVEST = '*'
      THEN
          LV_INVEST :='%';
      ELSE 
          LV_INVEST :=IN_INVEST;
      END IF ;  

      IF IN_BUDGET = '*'
      THEN
          LV_BUDGET :='%';
      ELSE 
          LV_BUDGET :=IN_BUDGET;
      END IF ;    

      IF IN_BPOST = '*'
      THEN
          LV_BPOST :='%';
      ELSE 
          LV_BPOST :=IN_BPOST;
      END IF ;    

      IF IN_BILLNO = '*'
      THEN
          LV_BILLNO :='%';
      ELSE 
          LV_BILLNO :=IN_BILLNO;
      END IF ; 

      IF IN_TRANSFER_FLAG = '*'
      THEN
          LV_TRANSFER_FLAG :='%';
      ELSE 
          LV_TRANSFER_FLAG :=IN_TRANSFER_FLAG;
      END IF ;

      LV_POSID := IN_POSID;

      --根据省份入参决定动态sql 
      IF IN_PROVINCE = '安徽'
      THEN
      LV_TABNAME := 'select_table_name';
      END IF;

           LV_QUERY1 := 'INSERT INTO LT_TABLE (
                 SELECT
                POSID,
                POST1,
                BILLNO,
                BPOST,
                STATUS,
                BILLNO_NAME,
                CRDAT,
                INVEST,
                BUDGET,
                TRANSFER_FLAG,
                USER_ID,
                USER_CRT
                FROM 
         (SELECT
                POSID,
                POST1,
                BPOST,
                BILLNO,
                BILLNO_NAME,
                USER_CRT,
                MIN(TRANSFER_FLAG) as TRANSFER_FLAG,
                MAX(STATUS) as STATUS,
                MAX(CRDAT) as CRDAT,
                MAX(USER_ID) as USER_ID,
                SUM(INVEST) as INVEST,
                SUM(BUDGET) as BUDGET
           FROM  ' || :LV_TABNAME || '  
           WHERE POSID = ''' || :LV_POSID || '''
            AND TRANSFER_FLAG LIKE ''' || :LV_TRANSFER_FLAG || ''' 
            AND INVEST LIKE ''' || :LV_INVEST || '''
            AND BUDGET LIKE ''' || :LV_BUDGET || '''
            AND BPOST  LIKE ''' || :LV_BPOST  || '''
            AND BILLNO LIKE ''' || :LV_BILLNO || '''
            GROUP BY
                POSID,
                POST1, 
                BPOST,
                BILLNO,
                BILLNO_NAME,
                USER_CRT)   
        ORDER BY POSID,
                 BILLNO,
                 CRDAT,
                 BPOST) ';

            LV_QUERY2 := 'INSERT INTO LT_TABLE (
                 SELECT
                POSID,
                POST1,
                BILLNO,
                BPOST,
                STATUS,
                BILLNO_NAME,
                CRDAT,
                INVEST,
                BUDGET,
                TRANSFER_FLAG,
                USER_ID,
                USER_CRT
                FROM 
         (SELECT
                POSID,
                POST1,
                BPOST,
                BILLNO,
                BILLNO_NAME,
                USER_CRT,
                MIN(TRANSFER_FLAG) as TRANSFER_FLAG,
                MAX(STATUS) as STATUS,
                MAX(CRDAT) as CRDAT,
                MAX(USER_ID) as USER_ID,
                SUM(INVEST) as INVEST,
                SUM(BUDGET) as BUDGET
           FROM  "_SYS_BIC"."ZGUO_TEST.ZCT_TEST/ZWBS_CA_ALL" 
            WHERE POSID IN(
           SELECT POSID 
             FROM select_table1 
            WHERE PSPHI = 
             (SELECT PSPHI 
              FROM select_table1 
              WHERE POSID = ''' ||  :LV_POSID || '''))
           AND TRANSFER_FLAG LIKE ''' || :LV_TRANSFER_FLAG || '''
            AND INVEST LIKE ''' || :LV_INVEST || '''
            AND BUDGET LIKE ''' || :LV_BUDGET || '''
            AND BPOST  LIKE ''' || :LV_BPOST || '''
            AND BILLNO LIKE ''' || :LV_BILLNO || '''
           GROUP BY
                POSID,
                POST1, 
                BPOST,
                BILLNO,
                BILLNO_NAME,
                USER_CRT) 

        ORDER BY POSID,
                 BILLNO,
                 CRDAT,
                 BPOST )';

      --获取子项父项标示
      SELECT PRART INTO LV_PRART from select_table1 as PRPS where POSID = :IN_POSID;

      --子项情况
      IF :LV_PRART = '02' or :LV_PRART ='04' or :LV_PRART ='08'
      THEN
          TRUNCATE TABLE LT_TABLE;
          EXEC LV_QUERY1; 
          GT_WBS = SELECT * FROM LT_TABLE;

      --父项
      ELSEIF :LV_PRART = '01' or :LV_PRART ='03' or :LV_PRART ='07'
      THEN
          TRUNCATE TABLE LT_TABLE;
          EXEC LV_QUERY2;
          GT_WBS = SELECT * FROM LT_TABLE; 

      END IF ;           
      DROP TABLE LT_TABLE;   
    END;

• 注意：

  1. 动态sql不能写在 read only 的procedure里。即： read sql data

  2. 每一个变量都需要单独拼接。 例如：

     FROM  ' || :LV_TABNAME || '  
        WHERE 
     

  3. 字符标识 ’ 的处理。引用大神的讲解 如何在字符串中加入单引号 。

     WHERE POSID = ''' || :LV_POSID || '''
     AND TRANSFER_FLAG LIKE ''' || :LV_TRANSFER_FLAG || ''' 
     AND INVEST LIKE ''' || :LV_INVEST || '''
     AND BUDGET LIKE ''' || :LV_BUDGET || '''
     AND BPOST  LIKE ''' || :LV_BPOST  || '''
     AND BILLNO LIKE ''' || :LV_BILLNO || '''
     GROUP BY