Oracle
ORA-01422當我用數字參數呼叫過程時發生錯誤
我想編寫更新小於 2000 的薪水的 PL/SQL 過程。我編寫了這個過程。當我用整數 id ‘ORA-01422:exact fetch return more than requested number of rows’ 呼叫它時,TOAD 拋出錯誤. 我的程序如下:
DECLARE PROCEDURE update_salary(ID customers.id%type) is c_sal customers.salary%type; BEGIN SELECT salary INTO c_sal FROM customers WHERE id = ID; dbms_output.put_line ('Before update operation salary is:' || c_sal); --dbms_output.put_line ('Parameter :' || ID); IF (c_sal <= 2000) THEN UPDATE customers SET salary = salary + 1000 WHERE id = ID; dbms_output.put_line ('Salary updated'); END IF; SELECT salary INTO c_sal FROM customers WHERE id=ID; dbms_output.put_line ('After update operation salary is:' || c_sal); END; BEGIN update_salary(1); END; /
我用 dbms_output 列印參數 id。參數正確。我該如何解決這個錯誤!
以與命名列相同的方式命名參數是非常不幸的。
SELECT salary INTO c_sal FROM customers WHERE id = ID;
在上面的語句中,輸入
id
小寫或ID
大寫沒有區別,它不區分大小寫。僅僅因為您命名了您的 PL/SQL 參數ID
(大寫),並不意味著當您ID
在上面鍵入(大寫)時數據庫將使用它。即使id
incustomers
具有唯一值,上面也會SELECT
返回表中的所有 * 行,因為id
列在ID
參數之前,並且id
總是 * 等於ID
(* 除外NULLs
)。您可以簡單地為您的參數使用不同的名稱,例如我通常
p_
為它們使用前綴,因此您的ID
參數可以更改為P_ID
:DECLARE PROCEDURE update_salary(P_ID customers.id%type) is c_sal customers.salary%type; BEGIN SELECT salary INTO c_sal FROM customers WHERE id = P_ID; dbms_output.put_line ('Before update operation salary is:' || c_sal); --dbms_output.put_line ('Parameter :' || ID); SELECT salary INTO c_sal FROM customers WHERE id = P_ID; IF (c_sal <= 2000) THEN UPDATE customers SET salary = salary + 1000 WHERE id = P_ID; dbms_output.put_line ('Salary updated'); END IF; SELECT salary INTO c_sal FROM customers WHERE id=P_ID; dbms_output.put_line ('After update operation salary is:' || c_sal); END; BEGIN update_salary(1); END; /