PL/SQL Курсоры в PL/SQL Неявные курсоры создаются PL/SQL неявно для всех команд DML и SELECT. Явные курсоры объявляются программистом, который присваивает. - презентация

1 PL/SQL

2 Курсоры в PL/SQL Неявные курсоры создаются PL/SQL неявно для всех команд DML и SELECT. Явные курсоры объявляются программистом, который присваивает им имя и может использовать с помощью специальных команд в исполняемой секции блока.

3 Управление явным курсором 1. Объявление курсора. Курсору присваивается имя и определяется запрос, в соответствии с которым он будет выполняться (секция объявления переменных). 2. Открытие курсора. Команда OPEN выполняет запрос и связывает все используемые переменные. Строки, выбранные запросом, называющиеся активным набором, доступны после этого для выборки. 3. Выборка данных. Оператор FETCH загружает текущую строку из курсора в переменные. Каждое выполнение команды FETCH перемещает указатель курсора на следующую строку активного набора. Необходимо организовать проверку на достижение последней строки активного набора - перемещение по активному набору далее последней строки не имеет смысла. 4. Закрытие курсора. Оператор CLOSE освобождает активный набор строк. После этого курсор можно открыть вновь.

4 Объявление курсора CURSOR имя [список параметров] [RETURN возвращаемый тип] IS запрос [FOR UPDATE [OF (список столбцов)][NOWAIT]]

5 CURSOR BY_PLAYERS IS SELECT PLAYER_ID, FML FROM PLAYERS WHERE DOC_ID=1; CURSOR author_cur1(i_id in number) IS SELECT rowid FROM authors WHERE id > i_id;

6 Открытие курсора BEGIN OPEN BY_PLAYERS; OPEN author_cur1(50);... END;

7 Выборка данных из курсора FETCH имя_курсора INTO [переменная 1, переменная 2,...]|[имя_записи] [LIMIT int] Количество переменных в предложении INTO должно совпадать с количеством выходных столбцов запроса, на котором основан курсор, а типы данных должны быть совместимы. Порядок переменных должен соответствовать порядку столбцов. Если команда FETCH не возвращает никаких значений, это означает, что в наборе результатов больше нет строк для обработки. Исключительной ситуации при этом не возникает. Если используется курсорный цикл FOR, и указатель курсора находится в конце набора результатов, то команда FETCH также выходит из цикла.

8 Закрытие курсора CLOSE BY_PLAYERS; Close author_cur1;

9 Пример DECLARE CURSOR BY_PLAYERS SELECT PLAYER_ID, FML FROM PLAYERS WHERE DOC_ID=1 В_PL_ID PLAYERS.PLAYER_ID%TYPE; В_FML PLAYERS.FML%TYPE; BEGIN OPEN BY_PLAYERS; FOR В_ITEM IN LOOP FETCH BY_PLAYERS INTO В_PL_ID, В_FML; -- далее производятся манипуляции со значениями текущей строки курсора цикл закончится, если В_ITEM=10 или строки в активном наборе закончились END LOOP; CLOSE BY_PLAYERS; END;

10 Атрибуты явного курсора Атрибут Описание %ISOPENИстинно, если курсор открыт %NOTFOUND Истинно, если команда FETCH не возвращает строку %FOUND Истинно, если команда последняя FETCH возвращает строку (дополняет %NOTFOUND) %ROWCOUNT Общее количество строк, выбранное на данный момент %BULK_EXCEPTIONS Используется в BULK COLLECT. Дает информацию об исключительных ситуациях во время выполнения %BULK_ROWCOUNT Используется в BULK COLLECT. Дает информацию о числе измененных строк

11 DECLARE CURSOR C_BY_PLAYERS SELECT PLAYER_ID, FML FROM PLAYERS WHERE DOC_ID=1 В_BY_PLAYERS C_BY_PLAYERS%ROWTYPE; BEGIN ….. IF NOT C_BY_PLAYERS%ISOPEN THEN OPEN C_BY_PLAYERS END IF; LOOP FETCH C_BY_PLAYERS INTO В_BY_PLAYERS; EXIT WHEN C_BY_PLAYERS%NOTFOUND OR C_BY_PLAYERS%NOTFOUND IS NULL; …….. END LOOP; CLOSE BY_PLAYERS; END;

12 Использование цикла FOR FOR имя_записи IN имя_курсора LOOP выражение 1;... END LOOP;

13 DECLARE CURSOR C_BY_PLAYERS SELECT PLAYER_ID, FML FROM PLAYERS WHERE DOC_ID=1 В_BY_PLAYERS C_BY_PLAYERS%ROWTYPE; BEGIN …. FOR B_BY_PLAYERS IN C_BY_PLAYERS LOOP …… END LOOP; END

14 FOR B_BY_PLAYERS IN (SELECT PLAYER_ID, FML FROM PLAYERS WHERE DOC_ID=1) LOOP …… END LOOP; END

15 Удаление или изменение строки курсора UPDATE [schema.]{table | [alias] SET { (column [, column]...) = (subquery) | column = { expr | (subquery) } } [, { (column [, column]...) = (subquery) | column = { expr | (subquery) } } ]... WHERE CURRENT OF cursor_name; DELETE [FROM] [schema.]{table | [alias] WHERE CURRENT OF cursor_name;

16 Курсорные переменные DECLARE TYPE book_typ IS REF CURSOR RETURN BOOK%ROWTYPE; Cv_books book_typ; BEGIN OPEN cv_books FOR SELECT * FROM books; CLOSE cv_books; END;

17 Курсорные подзапросы DECLARE 2 3 cv_author SYS_REFCURSOR; 4 v_title BOOKS.TITLE%TYPE; 5 v_author AUTHORS%ROWTYPE; 6 v_counter PLS_INTEGER := 0; 7 8 CURSOR book_cur 9 IS 10 SELECT b.title, 11 CURSOR (SELECT * 12 FROM authors a 13 WHERE a.id = b.author1 14 OR a.id = b.author2 15 OR a.id = b.author3) 16 FROM books b 17 WHERE isbn = ' '; BEGIN DBMS_OUTPUT.ENABLE( ); OPEN book_cur; LOOP 26 FETCH book_cur INTO v_title, cv_author; 27 EXIT WHEN book_cur%NOTFOUND; v_counter := 0; DBMS_OUTPUT.PUT_LINE('Title from the main cursor: '||v_title); LOOP 34 FETCH cv_author INTO v_author; 35 EXIT WHEN cv_author%NOTFOUND; v_counter := v_counter + 1; DBMS_OUTPUT.PUT_LINE('Author'||v_counter||': ' 40 ||v_author.first_name||' ' 41 ||v_author.last_name); 42 END LOOP; 43 END LOOP; CLOSE book_cur; END;

18 Открытые курсоры Максимальное количество определяется в init.ora параметр OPEN_CURSORS Select value from v$parameter Where name = open_cursors;

19 select a.value, s.program, s.username, s.sid, s.serial#, s.client_identifier from v$sesstat a, v$statname b, v$session s where a.statistic# = b.statistic# and s.sid = a.sid and b.name = 'opened cursors current' order by 1 desc;

20 ROWID и ROWNUM Select num, odd, ROW_NUMBER () OVER (PARTITION BY ODD ORDER BY NUM) cumedist From tb1 NumOddCumedist