我想将变量参数传递给外部SQL文件(带有SQL * Plus的PL/SQL) [英] I want to pass a variable argument to external SQL file (PL/SQL with SQL*Plus)

问题描述

我已经在该论坛和Google上搜索了针对我的问题的答案，但找不到针对我的挑战的具体答案.这就是为什么我在这里要求它希望收到你们中一个人的答复.

I have search this forum and via google for an answer for my question, but I can't find a specific answer for my challenge. That is why I am asking it here to hope to receive an answer from one of you guys.

我想使用多个SQL文件，而一个SQL文件是控制文件，该控制文件执行带有参数的其他SQL文件. 该文件名为:startup.sql

I want to work with multiple SQL files, whereas one SQL file is the control file that executes the other SQL files with parameters. That file is called: startup.sql

我有一个包含所有值的表(不要介意列的名称，我在帖子中更改了它们).

I have a table with all my values (don't mind the names of the columns, I changed them for my post).

create table control (
  S varchar2(15) not null,
  N varchar2(25 char) not null, 
  B varchar2(25 char) not null, 
  Acheck varchar2(25 char) not null, 
  Adcheck varchar2(25) not null, 
  Blu varchar2(25) not null,
  ADB varchar2(25)
)

插入以下内容之一(有更多条目，但其中一个足以向您展示工作方式):

Where one of the following is inserted (there are more entries, but one is enough to show you the way of working):

    insert into control (S,N,B,Acheck,Adcheck,Blu,ADB)
       values('Test','B','J','J','N','N', '');

我的控制文件如下:

set escape on
set serveroutput on
SET DEFINE ON

declare
  cursor c_lees_control is
    select S, N, B, Acheck, Adcheck, Blu, ADB
  from control

  v_s           varchar2(30);
  v_b           varchar2(30);
  v_blu     varchar2(30);

begin

  for r_lees_control in c_lees_control
  loop
    v_s := r_lees_control.S;
    v_b := r_lees_control.B;
    v_blu := r_lees_control.Blu;

    if v_b = 'J' then
      --Also tried this.
      --@C:/Temp/uitvoer.sql $v_s $v_blu
      @C:/Temp/uitvoer.sql %v_s% %v_blu%
end if;
  end loop;
end;
/

在uitvoer.sql中，我有一个像这样的变量:

In my uitvoer.sql I have a variable like this:

    variable_s := '&&1';
    variable_blu := '&&2';

现在正在发生以下情况. 我启动SQL Plus(使用所有凭据)，然后启动控制文件(control.sql). 在SQL的输出中 Plus声明了以下内容:

Now the following is happening. I start SQLPlus (with all my credentials) and I start my control file (control.sql). In the output of SQLPlus the following is stated:

old 89:        s = '&&1';
new 89:        s = '%v_s%';
old 128:       b_lu := '&&2';
new 128:       b_lu := '%v_blu%';

我期望以下几点:

old 89:        s = '&&1';
new 89:        s = 'Test';
old 128:       b_lu := '&&2';
new 128:       b_lu := 'J';

为什么我的控制文件中的变量没有正确解析为新的SQL文件?

Why are my variables in the control file not parsed correctly to the new SQL file?

我还发现了以下帖子: 从批处理文件中启动PL/SQL脚本(带有参数) 看起来像是我的挑战，但我不是从批处理文件调用，而是从sql文件调用.

I also found the following posts: How do you pass an argument to a PL/SQL block within a sql file called using START in sqlplus? / Launch PL/SQL script from batch file (with arguments) It looks like my challenge, but I am not calling from an batchfile but from an sql file.

我希望有人能帮助我.如果不清楚，我可以尝试多解释一下.

I hope someone can help me. If something is unclear, I can try to explain it a bit more.

推荐答案

@是 list 缓冲区中的代码.您在控制块中声明的变量可直接用于包含"代码，而无需替换.

@ is a SQL*Plus command, it has no meaning in PL/SQL. Your script is being included within the PL/SQL block at parse time, which you can see if you list the code in the buffer. The variables declared in your control block are available to the 'included' code directly, without needing substitution.

例如，如果uitvoer.sql仅包含:

dbms_output.put_line(v_s);

然后此控制脚本:

set serveroutput on
declare
  v_s varchar2(10) := 'Test';
begin
  @uitvoer.sql
end;
/

list

产生:

Test

PL/SQL procedure successfully completed.

  1  declare
  2    v_s varchar2(10) := 'Test';
  3  begin
  4  dbms_output.put_line(v_s);
  5* end;

缓冲区中的PL/SQL块具有包含的代码，不是对uitvoer.sql的引用.但是其中包含的代码有效，因为它引用了控制脚本中仍在作用域内的变量.

The PL/SQL block in the buffer has the included code, not a reference to uitvoer.sql. But the included code worked because it referred to a variable from the control script which was still in-scope.

如果要允许使用具有不同名称的控制变量，或者允许更灵活地调用uitvoer.sql，那么您仍然可以使用替换变量，但是您仍将替换变量名称，而不是其值.例如，对于此uitvoer.sql(请注意，替换变量asgment在上周围没有引号):

If you want to allow for the control variables having different names, allowing uitvoer.sql to be called more flexibly perhaps, then you can still use substitution variables, but you're still substituting the variable name, not its value. For example, with this uitvoer.sql (note that the substitution variable assginment does not have quotes around it):

declare
  variable_s varchar2(10);
begin
  variable_s := &&1;
  dbms_output.put_line(variable_s);
end;

您的控制脚本传递了变量名:

And your control script passing the variable name:

declare
  v_s varchar2(10) := 'Test';
begin
  @uitvoer.sql v_s
end;
/

您看到的是

old   7:   variable_s := &&1;
new   7:   variable_s := v_s;
Test

PL/SQL procedure successfully completed.

  1  declare
  2    v_s varchar2(10) := 'Test';
  3  begin
  4  declare
  5    variable_s varchar2(10);
  6  begin
  7    variable_s := &&1;
  8    dbms_output.put_line(variable_s);
  9  end;
 10* end;

这篇关于我想将变量参数传递给外部SQL文件(带有SQL * Plus的PL/SQL)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！