MySQL存储过程

1.查看存储过程

show procedure status LIKE '%_PRO%';

2.查看event

SELECT * FROM information_schema.events; 

3.创建存储过程

DELIMITER //
CREATE PROCEDURE JSX_PRO()
BEGIN
		DECLARE old_pch VARCHAR(64);
		
		DECLARE stp int DEFAULT 0;
		
		DECLARE cur CURSOR FOR SELECT PCH FROM sys_esb_log_jsx_summary ;
		
		DECLARE CONTINUE HANDLER FOR NOT FOUND SET stp=1;
		
		OPEN cur;
		fetch cur into old_pch;
			while stp<>1 do
				UPDATE sys_esb_log_jsx_summary  SET PCH=CONCAT('630000000254qzj-',DATE_FORMAT(CURDATE()-1, '%Y%m%d'),'000000-01');
				fetch cur into old_pch;
			END WHILE;
		CLOSE cur;
END
//
DELIMITER ;

4.创建event

DELIMITER //
CREATE EVENT `EVENT_CALLPRO`
	ON SCHEDULE
		EVERY 1 DAY STARTS '2023-10-18 06:00:00'
	ON COMPLETION NOT PRESERVE
	ENABLE
	COMMENT ''
	DO 
	CALL JSX_PRO() 
//
DELIMITER ;