Rattlesnake Rattlesnake - 1 month ago 5
SQL Question

Why Do not I received the exact numbers of rows that I've fetch?

I'm learning PLSQL since 3 months And now I'm practicing explicit cursors. I 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;
/


]1

Answer

The loop first check the condition, so, when write 4 employees, check the rowcount of the nextone, and exits the loop, you have to put ROWCOUNT > 5 or ROWCOUNT = 6