Created
May 17, 2019 02:09
-
-
Save bobby5892/4775a0ee4efc7e5334892520fc327f74 to your computer and use it in GitHub Desktop.
Lab 7 276
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| CREATE TYPE DRIVER_T AS OBJECT ( | |
| first_name VARCHAR2(30), last_name VARCHAR2(30), date_of_birth DATE | |
| ); | |
| --Create a DRIVERS_VA VARRAY of the DRIVER_TY abstract data type you created in step 1. | |
| CREATE TYPE DRIVERS_VA IS VARRAY(5) OF DRIVER_T; | |
| --define what an owner is | |
| CREATE TYPE OWNER AS OBJECT( | |
| first_name VARCHAR2(30), | |
| last_name VARCHAR2(30), | |
| date_purchased DATE | |
| ); | |
| -- table of owners | |
| CREATE OR REPLACE TYPE OWNERS_NT AS TABLE OF OWNER; | |
| CREATE TABLE AUTOMOBILE( | |
| vehicle_identification_number NUMBER PRIMARY KEY, | |
| drivers DRIVERS_VA, | |
| owners OWNERS_NT | |
| ) NESTED TABLE owners STORE AS owners_nes_tab; | |
| --INSERT INTO AUTOMOBILE (vehicle_identification_number,drivers,owners) VALUES(); | |
| DECLARE | |
| autoRecord AUTOMOBILE%rowtype; | |
| BEGIN | |
| --autoRecord := autoRecord(); | |
| autoRecord.drivers := DRIVERS_VA(); | |
| autoRecord.drivers.EXTEND; | |
| autoRecord.vehicle_identification_number := 101; | |
| autoRecord.drivers(1) := DRIVER_T('Erin','Smalltalk',TO_DATE('1957/05/23', 'yyyy/mm/dd')); | |
| autoRecord.drivers.EXTEND; | |
| autoRecord.drivers(2) := DRIVER_T('Joe','Smalltalk',TO_DATE('1983/10/19', 'yyyy/mm/dd')); | |
| autoRecord.owners := OWNERS_NT(); | |
| autoRecord.owners.EXTEND; | |
| autoRecord.owners(1) := OWNER('Lance','Smalltalk',TO_DATE('2003/01/19', 'yyyy/mm/dd')); | |
| INSERT INTO AUTOMOBILE VALUES autoRecord ; | |
| END; | |
| DECLARE | |
| autoRecord AUTOMOBILE%rowtype; | |
| BEGIN | |
| --autoRecord := autoRecord(); | |
| autoRecord.drivers := DRIVERS_VA(); | |
| autoRecord.drivers.EXTEND; | |
| autoRecord.vehicle_identification_number := 102; | |
| autoRecord.drivers(1) := DRIVER_T('Julie','Goldstein',TO_DATE('1977/06/19', 'yyyy/mm/dd')); | |
| autoRecord.drivers.EXTEND; | |
| autoRecord.drivers(2) := DRIVER_T('Max','Lucids',TO_DATE('1989/02/12', 'yyyy/mm/dd')); | |
| autoRecord.owners := OWNERS_NT(); | |
| autoRecord.owners.EXTEND; | |
| autoRecord.owners(1) := OWNER('George','Stephanopolis',TO_DATE('1999/07/03', 'yyyy/mm/dd')); | |
| autoRecord.owners.EXTEND; | |
| autoRecord.owners(2) := OWNER('Max','Lucids',TO_DATE('2009/08/23', 'yyyy/mm/dd')); | |
| INSERT INTO AUTOMOBILE VALUES autoRecord ; | |
| END; | |
| DECLARE | |
| CURSOR cur_auto IS SELECT vehicle_identification_number,drivers,owners FROM AUTOMOBILE; | |
| l_row PLS_INTEGER; | |
| BEGIN | |
| FOR current_auto IN cur_auto | |
| LOOP | |
| l_row := current_auto.drivers.FIRST; | |
| WHILE (l_row IS NOT NULL) | |
| LOOP | |
| DBMS_OUTPUT.put_line('VIN: ' || current_auto.vehicle_identification_number || ' Driver: ' || current_auto.drivers(l_row).first_name || ' ' || current_auto.drivers(l_row).last_name ); | |
| l_row := current_auto.drivers.NEXT (l_row); | |
| END LOOP; | |
| END LOOP; | |
| END; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment