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 1One 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; /
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.
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; /
Inheritance enables you to create elegant hierarchies of types and avoid redundancies between parent and child types.
Read more about working with 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; /
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.