Обзор хранимых процедур

Хранимая процедура — это процедура, вызываемая оператором CALL. Она может иметь входные параметры, выходные параметры и параметры, которые являются как входными, так и выходными.

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

Вот пример скелета, демонстрирующего хранимую процедуру в действии:

DELIMITER //

CREATE PROCEDURE Reset_animal_count() 
 MODIFIES SQL DATA
 UPDATE animal_count SET animals = 0;
//

DELIMITER ;

Сначала меняется разделитель, поскольку определение функции будет содержать обычный разделитель точка с запятой. Процедура называется Reset_animal_count. MODIFIES SQL DATA указывает, что процедура будет выполнять запись, изменяя данные. Это для справочных целей только. И наконец, есть собственное SQL-утверждение — UPDATE.

SELECT * FROM animal_count;
+---------+
| animals |
+---------+
|     101 |
+---------+

CALL Reset_animal_count();

SELECT * FROM animal_count;
+---------+
| animals |
+---------+
|       0 |
+---------+

Более сложный пример с входными параметрами, из реальной процедуры, используемой банками:

CREATE PROCEDURE
  Withdraw                             /* Routine name */
  (parameter_amount DECIMAL(6,2),     /* Parameter list */
  parameter_teller_id INTEGER,
  parameter_customer_id INTEGER)
  MODIFIES SQL DATA                   /* Data access clause */
  BEGIN                        /* Routine body */
    UPDATE Customers
        SET balance = balance - parameter_amount
        WHERE customer_id = parameter_customer_id;
    UPDATE Tellers
        SET cash_on_hand = cash_on_hand + parameter_amount
        WHERE teller_id = parameter_teller_id;
    INSERT INTO Transactions VALUES (
        parameter_customer_id,
        parameter_teller_id,
        parameter_amount);
  END;

См. CREATE PROCEDURE для полного синтаксиса.

Зачем использовать хранимые процедуры?

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

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

Чтобы найти, какие хранимые функции работают на сервере, используйте SHOW PROCEDURE STATUS.

SHOW PROCEDURE STATUS\G
*************************** 1. row ***************************
                  Db: test
                Name: Reset_animal_count
                Type: PROCEDURE
             Definer: root@localhost
            Modified: 2013-06-03 08:55:03
             Created: 2013-06-03 08:55:03
       Security_type: DEFINER
             Comment: 
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci

или запросите таблицу routines в базе данных INFORMATION_SCHEMA напрямую:

SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES 
  WHERE ROUTINE_TYPE='PROCEDURE';
+--------------------+
| ROUTINE_NAME       |
+--------------------+
| Reset_animal_count |
+--------------------+

Чтобы узнать, что делает хранимая процедура, используйте SHOW CREATE PROCEDURE.

SHOW CREATE PROCEDURE Reset_animal_count\G
*************************** 1. row ***************************
           Procedure: Reset_animal_count
            sql_mode: 
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `Reset_animal_count`()
    MODIFIES SQL DATA
UPDATE animal_count SET animals = 0
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci

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

Чтобы удалить хранимую процедуру, используйте оператор DROP PROCEDURE.

DROP PROCEDURE Reset_animal_count();

Чтобы изменить характеристики хранимой процедуры, используйте ALTER PROCEDURE. Однако вы не можете изменить параметры или тело хранимой процедуры с помощью этого оператора; чтобы внести такие изменения, необходимо удалить и создать процедуру заново с помощью CREATE OR REPLACE PROCEDURE (которая сохраняет существующие привилегии) или DROP PROCEDURE, после которого следует CREATE PROCEDURE.

Права доступа в хранимых процедурах

См. статью Stored Routine Privileges.