批量添加字段
以下例子代码添加三个字段
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;