Cursors in Oracle

  CURSORS:
=========

To execute a multi-row query, Oracle opens an unnamed work area that stores processing information.
 You can access this area through an explicit cursor,
which names the work area, or through a cursor variable, which points to the work area.


Cursor variables are like C or Pascal pointers, which hold the address of some item instead of the item itself.
 Declaring a cursor variable creates a pointer, not an item.

1.Implicit Cursors:

---- SQl%isopen---always it should return null  value due to cursor close automatically in implicit cursors.
---- SQl%found,SQl%notfound,SQl%rowcount


Implicit cursor attributes return information about the execution of an INSERT, UPDATE, DELETE, or SELECT INTO statement.
The values of the cursor attributes always refer to the most recently executed SQL statement.
Before Oracle opens the SQL cursor, the implicit cursor attributes yield NULL

SQL> DECLARE
  2      CURSOR c_employees is SELECT * from employee;
  3
  4  BEGIN                                        -- implicit cursor open
  5    FOR emp_record  in c_employees loop        -- implicit cursor fetch
  6
  7         DBMS_OUTPUT.PUT_LINE('emp_record.id: ' || emp_record.id);
  8   END LOOP;                                   -- implicit cursor close
  9   COMMIT;
 10  END;
 11  /

Implicit Cursors Attributes:


---- SQl%isopen---always it should return null  value due to cursor close automatically in implicit cursors.
---- SQl%found,SQl%notfound,SQl%rowcount.


2.Explicit cursors:
============

When you need precise control over query processing, you can explicitly declare a cursor in the declarative part of any PL/SQL block, subprogram, or package.

Explicit cursors Attributes:


--  cursorname%isopen,
--  cursorname%found,cursorname%notfound,cursorname%rowcount.


You use three commands to control a cursor: OPEN, FETCH, and CLOSE. First, you initialize the cursor with the OPEN statement,
 which identifies the result set. Then, you can execute FETCH repeatedly until all rows have been retrieved,
or you can use the BULK COLLECT clause to fetch all rows at once. When the last row has been processed,
 you release the cursor with the CLOSE statement.

This technique requires more code than other techniques such as the implicit cursor FOR loop.
Its advantage is flexibility. You can:

--Process several queries in parallel by declaring and opening multiple cursors.

--Process multiple rows in a single loop iteration, skip rows, or split the processing into more than one loop.

DECLARE
   CURSOR c1 IS SELECT empno, ename, job, sal FROM emp
      WHERE sal > 2000;
   CURSOR c2 RETURN dept%ROWTYPE IS
      SELECT * FROM dept WHERE deptno = 10;


Cursor for loop
==========

DECLARE
  CURSOR c1 IS
    SELECT object_name, status FROM user_objects WHERE object_type = 'TABLE'
       AND object_name NOT LIKE '%$%';
BEGIN
  FOR item IN c1 LOOP
    dbms_output.put_line('Table = ' || item.object_name || ', Status = ' || item.status);
  END LOOP;
END;
/

Cursor without parameter

Cursor with parameter

Cursor with return type

cursor for update of sal nowait

cursor where current of cursorname


Querying Data with PL/SQL: Implicit Cursor FOR Loop:
===================================================

BEGIN
  FOR item IN
  (
    SELECT object_name, status FROM user_objects WHERE object_type = 'INDEX'
       AND object_name NOT LIKE '%$%'
  )
  LOOP
    dbms_output.put_line('Index = ' || item.object_name ||
      ', Status = ' || item.status);
  END LOOP;
END;

Querying Data with PL/SQL: Explicit Cursor FOR Loops:
====================================================

DECLARE
  CURSOR c1 IS
    SELECT object_name, status FROM user_objects WHERE object_type = 'TABLE'
       AND object_name NOT LIKE '%$%';
BEGIN
  FOR item IN c1 LOOP
    dbms_output.put_line('Table = ' || item.object_name ||
      ', Status = ' || item.status);
  END LOOP;
END;
/

Difference Between SQL%NOTFOUND and NO_DATA_FOUND:
=================================================

DECLARE
  2    -- Record to hold room information.
  3    v_Data   employee%ROWTYPE;
  4  BEGIN
  5    -- Retrieve information about room ID -1.
  6    SELECT *
  7      INTO v_Data
  8      FROM employee
  9      WHERE id = '-1';
 10
 11    -- The following statement will never be executed, since
 12    -- control passes immediately to the exception handler.
 13    IF SQL%NOTFOUND THEN
 14      DBMS_OUTPUT.put_line('SQL%NOTFOUND');
 15    END IF;
 16  EXCEPTION
 17    WHEN NO_DATA_FOUND THEN
 18      DBMS_OUTPUT.put_line('NO_DATA_FOUND');
 19  END;
 20  /
NO_DATA_FOUND



Cursor with simple loop:
================
DECLARE
  CURSOR emp_cur IS SELECT first_name, last_name, salary FROM emp_tbl;
 emp_rec emp_cur%rowtype;
BEGIN
  IF NOT emp_cur%ISOPEN THEN
 OPEN emp_cur;
  END IF;
   LOOP
    FETCH emp_cur INTO emp_rec;  
  EXIT WHEN emp_cur%NOTFOUND;
   dbms_output.put_line(emp_cur.first_name || ' ' ||emp_cur.last_name 13>     || ' ' ||emp_cur.salary);
 END LOOP;
 END;


Cursor with While  loop:
==================

DECLARE
  CURSOR emp_cur IS SELECT first_name, last_name, salary FROM emp_tbl;
 emp_rec emp_cur%rowtype;
BEGIN
  IF NOT emp_cur%ISOPEN THEN
 OPEN emp_cur;
  END IF;
   LOOP
    FETCH emp_cur INTO emp_rec;
while emp_cur%found then
loop
dbms_output.put_line(emp_cur.first_name || ' ' ||emp_cur.last_name 13>     || ' ' ||emp_cur.salary);
FETCH emp_cur INTO emp_rec;
 END LOOP;
 END;

Cursor with For loop:
================
DECLARE
  CURSOR emp_cur IS SELECT first_name, last_name, salary FROM emp_tbl;
 emp_rec emp_cur%rowtype;
BEGIN

for i in emp_cur loop

dbms_output.put_line(emp_cur.first_name || ' ' ||emp_cur.last_name 13>     || ' ' ||emp_cur.salary);

end loop;
end;



Ur's
AmarAlam