oracle、mysql新增字段，字段存在则不处理

oracle:

表名：CHANNEL_TRADE_DETAIL
列名：exchange_code

declare
    v_rowcount integer;
begin
    select count(*) into v_rowcount from dual where exists(
        select * from col
            where tname = upper('CHANNEL_TRADE_DETAIL')
                and cname = upper('exchange_code') );
    if v_rowcount = 0 then
        execute immediate 'ALTER TABLE CHANNEL_TRADE_DETAIL ADD exchange_code varchar2(32) default null ';
    end if;
end;
/

亲测plsql可用。

 

mysql：

表名：channel_product
列名：EXCHANGE_CODE

DROP PROCEDURE IF EXISTS add_column_channel_product;            
CREATE PROCEDURE add_column_channel_product() 
    BEGIN
        IF NOT EXISTS (SELECT 1  FROM information_schema.columns WHERE table_name = 'channel_product'  AND column_name = 'EXCHANGE_CODE')
        THEN 
          ALTER TABLE channel_product ADD COLUMN  `EXCHANGE_CODE`  VARCHAR(32) DEFAULT NULL  COMMENT '交易所Code';
        END IF;
    END;

-- 调用
CALL add_column_channel_product();

sqlyong测试报错，同事在其它工具测试可执行。

转载于:https://www.cnblogs.com/frank-quan/p/6044454.html