Netteza动态SQL布尔(任意)语句

Question

对于这个例子，我想在Netezza中使用动态sql。但我做不到。通常，我想知道是否可以对不需要“立即执行”的代码部分使用动态实例。例如，If else语句的条件。当我连续有几个语句时，我基本上不知道如何使用动态sql。如有任何意见，敬请见谅。

CREATE OR REPLACE PROCEDURE "SP_Count_Ndays_between_date1_date2_where"  (DATE, DATE, varchar(400), varchar(400))
RETURNS INTEGER
LANGUAGE NZPLSQL AS
BEGIN_PROC
DECLARE
START_DATE ALIAS FOR $1;
END_DATE ALIAS FOR $2;
whichweekdays ALIAS FOR $3;
whichdaysofmonth ALIAS FOR $4;
CURR_DATE DATE;
DAY_COUNT INT;
sql varchar(40000);
BEGIN
DAY_COUNT = 0;


CURR_DATE  =  START_DATE;


WHILE CURR_DATE <=  END_DATE
LOOP

IF  '(extract(dow from CURR_DATE) IN'|| whichweekdays||' and extract(day from CURR_DATE) IN'|| whichdaysofmonth||')' 
    THEN DAY_COUNT  = DAY_COUNT + 1; 
End if ;


  CURR_DATE  = CURR_DATE + 1;
END LOOP;




 RETURN DAY_COUNT;
 END;
 END_PROC;

Answer 1

您可以使用静态SQL和动态SQL混合来解决这个问题。

保持外部循环，然后在外部循环的每个步骤上重新创建一个动态SQL字符串。然后在内部执行循环块中使用该结果。

挖掘布尔值的结果，然后执行非动态的IF-然后继续循环。

CREATE OR REPLACE PROCEDURE SP_Count_Ndays_between_date1_date2_where  (DATE, DATE, varchar(400), varchar(400))
RETURNS INTEGER
LANGUAGE NZPLSQL AS
BEGIN_PROC
DECLARE

START_DATE ALIAS FOR $1;
END_DATE ALIAS FOR $2;
whichweekdays ALIAS FOR $3;
whichdaysofmonth ALIAS FOR $4;
CURR_DATE DATE;
DAY_COUNT INT;
sql varchar(40000);

vResult record;

BEGIN

DAY_COUNT = 0;
CURR_DATE  =  START_DATE;

WHILE CURR_DATE <=  END_DATE
LOOP

    sql := 'SELECT (
    extract(dow from ''' || CURR_DATE|| '''::DATE) IN ('|| whichweekdays||') and extract(day from ''' || CURR_DATE || '''::DATE) IN ('|| whichdaysofmonth|| ')
    ) bool_test' ;

    RAISE NOTICE '%', sql;

    for vResult in execute sql LOOP

        RAISE NOTICE 'Date: %, Test Result %', CURR_DATE, vResult.bool_test;

        IF vResult.bool_test then 
            DAY_COUNT = DAY_COUNT +1;
        END IF;

    END LOOP;

    CURR_DATE  = CURR_DATE + 1;

END LOOP;

RETURN DAY_COUNT;

END;
END_PROC;

使用我过于冗长的调试语句，结果如下：

TESTDB.ADMIN(ADMIN)=> exec SP_Count_Ndays_between_date1_date2_where (CURRENT_DATE, CURRENT_DATE + '4 days'::interval, '4,5,6', '7,8,9,10,11');
NOTICE:  SELECT (
        extract(dow from '2015-08-07'::DATE) IN (4,5,6) and extract(day from '2015-08-07'::DATE) IN (7,8,9,10,11)
        ) bool_test
NOTICE:  Date: 2015-08-07, Test Result t
NOTICE:  SELECT (
        extract(dow from '2015-08-08'::DATE) IN (4,5,6) and extract(day from '2015-08-08'::DATE) IN (7,8,9,10,11)
        ) bool_test
NOTICE:  Date: 2015-08-08, Test Result f
NOTICE:  SELECT (
        extract(dow from '2015-08-09'::DATE) IN (4,5,6) and extract(day from '2015-08-09'::DATE) IN (7,8,9,10,11)
        ) bool_test
NOTICE:  Date: 2015-08-09, Test Result f
NOTICE:  SELECT (
        extract(dow from '2015-08-10'::DATE) IN (4,5,6) and extract(day from '2015-08-10'::DATE) IN (7,8,9,10,11)
        ) bool_test
NOTICE:  Date: 2015-08-10, Test Result f
NOTICE:  SELECT (
        extract(dow from '2015-08-11'::DATE) IN (4,5,6) and extract(day from '2015-08-11'::DATE) IN (7,8,9,10,11)
        ) bool_test
NOTICE:  Date: 2015-08-11, Test Result f
 SP_COUNT_NDAYS_BETWEEN_DATE1_DATE2_WHERE
------------------------------------------
                                        1
(1 row)