PL SQL доцент Кокоулин А Н Структура блоков

PL/SQL доцент Кокоулин А. Н.

Структура блоков PL/SQL Весь код PL/SQL, выполняющий базовую работу, состоит из базовых блоков. Базовый блок состоит из: Секция заголовка Секция объявлений (необязательная) Выполняемая секция Секция исключений (необязательная) Анонимный блок – блок без секции заголовка.

Структура блоков PL/SQL Секция заголовка Заголовок базового блока верхнего уровня (блоки могут быть вложенными)для функции, процедуры или триггера содержит спецификацию этой функции, процедуры или триггера.

Структура блоков PL/SQL Секция объявлений Начинается после секции заголовка и заканчивается перед ключевым словом BEGIN. Содержит объявление констант, переменных, курсоров, исключений, функций и процедур PL/SQL, которые могут использоваться в выполняемой секции исключений. Объявленные элементы могут использоваться только в пределах данного блока или переданы в качестве фактических параметров. Область видимости – часть кода, в которой может использоваться переменная.

Структура блоков PL/SQL Выполняемая секция Начинается с ключевого слова BEGIN Заканчивается: ü EXCEPTION – присутствует секция исключений ü END – за ним может следовать необязательное имя функции или процедуры и «; » ü Может содержать 1 и более PL/SQL операторов BEGIN один и более PL/SQL операторов [секция исключений] END [имя функции или процедуры];

Структура блоков PL/SQL Секция исключений Исключение – ошибка, возникшая при выполнении PL/SQL оператора, которая делает дальнейшее выполнение программы невозможным. Секция начинается с ключевого слова EXCEPTION и продолжается до конца блока. Каждое исключение начинается с ключевого слова WHEN EXCEPTION WHEN имя исключения THEN действия, предпринимаемые при возникновении исключения

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

Переменные и константы PL/SQL Переменные. Синтаксис. Имя_переменной тип_данных [[NOT NULL] : = выражение_по_умолчанию] § Имя_переменной тип_данных [[NOT NULL] DEFAULT выражение_по_умолчанию] § Имя переменной – любой правильный индентификатор PL/SQL: ü Не более 30 символов, без пробелов и знаков табуляции; ü Состоит только из букв, цифр, символа подчеркивания «_» , знака «$» , знака «#» ; ü Начинается с буквы; ü Не совпадает с зарезервированными словами PL/SQL и SQL.

Переменные и константы PL/SQL Переменные. Синтаксис. Модификатор NOT NULL требует, чтобы переменная имела значение. Если он указан, переменной присваивается значение по умолчанию. Изменение значения переменной. Оператор присваивания Имя_переменной : = выражение; 2. Передача процедуре в качестве фактического параметра 3. Присваивание значений с использованием SQL 1.

Переменные и константы PL/SQL Константы. Синтаксис. Имя_переменной тип данных CONSTANT : = выражение; Константам обязательно присваивать значение, которое нельзя будет изменить

Типы данных в PL/SQL BINARY_INTEGER Этот тип данных и его подтипы NATURAL и POSITIVE применяются для создания переменных и констант, которые хранят число со знаком. Двоичные целые числа могут принимать значения в диапазоне от 2 в 31 степени до 2 в 31 степени минус 1. BOOLEAN Принимается для создания переменных и констант, в которых хранятся логические значения TRUE и FALSE. CHAR Есть подтипы CHARACTER и STRING. Максимальный размер 32767 NUMBER Есть подтипы DEC, DECIMAL, DOUBLE, PRECISION, FLOAT, INT, INTEGER, NUMERIC, REAL и SMALLINT. RECORD Используется для создания пользовательских типов записей базы данных. TABLE Служит для создания табличных типов данных PL/SQL

Типы данных в PL/SQL VARCHAR 2 Есть подтип VARCHAR. Максимальный размер 32767. col%TYPE Используется для определения типа данных столбца или переменной по типу данных другого столбца или переменной, к имени которого или которой (col) приписан суффикс %TYPE. tab%ROWTYPE Используется для определения типа данных записи по типу данных столбцов таблицы, к имени которой (tab) приписан суффикс %ROWTYPE.

Оператор IF IF условие_1 THEN действие_1; [ELSIF условие_2 THEN действие_2; ] … [ELSE альтернативное_действие; ] END IF; Действия – один или несколько PL/SQL операторов. Каждая группа операторов выполняется, если выполнено соответствующее условие. Как только обнаружено выполнение одного из условий, остальные условия не проверяются.

Цикл LOOP операторы; EXIT имя_цикла [WHEN условие_выхода]; операторы; END LOOP; При наличии конструкции WHEN все операторы в теле цикла повторяются до тех пор, пока условие_выхода не станет истинным. Условие проверяется на каждой итерации. Как только условие выполнится, все операторы после EXIT пропускаются, цикл завершается. Если WHEN отсутствует, то операторы в теле цикла будут выполняться только один раз.

Цикл WHILE условие_выхода LOOP операторы; END LOOP; Используется в ситуациях, когда количество итераций неизвестно и определяется некоторым внешним фактором. Условие проверяется перед каждой итерацией. Если условие истинно, выполняется очередная итерация.

Цикл FOR счетчик IN [REVERSE] нижняя граница. . верхняя граница LOOP операторы; END LOOP; При каждой итерации счетчик увеличивается на 1 (используется IN) с нижней границы до верхней или уменьшается на 1 (используется REVERSE) с верхней до нижней границы.

Курсоры Курсор – инструкция, лежащая в основе взаимодействия SQL и PL/SQL. Это специальный элемент PL/SQL, с которым связан SQL оператор SELECT. Используя курсор, можно отдельно обрабатывать каждую строку связанного с ним SQL оператора. Курсор объявляется в секции объявлений базового блока. Он открывается командой OPEN, а выборка строк осуществляется с помощью командой FETCH. После завершения все обработки курсор закрывается командой CLOSE. Строки, выбранные курсором, можно заблокировать, чтобы предотвратить их модификацию. Закрытие курсора и ли выполнение команд COMMIT или ROLLBACK приведет к разблокировке строк.

Курсоры Объявление курсора CURSOR имя_курсора [([парметр_1, [параметр_2…])] [RETURN спецификация_возврата] IS оператор_select [FOR UPDATE [OF таблица_или_столбец_1 [, таблица_или_столбец_2…] ] ]

Курсоры Параметры курсора всегда входные. Спецификация_возврата показывает, записи какого типа будут выбираться оператором SELECT. Таблица_или_столбец – имя столбца, который предстоит обновлять, или имя таблицы, в которой предстоит удалять или обновлять строки. FOR UPDATE блокирует строки, выбранные оператором SELECT при открытии курсора.

Курсоры Атрибуты курсора – индикаторы, показывающие состояние курсора Имя_курсора%ISOPEN Позволяет проверить, открыт ли курсор. Если курсор имя_курсора уже открыт, возвращает значение TRUE Имя_курсора%ROWCOUNT Количество строк таблицы, возвращенных оператором SELECT курсора Имя_курсора%FOUND Позволяет проверить, была ли успешной последняя попытка получения записи из курсора. Если запись была выбрана, возвращается значение TRUE Имя_курсора%NOTFOUND Противоположен атрибуту FOUND. Tесли записей больше не найдено, возвращается значение TRUE.

Курсоры Команда открытия курсора OPEN имя_курсора; Команда выборки из курсора FETCH имя-курсора INTO переменная_или_список_переменных; Команда закрытия курсора CLOSE имя_курсора;

Курсоры Курсорный цикл FOR запись_курсора IN имя_курсора LOOP операторы; END LOOP; В цикле выполняется обработка записей из курсора. При попытке выбрать запись из неоткрытого курсора, будет выдано сообщение : invalid cursor. Если курсоры не закрывать, то в конце концов их количество достигнет максимально возможного для системы.

Курсоры Конструкция WHERE CURRENT OF имя_курсора Используется для доступа к таблице и строке, которые соответствуют последней записи, выбранной в конструкции WHERE оператора UPDATE или DELETE.

Исключения Исключение – состояние ошибки, которое активизируется или возбуждается при возникновении некоторой проблемы. При возникновении исключительной ситуации, выполнение кода останавливается на операторе, который возбудил данное исключение, и управление передается той части блока, которая обрабатывает это исключение.

Системные исключения Исключение Причина возбуждения CURSOR_ALREADY_OPEN Попытка открыть уже открытый курсор DUP_VAL_ON_INDEX Попытка вставить повторяющееся значение в столбец, имеющий уникальный индекс, а следовательно, ограничение уникальности INVALID_CURSOR Попытка применить команду FETCH к неоткрытому курсору или попытка закрыть курсор, который не открывался NO_DATA_FOUND Попытка выполнить SELECT INTO, когда SELECT возвращает нулевое количество строк (а также другие причины, описание которых выходит за рамки этой книги) PROGRAM_ERROR Внутренняя ошибка. Обычно означает, что вам нужно обратиться в службу поддержки Oracle STORAGE_ERROR Программе не хватает системной памяти

Системные исключения Исключение Причина возбуждения TIMEOUT_ON_RESOURCE Программа слишком долго ожидала доступности некоторого ресурса TOO_MANY_ROWS SELECT INTO в PL/SQL вернул более одной строки VALUE_ERROR PL/SOL встретил неправильное преобразование или усечение данных, или неправильное ограничение на данные ZERO_DIVIDE Попытка деления на нуль OTHERS Все прочие исключения и внутренние ошибки, которые не охватываются исключениями, опреде ленными в базовом блоке. Используется в тех слу чаях, когда вы точно не знаете, какое именованное исключение предстоит обрабатывать, и хотите обрабатывать любые возбуждаемые исключения

Исключения, определяемые программистом Исключение объявляется в секции объявлений. Собственные исключения используются для обработки ошибок, которые система не обнаруживает или не считает за ошибки. (например требуется ограничение на целостность)

Исключения, определяемые программистом. Пример. set serveroutput on DECLARE quantity 1 NUMBER : = 2; quantity 2 NUMBER : = 3; total NUMBER : = 0; quantity_must_positive EXCEPTION; FUNCTION find_cost (quant NUMBER) RETURN NUMBER IS BEGIN IF (quant > 0) THEN RETURN (quant * 20); ELSE RAISE quantity_must_positive; END IF; END find_cost; BEGIN total : = find_cost(quantity 2); total : = total + find_cost(quantity 1); EXCEPTION WHEN quantity_must_positive THEN dbms_output. put_line('Total until now: ' || total); dbms_output. put_line('Tried to use negative quantity '); END;

Хранимые процедуры Хранимая процедура – определенный набор инструкций, написанных на языке PL/SQL. Вызов процедуры приводит к выполнению содержащихся в ней инструкций. Процедура хранится в базе данных, поэтому и называется хранимой. Состоит из 2 основных частей: спецификация и тело процедуры.

Хранимые процедуры Спецификация процедуры включает в себя имя процедуры и описание ее входных и выходных данных. Входные и выходные данные называются формальными параметрами или формальными аргументами. Тело процедуры – блок PL/SQL кода.

Хранимые процедуры. Пример. Код на создание хранимой процедуры может быть таким: create procedure p. Salary(emp_id number, n. Salary number) AS BEGIN update hr. employees set salary = n. Salary where employee_id = emp_id; END; Код для вызова хранимой процедуры может выглядеть так: begin p. Salary(100, 25000); end;

Хранимые функции Отличие функции от процедуры: функция предназначена для возврата значения, которое может использоваться в боле крупном SQL операторе.

Хранимые функции. Пример. Код для создания функции может выглядеть так: create function f. Salary(emp_id IN number) RETURN number is n. Salary number(10, 2); BEGIN SELECT salary into n. Salary from employees where employee_id = emp_id; RETURN n. Salary; end; Код для применения этой функции может быть таким: begin dbms_output. put_line(f. Salary(100)); end;

Триггеры Триггер – процедура PL/SQL, которая выполняется автоматически, когда происходит некоторое заданное событие, называемое триггерным событием. Различия между триггерами и процедурами: 1. Триггеры нельзя вызывать из кода программы. Oracle вызывает их автоматически в ответ на определенное событие. 2. Триггеры не имеют список параметров 3. Спецификация триггера немного отличается от спецификации процедуры.

Триггеры Создание триггера CREATE OR REPLACE TRIGGER имя_триггера момент_срабатывания триггерное_событие ON имя_таблицы [WHEN триггерное_ограничение] [FOR EACH ROW] [DECLARE объявления] BEGIN операторы [EXCEPTION WHEN имя_исключения THEN …] END имя_триггера;

Триггеры Момент срабатывания определяет, когда будет срабатывать триггер: до (BEFORE) или после (AFTER) наступления триггерного события. Триггерное событие может принимать значения INSERT, UPDATE, DELETE. Триггерное ограничение – одно и более дополнительных условий, которые должны быть выполнены для срабатывания триггера. FOR EACH ROW – необходимость выполнить тело триггера для всех строк, затрагиваемых запускаемым оператором.

Триггеры. Пример. CREATE OR REPLACE TRIGGER test. Trg AFTER INSERT OR DELETE OR UPDATE ON customers DECLARE BEGIN INSERT INTO MILLER. ADT(USAL, TISP) VALUES(USER, SYSDATE); END test. Trg; Триггер создан и будет реагировать на события INSERT, DELETE, UPDATE возникающие при работе с таблицей MILLER. CUSTOMERSS после того как они произойдут

Пакеты PL/SQL Пакеты используются для совместного хранения полезных функций, процедур, типов записей и курсоров. Пакет имеет спецификацию и тело, которые можно создавать по отдельности.

Пакеты PL/SQL Спецификация пакета CREATE PACKAGE имя_пакета IS [объявление_переменных_и_типов] [спецификации курсоров] [спецификации_функций_и_процедур] END имя_пакета; Создание тела пакета CREATE OR REPLACE PACKAGE BODY имя_пакета IS [локальные объявления] [полные спецификации курсоров пакета] [полные спецификации функций_и_процедур пакета] BEGIN [выполняемые операторы] [EXCEPTION] [обработка_исключений] END [имя_пакета];

Пакеты PL/SQL. Пример. Код на создание пакета: create package pkg. Salary As function f. Salary(emp_id IN number) RETURN number; procedure p. Salary(emp_id number, n. Salary number); end pkg. Salary; Код на создание тела пакета: create package body pkg. Salary AS function f. Salary(emp_id IN number) RETURN number is n. Salary number(10, 2); BEGIN SELECT salary into n. Salary from employees where employee_id = emp_id; RETURN n. Salary; end; procedure p. Salary(emp_id number, n. Salary number) AS BEGIN update hr. employees set salary = n. Salary where employee_id = emp_id; END; end pkg. Salary;

Пакеты PL/SQL. Пример. Код на запуск функции из пакета: begin dbms_output. put_line(pkg. Salary. f. Salary(100)); end; Код на запуск процедуры из пакета : begin pkg. Salary. p. Salary(100, 26000); end;

Достоинства PL/SQL объединяет мощь и гибкость SQL (языка 4 GL) и процедурные конструкции языка 3 GL. PL/SQL означает Procedural Language/SQL (процедурный язык/SQL). Как видно из названия, PL/SQL расширяет возможности SQL, добавляя в него такие конструкции процедурных языков, как: Ø Переменные и типы данных (как предварительно определенные, так и определяемые пользователями) Ø Управляющие структуры, такие как условные операторы и циклы Ø Процедуры и функции Ø Объектные типы и методы Ø Процедурные конструкции объединяются с Oracle SQL, что дает в результате структурированный и эффективный язык программирования.