CREATE TYPE emp_phone_number_typ TABLE OF VARCHAR2(60); CREATE TABLE employee_details (employee_id VARCHAR2(15) NOT NULL, employee_first_name VARCHAR2(25) NOT NULL, employee_last_name VARCHAR2(25) NOT NULL, employee_middle_name VARCHAR2(25), employee_active VARCHAR2(1) DEFAULT 'Y' NOT NULL, created_date DATE DEFAULT SYSDATE NOT NULL, updated_date DATE ); ALTER TABLE employee_details ADD (CONSTRAINT employee_id_pk PRIMARY KEY (employee_id) ); CREATE TABLE employee_phone_nt (employee_id VARCHAR2(15) NOT NULL, employee_phone_number emp_phone_number_typ, created_date DATE DEFAULT SYSDATE NOT NULL, updated_date DATE ); ALTER TABLE employee_phone_nt ADD (CONSTRAINT employee_id_fk FOREIGN KEY (employee_id) REFERENCES employee_details(employee_id) );
INSERT INTO employee_details (employee_id, employee_first_name, employee_last_name, employee_middle_name) VALUES(10000, 'John', 'Doe', 'X'); INSERT INTO employee_phone_nt(employee_id, employee_phone_number) VALUES(10000, emp_phone_number_typ('123-123-1234','234-234-2345','345-345-3456'));
SELECT e1.employee_id, e1.employee_first_name, e1.employee_last_name, e1.employee_middle_name, e3.*, e1.employee_active, e1.created_date FROM emp_details e1, emp_phone_nt e2 TABLE(e2.employee_phone_number) e3 WHERE e1.employee_id = e2.employee_id; -- Table Expression in a CURSOR Expression SELECT e1.employee_id, e1.employee_first_name, e1.employee_last_name, e1.employee_middle_name, CURSOR(SELECT * FROM TABLE(e2.employee_phone_number)) emp_phone e1.employee_active FROM emp_details e1, emp_phone_nt e2 WHERE e1.employee_id = e2.employee_id;
69939