use orgs;
DROP PROCEDURE IF EXISTS CreateTable;
DELIMITER //
CREATE PROCEDURE `CreateTable`(IN tableName varchar(100))
BEGIN
-- 定义一个变量来存放机构id
DECLARE id int(11);
-- 定义遍历数据结束标志
DECLARE done BOOLEAN DEFAULT 0;
DECLARE curid CURSOR FOR select orgId as id From common.t_org_info where `status`=2;
-- 当REPEAT由于没有更多的行供循环而不能继续时出现这个条件，将done设置为1，此时REPEAT终止
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
OPEN curid;
read_loop:LOOP
FETCH curid into id;
IF done THEN
LEAVE read_loop;
END IF;
-- call AsyncReportDataByOrgId(id);
Set @TableStr = CONCAT(tableName,id);
select COUNT(1) into @CountNum from information_schema.tables where table_schema='orgs'and table_name = @TableStr;
IF @CountNum = 0 THEN
-- select @TableStr;
Set @TableStr2 = CONCAT('table_template.',tableName,'template');
-- select @TableStr2;
Set @STMT= CONCAT('CREATE TABLE ',@TableStr, ' As SELECT * FROM ',@TableStr2,' WHERE 1=0;');
select @STMT;
-- Set @STMT=CONCAT(' show Create Table ', @TableStr);
PREPARE ShowCreateTable From @STMT;
EXECUTE ShowCreateTable ;
DEALLOCATE PREPARE ShowCreateTable;
END IF;
END LOOP;
CLOSE curid;
END
//
call CreateTable('t_assessment_');
-- 查询org 表格获取所有机构Id。
-- 自动字段脚本
use orgs;
DROP PROCEDURE IF EXISTS AddColumn;
DELIMITER //
CREATE PROCEDURE `AddColumn`(IN tableName varchar(100),IN columnName varchar(100),IN createScript varchar(500))
BEGIN
-- 定义一个变量来存放机构id
DECLARE id int(11);
-- 定义遍历数据结束标志
DECLARE done BOOLEAN DEFAULT 0;
DECLARE curid CURSOR FOR select orgId as id From common.t_org_info where `status`=2;
-- 当REPEAT由于没有更多的行供循环而不能继续时出现这个条件，将done设置为1，此时REPEAT终止
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
OPEN curid;
read_loop:LOOP
FETCH curid into id;
IF done THEN
LEAVE read_loop;
END IF;
Set @TableStr = CONCAT(tableName,id);
select COUNT(1) into @CountNum from information_schema.columns where table_schema='orgs'and table_name = @TableStr and column_name=columnName ;
-- select @CountNum;
IF @CountNum = 0 THEN
-- select createScript;
-- select @TableStr;
Set @STMT= REPLACE( REPLACE(createScript,'',@TableStr),'',columnName);
-- select @STMT;
-- Set @STMT=CONCAT(' show Create Table ', @TableStr);
PREPARE ShowCreateTable From @STMT;
EXECUTE ShowCreateTable ;
DEALLOCATE PREPARE ShowCreateTable;
END IF;
END LOOP;
CLOSE curid;
END
//
call AddColumn('t_activity_','extra','ALTER TABLE ADD COLUMN `` json NOT NULL COMMENT \'这里是备注\';');