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.
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…