将动态输入参数传递给“立即执行" [英] Passing dynamic input parameters to 'execute Immediate'
问题描述
我有一张表，其中存储某些条件以及输入参数，如下所示:
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;
/
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;]';
这篇关于将动态输入参数传递给“立即执行"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
