All the Methods to Object Types Madness

You can use object types to follow an object-oriented paradigm for your database development. Or you can just make occasional use of object types for collection datatypes. Or you might just use some of the object types pre-defined in Oracle Database, such as those used in the spatial and graph technologies.

In all of those cases, it'll come in very handy to understand the differences between static, member, map and order methods, and also how to invoke methods of supertypes from within a subtype method.

Transcript

1. • @sfonplsql • https://www.youtube.com/channel/PracticallyPerfectPLSQL • http://stevenfeuersteinonplsql.blogspot.com/ All the Methods to Object Type "Madness" Steven Feuerstein, Oracle Developer Advocate 1
2. Take advantage of our community websites. Deepen Your PL/SQL and SQL Expertise Oracle AskTOM – https://asktom.oracle.com Q&A site, Office Hours with database experts, and much more Oracle Dev Gym – https://devgym.oracle.com Quizzes, workouts and classes for an active learning experience Oracle LiveSQL – https://livesql.oracle.com 24x7 access to the latest release of Oracle Database, plus a script library and tutorials
3. Working with Object Type Methods Overview of object types Type hierarchies Static Methods Member Methods User-defined Constructors Map and Order Methods
4. About Object Types Object types are Oracle Database's version of classes. Not fully object-oriented, but close enough to offer an alternative development path. Supports substitutability, inheritance, dynamic polymorphism and more. To explore in detail, read the doc… Oracle Database's object-oriented features introduced in Oracle8 CREATE TYPE food_t AS OBJECT ( name VARCHAR2(100), food_group VARCHAR2 (100), grown_in VARCHAR2 (100) ) NOT FINAL ; CREATE TYPE dessert_t UNDER food_t ( contains_chocolate CHAR(1), year_created NUMBER(4) ) NOT FINAL ; https://bit.ly/oraot
5. About Object Type Methods Methods are procedures or functions that are associated with an object type. They are invoked using very familiar dot notation. But what goes on the left of the dot can be surprising. They're not the same as subprograms CREATE TYPE food_t AS OBJECT ( /* Attributes */ name VARCHAR2(100), food_group VARCHAR2 (100), grown_in VARCHAR2 (100), /* Methods */ STATIC FUNCTION version RETURN NUMBER, MEMBER FUNCTION price RETURN NUMBER ) NOT FINAL ;
6. Method Trivia Quiz In which version of Oracle Database were methods first available for use in PL/SQL? Time to show your age - I mean your wisdom. 8.x 7.3
7. Type Hierarchies Subtypes inherit attributes and methods from their supertypes. This is the BIG difference compared to tables and packages. The real added-value of an object-oriented approach LiveSQL: Object Type Hierarchies CREATE TYPE food_t AS OBJECT ( name VARCHAR2(100), food_group VARCHAR2 (100), grown_in VARCHAR2 (100) ) NOT FINAL ; CREATE TYPE dessert_t UNDER food_t ( contains_chocolate CHAR(1), year_created NUMBER(4) ) NOT FINAL ;
8. Static Methods Use STATIC keyword. Appropriate for type-level formulas, constants (e.g., the type version) Invoke with dot notation, with the name of the object type. Associated with / attached to the type. CREATE OR REPLACE TYPE food_ot AS OBJECT ( name VARCHAR2 (100), food_group VARCHAR2 (50) STATIC FUNCTION version RETURN VARCHAR2 ); CREATE OR REPLACE TYPE BODY food_ot IS STATIC FUNCTION version RETURN VARCHAR2 IS BEGIN RETURN '1.0.2'; END; END; BEGIN DBMS_OUTPUT.put_line (food_ot.version); END; LiveSQL: object type methods
9. Member Methods Member methods operate on the attributes of a specific instance. Add new ones to subtypes, override methods of supertypes, overload members. Associated with / attached to an instance of the type. CREATE OR REPLACE TYPE food_ot AS OBJECT ( name VARCHAR2 (100), food_group VARCHAR2 (50), grown_in VARCHAR2 (100 ), MEMBER FUNCTION to_string ( SELF IN food_ot) RETURN VARCHAR2 ), MEMBER FUNCTION to_string2 RETURN VARCHAR2, MEMBER PROCEDURE cook_food ) NOT FINAL; / LiveSQL: object type methods
10. User-defined Constructors Each type comes with its own pre- defined constructor Parameter list matches attribute list. You can add your own constructors. Change parameter list Perform app-specific logic Constructs and returns an instance of the type CREATE OR REPLACE TYPE food_t AS OBJECT ( name VARCHAR2 (100), food_group VARCHAR2 (100), grown_in VARCHAR2 (100), CONSTRUCTOR FUNCTION food_t RETURN SELF AS RESULT ) LiveSQL: user-defined constructors
11. Map and Order Methods If you store objects in a database table or manipulate them in PL/SQL, you might want to: Order the results in a query Check for equality or do other comparisons In this case, you will need to define a MAP or ORDER method. Needed to compare and sort object type instances LiveSQL: map and order
12. Map Method Performs a calculation on attributes of the object. The return value is used for comparisons and sorting. Operates on a single instance of the type LiveSQL: map and order CREATE OR REPLACE TYPE BODY food_ot IS MAP MEMBER FUNCTION food_mapping RETURN NUMBER IS BEGIN RETURN ( CASE self.food_group WHEN 'PROTEIN' THEN 30000 WHEN 'LIQUID' THEN 20000 WHEN 'CARBOHYDRATE' THEN 15000 WHEN 'VEGETABLE' THEN 10000 END + LENGTH (self.name)); END; END;
13. Order Method Single parameter is an object of the same type. Useful when comparison semantics cannot be handled by a MAP method. Returns a negative number, zero, or a positive number LiveSQL: map and order CREATE OR REPLACE TYPE BODY food_ot IS ORDER MEMBER FUNCTION food_ordering (other_food_in IN food_ot) RETURN INTEGER /* Subtypes are always less. Food > Dessert > Cake If of the same type, same rule AS for MAP: Vegetable < Carbohydrate < Liquid < Protein */ IS c_self_eq_of CONSTANT PLS_INTEGER := 0; c_self_gt_of CONSTANT PLS_INTEGER := 1; c_of_gt_self CONSTANT PLS_INTEGER := -1;
14. Questions? Comments?