Курсоры базы данных. Лабораторная работа по курсам Базы данных, Системы управления базами данных, Управление данными - файл n1.doc

Курсоры базы данных. Лабораторная работа по курсам Базы данных, Системы управления базами данных, Управление данными
Скачать все файлы (147.5 kb.)

Доступные файлы (1):
n1.doc148kb.12.01.2014 14:35скачать

n1.doc


Государственное образовательное учреждение

Высшего профессионального образования

Санкт-Петербургский государственный политехнический университет

Факультет технической кибернетики

Кафедра измерительных информационных технологий









УТВЕРЖДЕНО
На заседании кафедры ИИТ
Протокол №4
От «__» февраля 2009 г.


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

по курсам

«Базы данных»

«Системы управления базами данных»

«Управление данными»


Курсоры базы данных































Санкт-Петербург

2009


Для программирования курсоров используются условные операторы языка PL и операторы циклов.

Условная логика. Условная логика представлена операторами IF-THEN:

IF условие1 THEN операторы1

ELSIF условие2 THEN операторы2

ELSE операторы3 END IF;

Например, сформировать сообщение о количестве записей и области значений:

IF count>0 THEN Message:=‘count is positive’;

IF area>0 THEN Message:=‘count and area are positive’;

ELSIF count=0 THEN Message:=‘count is zero’

ELSE Message :=‘count is negative’;

END IF;

Простой цикл. Простой цикл выполняется до тех пор, пока не выполняется условие EXIT WHEN. Оператор EXIT WHEN может появляться в любом месте кода цикла

LOOP операторы EXIT WHEN условие END LOOP;

Например, следующий цикл выполняется пять раз:

counter:=0

LOOP

counter:=counter+1;

EXIT WHEN counter = 5;

END LOOP;

Цикл WHILE. Условие окончания цикла WHILE записывается в начале выполнения цикла

WHILE условие LOOP операторы END LOOP;

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

counter:=0;

WHILE counter <6

LOOP counter :=counter+1;

END LOOP;

Цикл FOR. Цикл FOR выполняется заданное число раз

FOR переменная цикла IN (reverse) LOOP операторы END LOOP;

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

FOR count IN 1..5

LOOP

DBMS_OUTPUT.PUT_LINE(count);

END LOOP;

Курсоры

Наиболее гибким методом связи 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 сделанные между открытием и закрытием курсора, не повлияют на множество выбранных кортежей.

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

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

2. Создайте курсор для выбора названия корабля, даты спуска на воду и сражения, в котором он участвовал. Используйте тип данных – записи. Простой цикл для получения данных из курсора. Распечатайте результат запроса

3. Создайте курсор для выбора характеристик кораблей, спусщенных на воду до 1943 года. Используйте тип данных – записи. Цикл For для получения данных из курсора.

4. Создайте курсор с параметрами для выбора названия корабля, сражения и даты для кораблей, которые имели результат сражения, указанный в параметрах курсора. Используйте тип данных – записи. Цикл For для получения данных из курсора.

5. Создайте курсор с подзапросом для выбора названия корабля, класса и страны. Используйте тип данных – записи. Цикл For для получения данных из курсора.


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

SET SERVEROUTPUT ON

DECLARE

v_ship Outcomes.ship%TYPE; v_battle Outcomes.battle%TYPE;

CURSOR out_cursor IS

SELECT ship,battle

FROM Outcomes;

BEGIN

OPEN out_cursor;

LOOP

FETCH out_cursor INTO v_ship,v_battle;

EXIT WHEN out_cursor %ROWCOUNT >10 OR out_cursor %NOTFOUND;

DBMS_OUTPUT.enable; DBMS_OUTPUT.put_line(v_ship||''||v_battle);

END LOOP;

CLOSE out_cursor;

END;
2. Создайте курсор для выбора названия корабля, даты спуска на воду и сражения, в котором он участвовал. Используйте тип данных – записи. Простой цикл для получения данных из курсора. Распечатайте результат запроса

DECLARE

CURSOR ship_cursor IS

SELECT name,class

FROM Ships;

ship_record ship_cursor %ROWTYPE;

BEGIN

OPEN ship_cursor;

LOOP

FETCH ship_cursor INTO ship_record;

EXIT WHEN ship_cursor %NOTFOUND;

DBMS_OUTPUT.enable;

DBMS_OUTPUT.put_line(ship_record.name||''||ship_record.class);

END LOOP;

CLOSE ship_cursor;

END;
3. Создайте курсор для выбора характеристик кораблей, спущенных на воду до 1943 года. Используйте тип данных – записи. Цикл For для получения данных из курсора. Распечатайте результат запроса

SET SERVEROUTPUT ON

DECLARE

CURSOR out_cursor IS

SELECT *

FROM Outcomes;

out_record out_cursor %ROWTYPE;

BEGIN

FOR out_record IN out_cursor LOOP

FETCH out_cursor INTO out_record;

DBMS_OUTPUT.enable;

DBMS_OUTPUT.put_line(out_record.ship||' '||out_record.battle);

END LOOP;

END;
4. Создайте курсор с параметрами для выбора названия корабля, сражения и даты для кораблей, которые имели результат сражения, указанный в параметрах курсора. Используйте тип данных – записи. Цикл For для получения данных из курсора. Распечатайте результат запроса

SET SERVEROUTPUT ON

DECLARE

CURSOR out_cursor(p_name CHAR,p_battle CHAR,p_result CHAR) IS

SELECT ship,battle,result

FROM outcomes

WHERE result=p_result;

out_record out_cursor %ROWTYPE;

BEGIN

OPEN out_cursor ('','','ok');

LOOP

FETCH out_cursor INTO out_record;

DBMS_OUTPUT.enable;

DBMS_OUTPUT.put_line(out_record.ship||''||out_record.battle);

EXIT when out_cursor %ROWCOUNT >10;

END LOOP;

close out_cursor;

END;
5. Создайте курсор с подзапросом для выбора названия корабля, класса и страны. Используйте тип данных – записи. Цикл For для получения данных из курсора. Распечатайте результат запроса

SET SERVEROUTPUT ON

DECLARE

CURSOR cl_cursor IS

SELECT class,country

FROM classes

WHERE class IN

(SELECT class

FROM ships);

cl_record cl_cursor %ROWTYPE;

BEGIN

OPEN cl_cursor;

LOOP

FETCH cl_cursor INTO cl_record;

EXIT WHEN cl_cursor %ROWCOUNT>1;

DBMS_OUTPUT.enable;

DBMS_OUTPUT.put_line(cl_record.class||''||cl_record.country);

END LOOP;

CLOSE cl_cursor;

END;





Учебный текст
© perviydoc.ru
При копировании укажите ссылку.
обратиться к администрации