Oracle PL/SQL - %ROWTYPE Attribute

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Data Type
  4. %ROWTYPE Attribute

Introduction

The %ROWTYPE attribute defines a record variable to represent either a full or partial row of a table or view.

The record has a field with the same name and data type.

If the structure of the row changes, then the structure of the record changes accordingly.

The record fields do not inherit the constraints or initial values of the corresponding columns.

To declare a record variable that always represents a full row of a database table or view, use this syntax: 

variable_name table_or_view_name%ROWTYPE;

The following code declares a record variable that represents a row of the table departments, assigns values to its fields, and prints them.

Demo

SQL>
SQL> DECLARE-- from   ww w .  j  a  v  a2  s.  c om
  2    dept_rec departments%ROWTYPE;
  3  BEGIN
  4    
  5
  6    dept_rec.department_id   := 10;
  7    dept_rec.department_name := 'Administration';
  8    dept_rec.manager_id      := 200;
  9    dept_rec.location_id     := 1700;
 10
 11    -- Print fields:
 12
 13    DBMS_OUTPUT.PUT_LINE('dept_id:   ' || dept_rec.department_id);
 14    DBMS_OUTPUT.PUT_LINE('dept_name: ' || dept_rec.department_name);
 15    DBMS_OUTPUT.PUT_LINE('mgr_id:    ' || dept_rec.manager_id);
 16    DBMS_OUTPUT.PUT_LINE('loc_id:    ' || dept_rec.location_id);
 17  END;
 18  /


SQL>

Related Topics