SQL列名称与PL/SQL变量名称相同-如何在select语句中完成此操作? [英] SQL column name same as PL/SQL variable name - How can this be done in a select statement?

问题描述

假设我有一张桌子:

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

然后我有以下代码

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;

当然，这将无法正常工作.无需更改变量名就可以使其工作吗?

解决方案

如果您对的定义足够宽松，则无需更改变量名即可..阅读神话般的 PL/SQL名称解析时说:

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

unit_name.simple_identifier_name

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

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;
/

变量名称未更改.相反，他们是嗯...更合格的:)

请注意以下内容无效:

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;
/

由于 解决方案

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.

这篇关于SQL列名称与PL/SQL变量名称相同-如何在select语句中完成此操作?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！