oracle - 将动态输入参数传递给 'execute Immediate'

Question

我有一个表，其中存储了某些条件以及输入参数，如下所示:

CONDITION                        |   INPUT_PARAMS
---------------------------------------------------------
:p_end_date < :p_start_date      |  v_end_date, IN v_start_date
:p_joining_day = 'MONDAY'        |  v_joining_day 

我想使用 execute immediate来评估条件。

select condition, input_param 
into v_execute_condition, v_input_param 
From table;

v_execute_statement  := 
   'IF '||v_execute_condition ||' '||
   'THEN :o_flag := ''Y'';'   ||' '|| 
   'ELSE :o_flag := ''N'';'   ||' '|| 
   'END IF;';

v_execute_statement := 'BEGIN '||v_execute_statement||' END;';

dbms_output.put_line(v_execute_statement);

EXECUTE IMMEDIATE v_execute_statement USING IN input_param OUT v_flag;

这给了我一个错误。如果我不动态传递输入参数，它就可以工作。

如何动态传递输入参数列表？

Answer 1

您不能以 using 的形式提供绑定(bind)值的字符串列表。参数，所以我能看到的唯一方法是使用嵌套的动态 SQL 调用，这有点困惑，并且意味着必须在内部声明(和绑定(bind))所有可能的参数。嵌套的动态语句。

declare
  v_execute_statement varchar2(4000);
  v_flag varchar2(1);
  v_start_date date := date '2018-01-01';
  v_end_date date := date '2018-01-31';
  v_joining_day varchar2(9) := 'MONDAY';
begin
  -- loop over all rows for demo
  for rec in (
    select condition, input_params
    From your_table
  )
  loop
    v_execute_statement := q'[
      DECLARE
        v_start_date date := :v_start_date;
        v_end_date date := :v_end_date;
        v_joining_day varchar2(9) := :v_joining_day;
      BEGIN 
        EXECUTE IMMEDIATE q'^
          BEGIN
            IF ]' || rec.condition || q'[ THEN
              :o_flag := 'Y';
            ELSE
              :o_flag := 'N';
            END IF;
          END;^'
        USING ]' || rec.input_params || q'[, OUT :v_flag;
      END;]';

    dbms_output.put_line('Statement: ' || v_execute_statement);

    EXECUTE IMMEDIATE v_execute_statement
    USING v_start_date, v_end_date, v_joining_day, OUT v_flag;

    dbms_output.put_line('Result flag: ' || v_flag);
  end loop;
end;
/

我用过 the alternative quoting mechanism这里是为了减少转义单引号引起的困惑。有两层嵌套的引用——外层由 q'[...]' 分隔。和由 q'^...^' 分隔的内部，但如果由于您的实际表格内容而出现问题，您可以使用其他字符。将这些引号转义两个级别将非常难看并且难以理解/正确；而且您还必须担心 condition 中的进一步转义引号字符串，这已经是您提供的第二个示例的现有代码的问题，因为其中包含文本文字。

使用您的两个示例表行和我在运行输出上方显示的虚拟日期/日期值:

Statement: 
      DECLARE
        v_start_date date := :v_start_date;
        v_end_date date := :v_end_date;
        v_joining_day varchar2(9) := :v_joining_day;
      BEGIN 
        EXECUTE IMMEDIATE q'^
          BEGIN
            IF :p_end_date < :p_start_date THEN
              :o_flag := 'Y';
            ELSE
              :o_flag := 'N';
            END IF;
          END;^'
        USING v_end_date, IN v_start_date, OUT :o_flag;
      END;
Result flag: N
Statement: 
      DECLARE
        v_start_date date := :v_start_date;
        v_end_date date := :v_end_date;
        v_joining_day varchar2(9) := :v_joining_day;
      BEGIN 
        EXECUTE IMMEDIATE q'^
          BEGIN
            IF :p_joining_day = 'MONDAY' THEN
              :o_flag := 'Y';
            ELSE
              :o_flag := 'N';
            END IF;
          END;^'
        USING v_joining_day, OUT :o_flag;
      END;
Result flag: Y

在生成的语句中首先要注意的是声明部分，它必须列出您在 input_params 中可能拥有的所有可能的变量名。 , 并从新的绑定(bind)变量中设置它们。您必须在主 block /过程中已经知道这些，作为局部变量或更可能的过程参数；但它们都在这里被复制，因为此时你不知道需要哪个。

然后该语句有它自己的内部动态 SQL，这本质上是您最初所做的，但在 input_params 中连接。字符串以及 condition .

这里的重要部分是引用。例如，在第一个中， :p_end_date和 :p_start_date位于第二级引号内，在 q'^...^' 内，因此它们被绑定(bind)到内部动态 SQL，其值来自本地 v_end_date和 v_start_date来自内心的 execute immediate .

整个生成的 block 使用所有可能的变量名的绑定(bind)值执行，这些变量名提供局部变量的值(通过 v_start_date date := :v_start_date; 等)，同时保留数据类型；加上输出标志。

然后该 block 执行其内部 execute immediate仅使用相关局部变量的语句，这些局部变量现在具有绑定(bind)值；以及仍然是外部 execute immediate 中的绑定(bind)变量的输出标志，所以外部 block 仍然可以看到它的结果。

您可以看到第二个生成的语句使用不同的条件并将变量和值绑定(bind)到第一个，并且在每种情况下根据相关条件和参数评估标志。

顺便说一句，您可以删除对 :o_flag 的重复引用。 (这不是问题，但我觉得有点困惑)使用 case 表达式代替:

    v_execute_statement := q'[
      DECLARE
        v_start_date date := :v_start_date;
        v_end_date date := :v_end_date;
        v_joining_day varchar2(9) := :v_joining_day;
      BEGIN 
        EXECUTE IMMEDIATE q'^
          BEGIN
            :o_flag := CASE WHEN ]' || rec.condition || q'[ THEN 'Y' ELSE 'N' END;
          END;^'
        USING OUT :v_flag, ]' || rec.input_params || q'[;
      END;]';