PLSQL不規範的引數命名導致的問題

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.

add_bonus('u001','c001',2,45);

PL/SQL procedure successfully completed.

* from fi_bonus_user_amount;

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

add_bonus('u567','c001',1,1000);

PL/SQL procedure successfully completed.

* 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限制的變數