-- UPDATE prescription_medicine SET 
-- PATIENT_ID = REPLACE(PATIENT_ID,"‘","");

DROP TABLE IF EXISTS tmp_update_replace;
CREATE TABLE if  NOT EXISTS `tmp_update_replace` 
SELECT 
(@i:=@i+1) as sid,
CONCAT(‘UPDATE ‘,TABLE_NAME,‘ SET ‘,COLUMN_NAME,‘ = REPLACE(‘,COLUMN_NAME,‘,\"\‘\",\"\");‘) 
as exe_text 
 FROM information_schema.`COLUMNS` 
,(select @i:=0) as b
WHERE TABLE_SCHEMA = ‘sxey‘ AND TABLE_NAME in (‘prescription_medicine‘,‘prescription_medicine_new‘,‘DEPT_INFO‘);

-- 创建执行函数
DROP PROCEDURE IF EXISTS exe_query_queue;
delimiter $$
CREATE PROCEDURE exe_query_queue(IN tbName varchar(100),IN colNameId varchar(100),IN colNameExe varchar(100))     
BEGIN
	DECLARE SQL_TEXT1 VARCHAR(500);
	DECLARE SQL_TEXT2 VARCHAR(500);
	DECLARE SQL_TEXT3 VARCHAR(500);
	DECLARE nR INT;
	DECLARE I INT;
		
	SET SQL_TEXT1 =CONCAT(‘SELECT COUNT(*) into @numRow From ‘,tbName);
	SET @tmp1 = SQL_TEXT1;
	PREPARE exeSql1 from @tmp1;
	EXECUTE exeSql1;
	DEALLOCATE PREPARE exeSql1;
	  -- 执行定义语句 SQL_TEXT1
	SET nR=  @numRow;
		-- 执行定义语句 SQL_TEXT2
	-- SELECT @numRow;
  IF nR is not NULL THEN 
		SET I = 1;
		WHILE I < nR DO
			SET SQL_TEXT2 =CONCAT(‘SELECT ‘,colNameExe,‘ into @exeSqlText2 FROM ‘,tbName,‘ WHERE ‘,colNameId,‘  =‘,I,‘;‘);
			-- 执行SQL_TEXT3
			-- SELECT SQL_TEXT2;

			SET @tmp2 = SQL_TEXT2;
			PREPARE exeSql2 from @tmp2;
			EXECUTE exeSql2;
			SET SQL_TEXT3 = @exeSqlText2;
			DEALLOCATE PREPARE exeSql2;
			
			-- SELECT SQL_TEXT3;

			SET @tmp3 = SQL_TEXT3;
			PREPARE exeSql3 from @tmp3;
			EXECUTE exeSql3;
			DEALLOCATE PREPARE exeSql3;
		 SET I=I+1;
		END WHILE;
	end if;
END  $$
delimiter ;
CALL exe_query_queue(‘tmp_update_replace‘,‘sid‘,‘exe_text‘);
-- 删除执行结果
DROP TABLE IF EXISTS tmp_update_replace;
-- 删除执行函数
DROP PROCEDURE IF EXISTS exe_query_queue;