There is no default way to do this. In other words, if I create a simple object type, add it as a column to a table, and try to compare or sort, all I get are errors. Let's take a look. First I will create a table that has an object type as a column and add a couple of rows.
CREATE TYPE food_ot AS OBJECT
(
name VARCHAR2 (100),
food_group VARCHAR2 (50),
grown_in VARCHAR2 (100)
)
NOT FINAL
/
CREATE TABLE meals
(
served_on DATE,
main_course food_ot
);
/
BEGIN
INSERT INTO meals (served_on, main_course)
VALUES (SYSDATE, food_ot ('Shrimp cocktail', 'PROTEIN', 'Ocean'));
INSERT INTO meals (served_on, main_course)
VALUES (SYSDATE + 1, food_ot ('House Salad', 'VEGETABLE', 'Farm'));
COMMIT;
END;
/
SELECT m.main_course.name name
FROM meals m
ORDER BY m.main_course.name
/
NAME
---------------
House salad
Shrimp Cocktail
SELECT m.main_course.name name
FROM meals m, meals m2
WHERE m.main_course = m2.main_course
ORDER BY m.main_course.name
/
NAME
---------------
House salad
Shrimp Cocktail
CREATE TYPE food_with_clob_ot AS OBJECT
(
name VARCHAR2 (100),
grown_in CLOB
)
NOT FINAL
/
CREATE TABLE meals_with_clobs
(
served_on DATE,
main_course food_with_clob_ot
);
/
SELECT m.main_course.name name
FROM meals_with_clobs m, meals_with_clobs m2
WHERE m.main_course = m2.main_course
ORDER BY m.main_course.name
/
ORA-22901: cannot compare VARRAY or LOB attributes of an object type
SELECT m.main_course.name name
FROM meals m
ORDER BY m.main_course
/
ORA-22950: cannot ORDER objects without MAP or ORDER method
SELECT m.main_course.name name
FROM meals m, meals m2
WHERE m.main_course > m2.main_course
/
ORA-22950: cannot ORDER objects without MAP or ORDER method
DECLARE
m1 food_ot := food_ot ('Shrimp cocktail', 'PROTEIN', 'Ocean');
m2 food_ot := food_ot ('House Salad', 'VEGETABLE', 'Farm');
BEGIN
IF m1 = m1
THEN
DBMS_OUTPUT.put_line ('Equal');
END IF;
IF m1 <> m2
THEN
DBMS_OUTPUT.put_line ('Unequal');
END IF;
END;
/
PLS-00526: A MAP or ORDER function is required for comparing objects in PL/SQL.
If you "cannot ORDER objects without MAP or ORDER method," perhaps you should create a MAP or ORDER method. :-)
A MAP method is a member method (attached to an instance of the type) that returns a "mapping" of the object value onto a datatype that Oracle Database already knows how to compare, such as a number or string.
An ORDER member method compares two different instances of a type and returns a flag value that indicates their relative ordering.
You can only have one MAP or ORDER method in an object type definition. They cannot co-exist.
The MAP Method
A MAP method performs calculations on the attributes of the object to produce a return value of ny Oracle built-in data types (except LOBs and BFILEs) and ANSI SQL types such as CHARACTER or REAL.This method is called automatically by Oracle Database to evaluate such comparisons as obj_1 > obj_2 and comparisons that are implied by the DISTINCT, GROUP BY, UNION, and ORDER BY clauses - since these all require sorting by rows in the table.
"Automatic" means:
1. You never invoke a map method directly in your code.
2. Assuming the type has a map method called "mapme", then when you write a comparison like this
obj_1 > obj_2
it is automatically translated (invisibly to you) to:
obj_1.mapme() > obj_2.mapme()
Let's add a map method to the food type. I'll keep it simple and silly. Proteins rate higher than liquids, which rate higher than carbs, which rate higher than vegetables. Take that number and add to the length of the food name. Then return that number for mapping. Finally, add some rows.
CREATE TYPE food_t AS OBJECT
(name VARCHAR2 (100)
, food_group VARCHAR2 (100)
, grown_in VARCHAR2 (100)
, MAP MEMBER FUNCTION food_mapping
RETURN NUMBER
)
NOT FINAL;
/
CREATE OR REPLACE TYPE BODY food_t
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;
/
BEGIN
-- Populate the meal table
INSERT INTO meals
VALUES (SYSDATE, food_ot ('Shrimp cocktail', 'PROTEIN', 'Ocean'));
INSERT INTO meals
VALUES (SYSDATE + 1, food_ot ('Stir fry tofu', 'PROTEIN', 'Wok'));
INSERT INTO meals
VALUES (SYSDATE + 1,
food_ot ('Peanut Butter Sandwich',
'CARBOHYDRATE',
'Kitchen'));
INSERT INTO meals
VALUES (SYSDATE + 1,
food_ot ('Brussels Sprouts', 'VEGETABLE', 'Backyard'));
COMMIT;
END;
/
SELECT m.main_course.name name
FROM meals m
ORDER BY main_course
/
NAME
----------------------
Brussels Sprouts
Peanut Butter Sandwich
Stir fry tofu
Shrimp cocktail
SELECT m1.main_course.name name
FROM (SELECT *
FROM meals m
WHERE m.main_course.name LIKE 'S%') m1,
(SELECT *
FROM meals m
WHERE m.main_course.name NOT LIKE 'S%') m2
WHERE m1 > m2
ORDER BY m1.main_course
/
NAME
---------------
Stir fry tofu
Stir fry tofu
Shrimp cocktail
Shrimp cocktail
DECLARE
ot1 food_ot := food_ot ('Eggs benedict', 'PROTEIN', 'Farm');
ot2 food_ot := food_ot ('Brussels Sprouts', 'VEGETABLE', 'Backyard');
ot3 food_ot := food_ot ('Brussels Sprouts', 'VEGETABLE', 'Backyard');
BEGIN
IF ot1 = ot2
THEN
DBMS_OUTPUT.put_line ('equal - incorrect');
ELSE
DBMS_OUTPUT.put_line ('not equal - correct');
END IF;
IF ot2 <> ot3
THEN
DBMS_OUTPUT.put_line ('not equal - incorrect');
ELSE
DBMS_OUTPUT.put_line ('equal - correct');
END IF;
END;
/
not equal - correct
equal - correct
The ORDER Method
Unlike map methods, order methods cannot determine the order of a number of objects. They simply tell you that the current object is less than, equal to, or greater than the object that it is being compared to, based on the criterion used.An order method is a function for an object (SELF), with one declared parameter that is an object of the same type. The method must return either a negative number, zero, or a positive number. This value signifies that the object (the implicit undeclared SELF parameter) is less than, equal to, or greater than the declared parameter object.
As with map methods, an order method, if one is defined, is called automatically whenever two objects of that type need to be compared.
Order methods are useful where comparison semantics may be too complex to use a map method.
Let's build an order method for the food type. Let's start with the specification for the food type, and also create the type hierarchy:
CREATE TYPE food_ot AS OBJECT
(
name VARCHAR2 (100),
food_group VARCHAR2 (100),
ORDER MEMBER FUNCTION food_ordering (other_food_in IN food_ot)
RETURN INTEGER
)
NOT FINAL;
/
CREATE TYPE dessert_ot UNDER food_ot (
contains_chocolate CHAR (1)
, year_created NUMBER (4)
)
NOT FINAL;
/
CREATE TYPE cake_ot UNDER dessert_ot (
diameter NUMBER
, inscription VARCHAR2 (200)
);
/
- Since an instance could be of food, dessert or cake, the first rule is that a supertype is always greater than a subtype. I use self IS OF (ONLY my_type) syntax to determine the type of the instance.
- I use a string-indexed collection, l_order_by_food_group to establish the hierarchy of ordering by food group. (I really like string-indexed collections!)
- If after checking for supertype/subtype ordering, I know that other_food_in is of the same type as SELF, then I set return value according to food group.
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
TYPE order_by_food_group_t IS TABLE OF PLS_INTEGER
INDEX BY VARCHAR2 (100);
l_order_by_food_group order_by_food_group_t;
c_self_eq_of CONSTANT PLS_INTEGER := 0;
c_self_gt_of CONSTANT PLS_INTEGER := 1;
c_of_gt_self CONSTANT PLS_INTEGER := -1;
l_ordering PLS_INTEGER := c_self_eq_of;
PROCEDURE initialize
IS
BEGIN
l_order_by_food_group ('PROTEIN') := 1000;
l_order_by_food_group ('LIQUID') := 100;
l_order_by_food_group ('CARBOHYDRATE') := 10;
l_order_by_food_group ('VEGETABLE') := 1;
END initialize;
BEGIN
initialize;
IF self IS OF (ONLY food_ot)
THEN
l_ordering :=
CASE
WHEN other_food_in IS OF (ONLY food_ot) THEN c_self_eq_of
ELSE c_self_gt_of
END;
ELSIF self IS OF (ONLY dessert_t)
THEN
l_ordering :=
CASE
WHEN other_food_in IS OF (ONLY dessert_t) THEN c_self_eq_of
WHEN other_food_in IS OF (ONLY food_ot) THEN c_of_gt_self
ELSE c_self_gt_of
END;
ELSE
/* It is cake. */
l_ordering :=
CASE
WHEN other_food_in IS OF (ONLY cake_t) THEN c_self_eq_of
ELSE c_of_gt_self
END;
END IF;
IF l_ordering = c_self_eq_of
THEN
/*
Further analysis is needed.
*/
l_ordering :=
CASE
WHEN l_order_by_food_group (self.food_group) =
l_order_by_food_group (other_food_in.food_group)
THEN
c_self_eq_of
WHEN l_order_by_food_group (self.food_group) >
l_order_by_food_group (other_food_in.food_group)
THEN
c_self_gt_of
WHEN l_order_by_food_group (self.food_group) <
l_order_by_food_group (other_food_in.food_group)
THEN
c_of_gt_self
END;
END IF;
RETURN l_ordering;
END;
END;
/
BEGIN
-- Populate the meal table
INSERT INTO meals
VALUES (SYSDATE, food_ot ('Shrimp cocktail', 'PROTEIN'));
INSERT INTO meals
VALUES (SYSDATE + 1, food_ot ('Stir fry tofu', 'PROTEIN'));
INSERT INTO meals
VALUES (SYSDATE + 1,
dessert_ot ('Peanut Butter Sandwich',
'CARBOHYDRATE',
'N',
1700));
INSERT INTO meals
VALUES (SYSDATE + 1, food_ot ('Brussels Sprouts', 'VEGETABLE'));
INSERT INTO meals
VALUES (SYSDATE + 1,
cake_ot ('Carrot Cake',
'VEGETABLE',
'N',
1550,
12,
'Happy Birthday!'));
COMMIT;
END;
/
SELECT m.main_course.name name
FROM meals m
ORDER BY main_course
/
NAME
----------------------
Carrot Cake
Peanut Butter Sandwich
Brussels Sprouts
Shrimp cocktail
Stir fry tofu
SELECT m1.main_course.name name
FROM (SELECT *
FROM meals m
WHERE m.main_course.name LIKE 'S%') m1,
(SELECT *
FROM meals m
WHERE m.main_course.name NOT LIKE 'S%') m2
WHERE m1.main_course > m2.main_course
ORDER BY m1.main_course
/
NAME
---------------
Shrimp cocktail
Shrimp cocktail
Shrimp cocktail
Stir fry tofu
Stir fry tofu
Stir fry tofu
DECLARE
ot1 food_ot := food_ot ('Eggs benedict', 'PROTEIN');
ot2 food_ot := food_ot ('Brussels Sprouts', 'VEGETABLE');
ot3 food_ot := dessert_ot ('Brownie', 'SUGAR', 'Y', 1943);
ot4 food_ot := cake_ot (
'Carrot Cake', 'VEGETABLE', 'N', 1550, 12, 'Happy Birthday!');
BEGIN
IF ot1 = ot1
THEN
DBMS_OUTPUT.put_line ('equal - correct');
ELSE
DBMS_OUTPUT.put_line ('not equal - incorrect');
END IF;
IF ot1 = ot2
THEN
DBMS_OUTPUT.put_line ('equal - incorrect');
ELSE
DBMS_OUTPUT.put_line ('not equal - correct');
END IF;
IF ot2 <> ot3
THEN
DBMS_OUTPUT.put_line ('not equal - correct');
ELSE
DBMS_OUTPUT.put_line ('equal - incorrect');
END IF;
IF ot2 > ot3
THEN
DBMS_OUTPUT.put_line ('food > dessert - correct');
ELSE
DBMS_OUTPUT.put_line ('food < dessert - incorrect');
END IF;
IF ot3 > ot4
THEN
DBMS_OUTPUT.put_line ('dessert > cake - correct');
ELSE
DBMS_OUTPUT.put_line ('dessert < cake - incorrect');
END IF;
IF ot3 < ot4
THEN
DBMS_OUTPUT.put_line ('dessert < cake - incorrect');
ELSE
DBMS_OUTPUT.put_line ('dessert > cake - correct');
END IF;
END;
/
equal - correct
not equal - correct
not equal - correct
food > dessert - correct
dessert > cake - correct
dessert > cake - correct
Check Out the Entire Series
Visit this post that gives you quick access to all the articles in the series.P.S. Don't forget that if you'd like to try out all this code for yourself, all you have to do is run this LiveSQL script.
Comments
Post a Comment