Object-Oriented PL/SQL
Quiz yourself on constructors and object types.
By Steven Feuerstein
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 1Constructors
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.
DISCLAIMER: We've captured these popular historical magazine articles for your reference. Links etc contained within these article possibly won't work. These articles are provided as-is with no guarantee that the information within them is the best advice for current versions of the Oracle Database.