PL/SQL: SQL Cursor

Shafeen sitting on a bicycle with Tameem, who is riding the cycle

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 the 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 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.

DECLARE

CURSOR csr_ac (p_name VARCHAR2) IS
SELECT empno, name, sal
FROM employee
WHERE name LIKE '%p_name%';
v_a employee.empno%TYPE;
v_b employee.name%TYPE;
v_c employee.sal%TYPE;

BEGIN
    OPEN csr_ac ('LE');
    LOOP
        FETCH csr_ac INTO a, b, c;
        EXIT WHEN csr_ac%NOTFOUND;                       
        DBMS_OUTPUT.PUT_LINE(v_a || ' ' || v_b || ' '||v_c);

    END LOOP;
    CLOSE csr_ac;
END;