Oracle SQL Nested Tables and Object Types Implementation
Posted by Wikiteka Community and classified in Arts and Humanities
Written on in 
English with a size of 2.71 KB
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