PL-SQL Programmer defined Records

PL-SQL Programmer defined Records-Learn about Programmer-defined Record declaration , Also Learn how same it is with other languages like C and C# structure related concepts.

PL-SQL Programmer-defined Record

It is a composite data structure that can store different types of data type values. It is similar to Structure concept in C and C#.
As Structure groups all the related data items into a single unit, PL-SQL Record also groups all the related data items into a single unit.
Each data item in the group is called as field. Each field has its own data type.

Programmer-defined Record with TYPE statement

Syntax

TYPE
type_name IS RECORD
  ( field_name1  datatype1  [NOT NULL]  [:= DEFAULT EXPRESSION],
   field_name2   datatype2   [NOT NULL]  [:= DEFAULT EXPRESSION],
   ...
   field_nameN  datatypeN  [NOT NULL]  [:= DEFAULT EXPRESSION);
record_name  type_name;

Record at field level

Syntax

record_name.field

Reference to a RECORD variable in different user defined RECORD type

Syntax

record_name2.record_name1.field

Reference to a record variable in different package or schema

Syntax

[schema_name.][package_name.]record_name.field

Example -1- Program using user defined RECORD Types

DECLARE
TYPE EMPLOYEES
IS
  RECORD
  (
    empid     NUMBER,
    empname   VARCHAR2(50),
    empGender VARCHAR2(20),
    EmpDEPT   VARCHAR2(10) );
  employee1 EMPLOYEES;
  employee2 EMPLOYEES;
BEGIN
  --EMPLOYEE 1  DETAILS----------
  employee1.empid     :=1;
  employee1.empname   :='Harsha';
  employee1.empGender :='MALE';
  employee1.EmpDEPT   :='IT';
  -- Print Employee1 record details
  dbms_output.put_line('Print Employee 1 record details');
  dbms_output.put_line('Employee1 1 ID : '|| employee1.empid);
  dbms_output.put_line('Employee1 1 Name : '|| employee1.empname);
  dbms_output.put_line('Employee1 1 Gender : '||employee1.empGender);
  dbms_output.put_line('Employee1 1 Department : ' ||employee1.EmpDEPT);
  dbms_output.put_line(' ');
  -- EMPLOYEE2 DETAILS----
  employee2.empid     :=2;
  employee2.empname   :='Rani';
  employee2.empGender :='FeMALE';
  employee2.EmpDEPT   :='ECE';
  -- Print Employee 2 record details
  dbms_output.put_line('Print Employee 2 record details');
  dbms_output.put_line('Employee1 2 ID : '|| employee2.empid);
  dbms_output.put_line('Employee1 2 Name : '|| employee2.empname);
  dbms_output.put_line('Employee1 2 Gender : '||employee2.empGender);
  dbms_output.put_line('Employee1 2 Department : ' ||employee2.EmpDEPT);
END;

Output

Print Employee 1 record details
Employee1 1 ID : 1
Employee1 1 Name : Harsha
Employee1 1 Gender : MALE
Employee1 1 Department : IT

Print Employee 2 record details
Employee1 2 ID : 2
Employee1 2 Name : Rani
Employee1 2 Gender : FeMALE
Employee1 2 Department : ECE

Example 2 – pass values to a procedure using programmer define records.

Pass Records to a procedure and Print the result by calling the procedure in PL-SQL block using Programmer defined records as Example.

DECLARE
TYPE EMPLOYYES
IS
  RECORD
  (
    NAME   VARCHAR2(50),
    SALARY NUMBER );
  EMP1 EMPLOYYES;
  EMP2 EMPLOYYES;
--procedure definition
PROCEDURE EMPDETAILS(
    EMP EMPLOYYES)
IS
BEGIN
  dbms_output.put_line ('EMPLOYEE   NAME :  ' || EMP.NAME);
  dbms_output.put_line('EMPLOYEE  SALARY : ' || EMP.SALARY);
END;
BEGIN
  --EMPLOYEE 1 DETAILS
  EMP1.NAME   :='TOD';
  EMP1.SALARY :=1000;
--passing values using Record type values
  EMPDETAILS(EMP1);
  dbms_output.put_line(' ');
  --EMPLOYEE 2 DETAILS
  EMP2.NAME   :='MARK';
  EMP2.SALARY :=20000;
--passing values using Record type values
  EMPDETAILS(EMP2);
END;

Output

EMPLOYEE NAME : TOD
EMPLOYEE SALARY : 1000

EMPLOYEE NAME : MARK
EMPLOYEE SALARY : 20000

Example 3- Program to  use PL/SQL record at field level

DECLARE
TYPE ADDRESS
IS
  RECORD
  (
    VILLAGE VARCHAR2(20),
    CITY    VARCHAR2(20),
    PIN     NUMBER );
--grouping one type data into another type
-- it is same as nested structure in c and C#
TYPE CONTACT
IS
  RECORD
  (
    PERM_ADD ADDRESS,
    CURR_RES_ADD ADDRESS );
  R_CONTRACT CONTACT;
BEGIN
  R_CONTRACT.PERM_ADD.VILLAGE :='SAINTFORD';
  R_CONTRACT.PERM_ADD.CITY    := 'NEW YORK';
  R_CONTRACT.PERM_ADD.PIN     :=2842384;
  DBMS_OUTPUT.PUT_LINE('PERMENAT ADDRESS DETAILS');
  DBMS_OUTPUT.PUT_LINE('VILAGE IS '|| R_CONTRACT.PERM_ADD.VILLAGE);
  DBMS_OUTPUT.PUT_LINE('CITY IS '|| R_CONTRACT.PERM_ADD.CITY);
  DBMS_OUTPUT.PUT_LINE('PIN IS '|| R_CONTRACT.PERM_ADD.PIN);
  DBMS_OUTPUT.PUT_LINE('');
  R_CONTRACT.CURR_RES_ADD.VILLAGE := ' GRANDTUTA';
  R_CONTRACT.CURR_RES_ADD.CITY    := 'NEW YORK';
  R_CONTRACT.CURR_RES_ADD.PIN     :=28456384;
  DBMS_OUTPUT.PUT_LINE('PERMENAT ADDRESS DETAILS');
  DBMS_OUTPUT.PUT_LINE('VILAGE IS '|| R_CONTRACT.CURR_RES_ADD.VILLAGE);
  DBMS_OUTPUT.PUT_LINE('CITY IS '|| R_CONTRACT.CURR_RES_ADD.CITY);
  DBMS_OUTPUT.PUT_LINE('PIN IS '|| R_CONTRACT.CURR_RES_ADD.PIN);
END;

Output

PERMENAT ADDRESS DETAILS
VILAGE IS SAINTFORD
CITY IS NEW YORK
PIN IS 2842384

PERMENAT ADDRESS DETAILS
VILAGE IS GRANDTUTA
CITY IS NEW YORK
PIN IS 28456384