Chr☆s Kwok 的技术笔记

.NET, C#, WPF, WCF, WF, .NetCore & LINQ ... I know how it works because I know why it works ...

博客园 首页 新随笔 订阅 管理
  130 随笔 :: 0 文章 :: 30 评论 :: 30万 阅读

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

 

posted on   Chr☆s  阅读(2072)  评论(0编辑  收藏  举报
(评论功能已被禁用)
相关博文:
阅读排行:
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
点击右上角即可分享
微信分享提示