Before, we dive in. PL SQL is a strongly typed language. For every variable used in a program or subprogram, the variable must be declared with a data type. PL SQL comes with some data types already defined. Examples of these data types are VARCHAR, VARCHAR2, NUMBER, DATE, etc., and so on. Generally, data types can be grouped into 4 main buckets which are scalars, LOB (Large objects) types, reference types, and composite data types. Scalars are atomic data types such as NUMBER, BOOLEAN, VARCHAR, and many more while composite data types consist of one or more scalars. Examples of composite data types are record types, collection types, and object types.
Think about small pieces of Legos coming together much like a puzzle to build a Lego Spider-Man. The same can be said about data types in databases. Although the inbuilt data types may not always be well suited to your needs, a combination of multiple data types could be used to fit into the puzzle or, in this case, your application. Furthermore, in SQL (PL SQL in this case), Oracle allows you to create custom data types that other programs and subprograms can use in the database. These are typically what composite data types are.
This article will focus mainly on scalars and composite data types. Let's dive in
Scalars
You can not create your own scalar because scalars are base types but you can create a subtype. Subtypes do not introduce a new type. They, however, place optional constraints on a base type. Generally, subtypes improve the readability of your code by indicating the intended use of the variable, for instance, a user-defined currency subtype will indicate that the variable is going to be used for finance-related activities. Subtypes also improve reliability by making use of the constraints.
Subtypes are defined in the declarative part of any PL SQL block, subprogram, or package using the syntax below
/* syntax to define subtypes */
SUBTYPE subtype_name IS base_type[(constraint)] [NOT NULL];
Examples of subtypes can be seen in the code snippet below.
DECLARE
/* Number must be 9 digits */
SUBTYPE T_NatIDNum IS PLS_INTEGER RANGE 100000000 .. 999999999;
SUBTYPE T_BirthDate IS DATE;
/* Numbers will have maximum precision of 2 decimal places */
SUBTYPE T_Height_weight IS NUMBER(10,2);
v_b_date T_BirthDate;
v_height T_Height_weight;
v_weight T_Height_weight;
v_nat_id_number T_NatIDNum;
BEGIN
......
From the above snippet, the v_height and v_weight variable can only have a maximum precision of 2 decimal places while the v_nat_id_number variable must be between 100000000 and 999999999.
Composite Data Types
Composite data types (also known as user-defined types) can be created by a user. These composite data types can usually take 3 forms which are record types, object types, and collection types. All composite data types have internal components. These internal components could either be a scalar or another composite data type. Internal components can be accessed in a composite data type and this is done usually using the dot notation.
Record Types
It is similar to a row in a database table. its internal components can be of different data types and are referred to as fields. The simple snippet below shows how a record type is declared and used in a subprogram. The internal components are accessed using the dot notation as indicated in the executable part.
Note: Record types are usually used and declared in packages and subprograms and are usually not preceded with the CREATE keyword as shown in the example below.
DECLARE
/* Record Type declaration */
TYPE emp_contact IS RECORD (
/* internal components */
emp_id hr.employees.employee_id%TYPE,
emp_email hr.employees.email%TYPE,
emp_phone_no hr.employees.phone_number%TYPE
);
/* Variable declaration */
v_emp_contact EMP_CONTACT;
BEGIN
SELECT employee_id, email, phone_number INTO v_emp_contact
FROM hr.employees
WHERE employee_id = 101;
DBMS_OUTPUT.PUT_LINE('Employee ID: ' || TO_CHAR(v_emp_contact.emp_id));
DBMS_OUTPUT.PUT_LINE('Employee Email: ' || LOWER(v_emp_contact.emp_email) || '@learnplsql.com');
DBMS_OUTPUT.PUT_LINE('Employee Phone Number: ' || TO_CHAR(v_emp_contact.emp_phone_no));
END;
Object types
These allow you to create abstractions of real-world objects just like other object-oriented programming languages. Object types can have 3 components
- Attributes: These can be user-defined types or the default scalars. They structure the object
- Name: This is the name of the object. It must be unique in the schema.
- Methods: Methods are functions or procedures that model the behavior of an object just like a real-world entity. They are usually preceded with the keyword MEMBER when specified as a component of the object. Methods can also be preceded with keywords STATIC or COMPARISON
NOTE: Methods preceded with the MEMBER keyword have an implicit SELF parameter as the first parameter
Objects types are created as stand-alone objects and can be used just like the built-in data types. Object types may require a body just like packages in PLSQL if the object has methods. Let's define 2 objects to model external parties in a company (visitors and vendors). The visitor object will be very simple while the vendor object will have methods defined as components.
/* Visitor object */
CREATE OR REPLACE TYPE obj_visitor AS OBJECT (
visitor_id NUMBER(4),
first_name VARCHAR2(30),
last_name VARCHAR2(30),
whom_to_see VARCHAR2(50)
)
While for the vendor object we are going to create, we have
/* Vendor Object */
/* object type specification */
CREATE OR REPLACE TYPE obj_vendor AS OBJECT (
visitor_id NUMBER(4),
first_name VARCHAR2(30),
last_name VARCHAR2(30),
whom_to_see VARCHAR2(50),
company VARCHAR2(40),
/* Constructor Method [optional] */
CONSTRUCTOR FUNCTION obj_vendor(visitor_id NUMBER, first_name VARCHAR2, last_name VARCHAR2, company VARCHAR2) RETURN SELF AS RESULT,
/* Other methods */
MEMBER PROCEDURE insert_vendor (SELF IN OUT NOCOPY obj_vendor),
MEMBER FUNCTION display_vendor_details RETURN VARCHAR2)
/* object type body */
CREATE OR REPLACE TYPE BODY obj_vendor AS
CONSTRUCTOR FUNCTION obj_vendor (
visitor_id NUMBER,
first_name VARCHAR2,
last_name VARCHAR2,
company VARCHAR2
) RETURN SELF AS RESULT AS
BEGIN
dbms_output.put_line('object constructor function fired ==>');
self.visitor_id := visitor_id;
self.first_name := first_name;
self.last_name := last_name;
self.company := company;
RETURN;
END;
MEMBER PROCEDURE insert_vendor (
self IN OUT NOCOPY obj_vendor
) AS
BEGIN
INSERT INTO visitors VALUES (
visitor_id,
upper(first_name),
upper(last_name),
upper(company),
sysdate
);
COMMIT;
END;
MEMBER FUNCTION display_vendor_details RETURN VARCHAR2 AS
v_details VARCHAR2(300);
BEGIN
--dbms_output.put_line('Vendor visitor details are');
--dbms_output.put_line('Visitor ID: ' || to_char(visitor_id));
--dbms_output.put_line('Visitor Name: ' || first_name || ' ' || last_name);
v_details := 'Vendor with visitor ID: '
|| TO_CHAR(visitor_id)
|| ' and Fullname: '
|| INITCAP(first_name)
|| ' '
|| INITCAP(last_name)
|| ' from '
|| UPPER(company)
|| ' company.';
RETURN v_details;
END;
END;
Note: For member procedures, you can either pass parameters like regular procedures with IN, OUT keywords. if the SELF keyword is not passed, the parameter mode will default to the IN OUT configuration. However, for performance reasons, you can use the SELF IN OUT NOCOPY . You can read more on this here.
So now that we have created two (2) objects. Let's use them.
DECLARE
v_vendor obj_vendor; /* object is automatically null at this point */
v_vendor_info VARCHAR2(200);
BEGIN
/* Instantiating the object and invoking the constructor */
v_vendor := obj_vendor(v_id_seq.nextval, 'john', 'doe', 'pl/sql academy');
/* Manually displaying the vendor ID */
dbms_output.put_line('Vendor id is: ' || to_char(v_vendor.visitor_id));
/* Calling the member method (function) */
v_vendor_info := v_vendor.display_vendor_details();
dbms_output.put_line(v_vendor_info);
/* Calling the member method (procedure) */
v_vendor.insert_vendor();
END;
Collection types
In a very simplified explanation, collections are basically arrays in PL SQL. There are 3 types: associative arrays, variable-sized arrays (VARRAYS), and nested tables. The sample below shows a simple associative array. Arrays have built-in methods like COUNT to get the length of the array. A comprehensive list of other methods can be found here. Elements in an array can be accessed using their indexes starting from 1 instead of 0 in scripting languages like python, etc.
DECLARE
TYPE t_emp_info IS RECORD (
emp_f_name hr.employees.first_name%TYPE,
emp_l_name hr.employees.last_name%TYPE,
emp_dept hr.departments.department_name%TYPE
);
/* associative array type (collection)*/
TYPE t_emp_array IS
TABLE OF t_emp_info INDEX BY PLS_INTEGER;
/* variable of collection type */
v_emp_array t_emp_array;
/* misc */
v_first_element t_emp_info;
v_last_element t_emp_info;
v_new_rec t_emp_info;
v_arr_length NUMBER(5);
BEGIN
SELECT
first_name,
last_name,
department_name
BULK COLLECT
INTO v_emp_array
FROM
hr.employees e,
hr.departments d
WHERE
e.department_id = d.department_id
ORDER BY
e.employee_id;
v_arr_length := v_emp_array.count;
dbms_output.put_line('Collection length: ' || to_char(v_arr_length));
v_first_element := v_emp_array(1);
dbms_output.put_line('First element: ' || v_first_element.emp_f_name);
v_last_element := v_emp_array(v_arr_length);
dbms_output.put_line('Last element: ' || v_last_element.emp_f_name);
/* delete first element */
v_emp_array.DELETE(1);
/* First element becomes empty */
BEGIN
dbms_output.put_line('New First element: ' || v_emp_array(1).emp_f_name);
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('Element is null');
END;
/* Re assign first element */
v_new_rec.emp_f_name := 'Jane';
v_new_rec.emp_l_name := 'Doe';
v_new_rec.emp_dept := 'Special-Ops';
v_emp_array(1) := v_new_rec;
/* print new first record */
dbms_output.put_line('New First element: ' || v_emp_array(1).emp_f_name || ' of ' || v_emp_array(1).emp_dept || ' department.');
END;
The output of the above PL SQL blocks returns
Composite data types can be fun and handy. Although it may not be very common to see developers use objects and OOP in PL SQL, records and collections could be instrumental in making your code more optimal, better performing, and neater to read.
I hope it is pretty clear what custom data types are in PL SQL and what you can do with them. Don't forget that practice makes better and you can use the live SQL platform here to start practicing right away.