Cursors in PL/pgSQL

About Cursors
Every SQL statement executed by the PostgreSQL Server has an individual cursor associated with it :

• Implicit cursors : Declared and managed by PL/pgSQL for all DML and PL/pgSQL SELECT statements
• Explicit cursors : Declared and managed by the programmer

Explicit Cursor Operations

Controlling Explicit Cursors

Declaring the Cursor

Syntax : 

             CURSOR cursor_name IS select_statement;

Example :

      DECLARE 

      CURSOR emp_cursor IS 

      SELECT empno, ename FROM emp 

      WHERE deptno =30;

Opening the Cursor :

       DECLARE

              CURSOR emp_cursor IS 

              SELECT empno, ename FROM emp

              WHERE deptno =30;

       BEGIN

              OPEN emp_cursor;

Fetching Data from the Cursor :

       DECLARE

              CURSOR emp_cursor IS 

              SELECT empno, ename FROM emp

              WHERE deptno =30;

              empno emp.empno%TYPE;

              lname emp.ename%TYPE;

       BEGIN

              OPEN emp_cursor;

              FETCH emp_cursor INTO empno, lname;

              raise Notice ‘%’,empno; 

              raise Notice ‘%’,lname;

              EXIT WHEN NOT FOUND;

              END LOOP; 

       END;

Closing the Cursor

       ...  

       LOOP

       CLOSE emp_cursor;

END;