PL/SQL For Practitioners – #8 Type object and Table of Type

Hi folks,

Object-oriented programming is especially suited for building reusable components and complex applications. In PL/SQL, object-oriented programming is based on object types. They let you model real-world objects, separate interfaces and implementation details, and store object-oriented data persistently in the database.

An object type is a user-defined composite datatype representing a data structure and functions and procedures to manipulate the data. With scalar datatypes, each variable holds a single value. With collections, all the elements have the same type. Only object types let you associate code with the data.

Like a package, an object type has a specification and a body. The specification (or spec for short) defines the programming interface; it declares a set of attributes along with the operations (methods) to manipulate the data. The body defines the code for the methods. However, If an object type spec declares only attributes, the object type body is unnecessary

Basic structure – Specification

CREATE OR REPLACE
TYPE <name> AS OBJECT
( 
  <variable1_name> <type>,
  <variable2_name> <type>
);

Basic structure – Body (when needed)

CREATE OR REPLACE
TYPE BODY <name> AS
  --your code
END;

Example 1

Well, in this first example I’m creating a type with the structure of the table city. I also created a package where I’m declaring this new type as a variable. I also created a function responsible for returning a single register.

Creating our type

--Specification or spec
create or replace TYPE T_CITY AS OBJECT
( 
  ID NUMBER,
  NAME VARCHAR(255), 
  NAME_ASCII VARCHAR(255),
  POPULATION NUMBER, 
  COUNTRY_ID NUMBER,
  
  CONSTRUCTOR FUNCTION T_CITY(ID NUMBER, 
                              NAME VARCHAR2, 
                              NAME_ASCII VARCHAR2, 
                              POPULATION NUMBER, 
                              COUNTRY_ID NUMBER)
    RETURN SELF AS RESULT
    
);
/

--body
create or replace TYPE BODY T_CITY AS

  CONSTRUCTOR FUNCTION T_CITY(ID NUMBER, 
                              NAME VARCHAR2, 
                              NAME_ASCII VARCHAR2, 
                              POPULATION NUMBER, 
                              COUNTRY_ID NUMBER)
    RETURN SELF AS RESULT AS
  BEGIN
    
    SELF.ID   := ID;
    SELF.NAME := NAME;
    SELF.NAME_ASCII := NAME_ASCII;
    SELF.POPULATION := POPULATION;
    SELF.COUNTRY_ID := COUNTRY_ID;
    
    RETURN;
  END T_CITY;

END;
/

Creating our package

--spec
create or replace PACKAGE PKG_CITY_CTRL AS

  V_CITY T_CITY;
  FUNCTION F_GET_CITY(p_city_id NUMBER) RETURN T_CITY;

END PKG_CITY_CTRL;
/

--body
create or replace PACKAGE BODY PKG_CITY_CTRL AS

  FUNCTION F_GET_CITY(p_city_id NUMBER) RETURN T_CITY AS
  BEGIN
    
    V_CITY := T_CITY(NULL, NULL, NULL, NULL, NULL);
    
    select  id, 
            name, 
            name_ascii, 
            population, 
            country_id 
    into 
      V_CITY.id,
      V_CITY.name,
      V_CITY.name_ascii,
      V_CITY.population,
      V_CITY.country_id
    from city
    where id = p_city_id;
    
    RETURN V_CITY;
    
  END F_GET_CITY;

END PKG_CITY_CTRL;
/

How to execute in an anonymous block

DECLARE
  P_CITY_ID NUMBER;
  v_Return T_CITY;
BEGIN
  P_CITY_ID := 1;

  v_Return := PKG_CITY_CTRL.F_GET_CITY(
    P_CITY_ID => P_CITY_ID
  );
  
  DBMS_OUTPUT.PUT_LINE('id: ' || v_return.id);
  DBMS_OUTPUT.PUT_LINE('name: ' || v_return.name);
  DBMS_OUTPUT.PUT_LINE('name ascii: ' || v_return.name_ascii);
  DBMS_OUTPUT.PUT_LINE('population: ' || v_return.population);
  DBMS_OUTPUT.PUT_LINE('country_id: ' || v_return.country_id);
  
END;

Result

Example 2

Well, another amazing feature that we can use while working with types is the lists or tables of types. They basically a table of a type. In this example, I’m creating a list of the type “CITY” and printing out.

Creating our table of type

CREATE OR REPLACE TYPE T_CITY_TABLE AS TABLE OF T_CITY;

Creating our function responsible for returns the new type

DECLARE
  V_CITY T_CITY;
  V_CITY_TABLE T_CITY_TABLE;
  
BEGIN
  
  V_CITY_TABLE := T_CITY_TABLE();
  
  V_CITY_TABLE.extend;
  V_CITY_TABLE(V_CITY_TABLE.COUNT) := T_CITY(1, 'City 1' ,'City 1', 123, 1);
  
  V_CITY_TABLE.extend;
  V_CITY_TABLE(V_CITY_TABLE.COUNT) := T_CITY(2, 'City 2' ,'City 2', 321, 1);
  
  FOR i IN 1 .. V_CITY_TABLE.count LOOP
    
    DBMS_OUTPUT.put_line ('City ID        : ' || V_CITY_TABLE(i).ID); 
    DBMS_OUTPUT.put_line ('City Name      : ' || V_CITY_TABLE(i).NAME); 
    DBMS_OUTPUT.put_line ('City Name ASCII: ' || V_CITY_TABLE(i).NAME_ASCII); 
    DBMS_OUTPUT.put_line ('City Population: ' || V_CITY_TABLE(i).POPULATION); 
    DBMS_OUTPUT.put_line ('City Country ID: ' || V_CITY_TABLE(i).COUNTRY_ID); 
    DBMS_OUTPUT.put_line (''); 
    
  END LOOP;  
  
END;
/

Result

This post is part of a series about the most common structures in PL/SQL. You can check the rest of them in the link below.

PL/SQL for real world – 8 features that you have to know

Please write down in the comments any further questions you may have. I’ll be happy to help you.