Updated November, 23 2023

Cursors#

Postgres has cursor variables with which we can implement most of the Oracle cursors

Cursor type and ref cursors

 1 --Oracle
 2 DECLARE
 3  TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE;  -- strong type
 4  TYPE genericcurtyp IS REF CURSOR;                       -- weak type
 5
 6  cursor1  empcurtyp;       -- strong cursor variable
 7  cursor2  genericcurtyp;   -- weak cursor variable
 8  my_cursor SYS_REFCURSOR;  -- weak cursor variable
 9
10  TYPE deptcurtyp IS REF CURSOR RETURN departments%ROWTYPE;  -- strong type
11  dept_cv deptcurtyp;  -- strong cursor variable
12BEGIN
13  NULL;
14END;

Explicit cursors declaration

 1 --Oracle
 2 DECLARE
 3  CURSOR cparam(p1 NUMBER, p2 VARCHAR2) RETURN c1%ROWTYPE IS
 4    SELECT * FROM departments
 5    WHERE p1 = department_id AND p2 = department_name;
 6
 7BEGIN
 8  OPEN cparam(123, 'st_clerk');
 9END;
10/

Cursor attributes

 1--Oracle
 2DECLARE
 3  CURSOR c1 RETURN departments%ROWTYPE IS  -- Define c1,
 4    SELECT * FROM departments;              -- repeating return type
 5
 6  the_row Rec;
 7BEGIN
 8  IF NOT c1%ISOPEN THEN
 9    DBMS_OUTPUT.PUT_LINE('NOT OPEN');
10  END IF;
11  OPEN c1;
12  FETCH c1 INTO the_row;
13  IF c1%FOUND THEN  -- fetch succeeded
14    DBMS_OUTPUT.PUT_LINE('Name = ' || the_row.f2 );
15  END IF;
16
17  IF c1%NOTFOUND THEN -- fetch failed
18    DBMS_OUTPUT.PUT_LINE('DATA NOTFOUND!!');
19  END IF;
20
21  IF c1%ROWCOUNT = 1 THEN
22     DBMS_OUTPUT.PUT_LINE('Fetched 1st row');
23  END IF;
24
25  IF c1%ISOPEN THEN
26     DBMS_OUTPUT.PUT_LINE('STILL OPEN!!');
27  END IF;
28  CLOSE c1;
29END;

%ROWTYPE of cursor

 1--Oracle
 2DECLARE
 3  CURSOR c IS
 4    SELECT first_name, last_name, phone_number
 5    FROM employees;
 6
 7  friend c%ROWTYPE;
 8BEGIN
 9  friend.first_name   := 'John';
10  friend.last_name    := 'Smith';
11  friend.phone_number := '1-650-555-1234';
12
13  DBMS_OUTPUT.PUT_LINE (
14    friend.first_name  || ' ' ||
15    friend.last_name   || ', ' ||
16    friend.phone_number
17  );
18END;

REFCURSOR in package#

For cursor type in the package, we create composite type and corresponding setters and getters for each cursor attribute

1--Oracle
2CREATE OR REPLACE PACKAGE app_libutils is
3  -- Ref Cursors Types
4  TYPE c_team_list IS REF CURSOR RETURN r_team_list;
5END app_libutils;

The opening cursor of package retype. We create a temp simple variable of REFCURSOR type for passing it to OPEN of FETCH statement and write it back after the operation

 1--Oracle
 2 DECLARE
 3     TeamList c_TeamList;
 4  BEGIN
 5     OPEN TeamList FOR
 6          SELECT rtm_team FROM vws_res_team WHERE rtm_link_id = ExclusionID AND rtm_type='PERSON';
 7     if (TeamList%isopen) then
 8       DBMS_OUTPUT.PUT_LINE('OPEN' );
 9     end if;
10   END ;