Иван Задворьев - Язык PL/SQL

SQL> DECLARE

2 arg NUMBER; – Переменная для вычисления факториала

3 i NUMBER; – Переменная-счетчик

4 limit NUMBER := 1000000000; – Граница

5 text1 VARCHAR2(80) := 'n! числа, впервые превышающий 1000000000';

6

7 BEGIN

8 i := 0;

9 arg := 1;

10 LOOP

11 EXIT WHEN arg > limit;

12 arg := arg*(i+1);

13 i := i + 1;

14 END LOOP;

15 DBMS_OUTPUT.PUT_LINE(text1);

16 DBMS_OUTPUT.PUT_LINE(TO_CHAR(arg));

17 DBMS_OUTPUT.PUT_LINE('Искомое число = '||TO_CHAR(i));

18 END;

/

n! числа, впервые превышающий 1000000000

6227020800

Искомое число = 13

PL/SQL procedure successfully completed.

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

Если происходит зацикливание (выполнение бесконечного цикла без выхода из него), то программа 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||' руб.)');