Name
EXC-01: Handle exceptions that cannot be avoided but can be anticipated
Synopsis
If you are writing a program in which you can predict that a certain error will occur, you should include a handler in your code for that error, allowing for a graceful and informative failure.
Example
This recommendation is easily demonstrated with a simple,
single-row lookup cursor. An error that often occurs is No data to FETCH, which indicates that
the cursor didn't identify any rows. Consider the following
function that returns the name of a department for its ID:
CREATE FUNCTION department_name(in_dept_id INT) RETURNS VARCHAR(30)
READS SQL DATA
BEGIN
DECLARE v_dept_name VARCHAR(30);
DECLARE dept_csr CURSOR FOR
SELECT department_name
FROM departments
WHERE department_id=in_dept_id;
OPEN dept_csr;
FETCH dept_csr INTO v_dept_name;
CLOSE dept_csr;
RETURN v_dept_name;
END;As currently coded, this function will raise the No data to FETCH error if an invalid
department ID is passed in.
mysql> SELECT department_name(1);
+--------------------+
| department_name(1) |
+--------------------+
| DUPLIN |
+--------------------+
1 row in set (0.00 sec)
mysql> SELECT department_name(60);
ERROR 1329 (02000): No data to FETCHThat may be fine for some scenarios, but in this particular
case, we simply want to return a special string (No such Department). The program that
calls department_name can then decide for itself if it wants or needs to raise an error or simply proceed. In this case, the solution is to add ...