各位熟悉语法的，请帮检查一下下面的MYSQL存储过程语法对不对解决方案

各位熟悉语法的，请帮检查一下下面的MYSQL存储过程语法对不对
RT，以前一直用的是oracle语言，对mysql语法不是很熟悉，现在急用一段能在mysql上编译通过的存储过程，可惜我在网吧 没有工具调试，以下是我大致写的代码，请帮忙在不改变功能的前提下能编译吧，谢谢：）

PS：功能上是配了一张data_merage_param，用于对比2个数据库中相同表名里面不同的内容，找出来之后 写进临时表"表名_Back"里面，然后主键加上一段偏移量，再写回到以前的某个表里，嗯 大致就是这样 整合数据，求帮完善，谢谢

SQL code

CREATE PROCEDURE Pro_Merage_DataBEGINDECLARE v_DB1,v_TABLE1,v_DB2,v_TABLE2 VARCHAR(45);DECLARE v_OFFSET INT;DECLARE v_KEYS   VARCHAR(80);DECLARE v_sql    VARCHAR(1000);DECLARE cur_Param CURSOR  FOR SELECT * FROM data_merage_param;DECLARE n_flag   INT;OPEN cur_Param;FETCH cur_Param INTO v_DB1,v_TABLE1,v_DB2,v_TABLE2,v_OFFSET,v_KEYS;WHILE @@FECTH_STATUS = 0BEGINv_sql = 'SELECT 1 FROM information_schema.TABLES where table_schema = '@+v_DB1' and table_name = '@+v_TABLE1'_Back';EXECUTE v_sql INTO n_flag;IF n_flag IS NOT NULL THENv_sql = 'TRUNCATE TABLE '@+v_TABLE1'_Back';EXECUTE v_sql;v_sql = 'INSERT INTO '@+v_TABLE1'_Back SELECT * FROM '@+v_DB1'.'@+v_TABLE1' A LEFT JOIN '@+v_DB1'.'@+v_TABLE1' B USING('@+v_KEYS') WHERE b.'@+v_KEYS' IS NULL;';EXECUTE v_sql;COMMIT;ELSEv_sql = 'CREATE TABLE '@+v_TABLE1'_Back SELECT * FROM '@+v_DB1'.'@+v_TABLE1' A LEFT JOIN '@+v_DB1'.'@+v_TABLE1' B USING('@+v_KEYS') WHERE b.'@+v_KEYS' IS NULL;';EXECUTE v_sql;COMMIT;END IF;v_sql = 'UPDATE '@+v_DB1'.'@+v_TABLE1' SET '@+v_KEYS' = '@+v_KEYS' + '@+V_OFFSET;EXECUTE v_sql;COMMIT;v_sql = 'INSERT INTO '@+v_DB1'.'@+v_TABLE1' SELECT * FROM '@+v_DB1'.'@+v_TABLE1'_Back';EXECUTE v_sql;COMMIT;ENDCLOSE cur_Param;DEALLOCATE cur_Param;END

[解决办法]
建议先看看MYSQL HELP
DELIMITER $$
DROP PROCEDURE IF EXISTS Pro_Merage_Data$$
CREATE PROCEDURE Pro_Merage_Data()
BEGIN
DECLARE v_OFFSET INT;
DECLARE v_KEYS VARCHAR(80);
DECLARE v_sql VARCHAR(1000);
DECLARE n_flag INT;
DECLARE done INT DEFAULT 0;
DECLARE v_DB1,v_TABLE1,v_DB2,v_TABLE2 VARCHAR(45);
DECLARE cur_Param CURSOR FOR SELECT * FROM data_merage_param;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN cur_Param;
FETCH cur_Param INTO v_DB1,v_TABLE1,v_DB2,v_TABLE2,v_OFFSET,v_KEYS;
WHILE done=0 DO
SET @v_sql = CONCAT('SELECT 1 into @aa FROM information_schema.TABLES where table_schema = ',v_DB1,' and table_name = ',v_TABLE1,'_Back');
PREPARE stml FROM @v_sql;
EXECUTE stml;
IF @aa IS NOT NULL THEN
SET @v_sql = conccat('TRUNCATE TABLE ',v_TABLE1,'_Back');
PREPARE stml FROM @v_sql;
EXECUTE stml;
SET @v_sql =CONCAT('INSERT INTO ',v_TABLE1,'_Back SELECT * FROM ',v_DB1,',',v_TABLE1,
' A LEFT JOIN ',v_DB1,'.',v_TABLE1,' B USING(',v_KEYS,') WHERE b.',v_KEYS,' IS NULL;');
PREPARE stml FROM @v_sql;
EXECUTE stml;
COMMIT;
ELSE
SET @v_sql =CONCAT( 'CREATE TABLE ',v_TABLE1,'_Back SELECT * FROM ',v_DB1,'.',v_TABLE1,
' A LEFT JOIN ',v_DB1,'.',v_TABLE1,' B USING(',v_KEYS,') WHERE b.',v_KEYS,' IS NULL;');
PREPARE stml FROM @v_sql;
EXECUTE stml;
COMMIT;
END IF;
SET @v_sql =CONCAT( 'UPDATE ',v_DB1,'.',v_TABLE1,' SET ',v_KEYS,' = ',v_KEYS,' + ',V_OFFSET);
PREPARE stml FROM @v_sql;
EXECUTE stml;
COMMIT;
SET @v_sql =CONCAT( 'INSERT INTO ',v_DB1,'.',v_TABLE1,' SELECT * FROM ',v_DB1,'.',v_TABLE1,'_Back');
PREPARE stml FROM @v_sql;
EXECUTE stml;
COMMIT;

END WHILE;
CLOSE cur_Param;
END$$
DELIMITER ;

查看更多 下一篇