SQL 列名称与 PL/SQL 变量名称相同 - 如何在选择语句中完成此操作?

假设我有一张桌子:

创建表 foo (col_1 号；col_2 号；);

然后我有以下代码

声明col_1 号；col_2 号；开始col_1 := 1;从 foo 中选择 col_2 到 col_2，其中 col_1 = col_1；结尾;

当然这不会按预期工作.如何在不需要更改变量名称的情况下使其工作?

解决方案

如果您对 无需更改变量名称"的定义足够自由，则可以..阅读精彩的 PL/SQL Name Resolution 说:

<块引用>

如果标识符是在命名的 PL/SQL 单元中声明的，您可以使用单元(块、子程序或包)的名称来限定其简单名称(其声明中的名称)，使用以下语法:

unit_name.simple_identifier_name

以下示例将按预期打印 20:

create table foo (a number, b number);插入 foo values(1, 10);插入 foo values(2, 20);插入 foo values(3, 30);开始<<条>>宣布一个号码;b号；开始一:= 2;select b into bar.b from foo where a = bar.a;dbms_output.put_line(b);结尾;结尾;/

变量名没有改变.相反，他们是嗯......更有资格:)

注意以下内容不起作用:

开始宣布一个号码;b号；开始一:= 2;select foo.b into b from foo where foo.a = a;dbms_output.put_line(b);结尾;结尾;/

由于 优先规则:

<块引用>

如果 SQL 语句引用的名称既属于列又属于局部变量或形参，则列名优先.

Suppose I have a table:

create table foo (
  col_1     number;
  col_2     number;
);

Then I have the following code

declare
   col_1    number;
   col_2    number;
begin
   col_1 := 1;
   select col_2 into col_2 from foo where col_1 = col_1;
end;

Of course this will not work as expected. How can one make it work without the need to change the variable names?

解决方案

You can if you're liberal enough of your definition of "without the need to change the variable names". Reading the fabulous PL/SQL Name Resolution says:

If an identifier is declared in a named PL/SQL unit, you can qualify its simple name (the name in its declaration) with the name of the unit (block, subprogram, or package), using this syntax:

unit_name.simple_identifier_name

The following example will print 20 as expected:

create table foo (a number, b number);

insert into foo values(1, 10);
insert into foo values(2, 20);
insert into foo values(3, 30);

begin
  <<bar>>
  declare
    a number;
    b number;
  begin
    a := 2;
    select b into bar.b from foo where a = bar.a;
    dbms_output.put_line(b);
  end;
end;
/

Variable names are not changed. Instead they are hmm ... more qualified :)

Note the following doesn't work:

begin
  declare
    a number;
    b number;
  begin
    a := 2;
    select foo.b into b from foo where foo.a = a;
    dbms_output.put_line(b);
  end;
end;
/

As the non-qualified a in the select-statement is interpreted as a column because of the precedence rules:

If a SQL statement references a name that belongs to both a column and either a local variable or formal parameter, then the column name takes precedence.