ORACLE可重复执行的脚本随记

一、创建表结构

declare
  vl_num        number(8);
  strSQL_CREATE varchar2(4000);
begin
  select count(1)
    into vl_num
    from tabs
   where table_name = upper('tabletest');
  if (vl_num = 0) then
    strSQL_CREATE := 'create table tabletest
(
        SERIALNO                  VARCHAR2(20) not null,  
        MAKEDATE                  DATE,
        MAKETIME                  VARCHAR2(20),
        MODITYDATE                DATE,
        MODITYTIME                VARCHAR2(20),
        STRINGDATA1               VARCHAR2(50),
        STRINGDATA2               VARCHAR2(50),
        STRINGDATA3               VARCHAR2(50),
        constraint NA_SERIALNO_PK primary key(SERIALNO)
)';
    EXECUTE IMMEDIATE strSQL_CREATE;
  end if;
end;

 

二、创建序列号

declare
 vl_num number(8);
 strSQL_CREATE varchar2(4000);
begin
   select count(1) into vl_num from user_sequences where sequence_name=upper('SEQ_GOALID');
  if (vl_num = 0) then
   strSQL_CREATE := 'create sequence seq_goalid
  minvalue 1000000
  maxvalue 9999999999999999999999999999
  start with 1000000
  increment by 1
  nocycle
  cache 20';
  EXECUTE IMMEDIATE strSQL_CREATE;
 end if;
end;

 

三、创建同义词

declare 
 vl_num number(8);
 strSQL_CREATE varchar2(4000);
 STRSQL_CREATEnew varchar2(4000);
begin 
  select count(1)  into vl_num from user_synonyms t where synonym_name='tabletest01';
  if (vl_num = 0) then
   strSQL_CREATE := ' Create  synonym tabletest01  for tatable.tabletest ';
   STRSQL_CREATEnew := 'grant select on tabletest01  to public';
  EXECUTE IMMEDIATE strSQL_CREATE;
   EXECUTE IMMEDIATE STRSQL_CREATEnew;
 end if;
end;