Необходимо использовать динамические переменные в предложении where, используемом в инструкции EXECUTE IMMEDIATE в ORACLE.

ORACLE (используя SQL DEVELOPER). Мне нужно правильно структурировать оператор EXECUTE IMMEDIATE. У меня нет права "создать". Задача состоит в том, чтобы получить количество строк в таблице за дату для динамического списка таблиц/дат. У меня есть следующее:

 DECLARE CURSOR cur_table_name IS SELECT TABLE_NAME 
 FROM ALL_TABLES WHERE TABLE_NAME IN ('table_a', 'table_b', 'table_c');
 CURSOR cur_BEGIN_DATE IS 
  select to_date('2014-09-25 00:00:00', 'YYYY-MM-DD HH24:MI:SS') + rownum -1 AS BEGIN_DATE,  
  to_date('2014-09-26 00:00:00', 'YYYY-MM-DD HH24:MI:SS') + rownum -1 AS END_DATE from dual 
  Connect by level <= to_date('2014-09-30 00:00:00', 'YYYY-MM-DD HH24:MI:SS') - to_date('2014-09-25 00:00:00', 'YYYY-MM-DD HH24:MI:SS') + 1;

    var_total_rows NUMBER(15);
    var_table_name VARCHAR2 (50);
    var_bgn_date DATE;
    var_end_date DATE;

    BEGIN
  OPEN cur_TABLE_NAME;
  LOOP
    FETCH cur_TABLE_NAME INTO var_table_name;
    EXIT WHEN cur_TABLE_NAME%NOTFOUND;
    --testing output 
   DBMS_OUTPUT.PUT_LINE ('Table: '|| var_table_name);
    var_total_rows :=0;        
  OPEN cur_BEGIN_DATE; 
  LOOP
    FETCH cur_BEGIN_DATE INTO var_bgn_date, var_end_date;
    EXIT WHEN cur_BEGIN_DATE%NOTFOUND;
    --TESTING OUTPUT
     DBMS_OUTPUT.PUT_LINE ('DATES ARE: ' || var_bgn_date || ', ' ||var_end_date|| ' Table IS: '||var_table_name);

  --------THIS IS THE NOT WORKING STATEMENT DUE TO VARIABLES IN THE WHERE STATEMENT:

    execute immediate 'SELECT COUNT(*) FROM '||var_table_name || ' where  DTM >= '|| var_bgn_date ||' and DTM < '||var_end_date INTO var_total_rows;

   DBMS_OUTPUT.PUT_LINE (var_table_name||' '||var_bgn_date||' '||var_end_date ||' '||var_total_rows);

END LOOP;
CLOSE cur_BEGIN_DATE;
END LOOP;
CLOSE cur_TABLE_NAME;

КОНЕЦ;

Если я удалю переменные из оператора where (просто выполните «Выбрать * из || var_table_name в var_total_rows;»), это сработает. И если в предложении where есть статическое значение - оно работает (но зацикливается с той же датой, и мне нужны изменяющиеся даты!). Но я не могу заставить синтаксис работать с динамическими переменными в предложении where. Можно ли это сделать?

Ценю твою помощь!


oracle where-clause execute-immediate
person Elena LL    schedule 19.02.2015    source источник
comment
Это фактический код, который вы используете? Первые 3 строки или около того заполнены синтаксическими ошибками.   -  person mmmmmpie    schedule 19.02.2015

Ответы (1)


arrow_upward
1
arrow_downward

Ваши переменные var_bgn_date и var_end_date имеют тип DATE, но они подключаются к динамическому оператору как строки без кавычек с неявным форматированием на основе значения сеанса NLS_DATE_FORMAT. Вы получите сгенерированный оператор, например:

SELECT COUNT(*) FROM table_a  where  DTM >= 2014-09-25 00:00:00 and DTM < 2014-09-26 00:00:00

Вы можете добавить экранированные одинарные кавычки, чтобы превратить это в допустимый оператор, по-прежнему полагаясь на неявное обратное преобразование с использованием тех же настроек NLS:

EXECUTE immediate 'SELECT COUNT(*) FROM '||var_table_name
  || ' where  DTM >= '''|| var_bgn_date ||''' and DTM < '''||var_end_date ||''''
INTO var_total_rows;

который будет генерировать:

SELECT COUNT(*) FROM table_a where  DTM >= '2014-09-25 00:00:00' and DTM < '2014-09-26 00:00:00'

Но на самом деле вы должны использовать переменные связывания, чтобы избежать преобразования в строки или из строк:

EXECUTE immediate 'SELECT COUNT(*) FROM '||var_table_name
  || ' where  DTM >= :bgn_date and DTM < :end_date'
INTO var_total_rows
USING var_bgn_date, var_end_date;
person Alex Poole    schedule 19.02.2015
comment
Алекс, а куда делась бы еще одна цитата? Перед оператором 'where стоит кавычка, но она не закрывается. - person Elena LL; 19.02.2015
comment
@ElenaLL - обновлен пример с цитированием; но вы должны использовать переменные связывания - это более эффективно, меньше нагружает кэши БД, и в других сценариях можно избежать атак SQL-инъекций. Это хорошая привычка. - person Alex Poole; 19.02.2015
comment
Алекс, я имел в виду, что в примере с переменными привязки кавычка отсутствует. Тот, что с преобразованием, действительно работал (YEY!), но, как вы также заявили, переменные связывания были бы более эффективными, и в этом коде отсутствует кавычка. Если у вас есть минутка - можете ли вы отредактировать это, пожалуйста? Очень ценю вашу помощь! - person Elena LL; 19.02.2015
comment
@ElenaLL - извините, да, глупая опечатка... Я это уже исправил - person Alex Poole; 19.02.2015