Хранимая процедура Stored Procedure — это объект базы данных, который представляет собой набор SQL-инструкций, компилируется один раз при создании и размещается на сервере.

Хранимые процедуры имеют большое сходство с процедурами и функциями языков высокого уровня по следующим критериям :

наличие входных и выходных параметров;
наличие локальных переменных;
использование всех инструкций SQL для манипулирования данными;
использование операторов ветвления : IF ... THEN ... ELSE;
использование операторов циклов : WHILE ... DO; FOR SELECT ... DO;
обработку исключительных ситуаций и ошибок.

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

В зависимости от типа СУБД, хранимые процедуры могут быть реализованы на таких языках программирования, как SQL, Java, C/C++. Наибольшее распространение, безусловно, получил SQL. Для различных типов СУБД используются разные реализации SQL :

Oracle : PL/SQL;
MS SQL : Transact-SQL (T-SQL);
PostgreSQL : PL/PgSQL.

Хранимую процедуру можно использовать как представление View для чтения данных. В этом случае она будет возвращать набор данных (записей). Отдельные значения хранимая процедура может вернуть через параметры, если они определены с ключевым словом "out". Процедуру можно оформить как функцию, возвращающую либо простое значение типа INT, CHAR и т.п., либо значение типа RECORD (запись).

Синтаксис создания процедуры, CREATE PROCEDURE

CREATE [OR REPLACE] PROCEDURE [schema.]name 
                               [( [IN | OUT | INOUT] param1 <datatype> 
                               [, [IN | OUT | INOUT] param2 <datatype> ...])]
 { AS | IS } 
[ -- Объявление локальных переменных
  DECLARE var1 <datatype>;
 [DECLARE var2 <datatype>; ...]]
BEGIN
  { исполняемый код }
  [EXCEPTION обработчики исключений]
END [name];

По умолчанию передаваемые процедуре параметры являются входными и ключевое слово "IN" можно не указывать. Если параметр является выходным типа [OUT, INOUT], то данные ключевые слова необходимо использовать. Тип параметров должен соответствовать принятым в СУБД типам.

<datatype> = {
{SMALLINT | INTEGER | FLOAT | DOUBLE PRECISION}
  | {DECIMAL | NUMERIC} [(precision [, scale])]
  | DATE
  | {CHAR | CHARACTER | CHARACTER VARYING | VARCHAR} [(int)]
    [CHARACTER SET charname]
  | {NCHAR | NATIONAL CHARACTER | NATIONAL CHAR}
    [VARYING] [(int)]}

В Oracle можно использовать также и "ссылку" на тип :

-- Ссылка на тип поля birthday таблицы user
birthday  user.birthday%TYPE;

Примечание : В СУБД PostgreSQL можно создать только функцию. Для создания процедуры в PostgreSQL необходимо чтобы функция возвращала значение типа "void".

Пример CREATE PROCEDURE ORACLE

CREATE OR REPLACE PROCEDURE InsertCourse ( v_name IN varchar2 )
 AS
   cnumber  number;
   crs cursor for
      SELECT course_number
         FROM courses_tbl
          WHERE course_name = v_name;
BEGIN
 
   open crs;
   fetch crs into cnumber;
 
   if crs%notfound then
      cnumber := 9999;
   end if;
   
   INSERT INTO student_courses ( course_name, course_number )
      VALUES ( v_name, cnumber );
 
   commit;
   close crs;
 
   EXCEPTION
      WHEN OTHERS THEN
        raise_application_error(-20001, 'An error was encountered - ' || 
                                SQLCODE || ' -ERROR- ' || SQLERRM);
END;

Синтаксис создания функции, CREATE FUNCTION

Синтаксис создания функции CREATE FUNCTION, по сравнения с созданием хранимой процедуры, включает дополнительно оператор RETURNS после описания типов входных параметров и оператор return в теле функции.

CREATE FUNCTION [schema.]name [( [IN | OUT | INOUT] param1 <datatype> 
                              [, [IN | OUT | INOUT] param2 <datatype> ...])]
 RETURNS <datatype>
 { AS | IS } 
[ -- Объявление локальных переменных
  DECLARE var1 <datatype>;
 [DECLARE var2 <datatype>; ...]]
BEGIN
  { исполняемый код }
  return <datatype>;
END [name];

Функция, возвращающая значения типа RECORD

Функция может возвращать как простые значения, так записи типа RECORD. Пример создания функции CREATE FUNCTION ORACLE, возвращающей запись.

CREATE FUNCTION getEmployee (p_empl_id employees.empl_id%type)
   returns employee%rowtype
as
  employee employee%rowtype;
  begin
    select * into employee 
        from employee
           where empl_no = p_empl_no;
    return employee;
  end;

Вызов процедуры, CALL

Вызов процедуры согласно SQL 2003 включает оператор CALL и наименование процедуры с параметрами :

CALL [schema.]имя_процедуры ([параметр [, …]]);

Синтаксис CALL определяет значения входных параметров, необходимых для хранимой процедуры - параметры должны быть заключены в скобки и отделяться друг от друга запятыми. Следует отметить, что скобки необходимы даже в том случае, если параметры отсутствуют, то есть при отсутствии параметров необходимо писать CALL [schema.]имя_процедуры().

Примечание : Для вызова процедуры из приложения Java необходимо использовать CallableStatement.

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

CREATE PROCEDURE SALES.CUSTOMER_UPDATE (id NUMBER, comm NUMBER)
IS 
 BEGIN
  UPDATE CUSTOMERS 
    SET commission = comm
      WHERE customer_id = id;
 END SALES.CUSTOMER_UPDATE;
 
-- вызов процедуры с передачей параметров
CALL SALES.CUSTOMER_UPDATE(213, 15);

Некоторые платформы поддерживают также альтернативную команду EXECUTE, имеющую те же возможности. В некоторых случаях EXECUTE может оказаться предпочтительнее, чем CALL, поскольку ее можно использовать для запуска любого заранее подготовленного кода SQL, в том числе процедур и функций.

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

Для вызова хранимой процедуры СУБД MSSQL следует использовать EXECUTE или EXEC.

Синтаксис вызова хранимой процедуры описан в MSDN

Удаление процедуры, DROP PROCEDURE

Синтаксис удаления процедуры :

DROP PROCEDURE [schema.]name;