ORACLE_批量添加、删除字段

批量添加字段

以下例子代码添加三个字段

C_INSERTTIME
C_UPDATETIME
C_FLAG

代码分解：
查询需要添加的数据表,以下以（ T_ ）开头的数据表

select table_name from user_tables where TABLE_NAME like 'T_%'

定义添加字段的SQL语句，以下添加 C_INSERTTIME 字段 类型为 date 类型

v_sql := 'alter table ' || i.table_name || ' add ' || C_INSERTTIME || ' date';

定义添加字段的注释，为：代表记录插入系统生效

v_sql := 'comment on column ' || i.table_name || '.' || C_INSERTTIME || ' is ' || q'['代表记录插入系统生效的时间

---

总代码（批量添加字段）：

DECLARE
    v_col_exists NUMBER;
    C_INSERTTIME  varchar2(20) := 'C_INSERTTIME';
    C_UPDATETIME  varchar2(20) := 'C_UPDATETIME';
    C_FLAG  varchar2(20) := 'C_FLAG';
    v_sql        varchar2(4000);
begin

    for i in (select table_name from user_tables where TABLE_NAME like 'T_%')
        loop
            SELECT count(*)
            INTO v_col_exists
            FROM user_tab_cols
            WHERE column_name = C_INSERTTIME AND table_name = i.table_name;

            IF (v_col_exists = 0) THEN
                v_sql := 'alter table ' || i.table_name || ' add ' || C_INSERTTIME || ' date';
                DBMS_OUTPUT.PUT_LINE(v_sql);
                execute immediate v_sql;
                v_sql := 'comment on column ' || i.table_name || '.' || C_INSERTTIME || ' is ' || q'['代表记录插入系统生效的时间']';
                DBMS_OUTPUT.PUT_LINE(v_sql);
                execute immediate v_sql;
            ELSE
                DBMS_OUTPUT.PUT_LINE('The column effective_date already exists');
            END IF;
            SELECT count(*)
            INTO v_col_exists
            FROM user_tab_cols
            WHERE column_name = C_UPDATETIME AND table_name = i.table_name;
            IF (v_col_exists = 0) THEN
                v_sql := 'alter table ' || i.table_name || ' add ' || C_UPDATETIME || ' date';
                DBMS_OUTPUT.PUT_LINE(v_sql);
                execute immediate v_sql;

                execute immediate 'comment on column ' || i.table_name || '.' || C_UPDATETIME || ' is ' || q'['代表记录最后更新失效的时间']';
            ELSE
                DBMS_OUTPUT.PUT_LINE('The column effective_date already exists');
            END IF;
            SELECT count(*)
            INTO v_col_exists
            FROM user_tab_cols
            WHERE column_name = C_FLAG AND table_name = i.table_name;
            IF (v_col_exists = 0) THEN
                v_sql := 'alter table ' || i.table_name || ' add ' || C_FLAG || ' char(1)';
                DBMS_OUTPUT.PUT_LINE(v_sql);
                execute immediate v_sql;
                execute immediate 'comment on column ' || i.table_name || '.' || C_FLAG || ' is ' || q'['标志位：S代表有效记录，I代表无效记录']';
            ELSE
                DBMS_OUTPUT.PUT_LINE('The column effective_date already exists');
            END IF;
        end loop;
end;

---

批量删除字段

批量删除代码一样，只需要将一下语句改为删除语句，去掉注释语句即可：

 v_sql := 'alter table ' || i.table_name || ' drop ' || C_INSERTTIME ;

总代码（批量删除字段）：

DECLARE
    v_col_exists NUMBER;
    C_INSERTTIME  varchar2(20) := 'C_INSERTTIME';
    C_UPDATETIME  varchar2(20) := 'C_UPDATETIME';
    C_FLAG  varchar2(20) := 'C_FLAG';
    v_sql        varchar2(4000);
begin

    for i in (select table_name from user_tables where TABLE_NAME like 'T_%')
        loop
            SELECT count(*)
            INTO v_col_exists
            FROM user_tab_cols
            WHERE column_name = C_INSERTTIME AND table_name = i.table_name;

            IF (v_col_exists = 0) THEN
                v_sql := 'alter table ' || i.table_name || ' drop ' || C_INSERTTIME ;
                DBMS_OUTPUT.PUT_LINE(v_sql);
                execute immediate v_sql;
                
            ELSE
                DBMS_OUTPUT.PUT_LINE('The column effective_date no already exists');
            END IF;
            SELECT count(*)
            INTO v_col_exists
            FROM user_tab_cols
            WHERE column_name = C_UPDATETIME AND table_name = i.table_name;
            IF (v_col_exists = 0) THEN
                v_sql := 'alter table ' || i.table_name || ' drop ' || C_UPDATETIME ;
                DBMS_OUTPUT.PUT_LINE(v_sql);
                execute immediate v_sql;
              
            ELSE
                DBMS_OUTPUT.PUT_LINE('The column effective_date no already exists');
            END IF;
            SELECT count(*)
            INTO v_col_exists
            FROM user_tab_cols
            WHERE column_name = C_FLAG AND table_name = i.table_name;
            IF (v_col_exists = 0) THEN
                v_sql := 'alter table ' || i.table_name || ' drop ' || C_FLAG ;
                DBMS_OUTPUT.PUT_LINE(v_sql);
                execute immediate v_sql;
               
            ELSE
                DBMS_OUTPUT.PUT_LINE('The column effective_date no already exists');
            END IF;
        end loop;
end;