Rattlesnake Rattlesnake - 1 month ago 9
SQL Question

Why Do I not get the exact numbers of rows that I've fetched?

I'm learning PLSQL since 3 months. Now I'm practicing explicit cursors. I have written a simple cursor and I've used the attribute %ROWCOUNT for query 5 rows of employees but I received only 4 employees on screen. Why?

SET SERVEROUTPUT ON
DECLARE
CURSOR cur_emp IS
SELECT e.employee_id, e.last_name, d.department_id, d.department_name
FROM employees e
JOIN departments d
ON (e.department_id = d.department_id);

v_emp_id employees.employee_id%TYPE;
v_emp_name employees.last_name%TYPE;
v_dept_id departments.department_id%TYPE;
v_dept_name departments.department_name%TYPE;
BEGIN
OPEN cur_emp;
LOOP
FETCH cur_emp
INTO v_emp_id, v_emp_name, v_dept_id, v_dept_name;
EXIT WHEN cur_emp%ROWCOUNT = 5;
DBMS_OUTPUT.PUT_LINE('EMPLOYEE ID: ' || v_emp_id || ' EMPLOYEE: ' ||
v_emp_name || ' DEPARTMENT ID: ' || v_dept_id || ' DEPARTMENT NAME: ' ||
v_dept_name);
END LOOP;
CLOSE cur_emp;
END;
/


image

Answer

The loop first checks the condition, so to write 4 employees, check the rowcount of the next one and exit the loop you have to put ROWCOUNT > 5, ROWCOUNT = 6 or move the EXIT WHEN condition to after your call to DBMS_OUTPUT.

Comments