Взаимодействие с базой данных: статический SQL

Взаимодействие программы на PL/SQL с БД может осуществляться тремя разными методами:

 

1) с помощью статических операторов SQL (DML, DCL и LOCK TABLE)

2) с помощью динамически порождаемых операторов SQL

3) с помощью курсоров

 

В этой главе рассмотрен первый метод (операторы DML). Существенное расширение его возможностей будет рассмотрено после темы «Коллекции».

 

Элементами PL/SQL являются DML-операторы SQL (SELECT, INSERT, UPDATE, DELETE и MERGE), подпадающие под определение «встроенного SQL» в стандарте ANSI, однако в рамках SQL диалекта Oracle. С точки зрения PL/SQL они являются статическими конструкциями (в отличие от «динамического SQL», о котором ниже).

 

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

 

SELECT список_столбцов INTO список_переменных_или_запись

FROM отбор_строк_как_обычно;

 

INSERT INTO табличное_выражение [(перечень_столбцов)] {VALUES (список_значений)| оператор_ SELECT }

[ RETURNING список_выражений INTO список_переменных_или_запись ];

 

UPDATE табличное_выражение SET присвоение [WHERE условие ]

[ RETURNING список_выражений INTO список_переменных_или_запись ];

 

DELETE FROM табличное_выражение [WHERE условие ]

[ RETURNING список_выражений INTO список_переменных_или_запись ];

 

MERGE INTO остальные_конструкции_предложения_ MERGE_как_обычно;

 

(Оператор MERGE – начиная с версии 9).

 

Следуя стандарту ANSI, имя_переменной выше может быть именем скалярной переменной или записи, а список_переменных – список скаляров. Начиная с версии 9 СУБД это могут быть и массивы, однако для этого нужно использовать специальную синтаксическую конструкцию, о которой речь позже.

 

Пример использования:

 

DECLARE

employee emp.ename%TYPE;

BEGIN

           SELECT ename INTO employee FROM emp WHERE empno = 7369;

           DBMS_OUTPUT.PUT_LINE (‘Employee ‘ || employee || ’ selected’);

 

           INSERT INTO emp (empno, ename) VALUES (1111, ‘BUSH’)

           RETURNING ename INTO employee;

           DBMS_OUTPUT.PUT_LINE (‘Employee ‘ || employee || ’ inserted’);

 

           UPDATE emp SET ename = ’LADEN’ WHERE empno = 1111

           RETURNING ename INTO employee;

           DBMS_OUTPUT.PUT_LINE (‘Employee ‘ || employee || ’ replaced him’);

 

           DELETE FROM emp WHERE empno = 1111

           RETURNING ename INTO employee;

           DBMS_OUTPUT.PUT_LINE (‘Employee ‘ || employee || ’ deleted’);

END;

/

 

Еще пример:

 

CREATE SEQUENCE seq;

 

DECLARE str VARCHAR2 (100);

BEGIN

INSERT INTO emp (empno, ename) VALUES (seq.NEXTVAL, 'BUSH')

RETURNING 'Inserted number was ' || TO_CHAR (empno) INTO str;

DBMS_OUTPUT.PUT_LINE (str);

 

DELETE FROM emp WHERE ename = 'BUSH'

RETURNING 'Employee '

     || ename

     || ' is deleted for his job is '

     || NVL (job, 'NULL')

INTO str;

DBMS_OUTPUT.PUT_LINE (str);

END;

/

 

 

6.1. Использование записей вместо (списка) скаляров

В версии 9.2 появилась возможность добавляемые или изменяемые значения указывать не списком скалярных переменных, а записью:

 

DECLARE

employee emp%ROWTYPE;

BEGIN

SELECT * INTO employee FROM emp WHERE empno = 7369;

 

employee.empno:= 1111;

INSERT INTO emp VALUES employee;

 

employee.empno:= 2222;

UPDATE emp SET ROW = employee WHERE empno = 1111;

 

DELETE FROM emp WHERE empno = 2222;

END;

/

 

(Использование записей во фразе INTO предложения SELECT и во фразе RETURNING допускалось и раньше).

 

Такое употребление имеет определенные ограничения, в частности запись может состоять только из скаляров (в частности, не может быть вложенной).

 

Регулирование изменений в БД

В программах PL/SQL можно непосредственно (статически) использовать DCL-операторы (COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION), а также оператор LOCK TABLE.

 

 

Управление транзакциями

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

 

 

COMMIT

COMMIT [WORK] [COMMENT текст ];

 

Слово WORK используется только для читаемости кода, а COMMENT текст может использоваться при разборе незавершившихся распределенных транзакций.

 

 

ROLLBACK

ROLLBACK [WORK] [TO SAVEPOINT] имя _контрольной _точки

 

Можно откатить транзакцию до контрольной точки (через контрольные точки, выполненные позднее) или до начала транзакции.

 

 

SAVEPOINT

SAVEPOINT имя_контрольной_точки;

 

Создает контрольную точку в пределах транзакции. Если имя повторить другим оператором SAVEPOINT, указанная контрольная точка “передвинется” вперед.

 

 

SET TRANSACTION

SET TRANSACTION READ ONLY;

 

Оператор дает начало “читающей” транзакции, в течение которой сеансу доступны только изменения, сделанные до ее точки отсчета. Завершение транзакции осуществляется по COMMIT или ROLLBACK. В пределах “читающей” транзакции можно выполнять только операторы

 

- LOCK TABLE

- SELECT, SELECT INTO

- OPEN

- FETCH

- CLOSE

- COMMIT/ROLLBACK

 

Выдача INSERT или UPDATE приведет к ошибке.

 

«Читающие» транзакции полезно использовать при составлении отчетов.

 

 

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

 

Аналогично READ ONLY обеспечивает целостность данных при чтении в пределах транзакции (а не SQL-оператора), но допускает модификацию данных.

 

 

SET TRANSACTION USE ROLLBACK SEGMENT имя _сегмента;

 

Приписывает только начавшейся транзакции конкретный сегмент отката, что часто используется в интенсивно модифицирующих транзакциях.

 

 

Блокировки

Блокировки таблицы выполняются SQL-предложением LOCK TABLE:

 

LOCK TABLE список_таблиц IN вид_блокировки MODE [NOWAIT];

 

Явно блокирует перечисленные в списке через запятую таблицы (полностью, а не на уровне строки) в указанном режиме. вид_блокировки может быть следующим:

 

- ROW SHARE

- ROW EXCLUSIVE

- SHARE UPDATE

- SHARE

- SHARE ROW EXCLUSIVE

- EXCLUSIVE

 

Если указано NOWAIT, то СУБД не переведет остальные запросы к таблице в состояние ожиданния, пока блокировка не будет снята (стандартная ситуация), а выдаст сообщение об ошибке ORA-00054.

 

 

Автономные транзакции

Начиная с версии 8.1 отдельные блоки кода можно выполнять в режиме автономной транзакции, на которую не будет распространяться откат или фиксация, осуществляемые для основной транзакции:

 

PROCEDURE main IS

BEGIN

           COMMIT;

           UPDATE …                     -- начало основной транзакции

           DELETE …

           some _ procedure;          -- начало автономной транзакции

           SELECT …

           INSERT …

           ROLLBACK;                  -- конец основной транзакции

END;

 

PROCEDURE some_procedure IS

           PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN                                              -- основная транзакция приостановлена

           SELECT …

           INSERT …                      -- начало автономной транзакции

           UPDATE …

           DELETE …

           COMMIT;                       -- конец автономной транзакции; фиксируются только

                                                           -- изменения, сделанные в рамках этой процедуры;

END;                                                 -- основная транзакция возобновляется

 

Правила видимости данных, измененных в БД основной и автономной транзакциями, соответствуют обычным правилам видимости для двух самостоятельных транзакций. То же касается регулирования изменений одних и тех же данных. Если основная транзакция заблокировала какую-нибудь таблицу, может возникнуть тупиковая ситуация. Избежать ее можно, используя указание NOWAIT в предложениях SELECT … FOR UPDATE. Блокировка может возникнуть в том числе вследствие существования межтабличной связи «внешний ключ»; см. пример ниже.

 

Упражнение. Что получим в результате следующих действий? Проверить в SQL*Plus.

 

Что видно из автономной транзакции:

 

SET SERVEROUTPUT ON

 

-- изменяем зарплату:

UPDATE emp SET sal = sal * 10 WHERE ename = 'SMITH';

 

-- автономная транзакция видит старое значение:

DECLARE

 PRAGMA AUTONOMOUS_TRANSACTION;

 salary emp.sal%TYPE;

BEGIN

 SELECT sal INTO salary FROM emp where ename = 'SMITH';

 DBMS_OUTPUT.PUT_LINE (salary);

END;

/

 

-- а основная транзакция продолжает видеть новое значение:

SELECT sal FROM emp where ename = 'SMITH';

 

ROLLBACK;

 

Построение транзакций:

 

DECLARE

 PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

 INSERT INTO emp (empno, deptno) VALUES (2222, 40);

END;

/

-- почему возникла ошибка?

 

DECLARE

 PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

 INSERT INTO emp (empno, deptno) VALUES (2222, 40);

 ROLLBACK;

END;

/

-- OK.

 

INSERT INTO dept (deptno) VALUES (50);

 

DECLARE

 PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

 INSERT INTO emp (empno, deptno) VALUES (2222, 50);

 ROLLBACK;

END;

/

-- почему возникла ошибка?  

 

ROLLBACK;

 

Поставьте комментарий перед INSERT INTO dept …. -- почему возникла новая ошибка?

 

INSERT INTO dept (deptno) VALUES (50);

 

DECLARE

 PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

 INSERT INTO emp (empno, deptno) VALUES (2222, 40);

 ROLLBACK;

END;

/

-- OK.

 

ROLLBACK;

 

Что видно из основной транзакции:

-- исходная зарплата:

SELECT sal FROM emp WHERE ename = 'SMITH';

-- изменяем зарплату в автономной транзакции:

DECLARE

 PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

 UPDATE emp SET sal = sal * 10 WHERE ename = 'SMITH';

 COMMIT;

END;

/

 

-- увидим это изменение из основной?

SELECT sal FROM emp WHERE ename = 'SMITH';

-- такая же проверка, только в основной транзакции READ ONLY:

SET TRANSACTION READ ONLY;

 

DECLARE

 PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

 UPDATE emp SET sal = sal / 10 WHERE ename = 'SMITH';

 COMMIT;

END;

/

 

-- а теперь увидим изменение?

SELECT sal FROM emp WHERE ename = 'SMITH';

 

ROLLBACK;

-- вышли из транзакции READ ONLY

SELECT sal FROM emp WHERE ename = 'SMITH';

 

Объяснить разницу в результатах до ROLLBACK и после.

 

Тот же эффект даст использование транзакции ISOLATION LEVEL SERIALIZABLE (но, в отличие от READ ONLY, не будет препятствовать внесению изменений в БД из основной).

 

 

Встроенный динамический SQL

В Oracle имеется два средства для динамического порождения программой предложений SQL:

 

- «встроенный динамический SQL» (native dynamic SQL, начиная с версии Oracle 8.1) и

- встроенный пакет DBMS_SQL (имелся и раньше)

 

Пакет DBMS_SQL обладает несколько большей функциональностью, но существенно более сложен в использовании.