|
DELIMITER $$ USE `txnfdb`$$ DROP PROCEDURE IF EXISTS `PD_VERIFICATION_TINDEXMIK30`$$ CREATE DEFINER=`root`@`%` PROCEDURE `PD_VERIFICATION_TINDEXMIK30`() BEGIN -- -------------------------------临时变量------------------------------------------- DECLARE TEMP_YEAR VARCHAR(10); DECLARE TEMP_MONTH VARCHAR(10); DECLARE TEMP_TABLE_NAME VARCHAR(100); DECLARE TEMP_BEGIN_TIME VARCHAR(30); DECLARE TEMP_END_TIME VARCHAR(30); DECLARE TEMP_SQL VARCHAR(1000); -- -------------------------------中间变量------------------------------------------- DECLARE F_CODE VARCHAR(10); -- 股票代码 DECLARE F_CLOSE DECIMAL(18,3); DECLARE F_VOLUME DECIMAL(18,3); DECLARE F_AMOUNT DECIMAL(18,3); DECLARE F_OPEN DECIMAL(18,3); DECLARE F_HIGH DECIMAL(18,3); DECLARE F_LOW DECIMAL(18,3); DECLARE F_TIME INT; DECLARE F_SEQ BIGINT; DECLARE ERROE_MSG VARCHAR(500); -- ------------------------------游标相关定义---------------------------------------- -- 定义游标遍历时，作为判断是否遍历完全部记录的标记 DECLARE no_more_departments INTEGER DEFAULT 0; -- 定义游标名字为C_RESULT DECLARE C_RESULT CURSOR FOR SELECT * FROM tmp_index30_table; -- 声明当游标遍历完全部记录后将标志变量置成某个值 DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_departments=1; -- ---------------------------------异常处理---------------------------------------- -- 记录错误日志 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN SET @err = fn_get_error(); INSERT INTO act_records(act,actTime,actDesc,flag) SELECT 'PD_VERIFICATION_TINDEXMIK30',NOW(),@err,1; END; SET TEMP_YEAR=DATE_FORMAT( NOW(), '%Y'); SET TEMP_MONTH=DATE_FORMAT( NOW(), '%m'); SET TEMP_TABLE_NAME=CONCAT('db.t_mik_index_30_',TEMP_YEAR,TEMP_MONTH); SET TEMP_BEGIN_TIME=CONCAT(DATE_FORMAT( NOW(), '%Y-%m-%d'),' 00:00:00'); SET TEMP_END_TIME=CONCAT(DATE_FORMAT( NOW(), '%Y-%m-%d'),' 23:59:59'); SET TEMP_SQL=CONCAT(' Create TEMPORARY Table tmp_index30_table( SELECT f_code,F_CLOSE,F_VOLUME,F_AMOUNT,F_OPEN,F_HIGH,F_LOW,f_time,f_SEQ FROM ',TEMP_TABLE_NAME, ' where f_update_time >=''',TEMP_BEGIN_TIME,''' AND f_update_time<=''' ,TEMP_END_TIME,''' order by f_date , f_time)'); -- 执行动态sql SET @sql=TEMP_SQL; PREPARE stmt FROM @sql; -- 预处理需要执行的动态SQL，其中stmt是一个变量 EXECUTE stmt; -- 执行SQL语句 DEALLOCATE PREPARE stmt; -- 释放掉预处理段 OPEN C_RESULT; REPEAT FETCH C_RESULT INTO f_code,F_CLOSE,F_VOLUME,F_AMOUNT,F_OPEN,F_HIGH,F_LOW,f_time,f_SEQ ; SET ERROE_MSG=''; IF F_CLOSE = NULL OR F_CLOSE = 0 THEN SET ERROE_MSG=CONCAT('F_CLOSE 的值为' ,F_CLOSE); END IF; IF ERROE_MSG <> '' THEN INSERT INTO verification_records (info_type,table_name,codes,f_seq,error_msg,create_time ) VALUES('1',TEMP_TABLE_NAME,F_CODE,f_SEQ,ERROE_MSG,NOW()); END IF; UNTIL no_more_departments END REPEAT; -- 结束循环 CLOSE C_RESULT; DROP TEMPORARY TABLE tmp_index30_table; -- 删除临时表 END$$ DELIMITER ; (责任编辑：IT) |