mysql exit handler_[MySQL] 存储过程错误异常处理例子 –> DECLARE EXIT HANDLER FOR SQLEXCEPTION | 学步园...

刚才一个朋友问到：

mysql  有类似 mssql 退出执行的方法不？

比如我执行到某个条件，下面就终止执行了。

想起以前写的存储过程，找了好久才找到，就发给他，希望对他有所帮助，贴在这里，留作纪念，也方便自己以后查找。

-- --------------------------------------------------------------------------------

-- Routine DDL

-- --------------------------------------------------------------------------------

DELIMITER $$

CREATE PROCEDURE xxx.proc_reuters_dss_equity_price(OUT p_result BOOLEAN)

BEGIN

-- 错误处理

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN

TRUNCATE TABLE reuters_interface.reuters_dss_equity_price;

SET p_result = FALSE;

END;

-- 处理数据

REPLACE INTO reuters_dss.equity_price (ric, company_name, currency_code, exchange_code, ticker, trade_date, ask_price, bid_price,

close_price, volume, last_updated_ask_price, last_updated_bid_price, last_updated_close_price, last_updated_date, last_updated_volume, split_factor, vwap_price,

insert_time)

SELECT ric, IF(company_name='', null, company_name), currency_code, exchange_code, ticker, trade_date, IF(ask_price='0.00000', null, ask_price),

IF(bid_price='0.00000', null, bid_price), close_price, IF(volume='0', null, volume), IF(last_updated_ask_price='0.00000', null, last_updated_ask_price),

IF(last_updated_bid_price='0.00000', null, last_updated_bid_price),

IF(last_updated_close_price='0.00000', null, last_updated_close_price),

last_updated_close_price_date,

IF(last_updated_volume='0', null, last_updated_volume),

IF(split_factor='', null, split_factor),

IF(vwap_price='0.00000', null, vwap_price),

insert_time

FROM reuters_interface.reuters_dss_equity_price;

COMMIT;

TRUNCATE TABLE reuters_interface.reuters_dss_equity_price;

SET p_result = TRUE;

COMMIT;

END