Oracle SQL Object Types and Tables

Important Oracle SQL Data Dictionary Views

Important Oracle SQL tablespaces views include: dba_tablespaces, dba_data_files, dba_tables, user_types, user_source.

Creating Complex Object Types

CREATE OR REPLACE TYPE direccion AS OBJECT(
    calle VARCHAR2(50),
    numero NUMBER(3),
    piso NUMBER(2),
    puerto VARCHAR2(1),
    codpostal NUMBER(5),
    localidad VARCHAR2(50),
    provincia VARCHAR2(50)
);

CREATE OR REPLACE TYPE persona AS OBJECT(
    dni VARCHAR2(9),
    nombre VARCHAR2(50),
    apellido1 VARCHAR2(50),
    apellido2 VARCHAR2(50),
    telefono NUMBER(9),
    fecha_nac DATE,
    direccion direccion
);

Creating Tables Using Object Types

CREATE TABLE agenda OF persona;

DESC agenda; -- Visualize table structure


CREATE TABLE agenda2 OF persona(dni PRIMARY KEY);


CREATE TABLE agenda3(
    year NUMBER,
    contacto persona
);

Inserting and Querying Object Data

INSERT INTO agenda VALUES(
    '28888888P',
    'Juan',
    'Morales',
    'Serrano',
    600700800,
    TO_DATE('15/01/2014', 'DD/MM/YYYY'),
    direccion('Abril', 33, 2, 'D', 28863, 'Madrid', 'Madrid')
);

SELECT a.direccion.localidad FROM agenda a;

Defining Object Methods

CREATE OR REPLACE TYPE rectangulo AS OBJECT(
    base NUMBER,
    area NUMBER,
    altura NUMBER,
    STATIC PROCEDURE proc1(ancho NUMBER, alto NUMBER),
    MEMBER PROCEDURE proc2(ancho NUMBER, alto NUMBER),
    CONSTRUCTOR FUNCTION rectangulo(base NUMBER, altura NUMBER)
    RETURN SELF AS RESULT
);

Implementing Object Type Body

CREATE TABLE tablarec(valor INTEGER);

CREATE OR REPLACE TYPE BODY rectangulo AS
    CONSTRUCTOR FUNCTION rectangulo (base NUMBER, altura NUMBER)
    RETURN SELF AS RESULT IS
    BEGIN
        SELF.base := base;
        SELF.altura := altura;
        SELF.area := base * altura;
        RETURN;
    END;

    STATIC PROCEDURE proc1(ancho NUMBER, alto NUMBER) IS
    BEGIN
        -- Static procedures cannot access SELF attributes directly.
        -- Assuming the intent was to insert the calculated area:
        INSERT INTO tablarec VALUES(ancho * alto);
        COMMIT;
    END;

    MEMBER PROCEDURE proc2(ancho NUMBER, alto NUMBER) IS
    BEGIN
        -- Body for MEMBER PROCEDURE proc2 is missing in the original text.
        -- Add implementation here if needed.
        NULL; -- Placeholder
    END;
END;

Using Objects and Methods in PL/SQL

DECLARE
    r1 rectangulo;
    r2 rectangulo;
    r3 rectangulo := rectangulo(7, 5);
BEGIN
    r1 := NEW rectangulo(10, 20);
    r2 := NEW rectangulo(30, 30);

    -- Manually setting attributes (overrides constructor/methods if not careful)
    r3.base := 5;
    r3.altura := 15;
    r3.area := r3.base * r3.altura; -- Recalculate area after changing base/altura

    -- Call static procedure
    rectangulo.proc1(10, 20);

    -- Call member procedure (Note: proc2 body was missing in original text)
    -- r1.proc2(5, 10); -- This call would fail without a proc2 body

    -- Example of accessing object attributes
    DBMS_OUTPUT.PUT_LINE('r1 area: ' || r1.area);
    DBMS_OUTPUT.PUT_LINE('r3 area: ' || r3.area);

END;
/