Cursors in PL/SQL

Preview

What is cursor: Pointer to the memory location where the information about sql stmt or DML statement got executed.

There are two types of cursors −

Implicit Cursor:= Any Select Stmt Or Any SQL Stmt Executed By Oracle Is Through Cursor And User Don’t Have Controlled On Implicit Cursor But Use Can Get Information From Its Attributes That Is Called Implicit Cursor. In Other Words, Oracle Automatically Open The Memory Allocation Execute The Stmt And Return The Result And Closes The Memory Allocation Called Implicit Cursor.

DECLARE 
    l_ename emp.ename%TYPE; 
BEGIN 
    SELECT ename 
    INTO   l_ename 
    FROM   emp 
    WHERE  empno = 7788; 

    dbms_output.Put_line ('Employee name s : ' 
                          || l_ename); 
END; 

Explicit Cursor:= Where As When User Gives Name For That Memory Allocation And He Is Trying To Access The Memory Allocation One By One Once He Access The Entire Information And If Developer Closes The Memory Allocation That Is Called Explicit Cursor Is Fully Managed By Developer.

DECLARE
   l_ename   emp.ename%TYPE;
   CURSOR c1
   IS
      SELECT ename
        FROM emp
       WHERE empno = 7788;
BEGIN
   OPEN c1;
   FETCH c1 INTO l_ename;
   CLOSE c1;
   DBMS_OUTPUT.put_line ('Employee name s : ' || l_ename);
END;

Difference: Explicit cursors can be controlled by by cursor attributes but implicit does not have these controls. Attributes are:

Cursor Attributes: Attributes Is The Way By Which We Can Get The Information Or Run Time Information About The Cursor Called Cursor Attributes.

Preview

  • %FOUND Attribute:
  • %ISOPEN Attribute:
  • %NOTFOUND Attribute :
  • %ROWCOUNT Attribute :

Parameterized cursor:= reusebility of the same cursor of the multiple places, if you want to use same select statement in multiple cases whare there is just difference between the whare condition you can go ahead with the parameterized cursor.

For update:= For update class will basically lock the record whatever we are going to do processing later so you can avoid DML operation on the same data from another session.


comments powered by Disqus