PL/SQL позволяет извлекать информацию из базы данных Oracle с помощью команды SELECT и вносить в нее изменения, используя язык обработки данных DML Блок PL/SQL не является единицей транзакции. Для фиксации или отмены изменений используются команды COMMIT, ROLLBACK, которые могут быть выполнены как в блоках, так и независимо от них. Язык определения данных DDL и язык управления данными DCL PL/SQL не поддерживает. Однако существует встроенный пакет Oracle DBMS_SQL, который позволяет выполнять эти команды.
Для извлечения данных из базы данных Oracle используется команда SELECT с обязательным предложением INTO со списком переменных, которым присваиваются извлеченные данные. Запрос должен заканчиваться точкой с запятой и возвращать только одну строку, иначе возникнет ошибка. Для извлечения данных с помощью PL/SQL доступен полный синтаксис для команды SELECT.
SELECT список_выбораINTO список_переменных | имя_записиFROM таблица_базы_данных [...];
список_выбора – список столбцов таблицы базы данных. Может содержать выражения, однострочные и групповые функции
список_переменных – список скалярных переменных, которым присваиваются возвращаемые данные
имя_записи – имя записи PL/SQL для хранения возвращаемых данных
таблица_базы_данных – имя таблицы базы данных
Список переменных в предложении INTO должен содержать столько переменных, сколько столбцов указано в списке выбора SELECT. Причем, порядок перечисления должен совпадать, а типы данных должны быть совместимы (это можно обеспечить посредством атрибута %TYPE).
Для хранения данных, возвращаемых запросом, можно использовать запись PL/SQL. Если необходимо сохранить значения всех столбцов таблицы, можно воспользоваться атрибутом %ROWTYPE.
SQL> DECLARE
2 emp_rec emp%ROWTYPE;
3 BEGIN
4 SELECT * INTO emp_rec FROM emp WHERE ename='ADAMS';
5 DBMS_OUTPUT.put_line(emp_rec.ename);
6 DBMS_OUTPUT.put_line(emp_rec.job);
7 END;
8 /
ADAMS
CLERK
PL/SQL procedure successfully completed.
Запрос в PL/SQL должен возвращать одну и только одну строку. Ситуации, когда запрос возвращает несколько строк или не возвращает ни одной, рассматривается как исключение, прекращающее выполнение блока PL/SQL:
· TOO_MANY_ROWS – возникает, если команда SELECT возвращает более одной записи
SQL> DECLARE
2 emp_rec emp%ROWTYPE;
3 BEGIN
4 SELECT * INTO emp_rec FROM emp;
5 END;
6 /
DECLARE
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 4
· NO_DATA_FOUND – возникает, если команда SELECT не возвращает ни одной записи
SQL> DECLARE
2 emp_rec emp%ROWTYPE;
3 BEGIN
4 SELECT * INTO emp_rec FROM emp WHERE ename='adams';
5 END;
6 /
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4
Чтобы избежать исключений команды SELECT, необходимо использовать групповые функции в списке выбора SELECT или ограничивать количество выбираемых строк с помощью предложения WHERE.
Имена идентификаторов не должны совпадать с именами столбцов таблицы базы данных, т.к. PL/SQL в первую очередь проверяет, является ли идентификатор столбцом базы данных. Возникновение подобной ситуации в предложении WHERE вызовет неоднозначность, которая приведет к незапланированному исключению.
SQL> DECLARE
2 emp_rec emp%ROWTYPE;
3 ename emp.ename%TYPE;
4 BEGIN
5 ename := 'ADAMS';
6 SELECT * INTO emp_rec FROM emp WHERE ename=ename;
7 END;
8 /
DECLARE
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 6
Для манипулирования данными используются команды языка манипулирования данными DML: INSERT, UPDATE, DELETE. С помощью команд DML можно обрабатывать сразу несколько строк, исключение TOO_MANY_ROWS при этом не возникает. Не возникает, также, исключение NO_DATA_FOUND в том случае, если ни одна строка не изменяется командой DML.
SQL> DECLARE
2 v_comm emp.comm%TYPE;
3 BEGIN
4 v_comm := 100;
5 UPDATE emp SET comm = comm+v_comm WHERE comm IS NOT NULL
6 END;
7 /
PL/SQL procedure successfully completed.
SQL> SELECT ename, comm FROM emp WHERE comm IS NOT NULL;
ENAME COMM
---------- ----------
ALLEN 400
WARD 600
MARTIN 1500
TURNER 100
Управлять логикой транзакций можно с помощью команд COMMIT, ROLLBACK и SAVEPOINT. Транзакция начинается с первой команды DML, следующей за COMMIT или ROLLBACK, и завершается явно последующим выполнением команды COMMIT или ROLLBACK. Подтверждение или откат транзакции может происходить как в самом блоке PL/SQL, так и в вызывающей среде.
SQL> SELECT ename, comm FROM emp WHERE ename='ALLEN';
ENAME COMM
---------- ----------
ALLEN 400
SQL> DECLARE
2 v_comm emp.comm%TYPE;
3 BEGIN
4 v_comm := 100;
5 UPDATE emp SET comm = comm+v_comm WHERE ename='ALLEN';
6 ROLLBACK;
7 END;
8 /
PL/SQL procedure successfully completed.
SQL> SELECT ename, comm FROM emp WHERE ename='ALLEN';
ENAME COMM
---------- ----------
ALLEN 400
SQL> DECLARE
2 v_comm emp.comm%TYPE;
3 BEGIN
4 v_comm := 100;
5 UPDATE emp SET comm = comm+v_comm WHERE ename='ALLEN';
6 END;
7 /
PL/SQL procedure successfully completed.
SQL> ROLLBACK;
Rollback complete.
SQL> SELECT ename, comm FROM emp WHERE ename='ALLEN';
ENAME COMM
---------- ----------
ALLEN 400
Для управления прогаммной логикой в PL/SQL используются следующие управляющие структуры:
· Структуры условного выполнения операторов (оператор IF)
· Циклы:
o Простой цикл для безусловного выполнения операторов в цикле
o Цикл FOR для управления количеством повторов по счетчику
o Цикл WHILE для управления количеством повторов в зависимости от выполнения условия
Для выхода из цикла используется оператор EXIT
Оператор IF позволяет выполнять команды PL/SQL в зависимости от условий.
IF условие THEN
операторы;
[ELSIF условиеTHEN
операторы;]
[ELSE
операторы;]
END IF;
Если условие истинно, выполняется последовательность операторов, следующих за ключевым словом THEN. Если условие ложно или не определено, последовательность операторов после THEN пропускается. В операторе IF может использоваться неограниченное число предложений ELSIF, но предложение ELSE может быть только одно.
SQL> DECLARE
2 v_comm emp.comm%TYPE;
3 BEGIN
4 SELECT comm INTO v_comm FROM emp WHERE ename='ADAMS';
5 IF v_comm IS NULL THEN
6 DBMS_OUTPUT.put_line('Commision is Null');
7 ELSE
8 DBMS_OUTPUT.put_line('Commision is not NULL');
9 END IF;
10 END;
11 /
Commision is Null
PL/SQL procedure successfully completed.
Предложение ELSE используется для определения действий, которые следует выполнить в том случае, если условие ложно или не определено. Однако действия в предложении ELSE, также, могут зависеть от некоторых условий, что вызывает необходимость вложенных операторов IF, каждый из которых обязательно должен заканчиваться своим END IF. Чтобы избежать вложенных операторов IF, используются предложения ELSIF, это упрощает логику программного кода.
Уважаемый посетитель!
Чтобы распечатать файл, скачайте его (в формате Word).
Ссылка на скачивание - внизу страницы.