USE `yz_zph_db`$$
DROP PROCEDURE IF EXISTS `sp_table_add_column`$$
CREATE DEFINER=`root`@`%` PROCEDURE `sp_table_add_column`(
IN P_OLD_DATABSAE VARCHAR(100),
IN P_NOW_DATABSAE VARCHAR(100))
BEGIN
DROP TEMPORARY TABLE IF EXISTS tmp_table1;
CREATE TEMPORARY TABLE tmp_table1
(p_int INT AUTO_INCREMENT PRIMARY KEY,p_TABLE_SCHEMA VARCHAR(100) ,p_TABLE_NAME VARCHAR(100),p_COLUMN_NAME VARCHAR(100),p_COLUMN_TYPE VARCHAR(100),
p_IS_NULLABLE VARCHAR(100),p_COLUMN_DEFAULT VARCHAR(100),p_COLUMN_COMMENT VARCHAR(100));
SET @tsql2= CONCAT(“INSERT INTO tmp_table1 (p_TABLE_SCHEMA,p_TABLE_NAME,p_COLUMN_NAME,p_COLUMN_TYPE,p_IS_NULLABLE,p_COLUMN_DEFAULT,p_COLUMN_COMMENT)
SELECT DISTINCT b.TABLE_SCHEMA , b.TABLE_NAME, b.COLUMN_NAME,b.COLUMN_TYPE,b.IS_NULLABLE,b.COLUMN_DEFAULT,b.COLUMN_COMMENT
FROM “,P_OLD_DATABSAE,” a JOIN “,P_NOW_DATABSAE,
” b ON a.TABLE_SCHEMA=b.TABLE_SCHEMA AND a.TABLE_NAME=b.TABLE_NAME
where b.COLUMN_NAME not in (SELECT b.COLUMN_NAME FROM “,P_OLD_DATABSAE,
” b WHERE a.TABLE_SCHEMA=b.TABLE_SCHEMA AND a.TABLE_NAME=b.TABLE_NAME) and b.TABLE_NAME not in
(select c.TABLE_NAME from information_schema.TABLES c where c.TABLE_TYPE=”,””””,”VIEW”,”””)”,”;”);
PREPARE tsql2 FROM @tsql2;
EXECUTE tsql2;
DEALLOCATE PREPARE tsql2;
INSERT INTO add_column_table (add_column)
SELECT CONCAT(“ALTER TABLE “,P_TABLE_SCHEMA,”.”,p_TABLE_NAME,” ADD “,p_COLUMN_NAME,” “,p_COLUMN_TYPE,
CASE WHEN p_IS_NULLABLE=”YES” THEN ” DEFAULT NULL ” ELSE ” NOT NULL ” END,
CASE WHEN p_COLUMN_DEFAULT IS NOT NULL THEN CONCAT(” DEFAULT “,” “,””””,p_COLUMN_DEFAULT,””””,” “) ELSE “” END,
CASE WHEN p_COLUMN_COMMENT IS NULL THEN “”
WHEN p_COLUMN_COMMENT=”” THEN “”
ELSE CONCAT(” COMMENT “,” “,””””,p_COLUMN_COMMENT,””””) END,”;”) FROM tmp_table1;
END$$
DELIMITER ;
40