Comparing Records for Equality

View All Scripts Login to Run Script

Script Name Comparing Records for Equality
Description PL/SQL does not provide native support for checking the equality (or inequality) of two records. Instead, you must write your own custom code to do so. Generally, you will compare the values of corresponding fields in the record, and don't forget about NULLs! PL/SQL Challenge quiz link: https://plsqlchallenge.oracle.com/pls/apex/f?p=10000:659:::NO:659:P659_COMP_EVENT_ID,P659_QUESTION_ID,P659_QUIZ_ID:985569,226,&cs=1F8A7F005655C2C8B20B4B2EB2A6BDAC0
Area PL/SQL General
Contributor Steven Feuerstein
Created Tuesday August 23, 2016

Statement 1

CREATE TABLE my_favorite_snacks
(
   name       VARCHAR2 (100),
   calories   INTEGER
)

Table created.

Statement 2

A little helper procedure

Convert Boolean to String for Display

CREATE OR REPLACE PROCEDURE my_show_boolean (val IN BOOLEAN) 
IS 
BEGIN 
   DBMS_OUTPUT.put_line ( 
      CASE val WHEN TRUE THEN 'TRUE' WHEN FALSE THEN 'FALSE' ELSE 'NULL' END); 
END my_show_boolean;

Procedure created.

Statement 3

Well, you get to decide what to check for record equality, but this one probably makes the wrong choice!

Whoops! Incomplete Check...

DECLARE 
   CURSOR tasty_cur 
   IS 
      SELECT name, calories FROM my_favorite_snacks; 
 
   stevens_favorites   tasty_cur%ROWTYPE; 
   vevas_favorites     tasty_cur%ROWTYPE; 
   l_equal             BOOLEAN; 
BEGIN 
   stevens_favorites.name := 'Smoothie'; 
   vevas_favorites.name := 'Chocolate-covered Almonds'; 
 
   l_equal := stevens_favorites.calories = vevas_favorites.calories; 
    
   my_show_boolean (l_equal); 
END;

NULL

Statement 4

It's a lovely idea, but at least up through Oracle Database 12c Release 1, this syntax is not supported.

Cannot check for "=" between records

DECLARE 
   CURSOR tasty_cur 
   IS 
      SELECT name, calories FROM my_favorite_snacks; 
 
   stevens_favorites   tasty_cur%ROWTYPE; 
   vevas_favorites     tasty_cur%ROWTYPE; 
   l_equal             BOOLEAN; 
BEGIN 
   stevens_favorites.name := 'Smoothie'; 
   vevas_favorites.name := 'Chocolate-covered Almonds'; 
 
   l_equal := stevens_favorites = vevas_favorites; 
 
   my_show_boolean (l_equal); 
END;

ORA-06550: line 13, column 33:
PLS-00306: wrong number or types of arguments in call to '='
ORA-06550: line 13, column 4:
PL/SQL: Statement ignored

More Details: https://docs.oracle.com/error-help/db/ora-06550

Statement 5

Check for equality oh and also maybe both are NULL (of course, in your situation two NULLs may NOT indicate equality - you need to decide on this) - for each field in the record. And, of course, if a field in the record is itself a record or collection, you will have to write more complex, nested checks.

Verbose But Accurate Comparison

DECLARE 
   CURSOR tasty_cur 
   IS 
      SELECT name, calories FROM my_favorite_snacks; 
 
   stevens_favorites   tasty_cur%ROWTYPE; 
   vevas_favorites     tasty_cur%ROWTYPE; 
   l_equal             BOOLEAN; 
BEGIN 
   stevens_favorites.name := 'Smoothie'; 
   vevas_favorites.name := 'Chocolate-covered Almonds'; 
 
   l_equal := 
          (   stevens_favorites.name = vevas_favorites.name 
           OR (    stevens_favorites.name IS NULL 
               AND vevas_favorites.name IS NULL)) 
      AND (   stevens_favorites.calories = vevas_favorites.calories 
           OR (    stevens_favorites.calories IS NULL 
               AND vevas_favorites.calories IS NULL)); 
 
   my_show_boolean (l_equal); 
END;

FALSE

Statement 6

Move the equality check into its own function and call that. In the "real world", this function would be best placed inside a package and exposed in the package specification. that way, it can called from anywhere in your code base.

My Favorite Solution

DECLARE 
   CURSOR tasty_cur 
   IS 
      SELECT name, calories FROM my_favorite_snacks; 
 
   stevens_favorites   tasty_cur%ROWTYPE; 
   vevas_favorites     tasty_cur%ROWTYPE; 
   l_equal             BOOLEAN; 
 
   FUNCTION favorites_equal (rec1   IN my_favorite_snacks%ROWTYPE, 
                             rec2   IN my_favorite_snacks%ROWTYPE) 
      RETURN BOOLEAN 
   IS 
   BEGIN 
      RETURN     (   rec1.name = rec2.name 
                  OR (rec1.name IS NULL AND rec2.name IS NULL)) 
             AND (   rec1.calories = rec2.calories 
                  OR (rec1.calories IS NULL AND rec2.calories IS NULL)); 
   END; 
BEGIN 
   stevens_favorites.name := 'Smoothie'; 
   vevas_favorites.name := 'Chocolate-covered Almonds'; 
 
   l_equal := favorites_equal (stevens_favorites, vevas_favorites); 
 
   my_show_boolean (l_equal); 
END;

FALSE

Statement 7

DROP PROCEDURE my_show_boolean

Procedure dropped.

Statement 8

DROP TABLE my_favorite_snacks

Table dropped.

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database