SQL Cursor
A SQL cursor is a private Oracle SQL working area. There are two types of SQL cursor: implicit or explicit cursor. The implicit cursor is used by Oracle server to test and parse the SQL statements and the explicit cursors are declared by the programmers.
Using the implicit cursor, we can test the outcome of SQL statements in PL/SQL. For example, • SQL%ROWCOUNT, return the number of rows affected; • SQL%FOUND, a BOOLEAN attribute indicating whether the recent SQL statement matches to any row; • SQL%NOTFOUND, a BOOLEAN attribute indicating whether the recent SQL statement does not match to any row; • SQL%ISOPEN, a BOOLEAN attribute and always evaluated as FALSE immediately after the SQL statement is executed.
To write the explicit cursor, please refer to the following example. Note that a cursor definition can array a number of arguments.
For example, DECLARE
CURSOR csr_ac (p_name VARCHAR2) IS
SELECT empno, name, sal
FROM employee
WHERE name LIKE '%p_name%';
BEGIN
FOR rec_ac IN csr_ac ('LE')
LOOP
DBMS_OUTPUT.PUT_LINE(rec_ac.empno || ' ' ||rec_ac.name || ' '||v_sal);
END LOOP ;
CLOSE csr_ac;
END;
/
Another way of writing the above code, is to use the basic loop and the SQL%NOTFOUND cursor, as shown in the following.

SQL> DECLARE

2 CURSOR csr_ac (p_name VARCHAR2) IS

3 SELECT empno, ename, sal

4 FROM emp

5

6 WHERE ename LIKE '%SMITH%';

7

8 v_a emp.empno%TYPE;

9 v_b emp.ename%TYPE;

10 v_c emp.sal%TYPE;

11

12 BEGIN

13 OPEN csr_ac('');

14 LOOP

15 FETCH csr_ac INTO v_a, v_b, v_c;

16 EXIT WHEN csr_ac%NOTFOUND;

17

18 DBMS_OUTPUT.PUT_LINE(v_a || ' ' || v_b || ' '||v_c);

19

20 END LOOP;

21 CLOSE csr_ac;

22 END;

23

24