首页 > 未分类 > mysql procedure

mysql procedure

2016年5月19日 发表评论 阅读评论
DELIMITER //
 
CREATE PROCEDURE fix_trade_status(in var_supplier_trade_id varchar(32))
BEGIN
 
DECLARE var_prt_supplier_trade_id varchar(32) DEFAULT '';
DECLARE var_trade_id varchar(32) DEFAULT '';
DECLARE var_prt_trade_id varchar(32) DEFAULT '';
 
 
SELECT prt_trade_id, trade_id INTO var_prt_supplier_trade_id, var_trade_id FROM supplier_trade WHERE id = var_supplier_trade_id;
 
UPDATE supplier_trade SET status = 'closed', finished_type = 'refunded', fulfillment_status = 'finished', refunded_at = unix_timestamp(), closed_at = unix_timestamp(), success_at = unix_timestamp() WHERE id = var_supplier_trade_id AND has_refund = 1 AND order_number = (SELECT COUNT(id) FROM supplier_trade_item WHERE trade_id = var_supplier_trade_id AND refund_status = 'refunded');
 
if var_prt_supplier_trade_id != '' then
    SELECT prt_trade_id INTO var_prt_trade_id FROM prt_supplier_trade WHERE id = var_prt_supplier_trade_id;
 
    UPDATE prt_supplier_trade SET status = 'closed', finished_type = 'refunded', fulfillment_status = 'finished', refunded_at = unix_timestamp(), closed_at = unix_timestamp(), success_at = unix_timestamp() WHERE id = var_prt_supplier_trade_id AND has_refund = 1 AND order_number = (SELECT COUNT(id) FROM supplier_trade WHERE prt_trade_id = var_prt_supplier_trade_id AND status = 'closed');
end if;
 
if var_trade_id != '' then
    UPDATE trade SET status = 'closed', finished_type = 'refunded', fulfillment_status = 'finished', refunded_at = unix_timestamp(), closed_at = unix_timestamp(), success_at = unix_timestamp() WHERE id = var_supplier_trade_id AND has_refund = 1 AND order_number = (SELECT COUNT(id) FROM supplier_trade_item WHERE trade_id = var_supplier_trade_id AND refund_status = 'refunded');
end if;
 
 
if var_prt_trade_id != '' then
    UPDATE prt_trade SET status = 'closed', finished_type = 'refunded', fulfillment_status = 'finished', refunded_at = unix_timestamp(), closed_at = unix_timestamp(), success_at = unix_timestamp() WHERE id = var_prt_supplier_trade_id AND has_refund = 1 AND order_number = (SELECT COUNT(id) FROM trade WHERE prt_trade_id = var_prt_supplier_trade_id AND status = 'closed');
end if;
 
select var_prt_supplier_trade_id;
 
END
分类: 未分类 标签:
  1. 本文目前尚无任何评论.
  1. 本文目前尚无任何 trackbacks 和 pingbacks.