PL/SQL & SQL CODING GUIDELINE 56 - Avoid unhandled exceptions

條款五十六,避免未處理的例外。


像是下面這段程式,當沒資料或是資料過多時 Select into 就會丟出例外。

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE OR REPLACE PACKAGE BODY department_api IS 
FUNCTION name_by_id (in_id IN departments.department_id%TYPE)
RETURN departments.department_name%TYPE IS
l_department_name departments.department_name%TYPE;
BEGIN
SELECT department_name
INTO l_department_name
FROM departments
WHERE department_id = in_id;

RETURN l_department_name;
END name_by_id;
END department_api;


建議是要確保程式不會有未處理的例外,像是上面這樣的程式就要加處理 NO_DATA_FOUND 與 TOO_MANY_ROWS。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CREATE OR REPLACE PACKAGE BODY department_api IS 
FUNCTION name_by_id (in_id IN departments.department_id%TYPE)
RETURN departments.department_name%TYPE IS
l_department_name departments.department_name%TYPE;
BEGIN
SELECT department_name
INTO l_department_name
FROM departments
WHERE department_id = in_id;

RETURN l_department_name;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RETURN NULL;
WHEN TOO_MANY_ROWS
THEN
RAISE;
END name_by_id;
END department_api;