How to check if cursor returns any records in oracle?
It's not possible to check if the cursor returns records without opening it.
(see here)
So you can either have some fast query just to see if there are records (using count for example),
Or, you can do it like this:
CREATE OR REPLACE PROCEDURE SP_EMPLOYEE_LOOKUP_BY_EMP_ID( IN_USER_ID IN NUMBER, IN_EMPLOYEE_ID NUMBER, IN_HC_AS_ON_DATE VARCHAR2, emp_cursor OUT SYS_REFCURSOR) IS is_found_rec boolean := false; CURSOR employees IS SELECT * FROM EMPLOYEE e; BEGIN FOR employee IN employees LOOP is_found_rec := true; // do something END LOOP; if not is_found_rec then // do something else end if;END;
I like this way:
DECLARE CURSOR my_cur IS SELECT 'a' AS a FROM DUAL WHERE 1=1; my_rec my_cur%rowtype;BEGIN OPEN my_cur; LOOP FETCH my_cur INTO my_rec; IF my_cur%NOTFOUND THEN IF my_cur%ROWCOUNT=0 THEN -- do stuff when cursor empty DBMS_OUTPUT.PUT_LINE('NOTFOUND,RC=0' || '_' || my_cur%ROWCOUNT || '_' || my_rec.a); END IF; EXIT; ELSE -- do stuff when cursor not empty DBMS_OUTPUT.PUT_LINE('FOUND,RC>0' || '_' || my_cur%ROWCOUNT || '_' || my_rec.a); END IF; END LOOP; CLOSE MY_CUR;END;
Output when cursor is 1=1 (not empty):
FOUND,RC>0_1_a
Output when cursor is 1=0 (empty):
NOTFOUND,RC=0_0_