DECLARE handler_action HANDLER
FOR condition_value [, condition_value] ...
statement;
handler_action: CONTINUE | EXIT | UNDO -- UNDO 暫不支持
condition_value:
mysql_error_code -- 數字錯誤碼,如 1062
| SQLSTATE [VALUE] sqlstate_value -- 5 字符標準碼,如 '23000'
| condition_name -- 自定義條件名
| SQLWARNING -- SQLSTATE 以 '01' 開頭
| NOT FOUND -- SQLSTATE 以 '02' 開頭
| SQLEXCEPTION -- 除 '00','01','02' 外的所有錯誤
| 動作 | 執行完 statement 後 | 默認適用場景 |
|---|---|---|
| CONTINUE | 繼續執行後續語句 | SQLWARNING、NOT FOUND |
| EXIT | 退出當前 BEGIN...END 塊 | SQLEXCEPTION |
| UNDO | 回滾並退出(暫不支持) | — |
SQLWARNING:警告類(如截斷、死鎖檢測),默認 忽略繼續NOT FOUND:游標無數據、SELECT...INTO 無結果,默認 忽略繼續SQLEXCEPTION:真正錯誤,默認 終止存儲過程-- 原始寫法
DECLARE CONTINUE HANDLER FOR 1216 SET @msg = 'FK error';
-- 推薦寫法
DECLARE foreign_key_error CONDITION FOR 1216;
DECLARE CONTINUE HANDLER FOR foreign_key_error SET @msg = 'FK error';
CREATE PROCEDURE sp_add_location (
IN in_location VARCHAR(30),
IN in_address1 VARCHAR(30),
IN in_address2 VARCHAR(30),
IN in_zipcode VARCHAR(10),
OUT out_status VARCHAR(30)
)
BEGIN
DECLARE CONTINUE HANDLER FOR 1062 -- Duplicate entry
SET out_status = 'Duplicate Entry';
SET out_status = 'OK';
INSERT INTO locations (location, address1, address2, zipcode)
VALUES (in_location, in_address1, in_address2, in_zipcode);
END;
插入重複主鍵時,不拋錯中斷,而是設置輸出參數繼續執行,調用方可根據 out_status 判斷結果。
CREATE PROCEDURE P_GET_CLASS_NAME(IN cid INT, OUT cname VARCHAR(50))
BEGIN
IF cid = 1 THEN SET cname = '一班';
ELSEIF cid = 2 THEN SET cname = '二班';
ELSE SET cname = '未知';
END IF;
END;
CREATE PROCEDURE P_INSERT_STUDENT(
IN sid INT, IN sname VARCHAR(10), IN classno INT, IN birth DATETIME
)
BEGIN
DECLARE classname VARCHAR(50) DEFAULT NULL;
CALL P_GET_CLASS_NAME(classno, classname);
SET @sql = 'INSERT INTO TBL_STUDENT VALUES(?,?,?,?)';
PREPARE stmt FROM @sql;
EXECUTE stmt USING sid, sname, classname, birth;
DEALLOCATE PREPARE stmt;
END;
DECLARE done INT DEFAULT FALSE;
DECLARE cur CURSOR FOR SELECT id FROM t;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur;
read_loop: LOOP
FETCH cur INTO @id;
IF done THEN LEAVE read_loop; END IF;
-- 處理 @id
END LOOP;
CLOSE cur;
SQLEXCEPTION 統一退出並記錄日誌CONTINUE 必須伴隨明確的業務狀態記錄(輸出參數、日誌表),否則難以排查ROLLBACK,因 UNDO 不支持