Курсоры

Обчислити площу фігури заданої на малюнку у вашому варіанті

Варіант	Завдання	Варіант	Завдання

 

Курсоры

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

 

 

Объявление курсора. При объявлении курсора используется предложение:

CURSOR имя курсора IS Команда SELECT

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

Theatre (thatreName,city, artDirector, cert#)

объявим курсор theatreCursor в виде:

DECLARE

CURSOR theatreCursor IS

SELECT thatreName,city

FROM Theatre;

Или для таблицы продюсеров:

Producers (name, address, cert#, networth)

объявим курсор producersCursor:

CURSOR producersCursor IS

SELECT name, address

FROM Producers

WHERE networth>1000000;

Открытие курсора. Инициализация курсора и установка его в начальное положение выполняется с помощью оператора:

OPEN имя_курсора;

Курсор открывается для выполнения запроса и получения активного набора данных.

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

OPEN producersCursor;

OPEN theatreCursor;

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

Например, создадим курсор для получения названия театров и городов для всех театров, руководителем которых является Гергиев. Отношение для театров:

Theatre (thatreName,city,artDirector,cert#)

Значение текущей строки выбирается в переменные связи. Число переменных связи равно числу столбцов в запросе.

SET SERVEROUTPUT ON

DECLARE

CURSOR theatreCursor IS

SELECT thatreName,city

FROM Theatre

WHERE artDirector ='Valery Gergiev';

vName Theatre.theatreName%Type;

vCity Theatre.city%Type;

i INT:=0;

BEGIN

open theatreCursor;

LOOP

i:=i+1;

FETCH theatreCursor INTO vName,vCity;

DBMS_OUTPUT.enable;

DBMS_OUTPUT.put_line(vName||’ ’||vCity);

EXIT WHEN i>1;

END LOOP;

close theatreCursor;

END;

После завершения работы происходит закрытие курсора: CLOSE имя курсора. Если понадобится возобновить работу с курсором, то можно открыть курсор снова.

Атрибуты курсора. Курсор имеет атрибуты, которые помогают выполнять циклы работы с ним. Значения атрибутов курсора приведены в таблице:

 

Атрибут	Тип	Описание
%ISOPEN	Boolean	Истинно (TRUE), если курсор открыт
%NOTFOUND	Boolean	Истинно (TRUE), если команда FETCH не возвращает строку
%FOUND	Boolean	Истинно (TRUE), если команда FETCH возвращает строку;
%ROWCOUNT	Number	Общее количество строк, выбранных на данный момент

 

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

IF NOT out_cursor %ISOPEN THEN

open out_cursor; END IF;

LOOP

Для выборки нескольких строк из курсора можно организовать цикл. При каждом выполнении цикла извлекается одна строка таблицы базы данных. Проверить успех каждой выборки можно с помощью атрибутов курсора. Атрибут %ROWCOUNT позволяет выбрать точное количество строк.

Момент выхода цикла определяется по атрибуту курсора %NOTFOUND. Создадим курсор для отношения

ActsIn(perfTitle,perfYear,perfTheatre,actorName)

Определим, в каких спектаклях играют артисты Мариинского театра:

SET SERVEROUTPUT ON

DECLARE

vName ActsIn.actorName % TYPE;

vTitle ActsIn.perfTitle % TYPE;

CURSOR TheatreCur IS

SELECT actorName,perfTitle

FROM ActsIn;

WHERE perfTheatre = ‘Mariinsky’;

BEGIN

OPEN TheatreCur;

LOOP

FETCH TheatreCur INTO vName,vTitle;

EXIT WHEN

TheatreCur %ROWCOUNT >10 OR TheatreCur %NOTFOUND;

DBMS_OUTPUT.enable;

DBMS_OUTPUT.put_line(vName||''||vTitle);

END LOOP;

CLOSE TheatreCur;

END;

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

Например, создадим курсор для работы с таблицей спектаклей:

Performances(title,year,runningTime,type, theatreName,composer,producerC#)

Курсор предназначен для выбора названия и года постановки спектаклей на музыку Верди:

DECLARE

CURSOR perfCursor IS

SELECT title,year

FROM Performances

WHERE composer= ‘Diuseppe Verdy’;

perfRecord perfCursor%ROWTYPE;

BEGIN

OPEN perfCursor;

LOOP

FETCH perfCursor INTO perfRecord;

EXIT WHEN perfCursor %NOTFOUND;

DBMS_OUTPUT.enable;

DBMS_OUTPUT.put_line(

perfCursor.title||''|| perfCursor.year);

END LOOP;

CLOSE perfCursor;

END;

Следующий курсор предназначен для выбора строк из таблицы продюсеров:

Producers(name,address,cert#,networth)

Курсор возвращает все атрибуты продюсеров с годовым доходом более $1 000 000используя для этого цикл FOR:

SET SERVEROUTPUT ON

DECLARE

CURSOR richCursor IS

SELECT *

FROM Producers

WHERE networth > 1 000 000;

richRecord richCursor%ROWTYPE;

BEGIN

FOR richRecord IN richCursor LOOP

FETCH richCursor INTO richRecord;

DBMS_OUTPUT.enable;

DBMS_OUTPUT.put_line(richRecord.name||’ ‘

|| richRecord.address||’ ‘|| richRecord.cert#||’ ‘

|| richRecord.networth);

END LOOP;

END;

Курсор с параметрами. Следующий курсор предназначен для получения информации о названии театра, города и имени директора. Курсор пробегает по таблице театров:

Theatre(thatreName,city,artDirector,cert#)

Курсор имеет параметры и реализует динамический SQL. Номер сертификата директора театра задается динамически и передается через параметр курсора. Курсор использует тип записи и цикл FOR для получения данных:

SET SERVEROUTPUT ON

DECLARE

CURSOR dirCursor(pTheatreName CHAR,pCity CHAR, pArtDirector CHAR, pCert# int)

IS

SELECT thatreName,city,artDirector

FROM Theater

WHERE cert# = pCert#;

dirRecord dirCursor %ROWTYPE;

BEGIN

OPEN dirCursor (‘ ’,‘ ’,’ ‘,’ ‘,123456);

LOOP

FETCH dirCursor INTO dirRecord;

DBMS_OUTPUT.enable;

DBMS_OUTPUT.put_line(dirRecord.theatreName|| ''||dirRecord.city||’’||dirRecord.artDirector);

EXIT WHEN dirCursor %ROWCOUNT >10;

END LOOP;

close DirCursor;

END;

Курсор с подзапросом. Курсор может использовать подзапрос. Напишем курсор с подзапросом для выбора названия, года постановки и продолжительности всех спектаклей, поставленных режиссером Rollan Petit.

При этом следует обращаться к двум таблицам:

Producers(name,address,cert#,networth)

Performances(title,year,runningTime,type,theatreName, composer,producerC#)

Для получения данных из курсора использован цикл FOR.

SET SERVEROUTPUT ON

DECLARE

CURSOR performCursor IS

SELECT title, year, runningTime

FROM Performances

WHERE producerC# IN

SELECT cert#

FROM Producers

WHERE name=‘Rollan Petit’

performRec performCursor %ROWTYPE;

BEGIN

OPEN performCursor;

LOOP

FETCH performCursor INTO performRec;

EXIT WHEN performCursor %NOTFOUND;

DBMS_OUTPUT.enable;

DBMS_OUTPUT.put_line(performRec.title||'‘

||performRec.year||’’||performRec.runningTime);

END LOOP;

CLOSE performCursor;

END;

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

EXEC SQL DECLARE ActorCursor INSENSITIVE CURSOR FOR.

При объявлении такого курсора изменения отношения Actors, сделанные между открытием и закрытием курсора, не повлияют на кортежи, выбранные с его помощью. Однако на управление доступом СУБД затрачивает много времени.

Можно использовать курсор FOR READ ONLY, для которого запрещены операции UPDATE и DELETE. Такой курсор может пробегать отношение параллельно с нечувствительным курсором без риска изменить отношение.

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

Чтобы предотвратить одновременные изменения кортежей при параллельной работе нескольких функций в многопользовательской базе данных, курсор следует объявить нечувствительным:

DECLARE performCursor INSENSITIVE CURSOR;

Изменения отношений ActsInиPerformancesсделанные между открытием и закрытием курсора, не повлияют на множество выбранных кортежей.