В базе данных хранится информация, которая считывается и используется в приложении. Для работы с этой информацией используется как SQL, так и PL/SQL. При этом программы PL/SQL часто считывают информацию из базы данных Oracle, присваивают результат локальным переменным и после соответствующей обработки записывают информацию из переменных обратно в базу данных.
Предположим, что таблица с данными о компаниях содержит столбец NAME
с типом данных VARCHAR2(60)
. Локальная переменная для хранения значений этого столбца может быть объявлена следующим образом:
DECLARE cname VARCHAR2(60);
Допустим, информация о компании используется в некотором приложении. В нем могут быть десятки различных процедур и отчетов, содержащих одно и то же объявление PL/SQL VARCHAR(60)
. И все это прекрасно работает... пока не изменятся бизнес-требования или администратора базы данных не охватит жажда перемен. Тогда он запросто изменяет определение типа столбца NAME
таблицы на VARCHAR2(100)
, чтобы в нем помещались более длинные названия. И внезапно оказывается, что в таблицу могут попасть такие данные, что при считывании их в переменную cname
будет инициироваться исключение VALUE_ERROR
.
Программа становится несовместимой со структурой исходных данных. Все объявления cname
необходимо изменить и протестировать заново — в противном случае приложение становится «миной замедленного действия», а сбой становится делом времени. Переменная, которая должна была служить локальным представлением информации из базы данных, утрачивает синхронизацию со столбцом базы данных.
Нормализация локальных переменных
Другой недостаток явного объявления типов данных проявляется при работе с переменными PL/SQL, которые содержат вычисляемые значения, не хранящиеся в базе данных. Предположим, программисты написали приложение для управления финансами компании. Во многих его программах для хранения итоговой выручки используется переменная total_revenue
, объявленная следующим образом:
total_revenue NUMBER (10,2);
Как видите, выручка компании подсчитывается до последнего пенни. В 2002 году, когда была написана спецификация приложения, максимальная выручка составляла 99 миллионов долларов, поэтому для переменной использовалось объявление NUMBER(10,2)
. Позднее, в 2005 году, дела фирмы пошли в гору, и максимум был увеличен до NUMBER(14,2)
. Но для этого пришлось разыскивать в приложении экземпляры переменной total_revenue
и изменять их объявления. Работа была долгой и ненадежной — изначально была пропущена пара объявлений, и для их обнаружения пришлось проводить полное регрессионное тестирование. Одинаковые объявления были рассеяны по всему приложению. По сути, локальные структуры данных были денормализованы с обычными последствиями для сопровождения. Жаль, что локальные переменные total_revenue
не были объявлены со ссылкой на один тип данных, что позволило бы использовать объявления с атрибутом %TYPE
.
Объявления с привязкой и ограничение NOT NULL
При объявлении переменной PL/SQL для нее можно задать ограничение NOT NULL
, и оно будет перенесено на переменные, объявляемые на ее основе с атрибутом %TYPE
. Если включить ограничение NOT NULL
в объявление переменной, к которой с помощью атрибута %TYPE
привязываются другие переменные, то для использующих ее переменных необходимо задать значение по умолчанию. Допустим, мы объявили переменную max_available_date
с ограничением NOT NULL
:
DECLARE max_available_date DATE NOT NULL := ADD_MONTHS (SYSDATE, 3); last_ship_date max_available_date%TYPE;
Такое объявление переменной last_ship_date
вызовет следующую ошибку компиляции:
PLS_00218: a variable declared NOT NULL must have an initialization assignment.
Если вы используете переменную, объявленную с ограничением NOT NULL
в объявлении с атрибутом %TYPE
, обязательно укажите в этом объявлении ее начальное значение. Но если источником значений переменной является столбец базы данных, объявленный с ограничением NOT NULL
, делать это не обязательно, поскольку в подобных случаях ограничение NOT
NULL
на переменную не переносится.