
 -- --存储过程，处理跨天数据-----------
DROP PROCEDURE IF exists pro_dealAcrossDay;
CREATE PROCEDURE `pro_dealAcrossDay`()
BEGIN   
DECLARE sid BIGINT;   
DECLARE start_time varchar(50);   
DECLARE end_time varchar(50); 

declare b int default 0;    /*是否达到记录的末尾控制变量*/
  
DECLARE cur_1 CURSOR FOR SELECT scheduling_id FROM dd_scheduling WHERE shift_time_end<shift_time_begin;/*查询跨天的数据*/
DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1;
OPEN cur_1;
FETCH cur_1 INTO sid; /*获取第一条记录*/
while b<>1 do            
	/*插入24:00前的数据*/
	INSERT INTO `dd_scheduling` (
	`org_branch_code`,
	`org_branch_name`,
	`org_middle_branch_code`,
	`org_middle_branch_name`,
	`fence_name`,
	`fence_code`,
	`surveyor_name`,
	`surveyor_code`,
	`org_code`,
	`org_name`,
	`shift_no`,
	`shift_name`,
	`shift_time_begin`,
	`shift_time_end`,
	`batch_no`,
	`upload_dept_code`,
	`day_date`,
	`month_date`,
	`year_date`,
	`work_date`,
	`scheduling_type`,
	`scheduling_type_code`,
	`loss_center_no`,
	`is_enable`,
	`survey_car_roll`,
	`survey_car_code`,
	`survey_nocar_roll`,
	`survey_nocar_code`,
	`scheduling_table_type`,
	`survey_type`,
	`create_by`,
	`create_time`,
	`update_by`,
	`update_time`
) SELECT
	`org_branch_code`,
	`org_branch_name`,
	`org_middle_branch_code`,
	`org_middle_branch_name`,
	`fence_name`,
	`fence_code`,
	`surveyor_name`,
	`surveyor_code`,
	`org_code`,
	`org_name`,
	`shift_no`,
	`shift_name`,
	`shift_time_begin`,
	'23:59:59',
	`batch_no`,
	`upload_dept_code`,
	`day_date`,
	`month_date`,
	`year_date`,
	`work_date`,
	`scheduling_type`,
	`scheduling_type_code`,
	`loss_center_no`,
	`is_enable`,
	`survey_car_roll`,
	`survey_car_code`,
	`survey_nocar_roll`,
	`survey_nocar_code`,
	`scheduling_table_type`,
	`survey_type`,
	`create_by`,
	`create_time`,
	`update_by`,
	`update_time`
FROM
	dd_scheduling WHERE scheduling_id=sid;


	/*插入24:00后的数据*/
	INSERT INTO `dd_scheduling` (
		`org_branch_code`,
		`org_branch_name`,
		`org_middle_branch_code`,
		`org_middle_branch_name`,
		`fence_name`,
		`fence_code`,
		`surveyor_name`,
		`surveyor_code`,
		`org_code`,
		`org_name`,
		`shift_no`,
		`shift_name`,
		`shift_time_begin`,
		`shift_time_end`,
		`batch_no`,
		`upload_dept_code`,
		`day_date`,
		`month_date`,
		`year_date`,
		`work_date`,
		`scheduling_type`,
		`scheduling_type_code`,
		`loss_center_no`,
		`is_enable`,
		`survey_car_roll`,
		`survey_car_code`,
		`survey_nocar_roll`,
		`survey_nocar_code`,
		`scheduling_table_type`,
		`survey_type`,
		`create_by`,
		`create_time`,
		`update_by`,
		`update_time`
	) SELECT
		`org_branch_code`,
		`org_branch_name`,
		`org_middle_branch_code`,
		`org_middle_branch_name`,
		`fence_name`,
		`fence_code`,
		`surveyor_name`,
		`surveyor_code`,
		`org_code`,
		`org_name`,
		`shift_no`,
		`shift_name`,
		'00:00:00',
		`shift_time_end`,
		`batch_no`,
		`upload_dept_code`,
		`day_date`,
		`month_date`,
		`year_date`,
		`work_date`,
		`scheduling_type`,
		`scheduling_type_code`,
		`loss_center_no`,
		`is_enable`,
		`survey_car_roll`,
		`survey_car_code`,
		`survey_nocar_roll`,
		`survey_nocar_code`,
		`scheduling_table_type`,
		`survey_type`,
		`create_by`,
		`create_time`,
		`update_by`,
		`update_time`
	FROM
		dd_scheduling WHERE scheduling_id=sid;
	
	/*删除旧数据*/
	DELETE FROM dd_scheduling WHERE scheduling_id=sid;

	FETCH cur_1 INTO sid; /*取下一条记录*/
end while;
close cur_1;     

END






















