Константы, переменные и типы в PL/SQL
Переменная или константа определяется своим именем, которое является допустимым именем в СУБД Oracle. Любая константа или переменная должна иметь один из допустимых в СУБД Oracle типов. Константа идентифицируется ключевым словом CONSTANT и отличается от переменной тем, что попытка изменить ее значение в программе приведет к ошибке.
Для определения констант и переменных используется следующий синтаксис:
Имя [CONSTANT] тип данных [:= значение]; Пример. Рассмотрим пример простой программы, которая вычисляет значение синуса двух углов, кратных p.
-- переменные окружения set serveroutput on; set echo on; DECLARE Pi CONSTANT real :=3.14; x real :=1; BEGIN DBMS_OUTPUT.PUT_LINE ('y ='|| sin(Pi*x)); x:=x+1; DBMS_OUTPUT.PUT_LINE ('y ='|| sin(Pi*x)); END; /
Установка переменных окружения определяет режим вывода на терминал пользователя. Системная процедура DBMS_OUTPUT.PUT_LINE обеспечивает вывод данных на терминал. Символ / указывает на завершение текста программы и является командой к выполнению программы.
Курсоры PL/SQL
Курсор - это поименованный запрос, содержащий некоторое число строк в выборке. По сути, курсор является некоторой структурой, через которую пользователь получает доступ к строкам результирующей таблицы запроса.
Рассмотрим пример доступа к информации, которая хранится в базе данных, с использованием курсоров.
Пусть в базе данных есть таблица базы данных с именем T01, такая, как показана ниже.
| A1 number | 1 | 2 | 3 |
| A2 varchar2(5) | abc | cba | bca |
| A3 char(1) | A | B | C |
Опишем курсор для доступа к данным таблицы Т01.
CURSOR cur01 IS SELECT * FROM T01;
Работа с курсором выполняется по следующему алгоритму:
Открываем курсор:
OPEN cur01;
Выбираем данные из курсора в набор совместимых по типу переменных командой FETCH:
FETCH cur01 INTO x1,x2,x3;
Обрабатываем полученные данные.Выполняем команду FETCH для получения данных из следующей строки результирующей таблицы запроса.
И т.д.
В PL/SQL для курсоров предусмотрено несколько методов. Метод %NOTFOUND возвращает булевское истинное значение, если выборка в курсор пуста. Метод %FOUND возвращает булевское истинное значение, если выборка в курсор непуста. После открытия курсора до первой команды FETCH значения, возвращаемые этими методами, равны NULL. Метод %ROWCOUNT возвращает число строк в выборке после открытия курсора.
Предопределенный в PL/SQL метод %TYPE позволяет определить тип переменной как совпадающий с типом переменной таблицы.
PL/SQL поддерживает тип данных RECORD, который позволяет создать объект, соответствующий строке таблицы, как показано в примере ниже.
DECLARE TYPE t01_rec_type IS RECORD ( x1 t01.A1%TYPE, x2 t01.A2%TYPE, x3 t01.A3%TYPE); t01_rec t01_rec_type; … FETCH cur1 INTO t01_rec; DBMS_OUTPUT.PUT_LINE (cur1%ROWCOUNT||' '||t01_rec.x2); ѕ.
Обработка исключительных ситуаций в PL/SQL
Исключительная ситуация - это возникновение предопределенного и описанного события в системе. Например, ошибки преобразования типов переменных или переполнения при делении на нуль. Пример некоторых предопределенных ситуаций, распознаваемых в PL/SQL, приведен в таблице 12.1 ниже. Для получения полного списка таких ситуаций следует обратиться к документации по PL/SQL.
| NOT_LOGGED_ON | Попытка выполнить действие без подключения к серверу |
| INVALID_CURSOR | Ссылка на недопустимый курсор или недопустимая операция с курсором |
| NO_DATA_FOUND | Не найдены данные, соответствующие команде SELECT INTO |
| DUP_VAL_ON_INDEX | Попытка вставить дубликат значения в колонку с ограничением на уникальное значение |
| VALUE_ERROR | Арифметическая ошибка, ошибка усечения или преобразования |
Операторы управления выполнением программы PL/SQL
Операторы PL/SQL выполняются последовательно. Такая схема называется потоком команд. Изменить последовательный порядок выполнения команд можно с помощью команд управления потоком - оператором ветвления, оператором цикла и командой выхода из цикла.
Оператор IF условие TNEN группа операторов 1 ELSE группа операторов 2 позволяет проверить условие и в зависимости от результата проверки выполнить различные группы операторов. Если условие принимает значение TRUE, то выполняется группа операторов 1, в противном случае - группа операторов 2. Границы действия оператора IF определяются закрывающейся операторной скобкой END IF. Для расширения структуры ветвления предусмотрена операторная скобка ELSIF.
Синтаксис оператора ветвления следующий:
IF Условие THEN группа операторов 1 ELSIF условие 1 THEN Группа операторов 3 ELSIF … ELSE группа операторов 2 END IF Пример. Изменение потока команд
DECLARE Pi CONSTANT real :=3.14; x real :=1; BEGIN x:=Input_Data; IF (x>0.5) THEN DBMS_OUTPUT.PUT_LINE ('y ='|| sin(Pi*x)); ELSIF (x< 0.4) THEN DBMS_OUTPUT.PUT_LINE ('y ='|| cos(Pi*x)); ELSE x:=x+1; DBMS_OUTPUT.PUT_LINE ('y ='|| sin(Pi*x)); END IF; END; /
Переменная, имени которой предшествует знак &, вводится с терминала пользователя.
Организация цикла в программах на PL/SQL может быть выполнена несколькими способами. Рассмотрим примеры вычисления суммы.
Пример. Оператор LOOP.
DECLARE X number; I number; Limit number:=1000; BEGIN I:=0; X:=0; LOOP EXIT WHEN I > Limit; I:=I+1; X:=X+I*I; END LOOP; DBMS_OUTPUT.PUT_LINE (x); END; /
Оператор LOOP открывает цикл. Конструкция EXIT WHEN обеспечивает выход из цикла при выполнении условия, а закрывающая операторная скобка END LOOP завершает цикл.
Пример. Оператор WHILE
DECLARE X number; I number; Limit number:=1000; BEGIN I:=0; X:=0; WHILE I <= Limit LOOP I:=I+1; X:=X+I*I; END LOOP; DBMS_OUTPUT.PUT_LINE (x); END; /
Управление потоком команд типа WHILE обеспечивает выполнение цикла до тех пор, пока условие является истинным.
Пример. Оператор FOR
DECLARE X number; Limit number:=1000; BEGIN I:=0; X:=0; FOR I IN 0..Limit LOOP X:=X+I*I; END LOOP; DBMS_OUTPUT.PUT_LINE (x); END; /
Цикл, управляемый оператором FOR, используется, когда точно известно, сколько раз нужно выполнить операторы цикла. Переменную цикла описывать в блоке DECLARE не нужно.
Определение хранимых процедур и функций в PL/SQL
Процедура или функция PL/SQL имеет уникальное имя. Как и программы на PL/SQL, процедуры и функции имеют блок объявлений, блок исполняемого кода и, опционально, блок обработки исключительных ситуаций. Но процедура может принимать и возвращать значения параметром, а функция дополнительно возвращает значение.
Описание процедуры имеет следующий синтаксис:
PROCEDURE имя [(параметр [, параметр, ...])] IS [объявление локальных переменных, пользовательских типов данных, пользовательских исключительных ситуаций, локальных подпрограмм и функций] BEGIN Исполняемый код [EXCEPTION обработчики исключительных ситуаций] END [имя];
Параметры процедуры обеспечивают входные и выходные данные процедуры. Параметров у процедуры может и не быть, так что этот раздел не является обязательным. Для задания параметров используется следующий синтаксис:
Имя параметра [IN | OUT | IN OUT] тип данных [{:= | DEFAULT} выражение]
В определении параметров нельзя использовать ограничение NOT NULL, а в определении типа данных нельзя использовать никакие ограничения. Для каждого параметра должен быть указан его тип (parameter mode) - IN, OUT или IN OUT. Указание типа IN означает, что значение параметра определяется при обращении к процедуре и не изменяется процедурой. Попытка изменить такой параметр в теле процедуры приведет к возникновению ошибки. Указание типа OUT предполагает изменение значения параметра в процессе выполнения процедуры, т.е. это возвращаемый параметр. Указание типа IN OUT говорит о том, что при вызове процедуры такому параметру должно быть присвоено значение, которое может быть изменено в теле процедуры. Типом по умолчанию считается IN. Ниже в таблице 12.2 суммирована информация о типах параметров.
| Умолчание | Должен быть задан | Должен быть задан |
| Передает значение в процедуру или функцию | Возвращает значение из процедуры или функции | Передает значение в процедуру или функцию и возвращает измененное значение |
| Формальный параметр действует как константа | Формальный параметр действует как неинициализированная переменная | Формальный параметр действует как неинициализированная переменная |
| Формальному параметру не может быть присвоено значение | Формальный параметр не может быть использован в выражении, и ему должно быть присвоено значение | Формальному параметру можно присваивать значение |
| Действительный параметр может быть константой, инициализированной переменной, литеролом или выражением | Действительный параметр должен быть переменной | Действительный параметр должен быть переменной |
Определение процедуры начинается с ключевого слова PROCEDURE и заканчивается именем процедуры или списком параметров. Тело процедуры начинается с ключевого слова IS и заканчивается ключевым словом END. Тело процедуры состоит из трех частей, которые отвечают блокам программы PL/SQL.
Пример. Для нашей учебной базы данных можно рассмотреть вопрос о создании процедуры, которая увеличивает зарплату сотрудника по значению его номера.
PROCEDURE raise_salary (empid INTEGER, increase REAL) IS current_salary REAL; salary_missing EXCEPTION; BEGIN SELECT sal INTO current_salary FROM employee WHERE empno = emp_id; IF current_salary IS NULL THEN RAISE salary_missing; ELSE UPDATE employee SET sal = sal + increase WHERE empno = emp_id; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO emp_audit VALUES (emp_id, 'Нет сотрудника с таким номером'); WHEN salary_missing THEN INSERT INTO emp_audit VALUES (emp_id, Зарплата не назначена'); END raise_salary;
При вызове процедура принимает номер сотрудника и величину прибавки к зарплате в качестве параметров. Номер сотрудника используется далее для выборки значения текущей зарплаты из таблицы EMPLOYEE в локальную переменную current_salary. Если номер сотрудника не найден в базе данных или значение зарплаты не установлено, то возникают исключительные ситуации, стандартная и определенная пользователем соответственно. В противном случае зарплата обновляется.
Процедура вызывается как команда PL/SQL
raise_salary (emp_num, amount);
В примере использована вспомогательная таблица базы данных emp_audit для отслеживания возникновения ошибочных ситуаций при работе с базой данных.
Рассмотрим еще один пример использования процедуры для выборки зарплаты служащего по его фамилии из таблицы EMPLOYEE учебной базы данных.
PROCEDURE emp_salary (sName INTEGER, sal_p OUT REAL) IS current_salary REAL; salary_missing EXCEPTION; BEGIN SELECT sal INTO current_salary FROM employee WHERE ENAME=:sName'; IF current_salary IS NULL THEN RAISE salary_missing; ELSE sal_p = current_salary END IF; EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO emp_audit VALUES (emp_id, 'Нет сотрудника с такой фамилией'); WHEN salary_missing THEN INSERT INTO emp_audit VALUES (emp_id, Зарплата не назначена'); END raise_salary;
Определение процедуры начинается с ключевого слова PROCEDURE и заканчивается именем процедуры или списком параметров. Тело процедуры начинается с ключевого слова IS и заканчивается ключевым словом END. Тело процедуры состоит из трех частей, которые отвечают блокам программы PL/SQL.
Пример. Для нашей учебной базы данных можно рассмотреть вопрос о создании процедуры, которая увеличивает зарплату сотрудника по значению его номера.
PROCEDURE raise_salary (empid INTEGER, increase REAL) IS current_salary REAL; salary_missing EXCEPTION; BEGIN SELECT sal INTO current_salary FROM employee WHERE empno = emp_id; IF current_salary IS NULL THEN RAISE salary_missing; ELSE UPDATE employee SET sal = sal + increase WHERE empno = emp_id; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO emp_audit VALUES (emp_id, 'Нет сотрудника с таким номером'); WHEN salary_missing THEN INSERT INTO emp_audit VALUES (emp_id, Зарплата не назначена'); END raise_salary;
При вызове процедура принимает номер сотрудника и величину прибавки к зарплате в качестве параметров. Номер сотрудника используется далее для выборки значения текущей зарплаты из таблицы EMPLOYEE в локальную переменную current_salary. Если номер сотрудника не найден в базе данных или значение зарплаты не установлено, то возникают исключительные ситуации, стандартная и определенная пользователем соответственно. В противном случае зарплата обновляется.
Процедура вызывается как команда PL/SQL
raise_salary (emp_num, amount);
В примере использована вспомогательная таблица базы данных emp_audit для отслеживания возникновения ошибочных ситуаций при работе с базой данных.
Рассмотрим еще один пример использования процедуры для выборки зарплаты служащего по его фамилии из таблицы EMPLOYEE учебной базы данных.
PROCEDURE emp_salary (sName INTEGER, sal_p OUT REAL) IS current_salary REAL; salary_missing EXCEPTION; BEGIN SELECT sal INTO current_salary FROM employee WHERE ENAME=:sName'; IF current_salary IS NULL THEN RAISE salary_missing; ELSE sal_p = current_salary END IF; EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO emp_audit VALUES (emp_id, 'Нет сотрудника с такой фамилией'); WHEN salary_missing THEN INSERT INTO emp_audit VALUES (emp_id, Зарплата не назначена'); END raise_salary;
Особенности использования процедур и функций в СУБД Oracle
В этом подразделе рассмотрим некоторые особенности использования процедур и функций в PL/SQL:
Способы связывания формальных и фактических параметров в процедурах и функциях.Использование для параметров значений по умолчанию.Возникновение алиасных имен при передаче параметров.Перезагрузка имен процедур и функций.
При вызове процедур и функций в PL/SQL допускается передача параметров по имени и по позиции. Это означает, что вы указываете, как происходит связывание формальных и действительных параметров. Например, пусть имеется гипотетическая программа
DECLARE x1 INTEGER; x2 REAL; PROCEDURE proc1 (p1 INTEGER, p2 REAL) IS BEGIN ... END;
Процедуру Proc1 можно вызвать следующими эквивалентными способами:
BEGIN ... proc1 (x1, x2); -- передача параметров по позиции proc1 (p2 => x2, p1 => x1); -- передача параметров по имени proc1 (p1 => x1, p2 => x2); -- передача параметров по имени proc1 (x1, p2 => x2); -- передача параметров и по -- позиции, и по имени END;
При передаче параметра по позиции компилятор PL/SQL последовательно связывает первый фактический параметр с первым формальным параметром, второй фактический параметр - со вторым формальным параметром и так далее.
При передаче параметра по имени стрелка, называемая оператором связывания (association operator), связывает формальный параметр слева от стрелки с фактическим параметром справа от стрелки, причем порядок следования таких пар не имеет значения.
Можно комбинировать передачу параметра по позиции и по имени. Но при этом следует соблюдать требование того, чтобы передача параметра по позиции предшествовала передаче параметра по имени.
В процедуре или функции можно инициализировать параметр типа IN значением по умолчанию. Таким способом можно передавать различное число действительных параметров в процедуры или функции, принимая или переопределяя значение по умолчанию. Рассмотрим пример.
Пример. Для нашей учебной базы данных разработаем процедуру создания нового отдела таблице DEPARTAMENT.
PROCEDURE create_dept (new_dname CHAR DEFAULT 'Новый', new_loc CHAR DEFAULT 'Москва') IS BEGIN INSERT INTO departament VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;
Если при вызове процедуры фактический параметр не передается, то используется значение по умолчанию. Рассмотрим следующие вызовы этой процедуры:
create_dept; create_dept('Маркетинг'); create_dept('Маркетинг', Черноголовка);
При первом вызове не передается никаких параметров, так что используются значения параметра по умолчанию - Новый отдел в г. Москве. Во втором вызове передается один параметр - первый. Для второго используется значение по умолчанию. В третьем случае передаются оба параметра и значения по умолчанию не используются.
Для оптимизации выполнения процедур и функций компилятор PL/SQL может выбирать различные методы передачи параметров (по значению или по ссылке) для различных параметров в одном и том же вызове процедуры или функции. Когда компилятор выбирает метод передачи параметра по значению, то его значение копируется в процедуре или функции. Когда компилятор выбирает метод передачи параметра по ссылке, то адрес фактического параметра передается в процедуру или функцию. Может создаться ситуация когда переменная в процедуре или функции будет иметь два имени - так называемая проблема алиасных имен. В этом случае результат будет неопределенным. Это происходит, когда глобальная переменная передается по адресу, как в примере ниже.
Пример
DECLARE rent REAL; PROCEDURE raise_rent (increase IN OUT REAL) IS BEGIN rent := rent + increase; -- в случае передачи параметра по адресу -- одна и та же переменная будет иметь два имени. END raise_rent; BEGIN ... raise_rent(rent); -- indeterminate
Такая ситуация может также возникнуть, когда фактическая переменная дважды передается в процедуру или функцию. Если оба формальных параметра имеют тип IN, то результат не определен.
Пример. Рассмотрим процедуру реверсирования строки.
DECLARE str VARCHAR2(10); PROCEDURE reverse (in_str VARCHAR2, out_str OUT VARCHAR2) IS BEGIN ... END reverse; ... BEGIN str := 'abcd'; reverse(str, str); -- Не определен
Результат выполнения процедуры будет зависеть от выбранного компилятором способа передачи параметра в процедуру.
Компилятор PL/SQL позволяет также перезагружать (overload) имена процедур и функций, т.е. вы можете использовать одно и то же имя для нескольких различных процедур или функций. При этом число параметров, их порядок и типы данных могут быть различными.
Таким образом, основными преимуществами использования процедур и функций в базах данных являются:
обеспечение расширяемости, т.е. они расширяют декларативный язык SQL возможностями процедурной обработки;обеспечение модульности, т.е. позволяют использовать поэтапный подход обработки данных из базы данных на основе хорошо определенной бизнес-логики;создание серверного кода базы данных с целью увеличения производительности за счет сокращения сетевого трафика и накладных расходов на компиляцию кода.
Создание хранимых процедур и функций
Хранимая процедура или функция есть объект реляционной базы данных, который является поименнованным набором операторов SQL и, в случае СУБД Oracle, набором операторов PL/SQL, который может быть скомпилирован и необязательно сохранен в базе данных. Если процедура сохраняется в базе данных, то она называется хранимой процедурой или функцией. Описание хранимых процедур и функций хранится в словаре данных реляционной базы данных.
Обычно хранимые процедуры используются для выполнения набора типовых действий, характерных для достижения целей создания базы данных, а хранимые функции - для вычисления значений.
Код хранимых процедур и функций хранится в базе данных. Поэтому использование хранимых процедур и функций для выполнения типовых операций, характерных для приложения базы данных, приводит к упрощению приложений за счет передачи обработки на сервер базы данных. Такой подход уменьшает накладные расходы за счет сокращения фазы синтаксического анализа и уменьшения времени передачи запроса на конкретное действие по вычислительной сети, что приводит в целом к снижению трафика сети. Обычно при этом улучшается производительность, но это необязательно для маломощных серверов баз данных.
Хранимые процедуры и функции, как объекты базы данных, создаются командой CREATE и уничтожаются командой DROP. Команда создания хранимой процедуры имеет следующий синтаксис:
CREATE [OR REPLACE] PROCEDURE [имя схемы].имя процедуры [имя [(параметр [, параметр, ...])] {IS|AS} программа на PL/SQL;
Ключевое слово OR REPLACE указывает на безусловное замещение старого текста процедуры. Если процедура с таким именем уже определена, но ключевое слово OR REPLACE не указано, то возвращается сообщение об ошибке и замещения старого текста не происходит.
При описании переменных хранимой процедуры ключевое слово DECLARE не используется. Блок описания переменных начинается сразу после ключевого слова IS или AS.
Пример.
PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;
Исполнение созданной процедуры может быть выполнено оператором EXEC PL/SQL, как показано ниже:
EXEC ;
Команда создания хранимой процедуры имеет следующий синтаксис:
CREATE [OR REPLACE] FUNCTION [имя схемы].имя функции [имя [(параметр [, параметр, ...])] RETURN тип данных {IS|AS} программа на PL/SQL;
Как можно увидеть, синтаксис команды создания хранимой функции похож на синтаксис команды создания хранимой процедуры, за исключением того, что в описании функции используется предложение RETURN. Описание типа данных для возвращаемого значения требуется обязательно.
Пример. Для нашей учебной базы данных создадим функцию, которая считает число сотрудников, поступивших в организацию за определенный период:
CREATE OR REPLACE FUNCTION emp_cnts (data1 IN date, data2 IN date) RETURN Integer IS I_count number:=0; BEGIN SELECT COUNT(*) INTO i_count FRON employee WHERE hiredate BETWEEN date1 AND date2; RETURN i_count; END;
Вызвать созданную хранимую функцию можно следующим образом:
EXEC DBMS_OUTPUT.PUT_LINE(emp_cnts('01-may-02', ''01-jul-02'));
Для уничтожения хранимой процедуры или функции в базе данных используется команда DROP в формате DROP [имя схемы].имя процедуры;
Или
DROP [имя схемы].имя функции;
Создание пакетов PL/SQL
Процедуры, функции и глобальные переменные, объединенные общим функциональным замыслом, часто оформляются в виде специального объекта базы данных - пакета. Прием оформления родственных программ в пакет хорошо известен из программистской практики. Особенностью PL/SQL является раздельная компиляция и хранение интерфейсной и исполнительной частей пакета. Таким образом, пакет (package) есть объект базы данных, который группирует логически связанные типы, объекты, процедуры и функции PL/SQL. Пакет состоит из двух частей: спецификации пакета и тела пакета, хотя тело пакета иногда опускается. Спецификация пакета есть интерфейс для вашего приложения. В ней хранится описание процедур, функций, глобальных переменных, констант и курсоров, которые доступны из внешних приложений. Тело пакета полностью определяет курсоры, процедуры и функции, являясь, таким образом, реализацией спецификации. В теле пакета могут также быть определены переменные, курсоры, процедуры и функции. Они являются локальн ыми, т.е. не доступными из внешних приложений.
Синтаксис определения пакета в базе данных состоит также из двух частей. Оператор создания интерфейсной части имеет следующий синтаксис:
CREATE [OR REPLACE] PACKAGE [имя схемы].имя AS Определения типов и объектов Спецификации процедур и функций END [имя];
Ключевое слово OR REPLACE указывает на безусловное замещение предыдущего кода спецификации пакета. Если оно не указано, а пакет определен в базе данных, то замещения старого значения спецификации пакета не происходит и возвращается сообщение об ошибке.
Спецификация пакета начинается с объявления констант и переменных, при этом ключевое слово DECLARE не используется. Рассмотрим пример создания спецификации пакета.
Пример
CREATE OR REPLACE PACKAGE paket1 AS A1 CONSTANT number:= 1.3; PROCEDURE Pr1; FUNCTION F01 (x1 real) RETURN real; END; /
Оператор создания тела пакета имеет следующий синтаксис:
CREATE [OR REPLACE] PACKAGE BODY [имя схемы].имя AS Объявления локальных типов и переменных Тела процедур и функций [BEGIN команды инициализации END [имя];
Ключевое слово OR REPLACE указывает на безусловное замещение предыдущего кода тела пакета. Если оно не указано, а пакет определен в базе данных, то замещения старого значения тела пакета не происходит и возвращается сообщение об ошибке.
Рассмотрим пример создания тела пакета для спецификации, определенной в предыдущем примере.
Пример. Пусть функция пакета F01 умножает аргумент на заданное число, а процедура Pr1 фиксирует факт обращения к функции пакета в таблице базы данных. Предполагается, что таблица T01 уже существует.
CREATE OR REPLACE PACKAGE BODY paket1 AS Cnt number:=0; FUNCTION F01(x1 real) RETURN real IS BEGIN Pr1; RETURN x1*A1; END; PROCEDURE Pr1 IS BEGIN Cnt:=Cnt+1; INSERT INTO T01 VALUES(Cnt,SYSDATE); COMMIT; END; END; /
Отметим, что инициализация локальных переменных, таких как переменная Cnt примера, происходит при запуске сервера СУБД Oracle.
Константа или переменная, определенная в спецификации пакета, может быть доступна из пользовательской программы, при условии, если такой доступ разрешен администратором базы данных. Чтобы обратиться к глобальной переменной или константе пакета, нужно использовать квалифицируемое имя, указав в качестве префикса к имени переменной или константы имя пакета. Аналогично происходит обращение к процедурам или функциям пакета, определенных в спецификации пакета. Например,
package_name.type_name package_name.object_name package_name.subprogram_name
Чтобы уничтожить пакет для освобождения ресурсов сервера, используется команда SQL DROP в следующем формате:
DROP PACKAGE [BODY] [имя схемы].имя пакета;
Необязательное ключевое слово BODY указывает, что уничтожается только тело пакета, в противном случае удаляется и спецификация, и тело пакета.
Что дает проектировщику базы данных использование пакетов? Пакеты обладают рядом преимуществ, к которым принято относить следующие:
Модульность. Пакеты позволяют проектировщику базы данных инкапсулировать логически связанные типы данных, объекты, процедуры и функции в поименованный модуль PL/SQL.
Использование пакетов позволяет проектировщику базы данных сделать приложения проще, яснее и хорошо определенными. Поскольку каждый пакет в отдельности проще для понимания, связи между пакетами через хорошо определенные интерфейсы яснее и проще.Упрощение проектирования приложений базы данных. Когда проектировщик базы данных проектирует приложение, ему в случае использования пакетов необходимо сначала спроектировать только интерфейсную часть пакетов, т.е. определить спецификации пакета, а только потом разрабатывать тело пакета. Поскольку компиляция спецификации и тела пакета происходит раздельно, то нет необходимости полностью определять тело пакета до тех пор, пока проектировщик базы данных не завершит проектирования приложения базы данных.Скрытие информации. При разработке пакета проектировщик базы данных может определить, какие типы, объекты, процедуры и функции будут доступны внешним приложениям, а какие будут локальными в пакете, т.е. скрытыми от внешних программ. Этот прием упрощает сопровождение и расширение приложений базы данных в дальнейшем. В частности, скрытие деталей реализации пакета от пользователей защищает целостность пакета.Дополнительная функциональность. Глобальные переменные и курсоры пакета сохраняются в течение сессии работы пользователя с базой данных. Поэтому они могут разделяться всеми процедурами и функциями, которые могут выполняться в программной среде сессии. Они также позволяют сопровождать некоторые данные транзакции без сохранения их в базе данных.Улучшение производительности базы данных. Когда элементы пакета вызываются в первый раз, пакет полностью загружается в оперативную память сервера. Следовательно, последующие вызовы процедур и функций пакета будут происходить без обращения к диску, что сократит число операций ввода/вывода.
Рассмотрим для нашей учебной базы данных пример принятия решений проектировщиком базы данных о создании пакета.
Предположим, что в организации высокая степень текучести кадров. Пользователь базы данных, который работает с персоналом, проводит одну сессию работы с базой данных в начале недели.
В рамках этой сессии он фиксирует в базе данных новых сотрудников, которые приступают к работе с понедельника (бизнес-правило организации - принимать на работу только с понедельника), и удаляет информацию о сотрудниках из базы данных (бизнес-правило организации - не сохранять информацию об уволенном сотруднике в базе данных), которые ушли из организации. Тогда проектировщик базы данных может рассмотреть создание пакета, содержащего две процедуры: одну для занесения информации о сотруднике в базу данных, а другую - для удаления информации о сотруднике из базы данных. Код пакета может быть таким, как показано ниже.
CREATE PACKAGE emp_actions AS TYPE EmpRecTyp IS RECORD (emp_id INTEGER, salary REAL); CURSOR desc_salary RETURN EmpRecTyp; PROCEDURE hire_employee ( ename VARCHAR2, job VARCHAR2, mgr NUMBER, sal NUMBER, comm NUMBER, deptno NUMBER); PROCEDURE fire_employee (emp_id NUMBER); END emp_actions;
CREATE PACKAGE BODY emp_actions AS CURSOR desc_salary RETURN EmpRecTyp IS SELECT empno, sal FROM employee ORDER BY sal DESC; PROCEDURE hire_employee ( ename VARCHAR2, job VARCHAR2, mgr NUMBER, sal NUMBER, comm NUMBER, deptno NUMBER) IS BEGIN INSERT INTO employee VALUES (empno, ename, job, mgr, SYSDATE, sal, comm, deptno); END hire_employee; PROCEDURE fire_employee (emp_id NUMBER) IS BEGIN DELETE FROM employee WHERE empno = emp_id; END fire_employee; END emp_actions;
Создание триггеров PL/SQL
Триггер базы данных (database trigger) является объектом реляционной базы данных, который активизирует выполнение хранимой (или встроенной) PL/SQL-процедуры при изменении пользователем данных в таблице. Событие, управляющее запуском триггера, описывается в виде логических условий. Например, попытка модифицировать данные в таблице активизирует триггер, соответствующий данной команде манипулирования данными. Число триггеров на таблицу базы данных не ограничено.
Обычно триггеры используют для реализации ограничений ссылочной целостности, для предотвращения несогласованных изменений в базе данных (поддержка целостности базы данных), для выполнения скрытых операций при модификации, а также для снижения сетевого трафика за счет передачи обработки на сервер. Операции завершения транзакции выполняются после обработки триггеров.
При выполнении команды UPDATE с помощью триггера можно проверить, что модифицируемые данные удовлетворяют ограничениям целостности базы данных до выполнения операции (при этом возможен доступ к новым данным!). После выполнения операции с помощью триггера можно выполнить скрытую обработку данных с учетом поступивших изменений (старые данные также могут быть доступны).
При выполнении команды INSERT также можно проверить данные до вставки в таблицу на допустимость ограничениям целостности, а после - выполнить операции над только что вставленными данными.
При выполнении команды DELETE можно проверить данные до их удаления или восстановить данные после удаления.
Для создания триггера предусмотрена специальная команда SQL CREATE TPIGGER. Эта команда создает триггер на таблице, которой владеет пользователь. Невозможно создать триггер для виртуальной таблицы.
Синтаксис команды следующий:
CREATE [OR REPLACE] TPIGGER [имя схемы.]имя триггера {BEFORE|AFTER} {INSERT|DELETE|UPDATE [OF имя колонки [, имя колонки ѕ]]} [OR {INSERT|DELETE|UPDATE [OF имя колонки [, имя колонки ѕ]]}] ON [имя схемы.]{имя таблицы|имя представления} {FOR EACH ROW][WHEN условие] спецификация пакета на PL/SQL
Ключевое слово OR REPLACE указывает на безусловное замещение старого теста триггера. Если оно не указывается, а триггер определен в базе данных, то замещения старого триггера не происходит, и возвращается сообщение об ошибке.
Определение триггера состоит из нескольких частей:
задание имени триггера;указание команды SQL, к которой относится триггер;указание таблицы или представления, для которой определяется триггер;задание ограничений триггера;задание действия в теле триггера.
Если имя схемы опущено, то триггер создается в схеме текущего пользователя.
Первая часть - это указание команды, которая запускает триггер. При создании триггера необходимо указывать, к какой команде манипулирования данными он относится - INSERT, DELETE или UPDATE. Для последней модно указывать конкретные колонки, указав фразу OF имя_колонки [, имя_колонки ...] в предложении UPDATE.
Ключевое слово ON задает имя таблицы или представления, для которого создается триггер.
Ограничения триггера указывают дополнительные условия, которые должны удовлетворяться для строки, чтобы триггер запустился.
Триггер имеет время действия - до или после выполнения команды манипулирования данными, - которое задается ключевыми словами BEFORE или AFTER, соответственно. Обычно нельзя определить два триггера на одну и ту же операцию с одинаковым временем действия.
Необязательное ключевое слово ON EACH ROW определяет триггер как строчный, т.е. запускаемый для каждой строки результирующего множества команды SQL. Если оно опущено, то триггер запускается только один раз в начале обработки команды. Таким образом, условие "для каждой строки" активизируется, только когда есть строки (например, предложение WHERE дает истинное значение условий поиска), в то время как для условия "для каждой команды" триггер сработает и в этом случае.
Дополнительные условия, сужающие область действия триггера, могут быть заданы в предложении WHEN. Условия, задаваемые в этом предложении, являются стандартными для SQL условиями, должны содержать корреляционные имена и не могут содержать запрос.
Это предложение может быть указано только для строчного триггера.
Действие, которое выполняет триггер, задается в теле триггера блоком кода PL/SQL, который не может содержать команд управления транзакциями, таких как COMMIT, ROLLBACK и SAVEPOINT.
Для того чтобы задать корреляционные имена, т.е. чтобы видеть старые и новые значения колонок при обновлении, нужно воспользоваться предложением REFERENCING OLD AS имя_таблицы_старых_значений NEW AS имя_таблицы_новых_значений. Определяемые имена являются псевдонимами для обновляемой таблицы и должны быть различны. На эти имена можно ссылаться в теле триггера. По умолчанию корреляционные имена есть OLD и NEW для старого и нового значения строки.
С каждым триггером можно связать его тип. Тип триггера определяется следующими условиями:
когда запускается триггер (т.е. какое у него время действия);сколько раз запускается триггер.
Таким образом, тип триггера определяется всевозможными комбинациями ключевых слов BEFORE, AFTER и FOR EACH ROW, что дает четыре основных типа триггера, как показано в таблице 12.3 ниже.
| BEFORE | BEFORE: СУБД запускает триггер до выполнения команд | BEFORE: СУБД запускает триггер до модификации каждой строки, обрабатываемой командой |
| AFTER | AFTER: СУБД запускает триггер после выполнения команды | AFTER: СУБД запускает триггер после модификации каждой строки, обрабатываемой командой |
Пример
CREATE TRIGGER emp_permit_changes BEFORE DELETE OR INSERT OR UPDATE ON employee DECLARE dummy INTEGER; BEGIN /* Если сегодня суббота или воскресенье, то ошибка. */ IF (TO_CHAR(SYSDATE, 'DY') = 'SAT' OR TO_CHAR(SYSDATE, 'DY') = 'SUN') THEN raise_application_error( -20501, 'Можно изменять таблицу employee только в рабочие дни'); END IF; /* Если праздник, то тоже ошибка */ SELECT COUNT(*) INTO dummy FROM company_holidays WHERE day = TRUNC(SYSDATE); IF dummy > 0 THEN raise_application_error( -20501, 'Нельзя изменять таблицу employee по праздникам'); END IF; /* Если текущее время меньше 8:00 часов утра или 5:00 часов вечера, то ошибка */ IF (TO_CHAR(SYSDATE, 'HH24') < 8 OR TO_CHAR(SYSDATE, 'HH24') >= 17) THEN raise_application_error( -20502, 'Нельзя изменять таблицу employee в нерабочие часы'); END IF; END;
Структура программы на PL/SQL
Модельным прототипом для создания языка PL/SQL послужил язык программирования ADA, поэтому он обладает набором средств, характерных для любого современного языка программирования. Всякая программа на PL/SQL состоит из трех блоков: блока описаний, блока исполняемого кода и блока обработки исключительных ситуаций. Блок исполняемого кода может быть структурирован с помощью операторных скобок BEGIN … END.
Синтаксически программа на PL/SQL оформляется следующим образом:
DECLALE Описание переменных и констант BEGIN Операторы EXCEPTION Операторы END;
Перед блоком DECLALE могут располагаться команды установки переменных окружения. В блоке DECLALE описываются константы, переменные и определенные пользователем типы данных. Первый оператор BEGIN отмечает начало тела основной программы. В тело программы могут быть включены другие блоки, ограниченные операторными скобками. Блок EXCEPTION определяет фрагменты программного кода для обработки исключительных ситуаций в программе. Последний оператор END указывает конец тела программы. В любые части программы могут быть включены комментарии, т.е. текст, который начинается с символов -- и продолжается до конца текущей строки. Строка, начинающаяся с ключевого слова REM, также рассматривается как комментарий.
В этой лекции мы будем
В этой лекции мы будем заниматься формированием навыков решения следующей профессиональной задачи проектирования баз данных - задачи разработки серверного кода.
В многопользовательских системах пользователи совместно используют вычислительные ресурсы, в частности ресурсы дисковой памяти и оперативной памяти процессора. Вычислительные ресурсы могут быть сконцентрированы в одном месте (централизованные вычисления) или быть рассредоточенными в различных узлах, объединенных в компьютерную сеть (распределенные вычисления). СУБД в любом случае призвана координировать и осуществлять доступ пользователей к базам данных и их объектам.
Большинство современных СУБД поддерживают концепцию клиент- серверной технологии для распределенных вычислений. Это означает, что существуют концентраторы вычислений (называемые серверами), на которых выполняется наибольший объем вычислений с данными (серверы баз данных), и машины пользователей (клиенты), на которых выполняются приложения пользователей.
Приложения формируют запросы в форме команд SQL к базам данных, отправляют их серверам баз данных, получают запрашиваемые данные и обрабатывают их. Такие действия приложения порождают сетевой трафик, который в некоторых случаях может оказаться значительным и снизить производительность обработки запросов.
В клиент-серверной вычислительной среде приложение может взаимодействовать с сервером баз данных по другой схеме, когда приложение отправляет запрос, этот запрос обрабатывается на сервере, а приложению возвращается готовый результат. Перенос определенной части обработки бизнес-логики из клиентской части приложения на серверную может значительно сократить межсетевой обмен и тем самым повысить производительность информационной системы с базой данных.
Работа приложения по последней схеме основывается на использовании так называемого серверного кода (server-side code) - любого кода, выполняемого компьютером, на котором установлена СУБД. Ядро СУБД выполняет этот код в базе данных и возвращает приложению только результат.
Например, это может быть несколько колонок строки или вычисленное значение.
Использование серверного кода может значительно сократить объем сетевого трафика, и тем самым увеличить производительность базы данных в целом. Однако СУБД должна иметь встроенные средства для распознавания и обработки такого кода. Многие фирмы-производители промышленных СУБД, в том числе и Oracle, предлагают процедурные расширения SQL, с помощью которых можно выполнять построчную обработку данных, использовать циклы, сложные вычисления и операции управления данными.
PL/SQL является таким расширением SQL в СУБД Oracle. Он позволяет создавать серверный код в виде объектов реляционной базы данных, таких, как хранимые процедуры, функции, пакеты и триггеры. Проектировщик реляционной базы данных, который использует для создания базы данных СУБД Oracle, имеет возможность рассмотреть создание таких объектов с целью сокращения сетевого трафика или принять решение о переносе определенного объема обработки на сервер, особенно в тех случаях, когда эта обработка выполняется очень интенсивно. Например, несколько строк разных таблиц проверяются перед вставкой новой строки.
Таким образом, разработка серверного кода сводится к решению следующих подзадач:
принятие решения и создание хранимых процедур;принятие решения и создание функций;принятие решения и создание пакетов;принятие решения и создание триггеров.
Формулы для оценки размера БД
С целью упрощения вычислений размера базы данных, в настоящем разделе мы будем проводить вычисления на примере СУБД SQLBASE. Размер базы данных может быть оценен по формуле
Перед тем как вычислить размер таблицы, необходимо вычислить размеры всех ее колонок.
Вычисление размера колонки. Вычисление размера колонки зависит от типа домена колонки. Размер колонки или столбца таблицы - это число символов, которое отводится СУБД для хранения колонки заданного типа.
Как правило, в определении таблицы задаются максимальные размеры полей указанного типа для данной предметной области. Например, предполагается, что колонка адреса компании не будет занимать более 50 символов. С другой стороны, на практике, на реальных данных средний размер колонки адреса компании может составлять 30 символов. Расчет размера базы данных целесообразно проводить исходя из среднего размера колонок таблиц.
Типичные размеры колонок заданного типа приведены в таблице 13.2 ниже.
| Character | Число символов в строке |
| Number | [(NumberOfDigits + 2)/ + 1 байт |
| Date | 5 байт |
| DateTime | 12 байт |
| Long varchar | 12 байт плюс число сраниц для хранения данных |
Размер строки таблицы определяется как сумма размеров всех ее колонок по формуле
Data _ Length =
Вычисление размера таблицы. Основываясь на значении Data_Length можно оценить размер обычной таблицы или хэш-таблицы. Формулы для выполнения такой оценки приведены в таблицах 13.3 и 13.4. Различие в методике расчета размера хэш-таблицы заключается в необходимости учитывать параметр загрузки хэш-таблицы (packing_density), который устанавливается при определении такой таблицы.
| Row_Lenght | Длина строки на физической странице включает в себя длину заголовка и размер строки таблицы, которая вычисляется по формуле Row_Lenght = 18 + (2 * число_колонок) + Data_Lenght |
| Row_Lenght_with_Stack | Длина строки с размером стека Row_Lenght_with_Stack = Row_Lenght * 100 (100 - PCTFREE) |
| Usable_Row_Page_Size | Используемая СУБД длина строки на странице. В SQLBASE длина заголовка страницы равна 86 байт Usable_Row_Page_Size = 1024 - 86 = 936 байт |
| Rows_per_Page | Число строки на страницу: Rows_per_Page = [Usable_Row_Page_Size / Row_Lenght_with_Stack] |
| Nbr_Row_Pages | Число строк на странице: Nbr_Row_Pages = [NbrOfRows / Rows_per_Page],где NbrOfRows - предполагаемое число строк в таблице |
| Nbr_Long_Pages | Число страниц, занимаемых длинными строками: Nbr_Long_Pfge = NbrOfRows * Nbr_Long_Pages_per_Long_Col, Nbr_Long_Pages_per_Long_Col - число длинных строк на страницу |
| Total_Data_Page | Число страниц данных: Total_Data_Page = Nbr_Row_pages + Nbr_Long_Pages |
table class="xml_table" cellpadding="2" cellspacing="1">
Таблица 13.4. Оценки размера хэш-таблицы
| Key_Lenght | Длина ключа равна сумме средних длин колонок, которые составляют данный ключ |
| Index_Entry_Lenght | Длина размера строки индекса: Index_Entry_Lenght = 9 + число_колонок_ключа_индекса + Key_Lenght |
| Usable_Index_Page_Size | Используемый СУБД размер страницы индекса: Usable_Index_Page_Size = (1024 - 74)* (100 - PCTFREE)/100 |
| Index_Entry_per_Page | Число входов индекса на страницу: Index_Entry_per_Page = [Usable_Index_Page_Size / Index_Entry_Lenght |
| Nbr_Index_Pages | Число страниц, занимаемых индексом Nbr_Index_Pages = [NbrOfRows / Index_Entry_per_Page], где NbrOfRows - предполагаемое число строк в таблице |
Вычисление размера заголовка представления. Для каждого представления существует фиксированная часть заголовка и переменная часть заголовка, которая зависит от его сложности. Формулы для расчета размера заголовка представления приведены в таблице 13.6.
| Fixed_Overhead | = 12 * 1024 |
| Variable_Overhead | = 150 * число_таблиц + 170 * число_колонок |
| Variable_Overhead_all_Views |  |
| Total_View_overhead_in_Page | = [(Fixed_Overhead + Variable_Overhead + Variable_Overhead_all_Views)/1024] |
Total_Fixed_Overhead_Pages = 12*число_таблиц + 2*число_хэш_индексов + 602112/1024
Назначение привилегий
С каждой учетной записью связана так называемая область привилегий, которая остается пустой при создании ее с помощью команды CREATE USER.
Привилегия - это поддерживаемый системой признак, который определяет, может ли конкретный пользователь выполнить конкретную операцию, т.е. разрешение на выполнение в системе определенного действия. Таким образом, имеется несколько типов привилегий, соответствующих нескольким типам операций, и, следовательно, предполагается, что пользователь в системе будет обладать определенным набором привилегий.
Для понимания действия этого механизма определения привилегий важно понятие владельца таблицы. Будучи пользователем, определенным в базе данных, вы являетесь владельцем любой созданной вами таблицы. Вы можете разрешить доступ к вашей таблице другим пользователям, определяя им привилегии прав доступа к своим таблицам.
Множество базовых привилегий определено стандартом ANSI SLQ, но, как правило, в конкретных СУБД поддерживаются дополнительные типы привилегий. Например, в СУБД Oracle их около сотни.
Все привилегии могут быть разделены на два класса: системные привилегии и привилегии прав доступа к объектам базы данных.
Системная привилегия - это привилегия, которая дает пользователю право на выполнение какой-либо операции в масштабе базы данных. Например, привилегия SELECT ANY TABLE дает пользователю право выполнять выборку из любой таблицы базы данных.
Привилегия прав доступа к объекту - это разрешение пользователю на выполнение определенной операции над определенным объектом базы данных. Например, пользователю может быть предоставлена привилегия SELECT на выполнение выборки из конкретной таблицы.
Для предоставления привилегий или, как еще говорят, авторизации доступа в SQL предусмотрена команда GRANT - ее может выполнить обычно только системный администратор, который предопределен в системе как SYSADM (наивысший уровень доступа, при котором возможно выполнение всех операций над БД). В СУБД Oracle такой пользователь должен обладать привилегией GRANT ANY RPIVILEGE.
Команда GRANT для определения системных привилегий имеет следующий синтаксис:
GRANT системная_привилегия [ { , системная_привиления } ѕ] TO { пользователь | PUBLIC } [{, пользователь} ѕ ] [WITH ADMIN OPTION]
В СУБД Oracle привилегии прав доступа к объекту могут быть предоставлены двум объектам системы: пользователям и ролям. Роль представляет собой поименованный набор привилегий. Предложения команды GRANT в данном случае управляют разграничением доступа к объектам базы данных: таблицам, представлениям, процедурам и т.д. Синтаксис команды имеет вид
GRANT {привилегия_доступа_к_объекту | ALL PRIVILEGES} [ имя_столбца [{ , имя_столбца } ѕ] ] [{ , привилегия_доступа_к_объекту } ѕ ] ON [имя_схемы.]имя_объекта TO { пользователь | PUBLIC } [WITH GRANT OPTION]
Список привилегий доступа показан в таблице 13.1 ниже.
| SELECT | Выполнение вставки данных из соответствующего объекта |
| INSERT | Выполнение вставки данных в соответствующий объект или его элемент |
| UPDATE | Выполнение модификации данных в соответствующем объекте или его элементе |
| REFERENCES | Определение столбцов как родительских ключей по отношению к внешним ключам в таблицах, ссылки, по которой производится контроль целостности объекта или егоэлемента |
| DELETE | Выполнение удаления данных из соответствующего объекта |
| EXECUTE | Выполнение действия с соответствующим объектом, например, вызов процедуры |
| INDEX | Выполнение индексирования для соответствующего объекта |
GRANT SELECT ON EMPLOYEE TO PETROV;
Вы можете также дать привилегии на выполнение обновления, добавления, удаления данных в таблицах и виртуальных таблицах (UPDATE, INSERT, DELETE), а также для изменения структуры таблицы (ALTER) и право на использование индекса (INDEX).
При определении привилегии на обновление вы можете указать список колонок, обновлять которые вы разрешаете другим пользователям таблицы.
Можно разрешить доступ на выборку таблицы и группе пользователей. Допустим, что вы желаете разрешить доступ к таблице EMPLOYEE всем пользователям, но исключить при этом доступ к колонкам SAL, COMM и FINE. Хорошим решением в данном случае будет создать виртуальную таблицу и разрешить доступ к ней всем пользователям:
CREATE VIEW EMP AS SELECT EMPNO,ENAME,JOB,AGE,HIREDATE,DEPNO FROM EMPLOYEE;
Теперь вы можете разрешить доступ всем пользователям к созданной виртуальной таблице, используя ключевое слово PUBLIC.
GRANT SELECT ON EMP TO PUBLIC;
Вы можете организовать и более сложные механизмы защиты данных. Например, вы можете, используя виртуальные таблицы, ограничить доступ к данным таблицы EMPLOYEE руководителям подразделений таким образом, что они не будут иметь доступа к информации по другим подразделениям. При этом удобно воспользоваться ключевым словом USER для идентификации пользователя:
CREATE VIEW EMPDEPT AS SELECT * FROM EMPLOYEE WHERE DEPNO IN ( SELECT DEPNO FROM EMPLOYEE WHERE ENAME = USER );
Теперь вы можете определить привилегии доступа для всех руководителей организации:
GRANT SELECT,UPDATE ON EMPDEPT TO IVLEV
Для отмены привилегий доступа в SQL предназначена команда REVOKE. Эта команда, так же как и команда GRANT, включает предложения ON и TO. Предложение REVOKE определяет отменяемую привилегию. Чтобы отменить привилегии на добавление строк в таблицу пользователю PETROV, нужно выполнить команду
REVOKE INSERT ON DEPARTAMENT TO PETROV;
Оценка размера базы данных
Базы данных существуют и функционируют в конкретной программно-аппаратной среде. Компьютер представляет собой аппаратную среду, состоящую из множества различных компонент - центрального процессора, оперативной и внешней памяти и т.д. Для обеспечения взаимодействия аппаратных компонент служит операционная система. Операционные системы варьируются в зависимости от используемой аппаратуры. Операционная система представляет собой промежуточный уровень между компьютерной аппаратурой и программами. СУБД - это программа для поддержки базы данных. База данных хранится в файлах операционной системы, которые располагаются во внешней памяти компьютера, на жестких дисках. Файлы операционной системы обычно располагаются внутри логически поименованных областей жесткого диска, которые называются каталогами. Приложения базы данных взаимодействуют с данными в ней через цепочку программных взаимодействий:
Размер базы данных во многих практических ситуациях является критическим параметром ее эксплуатации. Проектировщик БД должен уметь оценивать как размер БД, так и возможный ее рост во времени.
Отправной точкой решения задачи оценки размера БД является вычисление размера каждой таблицы БД. Чтобы это сделать, необходимо вычислить размер каждой колонки в таблице и на основании этих данных оценить размер самой таблицы. Далее следует оценить размер всех индексов, представлений и других объектов.
Поскольку ОС распределяет память под файлы в страницах, то при вычислении размера базы данных необходимо оценить число страниц, которое она занимает. Для ОС типа Windows размер страницы составляет 1024 байта.
Подготовка скрипта создания физической базы данных
Рассмотрим решение этой задачи на учебном примере, который мы использовали в предыдущих лекциях.
Создание реляционных таблиц. Сначала проектировщик базы данных собирает команды создания таблиц базы данных, которые в нашем случае могут иметь вид
CREATE TABLE DEPARTAMENT ( DEPNO integer NOT NULL, DNAME char(20), LOC char(20), MANAGER char(20), PHONE char(15), PRIMARY KEY (DEPNO) определение первичного ключа );
CREATE TABLE EMPLOYEE ( EMPNO integer NOT NULL, ENAME char(25), LNAME char(10), DEPNO int, SSECNO char(10), JOB char(25), AGE date, HIREDATE date NOT NULL WITH DEFAULT, SAL dec(9,2), COMM dec(9,2), FINE dec(9,2), PRIMARY KEY (EMPNO) );
CREATE TABLE PROJECT ( PROJNO char(8) NOT NULL, PNAME char(25), BUDGET dec(9,2), PRIMARY KEY (PROJNO) ); CREATE TABLE EMP_PRJ ( EMPNO integer NOT NULL, PROJNO char(8) NOT NULL, WORKS number, PRIMARY KEY (EMPNO, PROJNO), FOREING KEY (EMPNO) REFERENCES EMPLOYEE, FOREING KEY (PROJNO) REFERENCES PROJECT ); Создание индексов. На втором шаге проектировщик базы данных собирает команды создания индексов, которые он решил построить. В нашем случае проектировщик мог принять решения не строить дополнительных индексов, а СУБД Oracle индекс первичного ключа строится автоматически. Поэтому этот раздел скрипта у нас пуст.Создание представлений. Проектировщик базы данных принял решение создать внешнюю схему для пользователей базы данных и разработал следующий фрагмент скрипта:
CREATE VIEW DEPARTAMENT_V AS SELECT DEPNO, DNAME, LOC, MANAGER, PHONE FROM DEPARTAMENT;
CREATE VIEW EMPLOYEE_V AS SELECT EMPNO, ENAME, LNAME, DEPNO, SSECNO, JOB, AGE, HIREDATE, SAL, COMM, FINE FROM EMPLOYEE; CREATE VIEW PROJECT_V AS SELECT PROJNO, PNAME, BUDGET FROM PROJECT;
Кроме этого, проектировщик решил добавить в физическую модель базы данных еще четыре представления:
CREATE VIEW PERSPROJ AS SELECT ENAME, JOB, PNAME FROM EMPLOYEE, PROJECT, EMPL_PROJ WHERE EMPLOYEE.EMPNO= EMPL_PROJ.EMPNO AND EMPL_PROJ.PROJNO=PROJECT.PROJNO;
CREATE VIEW EMPLIST AS SELECT DEPNO, EMPNO, ENAME, JOB FROM EMPLOYEE GROOP BY DEPNO, EMPNO, ENAME, JOB;
CREATE VIEW PERSPROJ AS SELECT ENAME, JOB, PNAME FROM EMPLOYEE, PROJECT WHERE EMPLOYEE.PROJNO=PROJECT.PROJNO;
CREATE VIEW CURPROJ AS SELECT * FROM PROJECT WHERE START_DATE < SYSDATE WITH CHECK OPTION; Создание синонимов. Проектировщик базы данных решил создать один синоним и добавил в скрип команду его создания:
CREATE PUBLIC SYNONYM EMP FOR EPMPLOYEE; Создание пользователей и предоставление привилегий. Проектировщик базы данных решил создать трех пользователей базы данных и не определять никаких ролей, поэтому добавил в скрипт следующие команды:
CREATE USER Ivan IDENTIFIED BY EXTERNALLY; CREATE USER Peter IDENTIFIED BY EXTERNALLY; CREATE USER Sidorov IDENTIFIED BY 'alsy_';
Проектировщик базы данных определил пользователям следующие привилегии:
GRANT SELECT ON EMPLOYEE, DEPARTAMENT, PROJECT, EMP_PRJ TO Ivan, Peter, Sidorov;
GRANT INSERT,UPDATE ON EMPLOYEE, DEPARTAMENT, PROJECT, EMP_PRJ TO Ivan;
Никаких других проектных решений проектировщик базы данных принимать не стал.
Далее он составил подробный отчет по каждому объекту базы данных, фрагмент отчета для реляционной таблицы DEPARTAMENT (таблица 13.15) и предал полученные результаты руководителю ИТ-проекта.
| Наименование | DNAME | char(20) |
| Размещение | LOC | char(20) |
| Руководитель | MANAGER | char(25) |
| Телефон | PHONE | char(15) |
Литература: [7], [8], [23], [38], [39], [45].
Пример расчета размера базы данных
Рассмотрим базу данных, которая состоит из таблицы CUSTOMER (ПОКУПАТЕЛЬ), таблицы CONTACT (КОНТАКТ), индекса NDX_CONTACT и представления BAD_CUSTOMER. Команды создания базы данных приведены ниже:
CREATE TABLE CUSTOMER (CUSTOMER_ID CHAR(5) NOT NULL, CUSTOMER_NAME VARCHAR(25), CUSTOMER_ADDR VARCHAR(50), CUSTOMER_RATING CHAR(10), PRIMARY KEY(CUSTOMER_ID)) PCTFREE 15;
CREATE TABLE CONTACT (CUSTOMER_ID CHAR(5) NOT NULL, CONTACT_NAME VARCHAR(25) NOT NULL, CONTACT_PHONE DECIMAL(10,0), CONTACT_TEXT LONG VARCHAR, PRIMARY KEY (CUSTOMER_ID, CONTACT_NAME) FOREIGN KEY CUSTKEY (CUSTOMER_ID) REFERENCES CUSTOMER ON DELETE RESTRICT) PCTFREE 15;
CREATE UNIQUE CLUSTERED HASHED INDEX NDX_CUSTOMER ON CUSTOMER (CUSTOMER_ID) SIZE 47628;
CREATE UNIQUE INDEX NDX_CONTACT ON CONTACT ON CONTACT (CUSTOMER_ID,CONTACT_NAME) PCTFREE 10;
CREATE VIEW BAD_CUSTOMER AS SELECT CUSTOMER_NAME, CUSTOMER_ADDR FROM CUSTOMER WHERE CUSTOMER_RATING='POOR';
Оценим размер базы данных в предположении, что она создана под управлением СУБД SQLBASE. Ожидаемое число строк в таблице CUSTOMER - порядка 50000, а в таблице CONTACT - 175000. После загрузки базы данных была выполнена оценка средней длины полей, которая приведена в таблице 13.7.
| CUSTOMER | CUSTOMER_ID | 5 | 5 |
| CUSTOMER_NAME | 25 | 10 | |
| CUSTOMER_ADDR | 50 | 30 | |
| CUSTOMER_RATING | 10 | 5 | |
| CONTACT | CUSTOMER_ID | 5 | 5 |
| CONTACT_NAME | 25 | 15 | |
| CONTACT_PHONE | 10 | 10 | |
| CONTACT_TEXT | 500 | 10 |
Оценка размера базы данных:
Таблица CUSTOMER:
Data_Length = 5 + 10 + 30 + 5 = 50 Row_Length = 18 + 6 + (2*4) + 50 = 82 Row_Length_with_Stack = (82*100)/85 =97 Rows_per_Page = (1024 - 86)/97 = 9 Nbr_Row_Pages =50000/9 = 5556 Nbr_Hashed_Table_Pages = 5556/0,7 = 7938 Total_Data_Page = 7938
Так как в этой таблице нет колонок типа LONG VARCHAR, то общее число страниц данных этой таблицы будет равно числу страниц хэш-таблицы.
Таблица CONTACT:
Data_Length = 5 + 15 + (((10 + 2)/2 + 1) + 12 = 39 Row_Length = 18 + (2*4) + 39 = 65 Row_Length_with_Stack = (65*100)/75 = 87 Rows_per_Page = (1024 - 86)/87 = 10 Nbr_Row_Pages =175000/10 = 17500 Nbr_Long_Pages = 17500 * 1 = 175000 Total_Data_Page = 175000 + 17500 = 192500 Индекс NDX_CONTACT:
Key_Length = 5 +15 = 20 Index_Entry_Length = 9 + 2 + 20 = 31 Usable_Index_Page_Size = (1024 - 74)*(100 - 10)/100 = 855 Index_Entry_per_Page = 855/31 = 27 Nbr_Index_Pages = 175000/27 = 6482
Представление BAD_CUSTOMER
Fixed_Overhead = 12*1024 = 12288 байт Variable_Overhead = 1*150 + 2*170 = 490 байт Variable_Overhead_all_Views = 0 Total_View_overhead_in_Pages = (12288 + 490 + 0)/1024 = 13 страниц Оценка размера фиксированной системной области:
Total_Fixed_Overhead_Pages = 2*12 + 1*2 + 602112/1024 = 614 страниц Оценка размера базы данных:
Размер базы данных = 7938 + 192500 + 6482 + 13 + 614 = 207560 страниц или 203 Мб.
Проверка физической модели реляционной базы данных
Перед созданием физической базы данных проектировщик базы данных должен проверить созданную им модель физической структуры базы данных на полноту и корректность.
Полнота в данном случае означает, что следует убедиться в том, что:
все сущности логической модели базы данных нашли свое отражение в объектах физической модели;в соответствии анализом транзакций системы и требованиями производительности приняты соответствующие проектные решения, которые получили свое отражение в объектах физической структуры;определен круг пользователей системы, их роли, и созданы соответствующие объекты базы данных для поддержки;оценен потенциальный размер базы данных и ее возможный рост, а полученные оценки нашли свое отражение в объектах базы данных, связанных с управлением файловым пространством базы данных.
Корректность означает, что в процессе денормализации логической модели базы данных при ее преобразовании в физическую с учетом требований по производительности не была потеряна реляционность отношений базы данных на физическом уровне модели.
Заметим, что наших лекциях не нашли своего отражения ряд вопросов проектирования базы данных, а именно - связанных с выбором программно-аппаратной архитектуры (например, трехзвенная архитектура "клиент-сервер"), настройкой производительности, работой с физическим пространством базы данных и т.д. Как правило, эти вопросы, по большей части связанные с конкретной реализацией базы данных, решаются непосредственно администраторами баз данных, а проектировщики редко привлекаются к решению, в том числе и этих задач, на стадии управления изменениями в процессе начального, тестового, опытного этапов эксплуатации базы данных. В наших лекциях мы акцентировали внимание на тех задачах проектировщика базы данных, которые в основном не зависят от учета конкретной программно-аппаратной среды функционирования базы данных.
На этом этапе проектирования базы данных важно завершить документирование модели. Все объекты и их элементы физической модели должны быть описаны и прокомментированы.
В результате должен быть создан подробный отчет по физической модели. В этом случае неоценимую поддержку в работе проектировщика оказывают CASE-средства поддержки процесса проектирования, например, хорошо известный Erwin или PowerDesigner, которые позволяют в автоматическом режиме создавать такие отчеты.
После разработки или генерирования скрипта создания физической базы данных, загрузки в нее реальных или тестовых данных, проектировщик базы данных обязан с помощью средств СУБД получить информацию о параметрах объектов базы данных, чтобы сопоставить ее с предполагаемыми параметрами, и при необходимости выполнить их корректировку (не забывая отразить эти действия в отчете). Основные параметры объектов, которые следовало бы проконтролировать после создания физической базы данных, приведены в таблицах 13.9.- 13.14. Это касается основных объектов - таблиц, индексов, представлений, синонимов, ролей и пользователей.
| Таблица | Имя таблицы | |
| Столбец | Имя столбца | |
| Default | Устанавливает для столбца значение по умолчанию, которое будет использоваться при отсутствии значения в операторе insert | |
| Ограничение_столбца_ref | Содержит ссылку на ограничение другого столбца, которое должно применяться к данному столбцу | |
| Ограничение_столбца | Устанавливает ограничения целостности как часть определения столбца | |
| Тип_данных | Задает тип данных - числовой, символьный, большой объем и т.д. | |
| Ограничение_таблицы | Устанавливает ограничения целостности для все таблицы | |
| Ограничение_таблицы_ref | Содержит ссылку на ограничение другой таблицы, которое должно применяться к данной таблице | |
| Tablespace | Табличное пространство, в которое должна быть помещена таблица | Табличное пространство по умолчанию, назначенное владельцу таблицы |
| Logging/NoLogging | Указывает, должна ли информация об объекте отслеживаться в файле журнала повтора | Logging |
| Petfree | Указывает, сколько процентов свободного пространства должно сохраняться в каждом блоке данных для будущих обновлений строк таблицы | Диапазон 1-99, по умолчанию 10 % |
| Petused | Задает минимальный объем использованного пространства, поддерживаемый в каждом блоке данных таблицы | Диапазон 1-100, по умолчанию 40 % |
| Initrans | Задает начальное количество транзакционных записей, выделяемых в каждом блоке данных таблицы | Диапазон 1-255, по умолчанию 1 (2 для кластера или индекса) |
| Maxtrans | Задает максимальное количество параллельных транзакций, которые могут обновлять блок данных таблицы (не применяется к запросам) | Диапазон 1-255, значение по умолчанию зависит от размера блока данных |
| Конструкция_хранения | Те же параметры, что и для табличного пространства |
| Unigue | Указывает, что значения столбца (столбцов) индекса должны быть уникальны | Nonunigue |
| Bitmap | Указывает, что будет битовым, а не индексом В-дерева (используется для столбцов с низкой кардинальностью) | В-дерево |
| Схема | Указывает имя владельца таблицы | Схема создателя индекса |
| Имя_индекса | Задает имя индекса | |
| Конструкция кластерного индекса | Указывает, что индекс должен быть построен для кластера, и содержит список кластерных атрибутов | |
| Конструкция индекса таблицы | Указывает таблицу, для которой строится индекс, в том числе любые псевдонимы таблицы, список индексных выражений, а также является ли индекс локальным или глобальным (для разделенных индексов) | По умолчанию используется схема создателя индекса, индекс создается как глобальный |
| Список индексных выражений | Определяет либо столбцы, по которым выполняется индексирование, либо список выражений для создания функционального индекса | Для регулярного индекса не более 32 столбцов; для битового индекса не более 30 |
| ASC/DESC | Указывает, в каком порядке будет создаваться индекс - возрастающем или убывающем | По возрастанию |
| Список физических атрибутов | Те же атрибуты, что и для таблицы: pctfree, pctused, initrans, maxtrans, конструкция_хранения | |
| Logging/Nologging | Указывает, будет ли информация об объекте отслеживаться в файле журнала повтора | Logging |
| Online | Указывает, должен ли индекс быть доступен сразу после создания | Online |
| Compute statistics | Указывает, должна ли генерироваться статистика по индексу | |
| Tablespace | Указывает табличное пространство, в котором будет храниться индекс | Табличное пространство по умолчанию, назначенное создателю индекса |
| Compress/Nocompress | Позволяет исключить повторяющиеся ключевые слова | Nocompress |
| Nosort | Указывает, что значения должны вставляться в порядке возрастания, - Oracle не будет сортировать строки при вставке | |
| Reverce | Сохраняет байты индекса в обратном порядке, за исключением идентификатора строки (row ID) - не может использоваться совместно с nosort |
В результате должен быть создан подробный отчет по физической модели. В этом случае неоценимую поддержку в работе проектировщика оказывают CASE-средства поддержки процесса проектирования, например, хорошо известный Erwin или PowerDesigner, которые позволяют в автоматическом режиме создавать такие отчеты.
После разработки или генерирования скрипта создания физической базы данных, загрузки в нее реальных или тестовых данных, проектировщик базы данных обязан с помощью средств СУБД получить информацию о параметрах объектов базы данных, чтобы сопоставить ее с предполагаемыми параметрами, и при необходимости выполнить их корректировку (не забывая отразить эти действия в отчете). Основные параметры объектов, которые следовало бы проконтролировать после создания физической базы данных, приведены в таблицах 13.9.- 13.14. Это касается основных объектов - таблиц, индексов, представлений, синонимов, ролей и пользователей.
| Таблица | Имя таблицы | |
| Столбец | Имя столбца | |
| Default | Устанавливает для столбца значение по умолчанию, которое будет использоваться при отсутствии значения в операторе insert | |
| Ограничение_столбца_ref | Содержит ссылку на ограничение другого столбца, которое должно применяться к данному столбцу | |
| Ограничение_столбца | Устанавливает ограничения целостности как часть определения столбца | |
| Тип_данных | Задает тип данных - числовой, символьный, большой объем и т.д. | |
| Ограничение_таблицы | Устанавливает ограничения целостности для все таблицы | |
| Ограничение_таблицы_ref | Содержит ссылку на ограничение другой таблицы, которое должно применяться к данной таблице | |
| Tablespace | Табличное пространство, в которое должна быть помещена таблица | Табличное пространство по умолчанию, назначенное владельцу таблицы |
| Logging/NoLogging | Указывает, должна ли информация об объекте отслеживаться в файле журнала повтора | Logging |
| Petfree | Указывает, сколько процентов свободного пространства должно сохраняться в каждом блоке данных для будущих обновлений строк таблицы | Диапазон 1-99, по умолчанию 10 % |
| Petused | Задает минимальный объем использованного пространства, поддерживаемый в каждом блоке данных таблицы | Диапазон 1-100, по умолчанию 40 % |
| Initrans | Задает начальное количество транзакционных записей, выделяемых в каждом блоке данных таблицы | Диапазон 1-255, по умолчанию 1 (2 для кластера или индекса) |
| Maxtrans | Задает максимальное количество параллельных транзакций, которые могут обновлять блок данных таблицы (не применяется к запросам) | Диапазон 1-255, значение по умолчанию зависит от размера блока данных |
| Конструкция_хранения | Те же параметры, что и для табличного пространства |
| Unigue | Указывает, что значения столбца (столбцов) индекса должны быть уникальны | Nonunigue |
| Bitmap | Указывает, что будет битовым, а не индексом В-дерева (используется для столбцов с низкой кардинальностью) | В-дерево |
| Схема | Указывает имя владельца таблицы | Схема создателя индекса |
| Имя_индекса | Задает имя индекса | |
| Конструкция кластерного индекса | Указывает, что индекс должен быть построен для кластера, и содержит список кластерных атрибутов | |
| Конструкция индекса таблицы | Указывает таблицу, для которой строится индекс, в том числе любые псевдонимы таблицы, список индексных выражений, а также является ли индекс локальным или глобальным (для разделенных индексов) | По умолчанию используется схема создателя индекса, индекс создается как глобальный |
| Список индексных выражений | Определяет либо столбцы, по которым выполняется индексирование, либо список выражений для создания функционального индекса | Для регулярного индекса не более 32 столбцов; для битового индекса не более 30 |
| ASC/DESC | Указывает, в каком порядке будет создаваться индекс - возрастающем или убывающем | По возрастанию |
| Список физических атрибутов | Те же атрибуты, что и для таблицы: pctfree, pctused, initrans, maxtrans, конструкция_хранения | |
| Logging/Nologging | Указывает, будет ли информация об объекте отслеживаться в файле журнала повтора | Logging |
| Online | Указывает, должен ли индекс быть доступен сразу после создания | Online |
| Compute statistics | Указывает, должна ли генерироваться статистика по индексу | |
| Tablespace | Указывает табличное пространство, в котором будет храниться индекс | Табличное пространство по умолчанию, назначенное создателю индекса |
| Compress/Nocompress | Позволяет исключить повторяющиеся ключевые слова | Nocompress |
| Nosort | Указывает, что значения должны вставляться в порядке возрастания, - Oracle не будет сортировать строки при вставке | |
| Reverce | Сохраняет байты индекса в обратном порядке, за исключением идентификатора строки (row ID) - не может использоваться совместно с nosort |
Создание пользователей
Одной из задач, которая чаще всего решается в процессе эксплуатации базы данных, является создание пользователей базы данных. В контексте баз данных термин "пользователь" имеет два основных значения:
это человек, который входит в базу данных для выполнения работы;это область схемы приложения, в которой хранятся объекты, используемые этим приложением.
На самом деле под созданием пользователя базы данных понимают создание учетной записи пользователя (user account) в словаре данных, с помощью которой пользователь может войти в базу данных для выполнения своей работы. Учетные записи представляют собой способ организации доступа пользователя к базе данных, выдачи разрешений на выполнение требуемых задач, а также отслеживания выполняемых пользователем действий. Такая учетная запись включает имя пользователя базы данных, которое присваивают пользователю в базе данных, пароль, указание которого разрешает доступ к объектам базы данных, уровень полномочий или уровень доступа и еще ряд опциональных параметров. Уровень доступа означает тип операций, которые данный пользователь может выполнять (создавать таблицу, определять пользователей и т.д).
Пользователь является объектом базы данных. При создании пользователя базы данных обязательным является указание имени. Остальные параметры могут быть установлены администратором базы данных позже с помощью команды SQL ALTER USER.
Создание пользователей называется еще задачей авторизации или аутентификации пользователей. Решение этой задачи возлагается на администраторов базы данных. Проектировщик во время проектирования базы данных, совместно с администратором базы данных, может создать всех потенциальных пользователей. Хорошим тоном проектирования является создание проектировщиком следующих пользователей: разработчиков приложения базы данных и тестировщиков базы данных. Именно эти лица получают спроектированную базу данных в опытную эксплуатацию.
Чтобы создать пользователей базы данных, проектировщик должен иметь список лиц, которым будут разрешен доступ к базе данных.
Для создания пользователей базы данных или учетной записи пользователя используется команда SQL CREATE USER, неполный синтаксис которой приведен ниже:
CREATE USER имя_пользователя IDENTIFIED BY [пароль|EXTERNALLY];
Ключевое слов EXTERNALLY указывает, что должна быть использована аутентификация операционной системы.
Для исключения пользователей из базы данных применяется команда DROP USER.
В качестве примера создадим пользователей нашей учебной базы данных. Допустим, что у нас есть следующий фрагмент списка лиц, которые в организации будут пользоваться базой данных:
... Иванов А.А. - разработчик приложения Петров В.В. - разработчик приложения Сидоров С.С. - тестировщик базы данных …
Иванов и Петров уже имеют системную аутентификацию, и администратор базы данных решил, что учетная запись для их доступа к базе данных будет такой же. Их имена для входа в систему - Ivan и Peter, соответственно. Сидоров - это новый сотрудник, который будет принят на работу через 1,5 месяца специально для тестирования базы данных. Совместно с администратором базы данных было решено, что его имя для входа в базу данных будет Sidorov, а пароль - alsy_. Тогда проектировщик базы данных может включить в свой скрипт следующие команды:
CREATE USER Ivan IDENTIFIED BY EXTERNALLY; CREATE USER Peter IDENTIFIED BY EXTERNALLY; CREATE USER Sidorov IDENTIFIED BY 'alsy_';
Создание табличных пространств
Проектировщик базы данных работает с логическими объектами - табличными пространствами, таблицами, представлениями, индексами и т.д., так называемыми логическими файлами СУБД Oracle. Информация о содержимом логических файлов хранится в словаре данных.
Одним из самых трудных для понимания и объяснения объектов реляционной базы данных Oracle является табличное пространство (tablespace). Табличным пространством в Oracle называется логическая область хранения данных, размер которой ограничен размером используемого жесткого диска. Физические файлы создаются на уровне табличного пространства.
Выделением места для табличных пространств и способом их совместного использования управляют администраторы базы данных. Поэтому проектировщики базы данных должны при использовании табличных пространств работать в тесном сотрудничестве с ними.
Табличные пространства можно создавать, менять и удалять. Для создания табличных пространств в СУБД Oracle предусмотрена команда SQL CREATE TABLESPACE, параметры которой приведены в таблице 13.8.
| Имя_табличного_пространства | Имя, присваемое табличному пространству. Оно должно отражать назначение этого табличного пространства | |
| Спецификация файла данных | ||
| Местонахождение файла | Полный путь к каталогу и имя файла | |
| size | Полный начальный размер файла данных, в соответствии с которым выделяется дисковое пространство | |
| reuse | Если файл данных существует, его нужно использовать повторно, указав этот параметр, в противном случае возникнет ошибка. Размер файла должен совпадать с указанным в параметре size | |
| autoextend | Разрешает или запрещает автоматическое увеличение размера файла данных.Может принимать значения on и off. Для on существуют дополнительные параметры: next: величина приращения файла (в байтах). maxsize: наибольший допустимый размер файла данных. Может быть неограниченным (unlimited) | ON |
| minimum extend | Предназначен для управления фрагментацией - определяет минимальный размер экстента | |
| default предложение хранения | объем пространства, выделяемого объекту при отсутствии явно указанной конструкции хранения | |
| initial | Объем пространства, выделяемого для первого экстента страниц | 5 |
| next | Объем для пространства, выделяемого для второго и последующих экстентов | 5 физических страниц |
| minextens | Минимальное количество выделяемых экстентов | 1 |
| maxextents | Максимальное количество выделяемых экстентов - может быть неограниченным (intimated) | 121 |
| petincrease | Коэффициент приращения размера (в %) для каждого следующегоэкстента после next | 50 |
| freelist groops | Используется в режиме параллельного сервера и указывает количество списков свободных блоков для объектов, созданных в данном табличном пространстве без использования конструкций хранения | |
| freelist | Указывает количество списков свободных блоков, созданных в данном табличном пространстве без использования конструкций хранения | |
| optimal | Применяется только к сегментам отката и определяет минимальный объем пространства, до которого сокращается сегмент отката после расширения за пределы оптимального значения | |
| online/offline | Указывает, режим (оперативный/автономный, в котором изначально должно находиться табличное пространство | online |
| permanent/temporary | Указывает, будет ли табличное пространство содержать объекты или только временные сегменты | permanent |
| Предложение управления экстентами | ||
| dictionary | Указывает, что управление экстентами осуществляется только через словарь данных | dictionary |
| local | Указывает, что некоторая часть табличного пространства зарезервирована для битовых карт | user |
| plugged_in | Используется с переносимыми табличными пространствами и указывает, что табличное пространство может быть "подключено" к базе данных | NO |
В качестве примера создадим табличное пространство, не используя никаких параметров, как показано ниже.
CREATE TABLESPACE my_ts DATAFILE 'c:\ora9i\oradata\orcr\myfile01.dbf' SIZE 2M;
Ключевое слово TABLESPACE задает имя табличной области (my_ts), ключевое слово DATAFILE задает спецификацию файла операционной системы ('c:\ora9i\oradata\orcr\myfile01.dbf'), в котором будут размещаться данные создаваемой табличной области, ключевое слово SIZE задает размер табличного пространства в мегабайтах. Остальные значения параметров принимаются по умолчанию. В частности, поскольку значение по умолчанию для параметра AUTOEXTEND есть ON (см. таблицу 13.8), то разрешено автоматическое расширение пространства, выделенного для данного табличного пространства. По умолчанию созданное табличное пространство переходит в оперативный режим (ONLINE) и является постоянным табличным пространством (PERMANENT).
Для изменения параметров табличного пространства используется команда ALTER TABLESPACE, а для удаления - команда DROP.
Чтобы разместить объект базы данных в определенном табличном пространстве, необходимо явно указать это табличное пространство в командах SQL. Например, команда
CREATE TABLE CUSTOMER (CUSTOMER_ID CHAR(5) NOT NULL, CUSTOMER_NAME VARCHAR(25), CUSTOMER_ADDR VARCHAR(50), CUSTOMER_RATING CHAR(10)) TABLESPASE my_st, PCTFREE 15;
размещает таблицу CUSTOMER и табличном пространстве my_st.
Средства разграничения доступа в СУБД Oracle
Современный уровень развития средств вычислительной техники характеризуется высокой степенью распределенной обработки данных, когда логически единая информационная база данных размещается в сетевой среде. Вычислительные сети характеризуются объединением различных компьютеров на основе программно-аппаратной платформы, асинхронной многопользовательской обработкой и развитыми средствами разграничения доступа. При этом реальная сложность организации управления доступа скрыта от пользователя. Логическое пространство базы данных должно выглядеть для пользователя так, как если бы вся база данных располагалась на его локальном компьютере.
При работе с базой данных должна быть обеспечена высокая степень безопасности данных практически без усложнения работы пользователя. Одной из неотъемлемых функций любой промышленной СУБД, в том числе и Oracle, является обеспечение защиты информации на уровне доступа к данным. Для этого в СУБД Oracle используется система избирательного управления доступом. Это означает, что администратор базы данных создает пользователей и управляет их полномочиями на выполнение конкретных операций с конкретными объектами в базе данных.
Задача распознавания пользователей и разграничения доступа между различными пользователями базы данных решается как на уровне средств операционной системы, так и на уровне СУБД. Стандартным подходом к решению этой задачи на обоих уровнях является поддержка механизма создания (регистрации) пользователя и назначения ему привилегий. С другой стороны, каждый пользователь, создающий некоторый объект, должен иметь право на управление доступом к этому объекту. Типичное решение состоит в реализации концепции привилегий или прав доступа. Привилегия - это некоторый поддерживаемый СУБД или операционной системой признак, который определяет, может ли конкретный пользователь выполнить конкретную операцию.
Для этого администраторы базы данных создают пользователей базы данных и предоставляют им некоторые полномочия по доступу к объектам базы данных.
Обеспечение правомерности доступа пользователя реализуется средствами аутентификации системы, простейшим из которых является использование пароля, подтверждающего подлинность пользователя. Для входа в базу данных пользователь должен ввести имя и подтвердить свою подлинность паролем.
В СУБД Oracle реализована поддержка принципа безопасности по умолчанию, который состоит в назначении наименьших привилегий. Т.е. пользователь может получить доступ к объекту базы данных или выполнить определенные действия в базе данных, только если ему это явно разрешено.
Концепция обеспечения безопасности доступа, принятая в Oracle, предусматривает выполнение следующих действий:
идентификацию и аутентификацию пользователей;контроль глобальных действий в базе данных, определяющих доступность ее объектов;контроль доступа к объектам базы данных;контроль операций, производимых над объектами базы данных.
Проектировщик базы данных решает эти задачи, исходя из предположения, что база данных доступна. В терминах СУБД Oracle это означает, что администратор базы данных запустил экземпляр (instance) сервера базы данных.
Основной целью настоящей лекции является
Основной целью настоящей лекции является формирование навыков решения следующей профессиональной задачи проектирования баз данных этапа создания физической модели базы данных, а именно - подготовки инсталляционного скрипта для создания базы данных.
Проектировщик базы данных, выполнив задачи предыдущей лекции, фактически выполнил свою основную работу над созданием скрипта для создания базы данных. Если работа по проектированию базы данных закончена, то зачем проектировщику еще что-то делать, кроме того как проанализировать и проверить проделанную работу, отредактировать окончательный вариант скрипта и создать физическую базу данных? Принято считать, что задача создания базы данных, так же как и управление базой данных, является задачей администратора базы данных. Значит, можно задокументировать проделанную работу и передать ее администратору базы данных.
Однако процесс проектирования физической модели базы данных не закончен. Из нашего рассмотрения выпали следующие вопросы:
требования по обеспечению потенциальных пользователей к базе данных и ее объектам, так называемые требования безопасности базы данных;требования к размещению и хранению объектов базы данных на физических носителях в рамках операционной системы, т.е. привязка объектов базы данных файлам операционной системы.
Однако одним из главных результатов проектирования базы данных является создание физической базы данных на основе разработанного в процессе проектирования инсталляционного скрипта. Поэтому проектировщик базы данных должен вместе с администратором базы данных рассмотреть вышеуказанные задачи и подготовить такой инсталляционный скрипт для создания базы, а не передавать эту работу администратору базы данных, хотя на практике последнее часто происходит. С другой стороны, проектировщик базы данных в организации зачастую выступает в качестве администратора базы данных. Это тоже одна из важных причин рассмотрения решения этих вопросов в рамках этого курса.
Таким образом, задача настоящей лекции состоит из решения четырех крупных подзадач:
создание пользователей, их идентификация и назначение им привилегий;привязка разработанных объектов реляционной базы данных к параметрам физического хранения базы данных с помощью создания специальных объектов базы данных;создание инсталляционного скрипта;документирование базы данных.
Анализ функциональной модели предметной области базы данных
Чтобы спроектировать модули приложений, необходимо знать, как будет работать информационная система с базой данных. Такую информацию можно получить из функциональной модели предметной области базы данных. Для проектирования модулей приложений проектировщику базы данных нужен набор спецификаций функций, которые задают необходимые требования к обработке бизнес-данных, а также набор зависимостей между различными бизнес-функциями.
Фактически это означает, что входом для решения задачи проектирования модулей приложений базы данных является иерархия функций. На выходе проектировщик должен получить описание (спецификацию) модулей приложений, а в процессе проектирования модулей проектировщик строит отображение бизнес-требований в спецификации модулей.
Алгоритм действий проектировщика базы данных состоит в следующем: сначала проектировщик пытается сформулировать бизнес-требования (функции) в самом общем виде, а затем выполняет декомпозицию каждой такой бизнес-функции до тех пор, пока не будет получена некоторая функция, которую можно считать атомарной функцией. Критерием атомарности функции является получение ответа на вопрос, имеет ли смысл выполнить только часть функции.
Пример. Рассмотрим фрагмент иерархии функций для обработки заявлений о выплате страхового возмещения. На упрощенной схеме рис. 14.1 показана функция "2. Обработать заявление". Выполнение этой функции включает выполнение четырех функций следующего уровня: "2.1. Зарегистрировать заявление", "2.2. Принять решение по заявлению", "2.3. Произвести платеж по заявлению", "2.4. Закрыть заявление".
На рис. 14.1 показана дальнейшая декомпозиция функции "2.2. Принять решение по заявлению". Полученная на этом этапе функция "2.2.5. Разрешить ремонт" является атомарной функцией. Ремонт разрешается либо не разрешается.
Рис. 14.1. Иерархия функции для обработки заявлений о выплате страхового возмещения
При рассмотрении иерархии функций проектировщику базы данных следует обратить внимание на следующие моменты:
в функциональной модели базы данных описываются бизнес-функции, и не все они будут непосредственно поддерживаться приложением базы данных;при рассмотрении иерархий нередко возникает ситуация, когда экземпляры одной и той же функции будут иметь разные номера.Если в первом случае дополнительную информацию о том, какие бизнес-функции будут реализованы в системе, можно получить от руководителя проекта, то во втором случае проектировщик базы данных, вероятнее всего, имеет дело с ошибкой аналитика в определении функции.
Общие принципы разработки спецификаций модулей
После разработки схемы "функции-модули" и схемы "модули-данные" проектировщик приступает к решению довольно трудоемкой задачи - написанию спецификаций модулей. Именно это спецификация позволит программистам и компоновщикам построить реальную систему с использованием базы данных.
При написании спецификаций следует исходить из того, что человек, который будет писать код, умеет это делать. Поэтому из спецификаций нужно по возможности исключить все указания по тому, как нужно, с вашей точки зрения, писать код. Это следует из того практического соображения, что никто не может создать правильный код без его тестирования. Поскольку проектировщик базы данных, как правило, сам не собирается писать код, то ему в спецификации не следует диктовать структуру реального кода.
Алгоритмы, даже очень сложные, следует формулировать в общем виде. Нужно стараться избегать формальных языков описания алгоритмов.
Также не следует вставлять в спецификации модулей команды SQL. В процессе тестирования администратор базы данных может изменить разработанную проектировщиком базы данных физическую модель базы данных, и, следовательно, команды могут измениться.
Следует избегать лишних инструкций в спецификациях. Например, не нужно объяснять программистам, что они должны выйти из цикла при исключительных ситуациях.
Спецификация модуля должна обязательно включать следующее:
Условное название модуля.Функции, выполнение которых обеспечивает данный модуль.Список таблиц и колонок, к которым производится доступ.Для каждой колонки - способ использования колонки, а именно, запрашиваются ли, вставляются, удаляются ли, обновляются ли данные указанных колонок.Список колонок, которые используются в предикатах поиска.Конкретное описание того, что модуль должен делать.
Пример. Приведем типовую спецификацию модуля для предоставления пользователю доступа к приложению базы данных.
Наименование модуля: Страница для входа в приложение (LogIn).
Цель: идентификация пользователя и предоставление доступа к приложению базы данных.
Входные данные
Имя пользователя
Пароль
Таблица базы данных: USERACCOUNT
Колонки:
USERNAME - запрашивается, используется в предикате поиска
USERPASS - запрашивается, используется в предикате поиска
Действия:
Если пользователя с таким именем и паролем нет в базе данных - отказать в доступе и попросить правильно ввести свои данные (на случай ошибки), но не более трех раз.
Если пользователь есть в базе данных - предоставить доступ к модулю "Главная страница", которая в зависимости от полномочий пользователя может иметь различный внешний вид.
Комментарий:
В зависимости от типа модуля (экранная форма, отчет и т.д.), спецификации могут включать дополнительную информацию, такую как требования к расположению кнопок или формат отчета. Для таких модулей спецификацию следует дополнить следующими позициями:
данные о навигации (какой модуль вызывает, и какие модули вызываются;значения входных параметров по умолчанию; список событий, обрабатываемый на экранной форме, и как они обрабатываются; список ошибок и действий, связанных с их обработкой; данные о безопасности; макет экранной формы или шаблон отчета.
Пример. В качестве примера приведем спецификацию экранной формы для работы с базой данных через браузер.
Наименование экранной формы: Web-страница Форма 3: Список исполнителей.
Цель: приписать исполнителей к проекту, определить их занятость и статус.
Входные данные
Номер проекта
Навигация:
Вызывается из модуля "Редактирование Формы 1".
Возвращает управление в модуль "Редактирование Формы 1".
Действия:
Выбрать из списка исполнителяОпределить его статус - основной, неосновной, руководительОпределить занятость исполнителяСохранить запись об исполнителеПерейти на ввод данных о следующемВозвратить на редактирование Формы 1.
Таблицы:
| ENPID | Внутренний номер служащего | INSERT |
| PROJID | Внутренний номер проекта | INSERT |
| TN | табельный номер (из представления) | |
| NM | ФИО | INSERT |
| PS | Должность | |
| GR | Разряд | |
| DR | Ученая степень | |
| ZV | Ученое знание | |
| JOB | Занятость в проекте в мес. | INSERT |
| EMPSTATUS | Статус исполнителя | INSERT |
| ENPID | Внутренний номер служащего | |
| TN | табельный номер (из представления) | |
| NM | ФИО | Предикат поиска |
| PS | Должность | |
| GR | Разряд | |
| DR | Ученая степень | |
| ZV | ученое звание |
Входные данные
Имя пользователя
Пароль
Таблица базы данных: USERACCOUNT
Колонки:
USERNAME - запрашивается, используется в предикате поиска
USERPASS - запрашивается, используется в предикате поиска
Действия:
Если пользователя с таким именем и паролем нет в базе данных - отказать в доступе и попросить правильно ввести свои данные (на случай ошибки), но не более трех раз.
Если пользователь есть в базе данных - предоставить доступ к модулю "Главная страница", которая в зависимости от полномочий пользователя может иметь различный внешний вид.
Комментарий:
В зависимости от типа модуля (экранная форма, отчет и т.д.), спецификации могут включать дополнительную информацию, такую как требования к расположению кнопок или формат отчета. Для таких модулей спецификацию следует дополнить следующими позициями:
данные о навигации (какой модуль вызывает, и какие модули вызываются;значения входных параметров по умолчанию; список событий, обрабатываемый на экранной форме, и как они обрабатываются; список ошибок и действий, связанных с их обработкой; данные о безопасности; макет экранной формы или шаблон отчета.
Пример. В качестве примера приведем спецификацию экранной формы для работы с базой данных через браузер.
Наименование экранной формы: Web-страница Форма 3: Список исполнителей.
Цель: приписать исполнителей к проекту, определить их занятость и статус.
Входные данные
Номер проекта
Навигация:
Вызывается из модуля "Редактирование Формы 1".
Возвращает управление в модуль "Редактирование Формы 1".
Действия:
Выбрать из списка исполнителяОпределить его статус - основной, неосновной, руководительОпределить занятость исполнителяСохранить запись об исполнителеПерейти на ввод данных о следующемВозвратить на редактирование Формы 1.
Таблицы:
| ENPID | Внутренний номер служащего | INSERT |
| PROJID | Внутренний номер проекта | INSERT |
| TN | табельный номер (из представления) | |
| NM | ФИО | INSERT |
| PS | Должность | |
| GR | Разряд | |
| DR | Ученая степень | |
| ZV | Ученое знание | |
| JOB | Занятость в проекте в мес. | INSERT |
| EMPSTATUS | Статус исполнителя | INSERT |
| ENPID | Внутренний номер служащего | |
| TN | табельный номер (из представления) | |
| NM | ФИО | Предикат поиска |
| PS | Должность | |
| GR | Разряд | |
| DR | Ученая степень | |
| ZV | ученое звание |
Определение функций
При разработке иерархии функций аналитик должен предоставить текстовое описание к каждой функции, по крайней мере для верхнего и самого нижнего уровней иерархии. Желательно, чтобы в этом описании аналитики выделяли сущности предметной области. Это важно для того, чтобы знать с какими сущностями предметной области работает функция, т.е. какие потенциальные объекты реляционной базы данных будут использоваться в каждой функции. Если это не сделано, то проектировщику базы данных придется делать это самостоятельно.
Пример. Определения функции "2.2.2. Проверить обеспечено ли заявление".
"Получить и зарегистрировать все требуемые страховой компанией сведения о заявлении (СВЕДЕНИЯ О ЗАЯВЛЕНИИ), включая все подробные сведения о третьих сторонах (СТОРОННИЕ ЮРИДИЧЕСКИЕ ЛИЦА) и свидетелях (ФИЗИЧЕСКИЕ ЛИЦА).
Изучить страховой полис (ПОЛИС) на предмет наличия исключительных ситуаций (ИСКЛЮЧЕНИЯ) и определить, действуют ли эти ситуации в случае данного заявления (ЗАЯВЛЕНИЕ).
Если имеется исключение, то закрыть заявление и составить стандартное письмо заявителю об отказе в выплате (ПИСЬМО) заявителю (ЗАЯВИТЕЛЬ).
Если никаких исключений нет, то изменить статус заявления на ожидание оценки, назначить и уведомить оценщика (ОЦЕНЩИК)."
Из примера видно, какие сущности предметной области участвуют в выполнении функции (выделены в скобках), как меняется состояние сущности (выделено курсивом) и каков алгоритм работы этой функции.
Из примера ясно, что на этом этапе проектировщик базы данных в качестве входных данных использует также информационную модель предметной области базы данных (описание сущностей).
При выполнении анализа функций полезно иметь некоторую таблицу (матрицу) "Функция-Сущность". Эта матрица должна дать ответ на следующие вопросы:
имеет ли каждая сущность конструктор (функцию, которая создает все экземпляры сущности);имеет ли она деструктор (функцию, которая удаляет экземпляры сущности);есть ли ссылка на эту сущность (функции, которые используют эту сущность и каким образом).
Процесс анализа взаимодействия функции и сущности принято обозначать аббревиатурой CRUD (Create, Reference, Update, Delete - создание, ссылка, модификация, удаление).
Полезными для понимания проектировщиком базы данных назначения функций и того, как данные функции участвуют в процессе обработки данных в системе, могут быть диаграммы потока данных и диаграммы жизненных циклов сущностей, которые были рассмотрены нами во второй лекции. Последние, в частности, дают ясную картину изменения состояния сущности, что важно в определении атрибутов статуса сущности.
Отображение функций в модули
Одной из основных задач проектирования модулей приложений является построение отображения функций в модули. При решении этой задачи проектировщик базы данных должен акцентировать внимание на структуре базы данных, которая составляет основу приложения.
Как правило, решение задачи отображения функций в модули решается в четыре этапа:
Анализ работы функции.Построение модели сущностей, поддерживающей эти функции.Начать проектирование физической структуры с создания схемы, поддерживающей разработанную модель сущностей.Завершить проектирование разработкой спецификаций модулей, которые реализуют функции на предложенной схеме базы данных.
Из предложенного выше подхода видно, как тесно переплетаются в процессе проектирования процессы разработки физической модели базы данных и спецификаций модулей приложений. Таким образом, если проектировщиком был разработан черновой вариант физической модели базы данных по алгоритмам, рассмотренным нами в предыдущих лекциях, то на этом этапе он должен быть адаптирован к реализации функций и, возможно, значительно переработан.
К сожалению, никаких унифицированных и простых способов отображения функций в модули приложений не существует. Это обусловлено двумя обстоятельствами: комбинаторной сложностью построения отображений множеств (теоретически доказано, что для такого класса комбинаторных задач не существует в общем случае алгоритмов с линейной сходимостью) и сложностью выработки критерия того, что полученное отображение оптимально (т.е. довольно широкий семантический произвол в обосновании вариантов того или иного отображения). Как показывает опыт проектирования, мнение относительно состава и количества модулей приложений в процессе проектирования меняется неоднократно.
В последнее время хорошие результаты в разработке и проектировании систем и, в частности, модулей приложений получены с применением объектно-ориентированного подхода на основе унифицированного языка моделирования UML и CASE-инструментария, который этот язык поддерживает. Однако рассмотрение этой методики разработки систем представляет собой предмет отдельного курса, и здесь излагаться не будет. В списке литературы к лекции указаны монографии и учебники по этой популярной методике.
Однако рассмотрение этой методики разработки систем представляет собой предмет отдельного курса, и здесь излагаться не будет. В списке литературы к лекции указаны монографии и учебники по этой популярной методике.
При отображении функций в модули необходимо получить схему, которая ставит в соответствие каждой функции определенный модуль.
Пример. Рассмотрим нашу учебную базу данных, содержащую информацию о сотрудниках, отделах и проектах организации. Допустим, она будет поддерживать бизнес-функцию "Управление проектами в организации". Функциональная модель предметной области базы данных в терминах иерархии функций приведена на рис. 14.2, а на рис. 14.3 приведен перечень функций управления проектами в организации.
Рис. 14.2. Иерархия бизнес-функции "Управление проектами в организации"
Рис. 14.3. Перечень функции управления проектами в организации
Задача состоит в отображении функций из перечня на рис. 14.3 в список модулей.
Сначала из перечня функций должны быть удалены те функции, которые не будут поддерживаться приложением базы данных. Проектировщик узнает у руководителя проекта, что в приложении базы данных не будут поддерживаться следующие функции:
назначить куратора проекта;известить руководителей подразделений; известить сотрудников; собрать совещание; приступить к выполнению; составить список работ; определить объем работ; определить стоимость работ; определить время работ; определить производственные мощности; распределить производственные мощности; распределить работы по сотрудникам; контролировать ход выполнения проекта.
Таким образом, будет получен список функций, который показан в левой колонке таблицы 14.1. Этому списку функций должен быть поставлен в соответствие список модулей приложения базы данных.
| Назначить руководителяя проекта | Ввод информации о проекте | |
| Определить бюджет проекта | Ввод информации о сотрудниках | |
| Определить список подразделений | Поиск информации о сотрудниках | |
| Определить список сотрудников | Поиск информации о проектах | |
| Выполнять проект | Генерация отчета о выполненных проектах | |
| Сдать проект | Генерация отчета о выполняемых проектах |
Руководитель проекта передал проектировщику базы данных характеристику приложения базы данных по управлению выполнением проектов в организации. Это приложение будет заниматься учетом выполняемых и выполненных проектов в организации. Главными вопросами, на которые должно отвечать приложение, являются:
Какие проекты выполняются в организации? Какие сотрудники в каком проекте участвуют? Какими проектами кто руководит? Какие проекты выполнялись в организации? Какие сотрудники в каком проекте участвовали? Какими проектами кто руководил?
Проектировщик базы данных составил список модулей приложения базы данных (правая колонка таблицы 14.1) и установил отображение функций в модули, как показано на рис. 14.4.
Рис. 14.4. Отображение функции в модули
Приведенный пример показывает общий принцип построения отображения бизнес-функций в модули.
В дополнение будет весьма полезным к разработанной схеме "функции-модули" составить схему "модули-данные", опираясь на изучение определения функций.
При составлении схемы "модули-данные" используется описание функций, логическая модель данных или итерация физической модели данных.
Пример. Рассмотрим модуль "Ввод информации о сотрудниках" из предыдущего примера и составим для него схему "модули-данные". При этом мы используем схему базы данных, приведенную на рис. 14.5.
Рис. 14.5. Физическая модель базы данных
Один из возможных результатов, который может быть получен проектировщиком базы данных, приведен в таблице 14.2.
| Ввод информации о сотрудниках | Employee | Empno | Чтение |
| Ename | Чтение, Поиск | ||
| Lname | Чтение, Поиск | ||
| Job | Чтение | ||
| Sal | Чтение | ||
| Depno | Чтение, Поиск | ||
| Department | Depno | Чтение, Поиск | |
| Manager | Чтение | ||
Проектирование процесса тестирования модулей приложений
Тестирование приложения базы данных есть один из основных элементов подготовки и проведения приемо-сдаточных испытаний. Как показывает практика, планирование тестирования приложений базы данных должно начинаться еще на стадии анализа. Имеется вероятность того, что заказчик может отказаться от ранее принятых критериев испытаний в процессе выполнения проекта. Однако чаще всего планирование тестирования откладывают до этапа проектирования, и, таким образом, составление плана тестирования становится задачей проектировщика базы данных или администратора баз данных.
процессе тестирования должно быть выяснено, что приложение базы данных делает то, что от него требуется, т.е. отвечает сформулированным требованиям приемо-сдаточных испытаний.
Как правило, в процессе проектирования проектировщик базы данных предлагает стратегию (или план) комплексного и системного тестирования. При разработке стратегии тестирования следует помнить о том, что должны использоваться тесты следующих категорий:
Автономные тесты (тесты модулей).Тесты связей модулей.Системный тест для приложения базы данных в целом.Приемо-сдаточные испытания (которые может проводить заказчик).Тесты производительности.
Обычно после проведения приемо-сдаточных испытаний подписывается Акт приемки-сдачи. Считается, что система переходит в состояние опытной эксплуатации, на которой выявляются и исправляются ошибки. После окончания стадии опытной эксплуатации система переходит в состояние промышленной эксплуатации, т.е. становится производственным ресурсом компании.
Как правило, тесты и их проведение планируются для удовлетворения требований приемо-сдаточных испытаний. Поэтому разработку стратегии тестирования следует начинать с детального изучения этих требований.
Планирование тестирования приложений базы данных зависит от используемых внутри организации стандартов и методик разработки информационных систем с базами данных. Такие методики различаются как по своему подходу к разработке систем, так и по составу производимой проектной документации.
Так, например, методики разработки, предлагаемые компаниями Microsoft и IBM, отличаются по составу проектной документации, хотя во многом схожи по методологической основе (объектно-ориентированному подходу).
Рассмотрим подход, который основан на модели проектной группы Модель MSF версии 3.1, предлагаемой компанией Microsoft. В этой методике предусмотрен так называемый ролевой кластер "Тестирование".
Задача ролевого кластера "Тестирование" (test) - одобрение выпуска продукта только лишь после того, как все дефекты выявлены и улажены. Любое программное обеспечение содержит дефекты. Обнаружение и устранение дефектов может подразумевать различные решения, начиная от устранения и заканчивая документированием способов обхода дефекта. Поставка продукта с известным дефектом, но с описанием способов его обхода является более предпочтительной, чем поставка продукта с невыявленным дефектом, который в дальнейшем станет сюрпризом - как для проектной команды, так и для заказчика.
Чтобы достичь успеха, команда тестировщиков должна фокусироваться на определенных ключевых задачах. Они структурируются в виде трех областей компетенции.
Планирование тестов: разработка методологии и плана тестирования;участие в установлении стандарта качества (quality bar);разработка спецификаций тестов.Разработка тестов: разработка и поддержка автоматизированных тестов (automated test cases), инструментов и скриптов;проведение тестов с целью определения состояния проекта;управление билдами (manage the build process).Отчетность о тестах: доведение до сведения проектной группы информации о качестве продукта;мониторинг найденных ошибок с целью обеспечения их улаживания до выпуска продукта.
Планирование тестов. Данная область компетенции (планирование тестов - test planning) ролевого кластера "Тестирование" формулирует методологию нахождения и урегулирования проблем качества продукта.
Команда тестировщиков разрабатывает планы и методики тестирования и таким образом формирует стратегию, используемую в проекте для тестирования решения.
В заключение раздела рассмотрим некоторые практические рекомендации проектировщикам базы данных при разработки стратегии тестирования.
Стратегия тестирования - это план проведения работ по тестированию системы или ее модуля, учитывающий специфику функциональности и взаимозависимости с другими компонентами системы и платформы. Стратегия определяет типы тестов, которые нужно выполнять для данного функционала системы, включает описание необходимых подходов с точки зрения целей тестирования и может задавать описания или требования к необходимым для проведения тестирования инструментам и инфраструктуре.
Стратегия тестирования должна отвечать на следующие вопросы:
Как, каким образом тестирование даст ответ, что данный функционал работает?Что нужно сделать и чем пользоваться из инструментальных средств, для достижения целей тестирования?Когда определенный функционал будет тестироваться и, соответственно, когда ожидать получения результатов?
В качестве дополнительной задачи, которая решается в процессе понимания стратегии тестирования, можно рассматривать задачу минимизации затрат на тестирование.
Из стратегии тестирования органично выписывается план тестирования. Шаблоны планов тестирования, предлагаемые различными методологиями, зачастую прямо включают одним из разделов описание стратегии тестирования или же включают описание стратегии в пункты плана, отвечающие за тестирование конкретных частей функционала. К примеру, шаблон методологии Rational Unified Process определяет стратегию тестирования как самый большой раздел плана тестирования.
Заказчик зачастую хочет контролировать процесс тестирования и видеть понимание задачи тестирования исполнителями по проекту. Для него стратегия тестирования - это менее детальный документ-видение того, как будет тестироваться система в процессе разработки.
При разработке стратегии тестирования для распределенной системы с базой данных проектировщику базы данных нужно выделить основные области, которые могут тестироваться отдельно друг от друга.
Объединим в рамках рассматриваемого примера тестирование функциональности сервера приложений и базы данных. Пусть сервер приложения реализует 20 команд по обработке данных и пользовательских сессий (без учета работы с системными пулами соединений, функций сжатия передаваемого по сети трафика и т.п.). Сервер баз данных реализует 10 системных операций по архивации данных, построению статистики использования отчетов и еще несколько подобных операций. Общий смысл заключается в том, что мы имеем конечный набор тестируемых операций, и так как конфигурации определены заранее, можем говорить о конечном наборе тестов, которые необходимо выполнить, чтобы проверить работоспособность серверного функционала системы. Итог - 30 тестов на серверной стороне. Заметим, что в данном примере мы не затрагиваем нагрузочную составляющую тестирования: речь идет только о функциональном тестировании.
При разработке стратегии тестирования проектировщик базы данных должен учитывать, что, планируя тестирование, он не начинает разбираться с системой, а занимается непосредственно планированием, выделением ресурсов и сроков на конкретные задачи.
Тестирование информационных систем является бурно развивающейся прикладной наукой. Практика показывает целесообразность применения CASE-средств для организации и проведения тестирования. В списке литературы к лекции указаны источники, которые следует использовать для углубленного изучения разработки планов тестирования приложений базы данных.
В этой лекции мы закончили обсуждение основных задач проектировщика базы данных, которые он должен выполнить обязательно (построение логической, физической модели данных, подготовка скрипта для создания базы данных и документирование модели), и в решении которых его участие очень желательно (планирование модулей приложений, планирование тестирования).
В двух последних лекциях мы рассмотрим задачи управления изменениями в структуре базы данных (или задачи обратного влияния), которые могут потребовать непосредственного участия проектировщика базы данных.
Литература: [9], [17], [18], [19], [22], [30], [33], [45].
Размещение логики обработки
Сложность решения задачи проектирования модулей обусловлена еще и тем, что проектировщик баз данных должен выделить из всего планируемого кода серверный код, создание которого обсуждалось в одной из предыдущих лекций. При этом следует придерживаться следующих простых правил:
Задействовать как можно больше ограничений на колонки реляционных таблиц для реализации правил обработки данных без процедурного кода.Задействовать триггеры базы данных для процедурного ввода правил обработки данных, в частности, для поддержки ссылочной целостности данных.Задействовать хранимые процедуры для инкапсуляции общих бизнес-функций. Использовать требования производительности при принятии окончательного выбора о разделении кода.
В настоящем разделе мы рассмотрим некоторые факторы, которые должны учитывать проектировщики баз данных при разграничении управления пользовательским интерфейсом приложений и выполнением операций обработки данных в модулях.
Как отмечают специалисты в области разработки и проектирования информационных систем, многие недостатки в прикладных системах вызваны тем, что в них не определены различия между правилами для данных, правилами для процессов и правилами для интерфейса. Рассмотрим основные.
Правила для данных. В правилах для данных формулируются условия, которым должны удовлетворять данные. Эти правила действуют для каждого экземпляра данных и выводятся из модели данных.
Примерами правил для данных являются следующие:
Пол человека должен быть либо мужской, либо женский. Это правило может быть введено с помощью ограничения CHECK в определении колонки таблицы базы данных.Каждый заказ должен быть предназначен для одного и только одного покупателя. Это правило для данных можно ввести с помощью ограничений PRIMERY KEY или NOT NULL.
Правила для процессов. В правилах для процессов определяется, что может (и что не может) делать приложение. Эти правила обычно выводятся из модели функций.
Примерами правил для процессов являются следующие:
Размер зарплаты не должен превышать 160000 рублей.
Это правило следует реализовать в приложении, в нем ничего не говорится о содержимом и определении базы данных. Оно выражает утверждение о том, что может быть, а чего не может быть.Только руководитель может санкционировать выплату премиальных. В момент санкционирования платежа приложение должно проверить наличие у пользователя надлежащих прав, т.е. руководитель ли он.
Правила для интерфейса. В правилах для интерфейса устанавливается, каким должен видеть пользователь приложение. Эти правила не касаются обработки, а только влияют на представление пользователя о приложении базы данных. Эти правила выводятся из спецификации пользовательского интерфейса.
Примерами правил для интерфейса являются следующие:
Все коды валют должны разъясняться. Это спецификация требований заказчика к визуализации кодов валют. Номера отделов и подразделений не должны показываться. Это также является требованием заказчика системы относительно визуализации данных в приложении.
Некоторые сформулированные правила бывают составными, а их составные части относятся к разным группам правил. Например, рассмотрим правило:
Все торговые операции, производимые в воскресенье, учитываются в бухгалтерских книгах за следующий понедельник.
Это два правила. Первое утверждает, что проводки по торговым операциям в бухгалтерских книгах нельзя делать за воскресенье. Это правило для данных. Второе разъясняет приложению, как откорректировать дату проводки, чтобы она стала приемлемой. К дате проводки, выпадающей на понедельник, нужно добавить единицу. Это правило для процессов.
Выделение и анализ этих трех групп правил приводит к формированию трех наборов документов: описание структуры интерфейса, структура процессов, которая определяет, как должен быть реализован интерфейс, и структура данных, задающая основные объекты базы данных, с которыми работают процессы.
Эти документы играют важную роль как в определении и логике, и ее размещении в приложении, так и в составлении спецификации модулей приложений базы данных.
Остановимся кратко на основных принципах размещения бизнес-логики в модулях приложения базы данных.
Правила для интерфейса реализуются во внешней (клиентской) части системы, независимо от того, какие языки программирования или генераторы отчетов используются. Правила для процессов реализуются в виде процедур, вызываемых из клиентской части системы. Они могут представлять серверный код, либо быть реализованы в виде модулей или библиотек на сервере приложений. Правила для данных реализуются самой базой данных в виде ограничений или триггеров.
Системные модули
Как указывалось выше, целью проектирования модулей является реализация функциональных возможностей, которые удовлетворяют бизнес-требованиям, выявленным в функциональной модели предметной области базы данных. Однако при этом необходимо рассмотреть достаточное число процессов, которые не вытекают непосредственно из сформулированных бизнес-функций. Например, возможность выдачи результатов работы модуля в файл или на принтер.
Набор модулей приложений базы данных, который непосредственно не следует из бизнес-функций, но необходим для обеспечения работы системы, называется системным.
К таким модулям можно отнести процедуры резервного копирования и автоматического восстановления, модули, предоставляющие пользователям возможность менять свой пароль, модули печати, модули навигации по приложениям базы данных и т.д.
Проектировщик базы данных самостоятельно разрабатывает спецификации таких модулей.
в общих чертах некоторые задачи,
В этой лекции рассматриваются в общих чертах некоторые задачи, которые должен решить проектировщик базы данных в процессе проектирования базы данных.
Даже хорошо спроектированная база данных ничего не стоит без приложений, которые обеспечивают ее жизнедеятельность, переводя ее из одного актуального состояния в другое и тем самым удовлетворяя потребности пользователей в информации. При этом пользователи ждут программ, которые помогали бы им решать их задачи быстро и обладали широкими возможностями поиска и обработки информации. Поэтому проектировщику базы данных следует также обратить внимание на проектирование модулей приложений, которые будут дополнять базу данных.
Как уже отмечалось выше, на этапе анализа аналитики ИТ-проекта разрабатывают функциональную модель предметной области. Эта модель является входными данными для этапа проектирования модулей приложений базы данных.
Элементы функциональной модели позволяют описать функции обработки данных, организовать их в соответствии с бизнес-требованиями и, следовательно, построить отображения этих функций в набор определений модулей приложений. При этом следует придерживаться следующих двух правил: избегать создания дублирующего кода и стараться не создавать больших модулей со сложной логикой.
Как правило, проектирование модулей приложений выполняется параллельно с проектированием физической структуры базы данных. Эти две задачи взаимосвязаны. Практически любое решение в моделировании данных почти всегда выгодно для одних модулей и создает проблемы для других.
Пример. Предположим, что при поиске одного вида структурированного документа, размещенного в таблице базы данных, используются два идентификатора: внутренний номер организации, генерируемый системой, и аббревиатура организации (краткое наименование). Наличие в базе данных этих колонок есть проектное решение. На экранной форме поиска (модуль приложения) в таких документах используются два раскрывающихся списка. Один список сформирован по номеру организации, а другой - по ее аббревиатуре.
Поддержка двух идентификаторов, фактических определяющих однозначно одну и ту же организацию, создает следующую проблему для модуля ввода документа: при ошибке в наборе аббревиатуры одна и та же организация будет иметь два кратких наименования. Чтобы избежать такой ситуации, в модуле ввода документа должен быть предусмотрен код, который обеспечивает ввод аббревиатур через раскрывающийся список, а в модуле ввода данных об организациях предусмотрено, чтобы поле аббревиатуры было не пусто.
Поэтому проектировщик базы данных должен учитывать последствия выбираемых им решений и выбирать компромиссный вариант.
