Oracle PL/SQL - FETCH Statement for Assigning Row to Record Variable

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Data Type
  4. RECORD Types

Introduction

The syntax of a simple FETCH statement is: 

FETCH cursor INTO record_variable_name;

A cursor is associated with a query.

For every column that the query selects, the record variable must have a corresponding, type-compatible field.

The cursor must be either an explicit cursor or a strong cursor variable.

In the following code, each variable of RECORD type EmpRecTyp represents a partial row of the emp table-the columns empid and salary.

Both the cursor and the function return a value of type EmpRecTyp.

In the function, a FETCH statement assigns the values of the columns empid and salary to the corresponding fields of a local variable of type EmpRecTyp.

Demo

SQL>
SQL> drop table emp;

Table dropped.--   w  w w . j a v  a 2  s. c  om

SQL> CREATE TABLE emp(
  2  empid NUMBER(6),
  3  first_name VARCHAR2(20),
  4  last_name VARCHAR2(25),
  5  email VARCHAR2(25),
  6  phone_number VARCHAR2(20),
  7  hire_date DATE,
  8  job_id VARCHAR2(10),
  9  salary NUMBER(8,2),
 10  commission_pct NUMBER(2,2),
 11  manager_id NUMBER(6),
 12  department_id NUMBER(4)) ;
SQL>
SQL> INSERT INTO emp VALUES( 100, 'Steven', 'King', 'SKING', '123.123.4567', TO_DATE('17-JUN-1987', 'dd-MON-yyyy'), 'CODER', 24000, NULL, NULL, 90);
SQL>
SQL>
SQL> DECLARE
  2    TYPE EmpRecTyp IS RECORD (
  3      emp_id  emp.empid%TYPE,
  4      salary  emp.salary%TYPE
  5    );
  6
  7    CURSOR desc_salary RETURN EmpRecTyp IS
  8      SELECT empid, salary
  9      FROM emp
 10      ORDER BY salary DESC;
 11
 12    highest_paid_emp       EmpRecTyp;
 13    next_highest_paid_emp  EmpRecTyp;
 14
 15    FUNCTION nth_highest_salary (n INTEGER) RETURN EmpRecTyp IS
 16      emp_rec  EmpRecTyp;
 17    BEGIN
 18      OPEN desc_salary;
 19      FOR i IN 1..n LOOP
 20        FETCH desc_salary INTO emp_rec;
 21      END LOOP;
 22      CLOSE desc_salary;
 23        RETURN emp_rec;
 24      END nth_highest_salary;
 25
 26  BEGIN
 27      highest_paid_emp := nth_highest_salary(1);
 28      next_highest_paid_emp := nth_highest_salary(2);
 29
 30      DBMS_OUTPUT.PUT_LINE(
 31        'Highest Paid: #' ||
 32        highest_paid_emp.emp_id || ', $' ||
 33        highest_paid_emp.salary
 34      );
 35      DBMS_OUTPUT.PUT_LINE(
 36        'Next Highest Paid: #' ||
 37        next_highest_paid_emp.emp_id || ', $' ||
 38        next_highest_paid_emp.salary
 39      );
 40  END;
 41  /
Highest Paid: #100, $24000
Next Highest Paid: #100, $24000

PL/SQL procedure successfully completed.

SQL>

Related Topic