mysql创建存储过程

需求：定时清理过期用户
清理过期7天的学校连同学校的学生及产生的数据

CREATE  PROCEDURE `delete_data`()
BEGIN
declare delete_college_count decimal(7,2);
-- 查询已过期7天的学校 
        select COUNT(1) into  delete_college_count from t_app_school where  spare5 = 2 and UNIX_TIMESTAMP(date_add(spare1,interval 7 day))< UNIX_TIMESTAMP(NOW()) ;
				
IF delete_college_count>0 THEN  			
				
SET @cur_time= UNIX_TIMESTAMP(NOW()) ;

SET @school_ids=CONCAT('SELECT cuid FROM t_app_school where spare5 = 2 and UNIX_TIMESTAMP(date_add(spare1,interval 7 day))< ', @cur_time );  



SET @user_ids=CONCAT('SELECT cuid FROM t_app_user where  college in ','(',@school_ids,')');  

SET @examinee_ids=CONCAT('SELECT cuid FROM t_app_examinee where  user_cuid in ','(',@user_ids,')'); 

SET @exam_nos=CONCAT('SELECT exam_no FROM t_app_examinee where  user_cuid in ','(',@user_ids,')');

-- 删除考生相关数据

SET @delete_t_app_examscore_formal=CONCAT('delete from t_app_examscore_formal where  user_cuid in ','(',@examinee_ids,')');  

SET @delete_t_c_answerrecord_every_setup=CONCAT('delete from t_c_answerrecord_every_setup where  create_user in ','(',@examinee_ids,')');  

SET @delete_t_c_answerrecord_formal=CONCAT('delete from t_c_answerrecord_formal where  create_user in ','(',@examinee_ids,')'); 

SET @delete_t_c_answerrecord_high_level_formal=CONCAT('delete from t_c_answerrecord_high_level_formal where  create_user in ','(',@examinee_ids,')'); 

SET @delete_t_c_answerrecord_interval_setup=CONCAT('delete from t_c_answerrecord_interval_setup where  exam_no in ','(',@exam_nos,')'); 

SET @delete_t_c_answerresult_formal=CONCAT('delete from t_c_answerresult_formal where  create_user in ','(',@examinee_ids,')'); 

SET @delete_t_c_answerresult_high_level_formal=CONCAT('delete from t_c_answerresult_high_level_formal where  create_user in ','(',@examinee_ids,')'); 



SET @delete_t_q_exam_notes_formal=CONCAT('delete from t_q_exam_notes_formal where  create_user in ','(',@examinee_ids,')'); 



SET @delete_t_q_exam_notes_formal_record_setup=CONCAT('delete from t_q_exam_notes_formal_record_setup where  create_user in ','(',@examinee_ids,')'); 

SET @t_app_examinee=CONCAT('delete from t_app_examinee where  cuid in  (SELECT w.cuid from (
SELECT cuid FROM t_app_examinee where  user_cuid in ','(',@user_ids,')
)w)'); 

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

-- 用户相关数据
SET @t_app_student=CONCAT('delete from t_app_student where  user_cuid in ','(',@user_ids,')'); 
SET @t_app_comment=CONCAT('delete from t_app_comment where  user_id in ','(',@user_ids,')'); 
SET @t_app_examlog=CONCAT('delete from t_app_examlog where  user_cuid in ','(',@user_ids,')'); 
SET @t_app_examscore=CONCAT('delete from t_app_examscore where  user_cuid in ','(',@user_ids,')'); 
SET @t_app_excess_exam_log=CONCAT('delete from t_app_excess_exam_log where  user_cuid in ','(',@user_ids,')'); 
SET @t_app_learnrecord=CONCAT('delete from t_app_learnrecord where  user_cuid in ','(',@user_ids,')'); 
SET @t_app_paperlog=CONCAT('delete from t_app_paperlog where  user_cuid in ','(',@user_ids,')'); 
SET @t_app_sharedownload=CONCAT('delete from t_app_sharedownload where  user_cuid in ','(',@user_ids,')'); 
SET @t_app_simulation_operate_log=CONCAT('delete from t_app_simulation_operate_log where  user_cuid in ','(',@user_ids,')'); 
SET @t_app_studentlearn=CONCAT('delete from t_app_studentlearn where  user_cuid in ','(',@user_ids,')'); 
SET @t_app_userlogin=CONCAT('delete from t_app_userlogin where  user_cuid in ','(',@user_ids,')'); 
SET @t_c_answerrecord=CONCAT('delete from t_c_answerrecord where  create_user in ','(',@user_ids,')'); 
SET @t_c_answerrecord_high_level=CONCAT('delete from t_c_answerrecord_high_level where  create_user in ','(',@user_ids,')'); 
SET @t_c_answerresult=CONCAT('delete from t_c_answerresult where  create_user in ','(',@user_ids,')'); 
SET @t_c_answerresult_high_level=CONCAT('delete from t_c_answerresult_high_level where  create_user in ','(',@user_ids,')'); 
SET @t_p_appraisal_user_score=CONCAT('delete from t_p_appraisal_user_score where  user_cuid in ','(',@user_ids,')'); 
SET @t_p_exam_record=CONCAT('delete from t_p_exam_record where  user_cuid in ','(',@user_ids,')'); 
SET @t_p_jobs_statistics=CONCAT('delete from t_p_jobs_statistics where  user_cuid in ','(',@user_ids,')'); 
SET @t_p_major_statistics=CONCAT('delete from t_p_major_statistics where  user_cuid in ','(',@user_ids,')'); 
SET @t_p_resource_history_learnrecord=CONCAT('delete from t_p_resource_history_learnrecord where  user_cuid in ','(',@user_ids,')'); 
SET @t_p_resource_learnrecord=CONCAT('delete from t_p_resource_learnrecord where  user_cuid in ','(',@user_ids,')'); 
SET @t_p_resource_month_statistics=CONCAT('delete from t_p_resource_month_statistics where  user_cuid in ','(',@user_ids,')'); 
SET @t_p_resource_questionrecord=CONCAT('delete from t_p_resource_questionrecord where  user_cuid in ','(',@user_ids,')'); 
SET @t_p_sign_record=CONCAT('delete from t_p_sign_record where  create_user in ','(',@user_ids,')'); 
SET @t_p_sign_score=CONCAT('delete from t_p_sign_score where  user_cuid in ','(',@user_ids,')'); 
SET @t_p_skill_statistics=CONCAT('delete from t_p_skill_statistics where  user_cuid in ','(',@user_ids,')'); 
SET @t_p_user_certificate=CONCAT('delete from t_p_user_certificate where  user_cuid in ','(',@user_ids,')'); 
SET @t_q_collection=CONCAT('delete from t_q_collection where  user_cuid in ','(',@user_ids,')'); 
SET @t_q_practicenotes=CONCAT('delete from t_q_practicenotes where  create_user in ','(',@user_ids,')'); 
SET @t_q_question_mark=CONCAT('delete from t_q_question_mark where  user_cuid in ','(',@user_ids,')'); 
SET @t_q_timerecord=CONCAT('delete from t_q_timerecord where  user_cuid in ','(',@user_ids,')');

SET @t_app_user=CONCAT('delete from t_app_user where  cuid in  (SELECT w.cuid from (
SELECT cuid FROM t_app_user where  college in ','(',@school_ids,')
)w)'); 
-- -----------------------------------------------------------------------------------------------------

--  删除学校相关数据
SET @t_app_exam=CONCAT('delete from t_app_exam where  user_ccode in ','(',@school_ids,')'); 
SET @t_app_college_studentclass=CONCAT('delete from t_app_college_studentclass where  college in ','(',@school_ids,')'); 
SET @t_app_college_class=CONCAT('delete from t_app_college_class where  college_cuid in ','(',@school_ids,')'); 
SET @t_app_college_comment=CONCAT('delete from t_app_college_comment where  college in ','(',@school_ids,')'); 
SET @t_app_college_course=CONCAT('delete from t_app_college_course where  college in ','(',@school_ids,')'); 
SET @t_app_college_course_resource=CONCAT('delete from t_app_college_course_resource where  college in ','(',@school_ids,')'); 
SET @t_app_college_learnrecord=CONCAT('delete from t_app_college_learnrecord where  college in ','(',@school_ids,')'); 
SET @t_app_college_menu_config=CONCAT('delete from t_app_college_menu_config where  college_cuid in ','(',@school_ids,')'); 
SET @t_app_college_share=CONCAT('delete from t_app_college_share where  college in ','(',@school_ids,')'); 
SET @t_app_college_share_downlaod=CONCAT('delete from t_app_college_share_downlaod where  college in ','(',@school_ids,')'); 
SET @t_app_college_sharecomment=CONCAT('delete from t_app_college_sharecomment where  college in ','(',@school_ids,')'); 
SET @t_app_college_simulation=CONCAT('delete from t_app_college_simulation where  college in ','(',@school_ids,')'); 
SET @t_app_college_specialtytype=CONCAT('delete from t_app_college_specialtytype where  college_cuid in ','(',@school_ids,')'); 
SET @t_app_exampaper=CONCAT('delete from t_app_exampaper where  user_ccode in ','(',@school_ids,')'); 
SET @t_app_examquestion=CONCAT('delete from t_app_examquestion where  user_ccode in ','(',@school_ids,')'); 
SET @t_c_project_college=CONCAT('delete from t_c_project_college where  college in ','(',@school_ids,')'); 
SET @t_p_appraisal=CONCAT('delete from t_p_appraisal where  school_cuid in ','(',@school_ids,')'); 
SET @t_p_chapter_college=CONCAT('delete from t_p_chapter_college where  college in ','(',@school_ids,')'); 
SET @t_p_chapter_resource_college=CONCAT('delete from t_p_chapter_resource_college where  college in ','(',@school_ids,')'); 
SET @t_p_course=CONCAT('delete from t_p_course where  school_cuid in ','(',@school_ids,')'); 
SET @t_p_course_college=CONCAT('delete from t_p_course_college where  college in ','(',@school_ids,')'); 
SET @t_q_classes=CONCAT('delete from t_q_classes where  college in ','(',@school_ids,')'); 
SET @t_q_classandquestion=CONCAT('delete from t_q_classandquestion where  college in ','(',@school_ids,')'); 
SET @t_q_questionbank_college=CONCAT('delete from t_q_questionbank_college where  college in ','(',@school_ids,')'); 
SET @t_s_examinfo=CONCAT('delete from t_s_examinfo where  college_cuid in ','(',@school_ids,')'); 
SET @t_sys_college_config=CONCAT('delete from t_sys_college_config where  college in ','(',@school_ids,')'); 

SET @t_sys_user=CONCAT('delete from t_sys_user where  spare4 in ','(',@school_ids,')');

SET @t_app_school=CONCAT('delete from t_app_school where  cuid in  (SELECT w.cuid from (
SELECT cuid FROM t_app_school where spare5 = 2 and UNIX_TIMESTAMP(date_add(spare1,interval 7 day))< ', @cur_time ,') w)');

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

PREPARE school_ids from @school_ids;    
EXECUTE school_ids;
DEALLOCATE PREPARE school_ids;  


PREPARE user_ids from @user_ids;    
EXECUTE user_ids;
DEALLOCATE PREPARE user_ids;  


PREPARE exam_nos from @exam_nos;    
EXECUTE exam_nos;
DEALLOCATE PREPARE exam_nos;  

PREPARE examinee_ids from @examinee_ids;    
EXECUTE examinee_ids;
DEALLOCATE PREPARE examinee_ids;  

-- 删除考生相关数据

prepare delete_talbe_data from @delete_t_app_examscore_formal;   
execute delete_talbe_data; 
deallocate prepare delete_talbe_data;

prepare delete_talbe_data from @delete_t_c_answerrecord_every_setup;   
execute delete_talbe_data; 
deallocate prepare delete_talbe_data;

prepare delete_talbe_data from @delete_t_c_answerrecord_formal;   
execute delete_talbe_data; 
deallocate prepare delete_talbe_data;

prepare delete_talbe_data from @delete_t_c_answerrecord_high_level_formal;   
execute delete_talbe_data; 
deallocate prepare delete_talbe_data;

prepare delete_talbe_data from @delete_t_c_answerrecord_interval_setup;   
execute delete_talbe_data; 
deallocate prepare delete_talbe_data;

prepare delete_talbe_data from @delete_t_c_answerresult_formal;   
execute delete_talbe_data; 
deallocate prepare delete_talbe_data;

prepare delete_talbe_data from @delete_t_c_answerresult_high_level_formal;   
execute delete_talbe_data; 
deallocate prepare delete_talbe_data;

prepare delete_talbe_data from @delete_t_q_exam_notes_formal;   
execute delete_talbe_data;
deallocate prepare delete_talbe_data;

prepare delete_talbe_data from @delete_t_q_exam_notes_formal_record_setup;   
execute delete_talbe_data;
deallocate prepare delete_talbe_data;


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

-- 用户相关数据
prepare delete_talbe_data from @t_app_student;
execute delete_talbe_data;
deallocate prepare delete_talbe_data;

prepare delete_talbe_data from @t_app_comment;
execute delete_talbe_data;
deallocate prepare delete_talbe_data;

prepare delete_talbe_data from @t_app_examlog;
execute delete_talbe_data;
deallocate prepare delete_talbe_data;

prepare delete_talbe_data from @t_app_examscore;
execute delete_talbe_data;
deallocate prepare delete_talbe_data;
 
prepare delete_talbe_data from @t_app_excess_exam_log;
execute delete_talbe_data;
deallocate prepare delete_talbe_data;

prepare delete_talbe_data from @t_app_learnrecord;
execute delete_talbe_data;
deallocate prepare delete_talbe_data;
 
prepare delete_talbe_data from @t_app_paperlog;
execute delete_talbe_data;
deallocate prepare delete_talbe_data;
 
prepare delete_talbe_data from @t_app_sharedownload;
execute delete_talbe_data;
deallocate prepare delete_talbe_data;
                
prepare delete_talbe_data from @t_app_simulation_operate_log;
execute delete_talbe_data;
deallocate prepare delete_talbe_data;

prepare delete_talbe_data from @t_app_studentlearn;
execute delete_talbe_data;
deallocate prepare delete_talbe_data;
                    
prepare delete_talbe_data from @t_app_userlogin;
execute delete_talbe_data;
deallocate prepare delete_talbe_data;
                      
prepare delete_talbe_data from @t_c_answerrecord;
execute delete_talbe_data;
deallocate prepare delete_talbe_data;

prepare delete_talbe_data from @t_c_answerrecord_high_level;
execute delete_talbe_data;
deallocate prepare delete_talbe_data;
         
prepare delete_talbe_data from @t_c_answerresult;
execute delete_talbe_data;
deallocate prepare delete_talbe_data;

prepare delete_talbe_data from @t_c_answerresult_high_level;
execute delete_talbe_data;
deallocate prepare delete_talbe_data;

prepare delete_talbe_data from @t_p_appraisal_user_score;
execute delete_talbe_data;
deallocate prepare delete_talbe_data;

prepare delete_talbe_data from @t_p_exam_record;
execute delete_talbe_data;
deallocate prepare delete_talbe_data;
                
prepare delete_talbe_data from @t_p_jobs_statistics;
execute delete_talbe_data;
deallocate prepare delete_talbe_data;
               
prepare delete_talbe_data from @t_p_major_statistics;
execute delete_talbe_data;
deallocate prepare delete_talbe_data;

prepare delete_talbe_data from @t_p_resource_history_learnrecord;
execute delete_talbe_data;
deallocate prepare delete_talbe_data;

prepare delete_talbe_data from @t_p_resource_learnrecord;
execute delete_talbe_data;
deallocate prepare delete_talbe_data;
      
prepare delete_talbe_data from @t_p_resource_month_statistics;
execute delete_talbe_data;
deallocate prepare delete_talbe_data;

prepare delete_talbe_data from @t_p_resource_questionrecord;
execute delete_talbe_data;
deallocate prepare delete_talbe_data;

prepare delete_talbe_data from @t_p_sign_record;
execute delete_talbe_data;
deallocate prepare delete_talbe_data;

prepare delete_talbe_data from @t_p_sign_score;
execute delete_talbe_data;
deallocate prepare delete_talbe_data;

prepare delete_talbe_data from @t_p_skill_statistics;
execute delete_talbe_data;
deallocate prepare delete_talbe_data;

prepare delete_talbe_data from @t_p_user_certificate;
execute delete_talbe_data;
deallocate prepare delete_talbe_data;

prepare delete_talbe_data from @t_q_collection;
execute delete_talbe_data;
deallocate prepare delete_talbe_data;

prepare delete_talbe_data from @t_q_practicenotes;
execute delete_talbe_data;
deallocate prepare delete_talbe_data;

prepare delete_talbe_data from @t_q_question_mark;
execute delete_talbe_data;
deallocate prepare delete_talbe_data;

prepare delete_talbe_data from @t_q_timerecord;                       
execute delete_talbe_data;
deallocate prepare delete_talbe_data;



-- -----------------------------------------------------------------------------------------------------
--  删除学校相关数据
prepare delete_talbe_data from @t_app_exam;  
execute delete_talbe_data; 
deallocate prepare delete_talbe_data;

prepare delete_talbe_data from @t_app_college_studentclass;  
execute delete_talbe_data; 
deallocate prepare delete_talbe_data;

prepare delete_talbe_data from @t_app_college_class;  
execute delete_talbe_data; 
deallocate prepare delete_talbe_data;

prepare delete_talbe_data from @t_app_college_comment;  
execute delete_talbe_data; 
deallocate prepare delete_talbe_data;

prepare delete_talbe_data from @t_app_college_course;  
execute delete_talbe_data; 
deallocate prepare delete_talbe_data;

prepare delete_talbe_data from @t_app_college_course_resource;  
execute delete_talbe_data; 
deallocate prepare delete_talbe_data;

prepare delete_talbe_data from @t_app_college_learnrecord;  
execute delete_talbe_data; 
deallocate prepare delete_talbe_data;

prepare delete_talbe_data from @t_app_college_menu_config;  
execute delete_talbe_data; 
deallocate prepare delete_talbe_data;

prepare delete_talbe_data from @t_app_college_share;  
execute delete_talbe_data; 
deallocate prepare delete_talbe_data;

prepare delete_talbe_data from @t_app_college_share_downlaod;  
execute delete_talbe_data; 
deallocate prepare delete_talbe_data;

prepare delete_talbe_data from @t_app_college_sharecomment;  
execute delete_talbe_data; 
deallocate prepare delete_talbe_data;

prepare delete_talbe_data from @t_app_college_simulation;  
execute delete_talbe_data; 
deallocate prepare delete_talbe_data;

prepare delete_talbe_data from @t_app_college_specialtytype;  
execute delete_talbe_data; 
deallocate prepare delete_talbe_data;

prepare delete_talbe_data from @t_app_exampaper;  
execute delete_talbe_data; 
deallocate prepare delete_talbe_data;

prepare delete_talbe_data from @t_app_examquestion;  
execute delete_talbe_data; 
deallocate prepare delete_talbe_data;

prepare delete_talbe_data from @t_c_project_college;  
execute delete_talbe_data; 
deallocate prepare delete_talbe_data;

prepare delete_talbe_data from @t_p_appraisal;  
execute delete_talbe_data; 
deallocate prepare delete_talbe_data;

prepare delete_talbe_data from @t_p_chapter_college;  
execute delete_talbe_data; 
deallocate prepare delete_talbe_data;

prepare delete_talbe_data from @t_p_chapter_resource_college;  
execute delete_talbe_data; 
deallocate prepare delete_talbe_data;

prepare delete_talbe_data from @t_p_course;  
execute delete_talbe_data; 
deallocate prepare delete_talbe_data;

prepare delete_talbe_data from @t_p_course_college;  
execute delete_talbe_data; 
deallocate prepare delete_talbe_data;

prepare delete_talbe_data from @t_q_classes;  
execute delete_talbe_data; 
deallocate prepare delete_talbe_data;

prepare delete_talbe_data from @t_q_classandquestion;  
execute delete_talbe_data; 
deallocate prepare delete_talbe_data;

prepare delete_talbe_data from @t_q_questionbank_college;  
execute delete_talbe_data; 
deallocate prepare delete_talbe_data;

prepare delete_talbe_data from @t_s_examinfo;  
execute delete_talbe_data; 
deallocate prepare delete_talbe_data;

prepare delete_talbe_data from @t_sys_college_config;  
execute delete_talbe_data; 
deallocate prepare delete_talbe_data;

prepare delete_talbe_data from @t_sys_user;
execute delete_talbe_data; 
deallocate prepare delete_talbe_data;
-- ------------------------------------------------------------------
prepare delete_talbe_data from @t_app_examinee;
execute delete_talbe_data;
deallocate prepare delete_talbe_data;

prepare delete_talbe_data  from @t_app_user; 
execute delete_talbe_data;
deallocate prepare delete_talbe_data;

prepare delete_talbe_data from @t_app_school;  
execute delete_talbe_data; 
deallocate prepare delete_talbe_data;

END IF;  
END