Обработка запросов с использованием курсоров. Что такое курсор? Каждая команда SQL, выполняемая на сервере Oracle, имеет свой курсор. Два типа курсоров: - презентация

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

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

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

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

5 Объявление курсора: синтаксис 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...

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

7 Выборка данных из курсора: пример 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;

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

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

10 Выборка строк возможна только при открытом курсоре. Прежде, чем выполнять операцию 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: пример

11 Атрибуты %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;

12 Курсоры и записи: пример Строки из активного набора строк удобно обрабатывать, выбирая значения в запись (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;

13 Курсоры с параметрами: синтаксис Курсоры с параметрами: пример 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;

14 Циклы 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; -- неявное закрытие

15 Предложение 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;

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

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

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

19 Пример объявления курсорной переменной 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;

20 Ограниченные и неограниченные курсорные переменные Ограниченные курсорные переменные (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;

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

22 Использование курсорной переменной. Пример. 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;

23 Использование курсорной переменной. Пример. (Продолжение) (Продолжение) 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;

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

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

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

27 ЗаданиеЗадание 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 после каждого теста.

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

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

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

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