Подпрограммы PL / SQL называются блоками PL / SQL, которые могут быть вызваны с помощью набора параметров. PL / SQL предоставляет два вида подпрограмм —
Функции — эти подпрограммы возвращают одно значение; в основном используется для вычисления и возврата значения.
Процедуры — Эти подпрограммы не возвращают значение напрямую; в основном используется для выполнения действий.
Функции — эти подпрограммы возвращают одно значение; в основном используется для вычисления и возврата значения.
Процедуры — Эти подпрограммы не возвращают значение напрямую; в основном используется для выполнения действий.
Части подпрограммы PL / SQL
Каждая подпрограмма PL / SQL имеет имя и может также иметь список параметров. Как и анонимные блоки PL / SQL, именованные блоки также будут иметь следующие три части:
S.No
Части и описание
1
Это необязательная часть. Однако декларативная часть подпрограммы не начинается с ключевого слова DECLARE. Он содержит объявления типов, курсоров, констант, переменных, исключений и вложенных подпрограмм. Эти элементы являются локальными для подпрограммы и перестают существовать, когда подпрограмма завершает выполнение.
Это обязательная часть и содержит операторы, которые выполняют назначенное действие.
Это опять необязательная часть. Он содержит код, который обрабатывает ошибки во время выполнения.
Это необязательная часть. Однако декларативная часть подпрограммы не начинается с ключевого слова DECLARE. Он содержит объявления типов, курсоров, констант, переменных, исключений и вложенных подпрограмм. Эти элементы являются локальными для подпрограммы и перестают существовать, когда подпрограмма завершает выполнение.
Это обязательная часть и содержит операторы, которые выполняют назначенное действие.
Это опять необязательная часть. Он содержит код, который обрабатывает ошибки во время выполнения.
Создание процедуры
имя-процедуры определяет имя процедуры.
Опция [ИЛИ ЗАМЕНА] позволяет модифицировать существующую процедуру.
Список необязательных параметров содержит имя, режим и типы параметров. IN представляет значение, которое будет передано извне, а OUT представляет параметр, который будет использоваться для возврата значения вне процедуры.
Тело процедуры содержит исполняемую часть.
Ключевое слово AS используется вместо ключевого слова IS для создания отдельной процедуры.
имя-процедуры определяет имя процедуры.
Опция [ИЛИ ЗАМЕНА] позволяет модифицировать существующую процедуру.
Список необязательных параметров содержит имя, режим и типы параметров. IN представляет значение, которое будет передано извне, а OUT представляет параметр, который будет использоваться для возврата значения вне процедуры.
Тело процедуры содержит исполняемую часть.
Ключевое слово AS используется вместо ключевого слова IS для создания отдельной процедуры.
пример
В следующем примере создается простая процедура, которая отображает строку «Hello World!» на экране при исполнении.
Когда приведенный выше код выполняется с использованием подсказки SQL, он даст следующий результат:
Выполнение отдельной процедуры
Автономная процедура может быть вызвана двумя способами:
Использование ключевого слова EXECUTE
Вызов имени процедуры из блока PL / SQL
Использование ключевого слова EXECUTE
Вызов имени процедуры из блока PL / SQL
Вышеупомянутая процедура с именем «приветствия» может быть вызвана с ключевым словом EXECUTE как —
Вышеуказанный вызов будет отображаться —
Процедура также может быть вызвана из другого блока PL / SQL —
Вышеуказанный вызов будет отображаться —
Удаление автономной процедуры
Вы можете отказаться от процедуры приветствия, используя следующее утверждение —
Режимы параметров в подпрограммах PL / SQL
В следующей таблице перечислены режимы параметров в подпрограммах PL / SQL —
S.No
Параметр Mode & Description
1
Параметр IN OUT передает начальное значение подпрограмме и возвращает обновленное значение вызывающей стороне. Ему может быть присвоено значение, и значение может быть прочитано.
Фактический параметр, соответствующий формальному параметру IN OUT, должен быть переменной, а не константой или выражением. Формальному параметру должно быть присвоено значение. Фактический параметр передается по значению.
Параметр IN OUT передает начальное значение подпрограмме и возвращает обновленное значение вызывающей стороне. Ему может быть присвоено значение, и значение может быть прочитано.
Фактический параметр, соответствующий формальному параметру IN OUT, должен быть переменной, а не константой или выражением. Формальному параметру должно быть присвоено значение. Фактический параметр передается по значению.
Пример режима IN & OUT 1
Эта программа находит минимум двух значений. Здесь процедура берет два числа, используя режим IN, и возвращает их минимум, используя параметры OUT.
Когда приведенный выше код выполняется в командной строке SQL, он дает следующий результат —
Пример 2 в режиме IN & OUT
Эта процедура вычисляет квадрат значения переданного значения. В этом примере показано, как мы можем использовать один и тот же параметр, чтобы принять значение и затем вернуть другой результат.
Когда приведенный выше код выполняется в командной строке SQL, он дает следующий результат —
Методы для передачи параметров
Фактические параметры могут быть переданы тремя способами:
Позиционная запись
В позиционной нотации вы можете вызвать процедуру как —
Именное обозначение
Смешанная запись
В смешанной нотации вы можете смешивать обе нотации в вызове процедуры; однако позиционная нотация должна предшествовать именованной нотации.
Здесь, я применил параметр NUM с типом NUMBER. IN в данном случае определяет, что параметр является входным. Что так же справедливо и по умолчанию. То есть, если параметр имеет только тип IN, то предикат IN можно не указывать. Так же напомню, что NUM является формальным параметром функции (formal parameters). А вот входное значение функции является фактическим параметром (actual parameters). Формальный параметр NUM, является только вместилищем для фактически передаваемого параметра и все операции производятся с формальным параметром (в программировании, если я правильно помню, это называется передача параметра в процедуру по значению).
Что, собственно я и говорил выше. Вернемся к нашей процедуре, она содержит, один оператор SELECT (уже что-то полезное!), который выбирает одну запись из таблицы customers, в данном случае заказчика по его номеру. Для примера вызовем ее из анонимного блока предварительно определив и передав ей параметр:
Получаем после вызова:
Вот такой «желтый и круглый» заказчик! 🙂 Ясно видно, как сработал оператор SELECT и функция TESTPRM вывела результат. При этом фактический параметр, был передан и не изменялся. А теперь попробуем написать более сложную процедуру:
Получаем после компиляции:
Рассмотрим параметры более внимательно, про первый мы уже все знаем, а вот второй DT определен как OUT и имеет тип VARCHAR2. Что это значит? А, вот что. DT выходной параметр и принимает значение из самой процедуры на возврат, т.е.
Вот так работает тип OUT. Давайте определим анонимный блок и посмотрим, что же получается:
Заметили, что значение HELLO не появилось? Естественно, так как параметр DT функции TESTOUT является выходным. Если сказать честно, то лично меня такой тип возврата данных из процедуры немного шокирует, так как в классическом программировании такое применяется по моему редко, хотя я и могу ошибаться. Но, тем не менее может быть в каком-то случае такой вызов вполне оправдан! Можете пока поработать со всем этим. 🙂
например: создать хранимую процедуру dept_proc и вставить запись в таблицу dept (хранимая процедура без параметров)
Результат показывает, что создание было успешным, а затем выполните запрос, чтобы узнать, вставлена ли запись.
Результат показывает, что запись не была вставлена, потому что мы не выполняли эту хранимую процедуру. Далее мы представим выполнение хранимой процедуры.
2. Вызов хранимой процедуры
При вызове хранимой процедуры обычно используется оператор EXECUTE, но имя хранимой процедуры можно использовать для вызова непосредственно в блоке PL / SQL. Формат синтаксиса следующий:
[exec | execute] procedure_name
Все возможные варианты использования вызова exec или использования вызова execute
например: вызовите только что созданную хранимую процедуру и запросите
Затем запросите: выберите * из отдела;
На этот раз очевидно, что запись была вставлена успешно.
1. Используйте хранимую процедуру для запроса информации о сотруднике с указанным номером (с помощью параметра in).
Перед вызовом хранимой процедуры запишите set serveroutput для вывода системы. При вызове хранимой процедуры вы должны дать хранимой процедуре параметр
2. Используйте параметр out для запроса имени сотрудника по номеру сотрудника.
Вызов хранимой процедуры в PL / SQL:
3. Используйте параметры типа in out, чтобы запросить зарплату по номеру сотрудника.
Вызов для изменения хранимой процедуры в PL / SQL:
Модификация хранимой процедуры
Изменение хранимой процедуры аналогично изменению представления.Хотя есть также оператор ALTER PROCEDURE, он используется для перекомпиляции или проверки существующих процедур. Если вы хотите изменить определение процесса, по-прежнему используйте команду CREATE OR REPLACE PROCEDURE с тем же форматом синтаксиса. Фактически, изменение существующего процесса, по сути, означает использование CREATE OR REPLACE PROCEDURE для воссоздания нового процесса с сохранением того же имени, что и у исходного.
Удаление хранимых процедур
Когда хранимая процедура больше не нужна, чтобы удалить хранимую процедуру из памяти, чтобы освободить соответствующее пространство памяти, вы можете использовать следующий оператор:
DROP PORCEDURE count_name;
например: удалить хранимую процедуру dept_proc
drop procedure dept_proc;
Во-вторых, спусковой крючок
Используйте операторы SQL для создания триггеров
1. Создайте триггер на уровне оператора.
Триггер на уровне оператора выполняется для триггера, вызванного оператором DML. В триггерах на уровне операторов нет необходимости использовать предложение for each row, то есть независимо от того, на сколько строк влияет операция с данными, триггер будет выполнен только один раз.
eg1: используйте триггеры для отслеживания операций в таблице dept и записи их в таблицу журнала.
Создайте таблицу журнала:
insert into dept values(88,’ALICE’,’JA’);
insert into dept values(89,’ALICE’,’CH’);
insert into dept values(90,’ALICE’,’AM’);
update dept set loc=’JC’ where deptno=88;
delete from dept where deptno=90;
Запросить результаты в dept_log:
Каждый раз, когда выполняется инструкция, запускается триггер
eg2: в случае отсутствия указания имени первичного ключа, эффект самоприращения первичного ключа таблицы учеников
Создайте студенческий стол:
Результат создания:
вставить в значения ученика (оглушение, оценка) («Чжан Сань», 89,5);
вставить в значения ученика (stuname, score) (‘ ‘ ， 77);
вставить в значения ученика (stuname, score) (‘ ‘ ， 99);
Запросите таблицу студентов после вставки данных:
В это время учеба студента растет.
Включение и отключение триггеров
Oracle предоставляет оператор ALTER TRIGGER для включения и отключения триггеров в формате синтаксиса:
ALTER TRIGGER [schema.]trigger_name DISABLE | ENABLE;
Удаление триггера
Когда триггер не используется, удалите его из памяти. грамматика:
Удалить: DROP TRIGGER tri_name;
Если триггер устарел и вы хотите его переопределить, вам не нужно удалять его перед созданием. Вам также необходимо добавить ключевое слово OR REPLACE после оператора CREATE. Такие как:
Переопределить: СОЗДАТЬ ИЛИ ЗАМЕНИТЬ ТРИГГЕР tri_name;
Интеллектуальная рекомендация
Статическая и динамическая отправка в Java
Освойте команду jar
Упражнение «Think Python» 4-1: Пример схемы стека кода и размышления об остановке в этой главе
Глава 4 Пример: дизайн интерфейса Упражнение 4-1. Пример схемы стека кода в этой главе, точка остановки отклоняется от мышления 4.1 Упражнение 4.1.1】 Нарисуйте диаграмму стека, чтобы показать состояни.
Конвертировать PDF в SWF файл
1. Необходимо использовать службу pdf2swf.exe Во-вторых, код выглядит следующим образом.
Windows 10 Python 2.x совместим с Python 3.x
Некоторое время назад, из-за потребностей работы, Python 3.6.1, установленный на компьютере, использовал модуль M2Crypto для шифрования закрытого ключа, и дешифрование открытого ключа всегда происходи.
В этом руководстве вы увидите подробное описание того, как создавать и выполнять именованные блоки (процедуры и функции).
Процедуры и функции — это подпрограммы, которые можно создавать и сохранять в базе данных как объекты базы данных. Они могут быть вызваны или переданы внутри других блоков.
Помимо этого, мы рассмотрим основные различия между этими двумя подпрограммами. Также мы собираемся обсудить встроенные функции Oracle.
В этом уроке вы узнаете
Терминологии в подпрограммах PL / SQL
Прежде чем мы узнаем о подпрограммах PL / SQL, мы обсудим различные термины, которые являются частью этих подпрограмм. Ниже приведены термины, которые мы собираемся обсудить.
Параметр:
Параметр является переменной или заполнителем любого допустимого типа данных PL / SQL, через который подпрограмма PL / SQL обменивается значениями с основным кодом. Этот параметр позволяет вводить подпрограммы и извлекать из них подпрограммы.
На основании их назначения параметры классифицируются как
IN параметр:
Выходной параметр:
Параметр IN OUT:
Эти типы параметров должны быть упомянуты во время создания подпрограмм.
ВОЗВРАЩЕНИЕ
RETURN — это ключевое слово, которое инструктирует компилятору переключать элемент управления из подпрограммы в оператор вызова. В подпрограмме RETURN просто означает, что элемент управления должен выйти из подпрограммы. Как только контроллер обнаружит ключевое слово RETURN в подпрограмме, код после этого будет пропущен.
Обычно родительский или основной блок вызывает подпрограммы, а затем элемент управления переключается с родительского блока на вызываемые подпрограммы. RETURN в подпрограмме вернет элемент управления обратно в родительский блок. В случае функций оператор RETURN также возвращает значение. Тип данных этого значения всегда упоминается во время объявления функции. Тип данных может иметь любой допустимый тип данных PL / SQL.
Что такое процедура в PL / SQL?
Процедура — это подпрограмма, состоящая из группы операторов PL / SQL. Каждая процедура в Oracle имеет свое уникальное имя, по которому она может быть передана. Этот подпрограммный блок хранится в виде объекта базы данных. Ниже приведены характеристики этого подпрограммного блока.
Примечание. Подпрограмма — это не что иное, как процедура, и ее необходимо создавать вручную в соответствии с требованием. После создания они будут сохранены как объекты базы данных.
Синтаксис:
Пример 1: создание процедуры и вызов ее с помощью EXEC
В этом примере мы собираемся создать процедуру, которая принимает имя в качестве входных данных и печатает приветственное сообщение в качестве выходных данных. Мы будем использовать команду EXEC для вызова процедуры.
Объяснение кода:
Что такое функция?
Функции — это отдельная подпрограмма PL / SQL. Подобно процедуре PL / SQL, функции имеют уникальное имя, по которому на них можно ссылаться. Они хранятся в виде объектов базы данных PL / SQL. Ниже приведены некоторые характеристики функций.
Синтаксис
Пример 1: Создание функции и вызов ее с помощью анонимного блока
PL SQL — функции, процедуры, переменные, курсоры и циклы plsql
Composer: скачать, установка, как начать использовать. Проекты на GitHub.Com
Основы PL/SQL
Programming Language for SQL (PL/SQL) – решение Oracle, предоставляющее средства, которые позволяют выполнять сложную обработку информации (plsql).
Программные единицы PL/SQL
Структура блока PL/SQL
Блок PL/SQL может содержать до четырех разделов, однако только один из них является обязательным.
Структура блока PL/SQL для процедуры показана на рисунке: Рисунок «Процедура, содержащая все четыре раздела»:
Хранимая процедура
Хранимая процедура — это определенный набор инструкций, написанных на языке PL/SQL. Вызов процедуры приводит к выполнению содержащихся в ней инструкций. Процедура хранится в базе данных, поэтому и называется хранимой. Процедура состоит из двух основных частей:спецификации и тела. Спецификация процедуры (procedure specification) включает в себя имя процедуры и описание ее входных и выходных данных. Эти входные и выходные данные называются формальными параметрами (formal parameters) или формальными аргументами (formal arguments). Если при вызове процедуры указываются параметры командной строки или другие входные данные, эти значения называются фактическими (actual) параметрами или фактическими аргументами. Пример спецификации
Здесь мы видим процедуру с тремя формальными параметрами. Слово IN после имени параметра означает, что при вызове процедура может считать из этого параметра входное значение. Слово OUT означает, что процедура может использовать данный параметр для возврата значения в ту программу, из которой она была вызвана. Комбинация IN OUT после имени параметра говорит о том, что параметр может использоваться как для передачи значения процедуре, так и для возврата значения. Тело процедуры (procedure body) — это блок PL/SQL-кода.
Хранимые функции
Функция PL/SQL похожа на процедуру PL/SQL: она также имеет спецификацию и тело. Главное различие между процедурой и функцией в том, что функция предназначена для возврата значения, которое может использоваться в более крупном SQL-Операторе.
Триггеры
Триггер — это процедура PL/SQL, которая выполняется автоматически, когда происходит некоторое заданное событие, называемое триггерным событием (triggering event). Например, можно писать триггеры, срабатывающие при выполнении над таблицей операций INSERT, UPDATE или DELETE; при выдаче команд DDL; при входе пользователя в систему или его выходе из системы; при запуске или останове базы данных; при возникновении ошибок. Между триггерами и процедурами PL/SQL есть три различия:
Структура блока PL/SQL
Базовый блок PL/SQL состоит из четырех секций:
Анонимный блок (anonumous block) — это блок PL/SQL без секции заголовка, иначе говоря, секции имени, поэтому он и называется анонимным. Анонимные блоки могут выполняться из SQL*Plus и использоваться в функциях, процедурах и триггерах PL/SQL. Вспомните, что сами процедуры, функции и триггеры также состоят из базовых блоков. Это означает, что базовый блок можно помещать в другой базовый блок.
Секция объявлений
Секция объявлений не является обязательной. В случае использования она начинается после секции заголовка и оканчивается перед ключевым, словом BEGIN. Эта секция содержит объявления переменных, констант, курсоров, исключений, функций и процедур PL/SQL, которые будут использоваться в выполняемой секции и секции исключений. Все объявления переменных и констант должны размещаться до объявлений функций или процедур. О переменных и константах PL/SQL будет подробно рассказано в следующем разделе. Объявление сообщает PL/SQL о том, что нужно создать переменную, константу, курсор, функцию или процедуру согласно приведенной спецификации. Когда выполнение базового блока завершается, все элементы, объявленные в секции объявлений, перестают существовать. Элементы, объявленные в секции объявлений базового блока, могут использоваться только в пределах этого блока. Одним словом, все, что находится в секции объявлений, при надлежит блоку и может использоваться только внутри него, а следовательно, существует только на протяжении его времени жизни. Часть кода, в которой может использоваться переменная, называется областью видимости (scope).
Выполняемая секция
Выполняемая секция начинается с ключевого слова BEGIN и заканчивается либо ключевым словом EXCEPTION, если присутствует секция исключений, либо ключевым словом END, за которым следуют необязательное имя функции или процедуры и точка с запятой. Выполняемая секция содержит один и более PL/SQL-операторов, выполняемых при передаче управления данному блоку. Структура выполняемой секции показана ниже.
В выполняемом коде PL/SQL чаще всего встречается оператор присваивания (:=). Он указывает, что нужно вычислить выражение справа и поместить результат в переменную слева.
Секция исключений
В ходе выполнения PL/SQL-оператора может возникнуть ошибка, которая сделает невозможным дальнейшее выполнение программы. Такие исключительные ситуации называются исключениями (exceptions). Пользователь, вызвавший процедуру, должен быть проинформирован о возникновении исключения, а также о причинах, его вызвавших. Вы можете выдать пользователю содержательное сообщение об ошибке, или предпринять некоторые корректирующие действия и повторить операцию, выполнявшуюся до возникновения ошибки. Вы также можете откатить изменения, которые были произведены в базе данных к этому моменту. PL/SQL помогает вам во всех этих случаях, предоставляя средства обработки исключений (exception handling).
Секция исключений начинается с ключевого слова EXCEPTION и продолжается до конца блока. Каждому исключению соответствует оператор WHEN имя_исключения, указывающий, что должно быть сделано при возникновении данного исключения. Все операторы, находящиеся между оператором, вызвавшим ошибку, и секцией исключений, игнорируются. Выполнение оператора, указанного в секции исключений, называется обработкой исключения (exception handling). Процесс, включающий в себя обнаружение ошибки, определение, какое исключение описывает ее наилучшим образом, и передачу PL/SQL информации, позволяющей найти соответствующий код в секции исключений, называется возбуждением исключения (raising exception).
Переменные
Переменные — это именованные контейнеры. Они могут содержать информацию (данные) различных видов. В зависимости от того, какую информацию в них можно помещать, они имеют различные типы данных, а чтобы отличать их друг от друга, им присваиваются имена. PL/SQL хранит числа в переменных типа NUMBER, а текст — в переменных типа CHAR или VARCHAR2. Синтаксис объявления переменной в PL/SQL может иметь любую из следующих форм:
Имя_переменной — это любой правильный идентификатор PL/SQL. Правильный идентификатор PL/SQL должен:
Тип_данных — это любой допустимый тип данных SQL или PL/SQL. Модификатор NOT NULL требует, чтобы переменная имелазначение. Если он указан, переменной должно быть присвоено значение по умолчанию.
Объявление констант PL/SQL
Синтаксис объявления константы имеет следующий вид:
В отличие от переменных константам обязательно присваивается значение, которое нельзя изменять на протяжении времени жизни константы. Константы очень полезны для поддержания безопасности и дисциплины при разработке больших и сложных приложений. Например, если вы хотите гарантировать, что процедура PL/SQL не будет модифицировать передаваемые ей данные, можете объявить их константами. Если процедура все же попытается их модифицировать, PL/SQL возбудит исключение.
Оператор IF
Оператор IF имеет следующий синтаксис:
Действие_1 … альтернативное Действие представляют один или несколько PL/SQL-операторов. Каждая группа операторов выполняется только в том случае, если выполнено соответствующее условие. После того как обнаружено выполнение одного из условий, остальные условия не проверяются.
Циклы
PL/SQL предоставляет три различные конструкции для итеративной обработки. Каждая из них позволяет циклически выполнять набор операторов PL/SQL. Выход из цикла осуществляется в зависимости от некоторого условия.
Конструкция LOOP имеет следующий синтаксис:
При наличии конструкции WHEN все операторы в теле цикла повторяются до тех пор, пока выражение условие_выхода не примет положительное значение (т.е. не станет истинным). Условие выхода проверяется на каждом проходе, иначе называемом итерацией. Как только выражение принимает значение «истина», все операторы после EXIT пропускаются, итерации прекращаются и выполнение продолжается с первого оператора, следующего за END LOOP. Если условие WHEN отсутствует, операторы между LOOP и EXIT выполняются только один раз. Очевидно, что опустив условие WHEN, вы поступите нелогично. В конце концов идея цикла состоит в том, чтобы обеспечить потенциально многократное выполнение кода.
Цикл WHILE
Еще одной разновидностью цикла является цикл WHILE. Он хорошо подходит в ситуациях, когда количество итераций заранее неизвестно, и определяется некоторым внешним фактором. Цикл WHILE имеет следующий синтаксис:
Условие WHILE проверяется перед каждым входом в цикл. Если оно имеет значение «истина», то выполняется очередная итерация.
Цикл FOR
В цикле FOR для подсчета итераций используется переменная-счетчик, называемая также индексом цикла (loop index). По завершении каждой итерации счетчик увеличивается, начиная с нижнего предела, или уменьшается, начиная с верхнего предела. Как только его значение выйдет за указанный диапазон, цикл завершается. Синтаксис цикла FOR выглядит следующим бразом:
Курсоры
Курсор — это исключительно важная конструкция PL/SQL, лежащая в основе взаимодействия PL/SQL и SQL. Название «курсор» означает «текущий набор записей». Курсор представляет собой специальный элемент PL/SQL, с которым связан SQL-оператор SELECT. Используя курсор, можно отдельно обрабатывать каждую строку связанного с ним SQL-оператора. Курсор объявляется в секции объявлений базового блока. Он открывается командой OPEN, а выборка строк осуществляется с помощью команды FETCH. После завершения всей обработки курсор закрывается командой CLOSE. Закрытие курсора освобождает те системные ресурсы, которые использовались, пока он был открыт. Строки, выбранные курсором, можно заблокировать, чтобы предотвратить их модификацию другими пользователями. Закрытие курсора или выполнение явной операции COMMIT или ROLLBACK приведет к разблокированию строк. Для SQL-операторов, используемых в коде PL/SQL, применяются скрытые, или неявные (implicit), курсоры, а также явные (explicit) курсоры, т.е. те, которым присвоено имя.
Объявление курсора и атрибуты курсора
Курсор объявляется в процедуре PL/SQL следующим образом:
Параметры курсора похожи на параметры процедуры, за тем исключением, что они всегда являются входными (IN). Использование параметров OUT или IN OUT невозможно, поскольку курсор не может их модифицировать. Параметры используются в конструкции WHERE курсорного оператора SELECT. Спецификация возврата показывает, записи какого типа будут выбираться оператором SELECT. Таблица_или_столбец — это имя столбца, который предстоит обновлять, или имя таблицы, в которой предстоит удалять или обновлять строки. Оно должно входить в число имен таблиц и столбцов, указанных в операторе SELECT курсора, и предназначено для документирования, показывая, какие элементы могут быть потенциально модифицированы кодом, использующим данный курсор. Команда FOR UPDATE блокирует строки, выбранные оператором SELECT при открытии курсора. Строки остаются заблокированными до тех пор, пока вы не закроете курсор рассмотренными выше способами. Атрибуты курсора:
Записи PL/SQL
ЗаписьPL/SQL— это набор данных базовых типов. К ней можно обращаться, как к единому целому. Для доступа к отдельным полям записи применяется нотация имя_записи_имя_поля, которую вы уже использовали для столбцов таблицы. Записи могут иметь один из трех типов, перечисленных ниже; вы можете объявлять переменные, имеющие тип записи.
Использование команд OPEN, FETCH и CLOSE
Команды открытия курсора, выборки из курсора и закрытия курсора имеют следующий синтаксис:
После открытия курсор содержит набор записей, если в результате успешного выполнения оператора SELECT из базы данных были выбраны заданные строки. Каждая команда FETCH удаляет запись из открытого курсора и перемещает ее содержимое либо в переменную PL/SQL, тип записи которой совпадает с типом записи курсора, либо в группу переменных PL/SQL, где каждая переменная в списке совпадает по типу с соответствующим полем в записи курсора. Перед тем как пытаться выбрать из курсора очередную запись, следует проверить с помощью атрибутов FOUND и NOTFOUND, есть ли в нем еще записи. Выборки из пустого курсора будут все время давать последнюю запись, не приводя к ошибке. Не забывайте проверять атрибуты FOUND и NOTFOUND при использовании FETCH. Фактическая обработка записей из курсора обычно выполняется внутри цикла. При написании такого цикла неплохо начать с проверки, была ли найдена запись в курсоре. Если да, можно продолжать необходимую обработку; в противном случае следует выйти из цикла. То же самое можно сделать более коротким путем, использовав курсорный цикл FOR. При этом PL/SQL будет осуществлять открытие, выборку и закрытие без вашего участия.
Курсорный цикл FOR
Синтаксис курсорного цикла FOR имеет следующий вид:
Этот цикл выбирает записи из курсора в переменную типа запись_курсора. Поля записи_курсора можно использовать для доступа к данным из операторов PL/SQL, выполняемых в цикле. Когда все записи выбраны, цикл завершается. Для удобства открытие и закрытие курсора производится автоматически. Попытавшись выбрать запись из неоткрытого курсора, вы получите сообщение in valid cursor (недействительный курсор). Если не закрывать курсоры, то в конце концов количество открытых курсоров достигнет максимальной величины, допускаемой системой.
Конструкция WHERE CURRENT OF
Когда курсор открывается для обновления или удаления выбранных записей, можно использовать конструкцию WHERE CURRENT OF имя_курсора для доступа к таблице и строке, которые соответствуют последней записи, выбранной в конструкции WHERE оператора UPDATE или DELETE.
Обработка ошибок
В языке PL/SQL ошибки всех видов интерпретируются как исключения — ситуации, которые не должны возникать при нормальном выполнении программы. К числу исключений относятся:
PL/SQL перехватывает ошибки и реагирует на них при помощи так называемых обработчиков исключений. Механизм обработчиков исключений позволяет четко отделить код обработки ошибок от основной логики программы, а также дает возможность реализовать обработку ошибок, управляемую событиями. Независимо от того, как и по какой причине возникло конкретное исключение, оно всегда обрабатывается одним и тем же обработчиком в разделе исключений. При возникновении ошибки — как системной, так и ошибки в приложении — в PL/SQL инициируется исключение. В результате выполнение блока прерывается, и управление передается для обработки в раздел исключений текущего блока, если он имеется. После обработки исключения возврат в тот блок, где исключение было инициировано, невозможен, поэтому управление передается во внешний блок. Схема передачи управления при возникновении исключения:
Существует два типа исключений:
Исключения
Исключение — это состояние ошибки, которое активизируется — или возбуждается — при возникновении некоторой проблемы. Существует много разных исключений, каждое из которых связано с определенным типом проблем. При возникновении исключительной ситуации выполнение кода останавливается на операторе, который возбудил исключение, и управление передается той части блока, которая обрабатывает это исключение. Если блок не содержит выполняемой секции, PL/SQL пытается найти выполняемую секцию во включающем базовом блоке (enclosing basic block), т.е. в блоке, который является внешним по отношению к коду, возбудившему исключение. Если в непосредственном включающем блоке отсутствует обработчикданного исключения, то поиск продолжается в блоках следующих уровней, пока не будет найден подходящий обработчик, а если его найти не удается, то выполнение программы прекращается с выдачей сообщения о необрабатываемой ошибке. Часть блока, предназначенная для обработки исключений, — это идеальное место для выдачи информативных сообщений об ошибках и выполнения очистки (cleanup), позволяющей избавиться от всего, что могло бы в дальнейшем вызвать путаницу или проблемы. Если исключение было возбуждено в ходе выполнения процедуры, вставляющей строки в таблицу, то типичная процедура очистки может включать в себя оператор ROLLBACK. После того как управление было передано обработчику исключения, оно уже не возвращается оператору, ставшему причиной этого исключения. Вместо этого управление передается оператору включающего базового блока, который следует сразу за вызовом вложенного блока или процедуры/функции.
Системные исключения
В PL/SQL можно выдавать пользователям информацию об ошибке двумя способами. Первый способ — использовать команду SQLCODE, которая возвращает код ошибки. Этот код представляет собой отрицательное число, обычно равное номеру ошибки ORA, которая выводится при завершении приложения, если исключение осталось необработанным. Второй способ — возвращать текстовое сообщение, описывающее ошибку. Неудивительно, что соответствующая команда называется SQLERRM. В обработчике исключения можно использовать как SQLCODE, так и SQLERRM. Замечание: не у всех системных исключений есть имена. Системные исключения:
Исключения, определяемые программистом
Одной из удобных возможностей PL/SQL является то, что он позволяет вам определять свои собственные исключения. При возбуждении и обработке они должны именоваться и объявляться аналогично любым другим элементам PL/SQL. Исключение объявляется в секции объявлений. Аналогично любой другой объявленной там переменной, исключение действительно только для данного блока. Вы можете использовать свои собственные исключения для обработки ошибок, которые система не обнаруживает или не считает за ошибки.
Схема сложного запроса PL/SQL с использованием временных таблиц
Ниже рассмотрена схема получения результирующей таблицы в Oracle Database, используя временные таблицы.
