Лабораторная работа №3. Курсоры.

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

Курсоры являются одной из наиболее важных возможностей языка PL/SQL. Название «курсор» означает текущий набор записей. Курсор представляет собой специальный объект PL/SQL, связанный с SQL-запросом. Используя курсор, можно последовательно обращаться к каждой записи, возвращаемой этим запросом. Другими словами, курсор – это поименованный запрос, содержащий некоторое фиксированное количество записей в выборке. Существует три типа курсоров:

1. Неявный курсор (implicit CURSOR);

2. Явный курсор (explicit CURSOR);

3. Курсорный цикл FOR.

Неявный курсор создается автоматически при выполнении любых SQL-запросов (SELECT, UPDATE, INSERT, DELETE). Слово неявный (implicit) говорит о том, что курсоры данного типа нигде в подпрограмме явно не определяются. В процессе выполнения SQL-запроса автоматически курсор открывается, из него выбираются данные, и курсор закрывается. Все эти действия выполняются СУБД за один шаг и не требуют от пользователя каких-либо дополнительных действий. После выполнения SQL-запроса можно обращаться к атрибутам созданного неявного курсора, который по умолчанию принимает имя “SQL”:

create or replace procedure update_aaa(i in integer)

as

begin

update aaa set a = i;

If SQL%ROWCOUNT > 0 then

dbms_output.put_line(SQL%ROWCOUNT || ' records updated');

else

dbms_output.put_line('No records updated!');

end if;

end;

Явные курсоры, что видно из названия, определяются разработчиком в секции объявлений PL/SQL модуля. В упрощенном виде делается это следующим образом:

CURSOR ИМЯ_КУРСОРА (ПАРАМЕТР1 ТИП1, ПАРАМЕТР2 ТИП2) IS ЗАПРОС-SELECT.

Список входных параметров курсора является необязательным. Входные параметры можно использовать в спецификации запроса SELECT для ограничения выборки. Запрос-SELECT формирует НД, составляющий курсор. Рассмотрим несколько примеров объявлений курсоров:

cursor jobc is select * from jobs;

cursor jobc(JD in varchar2) is select JOB_ID from jobs where JOB_ID = JD.

Курсор любого типа имеет ряд показателей, показывающих его состояние. Они называются атрибутами курсора:

1. Имя курсора%ISOPEN. Атрибут позволяет определить, открыт ли курсор. Если курсор открыт, то атрибут имеет значение TRUE, в противном случае - FALSE.

2. Имя курсора%ROWCOUNT. Атрибут возвращает количество строк, считанных из курсора в локальные переменные или переменную курсора;

3. Имя-курсора%FOUND. Атрибут позволяет проверить, была ли успешной последняя попытка получения (чтения) записи из курсора. В случае успешной попытки чтения возвращает TRUE, в противном случае – FALSE.

4. Имя курсора%NOTFOUND. Данный атрибут противоположен по значению атрибуту FOUND. Если записей в курсоре при чтении больше не найдено, то атрибут возвращает TRUE, в противном случае – TRUE.

Для работы с курсором используются три команды:

1. OPEN ИМЯ_КУРСОРА. Открывает курсор, т.е. выполняет запрос курсора. После открытия курсора содержит набор записей.

2. FETCH ИМЯ_КУРСОРА INTO ПЕРЕМЕННАЯ_ИЛИ_СПИСОК_ПЕРЕМЕННЫХ. Удаляет запись из курсора и перемещает ее содержимое либо в переменную курсора того же типа, либо в группу переменных, где каждая переменная совпадает по типу с соответствующим полем SELECT- запроса курсора.

3. CLOSE ИМЯ_КУРСОРА. Закрывает курсор.

Рассмотрим пример использования атрибутов и команд для работы с явными курсорами:

create or replace procedure outdepartment_employees(ADID IN INTEGER)

as

DNAME varchar(40);

D1 varchar(10);

D2 varchar(20);

CURSOR EMPS(i in integer) is select first_name, last_name, salary from employees where department_id = i;

S number(12,1);

Ssum number(12,1):= 0;

Cnt Integer:= 0;

begin

select department_name into dname from departments where department_id = adid;

DBMS_OUTPUT.PUT_LINE('Department = ' || DName);

open emps(adid);

fetch emps into d1, d2, s;

while emps%found loop

dbms_output.put_line(d1 || ' ' || d2 || '(' || s || ')');

fetch emps into d1, d2, s;

Ssum:= Ssum + S;

Cnt:= Cnt + 1;

end loop;

CLOSE EMPS;

DBMS_OUTPUT.PUT_LINE('Employees Count = ' || Cnt);

DBMS_OUTPUT.PUT_LINE('Avarage Salary = ' || Ssum / Cnt);

end.

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

FOR ЗАПИСЬ_КУРСОРА IN ИМЯ_КУРСОРА LOOP

операторы;

END LOOP.

Данный цикл последовательно выбирает записи из курсора в переменную типа ЗАПИСЬ_КУРСОРА. Переменную курсора явно объявлять не требуется. Если все-таки присутствует необходимость явно объявить переменную курсора, то это сделать можно через курсор и его атрибут ROWTYPE:

CURSOR TEST IS SELECT A, B, C FROM TAPD;

CREC TEST%ROWTYPE.

Поля переменной курсора используются для доступа к данным из операторов PL/SQL в операторах тела цикла. Чтобы обратиться к полю курсора, т.е. к полю текущей записи запроса SELECT, то для этого необходимо написать переменную курсора и через точку имя поля, например:

CREC.A;

CREC.B.

Когда все записи SELECT-запроса были считаны, курсорный цикл завершается. Рассмотрим пример работы с курсорным циклом:

create or replace procedure getcountryemployees as

cursor cloc is select country_id, country_name from countries;

cloc_rec cloc%rowtype;

Cnt integer;

begin

for cloc_rec in cloc loop

select count(*) into Cnt from employees e left join departments d on d.department_id = e.department_id left join locations l on d.location_id = l.location_id

where country_id = cloc_rec.country_id;

dbms_output.put_line(cloc_rec.country_name || ' = ' || Cnt || ' employees');

end loop;

end.

 

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

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

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

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

4. Создайте хранимую процедуру, которая бы с помощью цикла выводила список наименований овощей из справочника. Кроме этого по каждому овощу второй колонкой необходимо вывести долю (в процентах) веса конкретного овоща от общей массы всех овощей, т.е. , где - масса i-того овоща, n – количество овощей в таблице.

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

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

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