Using explicit definition to define record types and a compound record type; and, the use of nested types

    
SQL> CREATE TABLE emp
  2   (emp_id              INTEGER             NOT NULL
  3   ,fname               VARCHAR2(30 CHAR)   NOT NULL
  4   ,mid_name           VARCHAR2(1 CHAR)
  5   ,lname                VARCHAR2(30 CHAR)   NOT NULL
  6   ,CONSTRAINT emp_pk PRIMARY KEY (emp_id));

Table created.

SQL>
SQL>
SQL> CREATE TABLE customer (
  2     id         NUMBER PRIMARY KEY,
  3     fname VARCHAR2(50),
  4     lname  VARCHAR2(50)
  5   );

Table created.

SQL>
SQL>
SQL>
SQL>
SQL> INSERT INTO customer (id, fname, lname)VALUES (1, 'A', 'B');

1 row created.

SQL>
SQL>
SQL>
SQL> INSERT INTO customer (id, fname, lname)VALUES (2, 'C', 'D');

1 row created.

SQL>
SQL>
SQL>
SQL> INSERT INTO customer (id, fname, lname)VALUES (3, 'Enn', 'F');

1 row created.

SQL>
SQL>
SQL>
SQL> INSERT INTO customer (id, fname, lname)VALUES (4, 'G', 'H');

1 row created.

SQL>
SQL>
SQL>
SQL> INSERT INTO customer (id, fname, lname)VALUES (5, 'G', 'Z');

1 row created.

SQL>
SQL>
SQL>
SQL>
SQL> SET ECHO ON
SQL>
SQL> 
SQL> DECLARE
  2     TYPE emp_record IS RECORD(emp_id INTEGER,fname VARCHAR2(30 CHAR),mid_name VARCHAR2(1 CHAR),lname VARCHAR2(30 CHAR));
  3
  4     TYPE address_record IS RECORD(address_id INTEGER,emp_id INTEGER,address1 VARCHAR2(30 CHAR),address2 VARCHAR2(30 CHAR)
  5     ,address3 VARCHAR2(30 CHAR),city VARCHAR2(20 CHAR),state VARCHAR2(20 CHAR),postal_code VARCHAR2(20 CHAR),country_code VARCHAR2(10 CHAR));
  6
  7     
  8     TYPE empType IS RECORD(emp emp_RECORD,address ADDRESS_RECORD);
  9
 10     
 11     anEmp empType;
 12
 13   BEGIN
 14
 15     
 16     anEmp.emp.emp_id := 3;
 17     anEmp.emp.fname := 'U';
 18     anEmp.emp.mid_name := 'S';
 19     anEmp.emp.lname := 'G';
 20
 21     
 22     anEmp.address.address_id := 1;
 23     anEmp.address.emp_id := 3;
 24     anEmp.address.address1 := 'R';
 25     anEmp.address.address2 := '';
 26     anEmp.address.address3 := '';
 27     anEmp.address.city := 'City';
 28     anEmp.address.state := 'NY';
 29     anEmp.address.postal_code := '10027-1111';
 30     anEmp.address.country_code := 'USA';
 31     
 32     INSERT INTO emp VALUES(anEmp.emp.emp_id,anEmp.emp.fname,anEmp.emp.mid_name,anEmp.emp.lname);
 33
 34     
 35     COMMIT;
 36
 37   END;
 38   /

PL/SQL procedure successfully completed.

SQL>
SQL> drop TABLE emp;

Table dropped.

SQL> drop table customer;

Table dropped.

SQL>

Related examples in the same category

1.	PL/SQL Collections: record type
2.	The %ROWTYPE can also be used to create a record based on the structure of a cursor
3.	A simple record that holds a person's name, phone number, and birth date and fill data in
4.	Declare the record data type
5.	Load a row in table to record type
6.	A record is populated using a SELECT statement
7.	declare record with not null attribute
8.	Assign a wrong record type
9.	This block shows legal and illegal record assignments.
10.	How to select into a record.
11.	Select into Records
12.	Record type
13.	Nest records, access the names of the nested records by using another component selector, or period

Using explicit definition to define record types and a compound record type; and, the use of nested types : Record Data Type « PL SQL « Oracle PL / SQL

Related examples in the same category