Oracle SQL Nested Tables and Object Types Implementation

Oracle Nested Tables and Object Types

1. Implementing Nested Tables

Define the object type and the nested table structure:

CREATE OR REPLACE TYPE detalle_tip AS OBJECT (
  id_producto NUMBER(3),
  cantidad NUMBER(10)
);

CREATE TYPE nest_detalle AS TABLE OF detalle_tip;

Create the parent table and store the nested table:

CREATE TABLE orden (
  id_orden NUMBER,
  fecha DATE NOT NULL,
  detalles nest_detalle
) NESTED TABLE detalles STORE AS store_detalles;

Insert and query data:

INSERT INTO orden VALUES (100, SYSDATE, nest_detalle(detalle_tip(10, 1000)));

SELECT id_orden, t2.id_producto FROM orden t, TABLE(t.detalles) t2;
SELECT id_orden, fecha, t2.id_producto, t2.cantidad FROM orden t, TABLE(t.detalles) t2;

2. Varrays and Nested Tables in Companies

CREATE TYPE dueno AS VARRAY(5) OF VARCHAR2(16);
CREATE TYPE phone_nested AS TABLE OF VARCHAR2(12);

CREATE TABLE company2 (
  name VARCHAR2(20),
  phone phone_nested
) NESTED TABLE phone STORE AS phone_nr_table;

INSERT INTO company2 VALUES ('abc', phone_nested('243-4758', '485-2534'));

3. Complex Object-Relational Modeling

1. CREATE TYPE nombreUdt AS OBJECT (nombres VARCHAR2(30), apellidos VARCHAR2(30));
2. CREATE TYPE direccionUdt AS OBJECT (calle VARCHAR2(30), numeroCalle CHAR(5), ciudad VARCHAR2(20), cp CHAR(5));
3. CREATE TYPE telefonosUdt AS VARRAY(10) OF VARCHAR2(15);
4. CREATE TYPE PersonaUdt AS OBJECT (SSN CHAR(9), Nombre nombreUdt, fechaNacimiento DATE, Direccion direccionUdt, telefonos telefonosUdt) NOT FINAL;
5. CREATE TYPE CursoUdt;
6. CREATE TYPE RefCursosUdt AS TABLE OF REF CursoUdt;
7. CREATE TYPE CursoTomadoUdt;
8. CREATE TYPE RefCursoTomadoUdt AS TABLE OF REF CursoTomadoUdt;
9. CREATE TYPE instructorUdt;
10. CREATE TYPE diplomaUdt AS OBJECT (nombre VARCHAR2(15), anio CHAR(4));
11. CREATE TYPE diplomasUdt AS VARRAY(10) OF diplomaUdt;
12. CREATE TYPE estudianteUdt UNDER PersonaUdt (numEstud CHAR(6), departamento VARCHAR2(20), diplomas diplomasUdt, cursoInscrito RefCursosUdt, cursoTomado RefCursoTomadoUdt);
13. CREATE TYPE RefEstudiantesUdt AS TABLE OF REF estudianteUdt;
14. CREATE OR REPLACE TYPE CursoUdt AS OBJECT (numCurso CHAR(10), nombreCurso VARCHAR2(40), creditos INTEGER, anio INTEGER, departamento VARCHAR2(20), RequisitoDeRef RefCursosUdt, tienePrerequisito RefCursosUdt, cursoTomado RefCursoTomadoUdt, estudianteInscrito RefEstudiantesUdt, maestroRef REF instructorUdt);