Линтер методичка

22!

Задача 8. (студенты выполняют самостоятельно) Создать таблицу

AUTO_PLANTS со столбцами ID типа integer, автоинкремент, первичный ключ; и NAME типа char(50), уникальное. Импортировать данные из файла /home/linter/samples/db/dbf/auto.dbf,

занося в столбец NAME данные из столбца MAKE.

E- mail

:

mar ket @re lex.r u

Практическое занятие 4 Создание, исполнение и отладка хранимых процедур и триггеров

Практика 2 часа (Лекция 4)

Целью занятия является освоение способов создания, исполнения и отладки хранимых процедур и триггеров.

4.1. Основные приемы создания, исполнения и отладки на примере хранимой процедуры для генерации значений уникальных ключей

При проектировании баз данных часть в качестве первичного ключа некоторой таблицы используется суррогатный ключ – уникальное числовое значение. Для генерации значений такого ключа удобно использовать механизм последовательностей (sequence). Однако, в СУБД ЛИНТЕР версий младше 5.9 последовательности не поддерживаются. Другим удобным и универсальным способом для генерации уникальных значений может быть использование хранимой процедуры. Хранимая процедура будет обращаться к специальной таблице (назовем ее SEQUENCES), чтобы определить очередное значение.

Таблицу SEQUENCES можно создать следующим запросом:

create table SEQUENCES(

TAB_NAME VARCHAR(64) primary key not null,

);

Здесь колонка TAB_NAME хранит имена таблиц, для которых генерируются уникальные значения, а колонка CUR_VALUE – текущее значение для генерации.

Соответствующая хранимая процедура должна получать на входе имя таблицы, а на выходе выдавать сгенерированное значение. Логика работы процедуры проста: если запись о нужной таблице уже есть в SEQUENCES , надо выдать соответствующее значение и увеличить его на 1, если же записи еще не было, надо добавить новую запись со значением 1.

SQL-операторы на создание таблицы SEQUENCES и хранимой процедуры

GeneratePK содержатся в файле sp_seq.sql. Процедура создается таким запросом:

create procedure GeneratePK(in tabname char(64)) result int

for debug

declare

var v cursor(value int); //

code

execute direct "lock table SEQUENCES wait;"; //

execute direct "update SEQUENCES set CUR_VALUE = CUR_VALUE + 1 where TAB_NAME = '"+

tabname+"';"; // if rowcount() > 0 then

24!

иВыполняем команды:

в

Теперь процедура создана. Запустить ее можем тут же, из inl, например:

SQL>

Return value = 1

SQL> execute generatepk(‘TEST’);

Return value = 2

Мы создали процедуру с опцией FOR DEBUG , что позволяет нам отлаживать ее исполнение.

Для отладки необходимо запустить утилиту spman

(ЛИНТЕР-ВС: в дистрибутиве ЛИНТЕР-ВС утилита для отладки хранимых процедур

итриггеров называется spdebug; отличается она тем, что в ней нет возможности создавать

иEмодифицировать- процедуры).

mail По команде «Открыть процедуру…» в меню «Файл» выбираем нужную процедуру.

Ее: код показывается в отдельном окне. Теперь можно запустить ее из-под отладчика mar

ket @re lex.r u

26!

необходимои описать в секции деклараций процедуры, например перед ключевым словом code:отл

качествепр параметра change . Отладчик всегда останавливается в строке, в которой произошлооце исключение.

дур Если мы запустим процедуру с неверными параметрами из inl, то тоже получим информацию об исключении. Запрос execute, завершившийся с исключением, всегда имеет

создателя процедуры, то при попытке выполнить процедуру (ее имя в запросе execute, кстати, должно будет уже включать имя владельца, отделенной точкой от собственно имени) может возникнуть ошибка доступа к таблице SEQUENCES 2202 (у пользователя скорее всего не будет таблицы с именем SEQUENCES).

(Замечание: конечно, мы могли бы в запросах явно написать SYSTEM.SEQUENCES; это более верно, но тем не менее, может возникнуть ошибка нарушения прав доступа; чтобы процедура реально работала для нескольких пользователей, обычно удобно создать общие (PUBLIC) синонимы на используемые таблицы, или же использовать полное имя таблицы, и в любом случае надо назначить необходимые права

grant execute on GENERATEPK to PUBLIC;).

Если мы подадим запрос на выполнение SYSTEM.GENERATEPK из-под другого пользователя (не SYSTEM), то увидим, что процедура всегда возвращает 1. Чтобы разобраться, в чем дело, можно запустить ожидание процедуры и прийти в режим ее отладки. В отладчике мы видим, что при попытке исполнения запросов происходит исключение 2202 (нет такой таблицы), но процедура продолжает исполняться. Это происходитE- из-за того, что исключение некритичное, и при отсутствии обработки оно просто игнорируетсяmail . Значит, нам необходимо обработать это исключение.

: В секции определений объявим исключение: mar

ket @re lex.r u

менюни «Файл». Предупреждение: в диалоге создания процедуры регистр символов имени

кода завершения запроса drop table удобно использовать стандартную функцию errcode).

Примечаниех а : Студенты пытаются написать процедуру сами…

ни (процедура должна иметь примерно такой вид:

вcommit;

27! endif end;

)

4.5. Создание триггера

Рассмотрим пример триггера, который использует созданную нами ранее хранимую процедуру для генерации значений первичного ключа при вставке записей в некоторую таблицу OBJECT:

create table object(id int primary key, value varchar(64));

E-

Триггер позволяет принудительно установить значение поля ID новой записи, какое

бы значение не задал пользователь в исходном запросе: mail

: create trigger INS_OBJECT before insert on object

mar ket @re lex.r u

29!

E- mail

:

mar ket @re lex.r u

30!

Практическое занятие 5 Разграничение доступа в СУБД ЛИНТЕР. Дискреционный доступ

Практика 2 часа (Лекция 5)

Целью занятия является освоение способов разграничения доступа к базе данных на основе дискреционного доступа. При выполнении задания будет использовано приложение «Интерактивный SQL» (INL).

5.1. Создание нового пользователя. Полномочия пользователей

Различные пользователи являются владельцами данных, которые находятся в созданных ими таблицах. Рассмотрим на следующей задаче.

Задача: Создать двух пользователей A и B, создать от имени пользователя A таблицу для хранения телефонной книги, предоставить пользователю B доступ для чтения и добавления данных.

Решение:

Запускаем утилиту INL, вводим имя и пароль администратора базы данных или пользователя обладающего привилегией администратора базы данных (DBA).

1.Создаем пользователей A и B с паролями ‘1’ и ’2’: create user A identified by ‘1’;

create user B identified by ‘2’;

2.Даем пользователю A привилегию RESOURCE: grant resource to A;

3.Выходим из утилиты INL командой exit и запускаем утилиту повторно с именем пользователя ‘A’ и паролем ‘1':

4.Создаем таблицу для хранения телефонов:

create table PHONES(ID integer autoinc,NAME char(20), PHONE char(12));

5.Передаем привилегии на чтение и вставку пользователю “B’: grant select,insert on PHONES to B;

6.Выходим из утилиты INL командой exit и запускаем утилиту повторно с именем пользователя ‘B’ и паролем ‘2':

7.Осуществляем вставку данных:

insert into A.PHONES(NAME,PHONE) values(‘Иванов’,’8-095-167-34-80’); 8. Осуществляем выборку данных:

select * from A.PHONES;

Задачи для самостоятельной работы:

1.Создать пользователя с привилегией DBA, создать от имени этого пользователя таблицы для хранения почтовых адресов предприятий (название, почтовый индекс, город, улица, дом, а/я). Созданным ранее пользователям A,B предоставить возможность заполнения, модификации, удаления и просмотра данных из этой таблицы. Всем остальным пользователям предоставить возможность только чтения данных из созданной таблицы.

5.2.Использование представлений для разграничения доступа

Для «вертикального» разграничения доступа к таблице используются представления. Рассмотрим это на следующей задаче.