Недокументированный Oracle – Использование ENUM в PL/SQL
Несколько недель назад мой коллега спросил меня о возможности создания перечисляемых типов (ENUM) в PL/SQL, также как это позволяют делать такие языки как Си. Реальный пример, который этот человек прислал мне по почте слишком специфичный для его компании и поэтому я не хочу использовать его здесь, так же я не хочу без его разрешения раскрывать информацию о том кем он работает. Поэтому я буду приводить простые примеры.
Поскольку у меня есть кое-какие навыки в PL/SQL, то мои мысли развернулись в сторону обычных типов (TYPE). Я подумал что должна быть возможность создать тип, который по сути являлся бы типом с органичениями (constrained TYPE) или перечисляемым типом. Простым примером такого типа является логический (BOOLEN) тип в PL/SQL, который ограничен значениям TRUE и FALSE.
SQL> declare 2 pv_var boolean; 3 begin 4 pv_var:=TRUE; 5 pv_var:=FALSE; 6 end; 7 / PL/SQL procedure successfully completed. SQL>
Это работает как и предполагалось. Но что будет если мы попробуем присвоить другое значение?
SQL> declare
2 pv_var boolean;
3 begin
4 pv_var:=7;
5 end;
6 /
pv_var:=7;
*
ERROR at line 4:
ORA-06550: line 4, column 9:
PLS-00382: expression is of wrong type
ORA-06550: line 4, column 1:
PL/SQL: Statement ignored
SQL> declare
2 pv_var boolean;
3 begin
4 pv_var:='NOT';
5 end;
6 /
pv_var:='NOT';
*
ERROR at line 4:
ORA-06550: line 4, column 9:
PLS-00382: expression is of wrong type
ORA-06550: line 4, column 1:
PL/SQL: Statement ignored
SQL>
Это не работает, потому что срабатывают ограничения — и это здорово. Интересно также то, что мы всегда может присвоить переменной NULL и таким образом, в совокупности с тем что говорилось выше, наш ENUM-тип может содержать три возможных значения — TRUE, FALSE и NULL:
SQL> declare 2 pv_var boolean; 3 begin 4 pv_var:=NULL; 5 end; 6 / PL/SQL procedure successfully completed. SQL>
Значения типа BOOLEAN определены в файле standart.sql (stdspec.sql и stdbody.sql) следующим образом:
type BOOLEAN is (FALSE, TRUE);
Это выглядит как раз так как нужно моему коллеге. В самом деле, язык ADA (на котором основан PL/SQL) поддерживает перечисления в таком же формате в каком тип BOOLEAN описан в пакете STANDART. А можем ли мы использовать такой синтаксис в PL/SQL? Например:
SQL> declare
2 type colors is (RED, GREEN, BLUE, YELLOW);
3 begin
4 null;
5 end;
6 /
type colors is (RED, GREEN, BLUE, YELLOW);
*
ERROR at line 2:
ORA-06550: line 2, column 16:
PLS-00505: User Defined Types may only be defined as PLSQL Tables or Records
ORA-06550: line 2, column 1:
PL/SQL: Item ignored
SQL>
Этот простой тест показывает невозможность определения ENUM-типов в PL/SQL также как в ADA. Мы можем создавать типы которые описывают только таблицы или записи. Это раздражает, т.к. иллюстрирует недокументированную особенность Oracle: сам Oracle использует определение TYPE для создания перечисляемого типа в пакете STANDART, но не позволяет нам делать то же самое. Понятно, что такой синтаксис должен пройти компиляцию, если код находится в стандартном пакете, а не где-нибудь, поэтому в стандартный пакет можно (но естественно не рекомендуется так как повлияет на поддержку и гарантию) добавлять новые перечисления. В том месте компилятора которое запускает ошибку PLS-00505 , Oracle должен проводить проверку — если TYPE определяет перечисление и находится не в пакете STANDART. Выше я вошел в Oracle как AS SYSDBA, так что это не обычный пользователь, который выполняет код из пакета STANDART. /I am also logged in AS SYSDBA above so its not the user that allows this but the location (STANDARD PACKAGE)/.
Почему Oracle использует синтаксис, разрешенный только для него в пакете STANDART и неразрешенный для нас? — Ну, мои знания подсказывают, что разгадка состоит в том что разработчики использовали этот синтаксис в очень ограниченном контексте, т.е. для выполнения конкретной задачи и не более. Они, должно быть, удостоверились в том что BOOLEAN компилируется правильно, но они не проверяли или не реализовывали все остальное поведение перечислений, поэтому мы не можем использовать такой синтаксис. Это также является причиной, по которой простое добавление перечислений в стандартный пакет может откомпилироваться, но результаты, вероятно, будут неопределенными в некоторых случаях, когда тестирование для проверки поведения перечислений не производили должным образом.
Довольно интересно что Oracle ограничивает типы (или создает типы-перечисления) используя для этого ADA-синтаксис, в то время как сам Oracle поддерживает синтакис SUBTYPE для ограниченных и неограниченных типов. Неограниченный тип это по сути просто еще одно название (ALIAS) для другого типа. Примеры можно найти в пакете STANDART. Ограниченный тип — это тип который устанавливает интервал численных значений (которые доступны пользователю в рамках PL/SQL; и это не позволяет ему выглядеть как связь между ENUM-идентификаторами, которые в Си были бы численно ассоциированы и Си представлял бы ENUM-значения как целые) и численные ограничения. Это привело меня к идее о том что ENUM можно эмулировать через константы (CONSTANT) и подтипы (SUBTYPE) и эту идею я предложил моему коллеге:
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 begin 13 pv_var:=YELLOW; 14 end; SQL> / PL/SQL procedure successfully completed. SQL> 13 13* pv_var:=YELLOW; SQL> c/YELLOW/VIOLET/ 13* pv_var:=VIOLET; SQL> / declare * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error ORA-06512: at line 13 SQL>
Как вы могли заметить я создал несколько констант (CONSTANT) которые представляют значения ENUM-списка и создал подтип (SUBTYPE) который ограничивает значения своих переменных этими константами.
Это не идеальное решение, но очень близкое. Не идеальное, потому что мы не можем использовать синтаксис TYPE BOOLEAN IS (TRUE,FALSE) как Oracle делает в стандартном пакете, или TYPE COLORS IS (RED,GREEN,BLUE,YELLOW) как мы можем сделать в ADA; также есть сомнения по использованию этого подхода в разных частях программы — идеальным был бы подход при котором определение происходило бы единожды, но это неизбежно приводит нас к использованию PACKAGE.CONSTANT. Еще здесь не хватае связи слов (элементов перечисления) и типов, хотя мы здесь имеем почти все то что делает Си, но кроме того Си делает для нас трансляцию отображение сказанного YELLOW в число 4 в своих внутренностях и позволяет нам использовать YELLOW без отдельного определения. Для иллюстрации этой идей моему коллеге, я создал простой пример программы, которая позволяет использовать «enum» как тип результата функции и т.д., т.е. в том же контексте, в котором я бы применял перечисления в своей Си-программе.
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 --
23 begin
24 pv_var:=test_a(YELLOW);
25 if (pv_var=YELLOW) then
26 dbms_output.put_line('YELLOW');
27 elsif(pv_var=RED) then
28 dbms_output.put_line('RED');
29 elsif(pv_var=BLUE) then
30 dbms_output.put_line('BLUE');
31 elsif(pv_var=GREEN) then
32 dbms_output.put_line('GREEN');
33 else
34 dbms_output.put_line('UN-KNOWN');
35 end if;
36 end;
37 /
BLUE
PL/SQL procedure successfully completed.
SQL>
Хорошо, я думаю хватит пока о ENUM в PL/SQL. Я люблю заглядывать во внутренности Oracle и PL/SQL и в стандартный пакет, где можно сделать множество замечательных открытий. Например таких, как определение VARCHAR2, которое сделано с посредством ADA-синтаксиса определения новых типов. В ADA мы можем сделать следующее:
TYPE BOOL IS NEW BOOLEAN; mybool: BOOL
Что похоже на синтаксис используемый в PL/SQL, но его использование вы найдете, опять же, только в стандартном пакете. В исходных кодах, поставляемым вместе с 11g, я нашел 10 применений такого синтаксиса, и во всех случаях он использовался для описания деталей CHAR_BASE или DATE_BASE. Если мы попробуем использовать похожий синтакс в пользовательском пространстве как показано в примере ниже, то мы получим:
SQL> declare
2 type mychar is new char_base;
3 begin
4 null;
5 end;
SQL> /
type mychar is new char_base;
*
ERROR at line 2:
ORA-06550: line 2, column 16:
PLS-00504: type CHAR_BASE may not be used outside of package STANDARD
ORA-06550: line 2, column 1:
PL/SQL: Item ignored
SQL>
Интересно, получается что есть недокументированные возможности языка PL/SQL которые пришли из стандарта ADA но которые мы не может использовать в нашем PL/SQL коде. Полезно, как я уже говорил, углубляться в код, идущий в комплекте с Oracle для того чтобы увидеть что они такого используют что нам нельзя. В некоторых случаях мы можем расширить наши знания и может быть мы можем найти возможность которая будет работать и которая будет полезной для каких-либо задач. Для меня это определенно изучение внутренностей Oracle. Развлекайтесь!
Оригинал: Undocumented Oracle – Using ENUM’s in PL/SQL