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;
/
