MySQL 存储过程的异常处理

来源：互联网发布：linux apache下载64位编辑：程序博客网时间：2024/05/01 15:24
[代码] SQL异常处理

view source
print?
001mysql>
002mysql> delimiter $$
003mysql>
004mysql> CREATEPROCEDURE myProc
005    ->     (p_first_name         VARCHAR(30),
006    -> p_last_nameVARCHAR(30),
007    ->       p_city               VARCHAR(30),
008    ->       p_description        VARCHAR(30),
009    ->      OUT p_sqlcode        INT,
010    ->      OUT p_status_message VARCHAR(100))
011    -> BEGIN
012    ->
013    -> /* STARTDeclare Conditions */
014    ->
015    ->   DECLARE duplicate_key CONDITIONFOR 1062;
016    ->   DECLARE foreign_key_violated CONDITIONFOR 1216;
017    ->
018    -> /*END DeclareConditions */
019    ->
020    -> /* STARTDeclare variablesand cursors */
021    ->
022    ->     DECLARE l_manager_id      INT;
023    ->
024    ->     DECLARE csr_mgr_idCURSOR FOR
025    ->      SELECT id
026    ->        FROM employee
027    ->       WHERE first_name=p_first_name
028    ->             AND last_name=p_last_name;
029    ->
030    -> /*END Declarevariables and cursors */
031    ->
032    -> /* STARTDeclare Exception Handlers */
033    ->
034    ->   DECLARE CONTINUEHANDLER FOR duplicate_key
035    ->    BEGIN
036    ->      SET p_sqlcode=1052;
037    ->      SET p_status_message='Duplicate key error';
038    ->    END;
039    ->
040    ->   DECLARE CONTINUEHANDLER FOR foreign_key_violated
041    ->    BEGIN
042    ->      SET p_sqlcode=1216;
043    ->      SET p_status_message='Foreign key violated';
044    ->    END;
045    ->
046    ->   DECLARE CONTINUEHANDLER FOR not FOUND
047    ->    BEGIN
048    ->      SET p_sqlcode=1329;
049    ->      SET p_status_message='No record found';
050    ->    END;
051    ->
052    -> /*END DeclareException Handlers */
053    ->
054    -> /* START Execution */
055    ->
056    ->   SET p_sqlcode=0;
057    ->   OPEN csr_mgr_id;
058    ->   FETCH csr_mgr_id INTO l_manager_id;
059    ->
060    ->   IF p_sqlcode<>0THEN           /* Failedto get manager id*/
061    ->    SET p_status_message=CONCAT(p_status_message,' when fetching manager id');
062    ->   ELSE
063    ->    INSERT INTOemployee (first_name,id,city)
064    ->    VALUES(p_first_name,l_manager_id,p_city);
065    ->
066    ->     IF p_sqlcode<>0THEN     /* Failedto insertnew department */
067    ->      SET p_status_message=CONCAT(p_status_message,
068    ->                           ' when inserting new department');
069    ->    END IF;
070    ->   END IF;
071    ->
072    ->   CLOSE csr_mgr_id;
073    ->
074    -> /*END Execution */
075    ->
076    -> END$$
077Query OK, 0 rowsaffected (0.02 sec)
078 
079mysql>
080mysql> delimiter ;
081mysql> set@myCode = 0;
082Query OK, 0 rowsaffected (0.00 sec)
083 
084mysql> set@myMessage = 0;
085Query OK, 0 rowsaffected (0.00 sec)
086 
087mysql>
088mysql> call myProc('Jason','Martin','New City','New Description',@myCode,@myMessage);
089Query OK, 1 row affected (0.00 sec)
090 
091mysql>
092mysql> select@myCode, @myMessage;
093+---------+------------+
094| @myCode | @myMessage |
095+---------+------------+
096| 0       | NULL      |
097+---------+------------+
0981 row inset (0.00 sec)
099 
100mysql>
101mysql> dropprocedure myProc;
102Query OK, 0 rowsaffected (0.00 sec)