
# app库

# apma_metadata 卷内表 新增 载体类型 medium_type
DROP PROCEDURE IF EXISTS add_medium_type;
CREATE PROCEDURE add_medium_type(IN tenantId INT(20)) BEGIN

/*用于判断是否结束循环*/
declare tableId bigint;
declare storageLocate varchar(50);
declare done int default false;

/*定义游标*/
DECLARE cur1 CURSOR FOR SELECT id, storage_locate FROM apma_archive_table where archive_layer = 'F' and tenant_id = tenantId;

declare continue handler for not found set done = true;
/*打开游标*/
OPEN cur1;
/* 循环开始 */
    myLoop: LOOP
        fetch cur1 into tableId, storageLocate;
        if done then
                leave myLoop;
        end if;

        INSERT INTO `apma_metadata`(`table_id`, `storage_locate`, `metadata_chinese`, `metadata_english`, `metadata_class`, `tag_english`, `metadata_type`, `metadata_length`, `metadata_dot_length`, `metadata_sort`, `metadata_null`, `metadata_default_value`, `dict_code`, `is_list`, `is_edit`, `is_search`, `is_repeat`, `sort_no`, `tenant_id`, `revision`, `created_by`, `created_time`) VALUES (tableId, storageLocate, '载体类型', 'medium_type', 1, 'medium_type', 'varchar', 20, 0, NULL, 0, '原生电子', 'medium_type', 0, 0, 0, 0, 0, tenantId, 1, 1, '2023-02-28 11:32:29');

    end LOOP myLoop;
close cur1;
END;


/* apma_template_metadata 卷内表 新增 载体类型 medium_type*/
DROP PROCEDURE IF EXISTS template_add_medium_type;
CREATE PROCEDURE template_add_medium_type(IN tenantId INT(20)) BEGIN

/*用于判断是否结束循环*/
declare templateTableId bigint;
declare done int default false;

/*定义游标*/
DECLARE cur1 CURSOR FOR SELECT id FROM apma_template_table where layer_code = 'F' and tenant_id = tenantId;

declare continue handler for not found set done = true;

/*打开游标*/
OPEN cur1;
/* 循环开始 */
    myLoop: LOOP
        fetch cur1 into templateTableId;
        if done then
				leave myLoop;
		end if;

        INSERT INTO `apma_template_metadata`(`template_table_id`, `metadata_chinese`, `metadata_english`, `metadata_sys`, `tag_english`, `metadata_english_hidden`, `metadata_english_no`, `metadata_type`, `metadata_length`, `metadata_dot_length`, `metadata_sort`, `metadata_null`, `metadata_default_value`, `dict_code`, `metadata_class`, `is_list`, `is_edit`, `is_search`, `is_repeat`, `sort_no`, `revision`, `created_by`, `created_time`) VALUES (templateTableId, '载体类型', 'medium_type', 0, 'medium_type', NULL, NULL, 'varchar', 20, 0, NULL, 0, '原生电子', 'medium_type', 1, 0, 0, 0, 0, NULL, 1, 1, '2023-02-28 16:31:36');
    end LOOP myLoop;
close cur1;
END;



-- 调用存储过程
CALL add_medium_type(租户id);
CALL template_add_medium_type(租户id);


-- 删除存储过程
DROP PROCEDURE add_medium_type;
DROP PROCEDURE template_add_medium_type;












## 档案表所在的库执行
## 卷内表新增 载体类型
DROP PROCEDURE IF EXISTS archive_table_add_medium_type;
CREATE PROCEDURE `archive_table_add_medium_type`(IN tenantId INT ( 20 ),IN dbName VARCHAR ( 20 ))
BEGIN
    DECLARE sqlScript VARCHAR ( 1000 ) DEFAULT '';
    DECLARE done INT DEFAULT FALSE;
/*定义游标*/
    DECLARE
        cur1 CURSOR FOR SELECT
        CONCAT( 'ALTER TABLE ', table_name, " ADD COLUMN medium_type varchar(20) DEFAULT '原生电子' COMMENT '载体类型';" ) AS sql_script
    FROM
        information_schema.TABLES
    WHERE
        table_schema = dbName
        AND table_name LIKE CONCAT("t_",tenantId,"%")
        AND ( table_name LIKE "%_f" );
    DECLARE
        CONTINUE HANDLER FOR NOT found
        SET done = TRUE;
/*打开游标*/
    OPEN cur1;
/* 循环开始 */
    myLoop: LOOP
        FETCH cur1 INTO sqlScript;
        IF done THEN
            LEAVE myLoop;
        END IF;
        SET @SQL = sqlScript;
        PREPARE stmt  FROM @SQL;-- 预编译一条sql语句，并命名为stmt
        EXECUTE stmt;-- 执行预编译sql
    END LOOP myLoop;
    CLOSE cur1;
END;


-- 调用存储过程
CALL archive_table_add_medium_type(租户id,档案表所在库);

-- 删除存储过程
DROP PROCEDURE archive_table_add_medium_type;





