MySQL Stored Procedure example with parameter
MySQL Stored Procedure: since every time you pass a query string to Server the code has to be compiled etc, stored procedure are already compiled and ready to run on the server.
What is Stored Procedure?
A Stored Procedure is a routine or set of SQL Statements invoked with a CALL statement. It may have input parameters, output parameters and parameters that are both input parameters and output parameters.
Some useful Commands:
- SHOW PROCEDURE STATUS // To get list of all running stored procedure on the DB server
- SHOW CREATE PROCEDURE PROCEDURE_NAME //To get the stored procedure code
- CREATE PROCEDURE ...// To create a new stored procedure
- DROP PROCEDURE PROCEDURE_NAME //To drop a stored procedure
Benefits of stored procedure:
1) Security-Since the actual query is stored on the server you are not transmitting this over the network which means anyone intercepting your network traffic does not gain any insight into your table structure. Also a well designed SP will prevent injection attacks. you can also set Access privileges for users using stored procedure.
2) Code Separation-you keep your database code in your database and your application code in your application, there is very little crossover and I find this makes bug fixing a lot nicer.
3) Code Reusability & Maintainability-you can reuse a procedure many times without having to copy paste the query, also if you wish to update the query you just have to update it in one place.
4) Decreased Network Traffic-As mentioned above this may not be an issue for most people but with a large application you can significantly reduce the amount of data being transferred via your network by switching to using stored procedures.
You may also like: MySQL Interview Questions Answers
- CREATE PROCEDURE and CREATE FUNCTION require the CREATE ROUTINE privilege.
- To check current user privilege use - SHOW PRIVILEGES;
- The delimiter is the character/string of characters which is used to complete an SQL statement. By default we use semicolon (;) as a delimiter.
- But semicolon (;) as a delimiter, causes problem in stored procedure because a procedure can have many statements, and everyone must end with a semicolon.
- So for your delimiter, pick a string which is rarely occurs within procedure
- So, we have used double dollar sign i.e. $$ as a Delimiter in Stored Procedure.
You may also like: Database Interview Questions Answers
How to create MySQL Stored Procedure with ExamplesExample - Suppose the following table structure:
|1||ravi||[email protected]||test data||Y|
|2||dev||[email protected]||test data||Y|
|3||surya||[email protected]||test data||Y|
|4||pankaj||[email protected]||test data||N|
1. Get User Details By User ID
DELIMITER $$ DROP PROCEDURE IF EXISTS proc_userdetails; CREATE PROCEDURE proc_userdetails(IN uid INT) BEGIN SELECT `id`, `name`, `email`, `status` FROM `user` WHERE id = uid; END $$ DELIMITER ; // CALL ABOVE PROCEDURE call proc_userdetails(2);OUTPUT:
|2||dev||[email protected]||test data||Y|
You may also like: MongoDB Interview Questions Answers
Let's see another example of using Order table.
CREATE TABLE `order` ( `order_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, `customer_id` VARCHAR(50) NULL DEFAULT NULL, `order_date` DATETIME NULL DEFAULT NULL, `shipped_date` DATETIME NULL DEFAULT NULL, `delivery_date` DATETIME NULL DEFAULT NULL, `order_status` ENUM('pending','canceled','shipped','delivered') NOT NULL DEFAULT 'pending', PRIMARY KEY (`order_id`) ) COMMENT='Order Table' COLLATE='latin1_swedish_ci' ENGINE=InnoDB;
Create Another MySQL STORE PROCEDURE for ORDER table - with IN & OUT Parameters
DELIMITER $$ DROP PROCEDURE IF EXISTS proc_getCustomerOrders$$ CREATE PROCEDURE proc_getCustomerOrders( IN custid INT, OUT pending INT, OUT shipped INT, OUT canceled INT, OUT delivered INT ) BEGIN -- pending SELECT count(*) INTO pending FROM `order` WHERE customer_id = custid AND order_status = 'pending'; -- shipped SELECT count(*) INTO shipped FROM `order` WHERE customer_id = custid AND order_status = 'shipped'; -- canceled SELECT count(*) INTO canceled FROM `order` WHERE customer_id = custid AND order_status = 'canceled'; -- delivered SELECT count(*) INTO delivered FROM `order` WHERE customer_id = custid AND order_status = 'delivered'; END $$ DELIMITER ; // CALL ABOVE PROCEDURE CALL proc_getCustomerOrders(1001,@pending,@shipped,@canceled,@delivered); SELECT @pending,@shipped,@canceled,@delivered;