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

SQL> CREATE TABLE tab1 (at1 integer);

Table created.

SQL> DECLARE

2 l_at1 tab1.at1%TYPE;

3 BEGIN

4 l_at1 := 1;

5 INSERT INTO tab1 VALUES (l_at1);

6

7 l_at1 := 2;

8 INSERT INTO tab1 VALUES (l_at1);

9

10 l_at1 := 3;

11 INSERT INTO tab1 VALUES (l_at1);

12 DELETE FROM tab1 WHERE at1=1;

13

14 UPDATE tab1 SET at1=at1+1 WHERE at1=l_at1;

15

16 END;

17 /

PL/SQL procedure successfully completed.

SQL> SELECT * FROM tab1;

AT1

–

2

4

Используемые для выполнения команд INSERT, UPDATE, DELETE неявные курсоры тоже имеют атрибуты. Чтобы получить их значения, следует указывать имя курсора SQL%.

Таблица 4.Атрибуты неявного курсора.

SQL%FOUND

возвращает TRUE, если хотя бы одна строка была обработана DML-предложением SQL

SQL%NOTFOUND

возвращает TRUE, если ни одной строки не было обработано

SQL%ROWCOUNT

возвращает количество обработанных строк

SQL%ISOPEN

для неявных курсоров всегда возвращает FALSE, поскольку Oracle закрывает и открывает их автоматически

Эти атрибуты относятся к последнему использовавшемуся в программе неявному курсору, независимо от того, в каком блоке этот курсор использовался. До выполнения в программе первой команды PL/SQL с использованием неявного курсора атрибуты курсора с именем SQL% остаются неинициализированными (имеют значения UNKNOWN и NULL).

Наиболее часто используются атрибуты SQL%FOUND и SQL%ROWCOUNT, которые позволяют получить информацию о результатах обработки данных – сколько строк было обработано (добавлено, изменено или удалено) и были ли они вообще.

Приведем пример использования атрибутов неявных курсоров.

SQL> DECLARE

2 l_at1 tab1.at1%TYPE;

3 BEGIN

4

5 l_at1 := 1;

6

7 INSERT INTO tab1 VALUES (l_at1);

8 DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);

9

10 INSERT INTO tab1 SELECT * FROM tab1;

11 DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);

12

13 INSERT INTO tab1 SELECT * FROM tab1;

14 DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);

15

16 UPDATE tab1 SET at1=2 WHERE at1=1;

17 IF SQL%FOUND THEN

18 DBMS_OUTPUT.PUT_LINE('Строки изменялись');

19 ELSE

20 DBMS_OUTPUT.PUT_LINE('Строки не изменялись');

21 END IF;

22

23 END;

24 /

1

1

2

Строки изменялись

PL/SQL procedure successfully completed.

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

Рассмотрим еще две возможности языка PL/SQL: конструкцию RETURNING и использование записей PL/SQL в DML-командах. Эти возможности наглядно иллюстрируют удобство использования языка PL/SQL при работе с базами данных Oracle.

Конструкция RETURNING

Конструкция RETURNING позволяет получить новые значения данных в таблицах после их добавления или изменения. Например, после увеличения оклада сотрудника на 10% в дальнейших вычислениях в коде может понадобиться новое значение оклада. Конечно, можно сразу после изменения выполнить выборку данных по этому сотруднику, но это будет еще одна операция, на которую потребуются дополнительные расходы ресурсов. Конструкция RETURNING позволяет их избежать.

Конструкцию RETURNING часто используют для получения значения первичного ключа после добавления новой строки в таблицу с использованием последовательности.

SQL> CREATE SEQUENCE sq1 START WITH 1 INCREMENT BY 2;

Sequence created.

SQL> DECLARE

2 l_at1 tab1.at1%TYPE;

3 BEGIN

4 INSERT INTO tab1 VALUES(sq1.NEXTVAL) RETURNING at1 INTO l_at1;

5 DBMS_OUTPUT.PUT_LINE(l_at1);

6 INSERT INTO tab1 VALUES(sq1.NEXTVAL) RETURNING at1 INTO l_at1;

7 DBMS_OUTPUT.PUT_LINE(l_at1);

8 END;

9 /

1

3

PL/SQL procedure successfully completed.

Использование записей PL/SQL в DML-командах

В DML-командах языка PL/SQL можно использовать и параметры-записи PL/SQL:

для указания того, что в команде UPDATE следует изменить целиком строку таблицы, используется ключевое слово ROW;

в команде INSERT после ключевого слова VALUES вместо списка переменных скалярных типов со значениями всех столбцов добавляемой строки указывается одна переменная-запись PL/SQL, которая целиком «укладывается» в таблицу в виде новой строки.

SQL> CREATE TABLE tab1 (at1 INTEGER, at2 VARCHAR2(1));

Table created.

SQL> DECLARE

2 l_tab1 tab1%ROWTYPE;

3 BEGIN

4 l_tab1.at1 := 1;

5 l_tab1.at2 := 'a';

6 INSERT INTO tab1 VALUES l_tab1;

7 l_tab1.at1 := 2;

8 l_tab1.at2 := 'b';

9 INSERT INTO tab1 VALUES l_tab1;

10 l_tab1.at2 := 'c';

11 UPDATE tab1 SET ROW = l_tab1 WHERE at1=2;

12 END;

13 /

PL/SQL procedure successfully completed.

SQL> SELECT * FROM tab1;

AT1 AT2

– –

1 a

2 c

Рекомендуется используемые в DML-командах записи PL/SQL объявлять на основе схем таблиц с помощью атрибута %ROWTYPE. Если впоследствии схемы этих таблиц изменятся, то код PL/SQL останется работоспособным. Таким образом, использование в DML-командах одной записи PL/SQL вместо нескольких переменных скалярных типов приводит к тому, что код становится более компактным и повышается его надежность.

Формирование предложений SQL со связываемыми переменными