存储过程报错
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：未找到数据
------解决方案--------------------
应该是into的检索SQL没有检索结果
检查你的那个检索语句是否有结果
[解决办法]
- 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