FETCH

Example

DECLARE cursor_example CURSOR FOR SELECT * FROM table_sample;
FETCH NEXT FROM cursor_example;

Output

column1 | column2 | column3
--------|---------|--------
   1    |    a    |   aaa
   2    |    b    |   bbb
   3    |    c    |   ccc

Explanation

The FETCH command in PostgreSQL is used to retrieve rows from a result set one at a time or few at a time. In the example mentioned above, DECLARE cursor is created for columns from table_sample. Then, FETCH command is used to retrieve the next set of rows of the cursor.

Example

DECLARE @MyCursor CURSOR;
DECLARE @MyField varchar(50);

BEGIN
  SET @MyCursor = CURSOR FOR
  SELECT MyField FROM MyTable,
  OPEN @MyCursor
  FETCH NEXT FROM @MyCursor
  INTO @MyField;

  WHILE @@FETCH_STATUS = 0
  BEGIN
    PRINT @MyField ;
    FETCH NEXT FROM @MyCursor
    INTO @MyField ;
  END;

  CLOSE @MyCursor ;
  DEALLOCATE @MyCursor;
END;

Output

Output_Field1
Output_Field2
Output_Field3
...

Explanation

In this example, a cursor named @MyCursor is declared along with a variable @MyField, which will hold data fetched from the cursor. The cursor is filled with rows from MyTable. Using a while loop, the cursor fetches next row from MyCursor and prints the value of MyField until there are no more rows to fetch (indicated by @@FETCH_STATUS = 0). Once every row has been fetched and printed, the cursor is closed and deallocated.

Example

DECLARE
   CURSOR c_emp
   IS
      SELECT ename, sal
         FROM emp
            WHERE sal > 2000;
   rec_emp c_emp%ROWTYPE;
BEGIN
   OPEN c_emp;

   FETCH c_emp INTO rec_emp;

   dbms_output.Put_line ('Employee Name: ' || rec_emp.ename);
   dbms_output.Put_line ('Salary: ' || rec_emp.sal);

   CLOSE c_emp;
END;
/

Output

Employee Name: KING
Salary: 5000

Explanation

The example declares a cursor c_emp for a SELECT statement that retrieves employee name and salary for employees with salary greater than 2000. The FETCH statement gets a single row from the result set of this SELECT statement and stores the data in rec_emp. The dbms_output.Put_line procedure is then used to send the output to the client. The rec_emp.ename and rec_emp.sal expressions extract the employee name and salary from the fetched row.