Хотя язык SQL и является легким в изучении и обладает массой мощных функциональных возможностей, он не позволяет создавать такие процедурные конструкции, которые возможны в языках третьего поколения вроде C. Язык PL/SQL является собственным расширением языка SQL от Oracle и предлагает функциональность серьезного языка программирования. Одно из главных его преимуществ состоит в том, что он позволяет использовать в базе данных такие программные единицы, как процедуры и пакеты, и тем самым увеличивать возможность повторного использования кода и его производительность.
Базовый блок PL/SQL
Блоком в PL/SQL называется исполняемая программа. Блок кода PL/SQL, независимо от того, инкапсулируется он внутри какой-то программной единицы наподобие процедуры или задается в виде анонимного блока в свободной форме, состоит из следующих структур, которые представляют собой четыре ключевых оператора, только два из которых являются обязательными.
DECLARE
. Этот оператор является необязательным и представляет собой то место, в котором при желании объявляются переменные и курсоры программы.BEGIN
. Этот оператор является обязательным и указывает, что далее будут идти операторы SQL и PL/SQL, т.е. обозначает начало блока кода PL/SQL.EXCEPTION
. Этот оператор является необязательным и описывает методы обработки ошибок.END
. Этот оператор является обязательными и обозначает конец блока кода PL/SQL.
Ниже приведен пример простого блока кода PL/SQL:
SQL>
DECLARE isbn NUMBER(9)
BEGIN
isbn := 123456789;
insert into book values (isbn, 'databases', 59.99);
COMMIT;
END;
SQL>
Объявление переменных в PL/SQL
В операторе DECLARE можно объявлять как переменные, так и константы. Прежде чем использовать какую-либо переменную ее нужно обязательно объявить. Переменная в PL/SQL может представлять собой как переменную встроенного типа, такого как DATE, NUMBER, VARCHAR2 или CHAR, так и составного вроде VARRAY. Помимо этого, в PL/SQL еще применяются такие типы данных, как BINARY_INTEGER и BOOLEAN.
Ниже приведены некоторые типичные примеры объявления переменной в PL/SQL:
hired_date DATE;
emp_name VARCHAR2(30);
Помимо переменных также можно объявлять и константы, как показано в следующем примере:
tax_rate constant number := 0.08;
Еще можно использовать атрибут %TYPE и с его помощью указывать при объявлении переменной, что ее тип данных должен совпадать с типом данных определенного столбца таблицы:
emp_num employee.emp_id%TYPE;
Посредством атрибута %ROWTYPE можно указывать, что тип данных записи (строки) должен совпадать с типом данных определенной таблицы базы данных. Например, в следующем коде указано, что запись DeptRecord должна содержать все те же столбцы, что и таблица department, а типы данных и длина этих столбцов в ней должны выглядеть абсолютно идентично:
declare
v_DeptRecord department%ROWTYPE;
Написание исполняемых операторов PL/SQL
После оператора BEGIN можно начинать вводить все свои желаемые SQL-операторы. Выглядеть эти операторы должны точно так же, как обычные операторы SQL. При использовании операторов SELECT и INSERT в PL/SQL, правда, необходимо помнить об особенностях, о которых более подробно речь пойдет в следующих разделах.
Использование оператора SELECT в PL/SQL
При использовании оператора SELECT в PL/SQL нужно сохранять извлекаемые значения в переменных, как показано ниже:
DECLARE
name VARCHAR2(30);
BEGIN
SELECT employee_name INTO name FROM employees WHERE emp_id=99999;
END;
/
Использование DML-операторов в PL/SQL
Любые операторы INSERT, DELETE или UPDATE работают в PL/SQL точно так же, как в обычном SQL. Однако в PL/SQL после каждого из них можно также применять оператор COMMIT, как показано ниже:
BEGIN
DELETE FROM employee WHERE emp_id = 99999;
COMMIT;
END;
/
Обработка ошибок
В PL/SQL любая ошибка или предупреждение называется исключением (exception). В PL/SQL есть кое-какие определенные внутренне ошибки, но также допускается определять и свои собственные. При возникновении любой ошибки инициируется исключение, и управление переходит в отвечающий за обработку исключений раздел программы PL/SQL. В случае определения своих собственных ошибочных ситуаций необходимо обеспечивать инициирование исключений за счет применения специального оператора RAISE.
Ниже приведен пример использования оператора RAISE для обработки исключений:
DECLARE
acct_type INTEGER := 7;
BEGIN
IF acct_type NOT IN (1, 2, 3) THEN
RAISE INVALID_NUMBER; -- raise predefined exception
END IF;
EXCEPTION
WHEN INVALID_NUMBER THEN
ROLLBACK;
END;
/
Управляющие структуры в PL/SQL
В PL/SQL предлагается несколько видов управляющих структур (control structures), которые позволяют обеспечивать итерацию кода или условное выполнение определенных операторов. Все они кратко описаны в последующих разделах моего блога.
Условное управление
Главной разновидностью условной управляющей структуры в PL/SQL является оператор IF, который обеспечивает условное выполнение операторов. Он может применяться в одной из трех следующих форм: IF-THEN, IF-THEN-ELSE и IF-THEN-ELSEIF. Ниже приведен пример простого оператора IF-THEN-ELSEIF:
BEGIN
. . .
IF total_sales > 100000 THEN
bonus := 5000;
ELSEIF total_sales > 35000 THEN
bonus := 500;
ELSE
bonus := 0;
END IF;
INSERT INTO new_payroll VALUES (emp_id, bonus . . .);
END;
/
Конструкции циклов в PL/SQL
Конструкции циклов в PL/SQL позволяют обеспечивать итеративное выполнение кода либо заданное количество раз, либо до тех пор, пока определенное условие не станет истинным или ложным. В следующих подразделах описываются основные виды этих конструкций.
Простой цикл
Конструкция простого цикла подразумевает помещение набора SQL-операторов между ключевыми словами LOOP и END LOOP. Оператор EXIT завершает цикл. Конструкция простого цикла применяется тогда, когда точно неизвестно, сколько раз должен выполняться цикл. В случае ее применения решение о том, когда цикл должен завершаться, принимается на основании содержащейся между операторами LOOP и END LOOP логики.
В следующем примере цикл будет выполняться до тех пор, пока значение quality_grade не достигнет 6:
LOOP
. . .
if quality_grade > 5 then
. . .
EXIT;
end if;
END LOOP;
Еще один простой вид цикла позволяет выполнять конструкция LOOP...EXIT...WHEN, в которой длительность цикла регулируется оператором WHEN. Внутри WHEN указывается условие, и когда это условие становится истинным, цикл завершается. Ниже показан простой пример:
DECLARE
count_num NUMBER(6);
BEGIN
count_num := 1;
LOOP
dbms_output.put_line(' This is the current count '|| count_num);
count_num := count_num + 1;
Exit when count_num > 100;
END LOOP;
END;
Цикл WHILE
Цикл WHILE указывает, что определенный оператор должен выполняться до тех пор, пока определенное условие остается истинным. Обратите внимание на то, что условие вычисляется за пределами цикла, и вычисляется оно всякий раз, когда выполняются операторы, указанные между операторами LOOP и END LOOP. Когда условие перестает быть истинным, происходит выход из цикла. Ниже приведен пример цикла WHILE:
WHILE total <= 25000
LOOP
. . .
SELECT sal INTO salary FROM emp WHERE . . .
total := total + salary;
END LOOP;
Цикл FOR
Цикл FOR применяется тогда, когда требуется, чтобы оператор выполнялся определенное количество раз. Он имитирует классический цикл do, который существует в большинстве языков программирования. Ниже приведен пример цикла FOR:
BEGIN
FOR count_num IN 1..100
LOOP
dbms_output.put_line('The current count is : '|| count_num);
END LOOP;
END;
Записи в PL/SQL
Записи (records) в PL/SQL позволяют воспринимать взаимосвязанные данные как одно целое. Они могут содержать поля, каждое из которых может представлять отдельный элемент. Можно использовать атрибут ROW%TYPE и с его помощью объявлять записью столбцы определенной таблицы, что подразумевает применение таблицы в качестве шаблона курсора, а можно создавать и свои собственные записи. Ниже приведен простой пример записи:
DECLARE
TYPE MeetingTyp IS RECORD (
date_held DATE,
location VARCHAR2(20),
purpose VARCHAR2(50));
Для ссылки на отдельное поле внутри записи применяется точечное обозначение, как показано ниже:
MeetingTyp.location
Использование курсоров
Курсором (cursor) в Oracle называется указатель на область в памяти, в которой содержится результирующий набор SQL-запроса, позволяющий индивидуально обрабатывать содержащиеся в результирующем наборе строки. Курсоры, которые используются Oracle при выполнении DML-операторов, называются неявными, а курсоры, которые создают и используют разработчики приложений — явными.
Неявные курсоры
Неявные курсоры автоматически применяются Oracle всякий раз, когда в коде PL/SQL используется оператор SELECT. Они могут использоваться лишь в тех операторах, которые возвращают одну строку. В случае если SQL-оператор возвращает более одной строки, будет выдаваться сообщение об ошибке.
В приведенном ниже блоке кода PL/SQL оператор SELECT, например, предусматривает применение неявного курсора:
DECLARE
emp_name varchar2(40);
salary float;
BEGIN
SELECT emp_name, salary FROM employees
WHERE employee_id=9999;
dbms_output.put_line('employee_name : '||emp_name||'
salary :'||salary);
END;
/
Явные курсоры
Явные курсоры создаются разработчиком приложения и облегчают операции с набором строк, которые могут обрабатываться друг за другом. Они применяются всегда, когда известно, что SQL-оператор будет возвращать более одной строки. Обратите внимание, что явный курсор необходимо всегда объявлять в начале блока PL/SQL внутри раздела DECLARE, в отличие от неявного курсора, на который никогда не нужно ссылаться в коде.
После объявления явного курсора он будет проходить через следующие этапы обработки.
- Конструкция OPEN будет определять строки, которые находятся в курсоре, и делать их доступными для программы PL/SQL.
- Команда FETCH будет извлекать данные из курсора в указанную переменную.
- По завершении процесса обработки курсор должен всегда закрываться явным образом.
В листинге А.4 показан пример создания курсора и затем его использования внутри цикла.
DECLARE
/* Курсор select_emp объявляется явно */
CURSOR select_emp IS
select emp_id, city
from employees
where city = 'DALLAS';
v_empno employees.emp_id%TYPE;
v_empcity employees.city%TYPE;
BEGIN
/* Курсор select_emp открывается */
Open select _emp;
LOOP
/* Данные курсора select_emp извлекаются в переменную v_empno */
FETCH select_emp into v_empno;
EXIT WHEN select_emp%NOTFOUND;
dbms_output.put_line(v_empno|| ','||v_empcity);
END LOOP;
/* Курсор select_emp закрывается */
Close select_emp;
END;
/
Атрибуты курсоров
В примере, приведенном в листинге А.4, для указания того, когда цикл должен завершаться, используется специальный атрибут курсора %NOTFOUND. Атрибуты курсоров очень полезны при работе с явными курсорами. Наиболее важные из них перечислены ниже.
- %ISOPEN. Булевский атрибут, который после завершения выполнения SQL-оператора возвращает false. До тех пор, пока курсор остается открытым, он возвращает true.
- %FOUND. Булевский атрибут, который выполняет проверку на предмет наличия подходящих для SQL-оператора строк, т.е. остались ли у курсора еще какие-то строки для извлечения.
- %NOTFOUND. Булевский атрибут, который сообщает о том, что не удалось обнаружить ни одной подходящей для SQL-оператора строки, т.е. у курсора больше не осталось никаких строк для извлечения.
- %ROWCOUNT. Атрибут, который возвращает информацию о том, сколько курсору удалось извлечь строк на текущий момент.
Курсорный цикл FOR
Обычно при использовании явных курсоров требуется открывать курсор, извлекать данные и по завершении закрывать курсор. Курсорный цикл FOR позволяет выполнять эти процедуры по открытию, извлечению и закрытию автоматически, чем очень сильно упрощает дело. В листинге А.5 показан пример применения конструкции курсорного цикла FOR.
DECLARE
CURSOR emp_cursor IS
SELECT emp_id, emp_name, salary
FROM employees;
v_emp_info employees%RowType;
Begin
FOR emp_info IN emp_cursor
LOOP
dbms_output.put_line ('Employee id : '||emp_id||'Employee
name : '|| emp_name||'Employee salary :'||salary);
END LOOP;
END;
/
Курсорные переменные
Курсорные переменные указывают на текущую строку в многострочном результирующем наборе. В отличие от обычного курсора, однако, курсорная переменная является динамической, что позволяет присваивать ей новые значения и передавать ее другим процедурами и функциям. Создаются курсорные переменные в PL/SQL следующим образом.
Сначала определяется тип REF CURSOR, как показано ниже:
DECLARE
TYPE EmpCurTyp IS REF CURSOR RETURN dept%ROWTYPE;
Затем объявляются сами курсорные переменные типа EmpCurType в анонимном блоке кода PL/SQL либо в процедуре (или функции):
DECLARE
TYPE EmpRecTyp IS RECORD (
Emp_id NUMBER(9),
emp_name VARCHAR2(3O),
sal NUMBER(7,2));
TYPE EmpCurTyp IS REF CURSOR RETURN EmpRecTyp;
emp_cv EmpCurTyp; -- объявление курсорной переменной
Процедуры, функции и пакеты
Процедуры в PL/SQL могут применяться для выполнения различных DML-операций. Ниже приведен пример простой процедуры Oracle:
create or replace procedure new_employee (emp_id number,
last_name varchar(2), first_name varchar(2))
is
begin
insert into employees values (emp_id, last_name, first_name);
end new_employee;
/
В отличие от процедур, функции в PL/SQL возвращают значение, как показано в следующем примере:
CREATE OR REPLACE FUNCTION sal_ok (salary REAL, title VARCHAR2) RETURN BOOLEAN
IS
min_sal REAL;
max_sal REAL;
BEGIN
SELECT losal, hisal INTO min_sal, max_sal FROM sals
WHERE job = title;
RETURN (salary >= min_sal) AND (salary <= max_sal);
END sal_ok;
Пакеты (packages) в Oracle представляют собой объекты, которые обычно состоят из нескольких взаимосвязанных процедур и функций и, как правило, применяются для выполнения какой-нибудь функции приложения путем вызова всех находящихся внутри пакета взаимосвязанных процедур и функций. Пакеты являются чрезвычайно мощным средством, поскольку могут содержать большие объемы функционального кода и многократно выполняться несколькими пользователями.
Каждый пакет обычно состоит из двух частей: спецификации и тела. В спецификации пакета объявляются все входящие в его состав переменные, курсоры и подпрограммы (процедуры и функции), а в теле пакета содержится фактический код этих курсоров и подпрограмм.
В листинге А.6 приведен пример простого пакета Oracle.
/* Сначала идет спецификация пакета /*
create or replace package emp_pkg as
type list is varray (100) of number (5);
procedure new_employee (emp_id number, last_name
varchar2, first_name varchar2);
procedure salary_raise (emp_id number, raise number);
end emp_pkg;
/
/* Далее следует тело пакета */
create or replace package body emp_pkg as
procedure new_employee (emp_id number,
last_name varchar(2), first_name varchar(2) is
begin
insert into employees values (emp_id, last_name, first_name);
end new_employee;
procedure salary_raise (emp_num number, raise_pct real) is
begin
update employees set salary = salary * raise_pct
where emp_id = emp_num;
end salary_raise;
end emp_pkg;
/
При желании использовать пакет emp_pkg для награждения какого-то сотрудника надбавкой к зарплате, все, что потребуется сделать — выполнить следующую команду:
SQL> EXECUTE emp_pkg.salary_raise(99999, 0.15);