oracle - 将动态输入参数传递给“立即执行”

问题描述

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

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;

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

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

标签： oracleplsqloracle11gdynamic-sql

您不能提供绑定值的字符串列表作为using参数，所以我能看到的唯一方法是使用嵌套的动态 SQL 调用，这有点混乱，意味着必须声明（和绑定）所有可能的参数在内部。嵌套的动态语句。

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;
/

我在这里使用了替代引用机制来减少转义单引号引起的混乱。有两个嵌套级别的引用 - 外层由分隔，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

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

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

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

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

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

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

顺便说一句，您可以通过使用 case 表达式来删除对的重复引用:o_flag（这不是问题，但我觉得有点混乱）：

    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;]';

oracle - 将动态输入参数传递给“立即执行”

问题描述

解决方案

推荐阅读