Panoramica sulle Stored Procedure

Le Stored Procedure sono Routine che vengono invocate con una istruzione CALL. Possono avere parametri di input, di output e parametri che sono sia di input sia di output.

Creare le Stored Procedure

Ecco un esempio su come creare una Stored Procedure:

DELIMITER //

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

DELIMITER ;

Per prima cosa si modifica il delimitatore, perché il corpo della funzione contiene il punto e virgola, che è il delimitatore predefinito. La procedura si chiama Reset_animal_count. MODIFIES SQL DATA indica che la procedura esegue un'operazione di modifica sui dati. E' puramente indicativo. Infine c'è l'effettiva istruzione SQL - una UPDATE.

MariaDB [test]> SELECT * FROM animal_count;
+---------+
| animals |
+---------+
|     101 |
+---------+

MariaDB [test]> CALL Reset_animal_count();

MariaDB [test]> SELECT * FROM animal_count;
+---------+
| animals |
+---------+
|       0 |
+---------+

Come esempio più complesso, con parametri in input, vediamo una procedura effettivamente usata dalle banche:

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;

Si veda CREATE PROCEDURE per conoscere i dettagli sulla sintassi usata.

Why use Stored Procedures?

Security is a key reason. Banks commonly use stored procedures so that applications and users don't have direct access to the tables. Stored procedures are also useful in an environment where multiple languages and clients are all used to perform the same operations.

Stored Procedure listings and definitions

To find which stored functions are running on the server, use SHOW PROCEDURE STATUS.

MariaDB [test]> 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

or query the routines table in the INFORMATION_SCHEMA database directly:

MariaDB [(none)]> SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE='PROCEDURE';
+--------------------+
| ROUTINE_NAME       |
+--------------------+
| Reset_animal_count |
+--------------------+

To find out what the stored procedure does, use SHOW CREATE PROCEDURE.

MariaDB [test]> 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

Dropping and Updating a Stored Procedure

To drop a stored procedure, use the DROP PROCEDURE statement.

DROP PROCEDURE Reset_animal_count();

To change the characteristics of a stored procedure, use ALTER PROCEDURE. However, you cannot change the parameters or body of a stored procedure using this statement; to make such changes, you must drop and re-create the procedure using DROP PROCEDURE and CREATE PROCEDURE.

Permissions in Stored Procedures

See the article Stored Routine Privileges.