Stored Procedure in MySql

Starting from MySQL 5, you get Stored Procedure in Mysql

What is a stored procedure: A stored procedure is simply a procedure that is stored on the database server like MySQL. In programming languages, you write procedures to execute a function/logic. You can write similar procedure in SQL and store it in the database. From the front end application you can just call the procedure to get the functionality. Usually, you send series of sqls to the databases to execute a logic. A stored procedure is better as it needs one single call. But not every logic can/should be implemented as stored procedures.

Sample stored procedure

CREATE PROCEDURE sp_hello()
SELECT ‘Hello World’;

It just creates a procedure. To run the procedure, you have to type

call sp_hello;

You will see ‘Hello World’ as output.

Stored procedures can also take parameters and return values. Both needs to be mentioned as parameter. In the parameter list we can declare a variable as IN, OUT, or INOUT parameter. You can use SQLs[insert,select] inside procedure to perform database operations. Also, you can use to set/unset session variables.

Session variable example

SET @X=100;
CREATE PROCEDURE sp_in(p VARCHAR(10))
SET @x = P;

call sp_in(1000);

You see 1000 in screen

A more practical stored procedure with multiple statementsmysql> DELIMITER |mysql> CREATE PROCEDURE sp_declare (P INT) -> BEGIN -> DECLARE x INT; -> DECLARE y INT DEFAULT 10; -> SET x = P*y; -> INSERT INTO sp1(id,txt) VALUES(x,HEX(‘DEF’)); -> END|Query OK, 0 rows affected (0.00 sec) mysql> DELIMITER ;mysql> CALL sp_declare(4);

please, note the use of | and DELIMETER. They provide support so that you can use ; in your procedures.

Some stored procedure related SQL commands

SHOW PROCEDURE STATUSSHOW CREATE PROCEDURE HelloSELECT * FROM INFORMATION_SCHEMA.ROUTINES [Ansi standard]ThanksSayed