是否可以在Oracle SQL*plus脚本中以编程方式构造替代变量的名称？

Question

我正在尝试编写一个运行单个任意过程的sql*plus脚本。棘手的部分是，我希望能够运行该过程，而不管该过程需要多少参数。

为了找出该过程的正确参数数量，我执行以下操作：

  SELECT COUNT(*) INTO v_in
  FROM all_arguments
  WHERE LOWER(owner) = LOWER(v_schema) 
    AND LOWER(package_name) = LOWER(v_package)
    AND LOWER(object_name) = LOWER(v_proc)
    AND in_out = 'IN';

当需要构建execute-immediate字符串时，我想使用某种循环来实现。传入的所有参数都只是编号，从&1到&n。

  FOR i IN 1..v_in
  LOOP
    v_block := v_block || '''' || &i || '''';
  IF i != v_in THEN
    v_block := v_block || ',';
  END IF;
  END LOOP;

然而，这并不起作用。它看到& i，当然认为它是一个名为i的参数，并且由于调度应用程序(Appworx...ugh)没有运行define i=something，这是一个令人痛苦的失败。

在这种情况下，有没有办法做到间接性，这样我就可以遍历给定过程中恰好是正确的多少次？

Answer 1

您可以使用new_value和缺省的SQL参数来欺骗它。

创建一个这样的脚本，例如test.sql：

-- hide output
set termout off

-- define parameter variables to be set with new_value
col par1 new_value 1 noprint
col par2 new_value 2 noprint
col par3 new_value 3 noprint

-- initialize parameter variables
select 1 par1, 2 par2, 3 par3 from dual where 1=2;

-- append comma to parameter variables, not needed for first parameter
select nullif(','||'&2',',') par2, nullif(','||'&3',',') par3 from dual;

-- show output
set termout on

-- you actual script starts here
prompt calling procedure my_proc(&1 &2 &3)

-- for next run
undef 1
undef 2
undef 3

现在使用@test 3 4调用

输出：

calling procedure my_proc(3 ,4 )

或使用@test 1 2 3调用

输出：

calling procedure my_proc(1 ,2 ,3 )

现在，您需要将其扩展到参数的最大预期数量。

(请注意，您必须登录才能正常工作，否则select from dual将静默失败。)

Answer 2

您希望生成动态sql，但不知道将涉及多少参数。Asktom has the authoritative answer for this。不要纠结于"new“方法。它对你不起作用，因为你不知道会有多少个参数。

基本上，您将通过连接来创建动态sql，但是参数将存储在sys_context中，以便查询具有绑定。这有助于像SQL注入这样的事情作为奖励。