首页 手机软件 手机游戏 单机游戏 资讯文章 专题下载 游戏合集 文章合集 php下载 php教程 电脑软件
+ -
当前位置：首页 → 问答吧 → 存储过程报错

存储过程报错

时间：2011-09-02

来源：互联网

CREATE OR REPLACE PROCEDURE NEW_PRO_CRECT(SUNITCODE IN CHAR,
  SPOLICYNO IN CHAR,
  SDAMAGEDATE IN CHAR,
  SCOUNTS OUT INTEGER) AS
  TEMP INTEGER;
  COUNTS INTEGER;
  ADDSDATE DATE;
  ADDEDATE DATE;
  SDATE DATE;
  EDATE DATE;
BEGIN
  SELECT B.IFEXPANDTIME,B.ADDSTARTDATE,B.ADDENDDATE,A.STARTDATE,A.ENDDATE ---此行报错

  INTO COUNTS,ADDSDATE,ADDEDATE,SDATE,EDATE
  FROM P_PUBLIC_POLICY A,P_CONSTRUCT B
  WHERE A.ENDDATE IS NOT NULL
  AND A.APPLYNO=B.APPLYNO
  AND A.UNITCODE=B.UNITCODE
  AND A.APPLYENDORSENO=B.APPLYENDORSENO
  AND A.STATUS = '3'
  AND A.GENERATEORDER =
  (SELECT MIN(D.GENERATEORDER)
  FROM P_PUBLIC_POLICY D
  WHERE D.UNITCODE = A.UNITCODE
  AND D.APPLYNO = A.APPLYNO
  AND D.EFFECTDATE <= TO_DATE(SDAMAGEDATE, 'YYYY-MM-DD')
  AND (D.STATUS || D.AUDITSTATUS != '07' AND
  D.STATUS || D.AUDITSTATUS != '08' AND
  D.STATUS || D.AUDITSTATUS != '50' AND
  D.STATUS || D.AUDITSTATUS != '55' AND
  D.STATUS || D.AUDITSTATUS != '40')
  AND GENERATEORDER >= 0)
  AND A.EFFECTDATE <= TO_DATE(SDAMAGEDATE, 'YYYY-MM-DD')
  AND A.UNITCODE = SUNITCODE
  AND A.POLICYNO = SPOLICYNO;

  IF (COUNTS = 0) THEN ---- 当扩展保证期为否按照其他险种的控制
  SELECT COUNT(*)
  INTO TEMP
  FROM P_PUBLIC_POLICY A
  WHERE A.STARTDATE <= TO_DATE(SDAMAGEDATE, 'YYYY-MM-DD')
  AND A.ENDDATE >= TO_DATE(SDAMAGEDATE, 'YYYY-MM-DD')
  AND A.STATUS = '3'
  AND A.GENERATEORDER =
  (SELECT MIN(D.GENERATEORDER)
  FROM P_PUBLIC_POLICY D
  WHERE D.UNITCODE = A.UNITCODE
  AND D.APPLYNO = A.APPLYNO
  AND D.EFFECTDATE <= TO_DATE(SDAMAGEDATE, 'YYYY-MM-DD')
  AND (D.STATUS || D.AUDITSTATUS != '07' AND
  D.STATUS || D.AUDITSTATUS != '08' AND
  D.STATUS || D.AUDITSTATUS != '50' AND
  D.STATUS || D.AUDITSTATUS != '55' AND
  D.STATUS || D.AUDITSTATUS != '40')
  AND GENERATEORDER >= 0)
  AND A.EFFECTDATE <= TO_DATE(SDAMAGEDATE, 'YYYY-MM-DD')
  AND A.UNITCODE = SUNITCODE
  AND A.POLICYNO = SPOLICYNO;
  ELSIF(COUNTS = 1 and ADDSDATE<SDATE and ADDEDATE<EDATE) THEN ----
  SELECT COUNT(*)
  INTO TEMP
  FROM P_PUBLIC_POLICY A,P_CONSTRUCT B
  WHERE B.ADDSTARTDATE <= TO_DATE(SDAMAGEDATE, 'YYYY-MM-DD')
  AND A.ENDDATE >= TO_DATE(SDAMAGEDATE, 'YYYY-MM-DD')
  AND A.STATUS = '3'
  AND A.GENERATEORDER =
  (SELECT MIN(D.GENERATEORDER)
  FROM P_PUBLIC_POLICY D
  WHERE D.UNITCODE = A.UNITCODE
  AND D.APPLYNO = A.APPLYNO
  AND D.EFFECTDATE <= TO_DATE(SDAMAGEDATE, 'YYYY-MM-DD')
  AND (D.STATUS || D.AUDITSTATUS != '07' AND
  D.STATUS || D.AUDITSTATUS != '08' AND
  D.STATUS || D.AUDITSTATUS != '50' AND
  D.STATUS || D.AUDITSTATUS != '55' AND
  D.STATUS || D.AUDITSTATUS != '40')
  AND GENERATEORDER >= 0)
  AND A.EFFECTDATE <= TO_DATE(SDAMAGEDATE, 'YYYY-MM-DD')
  AND A.UNITCODE = SUNITCODE
  AND A.POLICYNO = SPOLICYNO;
  ELSIF(COUNTS = 1 and ADDSDATE>SDATE and ADDEDATE<EDATE) THEN
  SELECT COUNT(*)
  INTO TEMP
  FROM P_PUBLIC_POLICY A,P_CONSTRUCT B
  WHERE A.STARTDATE <= TO_DATE(SDAMAGEDATE, 'YYYY-MM-DD')
  AND A.ENDDATE >= TO_DATE(SDAMAGEDATE, 'YYYY-MM-DD')
  AND A.STATUS = '3'
  AND A.GENERATEORDER =
  (SELECT MIN(D.GENERATEORDER)
  FROM P_PUBLIC_POLICY D
  WHERE D.UNITCODE = A.UNITCODE
  AND D.APPLYNO = A.APPLYNO
  AND D.EFFECTDATE <= TO_DATE(SDAMAGEDATE, 'YYYY-MM-DD')
  AND (D.STATUS || D.AUDITSTATUS != '07' AND
  D.STATUS || D.AUDITSTATUS != '08' AND
  D.STATUS || D.AUDITSTATUS != '50' AND
  D.STATUS || D.AUDITSTATUS != '55' AND
  D.STATUS || D.AUDITSTATUS != '40')
  AND GENERATEORDER >= 0)
  AND A.EFFECTDATE <= TO_DATE(SDAMAGEDATE, 'YYYY-MM-DD')
  AND A.UNITCODE = SUNITCODE
  AND A.POLICYNO = SPOLICYNO;
  ELSIF(COUNTS = 1 and ADDSDATE<SDATE and ADDEDATE>EDATE) THEN
  SELECT COUNT(*)
  INTO TEMP
  FROM P_PUBLIC_POLICY A,P_CONSTRUCT B
  WHERE B.ADDSTARTDATE <= TO_DATE(SDAMAGEDATE, 'YYYY-MM-DD')
  AND B.ADDENDDATE >= TO_DATE(SDAMAGEDATE, 'YYYY-MM-DD')
  AND A.STATUS = '3'
  AND A.GENERATEORDER =
  (SELECT MIN(D.GENERATEORDER)
  FROM P_PUBLIC_POLICY D
  WHERE D.UNITCODE = A.UNITCODE
  AND D.APPLYNO = A.APPLYNO
  AND D.EFFECTDATE <= TO_DATE(SDAMAGEDATE, 'YYYY-MM-DD')
  AND (D.STATUS || D.AUDITSTATUS != '07' AND
  D.STATUS || D.AUDITSTATUS != '08' AND
  D.STATUS || D.AUDITSTATUS != '50' AND
  D.STATUS || D.AUDITSTATUS != '55' AND
  D.STATUS || D.AUDITSTATUS != '40')
  AND GENERATEORDER >= 0)
  AND A.EFFECTDATE <= TO_DATE(SDAMAGEDATE, 'YYYY-MM-DD')
  AND A.UNITCODE = SUNITCODE
  AND A.POLICYNO = SPOLICYNO;
  ELSIF(COUNTS = 1 and ADDSDATE>SDATE and ADDEDATE>EDATE) THEN
  SELECT COUNT(*)
  INTO TEMP
  FROM P_PUBLIC_POLICY A,P_CONSTRUCT B
  WHERE A.STARTDATE <= TO_DATE(SDAMAGEDATE, 'YYYY-MM-DD')
  AND B.ADDENDDATE >= TO_DATE(SDAMAGEDATE, 'YYYY-MM-DD')
  AND A.STATUS = '3'
  AND A.GENERATEORDER =
  (SELECT MIN(D.GENERATEORDER)
  FROM P_PUBLIC_POLICY D
  WHERE D.UNITCODE = A.UNITCODE
  AND D.APPLYNO = A.APPLYNO
  AND D.EFFECTDATE <= TO_DATE(SDAMAGEDATE, 'YYYY-MM-DD')
  AND (D.STATUS || D.AUDITSTATUS != '07' AND
  D.STATUS || D.AUDITSTATUS != '08' AND
  D.STATUS || D.AUDITSTATUS != '50' AND
  D.STATUS || D.AUDITSTATUS != '55' AND
  D.STATUS || D.AUDITSTATUS != '40')
  AND GENERATEORDER >= 0)
  AND A.EFFECTDATE <= TO_DATE(SDAMAGEDATE, 'YYYY-MM-DD')
  AND A.UNITCODE = SUNITCODE
  AND A.POLICYNO = SPOLICYNO;
  END IF;
  SCOUNTS := TEMP;
END;


报ORA-01403：未找到数据

作者: gang9527999   发布时间: 2011-09-02

把into去掉，贴出来查询。一条也没有就会报NO_DATA_FOUND

作者: forgetsam   发布时间: 2011-09-02

应该是into的检索SQL没有检索结果
检查你的那个检索语句是否有结果

作者: wallace_jjh   发布时间: 2011-09-02

楼主能否贴出你的表结构以供参考，关联条件太多，看得不是太清楚。
从整体上看，应该是条件下得有问题，楼主可以变更条件查询看看

作者: langcai1981   发布时间: 2011-09-02

SQL code 

DECLARE S NUMBER;
BEGIN 
  SELECT COUNT(1) INTO S 
    FROM P_PUBLIC_POLICY A,P_CONSTRUCT B
  WHERE A.ENDDATE IS NOT NULL
  AND A.APPLYNO=B.APPLYNO
  AND A.UNITCODE=B.UNITCODE
  AND A.APPLYENDORSENO=B.APPLYENDORSENO
  AND A.STATUS = '3'
  AND A.GENERATEORDER =
  (SELECT MIN(D.GENERATEORDER)
  FROM P_PUBLIC_POLICY D
  WHERE D.UNITCODE = A.UNITCODE
  AND D.APPLYNO = A.APPLYNO
  AND D.EFFECTDATE <= TO_DATE(SDAMAGEDATE, 'YYYY-MM-DD')
  AND (D.STATUS || D.AUDITSTATUS != '07' AND
  D.STATUS || D.AUDITSTATUS != '08' AND
  D.STATUS || D.AUDITSTATUS != '50' AND
  D.STATUS || D.AUDITSTATUS != '55' AND
  D.STATUS || D.AUDITSTATUS != '40')
  AND GENERATEORDER >= 0)
  AND A.EFFECTDATE <= TO_DATE(SDAMAGEDATE, 'YYYY-MM-DD')
  AND A.UNITCODE = SUNITCODE
  AND A.POLICYNO = SPOLICYNO;
  IF S > 0 THEN 
  BEGIN 
    SELECT B.IFEXPANDTIME,B.ADDSTARTDATE,B.ADDENDDATE,A.STARTDATE,A.ENDDATE 
  INTO COUNTS,ADDSDATE,ADDEDATE,SDATE,EDATE
  FROM P_PUBLIC_POLICY A,P_CONSTRUCT B
  WHERE A.ENDDATE IS NOT NULL
  AND A.APPLYNO=B.APPLYNO
  AND A.UNITCODE=B.UNITCODE
  AND A.APPLYENDORSENO=B.APPLYENDORSENO
  AND A.STATUS = '3'
  AND A.GENERATEORDER =
  (SELECT MIN(D.GENERATEORDER)
  FROM P_PUBLIC_POLICY D
  WHERE D.UNITCODE = A.UNITCODE
  AND D.APPLYNO = A.APPLYNO
  AND D.EFFECTDATE <= TO_DATE(SDAMAGEDATE, 'YYYY-MM-DD')
  AND (D.STATUS || D.AUDITSTATUS != '07' AND
  D.STATUS || D.AUDITSTATUS != '08' AND
  D.STATUS || D.AUDITSTATUS != '50' AND
  D.STATUS || D.AUDITSTATUS != '55' AND
  D.STATUS || D.AUDITSTATUS != '40')
  AND GENERATEORDER >= 0)
  AND A.EFFECTDATE <= TO_DATE(SDAMAGEDATE, 'YYYY-MM-DD')
  AND A.UNITCODE = SUNITCODE
  AND A.POLICYNO = SPOLICYNO;
  END;
  ELSE
  BEGIN
   --其他操作或不操作。
  END;
  END IF;

END

作者: hao1hao2hao3   发布时间: 2011-09-02

相关阅读 更多

热门阅读

热门下载

更多
关于本站 免责申明 联系我们

Copyright 2006-2020 php爱好者(phpfans.net) All Rights Reserved.备案号：湘ICP备2023016114号-2

本站为非盈利性网站，不接受任何广告。本站所有资源均由网友上传，如有侵权，请发邮件至 [email protected] phpfans.net