Subscribe

Share

Database, SQL and PL/SQL

Object-Oriented PL/SQL

Quiz yourself on constructors and object types.

By Steven Feuerstein Oracle ACE Director

May/June 2016

The primary focus of PL/SQL is to enable developers to build efficient applications securely and quickly on relational tables in Oracle Database. Did you know, however, that you can also do object-oriented programming with PL/SQL? Instead of classes, Oracle Database offers object types, but almost all the features you might be familiar with from object orientation—including constructors, inheritance, dynamic polymorphism, and substitution—are available in object types. The quizzes in this issue of Oracle Magazine introduce you to some of PL/SQL’s object-oriented features.

Question 1
Constructors

One really nice aspect of object types is that PL/SQL automatically creates a constructor function with the same name as the type, which you can use to initialize an instance of an object type.

I execute these statements:

CREATE TYPE food_t AS OBJECT (
   food_name    VARCHAR2 (100)
 , food_group   VARCHAR2 (100)
)
NOT FINAL;
/
CREATE TYPE dessert_t UNDER food_t (
   contains_chocolate   CHAR (1)
)
NOT FINAL;
/

Which of the choices does not display “Broccoli” after execution?

a.

DECLARE
   my_fav_veggie food_t := 
      food_t ('Broccoli', 'Vegetable');
BEGIN
   DBMS_OUTPUT.PUT_LINE (my_fav_veggie.food_name);
END;
/

b.

DECLARE
   my_fav_veggie food_t;
BEGIN
   my_fav_veggie.food_name := 'Broccoli';
   DBMS_OUTPUT.PUT_LINE (my_fav_veggie.food_name );  
END;
/

c.

DECLARE
   my_fav_veggie food_t;
BEGIN
   my_fav_veggie := food_t ('Broccoli', 'Vegetable');
   DBMS_OUTPUT.PUT_LINE (my_fav_veggie.food_name);
END;
/


Quiz Summary

Unlike with “normal” variables such as numbers and strings and records, when you declare an object type instance, you must almost always initialize that instance with a call to the type’s constructor.

Read more about type constructor expressions.

 

Question 2
Object Type Hierarchy Inheritance

One of the most important and useful features of object-oriented programming is inheritance. When you define a hierarchy of types (parents and children), each child inherits the attributes and methods of the parent (and all of the parents’ parents as well!).

I execute these statements:

CREATE TYPE food_t AS OBJECT (
   food_name    VARCHAR2 (100)
 , food_group   VARCHAR2 (100)
 , MEMBER FUNCTION food_string RETURN VARCHAR2
) NOT FINAL;
/
CREATE OR REPLACE TYPE BODY food_t
IS
   MEMBER FUNCTION food_string RETURN VARCHAR2
   IS
   BEGIN
      RETURN (SELF.food_name || ' - ' || SELF.food_group);
   END;
END;
/
CREATE TYPE dessert_t UNDER food_t (
   contains_chocolate   CHAR (1)
);
/

Which of the choices does not display “Croissant – Fun” after execution?

a.

DECLARE
   my_croissant dessert_t := 
      dessert_t ('Croissant', 'Fun', 'Y');
BEGIN
   DBMS_OUTPUT.PUT_LINE (my_croissant.food_string);
END;
/

b.

DECLARE
   my_croissant food_t := food_t ('Croissant', 'Fun');
BEGIN
   DBMS_OUTPUT.PUT_LINE (my_croissant.food_string);
END;
/

c.

DECLARE
   my_croissant food_t := food_t ('Croissant', 'Fun');
BEGIN
   DBMS_OUTPUT.PUT_LINE (
       SELF.food_name || ' - ' || SELF.food_group);
END;
/


Quiz Summary

Inheritance enables you to create elegant hierarchies of types and avoid redundancies between parent and child types.

Read more about working with object types.

 

Question 3
Incomplete Object Types

When you build an extensive hierarchy of object types, you may want to include in that hierarchy an “incomplete” or template type. This sort of type declares, in essence, the methods that must be implemented by child types but does not itself include such an implementation.

I execute these statements:

CREATE TYPE food_t AS OBJECT (
   food_name    VARCHAR2 (100)
 , food_group   VARCHAR2 (100)
 , NOT INSTANTIABLE MEMBER 
      FUNCTION food_string RETURN VARCHAR2      
)  NOT INSTANTIABLE NOT FINAL;
/
CREATE TYPE dessert_t UNDER food_t (
   contains_chocolate   CHAR (1)
 , OVERRIDING MEMBER FUNCTION food_string RETURN VARCHAR2
);
/
CREATE OR REPLACE TYPE BODY dessert_t
IS
   OVERRIDING MEMBER FUNCTION food_string RETURN VARCHAR2
   IS
   BEGIN
      RETURN (
         CASE SELF.contains_chocolate 
            WHEN 'Y' THEN UPPER (SELF.food_name)
            ELSE SELF.food_name
         END);
   END;
END;
/

Which of the choices displays “MINT” after execution?

a.

DECLARE
   my_mint dessert_t := dessert_t ('Mint', 'Candy', 'Y');
BEGIN
   DBMS_OUTPUT.PUT_LINE (my_mint.food_string);
END;
/

b.

DECLARE
   my_mint food_t := food_t ('Mint', 'Candy');
BEGIN
   DBMS_OUTPUT.PUT_LINE ('MINT');
END;
/

c.

DECLARE
   my_mint dessert_t := dessert_t ('Mint', 'Candy', 'N');
BEGIN
   DBMS_OUTPUT.PUT_LINE (my_mint.food_string);
END;
/


Quiz Summary

When you declare a type as NOT INSTANTIABLE, you cannot declare an instance (variable) of this type. It can serve only as a supertype for other types, a kind of template or interface.

Read more about incomplete object types.

Next Steps

 TEST your SQL and PL/SQL knowledge.

 WATCH Feuerstein’s “Practically Perfect PL/SQL” videos.

 MEET the Oracle Developer Advocates team.

Photography by Aaron Burson, Unsplash