本文介绍了将动态输入参数传递给“立即执行"的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

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

I have a table where I am storing certain conditions along with input parameters as shown below:

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;

这给我一个错误.如果我没有动态传递输入参数,它将起作用.

This gives me an error. If I do not pass input parameters dynamically it works.

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

How can I pass the list of input parameters dynamically?

推荐答案

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

You can't supply a string list of bind values as a using parameter, so the only way I can see to do this is with nested dynamic SQL calls, which is a bit messy, and means having to declare (and bind) all possible parameters in the inner. nested, dynamic statement.

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字符串中的转义引号会进一步转义,这对于您提供的第二个示例中的现有代码已经是一个问题,因为其中包含文本文字.

I've used the alternative quoting mechanism here to reduce confusion from escaped single quotes. There are two nested levels of quoting - the outer one delimited by q'[...]' and the inner one delimited by q'^...^', but you can use other characters if those are a problem because of your actual table contents. Escaping those quotes for two levels would be quite ugly and hard to follow/get right; and you'd also have to worry about further escaping quotes in your condition strings, which would already be a problem with your existing code for the second sample you provided as that contains a text literal within it.

在显示运行结果后,显示了两个示例表行和虚拟日期/天值:

With your two sample table rows and the dummy date/day values I showed above the output from running that is:

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中拥有的所有可能的变量名称,并从新的绑定变量中进行设置.您必须已经在主程序块/过程中了解了这些信息,这些信息要么是局部变量,要么是更可能的过程参数.但它们都在此处重复,因为此时您不知道将需要什么.

The first thing to note in the generated statement is the declare section, which has to list all the possible variable names you might have in input_params, and set them from new bind variables. You must know these already in the main block/procedure, either as local variables or more likely procedure arguments; but they all have the be duplicated here, since at this point you don't know which will be needed.

然后,该语句具有自己的内部动态SQL,这实际上是您最初正在执行的操作,但同时连接在input_params字符串和condition中.

Then that statement has its own inner dynamic SQL which is essentially what you were originally doing, but concatenates in the input_params string as well as condition.

这里重要的部分是报价.例如,在第一个引号中,:p_end_date:p_start_date都在第二个引号内,在q'^...^'内,因此它们绑定到内部动态SQL,并带有局部v_end_date和execute immediate的c12>.

The important part here is the quoting. In the first one, for example, both :p_end_date and :p_start_date are inside the second level of quotes, within the q'^...^', so they are bound for the inner dynamic SQL, with values from the local v_end_date and v_start_date from that inner execute immediate.

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

That entire generated block is executed with bind values for all the possible variable names, which provide values for the local variables (via v_start_date date := :v_start_date; etc.) while preserving data types; plus the output flag.

然后,该块仅使用相关的局部变量来执行其内部的execute immediate语句,这些局部变量现在具有绑定的值;并且输出标志仍然是外部execute immediate的绑定变量,因此外部块仍然可以看到其结果.

That block then executes its internal execute immediate statement using only the relevant local variables, which now have bound values; and the output flag which is still a bind variable from the outer execute immediate, so the outer block can still see its result.

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

You can see that the second generated statement uses a different condition and bind variables and values to the first, and the flag is evaluated based on the relevant condition and parameters in each case.

顺便说一句,您可以改为使用case表达式来删除对:o_flag的重复引用(这不是问题,但我觉得有些困惑):

Incidentally, you could remove the duplicate reference to :o_flag (which isn't a problem but I find slightly confusing) by using a case expression instead:

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

这篇关于将动态输入参数传递给“立即执行"的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-21 05:19