Обработка запросов с использованием курсоров

Что такое курсор? Каждая команда SQL, выполняемая на сервере Oracle, имеет свой курсор. Два типа курсоров: –Неявные курсоры: создаются для всех команд DML и команд SELECT PL/SQL. –Явные курсоры: создаются программистом. Имя присваивает программист. Функции явного курсора Поочередная обработка строк, возвращаемых запросом. Отслеживание текущей обрабатываемой строки. Ручное управление курсорами в блоке PL/SQL.

Управление явными курсорами DECLARE FETCH OPEN CLOSE Создание именованной рабочей области SQL Выявление активного набора строк Загрузка текущей строки в перемен- ные Проверка на наличие строки Возврат к FETCH если строка обнаружена Освобож- дение активного набора строк Нет Да EMPTY?

Управление явными курсорами Указатель Выборка строки из курсора Выборка до тех пор, пока не останется строк Указатель Указатель Cursor Cursor Cursor Открытие курсора

Объявление курсора: синтаксис DECLARE CURSOR cursor_name IS CURSOR cursor_name IS select_statement; select_statement;DECLARE CURSOR cursor_name IS CURSOR cursor_name IS select_statement; select_statement; Не включайте выражение INTO в описание курсора. Объявление курсора: пример DECLARE... v_ord_ids_item.ord_id%TYPE; v_product_ids_item.product_id%TYPE; v_item_totalNUMBER (11,2); CURSOR item_cursor IS SELECTproduct_id, price*quantity FROMs_item WHEREord_id = v_ord_id; BEGIN... DECLARE... v_ord_ids_item.ord_id%TYPE; v_product_ids_item.product_id%TYPE; v_item_totalNUMBER (11,2); CURSOR item_cursor IS SELECTproduct_id, price*quantity FROMs_item WHEREord_id = v_ord_id; BEGIN...

OPEN cursor_name; Открытие курсора: синтаксис Значения текущей строки выбираются в выходные переменные. Включается столько переменных, сколько столбцов в запросе. Последовательность имен переменных должна соответствовать последовательности столбцов. Проверьте, есть ли строки в курсоре. FETCH cursor_name INTO variable1, variable2,...; Выборка данных из курсора: синтаксис

Выборка данных из курсора: пример FETCH item_cursor INTO v_product_id, v_item_total; INTO v_product_id, v_item_total; FETCH item_cursor INTO v_product_id, v_item_total; INTO v_product_id, v_item_total; Закрытие курсора: синтаксис CLOSE cursor_name;

Атрибуты явного курсора Информацию о состоянии курсора можно получить с помощью атрибутов курсора. АтрибутТипОписание %ISOPENBoolean Истинно (TRUE), если курсор открыт. %NOTFOUNDBoolean Истинно (TRUE), если последняя команда FETCH не вернула строку. %FOUNDBoolean Истинно (TRUE), пока последняя команда FETCH возвращает строку. %ROWCOUNTNumberОбщее количество строк, выбранных на данный момент.

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

Выборка строк возможна только при открытом курсоре. Прежде, чем выполнять операцию FETCH, проверьте, открыт ли курсор, с помощью атрибута %ISOPEN. IF item_cursor%ISOPEN THEN FETCH item_cursor INTO v_quantity, v_price; ELSE OPEN item_cursor; END IF; IF item_cursor%ISOPEN THEN FETCH item_cursor INTO v_quantity, v_price; ELSE OPEN item_cursor; END IF; Атрибут %ISOPEN: пример

Атрибуты %NOTFOUND и %ROWCOUNT: пример Выбрать точное количество строк можно с помощью атрибута курсора %ROWCOUNT. Момент выхода из цикла определяется по атрибуту курсора %NOTFOUND. LOOP FETCH item_cursor INTO v_product_id, v_item_total; EXIT WHEN item_cursor%ROWCOUNT > 5 OR item_cursor%NOTFOUND; v_order_total := v_order_total + v_item_total;... END LOOP; LOOP FETCH item_cursor INTO v_product_id, v_item_total; EXIT WHEN item_cursor%ROWCOUNT > 5 OR item_cursor%NOTFOUND; v_order_total := v_order_total + v_item_total;... END LOOP;

Курсоры и записи: пример Строки из активного набора строк удобно обрабатывать, выбирая значения в запись (RECORD) PL/SQL. CURSOR emp_cursor IS SELECTid, salary, start_date, rowid FROMs_emp WHEREdept_id = 41; emp_recordemp_cursor%ROWTYPE; BEGIN OPEN emp_cursor;... FETCH emp_cursor INTO emp_record; CURSOR emp_cursor IS SELECTid, salary, start_date, rowid FROMs_emp WHEREdept_id = 41; emp_recordemp_cursor%ROWTYPE; BEGIN OPEN emp_cursor;... FETCH emp_cursor INTO emp_record;

Курсоры с параметрами: синтаксис Курсоры с параметрами: пример CURSOR cursor_name [(parameter_name datatype,...)] IS IS select_statement; select_statement; CURSOR cursor_name [(parameter_name datatype,...)] IS IS select_statement; select_statement; CURSOR emp_cursor (v_dept NUMBER, v_job VARCHAR2) IS SELECTlast_name, salary, start_date FROMs_emp WHEREdept_id = v_dept AND title = v_job; CURSOR emp_cursor (v_dept NUMBER, v_job VARCHAR2) IS SELECTlast_name, salary, start_date FROMs_emp WHEREdept_id = v_dept AND title = v_job;

Циклы FOR с курсорами: синтаксис Циклы FOR с курсорами: пример FOR record_name IN cursor_name LOOP statement1; statement1; statement2; statement2; END LOOP; FOR record_name IN cursor_name LOOP statement1; statement1; statement2; statement2; END LOOP; FOR item_record IN item_cursor LOOP -- неявное открытие и неявная выборка v_order_total := v_order_total + (item_record.price * item_record.quantity); i := i + 1; product_id_table (i) := item_record.product_id; order_total_table (i) := v_order_total; END LOOP; -- неявное закрытие FOR item_record IN item_cursor LOOP -- неявное открытие и неявная выборка v_order_total := v_order_total + (item_record.price * item_record.quantity); i := i + 1; product_id_table (i) := item_record.product_id; order_total_table (i) := v_order_total; END LOOP; -- неявное закрытие

Предложение WHERE CURRENT OF Требует предварительной блокировки строк с помощью предложения FOR UPDATE в запросе. Используется для ссылки на текущую строку явного курсора. Если используется предложение FOR UPDATE, фиксация транзакций (COMMIT) между выборками из явного курсора не допускается. SELECT...FROM...FOR UPDATE [OF column-reference][NOWAIT]... CURSOR emp_cursor IS SELECT... FOR UPDATE; BEGIN... FOR emp_record IN emp_cursor LOOP UPDATE... WHERE CURRENT OF emp_cursor;... END LOOP; COMMIT; END;... CURSOR emp_cursor IS SELECT... FOR UPDATE; BEGIN... FOR emp_record IN emp_cursor LOOP UPDATE... WHERE CURRENT OF emp_cursor;... END LOOP; COMMIT; END;

Курсорная переменная

Курсорная переменная (cursor variable) может быть связана с различными операторами во время выполнения программы. Курсорные переменные аналогичны переменным PL/SQL, в которых могут содержаться различные значения. Понятие курсорной переменной

Объявление курсорной переменной TYPE имя_типа IS REF CURSOR [RETURN возвращаемый_тип]; Курсорные переменные имеют ссылочный тип. С помощью та кого типа можно именовать области хранения данных во время выполнения программы. Чтобы воспользоваться ссылочным типом, необходимо сначала объявить переменную, а затем выделить область памяти. имя_типа это имя нового ссылочного типа, а возвращаемый_тип - тип записи, указывающий типы списка выбора, которые в итоге будут возвращаться курсорной переменной.

Пример объявления курсорной переменной DECLARE - Описание при помощи %ROWTYPE TYPE t_StudentsRef IS REF CURSOR RETURN students%ROWTYPE; - Определяем новый тип записи, TYPE t_NameRecord IS RECORD ( first_name students.first_name%TYPE, last_name students. last_name%TYPE); - переменную этого типа v_NameRecord t_NameRecord; - и курсорную переменную, использующую этот тип записи. TYPE t_NamesRef IS REF CURSOR RETURN t_NameRecord; - При помощи %TYPE можно объявить еще один тип. TYPE t_NamesRef2 IS REF CURSOR RETURN t_NameRecord%TYPE; - Объявим курсорные переменные. v_StudentCV t_StudefitsRef; v_NameCV t_NamesRef; DECLARE - Описание при помощи %ROWTYPE TYPE t_StudentsRef IS REF CURSOR RETURN students%ROWTYPE; - Определяем новый тип записи, TYPE t_NameRecord IS RECORD ( first_name students.first_name%TYPE, last_name students. last_name%TYPE); - переменную этого типа v_NameRecord t_NameRecord; - и курсорную переменную, использующую этот тип записи. TYPE t_NamesRef IS REF CURSOR RETURN t_NameRecord; - При помощи %TYPE можно объявить еще один тип. TYPE t_NamesRef2 IS REF CURSOR RETURN t_NameRecord%TYPE; - Объявим курсорные переменные. v_StudentCV t_StudefitsRef; v_NameCV t_NamesRef;

Ограниченные и неограниченные курсорные переменные Ограниченные курсорные переменные (constrained) объявляются только для конкретного возвращаемого типа. Переменная должна открываться для такого запроса, список выбора которого соответствует типу, возвращаемому курсором. В противном случае возникает предопределенная исключительная ситуация ROWTYPE_MISMATCH. Для неограниченных курсорных переменных (unconstrained) предложение RETURN отсутствует. Такая переменная может быть открыта для любого запроса. DECLARE - Определим неограниченный ссылочный тип TYPE t_FlexibleRef IS REF CURSOR; - и переменную этого типа. v_CursorVar t_FlexibleRef; DECLARE - Определим неограниченный ссылочный тип TYPE t_FlexibleRef IS REF CURSOR; - и переменную этого типа. v_CursorVar t_FlexibleRef;

Открытие курсорной переменной для запроса OPEN курсорная_переменная FOR onepamop_select; где курсорная_переменная это ранее объявленная курсорная переменная, a onepamop_sekct требуемый запрос. После выполнения OPEN...FOR можно считывать информацию из курсорной переменной.

Использование курсорной переменной. Пример. PROCEDURE SEL_TEST ( in_last_name IN VARCHAR2 DEFAULT NULL, in_date_birth IN VARCHAR2 DEFAULT NULL ) IS TYPE ref_cursor IS REF CURSOR; v_sql_stmt VARCHAR2 (4000); v_comma CHAR (1) := NULL; out_cur ref_cursor; BEGIN v_sql_stmt := 'select * FROM PERSON '; IF (in_last_name IS NOT NULL) or (in_last_name IS NOT NULL) THEN v_sql_stmt := v_sql_stmt||'WHERE'; END IF; IF in_last_name IS NOT NULL THEN v_sql_stmt := v_sql_stmt || v_comma || ' last_name=''' || in_last_name || ''''; v_comma := and '; END IF; PROCEDURE SEL_TEST ( in_last_name IN VARCHAR2 DEFAULT NULL, in_date_birth IN VARCHAR2 DEFAULT NULL ) IS TYPE ref_cursor IS REF CURSOR; v_sql_stmt VARCHAR2 (4000); v_comma CHAR (1) := NULL; out_cur ref_cursor; BEGIN v_sql_stmt := 'select * FROM PERSON '; IF (in_last_name IS NOT NULL) or (in_last_name IS NOT NULL) THEN v_sql_stmt := v_sql_stmt||'WHERE'; END IF; IF in_last_name IS NOT NULL THEN v_sql_stmt := v_sql_stmt || v_comma || ' last_name=''' || in_last_name || ''''; v_comma := and '; END IF;

Использование курсорной переменной. Пример. (Продолжение) (Продолжение) IF in_date_birth IS NOT NULL THEN v_sql_stmt := v_sql_stmt || v_comma || ' date_birth=to_date(''' || in_date_birth || ''',''DD-MM-YYYY'')'; v_comma := ','; END IF; OPEN out_cur FOR v_sql_stmt; …. END; IF in_date_birth IS NOT NULL THEN v_sql_stmt := v_sql_stmt || v_comma || ' date_birth=to_date(''' || in_date_birth || ''',''DD-MM-YYYY'')'; v_comma := ','; END IF; OPEN out_cur FOR v_sql_stmt; …. END;

Ограничения на использование курсорных переменных Ограничения на использование курсорных переменных Курсорные переменные нельзя объявлять в модуле. Сам тип можно, но переменную нельзя. Удаленные подпрограммы не могут возвращать значение курсорной переменной. Курсорные переменные могут передаваться между клиентской и серверной стороной PL/SQL (например, из клиента Oracle Form), но не между двумя серверами. Сборные конструкции PL/SQL (индексные таблицы, вложенные таблицы и изменяемые массивы) не могут хранить курсорные переменные. Аналогично, таблицы и представления базы данных не могут хранить столбцы REF CURSOR. Запрос, связанный с курсорной переменной в операторе OPEN...FOR, не может быть FOR UPDATE. Это ограничение снято в Oracle8i и выше.

ЗаданиеЗадание 1. Создайте процедуру TOP_DOGS1 для определения самых высокооплачиваемых служащих a.Для этого упражнения создайте новую таблицу с данными о служащих и их заработной плате. b.Включите параметр, чтобы пользователь мог ввести нужное количество самых высокооплачиваемых служащих (n). c.Создайте цикл FOR с курсором для выборки из таблицы S_EMP фамилий и заработной платы n самых высокооплачиваемых служащих. d.Сохраните фамилию и заработную плату в таблице TOP_DOGS. e.Предполагается что двух служащих с одинаковой заработной платой не существует. f.Проверьте особые случаи – например, с n=0 и с n, превышающим количество служащих в таблице S_EMP. g.После каждого теста удаляйте данные из таблицы TOP_DOGS.

ЗаданиеЗадание 2. Создайте хранимую процедуру ADD_STARS, которая в новом столбце STARS проставляет для каждого служащего по призовой звёздочке за каждый процент заработанных комиссионных. Используйте курсор и цикл WHILE. a.Для этого упражнения создайте в таблице S_EMP новый столбец для хранения звёздочек (*) b.Определите процент комиссионных для каждого служащего, округлив его до ближайшего целого числа. Рассмотрите случай, когда служащий не получает комиссионных. c.Добавляйте звёздочку в строку звёздочек за каждый процент комиссионных. Если, например, служащий получает 10 процентов комиссионных, символьная строка в столбце STARS должна содержать десять звёздочек. d.Проставьте соответствующее количество звёздочек для каждого служащего в столбце STARS.

ЗаданиеЗадание 3. Скопируйте процедуру TOP_DOGS1 из упражнения 1 и назовите новый вариант TOP_DOGS2. Измените процедуру TOP_DOGS2 с учётом случая, когда несколько служащих из упражнения 1 имеют одинаковую заработную плату. Для каждой фамилии в списке должны быть перечислены все служащие с такой же заработной платой. Выполните процедуру TOP_DOGS2. В качестве n введите число 6, 7 или 8. В этом случае в выходных данных должны появиться фамилии Ngao, Dumas и Quick-To_See. Если же n равно 9, 10 или 11, должны появиться фамилии Nagayama, Magee и Maduro. Не забывайте полностью удалять данные из таблицы TOP_DOGS после каждого теста.

ЗаданиеЗадание 4. Напишите процедуру для печати фамилии служащих, чья заработная плата лежит в диапазоне плюс минус $100 от введённого значения. a.Если служащего с такой заработной платой нет, пользователь должен получить соответствующее сообщение. Используйте исключение. b.Если служащих с такой зарплатой более 3, сообщение должно указывать, сколько сотрудников попадёт в этот диапазон зарплат. 5.Создайте процедуру для определения: a.Сотрудников, работающих больше заданного числа лет. b.Для этих сотрудников определите менеджеров. Выведите без повторений полученный список менеджеров с указанием рядом с каждым именем через запятую имён сотрудников из начального списка. c.Определите и выведите на экран менеджеров, принявших суммарные заказы более заданной суммы.

ЗаданиеЗадание 6. Напишите процедуру, определяющую рейтинг лучших товаров по: суммарной цене приобретённого за указанный срок товара; количеству единиц приобретённого за указанный срок товара; количеству заказов, в которые входил приобретённого за указанный срок товар. Т.е. первым выдаётся на печать товар, у которого максимальный показатель по выбранному критерию, затем товар с более низким показателем и т.д. Какой из критериев использовать определяется по входным параметрам. Если указано несколько критериев, то их приоритет соответствует списку, указанному выше.

ЗаданиеЗадание 7. Скопируйте таблицу s_emp в s_emp_copy. Для s_emp_copy напишите пакет функций: Вставка строки в таблицу. Если отдел не введён то сотрудник заносится в самый малочисленный отдел. Выбор строки. Если указан id, то выводится строка с указанным ключом. Если нет, то происходит поиск по фамилии, имени, дате начала работы, году начала работы, отделу и заработной плате. Удаление. Если указана id, то удаляется строка с указанным ключом. Если нет, то происходит поиск по фамилии, имени, дате начала работы, году начала работы, отделу и заработной плате. Разработать структуру логирования информации для таблицы s_emp_copy с возможностью восстановления данных за нужный период.

ЗаданиеЗадание 8. Напишите процедуру, осуществляющую поиск в зависимости от входных параметров: 1. Указан интервал времени. Поиск заказчиков у которых время, прошедшее от заказа до получения товара больше чем указанный интервал. 2.Наименование товара. Список заказчиков, закупавших товар с таким наименованием (при этом не поиск должен работать независимо от указанного регистра или числа пробелов). 3.Сумма. Заказчиков, приобретших товар на сумму более указанной. Добавить в качестве входного параметра флаг. В первом случае он позволяет выбирать заказчиков по условиям независимо. Выбранных заказчиков без повторения поместить в специальную таблицу и в отдельном столбце в ней ставить столько плюсиков, сколько пунктов для заказчика истинно. Второе значение флага позволяет выбирать заказчиков удовлетворяющих всем указанным пунктам.