Oracle PL/SQL - Assigning NULL to Record Variable

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

Introduction

Assigning the value NULL to a record variable assigns the value NULL to each of its fields.

The following code prints the fields of a record variable before and after assigning NULL to it.

Demo

SQL>
SQL> drop table emp;

Table dropped.-- ww w . j av  a 2s.  c o  m

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> DECLARE
  2    TYPE age_rec IS RECORD (
  3      years  INTEGER DEFAULT 35,
  4      months INTEGER DEFAULT 6
  5    );
  6
  7    TYPE name_rec IS RECORD (
  8      first  emp.first_name%TYPE DEFAULT 'John',
  9      last   emp.last_name%TYPE DEFAULT 'Doe',
 10      age    age_rec
 11    );
 12
 13    name name_rec;
 14
 15    PROCEDURE print_name AS
 16    BEGIN
 17      DBMS_OUTPUT.PUT(NVL(name.first, 'NULL') || ' ');
 18      DBMS_OUTPUT.PUT(NVL(name.last,  'NULL') || ', ');
 19      DBMS_OUTPUT.PUT(NVL(TO_CHAR(name.age.years), 'NULL') || ' yrs ');
 20      DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(name.age.months), 'NULL') || ' mos');
 21    END;
 22
 23  BEGIN
 24    print_name;
 25    name := NULL;
 26    print_name;
 27  END;
 28  /
John Doe, 35 yrs 6 mos
NULL NULL, NULL yrs NULL mos

PL/SQL procedure successfully completed.

SQL>

Related Topic