28 March, 2024
MySQL Stored Procedures

MYSQL Stored Procedures

Database-stored procedures are sets of pre-compiled SQL statements created in the server, called and executed by database applications. It is very simple and the same result can be archived by SQL query.

Stored procedures advantages

  • Stored procedures increase the performance of an application. Once created, stored procedure is compiled and stored in the database catalog. It runs faster than an uncompiled SQL commands which are sent from application 

  • Stored procedure reduces the traffic between application and database server because instead of sending multiple uncompiled long SQL commands statement, application has only to send the stored procedure name and get the result back. 

  • Stored procedure is reusable and transparent to any application which wants to use it. Stored procedure exposes the database interface to all applications so developer doesn’t have to program the functions which are already supported in stored procedure in all programs. 

  • Stored procedure is secured. Database administrator can grant the right to application which to access which stored procedures in database catalog without granting any permission on the underlying database table.

Stored procedures disadvantages

  • Stored procedure make the database server high load in both memory and processors. Instead of being focused on the storing and retrieving data, you could be asking the database server to perform a number of logical operations or a complex of business logic which is not the role of it. 

  • Stored procedure only contains declarative SQL so it is very difficult to write a procedure with complexity of business like other languages in application layer such as Java, C#, C++… 

  • You cannot debug stored procedure in almost RDMBSs and in MySQL also. There are some workarounds on this problem but it still not good enough to do so. 

  • Writing and maintain stored procedure usually required specialized skill set that not all developers possess. This introduced the problem in both application development and maintain phrase. 

Read: How to install FFMPEG on local server

Stored procedure in MySQL

MySQL certainly is the most open source RDBMS which is widely used by both community and enterprise but during the first decade of its existence, it did not support stored procedure. Since MySQL version 5.0, Stored Procedure has been added to MySQL database engine.

 A procedure can exist with or without a return value and with or without parameters.

 There are three options for parameterized stored procedures:

IN parameter— You can only send values to the stored procedure. In the stored procedure body, you can execute queries based on input value. This is the default parameter, and keyword IN is optional. If a parameter type is not explicitly defined, by default it is treated as an IN parameter type.

OUT parameter— You can only receive values from the procedure. Your cannot pass values to OUT parameters in a stored procedure call. After successful execution of the stored procedure, all the OUT parameters values will be assigned to their respective bound variables.

INOUT parameter— You can send and receive values from the procedure. This provides the benefits of both OUT and IN parameters using the single bound variable.

Syntax:

CREATE
[DEFINER = { user | CURRENT_USER }]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
proc_parameter:
[ IN | OUT | INOUT ] param_name type
type:
Any valid MySQL data type
characteristic:
COMMENT 'string'
| LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
routine_body:
Valid SQL routine statement

 

Create Stored Procedure “select_procedure” without parameters

DELIMITER $$
CREATE PROCEDURE
select_procedure ()
BEGIN
SELECT * FROM users_age;
END $$
DELIMITER ;

 

PHP:

$mysqli = new mysqli("localhost", "root", "", "sample_db");
$mysqli->query("CREATE PROCEDURE
select_procedure ()
BEGIN
SELECT * FROM users_age;
END;");
CALL select_procedure();

 

PHP:

$res = $mysqli->query("CALL select_procedure();");

 

Create Stored Procedure “insert_procedure_in” with IN parameters

DELIMITER //
CREATE PROCEDURE
insert_procedure_in (IN nam VARCHAR(25), IN age INT)
BEGIN
INSERT INTO users_age(nam, age) VALUES(nam, age);
END //
DELIMITER ;

 

PHP:

$mysqli->query("CREATE PROCEDURE
insert_procedure_in (IN nam VARCHAR(25), IN age INT)
BEGIN
INSERT INTO users_age(nam, age) VALUES(nam, age);
END;");

 

Call Stored Procedure “insert_procedure”

CALL insert_procedure_in('Jack Sparrow', 75);

 

PHP:

$mysqli->query("CALL insert_procedure_in('Jack Sparrow', 75);");

 

Create Stored Procedure “out_procedure” with OUT parameter

DELIMITER $$
CREATE PROCEDURE
out_procedure( OUT val INT)
BEGIN
SELECT age into val FROM users_age LIMIT 0,1;
END $$
DELIMITER ;

 

Call Stored Procedure

out_procedure”

CALL out_procedure(@val);
SELECT @val;
$mysqli->query("CALL out_procedure(@total);");
$res = $mysqli->query("SELECT @total");

 

Create Stored Procedure “inout_procedure” with INOUT parameter

DELIMITER $$
CREATE PROCEDURE
inout_procedure( INOUT val INT)
BEGIN
SELECT age into val FROM users_age WHERE regitration_number=val LIMIT 0,1;
END $$
DELIMITER ;

 

Call Stored Procedure

inout_procedure”

SET @inout:= 123;
CALL inout_procedure(@inout);
SELECT @inout;

 

PHP:

$mysqli->query("SET @inout:= 123;");
$mysqli->query("CALL inout_procedure(@inout);");
$res = $mysqli->query("SELECT @inout");

 

Alter Procedure Syntax

ALTER PROCEDURE proc_name [characteristic ...]
characteristic:
COMMENT 'string'
| LANGUAGE SQL
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }

This statement can be used to change the characteristics of a stored procedure. More than one change may be specified in an ALTER PROCEDURE statement. 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.

 

Drop Procedure:

 DROP PROCEDURE IF EXISTS sample_procedure;

$res = $mysqli->query("DROP PROCEDURE IF EXISTS sample_procedure");

 

List All Procedures:

SHOW PROCEDURE STATUS

$res = $mysqli->query("SHOW PROCEDURE STATUS");
print_r(mysqli_fetch_object($res));

 

Show Procedure Code:

SHOW CREATE PROCEDURE sample_procedure

$res = $mysqli->query("SHOW CREATE PROCEDURE sample_procedure");
print_r(mysqli_fetch_object($res));

 

Delimiters

Delimiters other than the default ; are typically used when defining functions, stored procedures, and triggers wherein you must define multiple statements. You define a different delimiter like $$ which is used to define the end of the entire procedure, but inside it, individual statements are each terminated by ;. That way, when the code is run in the mysql client, the client can tell where the entire procedure ends and execute it as a unit rather than executing the individual statements inside.

Note that the DELIMITER keyword is a function of the command line mysql client only and not a regular language feature. It won’t work if you tried to pass it through a programming language API to MySQL. Some other clients like PHPMyAdmin have other methods to specify a non-default delimiter.

Conclusion:

This tutorial would have helped in gaining certain facts and information about the Stored Procedures in MySQL in an easy and straightforward approach. Do share your thoughts and feedback on the same.

Alex Sam is a digital marketer by choice & profession. He munches on topics relating to technology, eCommerce, enterprise mobility, Cloud Solutions and Internet of Things. His other interests lies in SEO, Online Reputation Management and Google Analytics. Follow Me Socially: Habr , Dev.to & Viblo .

4 Comments

  1. Peter Reply

    Now i got a clear info about mysql stored procedures. Thanks for the benefits and drawbacks you pointed out. It will be very helpful information for the users who are looking to know about this.

  2. Racheal Meadows Reply

    Very Informative post about SQL Stored procedures. Mainly the advantage and disadvantage of sql stored procedures explained in great manner, with every procedure the syntax clearly explains well. Thanks for sharing this great post.

    1. ramanathan Reply

      Hi Racheal,

      Your sounds great. We added more posts about SQL Tutorials, you can get more information by searching our other posts. Try that too.

  3. hugh jackman workout Reply

    Whose this weblog is wonderful, I love studying your all posts. Keep up the good work! You understand, a lot of persons
    are hunting around for this info, you can aid them greatly.

Leave a Reply

Your email address will not be published. Required fields are marked *