oracle中批量添加字段脚本

DECLARE
    v_col_exists NUMBER;
    create_time  varchar2(20) := 'CREATE_TIME';
    update_time  varchar2(20) := 'UPDATE_TIME';
    create_user  varchar2(20) := 'CREATE_USER';
    update_user  varchar2(20) := 'UPDATE_USER';
    v_sql        varchar2(4000);
begin

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

            IF (v_col_exists = 0) THEN
                v_sql := 'alter table ' || i.table_name || ' add ' || create_time || ' date';
                DBMS_OUTPUT.PUT_LINE(v_sql);
                execute immediate v_sql;
                v_sql := 'comment on column ' || i.table_name || '.' || create_time || ' 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 = update_time AND table_name = i.table_name;
            IF (v_col_exists = 0) THEN
                v_sql := 'alter table ' || i.table_name || ' add ' || update_time || ' date';
                DBMS_OUTPUT.PUT_LINE(v_sql);
                execute immediate v_sql;

                execute immediate 'comment on column ' || i.table_name || '.' || update_time || ' 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 = create_user AND table_name = i.table_name;
            IF (v_col_exists = 0) THEN
                v_sql := 'alter table ' || i.table_name || ' add ' || create_user || ' number(8)';
                DBMS_OUTPUT.PUT_LINE(v_sql);
                execute immediate v_sql;
                execute immediate 'comment on column ' || i.table_name || '.' || create_user || ' is ' || q'['创建人ID']';
            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 = update_user AND table_name = i.table_name;
            IF (v_col_exists = 0) THEN
                v_sql := 'alter table ' || i.table_name || ' add ' || update_user || ' number(8)';
                DBMS_OUTPUT.PUT_LINE(v_sql);
                execute immediate v_sql;
                execute immediate 'comment on column ' || i.table_name || '.' || update_user || ' is ' || q'['更新人ID']';
            ELSE
                DBMS_OUTPUT.PUT_LINE('The column effective_date already exists');
            END IF;
        end loop;
end;