ITPUB论坛-中国最专业的IT技术社区

 找回密码
 注册
查看: 511|回复: 3

一个可能发生死循环的存储过程

[复制链接]
论坛徽章:
2
ITPUB15周年纪念
日期:2017-05-08 10:50:44罗罗诺亚·索隆
日期:2017-05-23 13:28:33
发表于 2017-8-11 14:26 | 显示全部楼层 |阅读模式
本帖最后由 LeoCp 于 2017-8-11 14:31 编辑

CREATE OR REPLACE PROCEDURE "ATS_GUEST"."ATS_GET_DATA_PATIENT_INFO"(PARM_IDENTITY_NO  IN VARCHAR2, --输入:身份证号
                                                                    PARM_INSURANCE_NO IN VARCHAR2, --输入:医保卡号
                                                                    PARM_SSN_NO       IN VARCHAR2, --输入: 社保卡号
                                                                    PARM_HOSPITAL_ID  IN VARCHAR2, --输入:登录用户所在医院域ID
                                                                    IF_OTHER_HOSPITAL IN NUMBER, --输入: 0-所有医院,1-非本院数据,2-本院数据
                                                                    IF_HAVE_DATA      OUT NUMBER --输出:0-未找到数据,1-仅有报告数据,2-有报告/影像数据
                                                                    ) AS
  PARM_GLOBAL_ID          VARCHAR2(256); --临时存放GLOBAL_ID
  PARM_DOMAIN_ID          VARCHAR2(256); --病人ID对应的域ID
  PARM_DOCUMENT_COUNT     NUMBER; --临时存放报告数量
  PARM_IMAGE_COUNT        NUMBER; --临时存放影像数量
  PARM_DOCUMENT_ALL_COUNT NUMBER; --临时存放所有报告数量
  PARM_IMAGE_ALL_COUNT    NUMBER; --临时存放所有影像数量
  PARM_SQL                VARCHAR2(2000); --临时存放SQL语句
  PARM_CURSOR             SYS_REFCURSOR; --使用系统预定义的游标
BEGIN
  PARM_DOCUMENT_COUNT := 0;
  PARM_IMAGE_COUNT := 0;
  PARM_DOCUMENT_ALL_COUNT := 0;
  PARM_IMAGE_ALL_COUNT := 0;
  IF_HAVE_DATA := 0;
  --根据输入参数组合SQL语句,获取GLOBAL_ID,存在返回多个结果的可能
  PARM_SQL := 'SELECT GLOBAL_ID,UNIVERSAL_IDENTIFIER_DOMAIN FROM ATS_GUEST.PERSON_INFO WHERE ROWNUM<10';
  IF (PARM_IDENTITY_NO IS NOT NULL) THEN PARM_SQL := PARM_SQL || ' AND IDENTITY_NO=''' || PARM_IDENTITY_NO || '''';
  END IF;
  IF (PARM_INSURANCE_NO IS NOT NULL) THEN PARM_SQL := PARM_SQL || ' AND INSURANCE_NO=''' || PARM_INSURANCE_NO || '''';
  END IF;
  IF (PARM_SSN_NO IS NOT NULL) THEN PARM_SQL := PARM_SQL || ' AND SSN=''' || PARM_SSN_NO || '''';
  END IF;

  --必须有一个条件不为空时才执行下列结果
  IF (PARM_IDENTITY_NO IS NOT NULL or PARM_INSURANCE_NO IS NOT NULL or PARM_SSN_NO IS NOT NULL) THEN
    --根据组合好的语句查询GLOBAL_ID 赋给游标
    OPEN PARM_CURSOR FOR PARM_SQL;
    LOOP
      FETCH PARM_CURSOR INTO PARM_GLOBAL_ID, PARM_DOMAIN_ID;
      EXIT WHEN PARM_CURSOR%NOTFOUND;
      CASE IF_OTHER_HOSPITAL
        WHEN 0 THEN
          SELECT COUNT(*) INTO PARM_DOCUMENT_COUNT FROM ATS_GUEST.DOCUMENT_INFO
          WHERE GLOBAL_ID = PARM_GLOBAL_ID;
          SELECT COUNT(*) INTO PARM_IMAGE_COUNT FROM ATS_GUEST.IMAGE_INFO_GET_DATA
          WHERE PATIENT_DOMAIN_ID = PATIENT_DOMAIN_ID
          AND SUBSTR(GLOBAL_ID, 0, 36) = PARM_GLOBAL_ID
          AND ACCESSION_NUMBER IN (SELECT ACCESSIONNUM FROM ATS_GUEST.DOCUMENT_INFO WHERE GLOBAL_ID = PARM_GLOBAL_ID);
        
        WHEN 1 THEN
          SELECT COUNT(*) INTO PARM_DOCUMENT_COUNT FROM ATS_GUEST.DOCUMENT_INFO
          WHERE GLOBAL_ID = PARM_GLOBAL_ID
          AND PATIENT_DOMAIN_ID NOT IN (SELECT ID_CODE FROM ATS_DICT.HOSPITAL_DOMAIN_DICT
             WHERE HOSPITAL_DOMAIN = PARM_HOSPITAL_ID)
          AND PATIENT_DOMAIN_ID IS NOT NULL;
        
          SELECT COUNT(*) INTO PARM_IMAGE_COUNT
          FROM ATS_GUEST.IMAGE_INFO_GET_DATA WHERE PATIENT_DOMAIN_ID = PATIENT_DOMAIN_ID
          AND SUBSTR(GLOBAL_ID, 0, 36) = PARM_GLOBAL_ID
          AND ACCESSION_NUMBER IN (SELECT ACCESSIONNUM
          FROM ATS_GUEST.DOCUMENT_INFO
          WHERE GLOBAL_ID = PARM_GLOBAL_ID)
          AND PATIENT_DOMAIN_ID NOT IN
                 (SELECT ID_CODE FROM ATS_DICT.HOSPITAL_DOMAIN_DICT
                  WHERE HOSPITAL_DOMAIN = PARM_HOSPITAL_ID)
          AND PATIENT_DOMAIN_ID IS NOT NULL;
        
        WHEN 2 THEN
          SELECT COUNT(*)
            INTO PARM_DOCUMENT_COUNT
            FROM ATS_GUEST.DOCUMENT_INFO
           WHERE GLOBAL_ID = PARM_GLOBAL_ID
             AND PATIENT_DOMAIN_ID IN
                 (SELECT ID_CODE
                    FROM ATS_DICT.HOSPITAL_DOMAIN_DICT
                   WHERE HOSPITAL_DOMAIN = PARM_HOSPITAL_ID);
        
          SELECT COUNT(*)
            INTO PARM_IMAGE_COUNT
            FROM ATS_GUEST.IMAGE_INFO_GET_DATA
            WHERE PATIENT_DOMAIN_ID = PATIENT_DOMAIN_ID AND SUBSTR(GLOBAL_ID, 0, 36) = PARM_GLOBAL_ID
            AND ACCESSION_NUMBER IN
                 (SELECT ACCESSIONNUM FROM ATS_GUEST.DOCUMENT_INFO
                  WHERE GLOBAL_ID = PARM_GLOBAL_ID)
            AND PATIENT_DOMAIN_ID IN
                 (SELECT ID_CODE
                    FROM ATS_DICT.HOSPITAL_DOMAIN_DICT
                   WHERE HOSPITAL_DOMAIN = PARM_HOSPITAL_ID);
      END CASE;
      PARM_DOCUMENT_ALL_COUNT := PARM_DOCUMENT_ALL_COUNT + PARM_DOCUMENT_COUNT;
      PARM_IMAGE_ALL_COUNT    := PARM_IMAGE_ALL_COUNT + PARM_IMAGE_COUNT;
    END LOOP;
    CLOSE PARM_CURSOR;
  END IF;
  IF PARM_DOCUMENT_ALL_COUNT = 0 AND PARM_IMAGE_ALL_COUNT = 0 THEN
    IF_HAVE_DATA := 0;
  ELSIF PARM_DOCUMENT_ALL_COUNT > 0 AND PARM_IMAGE_ALL_COUNT = 0 THEN
    IF_HAVE_DATA := 1;
  ELSIF PARM_DOCUMENT_ALL_COUNT > 0 AND PARM_IMAGE_ALL_COUNT > 0 THEN
    IF_HAVE_DATA := 2;
  ELSE
    IF_HAVE_DATA := 0;
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    IF_HAVE_DATA := 0;
END;

论坛徽章:
2
ITPUB15周年纪念
日期:2017-05-08 10:50:44罗罗诺亚·索隆
日期:2017-05-23 13:28:33
 楼主| 发表于 2017-8-11 14:30 | 显示全部楼层
本帖最后由 LeoCp 于 2017-8-11 14:34 编辑

以前写的一个存储过程,最近突然出现死循环,在AWR报告里显示每秒里边的select语句能执行38次,算上延迟基本等于死循环了。
由于不可能全部改成index unique scan,所以导致出现很严重的Latch:cache buffer chain的等待,CPU和内存全部暴涨到100%。
但是我思来想去也不知道哪种条件下可能有死循环,特来求助~~

使用道具 举报

回复
论坛徽章:
10
2012新春纪念徽章
日期:2012-01-04 11:56:19罗罗诺亚·索隆
日期:2017-08-30 13:50:35马上有钱
日期:2015-02-09 13:21:28蛋疼蛋
日期:2014-12-01 15:24:16比亚迪
日期:2013-09-02 15:14:36优秀写手
日期:2014-12-24 06:00:142013年新春福章
日期:2013-02-25 14:51:24蛋疼蛋
日期:2012-11-18 10:54:53ITPUB 11周年纪念徽章
日期:2012-10-10 13:11:14技术图书徽章
日期:2017-11-10 14:15:52
发表于 2017-8-11 16:35 | 显示全部楼层
跟死循环有什么关系   主要看下你的count语句

使用道具 举报

回复
论坛徽章:
480
榜眼
日期:2015-09-09 10:34:21秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12状元
日期:2015-11-23 10:04:09举人
日期:2015-11-23 10:04:09
发表于 2017-8-11 21:26 | 显示全部楼层
这种代码如果被TOM看见了是要挨骂的:
EXCEPTION
  WHEN OTHERS THEN
    IF_HAVE_DATA := 0;
END;

在ASKTOM网站上搜索“I hate your code"。
趁早赶快去掉,在EXCEPTION什么也不要写。

其他毛病也很多,比如拼SQL不用绑定变量,这会引起硬解析问题以及SQL注入攻击问题,难怪CPU会100%。

看起来只需检查数据是否存在,根本没必要用游标嵌套循环,想办法把你的需求用一个SQL实现。

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则

TOP技术积分榜 社区积分榜 徽章 电子杂志 团队 统计 虎吧 老博客 知识索引树 读书频道 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档 | IT博客
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛 | SAP ERP系统
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 网站律师 隐私政策 知识产权声明
京ICP备16024965号 北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表