Coders4Fun

Stored Procedures are a way to re-use and parametrize long SQL queries.
To create a Stored Procedure you need at least MySql 5.0. Using MySQL Query Browser the stored procedure creation is very quick:

Right click on the database where do you want to create the procedure (from version 5.0.1 procedures are assigned to a specific database, instead in the 5.0 version the procedures are globals)

Insert the name of the new procedure

After clicking Create PROCEDURE (FUNCTION is usefull to create a method that always returns a value), you will see a procedure stub like this:

The DELIMITER $$ instruction at the top of the procedure says to MySQL that the end instuction delimiter isn’t the semicolon, otherwise the first semicolon in the procedure will be read from MySQL as the end of instructions. At the end of the method there’s another DELIMITER instruction to restore the standard semicolon delimiter.

Params that we can pass to the functions are essentially of two types: IN and OUT. Using the IN keyword the parameter is a read-only one, instead using the OUT keyword the param will be assigned from the function, and the calling procedure can use it to check results. A parameter can be setted also as INOUT, and in this case it will be treated as input and output. By default, parameters are all IN, and in the Stored Functions the parameters must be only IN.

A Procedure heading example can be the following:

CREATE PROCEDURE CountGames(IN sportType VARCHAR(6), OUT numGames)

Invoking a Select in the Procedure, the SQL results will be returned from the Stored Procedure like if it executed the query, allowing us to not use the OUT parameters in some cases:

 CREATE PROCEDURE CountGames(IN sportType VARCHAR(6))
BEGIN
SELECT COUNT(ID) FROM games_played g WHERE g.type = sportType;
END $$

Now, to invoke the Stored Procedure we can simply write:

CALL CountGames('BASKET');

The example is very simple but it’s usefull to understand the Stored Procedures power. With a lot of Stored Procedure we can automatize our DB and, for example, creates procedure that backups our Database in zipped tables.

Se sei interessato a questo post, potresti anche provare a leggere:

• No related posts