Слайд 2
![Курсоры Курсор — объект БД, который позволяет приложениям работать с](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/3299/slide-1.jpg)
Курсоры
Курсор — объект БД, который позволяет приложениям работать с записями построчно.
Поддерживаются два типа
курсоров:
явный — объявляется разработчиком;
неявный — не требует объявления.
Курсор может возвращать одну строку, несколько строк или ни одной строки.
Для повторного создания результирующего набора для других значений параметров курсор следует закрыть, а затем повторно открыть.
Слайд 3
![Операторы управления явным курсором DECLARE — выполняет объявление явного курсора.](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/3299/slide-2.jpg)
Операторы управления явным курсором
DECLARE — выполняет объявление явного курсора.
OPEN — открывает
курсор, создавая новый результирующий набор на базе указанного запроса.
FETCH — выполняет последовательное извлечение строк из результирующего набора от начала до конца.
CLOSE — закрывает курсор и освобождает занимаемые им ресурсы.
Слайд 4
![Атрибуты курсора %ISOPEN — возвращает значение TRUE, если курсор открыт.](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/3299/slide-3.jpg)
Атрибуты курсора
%ISOPEN — возвращает значение TRUE, если курсор открыт.
%FOUND — определяет, найдена ли
строка, удовлетворяющая условию.
%NOTFOUND — возвращает TRUE, если строка не найдена.
%ROWCOUNT — возвращает номер текущей строки.
Слайд 5
![Курсоры Курсор Oracle – указатель на область в PGA, в](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/3299/slide-4.jpg)
Курсоры
Курсор Oracle – указатель на область в PGA, в которой хранится:
1)строки запроса,
2)число строк,
3)указатель на разобранный запрос в общем пуле.
Открытие курсора – создание контекстной области PGA – создается моментальный снимок (snapshot) данных запроса.
Слайд 6
![Курсоры PL/SQL позволяет создавать 2 вида курсоров: 1) Статические курсоры,](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/3299/slide-5.jpg)
Курсоры
PL/SQL позволяет создавать 2 вида курсоров:
1) Статические курсоры, SQL выражение
для которых определяется на этапе компиляции:
Используются для DML команд
Могут быть явно объявлены и именованы
2) Динамические курсоры, SQL выражение для которых определяется на этапе выполнения:
Могут использоваться для любых SQL выражений, включая DDL и DCL команды
Реализуются с помощью оператора EXECUTE IMMEDIATE или пакета dbms_sql
Слайд 7
![Неявные курсоры Неявные курсор – выполнение SQL выражения в секции](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/3299/slide-6.jpg)
Неявные курсоры
Неявные курсор – выполнение SQL выражения в секции исполнения или
в секции исключений блока
Операторы INSERT, UPDATE, DELETE, MERGE, SELECT INTO
Не требуют объявления
Не требуют OPEN, FETCH, CLOSE
Слайд 8
![Неявные курсоры Когда неявный курсор не возвращает строк вообще, PL/SQL](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/3299/slide-7.jpg)
Неявные курсоры
Когда неявный курсор не возвращает строк вообще, PL/SQL генерирует исключение
NO_DATA_FOUND и передает управление в секцию исключений.
Когда SELECT возвращает более одной строки, PL/SQL генерирует исключение TOO_MANY_ROWS и также передает управление в секцию исключений.
SELECT INTO предназначен исключительно для того, чтобы возвращать ровно 1 строку – точную выборку
Слайд 9
![Неявные курсоры](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/3299/slide-8.jpg)
Слайд 10
![Неявные курсоры](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/3299/slide-9.jpg)
Слайд 11
![Неявные курсоры](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/3299/slide-10.jpg)
Слайд 12
![PRAGMA Ключевое слово PRAGMA используется для того, чтобы указать директиву](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/3299/slide-11.jpg)
PRAGMA
Ключевое слово PRAGMA используется для того, чтобы указать директиву компилятору PL/SQL;
PRAGMA
instruction;
Не транслируется в исполняемый код;
Разрешается использовать в секции декларации текущего блока;
Слайд 13
![PRAGMA](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/3299/slide-12.jpg)
Слайд 14
![Неявные курсоры – исключения](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/3299/slide-13.jpg)
Неявные курсоры – исключения
Слайд 15
![Неявные курсоры – исключения](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/3299/slide-14.jpg)
Неявные курсоры – исключения
Слайд 16
![Неявные курсоры – атрибуты курсора](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/3299/slide-15.jpg)
Неявные курсоры – атрибуты курсора
Слайд 17
![Неявные курсоры](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/3299/slide-16.jpg)
Слайд 18
![Неявные курсоры – UPDATE](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/3299/slide-17.jpg)
Слайд 19
![Неявные курсоры – UPDATE](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/3299/slide-18.jpg)
Слайд 20
![Неявные курсоры –INSERT](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/3299/slide-19.jpg)
Слайд 21
![Неявные курсоры –INSERT](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/3299/slide-20.jpg)
Слайд 22
![Неявные курсоры –INSERT](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/3299/slide-21.jpg)
Слайд 23
![Неявные курсоры – DELETE](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/3299/slide-22.jpg)
Слайд 24
![Неявные курсоры – DELETE](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/3299/slide-23.jpg)
Слайд 25
![Неявные курсоры – RETURNING RETURNING в выражениях INSERT, UPDATE, DELETE](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/3299/slide-24.jpg)
Неявные курсоры – RETURNING
RETURNING в выражениях INSERT, UPDATE, DELETE используется для
получения данных, измененных соответствующим выражением.
Позволяет избежать дополнительного SELECT для уточнения результатов.
Слайд 26
![Неявные курсоры – RETURNING](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/3299/slide-25.jpg)
Неявные курсоры – RETURNING
Слайд 27
![Неявные курсоры – RETURNING](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/3299/slide-26.jpg)
Неявные курсоры – RETURNING
Слайд 28
![Неявные курсоры – RETURNING](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/3299/slide-27.jpg)
Неявные курсоры – RETURNING
Слайд 29
![Явные курсоры Этапы открытия явного курсора: разбор [parse] связывание переменных](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/3299/slide-28.jpg)
Явные курсоры
Этапы открытия явного курсора:
разбор [parse]
связывание переменных [bind]
выполнение [execute]
построение плана выполнения запроса [determining execution plan]
связывание внешних переменных [associating host variables] и курсорных параметров
определение набора данных
выставление указателя текущей строки на первую строку в результирующем наборе данных
Слайд 30
![Явные курсоры Открытие явного курсора: OPEN cursor_name [(argument [,argument ...])];](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/3299/slide-29.jpg)
Явные курсоры
Открытие явного курсора:
OPEN cursor_name [(argument [,argument ...])];
Выборка из явного курсора:
FETCH
cursor_name INTO record or variable_list;
Закрытие явного курсора:
CLOSE cursor_name;
Слайд 31
![Курсоры](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/3299/slide-30.jpg)
Слайд 32
![Явные курсоры Объявляются явно в секции декларации блока или в](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/3299/slide-31.jpg)
Явные курсоры
Объявляются явно в секции декларации блока или в спецификации пакета.
Объявления
явного курсора:
Курсор без параметров
CURSOR company_cur IS SELECT company_id FROM company;
Курсор, который принимает параметры через список
CURSOR company__cur (id_in IN NUMBER) IS
SELECT name FROM company WHERE company_id = id_in;
Курcор, который содержит выражение RETURN вместо SELECT
CURSOR company_cur (id_in IN NUMBER) RETURN company%ROWTYPE;
Слайд 33
![Явные курсоры](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/3299/slide-32.jpg)
Слайд 34
![Явные курсоры](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/3299/slide-33.jpg)
Слайд 35
![Явные курсоры](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/3299/slide-34.jpg)
Слайд 36
![Явные курсоры](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/3299/slide-35.jpg)
Слайд 37
![Явные курсоры с параметрами](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/3299/slide-36.jpg)
Явные курсоры с параметрами
Слайд 38
![Явные курсоры с параметрами](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/3299/slide-37.jpg)
Явные курсоры с параметрами
Слайд 39
![Явные курсоры с RETURN](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/3299/slide-38.jpg)
Слайд 40
![Явные курсоры](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/3299/slide-39.jpg)
Слайд 41
![WHERE CURRENT OF Можно изменять текущую строку курсора FOR UPDATE:](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/3299/slide-40.jpg)
WHERE CURRENT OF
Можно изменять текущую строку курсора FOR UPDATE:
DECLARE
CURSOR wip_cur IS
SELECT
acct_no, enter_date FROM wip
WHERE enter_date < SYSDATE - 7 FOR UPDATE;
BEGIN
FOR wip_rec IN wip_cur
LOOP
INSERT INTO acct_log (acct_no, order_date)
VALUES (wip_rec . acct_no, wip__rec . enter_ date);
DELETE FROM wip
WHERE CURRENT OF wip__cur;
END LOOP;
END;
Слайд 42
![WHERE CURRENT OF](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/3299/slide-41.jpg)
Слайд 43
![WHERE CURRENT OF](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/3299/slide-42.jpg)
Слайд 44
![Использование ROWID](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/3299/slide-43.jpg)
Слайд 45
![Использование ROWID](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/3299/slide-44.jpg)
Слайд 46
![Использование ROWNUM ROWNUM - логический номер записи в запросе](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/3299/slide-45.jpg)
Использование ROWNUM
ROWNUM - логический номер записи в запросе
Слайд 47
![SELECT FOR UPDATE FOR UPDATE используется для блокировки строк в](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/3299/slide-46.jpg)
SELECT FOR UPDATE
FOR UPDATE используется для блокировки строк в результирующем наборе.
Использование
FOR UPDATE не требует действительного изменения данных.
Блокировки освобождаются по завершению транзакции.
Синтаксис:
SELECT ... FROM ... FOR UPDATE [OF column_reference] [NOWAIT];
NOWAIT – не ожидать освобождения других блокировок.
Слайд 48
![SELECT FOR UPDATE DECLARE CURSOR hounds_in_stock_cur IS SELECT pet.stock_no, pet.breeder,](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/3299/slide-47.jpg)
SELECT FOR UPDATE
DECLARE
CURSOR hounds_in_stock_cur IS
SELECT pet.stock_no, pet.breeder, dog.size
FROM dog_breeds dog,
inventory pet
WHERE dog.breed = pet.breed AND dog.class = ‘HOUND’
FOR UPDATE OF pet.stock_no, pet.breeder;
Слайд 49
![Курсорные переменные Курсорные переменные - это структуры данных, которые указывают](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/3299/slide-48.jpg)
Курсорные переменные
Курсорные переменные - это структуры данных, которые указывают на
курсорный объект.
Используются для:
Передачи курсора в качестве параметра,
Чтобы отложить связь курсора с SELECT-запросом до выполнения команды OPEN
Слайд 50
![Курсорные переменные](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/3299/slide-49.jpg)
Слайд 51
![Курсорные переменные Курсорная переменная, объявленная с помощью REF CURSOR без](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/3299/slide-50.jpg)
Курсорные переменные
Курсорная переменная, объявленная с помощью REF CURSOR без указания
RETURN может быть связана с любым запросом.
Курсорная переменная, объявленная с помощью REF CURSOR с указанием RETURN может быть связана только с запросом, который возвращает результат точно соответствующий числу и типам данных в записи после фразы RETURN во время выполнения.
Слайд 52
![Курсорные переменные](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/3299/slide-51.jpg)
Слайд 53
![Курсорные переменные](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/3299/slide-52.jpg)
Слайд 54
![Курсорные переменные](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/3299/slide-53.jpg)
Слайд 55
![Курсорные подзапросы](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/3299/slide-54.jpg)
Слайд 56
![Динамические курсоры EXECUTE IMMEDIATE - однострочные запросы и DDL команды,](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/3299/slide-55.jpg)
Динамические курсоры
EXECUTE IMMEDIATE - однострочные запросы и DDL команды,
OPEN FOR,
FETCH и CLOSE - динамические многострочные запросы.
Синтаксис EXECUTE IMMEDIATE:
EXECUTE IMMEDIATE sql_statement
[INTO {variable [,variable ...] | record}] [USING [IN | OUT | IN OUT] bind_argument
[, [IN | OUT | IN OUT] bind_argument . ..] ] [{RETURNING | RETURN} INTO bind_argument [,bind_argument]...];
Слайд 57
![Динамические курсоры](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/3299/slide-56.jpg)
Слайд 58
![Динамические курсоры](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/3299/slide-57.jpg)
Слайд 59
![Динамические курсоры Для улучшения производительности выполнения SQL выражений можно использовать](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/3299/slide-58.jpg)
Динамические курсоры
Для улучшения производительности выполнения SQL выражений можно использовать динамические курсоры
со связанными переменными.
Это позволяет серверу Oracle повторно использовать разобранные SQL выражения из разделяемого пула.
EXECUTE IMMEDIATE ‘INSERT INTO
dept (deptno, dname, loc) VALUES (:deptno, :dname, :loc)' USING deptno_in, dname_in, loc_in;
Слайд 60
![Динамические курсоры - OPEN FOR DECLARE TYPE cv_typ IS REF](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/3299/slide-59.jpg)
Динамические курсоры - OPEN FOR
DECLARE
TYPE cv_typ IS REF CURSOR;
CV
cv_typ;
laccount_no NUMBER;
Ibalance NUMBER;
BEGIN
OPEN cv FOR
'SELECT account_no, balance FROM accounts
WHERE balance < 500';
LOOP
FETCH cv INTO laccount_no, Ibalance;
EXIT WHEN cv%NOTFOUND;
-- Process the row.
END LOOP;
CLOSE cv;
END;
Слайд 61
![Параметры Oracle, связанные с курсорами cursor_space_for_time = {TRUE|FALSE} – больший](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/3299/slide-60.jpg)
Параметры Oracle, связанные с курсорами
cursor_space_for_time = {TRUE|FALSE} – больший объем
памяти для курсоров и никогда не освобождается. Применяется для увеличения скорости работы курсоров при наличии памяти для разделяемого пула.
cursor_sharing = {EXACT|SIMILAR|FORCE}
open_cursors - максимальное количество открытых курсоров.
session_cached_cursors – максимальное количество кэшируемых курсоров для сессии.
Слайд 62
![Параметры Oracle, связанные с курсорами](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/3299/slide-61.jpg)
Параметры Oracle, связанные с курсорами