ORACLE 创建表前判断是否已存在

1、判断是否用户数据表已存在，不存在则创建：

DECLARE v_cnt number;
BEGIN
  SELECT count(1) INTO v_cnt FROM dba_tables WHERE owner='ACT' AND TABLE_NAME='PHYSICALORDERPRESCRIPTIONMAP' ;
  IF v_cnt = 0  THEN
    EXECUTE immediate
      'CREATE TABLE "ACT"."PHYSICALORDERPRESCRIPTIONMAP" 
      (  "PHYSICALREGISTERID" NUMBER(10,0) NOT NULL ENABLE, 
      "PHYSICALREGISTERITEMID" NUMBER(10,0) NOT NULL ENABLE, 
      "ENCOUNTERID" NUMBER(10,0) NOT NULL ENABLE, 
      "ORDERPRESCRIPTIONID" NUMBER(10,0) NOT NULL ENABLE, 
      "AMOUNT" NUMBER(18,4), 
      "ATTACHAMOUNT" NUMBER(18,4), 
      "ISDELETED" NUMBER(1,0) DEFAULT 0, 
      "ROWVERSION" TIMESTAMP (6) DEFAULT systimestamp, 
      "PHYSICALPARTYEXAMLABELID" NUMBER(10,0), 
      "PHYSICALITEMCLASSIFICATIONFLAG" NUMBER(3,0) DEFAULT 0, 
      "COMPOSITEITEMID" NUMBER(10,0), 
      "PHYSICALREGISTERBALANCEID" NUMBER(10,0), 
      "PEREGISTERDATE" DATE, 
      "PESEQNO" NUMBER(10,0), 
      "STATUSCODEID" NUMBER(10,0) DEFAULT (0), 
    CONSTRAINT "PHYSICALORSMAP_PK" PRIMARY KEY ("PHYSICALREGISTERID", "PHYSICALREGISTERITEMID", "ENCOUNTERID", "ORDERPRESCRIPTIONID"))';
  END if;
end;
/

2、判断是否全局临时表已存在，不存在则创建：

DECLARE v_cnt number;
BEGIN
  SELECT count(1) INTO v_cnt FROM dba_tables WHERE owner='ACT' AND TABLE_NAME='TEMPPHYSICALOPACCOUNTLIST' ;
  IF v_cnt = 0  THEN
    EXECUTE immediate
    'CREATE GLOBAL TEMPORARY TABLE "ACT"."TEMPPHYSICALOPACCOUNTLIST" 
       (  "ACCOUNTLISTID" NUMBER(10,0), 
      "ORDERPRESCRIPTIONID" NUMBER(10,0), 
      "PARENTORDERPRESCRIPTIONID" NUMBER(10,0), 
      "ENCOUNTERID" NUMBER(10,0), 
      "ACCOUNTINGORGANIZATIONID" NUMBER(10,0), 
      "COSTORGANIZATIONID" NUMBER(10,0), 
      "ACCOUNTINGEMPLOYEEID" NUMBER(10,0), 
      "ACCOUNTINGDATETIME" DATE, 
      "OCCURDATE" DATE, 
      "APPENDDATETIME" DATE, 
      "EXECORGANIZATIONID" NUMBER(10,0), 
      "ACCOUNTINGDOCTORID" NUMBER(10,0), 
      "ORDERSOURCEFLAG" NUMBER(3,0), 
      "MAINCONSUMABLECODEID" NUMBER(10,0), 
      "MAINCONSUMABLEID" NUMBER(10,0), 
      "CONSUMABLECODEID" NUMBER(10,0), 
      "CONSUMABLEID" NUMBER(10,0), 
      "PRICE" NUMBER(18,4), 
      "QUANTITY" NUMBER(18,4), 
      "AMOUNT" NUMBER(18,4), 
      "FEEKINDID" NUMBER(10,0) DEFAULT -1, 
      "PAYPROPORTION" NUMBER(18,4) DEFAULT 10000, 
      "HASPAID" NUMBER(1,0) DEFAULT 0, 
      "ISFEEKINDFROMCALC" NUMBER(1,0) DEFAULT 0, 
      "STANDARDPRICE" NUMBER(18,4), 
      "ISDUPLICATEREMOVAL" NUMBER(1,0) DEFAULT 0, 
      "PHYSICALCLASSIFICATIONFLAG" NUMBER(10,0) DEFAULT 1, 
      "PHYSICALITEMCLASSIFICATIONFLAG" NUMBER(10,0) DEFAULT 0 ) ON COMMIT DELETE ROWS';
  END if;
end;
/

DECLARE
v_cnt number(10,0);
BEGIN
  SELECT count(1) INTO v_cnt FROM dba_tab_columns c WHERE c.owner='ACT' AND TABLE_NAME='TEMPPHYSICALOPACCOUNTLIST' AND c.column_name = 'ISDUPLICATEREMOVAL1';
  IF (v_cnt = 0) THEN  
     EXECUTE immediate 'ALTER TABLE "ACT"."TEMPPHYSICALOPACCOUNTLIST" ADD "ISDUPLICATEREMOVAL1" NUMBER(1,0) DEFAULT 0';
  END if;
END;
/

DECLARE
v_cnt number(10,0);
BEGIN
  SELECT count(1) INTO v_cnt FROM dba_tab_columns c WHERE c.owner='ACT' AND TABLE_NAME='TEMPPHYSICALOPACCOUNTLIST' AND c.column_name = 'PHYSICALCLASSIFICATIONFLAG';
  IF (v_cnt = 0) THEN  
     EXECUTE immediate 'ALTER TABLE "ACT"."TEMPPHYSICALOPACCOUNTLIST" ADD "PHYSICALCLASSIFICATIONFLAG" NUMBER(10,0) DEFAULT 1';
  END if;
END;
/

DECLARE
v_cnt number(10,0);
BEGIN
  SELECT count(1) INTO v_cnt FROM dba_tab_columns c WHERE c.owner='ACT' AND TABLE_NAME='TEMPPHYSICALOPACCOUNTLIST' AND c.column_name = 'PHYSICALITEMCLASSIFICATIONFLAG';
  IF (v_cnt = 0) THEN  
     EXECUTE immediate 'ALTER TABLE "ACT"."TEMPPHYSICALOPACCOUNTLIST" ADD "PHYSICALITEMCLASSIFICATIONFLAG" NUMBER(10,0) DEFAULT 0';
  END if;
END;
/