DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workloads.

Avoid machine learning mistakes and boost model performance! Discover key ML patterns, anti-patterns, data strategies, and more.

Related

  • Designing a Blog Application Using Document Databases
  • Relational DB Migration to S3 Data Lake Via AWS DMS, Part I
  • NoSQL for Relational Minds
  • Business Logic Database Agent

Trending

  • Automating Data Pipelines: Generating PySpark and SQL Jobs With LLMs in Cloudera
  • 5 Subtle Indicators Your Development Environment Is Under Siege
  • Customer 360: Fraud Detection in Fintech With PySpark and ML
  • Understanding IEEE 802.11(Wi-Fi) Encryption and Authentication: Write Your Own Custom Packet Sniffer
  1. DZone
  2. Data Engineering
  3. Databases
  4. Quick Guide to User-Defined Types in Oracle PL/SQL

Quick Guide to User-Defined Types in Oracle PL/SQL

Get a detailed introduction to the different kinds of user-defined types in Oracle Database and PL/SQL.

By 
Steven Feuerstein user avatar
Steven Feuerstein
·
Feb. 08, 18 · Tutorial
Likes (3)
Comment
Save
Tweet
Share
37.1K Views

Join the DZone community and get the full member experience.

Join For Free

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this post.

PL/SQL is a strongly typed language. Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type).

PL/SQL offers a wide array of pre-defined data types, both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (i.e. the NUMBER_TABLE collection type in the DBMS_SQL package).

Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types, and object types.

You can't really declare your own "user-defined" scalars, though you can define subtypes from those scalars, which can be very helpful from the perspective of the maintainability and readability of code.

Here's an example:

DECLARE
   SUBTYPE currency_t is NUMBER (10,2);
   l_salary currency_t;
BEGIN
   l_salary := 10.50607;
   dbms_output.put_line (l_salary);
END;

10.51

You can, however, certainly define your own composite types. The phrase "user-defined types" often refers to object-oriented structures, but as you will see below, you the user can define your own types of:

  • Records (kinda like a row in a table, though usually consisting of a subset of the columns of a row).
  • Collections (similar to arrays in other programming languages).
  • Object types (comparable to classes in object-oriented languages).

User-Defined Record Types

You can very easily define records based on existing structures, such as a table or cursor. Sometimes, though, you will want to define your own record type to group together related elements or return multiple pieces of information from a function.

You can do this by defining your own record. Here's an example of a record type used as the return type of a function:

CREATE OR REPLACE PACKAGE comp_pkg
   AUTHID DEFINER
IS
   TYPE compensation_rt IS RECORD
   (
      salcomp        employees.salary%TYPE,
      total_salary   NUMBER
   );

   FUNCTION comp_info (emp_id_in IN employees.employee_id%TYPE)
      RETURN compensation_rt;
END;
/

CREATE OR REPLACE PACKAGE BODY comp_pkg
IS
   FUNCTION comp_info (emp_id_in IN employees.employee_id%TYPE)
      RETURN compensation_rt
   IS
      l_comp   compensation_rt;
   BEGIN
      SELECT salary, salary + NVL (commission_pct, 0)
        INTO l_comp
        FROM employees
       WHERE employee_id = emp_id_in;
      
      RETURN l_comp;
   END;
END;
/

DECLARE
   l_comp   comp_pkg.compensation_rt;
BEGIN
   l_comp := comp_pkg.comp_info (101);
   DBMS_OUTPUT.put_line ('Total salary = ' || l_comp.total_salary);
END;
/

(Thanks to Gavin Zzw for several suggestions to improve the code above.)

And here's an example of nesting one record type inside another — normalizing my runtime data structures!

DECLARE
   TYPE phone_rectype IS RECORD
   (
      area_code    PLS_INTEGER,
      exchange     PLS_INTEGER,
      phn_number   PLS_INTEGER,
      extension    PLS_INTEGER
   );

   TYPE contact_rectype IS RECORD
   (
      day_phone#    phone_rectype,
      eve_phone#    phone_rectype,
      cell_phone#   phone_rectype
   );

   l_sales_rep   contact_rectype;
BEGIN
   /* Set the day phone # */
   l_sales_rep.day_phone#.area_code := 773;
   l_sales_rep.day_phone#.exchange := 426;
   l_sales_rep.day_phone#.phn_number := 9093;
   l_sales_rep.day_phone#.extension := NULL;

   /* Copy day phone to evening phone */
   l_sales_rep.eve_phone# := l_sales_rep.day_phone#;

   /* "Override" just phn_number field. */
   l_sales_rep.eve_phone#.phn_number := 2056;
END;

Since we can define records based on a table, view or cursor using the %ROWTYPE attribute, user-defined records do not proliferate in PL/SQL code.

When it comes to collections, though, we almost always declare our own types.

User-Defined Collection Types

Collections are the analog of arrays in PL/SQL. There are three types of collections: associative arrays, nested tables, and arrays.

Collections come in very handy when you need temporary datasets in your program and do not want or need to rely on global temporary tables, SQL, and the context switches between the PL/SQL and SQL engines. Collections are also the enabling technology for table functions, which are functions that are invoked in the FROM clause of a SELECT statement.

You can certainly take advantage of a variety of pre-defined collection types. In the following code fragment, for example, I grab the topics and difficulty levels of a set of Oracle Dev Gym workouts:

   l_topics         DBMS_SQL.number_table;
   l_difficulties   DBMS_SQL.number_table;
BEGIN
     SELECT topic_id, difficulty_id
       BULK COLLECT INTO l_topics, l_difficulties
       FROM dg_workouts
      WHERE goal_id = goal_id_in
   ORDER BY week_number;

The IDs are all integers, so why not use the DBMS_SQL collection? Actually, I suggest you do not do this. That's right. Don't do what I do. Do what I say. Why?

Because this is an example of "bad lazy." When someone comes along later to maintain my code, they will see my use of a DBMS_SQL element and wonder, "Where's the dynamic SQL?"

Our code should answer, not raise, questions. So what I should do is have my own table of numbers and use that. Funnily enough, I do. So I am going to fix my code right now to look this like:

   l_topics         qdb_numbers_nt;
   l_difficulties   qdb_numbers_nt;
BEGIN
     SELECT topic_id, difficulty_id
       BULK COLLECT INTO l_topics, l_difficulties
       FROM dg_workouts
      WHERE goal_id = goal_id_in
   ORDER BY week_number;

Ah — much better!

User-defined record types and collection types are often used together.

For example, in the code underlying the Oracle Dev Gym, the qdb_rankings package defines both a record type and collection type of those records:

/* The record type */

TYPE user_info_rt IS RECORD
(
   comp_event_id   INTEGER,
   total_seconds   INTEGER
);

/* Collection type; each element contains one of those records */
TYPE user_info_tt IS TABLE OF user_info_rt
   INDEX BY PLS_INTEGER;

/* A variable of that collection type */

l_user_timings  user_info_tt;

....

/* Now I populate the collection of records with a BULK COLLECT query */

SELECT comp_event_id, total_seconds
  BULK COLLECT INTO l_user_timings
  FROM mv_qdb_compev_answers eva
 WHERE     eva.competition_id = competition_id_in
       AND TRUNC (eva.start_date) BETWEEN start_in AND end_in
       AND eva.user_id = rec.user_id
       AND eva.pct_correct_answers = 100;

Collections are a lot of fun and very handy. Check out my Practically Perfect PL/SQL YouTube channel for hours of instruction on collections. The PL/SQL doc offers extensive guidance on collections, as well.

User-Defined Object Types

Way back in Oracle8, object-oriented capabilities were added to Oracle Database, implemented in PL/SQL syntax. Instead of classes, they are called object types. And now, a number of big releases later, these object types support most of the features developers have come to expect from object-oriented languages including inheritance and dynamic polymorphism.

Here, for example, is a hierarchy of types to manage one of my all-time favorites things in the world: food.

CREATE TYPE food_t AS OBJECT
(
   name VARCHAR2 (100),
   food_group VARCHAR2 (100),
   grown_in VARCHAR2 (100),
   /* Generic foods cannot have a price, but we can
      insist that all subtypes DO implement a price
      function. */
   NOT INSTANTIABLE MEMBER FUNCTION price RETURN NUMBER
)
   NOT FINAL NOT INSTANTIABLE;
/

CREATE TYPE dessert_t UNDER food_t (
      contains_chocolate CHAR (1),
      year_created NUMBER (4),
      OVERRIDING MEMBER FUNCTION price RETURN NUMBER
   )
   NOT FINAL;
/

CREATE OR REPLACE TYPE BODY dessert_t
IS
   OVERRIDING MEMBER FUNCTION price RETURN NUMBER
   IS
      multiplier   NUMBER := 1;
   BEGIN
      DBMS_OUTPUT.put_line ('Dessert price!');

      IF self.contains_chocolate = 'Y'
      THEN
         multiplier := 2;
      END IF;

      IF self.year_created < 1900
      THEN
         multiplier := multiplier + 0.5;
      END IF;

      RETURN (10.00 * multiplier);
   END;
END;
/

CREATE TYPE cake_t UNDER dessert_t (
      diameter NUMBER,
      inscription VARCHAR2 (200),
      /* Inscription and diameter determine the price */
      OVERRIDING MEMBER FUNCTION price RETURN NUMBER
   );
/

CREATE OR REPLACE TYPE BODY cake_t
IS
   OVERRIDING MEMBER FUNCTION price
      RETURN NUMBER
   IS
   BEGIN
      DBMS_OUTPUT.put_line ('Cake price!');
      RETURN (5.00 + 0.25 * (LENGTH (self.inscription)) + 0.50 * diameter);
   END;
END;
/

DECLARE
   last_resort_dessert   dessert_t
                            := dessert_t ('Jello',
                                          'PROTEIN',
                                          'bowl',
                                          'N',
                                          1887);
   heavenly_cake         cake_t
                            := cake_t ('Marzepan Delight',
                                       'CARBOHYDRATE',
                                       'bakery',
                                       'N',
                                       1634,
                                       8,
                                       'Happy Birthday!');
BEGIN
   DBMS_OUTPUT.put_line (last_resort_dessert.price);
   DBMS_OUTPUT.put_line (heavenly_cake.price);
END;
/

/* Demonstration of dynamic polymorphism */

DECLARE
   TYPE foodstuffs_nt IS TABLE OF food_t;

   fridge_contents   foodstuffs_nt
                        := foodstuffs_nt (dessert_t ('Strawberries and cream',
                                                     'FRUIT',
                                                     'Backyard',
                                                     'N',
                                                     2001),
                                          cake_t ('Chocolate Supreme',
                                                  'CARBOHYDATE',
                                                  'Kitchen',
                                                  'Y',
                                                  2001,
                                                  8,
                                                  'Happy Birthday, Veva'));
BEGIN
   FOR indx IN fridge_contents.FIRST .. fridge_contents.LAST
   LOOP
      DBMS_OUTPUT.put_line (
            'Price of '
         || fridge_contents (indx).name
         || ' = '
         || fridge_contents (indx).price);
   END LOOP;
END;
/

As you can see from the above example, Oracle Database supports substitutability with object types as well. The easiest way to understand this concept is:

Every cake is a dessert; every dessert is a food. But, of course, not every food is a dessert, nor is every dessert a cake.

Take a look at that example of dynamic polymorphism. I declare a nested table of food_t, but I have no problem inserting a dessert and cake into the collection.

The same is true for relational tables. In the code below, I create a table whose single column is of type food_t. I then insert two rows into the table (a dessert and cake).

CREATE TABLE food_tab (food food_t)
/

DECLARE
   s_and_c    dessert_t
                 := dessert_t ('Strawberries and cream',
                               'FRUIT',
                               'Backyard',
                               'N',
                               2001);
   choc_sup   cake_t
                 := cake_t ('Chocolate Supreme',
                            'CARBOHYDATE',
                            'Kitchen',
                            'Y',
                            2001,
                            8,
                            'Happy Birthday, Veva');
BEGIN
   INSERT INTO food_tab
        VALUES (s_and_c);

   INSERT INTO food_tab
        VALUES (choc_sup);
END;
/

SELECT COUNT (*) FROM food_tab
/

2

I have heard from several developers who have used object types to implement applications within a comprehensive object-oriented framework. But it is rare. Most Oracle Database developers rely on the relational model of SQL and the procedural structure of PL/SQL to build their application backends.

There is, as I am sure you can imagine, lots more to learn about object types. If this approach intrigues you, check out the very useful Database Object-Relational Developer's Guide.

Well, there's your introduction to the different kinds of user-defined types in Oracle Database and PL/SQL. Did I leave anything important out? Let me know!

And here's a LiveSQL script that demonstrates all the object type-related functionality shown above.

PL/SQL Database Relational database

Published at DZone with permission of Steven Feuerstein, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

  • Designing a Blog Application Using Document Databases
  • Relational DB Migration to S3 Data Lake Via AWS DMS, Part I
  • NoSQL for Relational Minds
  • Business Logic Database Agent

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!