oracle pl/sql 存储过程：参数名与列名相同
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1607891/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
pl/sql stored procedure: parameter name same as column name
提问by gsharp
I have a Stored Procedure like this
我有一个这样的存储过程
procedure P_IssueUpdate
(
Id in integer,
ModifiedDate in date,
Solution in varchar2
) AS
BEGIN
update T_Issue
Set
ModifiedDate = ModifiedDate,
Solution = Solution
where id = id;
END P_IssueUpdate;
my problem is that the parameter name is the same name as the Table column name. Is there a way to instruct the sql that the value after the "=" should be the parameter and not the column?
我的问题是参数名称与表列名称相同。有没有办法指示sql“=”后面的值应该是参数而不是列？
Thanks for your help
谢谢你的帮助
回答by Tony Andrews
You can prefix parameter and variable names with the name of the procedure like this:
您可以使用过程名称作为参数和变量名称的前缀，如下所示：
SQL> declare
2 procedure p (empno number) is
3 ename varchar2(10);
4 begin
5 select ename
6 into p.ename
7 from emp
8 where empno = p.empno;
9 dbms_output.put_line(p.ename);
10 end;
11 begin
12 p (7839);
13 end;
14 /
KING
PL/SQL procedure successfully completed.
回答by Vincent Malgrat
what you described is called variable shadowing. It can happen in any language. You were given good workarounds but the common solution is to design a naming scheme so that it will never happen.
您所描述的称为变量阴影。它可以发生在任何语言中。您得到了很好的解决方法，但常见的解决方案是设计一个命名方案，使其永远不会发生。
For example, name your columns without prefix and have your variables with a prefix that depends upon their scope (P_for parameters, L_for local variables, G_for global package variables, etc...). This will have the added benefit of making the code more readable by giving you additional information.
例如，为您的列命名不带前缀，并为您的变量添加一个取决于其范围的前缀（P_对于参数、L_局部变量、G_全局包变量等）。通过为您提供附加信息，这将具有使代码更具可读性的额外好处。
回答by gsharp
i found a solution. it's working by full qualifying the parameter:
我找到了解决方案。它通过完全限定参数来工作：
procedure P_IssueUpdate
(
Id in integer,
ModifiedDate in date,
Solution in varchar2
) AS
BEGIN
update T_Issue
Set
ModifiedDate = P_IssueUpdate.ModifiedDate,
Solution = P_IssueUpdate.Solution
where id = P_IssueUpdate.id;
END P_IssueUpdate;
回答by Jim
RE Vincent's answer about prepending a prefix--that solution works until somebody modifies the table and adds a column whose name happens to collide with the parameter name. Not everybody goes through every line of code to make sure their table modifications won't conflict with variable or parameter names. Oracle's recommendation is to qualify every parameter or variable name in a SQL query.
RE Vincent's answer about prepending a prefix - 该解决方案一直有效，直到有人修改表并添加一个名称碰巧与参数名称冲突的列。不是每个人都会检查每一行代码以确保他们的表修改不会与变量或参数名称冲突。Oracle 的建议是限定 SQL 查询中的每个参数或变量名称。
If you're working with an anonymous block (outside a procedure), you can name the block and qualify variables that way:
如果您正在使用匿名块（在过程之外），您可以这样命名块并限定变量：
<<MY_BLOCK>>
declare
X sys.USER_TABLES%rowtype;
Y sys.USER_TABLES.TABLE_NAME%type := 'some_table_name';
begin
select UT.*
into MY_BLOCK.X
from sys.USER_TABLES UT
where UT.TABLE_NAME = MY_BLOCK.Y;
end MY_BLOCK;
