PL/SQL (Procedural Language / Structured Query Language) — язык программирования, процедурное расширение языка SQL, разработанное корпорацией Oracle.
PL/SQL даёт возможность использовать переменные, операторы, массивы, курсоры и исключения.
Стандартный SQL является специализированным декларативным языком программирования, то есть только описывает желаемый результат. На язык наложены определённые ограничения, такие как, например, отсутствие прямой поддержки циклов. PL/SQL же позволяет разработчикам обрабатывать данные в реляционной базе, используя императивный стиль программирования. Операторы SQL могут быть легко вызваны непосредственно из PL/SQL-процедуры, функции или триггера (иногда с некоторыми ограничениями).
Выполняемые единицы PL/SQL-кода имеют блочную структуру (такой код, с хорошо определенной структурой, проще сопровождать).
Программа на PL/SQL состоит из блоков (анонимных или поименованных). Блок может содержать вложенные блоки, называемые иногда подблоками. Общая форма PL/SQL-блока:
DECLARE
-- Описания блока, переменные, типы, курсоры и т.п. (опционально)
BEGIN
-- Непосредственно код программы
EXCEPTION
-- Обработка исключений (опционально)
END;
/* Многострочные
комментарии… */
-- Однострочный комментарий
Выполните самостоятельно следующий код.
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello World!');
END;
Данный анонимный блок должен вывести строку 'Hello World!'.
Пакет DBMS_OUTPUT позволяет выполнять операции ввода-вывода. В SQL Developer вывод можно увидеть во вкладке Script Output. Включить вывод результатов работы пакета DBMS_OUTPUT можно с помощью однократного выполнения следующей команды.
SET SERVEROUTPUT ON
Включите вывод результатов работы пакета DBMS_OUTPUT в SQL Developer кнопкой “Enable DBMS Output” на вкладке “DBMS Output”.
Объявление и инициализация переменных PL/SQL.
Переменные используются в следующих целях: временное хранение данных, манипулирование хранимыми значениями, повторное использование.
Для присвоения имен переменным используются идентификаторы.
Синтаксис
идентификатор [CONSTANT] тип_данных [NOT NULL] [:= | DEFAULT выражение];
Примеры
DECLARE
emp_hiredate DATE;
emp_deptno NUMBER(2) NOT NULL:= 10;
location VARCHAR2(13):= 'Atlanta';
c_comm CONSTANT NUMBER:= 1400;
Выполните самостоятельно следующий код.
DECLARE
Myname VARCHAR2(20);
BEGIN DBMS_OUTPUT.PUT_LINE('My name is: '||Myname);
Myname:= 'John';
DBMS_OUTPUT.PUT_LINE('My name is: '||Myname);
END;
Типы переменных.
1. Переменные PL/SQL:
1. Скалярные
2. Составные
3. Ссылочные
4. LOB (большие объекты)
2. Прочие переменные (не PL/SQL): связанные (bind) переменные.
Основные скалярные типы данных.
· CHAR [(максимальная_длина)]
· VARCHAR2 (максимальная_длина)
· LONG
· LONG RAW
· NUMBER [(точность, масштаб)]
· BINARY_INTEGER
· PLS_INTEGER
· BOOLEAN
· BINARY_FLOAT
· BINARY_DOUBLE
· DATE
· TIMESTAMP
· TIMESTAMP WITH TIME ZONE
· TIMESTAMP WITH LOCAL TIME ZONE
· INTERVAL YEAR TO MONTH
· INTERVAL DAY TO SECOND
Скалярные типы данных PL/SQL в основном совпадают с типами данных SQL. Но некоторые типы возможно использовать только в PL/SQL, например тип BOOLEAN.
Атрибут %TYPE
Атрибут %TYPE используется для объявления переменной на основе столбца таблицы базы данных или уже объявленной переменной. Этот атрибут придает гибкости при изменении типов данных (их удлинении). Например, если в одной из таблиц понадобилось изменить цифровой код на символьный или удлинить строковое значение, то при использовании атрибута %TYPE в хранимых программах не понадобиться прочесывать весь код, чтобы найти упоминания этого столбца и изменять тип данных на новый.
Синтаксис:
идентификатор таблица.имя_столбца%TYPE;
Примеры
...
emp_lname employees.last_name%TYPE;
balance NUMBER(7,2);
min_balance balance%TYPE:= 1000;
...
Логические переменные принимают три значения TRUE, FALSE, NULL. Подробнее будет рассказано после.
Связанные (bind) переменные
Связанные (bind) переменные создаются с помощью ключевого слова VARIABLE. Они используются в командах SQL и блоках PL/SQL и доступны даже после выполнения блока PL/SQL до окончания текущей сессии. Внутри блока или команды SQL при обращении к связанной переменной перед ней ставится двоеточие (:).
Пример.
VARIABLE emp_salary NUMBER
BEGIN
SELECT salary INTO:emp_salary
FROM employees WHERE employee_id = 178;
END;
PRINT emp_salary
SELECT first_name, last_name FROM employees
WHERE salary=:emp_salary;
Предложение SELECT INTO в отличие от предложения SELECT не только выполняет выборки, но и записывает полученные значения в указанные переменные. То есть позволяет сохранить данные.
Синтаксис.
SELECT список_выборки
INTO { имя_переменной [, имя_переменной ] ... | имя_записи }
FROM таблица [WHERE условие ];
Предложение INTO обязательно. Запросы должны возвращать одну и только одну строку.
Запросы манипулирования данными (команды INSERT, UPDATE, DELETE, MERGE) имеют такой же синтаксис как и в SQL.