Oracle PL/SQL - Assigning One Record Variable to Another

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

Introduction

You can assign one record variable to another record variable if:

  • The two variables have the same RECORD type.
  • The target variable is declared with a RECORD type, the source variable is declared with %ROWTYPE, their fields match in number and order, and corresponding fields have the same data type.

For record components of composite variables, the types of the composite variables need not match.

Demo

SQL>
SQL> drop TABLE emp;
SQL>-- w  ww.  j  a  va  2s  . co m
SQL> CREATE TABLE emp(
  2  empid NUMBER(6),
  3  first_name VARCHAR2(20),
  4  last_name VARCHAR2(25)) ;
SQL>
SQL> DECLARE
  2    TYPE name_rec IS RECORD (
  3      first  emp.first_name%TYPE DEFAULT 'John',
  4      last   emp.last_name%TYPE DEFAULT 'Doe'
  5    );
  6
  7    name1 name_rec;
  8    name2 name_rec;
  9
 10  BEGIN
 11    name1.first := 'Jane'; name1.last := 'Smith';
 12    DBMS_OUTPUT.PUT_LINE('name1: ' || name1.first || ' ' || name1.last);
 13    name2 := name1;
 14    DBMS_OUTPUT.PUT_LINE('name2: ' || name2.first || ' ' || name2.last);
 15  END;
 16  /
name1: Jane Smith
name2: Jane Smith

PL/SQL procedure successfully completed.

SQL>

Related Topic