Хранимые программные единицы

Хранимые программные единицы

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

Программная единица (подпрограмма), в том числе хранимая, может содержать до четырех секций:

§  Обязательная секция заголовка – определяет имя, тип и аргументы подпрограммы

§  Декларативная необязательная секция – содержит объявление локальных переменных, констант, курсоров, пользовательских исключений

§  Обязательная секция выполняемого кода – содержит команды SQL для манипулирования данными в базе и команды PL/SQL для манипулирования данными в блоке

§  Необязательная секция обработки исключений – определяет действия, связанные с обработкой ошибок сервера или пользовательских исключений, возникших в секции выполняемого кода

Хранимые процедуры

Процедура – именованный блок PL/SQL, который позволяет задавать параметры и выполняет заданные действия. Как правило, процедура создается именно для выполнения определенной последовательности команд. Процедура может храниться, как объект базы данных. Это позволяет неоднократно вызывать ее для повторного выполнения из любого инструментального средства, поддерживающего язык PL/SQL и позволяющего создавать сеанс связи с базой данных, в которой хранится процедура. Одна и та же хранимая процедура может использоваться в различных приложениях, что существенно облегчает поддержку приложений: нет необходимости исправлять все приложения, достаточно модифицировать только процедуру. Хранимая процедура – это процедура, которая хранится в базе данных, как объект базы данных.

Хранимая процедура создается командой DDL CREATE PROCEDURE.

CREATE [OR REPLACE] PROCEDURE имя_процедуры

 [(имя_параметра [IN|OUT|IN OUT] тип_данных [:=|DEFAULT выражение], ...) ]

IS

Блок_PL/SQL;

REPLACE – используется для пересоздания хранимой процедуры, если она уже существует

имя_процедуры – имя хранимой процедуры

имя_параметра – имя формального параметра, который может быть передан в процедуру и/или возвращен в вызывающую среду, в зависимости от типа параметра

тип_данных – тип данных параметра, указывается без размера

выражение – выражение PL/SQL,  задающее начальное значение параметра

Блок_PL/SQL  - тело процедуры, которое определяет действия, выполняемые процедурой.

·  Блок PL/SQL начинается со слова BEGIN или с описания локальных переменных

·  При описании локальных переменных процедуры ключевое слово DECLARE не пишется

·  Заканчивается блок оператором END или END имя_процедуры

·  В блоке PL/SQL хранимой процедуры нельзя ссылаться на хост-переменные и переменные подстановки

Чтобы создать хранимую процедуру из среды SQL*Plus необходимо выполнить следующие действия:

·  Напишите скрипт для создания хранимой процедуры, начиная с предложения CREATE PROCEDURE

·  Выполните скрипт из среды SQL*Plus. При этом исходный текст будет откомпилирован и сгенерируется исполняемый код (p-код). Исходный текст будет сохранен в словаре базы данных даже в том случае, если компиляция завершится с ошибками.

·  Если компиляция завершится с ошибками, информацию об ошибках компиляции можно получить с помощью команды SQL*Plus SHOW ERRORS

·  Если компиляция прошла успешно, исполняемый код (p-код) будет сохранен в словаре базы данных, и процедура может быть выполнена

·  Если компиляция прошла успешно, и был сгенерирован p-код, хранимая процедура может быть вызвана из любого инструмента, поддерживающего PL/SQL. В SQL*Plus для вызова процедуры используется команда EXECUTE

Типы параметров

Значения могут быть переданы в процедуру и/или возвращены в вызывающую среду посредством параметров. Существует три вида параметров:

Формальные параметры представляют собой значения, используемые в исполняемой секции блока PL/SQL, а фактические параметры (аргументы) подставляются при вызове процедуры.

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

SQL> CREATE OR REPLACE PROCEDURE emp_sal (v_job IN VARCHAR2)

  2  IS

  3     v_sal emp.sal%TYPE;

  4  BEGIN

  5     SELECT sal INTO v_sal FROM emp WHERE LOWER(job)=LOWER(v_job);

  6     DBMS_OUTPUT.put_line('Salary of ' || v_job || ' is ' || TO_CHAR(v_sal));

  7  EXCEPTION

  8     WHEN OTHERS THEN

  9        DBMS_OUTPUT.put_line('Error occurred');

 10  END emp_sal;

 11  /

Procedure created.

SQL> SET SERVEROUTPUT ON

SQL> EXECUTE emp_sal('President')

Salary of President is 5000

PL/SQL procedure successfully completed.

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

SQL> CREATE OR REPLACE PROCEDURE emp_sal (v_job IN VARCHAR2, v_sal OUT NUMBER)

  2  IS

  3  BEGIN

  4     SELECT sal INTO v_sal FROM emp WHERE LOWER(job)=LOWER(v_job);

  5  EXCEPTION

  6     WHEN OTHERS THEN

  7        DBMS_OUTPUT.put_line('Error occurred');

  8  END emp_sal;

  9  /

Procedure created.

SQL> VARIABLE h_sal NUMBER

SQL> EXECUTE emp_sal('President', :h_sal)

PL/SQL procedure successfully completed.

SQL> PRINT h_sal

     H_SAL

----------

      5000