create or replace procedure test1 is CURSOR C1 IS SELECT THREAD_ID FROM PERSON_APPLY_FOLDER WHERE THREAD_DT<SYSDATE-31 ORDER BY THREAD_ID DESC; THID PERSON_APPLY_FOLDER.THREAD_ID%TYPE; TCOUNT INT; BEGIN OPEN C1; LOOP FETCH C1 INTO THID; IF (C1%FOUND) THEN SELECT COUNT(p.THREAD_ID) INTO TCOUNT FROM PERSON_APPLY_FOLDER P,PERSON_APPLY_FOLDER C WHERE P.THREAD_ID =THID AND P.PARENT_THREAD=C.PARENT_THREAD AND C.THREAD_DT>=SYSDATE-31; IF TCOUNT=0 THEN DELETE FROM PERSON_APPLY_FOLDER WHERE THREAD_ID=THID; END IF; END IF; END LOOP; CLOSE C1; COMMIT; END; /
CURSOR C1 IS
SELECT THREAD_ID FROM PERSON_APPLY_FOLDER WHERE THREAD_DT<SYSDATE-31 ORDER BY THREAD_ID DESC;
THID PERSON_APPLY_FOLDER.THREAD_ID%TYPE;
TCOUNT INT;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO THID;
IF (C1%FOUND) THEN
SELECT COUNT(p.THREAD_ID) INTO TCOUNT FROM PERSON_APPLY_FOLDER P,PERSON_APPLY_FOLDER C WHERE P.THREAD_ID =THID AND P.PARENT_THREAD=C.PARENT_THREAD AND C.THREAD_DT>=SYSDATE-31;
IF TCOUNT=0 THEN
DELETE FROM PERSON_APPLY_FOLDER WHERE THREAD_ID=THID;
END IF;
END IF;
END LOOP;
CLOSE C1;
COMMIT;
END;
/