|
A Solution Using User
Defined Data Types |
Oracle provides a large range of
built-in data types but still there are times when we need to define
our own as a way of best constraining the integrity of our data.
Oracle's SIGNTYPE is great in that it will accept only integer
values of -1, 0, and 1. But often, I find, that what I really want
is a variable that will only hold two values, 0 and 1, that I can
use as a switch.
If you've always wanted to do this too ... here is a demo that shows
how to do it. |
set serveroutput on
DECLARE
SUBTYPE flagtype
IS PLS_INTEGER RANGE 0..1;
x flagtype;
BEGIN
FOR i IN -2 .. 2 LOOP
BEGIN
x := i;
dbms_output.put_line('Success: ' || TO_CHAR(x));
EXCEPTION
WHEN others THEN
dbms_output.put_line('Can not assign
' || TO_CHAR(i) || ' To Flagtype');
END;
END LOOP;
END;
/ |
Could it be easier?
Now lets extend this as a way of enforcing a business rule using the
airplanes table demo table. If you haven't downloaded it, already,
you can do so now [here].
The test we will do will generate 50,000 exceptions. First we will
use the traditional method |
DECLARE
CURSOR ln_cur IS
SELECT line_number, COUNT(*) testval
FROM airplanes
GROUP BY line_number;
too_many_planes EXCEPTION;
BEGIN
FOR ln_rec IN ln_cur LOOP
BEGIN
IF ln_rec.testval > 4 THEN
RAISE too_many_planes;
END IF;
EXCEPTION
WHEN too_many_planes THEN
NULL;
END;
END LOOP;
END;
/ |
| Now lets try handling it with a
defined data type. |
DECLARE
SUBTYPE testtype
IS PLS_INTEGER RANGE 1..4;
x testtype;
CURSOR ln_cur IS
SELECT line_number, COUNT(*) testval
FROM airplanes
GROUP BY line_number;
BEGIN
FOR ln_rec IN ln_cur LOOP
BEGIN
x := ln_rec.testval;
EXCEPTION
WHEN others THEN
NULL;
END;
END LOOP;
END;
/ |
| Which one is better I will leave for
others to consider. On my demo laptop they time out almost
identically. The subtype being very slightly faster. But what I like
about using the subtype is that it more naturally treats a business
rule violation as an exception. |
