Если происходит зацикливание (выполнение бесконечного цикла без выхода из него), то программа PL/SQL «уходит в себя» («повисает»). Для прекращения выполнения такой программы в SQL*Plus следует нажать на клавиатуре комбинацию клавиш Ctrl+C:
SQL> BEGIN LOOP NULL; END LOOP; END;
2 /
^C
Цикл WHILE
Цикл WHILE с предусловием позволяет выполнить одну и ту же последовательность команд PL/SQL пока истинно проверяемое предусловие.
С помощью цикла WHILE найдем число, факториал которого является наименьшим числом, впервые превышающим 1 000 000 000 000:
SQL> DECLARE
2 arg NUMBER; Переменная для вычисления факториала
3 i NUMBER; Переменная-счетчик
4 limit NUMBER := 1000000000000; Граница
5 text1 VARCHAR2(80):='n! числа, впервые превышающий 1000000000000;
6
7 BEGIN
8 i := 0;
9 arg := i;
10 WHILE arg < 1000000000000 LOOP
11 arg := arg*(i+1);
12 i := i + 1;
13 END LOOP;
14 DBMS_OUTPUT.PUT_LINE(text1);
15 DBMS_OUTPUT.PUT_LINE(TO_CHAR(arg));
16 DBMS_OUTPUT.PUT_LINE('Искомое число = '||TO_CHAR(i));
17 END;
/
n! числа,впервые превышающий 1000000000000
1307674368000
Искомое число = 15
PL/SQL procedure successfully completed.
Отметим, что если условие цикла WHILE изначально ложно (FALSE), то цикл не выполнится ни разу.
Цикл FOR
Цикл FOR («цикл со счетчиком»), используется в том случае, когда известно, сколько раз нужно выполнить итерацию цикла. Приведем пример вычисления факториала заданного числа.
SQL> DECLARE
2 arg NUMBER := 1;
3 n NUMBER := 20;
4 text1 VARCHAR2(30) := 'Факториал числа '||n||' = ';
5 BEGIN
6 FOR i IN 1..n LOOP
7 arg := arg*i;
8 END LOOP;
9 DBMS_OUTPUT.PUT_LINE(text1||TO_CHAR(arg));
10 END;
/
Факториал числа 20 = 2432902008176640000
PL/SQL procedure successfully completed.
Обратите внимание, что счетчикуправляющую переменную цикла (в данном случае i) объявлять в разделе объявлений не нужно, она объявляется автоматически с областью видимости между ключевыми словами LOOP и END LOOP.
При рассмотрении циклов FOR обычно возникают два вопроса:
есть ли возможность сделать так, чтобы значения счетчика цикла не возрастали, а уменьшались?
есть ли возможность нетривиальных, то есть не на единицу, приращений шага счетчика цикла?
Цикл с ключевым словом REVERSE
Цикл со счетчиком, кратным 10
SQL> BEGIN
2 FOR i IN REVERSE 1..5 LOOP
3 DBMS_OUTPUT.PUT_LINE(i);
4 END LOOP;
5 END;
6 /
5
4
3
2
1
PL/SQL procedure successfully completed.
SQL> BEGIN
2 FOR i IN 1..20 LOOP
3 IF MOD(i,10)=0 THEN
4тело цикла
5 DBMS_OUTPUT.PUT_LINE(i);
6 END IF;
7 END LOOP;
8 END;
9 /
10
20
PL/SQL procedure successfully completed.
На оба вопроса ответы положительныетакие возможности в языке PL/SQL имеются. Для обратного цикла в конструкции FOR LOOP следует указать ключевое слово REVERSE. Для нетривиальных приращений нужно внутри цикла с помощью команды IF просто пропускать шаги с ненужными значениями счетчика. Счетчик цикла всегда изменяется на единицу, просто на некоторых шагах цикла ничего делать не нужно.
Команда CONTINUE
Команда CONTINUE появилась в версии Oracle 11g. При истинности условия, записанного в конструкции WHEN команды CONTINUE, выполнение текущей итерации цикла прекращается и происходит переход на начало следующей итерации.
Благодаря команде CONTINUE можно, например, вынести проверки в начало цикла. Перепишем приведенный выше пример с нетривиальным приращением счетчика цикла FOR без помещения кода обработки в условную команду IF:
BEGIN
FOR i IN 1..20 LOOP
CONTINUE WHEN MOD(i,10)!=0;
DBMS_OUTPUT.PUT_LINE(i);
END LOOP;
END;
Работа с коллекциями
Коллекции относятся к составным типам данных PL/SQL. Так как основная операция с коллекциейэто перебор и обработка всех ее элементов, то естественно познакомиться с особенностями работы с коллекциями после того, как рассмотрены циклы.
Виды и свойства коллекций
Коллекция называется ограниченной, если заранее определены границы возможных значений индексов ее элементов. В противном случае коллекция называется неограниченной.
В PL/SQL есть три вида коллекций:
ассоциативные массивы (associative array)неограниченные коллекции, объявляемые только в программах PL/SQL (поэтому в литературе иногда эти коллекции называются таблицами PL/SQL);
вложенные таблицы (nested tables)неограниченные коллекции, типы данных на основе которых могут создаваться как объекты баз данных и объявляться в программах PL/SQL;
массивы переменной длины (variable-size array, VARRAY)ограниченные коллекции, типы данных на основе которых могут создаваться как объекты баз данных с помощью DDL-команд языка SQL и объявляться в программах PL/SQL.
Коллекция называется плотной, если все ее элементы, от первого до последнего, определены и им присвоены некоторые значения, включая NULL. Если же у коллекции в диапазоне индексов есть пропуски (какие-то элементы коллекции отсутствуют), то коллекция называется разреженной. Массивы VARRAY всегда являются плотными. Вложенные таблицы первоначально всегда плотные, но по мере удаления некоторых элементов становятся разреженными. Таблицы PL/SQL могут быть разреженными.
Вне зависимости от вида коллекции, все ее элементы будут одного типа данных, то есть коллекции PL/SQL являются однородными.
Доступ к элементам коллекций PL/SQL всех трех видов осуществляется по их целочисленным индексам. Таблицы PL/SQL кроме чисел также могут индексироваться символьными строками.
Работа с таблицей PL/SQL с помощью встроенных методов
Для иллюстрации техники работы с таблицей PL/SQL покажем, как ее объявить, затем создадим два элемента таблицы и переберем их в цикле:
SQL> DECLARE
2 TYPE t_job IS RECORD (position VARCHAR2(100),salary INTEGER);
3 TYPE t_job_table IS TABLE OF t_job INDEX BY PLS_INTEGER;
4 TYPE t_person IS RECORD (surname VARCHAR2(30),jobs t_job_table);
5 l_person t_person;
6 l_job t_job;
7 l_job_table t_job_table;
8 l_row_index PLS_INTEGER;
9 BEGIN
10 l_person.surname := 'Ильинский К.В.';
11
12 l_job := NULL;
13 l_job.position := 'инженер';
14 l_job.salary := 50000;
15 l_job_table(9) := l_job;
16
17 l_job := NULL;
18 l_job.position := 'старший инженер';
19 l_job.salary := 60000;
20 l_job_table(267) := l_job;
21
22 l_person.jobs := l_job_table;
23
24 DBMS_OUTPUT.PUT_LINE('Сотрудник: '||l_person.surname);
25 l_row_index := l_person.jobs.first();
26 WHILE l_row_index IS NOT NULL LOOP
27 DBMS_OUTPUT.PUT_LINE(l_person.jobs(l_row_index).position
28 ||' ('||l_person.jobs(l_row_index).salary||' руб.)');
29 l_row_index := l_person.jobs.next(l_row_index);
30 END LOOP;
31
32 END;
33 /
Сотрудник: Ильинский К.В.
инженер (50000 руб.)
старший инженер (60000 руб.)
PL/SQL procedure successfully completed.
Таблицы PL/SQL являются разреженными. Чтобы подчеркнуть это, в примере выше специально использованы случайно выбранные индексы 9 и 267, а не 1 и 2. Для перебора таблицы PL/SQL использованы ее встроенные методы FIRST и NEXT.
Коллекции PL/SQL имеют восемь встроенных методов.
Таблица 1. Встроенные методы коллекций PL/SQL.
Метод коллекции
Описание метода
COUNT (функция)
возвращает текущее число элементов в коллекции
DELETE (процедура)
удаляет из коллекции один или несколько элементов
EXISTS (функция)
определяет, существует ли в коллекции заданный элемент
EXTEND (процедура)
увеличивает количество элементов во вложенной таблице или массиве переменной длины
FIRST, LAST (функции)
возвращают индексы первого (FIRST) и последнего (LAST) элемента в коллекции
LIMIT (функция)
возвращает максимальное количество элементов в массиве переменной длины
PRIOR, NEXT (функции)
возвращают индексы элементов, предшествующих заданному (PRIOR) и следующему за ним (NEXT).
TRIM (процедура)
удаляет элементы, начиная с конца коллекции
Рекомендуется перебор элементов коллекций осуществлять с помощью методов FIRST и NEXT, а не с помощью циклов со счетчиком FOR, исходя из ожидаемой плотности коллекции. Цикл FOR перебирает весь заданный диапазон индексов подряд, что может привести к ошибкеобращению к отсутствующему элементу. Метод NEXT перемещается по индексам только «живых» элементов и ошибок из-за пропусков в нумерации не будет.
Индексы-строки таблиц PL/SQL
В версии Oracle 9i появилась возможность использовать для индексирования таблиц PL/SQL символьные строки. Это очень удобно, например, для работы со справочниками, в которых и коды и термины являются строками.
Рассмотрим пример.
SQL> DECLARE
2 TYPE t_tab IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(2);
3 l_tab t_tab;
4 l_code varchar2(3) := 'MD';
5 BEGIN
6заполняем таблицу PL/SQL
7 l_tab('UA') := 'Украина';