mysql存储过程实用游标调用动态sql，完整例子
时间:2019-02-18 13:02 来源:linux.it.net.cn 作者:IT
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)
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) |