在sql查询中调用方式语句：call deleteTenantAllData(id);
以下存储过程可放在MySQL数据库的函数下即可：
CREATE DEFINER=`root`@`%` PROCEDURE `deleteTenantAllData`( p_tenant_id BIGINT )
BEGIN
DECLARE
t_error INTEGER DEFAULT 0;
DECLARE
CONTINUE HANDLER FOR SQLEXCEPTION
SET t_error = 1;
-- 异常时设置为1
START TRANSACTION;
-- ----------------------------
-- delete ccr_category
-- ----------------------------
DELETE
FROM
ccr_category
WHERE
tenant_id = p_tenant_id;
-- ----------------------------
-- delete ccr_course
-- ----------------------------
DELETE
FROM
ccr_course
WHERE
tenant_id = p_tenant_id;
-- ----------------------------
-- delete ccr_course_count
-- ----------------------------
DELETE
FROM
ccr_course_count
WHERE
tenant_id = p_tenant_id;
-- ----------------------------
-- delete ccr_course_to_task
-- ----------------------------
DELETE
FROM
ccr_course_to_task
WHERE
tenant_id = p_tenant_id;
-- ----------------------------
-- delete ccr_course_to_tenant_category
-- ----------------------------
DELETE
FROM
ccr_course_to_tenant_category
WHERE
tenant_id = p_tenant_id;
-- ----------------------------
-- delete ccr_favorite_course
-- ----------------------------
DELETE
FROM
ccr_favorite_course
WHERE
tenant_id = p_tenant_id;
-- ----------------------------
-- delete ccr_notice
-- ----------------------------
DELETE
FROM
ccr_notice
WHERE
tenant_id = p_tenant_id;
-- ----------------------------
-- delete ccr_notice_user
-- ----------------------------
DELETE
FROM
ccr_notice_user
WHERE
tenant_id = p_tenant_id;
-- ----------------------------
-- delete ccr_study_duration
-- ----------------------------
DELETE
FROM
ccr_study_duration
WHERE
tenant_id = p_tenant_id;
-- ----------------------------
-- delete ccr_study_duration_average
-- ----------------------------
DELETE
FROM
ccr_study_duration_average
WHERE
tenant_id = p_tenant_id;
-- ----------------------------
-- delete ccr_study_duration_day
-- ----------------------------
DELETE
FROM
ccr_study_duration_day
WHERE
tenant_id = p_tenant_id;
-- ----------------------------
-- delete ccr_study_duration_month
-- ----------------------------
DELETE
FROM
ccr_study_duration_month
WHERE
tenant_id = p_tenant_id;
-- ----------------------------
-- delete ccr_study_duration_total
-- ----------------------------
DELETE
FROM
ccr_study_duration_total
WHERE
tenant_id = p_tenant_id;
-- ----------------------------
-- delete ccr_study_task
-- ----------------------------
DELETE
FROM
ccr_study_task
WHERE
tenant_id = p_tenant_id;
-- ----------------------------
-- delete ccr_tenant_point_config
-- ----------------------------
DELETE
FROM
ccr_tenant_point_config
WHERE
tenant_id = p_tenant_id;
-- ----------------------------
-- delete ccr_user_course_progress
-- ----------------------------
DELETE
FROM
ccr_user_course_progress
WHERE
tenant_id = p_tenant_id;
-- ----------------------------
-- delete ccr_user_point
-- ----------------------------
DELETE
FROM
ccr_user_point
WHERE
tenant_id = p_tenant_id;
-- ----------------------------
-- delete ccr_user_task_progress
-- ----------------------------
DELETE
FROM
ccr_user_task_progress
WHERE
tenant_id = p_tenant_id;
-- ----------------------------
-- delete ccr_user_task_statistics
-- ----------------------------
DELETE
FROM
ccr_user_task_statistics
WHERE
tenant_id = p_tenant_id;
-- ----------------------------
-- delete ccr_user_to_task
-- ----------------------------
DELETE
FROM
ccr_user_to_task
WHERE
tenant_id = p_tenant_id;
-- ----------------------------
-- delete ccr_user_video_progress
-- ----------------------------
DELETE
FROM
ccr_user_video_progress
WHERE
tenant_id = p_tenant_id;
-- ----------------------------
-- delete ccr_video
-- ----------------------------
DELETE
FROM
ccr_video
WHERE
tenant_id = p_tenant_id;
-- ----------------------------
-- delete ccr_video_to_course
-- ----------------------------
DELETE
FROM
ccr_video_to_course
WHERE
tenant_id = p_tenant_id;
-- ----------------------------
-- delete tb_dept
-- ----------------------------
DELETE
FROM
tb_dept
WHERE
tenant_id = p_tenant_id;
-- ----------------------------
-- delete tb_operation_log_admin
-- ----------------------------
DELETE
FROM
tb_operation_log_admin
WHERE
tenant_id = p_tenant_id;
-- ----------------------------
-- delete tb_operation_log_tenant
-- ----------------------------
DELETE
FROM
tb_operation_log_tenant
WHERE
tenant_id = p_tenant_id;
-- ----------------------------
-- delete tb_role
-- ----------------------------
DELETE
FROM
tb_role
WHERE
tenant_id = p_tenant_id;
-- ----------------------------
-- delete tb_tenant_app_history
-- ----------------------------
DELETE
FROM
tb_tenant_app_history
WHERE
tenant_id = p_tenant_id;
-- ----------------------------
-- delete tb_tenant_to_app
-- ----------------------------
DELETE
FROM
tb_tenant_to_app
WHERE
tenant_id = p_tenant_id;
-- ----------------------------
-- delete tb_user_to_role
-- ----------------------------
DELETE tb_user_to_role
FROM
tb_user_to_role,
tb_user
WHERE
tb_user_to_role.user_id = tb_user.id
AND tb_user.tenant_id = p_tenant_id;
-- ----------------------------
-- delete tb_user
-- ----------------------------
DELETE
FROM
tb_user
WHERE
tenant_id = p_tenant_id;
-- ----------------------------
-- delete ccr_document
-- ----------------------------
DELETE
FROM
ccr_document
WHERE
tenant_id = p_tenant_id;
-- ----------------------------
-- delete ccr_document_to_course
-- ----------------------------
DELETE
FROM
ccr_document_to_course
WHERE
tenant_id = p_tenant_id;
-- ----------------------------
-- delete ccr_paper_to_task
-- ----------------------------
DELETE
FROM
ccr_paper_to_task
WHERE
tenant_id = p_tenant_id;
-- ----------------------------
-- delete ccr_question
-- ----------------------------
DELETE
FROM
ccr_question
WHERE
tenant_id = p_tenant_id;
-- ----------------------------
-- delete ccr_question_group
-- ----------------------------
DELETE
FROM
ccr_question_group
WHERE
tenant_id = p_tenant_id;
-- ----------------------------
-- delete ccr_question_instance
-- ----------------------------
DELETE
FROM
ccr_question_instance
WHERE
tenant_id = p_tenant_id;
-- ----------------------------
-- delete ccr_question_paper
-- ----------------------------
DELETE
FROM
ccr_question_paper
WHERE
tenant_id = p_tenant_id;
-- ----------------------------
-- delete ccr_question_paper_instance
-- ----------------------------
DELETE
FROM
ccr_question_paper_instance
WHERE
tenant_id = p_tenant_id;
-- ----------------------------
-- delete ccr_question_to_question_group
-- ----------------------------
DELETE
FROM
ccr_question_to_question_group
WHERE
tenant_id = p_tenant_id;
-- ----------------------------
-- delete ccr_user_document_progress
-- ----------------------------
DELETE
FROM
ccr_user_document_progress
WHERE
tenant_id = p_tenant_id;
-- ----------------------------
-- delete ccr_user_to_live_broadcast
-- ----------------------------
DELETE
FROM
ccr_user_to_live_broadcast
WHERE
tenant_id = p_tenant_id;
-- ----------------------------
-- delete ccr_live_broadcast
-- ----------------------------
DELETE
FROM
ccr_live_broadcast
WHERE
tenant_id = p_tenant_id;
-- ----------------------------
-- delete tb_tenant
-- ----------------------------
DELETE
FROM
tb_tenant
WHERE
id = p_tenant_id;
IF
t_error = 1 THEN
BEGIN
SELECT
'删除失败';
ROLLBACK;
END;
ELSE BEGIN
SELECT
'删除成功';
COMMIT;
END;
END IF;
END