Хранимые программные единицы, страница 2

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

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

  2  IS

  3     v_sal_emp emp.sal%TYPE;

  4  BEGIN

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

  6     v_sal := v_sal_emp + v_sal;

  7  EXCEPTION

  8     WHEN OTHERS THEN

  9        DBMS_OUTPUT.put_line('Error occurred');

 10  END emp_sal;

 11  /

Procedure created.

SQL> VARIABLE h_sal NUMBER

SQL> EXECUTE :h_sal := 100

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

SQL> PRINT h_sal

     H_SAL

----------

      5100

Способы передачи параметров

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

SQL> CREATE OR REPLACE PROCEDURE emp_sal

  2         (v_job IN VARCHAR2,

  3          v_sal_add IN NUMBER := 100,

  4          v_sal_out OUT NUMBER)

  5  IS

  6     v_sal_emp emp.sal%TYPE;

  7  BEGIN

  8     SELECT sal INTO v_sal_emp FROM emp WHERE LOWER(job)=LOWER(v_job);

  9     v_sal_out := v_sal_emp + v_sal_add;

 10  EXCEPTION

 11     WHEN OTHERS THEN

 12        DBMS_OUTPUT.put_line('Error occurred');

 13  END emp_sal;

 14  /

Procedure created.

SQL> VARIABLE h_sal NUMBER

·  Позиционный – аргументы передаются в том порядке, в котором были перечислены формальные параметры

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

PL/SQL procedure successfully completed.

SQL> PRINT h_sal

     H_SAL

----------

      5200

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

·  По имени – аргументы передаются в произвольном порядке. Связь аргумента с формальным параметром осуществляется с помощью специального символа (=>)

SQL> EXECUTE emp_sal(v_sal_out=>:h_sal, v_job=>'President')

PL/SQL procedure successfully completed.

SQL> PRINT h_sal

     H_SAL

----------

      5100

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

·  Комбинированный – первые аргументы передаются по порядку, остальные – по имени

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

PL/SQL procedure successfully completed.

SQL> PRINT h_sal

     H_SAL

----------

      5100

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

Вызов хранимой процедуры

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

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

SQL> DECLARE

  2     v_out NUMBER;

  3     v_job VARCHAR2(20) := 'President';

  4     v_add NUMBER := 300;

  5  BEGIN

  6     emp_sal(v_job, v_add, v_out);

  7     DBMS_OUTPUT.put_line('Salary of ' || v_job || ' is ' || TO_CHAR(v_out));

  8  END;

  9  /

Salary of President is 5300

PL/SQL procedure successfully completed.

Хранимая процедура может быть вызвана, также, из другой хранимой процедуры.

SQL> CREATE OR REPLACE PROCEDURE pres_sal

  2  IS

  3     v_out NUMBER;

  4     v_job VARCHAR2(20) := 'President';

  5     v_add NUMBER := 300;

  6  BEGIN

  7     emp_sal(v_job, v_add, v_out);

  8     DBMS_OUTPUT.put_line('Salary of ' || v_job || ' is ' || TO_CHAR(v_out));

  9  END;

 10  /

Procedure created.

SQL> EXECUTE pres_sal

Salary of President is 5300

PL/SQL procedure successfully completed.

Удаление хранимой процедуры

Для удаления хранимой процедуры используется команда DDL DROP PROCEDURE.

DROP PROCEDURE имя_процедуры;

SQL> DROP PROCEDURE emp_sal;

Procedure dropped.

SQL> DROP PROCEDURE pres_sal;

Procedure dropped.

Хранимые функции

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

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

CREATE [OR REPLACE] FUNCTION имя_функции

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

RETURN тип_данных

IS

Блок_PL/SQL;

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

имя_функции – имя хранимой функции

имя_параметра – имя формального параметра

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

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

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

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

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

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

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

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

• В исполняемой секции блока PL/SQL хранимой функции должен содержаться хотя бы один оператор RETURN

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

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

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

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