Stored procedures are precompiled collections of SQL statements stored on the server, allowing for encapsulated logic, parameterized execution, and improved application performance.
A Stored Procedure is a routine invoked with a CALL statement. It may have input parameters, output parameters and parameters that are both input parameters and output parameters.
Here's a skeleton example to see a stored procedure in action:
First, the delimiter is changed, since the function definition will contain the regular semicolon delimiter. The procedure is named Reset_animal_count. MODIFIES SQL DATA indicates that the procedure will perform a write action of sorts, and modify data. It's for advisory purposes only. Finally, there's the actual SQL statement - an UPDATE.
A more complex example, with input parameters, from an actual procedure used by banks:
See for full syntax details.
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.
To find which stored functions are running on the server, use .
or query the in the INFORMATION_SCHEMA database directly:
To find out what the stored procedure does, use .
To drop a stored procedure, use the statement.
To change the characteristics of a stored procedure, use . 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 (which retains existing privileges), or DROP PROCEDURE followed CREATE PROCEDURE .
See the article .
This page is licensed: CC BY-SA / Gnu FDL
DELIMITER //
CREATE PROCEDURE Reset_animal_count()
MODIFIES SQL DATA
UPDATE animal_count SET animals = 0;
//
DELIMITER ;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;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_ciSELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE='PROCEDURE';
+--------------------+
| ROUTINE_NAME |
+--------------------+
| Reset_animal_count |
+--------------------+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_ciDROP PROCEDURE Reset_animal_count();