Ограничения при создании табличных триггеров

Триггеры, разработанные для таблицы, не могут использовать следующие команды: COMMIT, ROLLBACK, SAVEPOINT, SET TRANSATION. Процедуры и функции, которые вызываются в триггере, также не должны содержать внутри себя указанные команды.

В триггерах запрещено объявлять переменные типа LONG или LONG ROW. Следующие ограничения связаны с понятиями «мутирующей таблицы» и таблицы с ограничениями целостности типа PRIMARY KEY, UNIQUE, FOREIGN KEY.

Мутирующей является таблица, которая в данный момент модифицируется одной из DML команд (INSERT, DELETE или UPDATE). Таблица, которая может модифицироваться за счет каскадного ограничения целостности типа DELETE CASCADE, также является мутирующей.

В триггере типа «FOR EACH ROW» нельзя ни читать, ни изменять данные мутирующей таблицы внутри триггера. Эта ошибка выявляется только во время выполнения триггера, а не на этапе компиляции. Исключением является только триггеры типа BEFORE INSERT и AFTER INSERT, которые позволяют считывать и изменять данные с помощью корреляционных переменных:new и:old при условии добавления единственной строки.

В теле триггера запрещено изменять любые ключи (primary, unique, foreign) таблиц, поддерживающих ограничения ссылочной целостности. Другие поля данных таблиц могут быть модифицированы триггером.

 

Оборудование

персональный компьютер с установленной операционной системой Windows XP/7/8, браузер (Например, InternetExplorer, GoogleChrome, Opera), СУБД PostgreSQL.

 

Задание на работу

1. Создайте: триггер для INSERT, триггер для DELETE, триггер для UPDATE.

Один из них должен быть BEFORE, один – AFTER, один – INSTEAD OF. Два из них должны быть определены уровне строк (FOR EACH ROW), один – на уровне оператора.

Какой-то из триггеров должен проверять возможность действия (вызывать исключение в зависимости от определенных условий), какой-то – модифицировать ту же таблицу, для которой создается триггер, оставшийся должен быть определен ДЛЯ ПРЕДСТАВЛЕНИЯ и модифицировать таблицу, на базе которой построено это представление.

В каком-то из триггеров должна использоваться псевдострокаnew, в каком-то – псевдострокаold.

2. Приведите:

- исходное содержимое таблиц

- тексты триггеров

- формулировки того, что делает триггер

- операторы, проверяющие действия триггеров

- содержимое таблиц после этих операторов, с выделением (шрифтом или цветом) того, что изменилось.

 

7. Контрольные вопросы

1. Дана таблица Книга. Разработать триггер, который выполняется вместо изменения цены одной книги в этой таблице. Изменение цены выполнить только в том случае, если книга издается в издательстве 'Мир'.

CREATE TRIGGER trig_upd

ON Книга INSTEAD OF UPDATE 

AS

Напишите операторы для продолжения текста триггера.

2. Даны таблицы Город и Разговор. Пусть они не связаны внешним ключом. Для обеспечения целостности данных создать триггер, обрабатывающий удаление информации о некотором городе из таблицы Город. При этом в таблице Разговор необходимо также удалить записи обо всех телефонных разговорах с удаленным городом.

CREATE TRIGGER trig_del ON Город FOR DELETE 

AS

Напишите операторы для продолжения текста триггера.

3. Даны таблицы Рейс и Билет. Пусть они не связаны внешним ключом. Для обеспечения целостности данных создать триггер, обрабатывающий удаление записи о некотором рейсе из таблицы Рейс. При этом в таблице Билет необходимо его номер заменить на номер запасного рейса 111 для билетов, проданных на удаленный рейс.

CREATE TRIGGER trig_del ON Рейс FOR DELETE 

AS

Напишите операторы для продолжения текста триггера.

4. Даны таблицы Книга и Автор. Пусть они не связаны внешним ключом. Для обеспечения целостности данных создать триггер, обрабатывающий удаление информации о некотором авторе из таблицы Автор. При этом необходимо запретить удаление, если общий тираж упомянутого автора больше 1000 экземпляров..

CREATE TRIGGER trig_del ON Автор FOR DELETE 

AS

Напишите операторы для продолжения текста триггера.

5. Дана таблица Разговор. Разработать триггер, который выполняется вместо изменения продолжительности одного разговора. Выполнить изменение продолжительности только в том случае, если общая продолжительность телефонных соединений абонента, в разговоре которого меняется продолжительность, не превышает 300 мин.

CREATE TRIGGER trig_upd

ON Разговор INSTEAD OF UPDATE 

AS

Напишите операторы для продолжения текста триггера.

6. Дана таблица Билет. Разработать триггер, который выполняется вместо изменения стоимости одного билета. Изменение стоимости билета выполнить только в том случае, если на этот рейс продано не менее 20 билетов.

CREATE TRIGGER trig_upd

ON Билет INSTEAD OF UPDATE 

AS

Напишите операторы для продолжения текста. 

Лабораторная работа №11

Хранимые процедуры

Цель и задачи работы

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

 

Порядок выполнения работы

- ознакомится с теоретическими сведениями;

- выполнить задание;

- оформить отчет;

- ответить на контрольные вопросы, заданные преподавателем.

 

Оформление отчета

Отчет должен содержать: титульный лист, цель работы, описание пунктов выполнения лабораторной работы в соответствии с заданием, ответы на контрольные вопросы и выводы по работе.

 

Теоретические сведения

PL/SQL – это язык, структурированный блоками. Это значит, что основные единицы (процедуры, функции и анонимные блоки), составляющие программу PL/SQL, являются логическими БЛОКАМИ, которые могут содержать любое число вложенных в них подблоков.

Структура блока имеет вид: [DECLARE -- описание переменных, констант и пользовательских типов данных] BEGIN -- операторы SQL -- управляющие операторы PL / SQL [EXCEPTION -- действия, выполняемые при возникновении ошибки] END; Программы PL/SQL могут быть неименованными (анонимными блоками), но чаще всего используются именованные программы: процедуры, функции, пакеты и триггеры.

Циклы

Простой цикл. Синтаксис:

LOOP оператор 1;...EXIT [WHEN условие ];

END LOOP;

Простой цикл бесконечен. Завершить цикл можно оператором EXIT. Цикл FOR. Синтаксис:

FOR индекс IN[REVERSE] нижняя_граница.верхняя _ граница LOOP оператор 1;

...

ENDLOOP; индекс – неявно описанная целая переменная, значение которой автоматически увеличивается или уменьшается на 1 при каждом выполнении цикла, пока не будет достигнута верхняя граница. Объявлять индекс не нужно!!! REVERSE – вызывает уменьшение индекса.

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

FOR ctr IN 1..10 LOOP

...

IF NOT finished THEN

INSERT INTO... VALUES (ctr,...); -- законноfactor:= ctr * 2; -- законно

...ELSEctr:= 10; -- незаконно

END IF;

ENDLOOP;

Цикл WHILE. Цикл WHILE используется для повторения последовательности операторов в течение всего времени, пока значение условия равно TRUE. Синтаксис:

WHILE условие LOOP оператор 1;...ENDLOOP;Пример:WHILEtotal<= 25000 LOOP

...

SELECT sal INTO salary FROM empWHERE...total:= total + salary;

END LOOP;

Циклы могут быть вложены один в другой на несколько уровней.

Управление курсорами 

Явные курсоры

Множество строк, возвращаемых запросом (активное множество), может состоять из нуля, одной или нескольких строк, в зависимости от того, сколько строк удовлетворяют вашим поисковым условиям. Когда запрос возвращает несколько строк, вы можете явно определить курсор для обработки этих строк.

Вы определяете курсор в декларативной части блока PL/SQL, подпрограммы или пакета путем задания его имени и специфицирования запроса. После этого вы манипулируете курсором при помощи трех команд: OPEN, FETCH и CLOSE.

Прежде всего вы инициализируете курсор предложением OPEN, которое идентифицирует активное множество. Затем с помощью предложения FETCH вы извлекаете первую строку. Вы можете повторять FETCH неоднократно, пока не будут извлечены все строки. После обработки последней строки вы освобождаете курсор предложением CLOSE.

Вы можете обрабатывать параллельно несколько запросов, объявив и открыв несколько курсоров.

Объявление курсора

Ссылки в перед недопустимы в PL/SQL. Поэтому вы должны объявить курсор, прежде чем сможете ссылаться на него в других предложениях. Объявляя курсор, вы даете ему имя и ассоциируете его с конкретным запросом. В следующем примере объявляется курсор с именем c1:

DECLARE

CURSORc 1ISSELECTename,deptnoFROMempWHEREsal> 2000;

...

BEGIN...

Имя курсора - это необъявленный ранее идентификатор, а не переменная PL/SQL; его можно использовать только для обращения к запросу. Вы не можете присваивать значений имени курсора или использовать его в выражениях.

Параметризованные курсоры

Курсоры могут принимать параметры, как показывает следующий пример. Параметр курсора может появляться в запросе всюду, где допускается появление константы.

CURSOR c1 (median IN NUMBER) IS

SELECT job, ename FROM emp WHER sal> median;

Для объявления формальных параметров курсора используется синтаксис:

CURSOR имя[ (параметр [, параметр,...]) ] IS

SELECT...

гдепараметр, в свою очередь, имеет следующий синтаксис:имя_переменной [IN] тип_данных [{:= | DEFAULT} значение]

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

DECLARE

CURSOR c1

(low INTEGER DEFAULT 0,high INTEGER DEFAULT 99) IS SELECT...

...

Сфера параметров курсора локальна в этом курсоре, что означает, что к этим параметрам можно обращаться лишь в запросе, который участвует в объявлении курсора. Значения параметров курсора используются ассоциированным запросом в момент открытия курсора.

Открытие курсора

Открытие курсора предложением OPEN исполняет предложение SELECT и идентифицирует АКТИВНОЕ МНОЖЕСТВО, т.е. все строки, удовлетворяющие поисковым условиям запроса. Для курсоров, объявленных с фразой FOR UPDATE, предложение OPEN также осуществляет блокировку этих строк. Пример предложения OPEN:

OPEN c1;

Предложение OPEN не извлекает строк активного множества. Для этого используется предложение FETCH.

Передача параметров

Курсору могут быть переданы параметры при открытии. Например, при объявлении курсора

CURSOR c1 (my_ename CHAR, my_commNUMBER) IS SELECT...

любое из следующих предложений открывает этот курсор:

OPEN c1('ATTLEY', 300);

OPENc1(employee_name, 150);

OPEN c1('THURSTON', my_comm);

В последнем примере переменная, специфицированная в предложении OPEN, имеет такое же имя, что и параметр в объявлении курсора.

Когда идентификатор my_comm используется в объявлении курсора, он обозначает формальный параметр курсора. Когда этот же идентификатор используется вне объявления курсора, он обозначает переменную PL/SQL с этим именем. Однако, для ясности, рекомендуется использовать уникальные идентификаторы.

Если вы не хотите принять умалчиваемые значения, каждому формальному параметру в объявлении курсора следует сопоставить соответствующий фактический параметр в предложении OPEN.

Формальные параметры, объявленные с умалчиваемым значением, могут и не иметь соответствующих им фактических параметров. В этом случае они просто принимают свое умалчиваемое значение во время выполнения OPEN. Не забывайте, что формальные параметры курсора должны быть параметрами IN, так что они не могут возвращать значений фактическим параметрам.

Извлечение данных из курсора

Предложение FETCH извлекает очередную строку из активного множества. При каждом выполнении FETCH курсор продвигается к следующей строке в активном множестве. Пример предложения FETCH:

FETCHc1 INTO my_empno, my_ename, my_deptno;

Для каждого значения столбца, извлекаемого запросом, ассоциированного с курсором, в списке INTO должна быть соответствующая переменная, имеющая совместимый с этим столбцом тип данных. Обычно вы используете неиндивидуальные переменные, а запись:

...

OPEN c1;

LOOP

FETCH c1 INTO my_record;

EXITWHENc 1%NOTFOUND;

-- обработатьизвлеченные данные

END LOOP;

Любые переменные в фразе WHERE запроса, ассоциированного с курсором, вычисляются лишь в момент открытия курсора. Как показывает следующий пример, запрос может обращаться к переменным PL/SQL внутри своей сферы:

DECLARE

my_salemp.sal%TYPE; my_jobemp.job%TYPE; factor INTEGER:= 2;

CURSOR c1 IS

SELECT factor*sal FROM empWHERE job = my_job; BEGIN

...

OPEN c1; -- здесь factor равен 2

LOOP

FETCH c1 INTO my_sal;

EXIT WHEN c1%NOTFOUND;

...

factor:= factor + 1; -- не окажет влияния на FETCH

END LOOP;

END;

В этом примере каждое извлекаемое значение sal будет умножаться на 2, несмотря на то, что значение factor увеличивается после каждой операции FETCH. Чтобы изменить активное множество или значения переменных в запросе, вы должны закрыть и заново открыть курсор с новыми значениями входных параметров.

Однако для каждой операции FETCH на одном и том же курсоре вы можете использовать собственный список INTO. Каждая FETCH извлекает строку и присваивает значения своим переменным INTO, как показывает следующий пример: DECLARE

CURSOR c1 IS SELECT ename FROM emp;name1 emp.ename%TYPE;name2 emp.ename%TYPE;name3 emp.ename%TYPE;

BEGIN

OPEN c1;

FETCH c1 INTO name1; --извлекаетпервуюстроку

FETCH c1 INTO name2; -- извлекает вторую строку

FETCH c1 INTO name3; -- извлекает третью строку

...

CLOSEc 1;

END;

Если вы выдаете FETCH, но в активном множестве больше нет строк, то значения переменных в списке INTO не определены.

Закрытие курсора

Предложение CLOSE деактивирует курсор, и активное множество становится неопределенным. Пример предложения CLOSE:

CLOSE c1;

После того, как курсор закрыт, вы можете снова открыть его.

Атрибуты явного курсора

Каждый курсор, явно объявленный вами, имеет четыре атрибута: % NOTFOUND, % FOUND, % ROWCOUNT и % ISOPEN. Атрибуты позволяют вам получать полезную информацию о выполнении многострочного запроса. Для обращения к атрибуту просто присоедините его имя к имени курсора. Атрибуты явного курсора можно использовать в процедурных предложениях, но не в предложениях SQL.

Использование %NOTFOUND

Когда курсор открыт, строки, удовлетворяющие ассоциированному запросу, идентифицированы и образуют активное множество. Эти строки извлекаются операцией FETCH по одной за раз. Если последняя операция FETCH вернула строку, %NOTFOUND дает FALSE.

Если последняя операция FETCH не смогла вернуть строку (так как активное множество исчерпано), %NOTFOUND дает TRUE. Операция FETCH должна в конце концов исчерпать активное множество, так что, когда это происходит, никакого исключения не возбуждается.

В следующем примере вы используете %NOTFOUND, чтобы выйти из цикла, когда FETCH не сможет вернуть строку:

LOOP

FETCH c1 INTO my_ename, my_deptno;EXIT WHEN c1%NOTFOUND;

...

END LOOP;

Перед первой операцией FETCH атрибут % NOTFOUND дает NULL. Поэтому, если FETCH ни разу не выполнится успешно, вы никогда не выйдете из этого цикла. Причина в том, что предложение EXIT WHEN выполняется только в том случае, когда условие WHEN дает TRUE. Поэтому для безопасности вы можете предпочесть такой вариант предложения EXIT:

EXIT WHEN c1%NOTFOUND OR c1%NOTFOUNDIS NULL;

Вы можете открыть несколько курсоров, а затем использовать %NOTFOUND, чтобы проверять, в каких курсорах еще есть строки..

Использование %FOUND

%FOUND логически противоположен атрибуту %NOTFOUND. После открытия явного курсора, но до первой операции FETCH, %FOUND дает NULL. Впоследствии он дает TRUE, если последняя операция FETCH вернула строку, или FALSE, если последняя операция FETCH не смогла извлечь строку, так как больше нет доступных строк.

Следующий пример использует %FOUND, чтобы выбрать одно из двух альтернативных действий:

LOOP

FETCH c1 INTO my_ename, my_deptno;

IF c1%FOUND THEN --извлечение успешно

INSERT INTO temp VALUES (...);

ELSE

EXIT;

...

END LOOP;

Вы можете открыть несколько курсоров, а затем использовать %FOUND, чтобы проверять, в каких курсорах еще есть строки.

Использование %ROWCOUNT

Когда вы открываете курсор, его атрибут %ROWCOUNT обнуляется. Перед первой операцией FETCH %ROWCOUNT возвращает 0.Впоследствии, ROWCOUNT возвращает число строк, извлеченных операциями FETCH из активного множества на данный момент. Это число увеличивается, если последняя FETCH вернула строку.

Следующий пример использует %ROWCOUNT, чтобы предпринять определенные действия, если выбрано более10 строк:

LOOP

FETCH c1 INTO my_ename, my_deptno;

IFc 1%ROWCOUNT > 10THEN--выбрано больше 10 строк

...

END IF;

END LOOP;

Вы можете открыть несколько курсоров, а затем использовать

%ROWCOUNT, чтобы проверять, сколько строк извлечено из каждого курсора.

Использование %ISOPEN

%ISOPEN дает TRUE, если явный курсор открыт, и FALSE в противном случае. Следующий пример использует %ISOPEN для выбора действия:

IF с1%ISOPEN THEN -- курсор открыт

...

ELSE -- курсор закрыт, открыть его

OPENc 1;

ENDIF;

Курсорные циклы FOR

Курсорный цикл FOR неявно объявляет свой индекс цикла как запись типа %ROWTYPE, открывает курсор, в цикле извлекает строки из активного множества в поля записи, и закрывает курсор, когда все строки обработаны или когда вы выходите из цикла.

В большинстве ситуаций, которые требуют явного курсора, текст программы может быть упрощен при использовании "курсора в цикле FOR", заменяющего команды OPEN, FETCH и CLOSE. Курсор в цикле FOR: - неявно объявляет индекс цикла записью, поля которой соответствуют столбцам (псевдонимам) предложения SELECT... из описания курсора; - передает параметры курсора (если они есть) и открывает курсор; - выбирает в цикле строки из полученного набора в индекс цикла (поля записи); – закрывает курсор после обработки всех строк набора или досрочному выходу из него с помощью команд EXIT или GOTO. Синтаксис курсора в цикле FOR имеет вид:

FOR var_rec_name IN cursor_name [(value [,value]...) ] LOOPТЕЛОЦИКЛАEND LOOP,

где – var_rec_name индекс цикла, в котором при первом прохождении цикла хранится первая строка набора, при втором прохождении цикла - вторая строка и т.д.; – список значений ("value") используется для передачи параметров курсора (он заменяет в данном случае список из команды OPEN); - ТЕЛО ЦИКЛА содержит нужные строки повторяющейся части программы, в которых используются переменные с именами var_rec_name.column_name, а column_name имя столбца из перечня столбцов предложения SELECT в описании курсора.

Рассмотрим следующий блок PL/SQL, который анализирует данные, собранные в ходе лабораторных экспериментов, и помещает результаты во временную таблицу. Переменная c1rec, используемая как индекс в курсорном цикле FOR, неявно объявляется как запись, хранящая все элементы данных, возвращаемые одной операцией FETCH для курсора c1. Вы обращаетесь к элементам данных, хранящимся в полях записи, используя квалифицированные ссылки.

DECLARE

result temp.col1%TYPE;

CURSOR c1 IS

SELECT n1, n2, n3 FROM data_table

WHERE exper_num = 1; BEGIN

FOR c1rec IN c1 LOOP

/* вычислить и сохранить результаты */

result:= c 1 rec. n 2 / (c 1 rec. n 1 + c 1 rec. n 3);

INSERT INTO temp VALUES (result, NULL, NULL);

END LOOP;

COMMIT;

END;

То же самое без использования цикла FOR: DECLARE

num1 data_table.n1%TYPE; -- Объявить переменные num2 data_table.n2%TYPE; -- с теми же типами, num3 data_table.n3%TYPE; -- что и столбцы таблицы

result temp.col1%TYPE;

CURSOR c1 IS

SELECT n1, n2, n3 FROMdata_table

WHERE exper_num = 1;

BEGIN

OPEN c1;

LOOP

FETCH c1 INTOnum1, num2, num3;

EXIT WHEN c1%NOTFOUND;

-- условиеc1%NOTFOUNDбудетравноTRUE,

-- когда FETCH необнаружит больше строк

/* вычислить и сохранить результаты */ result:= num 2/(num 1 + num 3);

INSERT INTO temp VALUES (result,NULL, NULL);

END LOOP;

CLOSE c1;

COMMIT;

END;

Процедуры

Процедура – это подпрограмма, которая выполняет специфическое действие. Вы пишете процедуры, используя синтаксис

PROCEDUREимя [ (параметр [, параметр,...]) ] IS

[локальные объявления]BEGIN

исполняемые предложения

[EXCEPTIONобработчики исключений]END [имя];где каждый "параметр" имеет следующий синтаксис:

имя_перем [IN | OUT | IN OUT] тип_данных [{:= | DEFAULT} знач]

Каждый параметр сопровождается режимом и типом. В качестве режима можно задавать: IN (только чтение), OUT (только запись) и INOUT (чтение и запись). Замечание: в описании типа нельзя задавать длину. Например: CHAR(10)

или VARCHAR(20) не допустимые описания, а CHAR или VARCHAR – допустимые. Количество символов (длина) будет определена при вызове процедуры по фактическому параметру (аргументу) функции.

Процедура начинается с ключевых слов CREATE PROCEDURE, за которыми следует ее имя и список параметров. В качестве ключевого слова (описателя) вместо CREATE может использоваться ORREPLACE. Преимущество использования этого ключевого слова в том, что если процедура с каким-то именем уже определена, то новое определение с тем же именем не вызовет ошибки. С другой стороны, предыдущее определение процедуры с аналогичным именем заменится новым определением, и старая процедура перестанет существовать.

Процедура имеет две части: спецификацию и тело. Спецификация процедуры начинается с ключевого слова PROCEDURE и заканчивается именем процедуры или списком параметров. Объявления параметров необязательны. Если процедура не принимает параметров, скобки также не кодируются.

Тело процедуры начинается с ключевого слова IS и заканчивается ключевым словом END, за которым может следовать имя процедуры.

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

Пример. Процедура raise_salary, которая увеличивает жалованье сотрудника:

CREAT OR REPLACE PROCEDUREraise_salary (emp_id NUMBER, increase REAL) IS current_salary REAL; salary_missing EXCEPTION;

BEGIN

SELECT sal INTO current_salaryFROMemp

WHERE empno =emp_id;

IF current_salary IS NULL THEN

RAISE salary_missing;

ELSE

UPDATE emp SET sal = sal +increase

WHERE empno = emp_id;

END IF;

EXCEPTION

WHEN NO_DATA_FOUND THEN

INSERT INTO emp_auditVALUES(emp_id, 'No such number'); WHEN salary_missing THEN

INSERT INTO emp_auditVALUES(emp_id, 'Salary is null');

END raise _ salary;

Функции

Функция – это подпрограмма, которая вычисляет значение. Функции структурируются так же, как и процедуры, с той разницей, что функции содержат фразу RETURN. Вы пишете функции, используя синтаксис

FUNCTIONимя [ (аргумент [, аргумент,...]) ]RETURN тип_данныхIS[локальные_объявления]BEGIN

исполняемые предложения

[EXCEPTIONобработчики исключений]END [имя]; где каждый "аргумент" имеет следующий синтаксис: имя_перем [IN | OUT | IN OUT] тип_данных [{:= | DEFAULT} знач]

В исполняемой части процедуры должно встретиться хотя бы одно предложение RETURN.

Процедура начинается с ключевых слов CREATE PROCEDURE, за которыми следует ее имя и список параметров. В качестве ключевого слова (описателя) вместо CREATE может использоваться ORREPLACE. Преимущество использования этого ключевого слова в том, что если процедура с каким-то именем уже определена, то новое определение с тем же именем не вызовет ошибки. С другой стороны, предыдущее определение процедуры с аналогичным именем заменится новым определением, и старая процедура перестанет существовать.

Пример:

функция balance возвращает баланс заданного бухгалтерского счета:

CREATE OR REPLACE FUNCTION balance(acct_id NUMBER)RETURN REAL ISacct_bal REAL;

BEGIN

SELECT bal INTO acct_bal FROM acctsWHEREacctno = acct_id;

RETURN acct_bal;

END balance;

 

Оборудование

персональный компьютер с установленной операционной системой Windows XP/7/8, браузер (Например, InternetExplorer, GoogleChrome, Opera), СУБД PostgreSQL.

 

Задание на работу

1. Создайте функцию, возвращающую количество записей в таблице по указанному значению внешнего ключа. Входной параметр - значение ключа.

2. Создайте процедуру, изменяющую поле строки. Входные параметры - первичный ключ строки и новое значение изменяемого поля.

3. Создайте новую таблицу TEMP для занесения временных данных. Напишите процедуру (используя явный курсор) для выборки первых пяти записей какой-либо таблицы, соответствующих определенному критерию, и занесения их в таблицу TEMP.

4. Приведите:

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

- оператор, создающий данную процедуру или функцию

- состояние данных до выполнения процедуры или функции

- оператор, запускающий процедуру или функцию

- состояние данных после выполнения процедуры или функции

 

7. Контрольные вопросы

1. В базе данных basa1 имеется таблица Книга. Содержащуюся в ней информацию необходимо прочитать из базы данных basa2. В какой базе данных должна быть создана процедура, и как можно ее выполнить?

2. В базе данных basa1 имеется таблица Город. Из базы данных basa2 необходимо выполнить действие по увеличению тарифов для всех городов на 15%. В какой базе данных должна быть создана процедура и как можно ее выполнить?

3. В базе данных basa1 имеется таблица Блюдо. Из базы данных basa2 необходимо выполнить действие по увеличению стоимости всех блюд на 50%. В какой базе данных должна быть создана процедура и как можно ее выполнить?

4. В базе данных basa1 имеется таблица Рейс. Из базы данных basa2 необходимо выполнить действие по увеличению стоимости билетов на все рейсы на 25%. В какой базе данных должна быть создана процедура и как можно ее выполнить?

5. В базе данных basa1 имеется таблица Книга. Из базы данных basa2 необходимо выполнить действие по увеличению цены всех книг на 10%. В какой базе данных должна быть создана процедура и как можно ее выполнить?

6. В базе данных basa1 имеется таблица Рейс. Содержащуюся в ней информацию необходимо прочитать из базы данных basa2. В какой базе данных должна быть создана процедура, и как можно ее выполнить?

Лабораторная работа №1 2

Индексирование

Цель и задачи работы

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

Порядок выполнения работы

- ознакомится с теоретическими сведениями;

- выполнить задание;

- оформить отчет;

- ответить на контрольные вопросы, заданные преподавателем.

 

Оформление отчета

Отчет должен содержать: титульный лист, цель работы, описание пунктов выполнения лабораторной работы в соответствии с заданием, ответы на контрольные вопросы и выводы по работе.

 

Теоретические сведения

Индексы – это традиционное средство увеличения производительности БД. Используя индекс, сервер баз данных может находить и извлекать нужные строки гораздо быстрее, чем без него. Однако с индексами связана дополнительная нагрузка на СУБД в целом, поэтому применять их следует обдуманно.

Предположим, что у нас есть такая таблица:

CREATE TABLE test1 (id integer, content varchar);

и приложение выполняет много подобных запросов:

SELECT content FROM test1 WHERE id = константа;

Если система не будет заранее подготовлена, ей придётся сканировать всю таблицу test1, строку за строкой, чтобы найти все подходящие записи. Когда таблица test1 содержит большое количество записей, а этот запрос должен вернуть всего несколько (возможно, одну или ноль), такое сканирование, очевидно, неэффективно. Но если создать в системе индекс по полю id, она сможет находить строки гораздо быстрее. Возможно, для этого ей понадобится опуститься всего на несколько уровней в дереве поиска.

Задача программиста баз данных – заранее определить, какие индексы будут полезны.

Создать индекс для столбца id рассмотренной ранее таблицы можно с помощью следующей команды:

CREATE INDEX test1_id_index ON test1 (id);

Имя индекса test1_id_index может быть произвольным, главное, чтобы оно позволяло понять, для чего этот индекс.

Для удаления индекса используется команда DROP INDEX. Добавлять и удалять индексы можно в любое время.

Когда индекс создан, никакие дополнительные действия не требуются: система сама будет обновлять его при изменении данных в таблице и сама будет использовать его в запросах, где, по её мнению, это будет эффективнее, чем сканирование всей таблицы. Возможно, придётся только периодически запускать команду ANALYZE для обновления статистических данных, на основе которых планировщик запросов принимает решения.

Индексы могут быть полезны также при выполнении команд UPDATE и DELETE с условиями поиска. Кроме того, они могут применяться в поиске с соединением. То есть, индекс, определённый для столбца, участвующего в условии соединения, может значительно ускорить запросы с JOIN.

Создание индекса для большой таблицы может занимать много времени. По умолчанию PostgreSQL позволяет параллельно с созданием индекса выполнять чтение (операторы SELECT) таблицы, но операции записи (INSERT, UPDATE и DELETE) блокируются до окончания построения индекса. Для производственной среды это ограничение часто бывает неприемлемым. Хотя есть возможность разрешить запись параллельно с созданием индексов.

После создания индекса система должна поддерживать его в состоянии, соответствующем данным таблицы. С этим связаны неизбежные накладные расходы при изменении данных. Таким образом, индексы, которые используются в запросах редко или вообще никогда, должны быть удалены.

PostgreSQL поддерживает несколько типов индексов: B-дерево, хеш, GiST, SP-GiST, GIN и BRIN. Для разных типов индексов применяются разные алгоритмы, ориентированные на определённые типы запросов. По умолчанию команда CREATE INDEX создаёт индексы типа B-дерево, эффективные в большинстве случаев.

B-деревья могут работать в условиях на равенство и в проверках диапазонов с данными, которые можно отсортировать в некотором порядке. Точнее, планировщик запросов PostgreSQL может задействовать индекс-B-дерево, когда индексируемый столбец участвует в сравнении с одним из следующих операторов:

<

<=

=

>=

>

При обработке конструкций, представимых как сочетание этих операторов, например BETWEEN и IN, так же может выполняться поиск по индексу-B-дереву. Кроме того, такие индексы могут использоваться и в условиях IS NULL и IS NOT NULL по индексированным столбцам.

Также оптимизатор может использовать эти индексы в запросах с операторами сравнения по шаблону LIKE и ~, если этот шаблон определяется константой и он привязан к началу строки – например, col LIKE 'foo%' или col ~ '^foo', но не col LIKE '%bar'. Но если ваша база данных использует не локаль C, для поддержки индексирования запросов с шаблонами вам потребуется создать индекс со специальным классом операторов. Индексы-B-деревья можно использовать и для ILIKE и ~*, но только если шаблон начинается не с алфавитных символов, то есть символов, не подверженных преобразованию регистра.

B-деревья могут также применяться для получения данных, отсортированных по порядку. Это не всегда быстрее простого сканирования и сортировки, но иногда бывает полезно.

Хеш-индексы работают только с простыми условиями равенства. Планировщик запросов может применить хеш-индекс, только если индексируемый столбец участвует в сравнении с оператором =. Создать такой индекс можно следующей командой:

CREATE INDEX имя ON таблица USING HASH (столбец);

Операции с хеш-индексами в настоящее время не проходят через WAL, так что после аварийной остановки базы данных может потребоваться перестроить хеш-индексы командой REINDEX. Кроме того, изменения в хеш-индексах после начальной копии не переносятся при потоковой или файловой репликации, так что в последующих запросах они будут давать неправильные ответы. По этим причинам настоятельно рекомендуется не использовать их.

GiST-индексы представляют собой не просто разновидность индексов, а инфраструктуру, позволяющую реализовать много разных стратегий индексирования. Как следствие, GiST-индексы могут применяться с разными операторами, в зависимости от стратегии индексирования (класса операторов). Например, стандартный дистрибутив PostgreSQL включает классы операторов GiST для нескольких двумерных типов геометрических данных, что позволяет применять индексы в запросах с операторами:

<<

&<

&>

>>

<<|

&<|

|&>

|>>

@>

<@

~=

&&

Классы операторов GiST, включённые в стандартный дистрибутив. В коллекции contrib можно найти и другие классы операторов GiST, реализованные как отдельные проекты.

GiST-индексы также могут оптимизировать поиск «ближайшего соседа», например такой:

SELECT * FROM places ORDER BY location <-> point '(101,456)' LIMIT 10;

который возвращает десять расположений, ближайших к заданной точке. Возможность такого применения индекса опять же зависит от класса используемого оператора.

Индексы SP-GiST, как и GiST, предоставляют инфраструктуру, поддерживающие различные типы поиска. SP-GiST позволяет организовывать на диске самые разные несбалансированные структуры данных, такие как деревья квадрантов, k-мерные и префиксные деревья. Например, стандартный дистрибутив PostgreSQL включает классы операторов SP-GiST для точек в двумерном пространстве, что позволяет применять индексы в запросах с операторами:

<<

>>

~=

<@

<^

>^

Классы операторов SP-GiST, включённые в стандартный дистрибутив. GIN-индексы представляют собой инвертированные индексы, в которых могут содержаться значения с несколькими ключами, например массивы. Подобно GiST и SP-GiST, индексы GIN могут поддерживать различные определённые пользователем стратегии и в зависимости от них могут применяться с разными операторами. Например, стандартный дистрибутив PostgreSQL включает классы операторов GIN для одномерных массивов, что позволяет применять индексы в запросах с операторами:

<@

@>

=

&&

Классы операторов GIN, включённые в стандартный дистрибутив. В коллекции contrib можно найти и другие классы операторов GIN, реализованные как отдельные проекты.

BRIN-индексы (сокращение от Block Range indexes, Индексы зон блоков) хранят обобщённые сведения о значениях, находящихся в физически последовательно расположенных блоках. Подобно GiST, SP-GiST и GIN, индексы BRIN могут поддерживать определённые пользователем стратегии, и в зависимости от них применяться с разными операторами. Для типов данных, имеющих линейный порядок сортировки, записям в индексе соответствуют минимальные и максимальные значения данных в столбце для каждой зоны блоков, что позволяет поддерживать запросы со следующими операторами:

<

<=

=

>=

>

Классы операторов BRIN, включённые в стандартный дистрибутив.

Индексы можно создавать и по нескольким столбцам таблицы. Например, если у вас есть таблица:

CREATE TABLE test2 (major int, minor int, name varchar);

(предположим, что вы поместили в неё содержимое каталога /dev) и вы часто выполняете запросы вида:

SELECT name FROM test2 WHERE major = константа AND minor = константа;

тогда имеет смысл определить индекс, покрывающий оба столбца major и minor. Например:

CREATE INDEX test2_mm_idx ON test2 (major, minor);

В настоящее время составными могут быть только индексы типов B-дерево, GiST, GIN и BRIN. Число столбцов в индексе ограничивается 32 (этот предел можно изменить при компиляции PostgreSQL).

Составной индекс-B-дерево может применяться в условиях с любым подмножеством столбцов индекса, но наиболее эффективен он при ограничениях по ведущим (левым) столбцам. Точное правило состоит в том, что сканируемая область индекса определяется условиями равенства с ведущими столбцами и условиями неравенства с первым столбцом, не участвующим в условии равенства. Ограничения столбцов правее них также проверяются по индексу, так что обращение к таблице откладывается, но на размер сканируемой области индекса это уже не влияет. Например, если есть индекс по столбцам (a, b, c) и условие WHERE a=5 AND b>=42 AND c<77, индекс будет сканироваться от первой записи a=5 и b=42 до последней с a=5. Записи индекса, в которых c >= 77, не будут учитываться, но, тем не менее, будут просканированы. Этот индекс в принципе может использоваться в запросах с ограничениями по b и/или c, без ограничений столбца a, но при этом будет просканирован весь индекс, так что в большинстве случаев планировщик предпочтёт использованию индекса полное сканирование таблицы.

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