DROP PROCEDURE IF EXISTS delUserOrderDataByUserId;
DELIMITER $
CREATE PROCEDURE delUserOrderDataByUserId(IN pUserId BIGINT)
BEGIN
-- 依赖orderId来删除的相关表
DECLARE pOrderId BIGINT;
DECLARE s INT DEFAULT 0;
#声明游标
DECLARE orderIdList CURSOR FOR SELECT orderId FROM pais_orders WHERE userId = pUserId;
-- 声明当游标遍历完后将标志变量置成某个值
DECLARE CONTINUE HANDLER FOR NOT FOUND SET s=1;
OPEN orderIdList;
FETCH NEXT FROM orderIdList INTO pOrderId;
WHILE s<>1 DO
-- 支付相关表
DELETE FROM pais_service_goods WHERE orderId = pOrderId;
DELETE FROM pais_payment_settlements WHERE businessOrderId = pOrderId;
DELETE FROM pais_payment_refund WHERE businessOrderId = pOrderId;
-- 订单相关表
DELETE FROM pais_orders WHERE orderId = pOrderId;
DELETE FROM pais_order_services WHERE orderId = pOrderId;
DELETE FROM pais_order_refunds WHERE orderId = pOrderId;
DELETE FROM pais_order_goods WHERE orderId = pOrderId;
DELETE FROM pais_order_express WHERE orderId = pOrderId;
DELETE FROM pais_order_complains WHERE orderId = pOrderId;
DELETE FROM pais_order_complains WHERE orderId = pOrderId;
DELETE FROM pais_log_orders WHERE orderId = pOrderId;
-- 日志相关表
DELETE FROM pais_log_services WHERE orderId = pOrderId;
FETCH NEXT FROM orderIdList INTO pOrderId;
END WHILE;
CLOSE orderIdList;
END;
$
DELIMITER ;
DROP PROCEDURE IF EXISTS delUserPaymentDataByUserId;
DELIMITER $
CREATE PROCEDURE delUserPaymentDataByUserId(IN pUserId BIGINT)
BEGIN
-- 依赖orderId来删除的相关表
DECLARE pplatformOrderId BIGINT;
DECLARE s INT DEFAULT 0;
#声明游标
DECLARE platformOrderIds CURSOR FOR SELECT platformOrderId FROM pais_payment_platform_order WHERE userId = pUserId;
-- 声明当游标遍历完后将标志变量置成某个值
DECLARE CONTINUE HANDLER FOR NOT FOUND SET s=1;
OPEN platformOrderIds;
FETCH NEXT FROM platformOrderIds INTO pplatformOrderId;
WHILE s<>1 DO
-- 支付相关表
DELETE FROM pais_payment_refund_exception WHERE platformOrderId = pplatformOrderId;
DELETE FROM pais_payment_message_log_body WHERE platformOrderId = pplatformOrderId;
DELETE FROM pais_payment_message_log WHERE platformOrderId = pplatformOrderId;
FETCH NEXT FROM platformOrderIds INTO pplatformOrderId;
END WHILE;
CLOSE platformOrderIds;
END;
$
DELIMITER ;
DROP PROCEDURE IF EXISTS delUserBaseDataByUserId;
DELIMITER $
CREATE PROCEDURE delUserBaseDataByUserId(IN pUserId BIGINT)
BEGIN
-- 直接基于userId删除的相关表
DECLARE resultCode INT DEFAULT -1;
SET resultCode = -1;
SELECT userType INTO resultCode FROM pais_users WHERE userId = pUserId;
-- 用户相关表数据删除
DELETE FROM pais_users WHERE userId = pUserId;
DELETE FROM pais_users_link_path WHERE userId = pUserId;
DELETE FROM pais_user_address WHERE userId = pUserId;
DELETE FROM pais_user_moneys WHERE userId = pUserId;
DELETE FROM pais_users_update_record WHERE userId =pUserId;
DELETE FROM pais_weapp_session WHERE userId = pUserId;
DELETE FROM pais_wx_users WHERE userId = pUserId;
DELETE FROM pais_third_users WHERE userId = pUserId;
DELETE FROM pais_team_users_report WHERE userId = pUserId;
DELETE FROM pais_stat_cash_commission WHERE userId = pUserId;
DELETE FROM pais_team_users_report WHERE userId = pUserId;
-- 支付相关表数据
DELETE FROM pais_user_moneys WHERE userId = pUserId;
DELETE FROM pais_payment_platform_order WHERE userId = pUserId;
DELETE FROM pais_payment_platform_sub_order WHERE userId = pUserId;
-- 订单相关表数据删除
DELETE FROM pais_order_user WHERE userId = pUserId;
-- log相关表数据删除
DELETE FROM pais_log_user_share WHERE userId = pUserId;
DELETE FROM pais_log_user_register WHERE userId = pUserId;
DELETE FROM pais_log_user_logins WHERE userId = pUserId;
DELETE FROM pais_log_pays WHERE userId = pUserId;
DELETE FROM pais_log_pay_params WHERE userId = pUserId;
DELETE FROM pais_log_moneys WHERE targetId = pUserId;
-- 其他表 购物车表
DELETE FROM pais_invoices WHERE userId = pUserId;
DELETE FROM pais_carts WHERE userId = pUserId;
END;
$
DELIMITER ;
DROP PROCEDURE IF EXISTS delCommonUserId;
DELIMITER $
CREATE PROCEDURE delCommonUserId(IN pUserId BIGINT)
BEGIN
CALL delUserOrderDataByUserId(pUserId);
CALL delUserPaymentDataByUserId(pUserId);
CALL delUserBaseDataByUserId(pUserId);
END;
$
DELIMITER ;
CALL delUserBaseDataByUserId(57);
存储过程demo-(base mysql)
最新推荐文章于 2021-02-18 01:17:30 发布