2018-11-21 oracle重复提交多个表

两种方式
第一种：开始和结尾加 begin end

begin
declare
      num   number;
begin
  --TMP_USP_PLAT_CZRYQUERY_RESULT
  select count(1) into num from user_tables where table_name = upper('TMP_USP_PLAT_CZRYQUERY_RESULT');
  if num > 0 then execute immediate
  'drop table TMP_USP_PLAT_CZRYQUERY_RESULT';
  end if;
  
  select count(1) into num from user_tables where table_name = upper('TMP_USP_PLAT_CZRYQUERY_RESULT');
  if num = 0 then execute immediate
   'create global temporary table TMP_USP_PLAT_CZRYQUERY_RESULT
  (
    zclsh   VARCHAR2(64),
    rybm    VARCHAR2(64),
    jgbm    VARCHAR2(64),
    jgjc    VARCHAR2(64)
  )on commit preserve rows';
  end if;
end;

 declare
  num number;
begin
 
 select count(1) into num from user_tables where table_name = upper('TMP_USP_QUERY_ZDMLDZ_PARA');
    if num = 0 then execute immediate'
create global temporary table TMP_USP_QUERY_ZDMLDZ_PARA
(
  jgbm  VARCHAR2(22),
  zlmc  VARCHAR2(64),
  zldm  VARCHAR2(36),
  jsnum VARCHAR2(10)
)on commit preserve rows
';end if;
end;
end;

第二种：如果声明多个变量，每个表加结尾斜杠 / ， declare声明 之前

declare
      num   number;
begin
  --TMP_USP_PLAT_CZRYQUERY_RESULT
  select count(1) into num from user_tables where table_name = upper('TMP_USP_PLAT_CZRYQUERY_RESULT');
  if num > 0 then execute immediate
  'drop table TMP_USP_PLAT_CZRYQUERY_RESULT';
  end if;
  
  select count(1) into num from user_tables where table_name = upper('TMP_USP_PLAT_CZRYQUERY_RESULT');
  if num = 0 then execute immediate
   'create global temporary table TMP_USP_PLAT_CZRYQUERY_RESULT
  (
    zclsh   VARCHAR2(64),
    rybm    VARCHAR2(64),
    jgbm    VARCHAR2(64),
    jgjc    VARCHAR2(64)
  )on commit preserve rows';
  end if;
end;
/
 declare
  num number;
begin
 select count(1) into num from user_tables where table_name = upper('TMP_USP_QUERY_ZDMLDZ_PARA');
    if num = 0 then execute immediate'
create global temporary table TMP_USP_QUERY_ZDMLDZ_PARA
(
  jgbm  VARCHAR2(22),
  zlmc  VARCHAR2(64),
  zldm  VARCHAR2(36),
  jsnum VARCHAR2(10)
)on commit preserve rows
';end if;
end;
/