从ID列中找出不连续的ID号,并打印出来。
DECLARE
V_ID VARCHAR2(20);
V_NAME VARCHAR2(20);
V_DATE DATE;
V_STARTVALUE NUMBER;
BEGIN
V_STARTVALUE := 1;
FOR IN_DEPT_USERNUM IN (SELECT ID
FROM BO_CRM_CLIENT_SERDPT
WHERE CREATEUSER = 'admin'
AND CREATEDATE >
TO_DATE('2018/12/19', 'yyyy/mm/dd')
AND CREATEDATE <
TO_DATE('2018/12/29', 'yyyy/mm/dd')
ORDER BY ID) LOOP
BEGIN
IF V_STARTVALUE - IN_DEPT_USERNUM.ID < -1 THEN
DBMS_OUTPUT.PUT_LINE(V_STARTVALUE);
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20000,
'E-001(**):校验BO_CRM_CLIENT_SERDPT表中的ID不连续,找出跳号的ID!');
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20000,
'E-001(**):校验BO_CRM_CLIENT_SERDPT表中的ID不连续,找出跳号的ID!异常,原因:' ||
SQLERRM);
END;
V_STARTVALUE := IN_DEPT_USERNUM.ID;
END LOOP;
END;