Object Relational tables : Insert « Object Oriented « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Object Oriented
3. Insert

SQL>
SQL>
SQL> CREATE OR REPLACE TYPE address AS OBJECT
  2              (line1 VARCHAR2(20),
  3               line2 VARCHAR2(20),
  4               city VARCHAR2(20),
  5               state_code VARCHAR2(2),
  6               zip VARCHAR2(13),
  7    MEMBER FUNCTION get_address RETURN VARCHAR2,
  8    MEMBER PROCEDURE set_address
  9              (addressLine1 VARCHAR2,
 10               addressLine2 VARCHAR2,
 11               address_city VARCHAR2,
 12               address_state VARCHAR2,
 13               address_zip VARCHAR2)
 14  );
 15  /

Type created.

SQL> CREATE OR REPLACE TYPE BODY address AS
  2    MEMBER FUNCTION get_address RETURN VARCHAR2
  3    IS
  4    BEGIN
  5      RETURN (SELF.line1||' '||SELF.line2||' '||SELF.city||', '||
  6              SELF.state_code||' '||SELF.zip);
  7    END get_address;
  8    MEMBER PROCEDURE set_address (addressLine1 VARCHAR2,
  9                  addressLine2 VARCHAR2,
 10                  address_city VARCHAR2,
 11                  address_state VARCHAR2,
 12                  address_zip VARCHAR2)
 13    IS
 14    BEGIN
 15      line1 :=addressLine1;
 16
 17      line2 :=addressLine2;
 18
 19      city :=address_city;
 20
 21      state_code :=address_state;
 22
 23      zip :=address_zip;
 24
 25    END set_address;
 26  END;
 27  /

Type body created.

SQL>
SQL> CREATE TABLE employee
  2  (empid number(10)PRIMARY KEY,
  3   lastname varchar2(30)NOT NULL,
  4   firstname varchar2(30)NOT NULL,
  5   middle_initial varchar2(2),
  6   emp_address address);

Table created.

SQL>
SQL> INSERT INTO employee VALUES (101,'L','BULUSU',null,
  2  address('50 UNION SQUARE','SUITE 101','NEW YORK','NY','10020'));

1 row created.

SQL>
SQL> select * from employee;

 EMPID LASTNAME                       FIRSTNAME
------ ------------------------------ ------------------------------
MI
--
EMP_ADDRESS(LINE1, LINE2, CITY, STATE_CODE, ZIP)
----------------------------------------------------------------------
   101 L                              BULUSU
nu
ll
ADDRESS('50 UNION SQUARE', 'SUITE 101', 'NEW YORK', 'NY', '10020')


1 row selected.

SQL>
SQL> drop table employee;

Table dropped.

SQL>
SQL>