How to make enum types in PL/SQL?
I don't think that solution, provided by A.B.Cade is totally correct. Let's assume procedure like this:
procedure TestEnum(enum_in lights);
What is the value of enum_in
? red? yellow? green?
I propose another solution. Here is package example
CREATE OR REPLACE PACKAGE pkg_test_enum IS
SUBTYPE TLight IS BINARY_INTEGER RANGE 0..2;
Red CONSTANT TLight := 0;
Yellow CONSTANT TLight := 1;
Green CONSTANT TLight := 2;
--get sting name for my "enum" type
FUNCTION GetLightValueName(enum_in TLight) RETURN VARCHAR2;
PROCEDURE EnumTest(enum_in TLight);
END pkg_test_enum;
CREATE OR REPLACE PACKAGE BODY pkg_test_enum IS
FUNCTION GetLightValueName(enum_in TLight)
RETURN VARCHAR2
IS
ResultValue VARCHAR2(6);
BEGIN
CASE enum_in
WHEN Red THEN ResultValue := 'Red';
WHEN Green THEN ResultValue := 'Green';
WHEN Yellow THEN ResultValue := 'Yellow';
ELSE ResultValue := '';
END CASE;
RETURN ResultValue;
END GetLightValueName;
PROCEDURE EnumTest(enum_in TLight)
IS
BEGIN
--do stuff
NULL;
END EnumTest;
END pkg_test_enum;
I can now use TLight
in different packages. I can now test enum_in
against predefined values or null.
Here is usage example
begin
pkg_test_enum.EnumTest(pkg_test_enum.Red);
end;
Besides, you can make this type not nullable.
SUBTYPE TLight IS BINARY_INTEGER RANGE 0..2 NOT NULL;
This blog describes a way to do it using constant values
In addition to the constants, the blog defines a subtype for valid colors.
SQL> declare
2 RED constant number(1):=1;
3 GREEN constant number(1):=2;
4 BLUE constant number(1):=3;
5 YELLOW constant number(1):=4;
6 --
7 VIOLET constant number(1):=7;
8 --
9 subtype colors is binary_integer range 1..4;
10 --
11 pv_var colors;
12 --
13 function test_a (pv_var1 in colors) return colors
14 is
15 begin
16 if(pv_var1 = YELLOW) then
17 return(BLUE);
18 else
19 return(RED);
20 end if;
21 end;
22 --
The closest think I could think of is:
create or replace type lights as object
(
red varchar2(8),
yellow varchar2(8),
green varchar2(8),
constructor function lights return self as result
)
and the body:
create or replace type body lights is
constructor function lights return self as result is
begin
self.red = 'red';
self.yellow = 'yellow';
self.green = 'green';
return;
end;
end;
Then in the code you can use it:
declare
l lights := new lights;
begin
dbms_output.put_line(l.red);
end;