存储过程（四）批量建表

create or replace procedure CREATE_USER_HISTORY_TABLE is
vSql varchar2(2000);
vTable varchar2(50);
vSel varchar2(2000);
vnum number;
vcomment varchar(2000);
vcomment1 varchar(2000);
vcomment2 varchar(2000);
vcomment3 varchar(2000);
vcomment4 varchar(2000);
vcomment5 varchar(2000);
vcomment6 varchar(2000);
vcomment7 varchar(2000);
vcomment8 varchar(2000);
vcomment9 varchar(2000);
vcomment10 varchar(2000);
vcomment11 varchar(2000);
vcomment12 varchar(2000);
vgrant varchar2(100);
begin
  vgrant:='grant create table to testuser';
  execute immediate vgrant;
  
for m in 0..9 loop
  for n in 0..9 loop 
   
    vTable := 'user_info_history_'||m||'_'||n;
    vSel := 'select count(0) from user_tables where table_name = upper(''' || vTable||''')' ;
    execute immediate vSel into vnum ;
    
    
    if vnum > 0 then 
       execute immediate 'drop table ' || vTable;    
    end if;
   
   vSql := 'create table ' || vTable || '(
   id           NUMBER(16) not null,
   userid           NUMBER(16) not null,
  idtype       VARCHAR2(40) not null,
  idnum        VARCHAR2(40) not null,
  username     VARCHAR2(30),
  phonenum     VARCHAR2(30),
  createtime   DATE,
  lastmodified DATE,
  action       VARCHAR2(40),
  modifier     VARCHAR2(40),
  field1       VARCHAR2(100),
  field2       VARCHAR2(100),
  field3       VARCHAR2(100) ,
    constraint userHisPrimaryKey'||m||n||' primary key (ID) )';  
  
         vcomment:='comment on column '||vTable||'.id is ''主键'' ';
         vcomment1:='comment on column '||vTable||'.idtype is ''证件类型'' ';
         vcomment2:='comment on column  '||vTable||'.idtype  is ''证件类型'' ';
         vcomment3:='comment on column  '||vTable||'.idnum  is ''证件号''';
         vcomment4:='comment on column  '||vTable||'.username  is ''姓名''';
         vcomment5:='comment on column  '||vTable||'.phonenum  is ''手机号''';
         vcomment6:='comment on column  '||vTable||'.createtime  is ''创建时间''';
         vcomment7:='comment on column  '||vTable||'.lastmodified  is ''修改时间'' ';
         vcomment8:='comment on column  '||vTable||'.action  is ''修改动作''';
         vcomment9:='comment on column  '||vTable||'.modifier  is ''修改者'' ';
         vcomment10:='comment on column  '||vTable||'.field1  is ''备用字段1''';
         vcomment11:='comment on column  '||vTable||'.field2  is ''备用字段2''';
         vcomment12:='comment on column  '||vTable||'.field3  is ''备用字段3''';
   
     execute immediate vSql;
     execute immediate vcomment;   
     execute immediate vcomment1; 
     execute immediate vcomment2; 
     execute immediate vcomment3; 
     execute immediate vcomment4; 
     execute immediate vcomment5; 
     execute immediate vcomment6;   
     execute immediate vcomment7; 
     execute immediate vcomment8; 
     execute immediate vcomment9; 
     execute immediate vcomment10; 
     execute immediate vcomment11;
     execute immediate vcomment12;
  end loop;
end loop;

end CREATE_USER_HISTORY_TABLE;
/