存储过程应用

可重复执行SQL

原理：查询某个字段是否存在，如果不存在，则添加

字段操作（删除、添加等）

drop PROCEDURE if EXISTS add_code;
create procedure add_code() begin
    declare var int default
        (SELECT count(1) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA in (select database()) AND table_name='<table_name>' AND COLUMN_NAME='<column_name>');
    if var = 0
    then
        alter table <table_name> DROP <column_name>;
    end if;
end;
call add_code();
drop PROCEDURE if EXISTS add_code;

值操作（删除、添加等）

drop PROCEDURE if EXISTS add_code;
create procedure add_code() begin
    declare var int default
        (select count(1) from system_code
         where code in ('_TRAINING_AK', '_TRAINING_AK_1', '_TRAINING_AK_2', '_TRAINING_AK_3', '_TRAINING_AK_4'));
    if var = 0
    then
        insert into system_code(code, code_desc_en, code_desc_zh, code_desc_tr, priority, disabled, need_control, parent_code,
                                version, created_dtm, created_by, last_modified_dtm, last_modified_by, deleted)
        VALUES ('_TRAINING_AK', 'Training record status', '培训记录状态', '培訓記錄狀態', 0, 0, 0, null, 0, now(), 0, now(), 0, 0);
        insert into system_code(code, code_desc_en, code_desc_zh, code_desc_tr, priority, disabled, need_control, parent_code,
                                version, created_dtm, created_by, last_modified_dtm, last_modified_by, deleted)
        VALUES ('_TRAINING_AK_1', 'Running', '正在进行', '正在進行', 0, 0, 0, '_TRAINING_AK', 0, now(), 0, now(), 0, 0),
               ('_TRAINING_AK_2', 'over due', '超时', '超時', 0, 0, 0, '_TRAINING_AK', 0, now(), 0, now(), 0, 0),
               ('_TRAINING_AK_3', 'Completed', '已完成', '已完成', 0, 0, 0, '_TRAINING_AK', 0, now(), 0, now(), 0, 0),
               ('_TRAINING_AK_4', 'Did not pass', '未通过', '未通過', 0, 0, 0, '_TRAINING_AK', 0, now(), 0, now(), 0, 0);
    end if;
end;
call add_code();
drop PROCEDURE if EXISTS add_code;