Лабораторная работа №2. Хранимые процедуры. Логические операторы. Циклы.

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

Процедура или хранимая процедура (ХП) — это объект БД, который представляет собой подпрограмму, которая написана с помощью языка PL/SQL и предназначена для выполнения задач обработки данных. Как и любой другой объект БД, ХП имеет уникальное имя (в пределах схемы), по которому к ХП могут обращаться пользователи БД. Для создания ХП применяет команда:

CREATE OR REPLACE PROCEDURE имя_процедуры (ПАРАМЕТР1 ВИД_ПАРАМЕТРА ТИП1, ПАРАМЕТР2 ВИД_ПАРАМЕТРА ТИП2)

AS

/* СЕКЦИЯ ОБЪЯВЛЕНИЙ */

BEGIN

/* ВЫПОЛНЯЕМАЯ СЕКЦИЯ */

END;

После имени ХП в круглых скобках идет необязательная часть – список параметров. Каждый параметр отделен друг от друга запятой. Параметры ХП бывают трех видов:

1. IN -входной (нельзя изменить в ХП);

2. OUT -выходной;

3. IN OUT -входной-выходной.

Если вид параметра не указан, то по умолчанию используется вид IN. Выполните самостоятельно в программе следующий запрос:

create or replace procedure test_proc(a in integer) as

begin

insert into aaa values(1);

end.

Выполнить ХП можно несколькими способами:

1. С помощью анонимного блока:

Declare I integer;

Begin

имя_процедуры(значение_параметра1, значение_параметра2);

end.

2. С помощью ключевого слова EXECUTE или EXEC (только в SQL*Plus):

EXECUTE ИМЯ_ПРОЦЕДУРЫ

(значение_параметра1, значение_параметра2).

3. Вызов ХП из выполняемой секции модуля PL/SQL:

имя_процедуры(значение_параметра1, значение_параметра2).

Для удаления ХП из БД применяется команда DROP PROCEDURE:

DROP PROCEDURE имя_процедуры.

В секции объявлений мы можем определить локальные переменные и константы, которые могут потребоваться при реализации алгоритма. Для определения переменной необходимо написать имя новой переменной, пробел и ее тип (один из доступных типов для колонок таблиц), например:

A INTEGER.

Если переменная имеет значение по умолчанию, то это значение можно присвоить прямо в секции объявлений:

A INTEGER:= 5;

B NUMBER(6,2):= 20.2.

Для объявления константы в объявление добавляется ключевое слово CONSTANT:

A CONSTANT INTEGER:= 5.

В отличие от переменных значение константы в секции выполнения изменить невозможно. После определения переменные и константы можно использовать в теле процедуры. Однако время жизни локальных переменных и констант составляет период выполнения ХП. Как только процедура будет выполнена, все локальные переменные и константы будут уничтожены!

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

1. С помощью оператора присваивания “:=”, например, A:= 5;

2. С помощью SQL-оператора SELECT-INTO:

SELECT колонки-значения

INTO переменные FROM ТАБЛИЦА …..;

Тип колонок и типы соответствующих переменных должны совпадать, например:

SELECT MAX(MAX_SALARY), MAX(MIN_SALARY) INTO A,B FROM JOBS.

Локальные переменные и константы с легкостью используются в SQL-запросах:

INSERT INTO AAA VALUES(A).

Операторы языка PL/SQL в выполняемой секции процедуры всегда выполняются последовательно: от BEGIN к END. Как и в любом другом языке программирования, в PL/SQL существуют операторы, которые позволяют изменить последовательность выполнения команд в зависимости от некоторых условий. Рассмотрим некоторые из них.

Оператор IF-THEN-ELSE позволяет в зависимости от условий выполнить разные группы операторов. Границы оператора IF определяются ключевым словом END IF. Для расширения структуры ветвления используется ключевое слово ELSIF. Если такое расширение не требуется, допустимо использовать сокращенный вариант команды - IF-THEN-END IF. В полном виде конструкция условного оператора имеет следующий вид:

IF условие1 THEN

Операторы;

Операторы;

ELSIF условие2 THEN

Операторы;

Операторы;

ELSE

Операторы;

Операторы;

ENDIF.

Рассмотрим пример:

IF (a > 30) THEN

DBMS_OUTPUT.PUT_LINE(1);

ELSIF (a = 30) THEN

DBMS_OUTPUT.PUT_LINE(2);

ELSE

DBMS_OUTPUT.PUT_LINE(3);

END IF.

Циклы позволяют выполнять типовые операции множество раз. Рассмотрим два варианта определения циклов.

Цикл, управляемый оператором FOR - используется в ситуациях, когда заранее точно известно количество итераций. Формат определения цикла следующий:

FOR ПЕРЕМЕННАЯ ЦИКЛА IN НАЧАЛЬНОЕ ЗНАЧЕНИЕ..КОНЕЧНОЕ ЗНАЧЕНИЕ LOOP

Операторы;

Операторы;

END LOOP;

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

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

WHILE условие LOOP

Операторы;

Операторы;

END LOOP;

Рассмотрим пример:

T INTEGER;

I INTEGER;

BEGIN

I:= 0;

T:= I;

WHILE Arg < 100000000 LOOP

Arg:= Arg*(1+1);

I:= I + I;

END LOOP.

Выполните самостоятельно в программе следующие запросы:

1. create or replace procedure sproc(i in integer) as

T integer;

Y number(12,1);

begin

if i > 0 then

for N in 0..I LOOP

T:= I * N;

dbms_output.put_line(T);

end loop;

elsif i = 0 then

dbms_output.put_line('null');

else

Y:= -20;

while Y <> 0 LOOP

dbms_output.put_line(Y);

Y:= Y + 0.5;

end loop;

end if;

end;

2. create or replace procedure DropDepartment(did_o in integer,did_n in integer) as

T integer;

Y number(12,1);

begin

sproc(did_o);

select max(salary) into Y from employees e where e.department_id = did_o;

select EMPLOYEE_ID into T from employees e where (e.department_id = did_o) and (e.salary = Y);

update employees set department_id = did_n where EMPLOYEE_ID = T;

delete from employees where department_id = did_o;

end.

 

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

1. Создайте пару таблиц для хранения парка автомашин. Таблица первая является справочником для хранения моделей машин. Содержит всего два поля: ID (первичный ключ) и MONAME (символьное) для хранения наименования модели. Вторая таблица служит для хранения парка автомашин какой-то автобазы: ID (первичный ключ), MID (внешний ключ к таблице моделей, обязательное для добавления) и поле KOL (количество машин данной модели).

2. Создайте две любые последовательности.

3. Создайте хранимую процедуру, которая бы добавляла модель в справочник моделей и возвращала значение поля ID. Входной параметр – название модели, выходной – значение первичного ключа. В теле процедуры перед добавлением необходимо проверить, есть ли в справочнике модель с таким же именем. Если модель такая уже существует, то возвращается ключ записи модели и выполнение процедуры прекращается. Если модель не существует, то в таблицу моделей добавляется запись, первичный ключ которой генерируется с помощью первой последовательности. Значение сгенерированного ключа возвращается как результат выполнения процедуры. Используйте созданную процедуру для добавления 5 записей.

4. Создайте хранимую процедуру, которая бы добавляла бы запись во вторую таблицу. На входе два параметра: название модели и количество. Для получения внешнего ключа модели из таблицы справочника необходимо использовать предварительно созданную процедуру. Для заполнения первичного ключа второй таблицы необходимо использовать вторую последовательность. Используйте хранимую процедуру для добавления 10 записей.

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

6. Создайте хранимую процедуру, которая бы выводила на экран общее количество автомашин в парке. Входным параметром этой процедуры является максимальное количество мест в автобазе. Если общее количество автомашин в парке превышает значение входного параметра, то на экран надо вывести «Превышено мест в автобазе:» и значение на сколько превышено, в противном случае вывести константу «Свободных мест:» и количество свободных мест.

7. Создайте хранимую процедуру, которая бы удаляла все записи из первой и второй таблицы. Вызовите данную процедуру.

8. Удалите созданные хранимые процедуры, последовательности и таблицы.