Взаимодействие с базой данных Oracle

Страницы работы

10 страниц (Word-файл)

Содержание работы

Взаимодействие с базой данных Oracle

PL/SQL позволяет извлекать информацию из базы данных Oracle с помощью команды SELECT и вносить в нее изменения, используя язык обработки данных DML Блок PL/SQL не является единицей транзакции. Для фиксации или отмены изменений используются команды COMMIT, ROLLBACK, которые могут быть выполнены как в блоках, так и независимо от них. Язык определения данных DDL и язык управления данными DCL PL/SQL не поддерживает. Однако существует встроенный пакет Oracle DBMS_SQL, который позволяет выполнять эти команды.

Извлечение данных из базы данных Oracle

Для извлечения данных из базы данных Oracle используется команда SELECT с обязательным предложением INTO со списком переменных, которым присваиваются извлеченные данные. Запрос должен заканчиваться точкой с запятой и возвращать только одну строку, иначе возникнет ошибка. Для извлечения данных с помощью PL/SQL доступен полный синтаксис для команды SELECT.

SELECT список_выбораINTO список_переменных | имя_записиFROM таблица_базы_данных [...];

список_выбора – список столбцов таблицы базы данных. Может содержать выражения, однострочные и групповые функции

список_переменных – список скалярных переменных, которым присваиваются возвращаемые данные

имя_записи  – имя записи PL/SQL для хранения возвращаемых данных

таблица_базы_данных – имя таблицы базы данных

Список переменных в предложении INTO должен содержать столько переменных, сколько столбцов указано в списке выбора SELECT. Причем, порядок перечисления должен совпадать, а типы данных должны быть совместимы (это можно обеспечить посредством атрибута %TYPE).

SQL> SET SERVEROUTPUT ON

SQL> DECLARE

  2   v_name emp.ename%TYPE;

  3  BEGIN

  4   SELECT MIN(ename) INTO v_name FROM emp;

  5   DBMS_OUTPUT.put_line(v_name);

  6  END;

  7  /

ADAMS

PL/SQL procedure successfully completed.

Для хранения данных, возвращаемых запросом, можно использовать запись 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

Манипулирование данными в базе данных Oracle

Для манипулирования данными используются команды языка манипулирования данными 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

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

·  Структуры условного выполнения операторов (оператор IF)

·  Циклы:

o  Простой цикл для безусловного выполнения операторов в цикле

o  Цикл FOR для управления количеством повторов по счетчику

o  Цикл WHILE для управления количеством повторов в зависимости от выполнения условия

Для выхода из цикла используется оператор EXIT

Оператор IF

Оператор 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, это упрощает логику программного кода.

Похожие материалы

Информация о работе