PLSQL不规范的参数命名导致的问题

PLSQL代码编写过程中,对标识符的命名一定要规范,不然会引发一些非常莫名的问题...[@more@]

我们先通过环境来引出问题:

有一个PROCEDURE用来更新积分表.

****************************************************************************************************

desc">heyf@VOUCHER>desc fi_bonus_user_amount;

Name Type
-------------------------- ------------------------------------
ID VARCHAR2(20)
CORPORATION_ID VARCHAR2(20) --公司ID
USER_ID VARCHAR2(20) --用户ID
BONUS NUMBER --积分

select">heyf@VOUCHER>select * from fi_bonus_user_amount;

ID CORPORATION_ID USER_ID BONUS
----------- -------------------- -------------- -------------
1 c001 u001 10
2 c001 u021 0

create">heyf@VOUCHER>create or replace procedure add_bonus(user_id in varchar2,
2 corp_id in varchar2,
3 bonus_points number,
4 v_bonus_score in number) is
5 v_user_number number(10);
6 BEGIN
7 select count(u.user_id)
8 into v_user_number
9 from fi_bonus_user_amount u
10 where u.user_id = user_id
11 and u.corporation_id = corp_id;
12
13 if v_user_number <> 0 then
14 update fi_bonus_user_amount user_amount
15 set user_amount.BONUS = user_amount.BONUS +
16 bonus_points * v_bonus_score
17 where user_amount.USER_ID = user_id
18 and user_amount.CORPORATION_ID = corp_id;
19 else
20 insert into fi_bonus_user_amount
21 (ID,
22 corporation_id,
23 user_id,
24 bonus )
25 values
26 (SEQ_FI_BONUS_USER_AMOUNT.NEXTVAL,
27 corp_id,
28 user_id,
29 bonus_points * v_bonus_score);
30 end if; commit;
31 END add_bonus;
32 /

Procedure created.

exec">heyf@VOUCHER>exec add_bonus('u001','c001',2,45);

PL/SQL procedure successfully completed.

select">heyf@VOUCHER>select * from fi_bonus_user_amount;

ID CORPORATION_ID USER_ID BONUS
-------------------- -------------------- -------------------- ----------
1 c001 u001 100
2 c001 u021 90

exec">heyf@VOUCHER>exec add_bonus('u567','c001',1,1000);

PL/SQL procedure successfully completed.

select">heyf@VOUCHER>select * from fi_bonus_user_amount;

ID CORPORATION_ID USER_ID BONUS
-------------------- -------------------- -------------------- ----------
1 c001 u001 1100
2 c001 u021 1090

****************************************************************************************************

看到这里不知道大家有没有发现问题.

第一个测试中,

U001用户应该加90分,结果是C001的两个用户的都加了90分.(错误)

第二个测试中,

U567 用户不存在,应该插入,并记1000分,结果是不但没有插入新数据,反而在c001公司下面的两个用户中都加上了1000分.(错误)

为什么会发生这种情况呢?

经过多次测试发现:
1,只要CORP_id在表中存在,不管输入什么样的USER_ID,该CORP下的所有USER的积分都会被更新;
2,只有当CORP_id在表中不存在时,新的USER_ID才有可能会被插入, 否则,会更新所有该CORP_ID下的USER的积分;
3,在逻辑上看也没有检查出问题的所在.

再仔细检查发现一个细节:
where user_amount.USER_ID = user_id

这里的判断条件中, 表达式右边的user_id在用户角度理解应该为从外面传入的参数:
procedure add_bonus(user_id in varchar2, ...)

但ORACLE不这么认为,
因为fi_bonus_user_amount也刚好有一个字段,并且也命名为:USER_ID
而在执行过程中,ORACLE对这个表达式的判断永远为真.所以才会出现以上的错误结果.

问题找到了,解决很简单:
对形式参数USER_ID重新命名:P_USER_ID,并更改存过SQL中的相关变量.

小结:
在这里我们也发现了编写PLSQL程序过程中,变量命名规范的重要性.
这么一点细节当中却隐藏着如此简单而又严重的逻辑问题.
下面是建议的命名方法：

变量名 意义
--------------------------
V_variablename 程序变量
E_exceptionName 自定义的异常标识
T_TypeName 自定义的类型
P_parameterName 存储过程、函数的参数变量
C_ContantName 用CONTANT限制的变量

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/703656/viewspace-1008846/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/703656/viewspace-1008846/