Mega Code Archive
Using explicit definition to define record types and a compound record type; and, the use of nested types
<![CDATA[
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>
]]>