可重复执行部署脚本--检查表字段是否存在，不存在则创建字段并添加注释及默认值

 

sqlserver

USE OESv55
GO
IF NOT EXISTS(SELECT * FROM UP_SEC_BUSI_ROLE WHERE [ID] = object_id('UP_SEC_BUSI_ROLE') AND [NAME] = 'IS_PRE_MAKE')
ALTER TABLE UP_SEC_BUSI_ROLE ADD IS_PRE_MAKE INT NOT NULL DEFAULT 1 
EXECUTE sp_addextendedproperty N'MS_Description', '是否为预制角色 1预制，0其他', N'user', N'dbo', N'table', N'UP_SEC_BUSI_ROLE', N'column', N'IS_PRE_MAKE'
GO

oracle

DECLARE
    add_str   VARCHAR2(5000) := 'ALTER TABLE UP_SEC_BUSI_ROLE ADD IS_PRE_MAKE INT NOT NULL DEFAULT 1 COMMENT ON COLUMN UP_SEC_BUSI_ROLE.IS_PRE_MAKE IS ''是否为预制角色 1预制，0其他';
    count_flag  NUMBER;
BEGIN
    select count(*) into count_flag from user_tab_columns 
        where table_name = 'UP_SEC_BUSI_ROLE' and column_name = 'IS_PRE_MAKE ';
    if count_flag < 1 then
        execute immediate add_str;
    end if;
end