sys_addcol.sql

在产品升级过程中

CREATE OR REPLACE procedure sys_addcol

as

   --在指定的表中插入‘PLACEPOINTID’列

   --扫描系统视图中的用户表信息

   cursor cur_table is

      select table_name      

      from user_all_tables

      where tablespace_name = 'USERS' and

            (substr(table_name,1,4) <> 'SYS_' and

             substr(table_name,1,4) <> 'PUB_' and

             substr(table_name,1,4) <> 'PMC_' and

             substr(table_name,1,2) <> 'QR' and

             substr(table_name,1,4) <> 'IBIS');

    cur_1 cur_table%rowtype;

    

    --扫描指定用户表所对应的列信息

    cursor cur_colname is

       select count(column_name) colnum

       from user_col_comments

       where table_name = cur_1.table_name and

             column_name = 'PLACEPOINTID';    

    

    m_dynsql varchar2(200);  --动态sql语句        

    m_colnum number;

begin

   open cur_table;   

   loop

      fetch cur_table into cur_1;

      if cur_table%notfound then

         exit;

      end if;

      m_colnum := 0;

      open cur_colname;

      fetch cur_colname into m_colnum;

      close cur_colname;

      --在指定的表中插入‘sendflag’列

      if m_colnum < 1 then

         m_dynsql := 'ALTER TABLE '||cur_1.table_name||' add (PLACEPOINTID NUMBER(10,0))';

         EXECUTE IMMEDIATE m_dynsql;

      end if;

      

   end loop;

   close cur_table;

end;
/