带有的入出参的动态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;
注意:
- 动态sql不能写在 read only 的procedure里。即: read sql data
每一个变量都需要单独拼接。 例如:
FROM ' || :LV_TABNAME || ' WHERE
字符标识 ’ 的处理。引用大神的讲解 如何在字符串中加入单引号 。
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