批量处理sql表

1.批量CRUD表字段

DECLARE
  V_SQL        VARCHAR2(2000);
  V_TABLE_NAME VARCHAR2(30);
  CURSOR C1 IS
 -- 查询当前用户下(ZFPT40_STATISTIC_ANALYSIS)的所有表
 --SELECT TABLE_NAME FROM all_tables a WHERE a.OWNER = upper('ZFPT40_STATISTIC_ANALYSIS');
 -- 查询ZFPT40_STATISTIC_ANALYSIS用户中所有的表(表中有YEAR字段的)
    SELECT
     TABLE_NAME 
    FROM
     all_tables a 
    WHERE
     a.OWNER = upper( 'ZFPT40_STATISTIC_ANALYSIS' ) 
     AND EXISTS ( SELECT table_name FROM user_tab_columns WHERE column_name = 'YEAR' AND table_name = a.table_name );
BEGIN
  OPEN C1;
  LOOP
    -- 提取一行数据到c1
    FETCH C1
      INTO V_TABLE_NAME;
    -- 判读是否提取到值，没取到值就退出
    -- 取到值c_job%notfound 是false 
    -- 取不到值c_job%notfound 是true
    EXIT WHEN C1%NOTFOUND;
    -- 批量新增字段
    V_SQL := 'ALTER TABLE ZFPT40_STATISTIC_ANALYSIS.' || V_TABLE_NAME || ' ADD TIME_FLAG VARCHAR2(10)';
    EXECUTE IMMEDIATE V_SQL;
    -- 批量赋予字段注释
    --V_SQL := 'COMMENT ON COLUMN ZFPT40_STATISTIC_ANALYSIS.' || V_TABLE_NAME || '.TIME_FLAG IS ''中文注释(需要两个单引号引起来)''' ;
    --EXECUTE IMMEDIATE V_SQL;
    -- 批量更新数据内容
    --V_SQL := 'UPDATE ' || V_TABLE_NAME || ' SET TIME_FLAG = ''2023_1Q'' WHERE TIME_FLAG IS NULL';
    --EXECUTE IMMEDIATE V_SQL;
    -- 批量删除
    --V_SQL := 'ALTER TABLE ZFPT40_STATISTIC_ANALYSIS.' || V_TABLE_NAME || ' DROP (TIME_FLAG)';
    --EXECUTE IMMEDIATE V_SQL;
   -- 批量将某个字段内容清空
   --V_SQL := 'UPDATE ZFPT40_STATISTIC_ANALYSIS.' || V_TABLE_NAME || ' SET YEAR = NULL WHERE YEAR IS NOT NULL';
   --EXECUTE IMMEDIATE V_SQL;
  END LOOP; --关闭游标
  CLOSE C1;
END;

2. 查询拥有某些字段的所有表

-- 查询ZFPT40_STATISTIC_ANALYSIS用户中所有的表(表中有YEAR字段的)
SELECT
 TABLE_NAME 
FROM
 all_tables a 
WHERE
 a.OWNER = upper( 'ZFPT40_STATISTIC_ANALYSIS' ) 
 AND EXISTS ( SELECT table_name FROM user_tab_columns WHERE column_name = 'YEAR' AND table_name = a.table_name )

3.sql脚本中的中文注释

需要用两个单引号引起来即可

eg:  ''中文注释''