Stored Procedures in MySQL

Working with Stored Procedures in MySQL

Writing the first stored procedure

The first stored procedure is very simple. It retrieves all products from products table. First let’s take a look at the stored procedure source code below:

1	DELIMITER //
2	CREATE PROCEDURE GetAllProducts()

3	BEGIN
4	SELECT *  FROM products;

5	END //
6	DELIMITER ;

The first command you see is DELIMITER //. This command is not related to the stored procedure. DELIMITER statement in MySQL is used to change the standard delimiter (semicolon) to another. In this case, the delimiter is changed from semicolon(;) to //, so you can have multiple SQL statements inside stored procedure which can be separated by the semicolon. After the END keyword we use delimiter // to show the end of the stored procedure. The last command changes the delimiter back to the standard one (semicolon).
more

In order to create a new stored procedure you use CREATE PROCEDURE statement. After the CREATE PROCEDURE statement you can specify the name of stored procedure, in this case it is GetAllProducts.

The body part of the stored procedure started with between BEGIN and END block. You can write declarative SQL code here. We can analysis more details of each part later. Now we have created a new stored procedure, but we also need to know however to invoke it in program or in command line of MySQL.

Calling the stored procedure

In order to invoke a stored procedure we use the following SQL command:

1	CALL STORED_PROCEDURE_NAME()

For example, we can call the stored procedure we have created like this

1	CALL GetAllProducts();

We get all products in the products database table.

In this tutorial, you’ve learn how to change the delimiter by using DELIMITER statement. It allows you to type multiple SQL statements inside stored procedure. You’ve also learn how to write a simple stored procedure by using CREATE PROCEDURE statement and call it from command line by using CALL statement.

MySQL Stored Procedure Parameters

Summary: In this tutorial, you will learn how to write MySQL stored procedures with parameters. We will also give you a couple of stored procedure examples to help you understand more about using parameters in stored procedures.

Almost stored procedures you develop require parameters. Parameters make the stored procedure more flexible and useful. In MySQL, a parameter has one of three modes IN, OUT and INOUT.

§  IN this is the default mode. IN indicates that a parameter can be passed into stored procedures but any modification inside stored procedure does not change parameter. Suppose you pass parameter Id, which is equal 10, into stored procedure GetAll(Id), after executing the stored procedure the value of Id is still 10 even though the GetAll stored procedure can change the value of it.

§  OUT this mode indicates that stored procedure can change this parameter and pass back to the calling program.

§  INOUT obviously this mode is combined of IN and OUT mode; you can pass parameter into stored procedure and get it back with the new value from calling program.

The syntax of defining a parameter in stored procedure is as follows:

MODE param_name param_type(param_size)

MODE could be IN, OUT or INOUT depending on the purpose of parameter you specified.
param_name is the name of the parameter. The name must not be the same as the column name of tables and following naming convention. Followed the parameter name is the type of parameter and its size.

Each parameter is separated by a comma if the stored procedure more than one parameter.

Let’s practice with following examples to understand more.

The first example is a stored procedure to get all offices in a country. Here is the SQL source code:

1	DELIMITER //
2	CREATE PROCEDURE GetOfficeByCountry(IN countryName VARCHAR(255))

3	BEGIN
4	SELECT city, phone

5	FROM offices
6	WHERE country = countryName;

7	END //
8	DELIMITER ;

As you can see we use countryName as the IN parameter with its type is varchar and its size is 255. In body part of stored procedure, we retrieve all offices which its country is the countryName.
Suppose you want to retrieve all office in USA, just pass the value to the stored procedures like this:

1	CALL GetOfficeByCountry('USA')

It returns all offices in USA

To get all offices in France just call pass France to the stored procedure like following:

1	CALL GetOfficeByCountry(‘France’)

It returns all offices in USA

The second example, we will write a store procedure to count the order in a specific order status such as shipped, resolved, cancelled, on hold, disputed or in process. Here is the stored procedure

01	DELIMITER $$
02	CREATE PROCEDURE CountOrderByStatus(

03	IN orderStatus VARCHAR(25),
04	OUT total INT)

05	BEGIN
06	SELECT count(orderNumber)

07	INTO total
08	FROM orders

09	WHERE status = orderStatus;
10	END$$

11	DELIMITER ;

The CountOrderByStatus stored procedure has two parameters:

§  orderStatus parameter is IN parameter; we pass order status such as shipped or on hold in to get the number of it

§  total parameter is the OUT parameter which we use to get the total order by a specified status back.

So to get number of shipped orders, we just perform following statements

1	CALL  CountOrderByStatus('Shipped',@total);

To get number of in process we do the same as above

1	CALL CountOrderByStatus('in  process',@total);
2	SELECT @total AS  total_in_process;

In the third procedure, we will demonstrate the INOUT parameter. The stored procedure capitalizes all words in a string and returns it back to the calling program. The stored procedure source code is as follows:

01	DELIMITER $$
02	CREATE PROCEDURE `Capitalize`(INOUT str VARCHAR(1024))

03	BEGIN
04	DECLARE i INT DEFAULT 1;

05	DECLARE myc, pc CHAR(1);
06	DECLARE outstr VARCHAR(1000) DEFAULT str;

07	WHILE i <= CHAR_LENGTH(str) DO
08	SET myc = SUBSTRING(str, i, 1);

09	SET pc = CASE WHEN i = 1 THEN ' '
10	ELSE SUBSTRING(str, i - 1, 1)

11	END;
12	IF pc IN (' ', '&', '''', '_', '?', ';', ':', '!', ',', '-', '/', '(', '.') THEN

13	SET outstr = INSERT(outstr, i, 1, UPPER(myc));
14	END IF;

15	SET i = i + 1;
16	END WHILE;

17	SET str = outstr;
18	END$$

19	DELIMITER ;

Here is the usage of the Capitalize stored procedure

1	SET @str = 'mysql stored procedure tutorial';
2	CALL Capitalize(@str);

3	SELECT @str;

And the @str value is ‘Mysql Stored Procedure Tutorial’

SQL Cursor in Stored Procedures

Summary:In this tutorial, you will learn how to use database cursor in stored procedure to loop over a result set in MySQL.

MySQL supports cursor in stored procedures, functions and triggers. Cursor is used to iterate through a set of rows, which returned by a query, and process individual row. Currently with all versions greater 5.x, MySQL cursor has following properties:

§  Read only: it means you cannot update the cursor.

§  Non-scrollable: it only can traverse in one direction and cannot skip, move back or forth in result set.

§  Asensitive: you should avoid update table while open a cursor on that table otherwise you may get unexpected results.

MySQL supports following statements for working with cursor.

First you have to declare a cursor using DECLARE statement:

1	DECLARE cursor_name CURSOR FOR SELECT_statement;

Second you have to open the cursor using OPEN statement. You must open cursor before fetching rows from it.

1	OPEN cursor_name;

Next you can retrieve next row from cursor and move the cursor to the following row in a result set by using FETCH statement.

1	FETCH cursor_name INTO variable list;

And finally, you must close the cursor to deactivate it and release the memory associated with that cursor. To close the cursor you use CLOSE statement:

1	CLOSE cursor_name;

One of the most important point when working with cursor is you should use a NOT FOUND handler to avoid raising a fatal “no data to fetch” condition.
We use a stored procedure example below to demonstrate cursor. 

01	DELIMITER $$
02	DROP PROCEDURE IF EXISTS CursorProc$$

03	CREATE PROCEDURE CursorProc()
04	BEGIN

05	DECLARE  no_more_products, quantity_in_stock INT DEFAULT 0;
06	DECLARE  prd_code VARCHAR(255);

07	DECLARE  cur_product CURSOR FOR
08	SELECT  productCode FROM products;

09	DECLARE  CONTINUE HANDLER FOR NOT FOUND
10	SET  no_more_products = 1;

11
12	/* for  loggging information */

13	CREATE  TABLE infologs (
14	Id int(11) NOT NULL AUTO_INCREMENT,

15	Msg varchar(255) NOT NULL,
16	PRIMARY KEY (Id)

17	);
18	OPEN  cur_product;

19
20	FETCH  cur_product INTO prd_code;

21	REPEAT
22	SELECT  quantityInStock INTO quantity_in_stock

23	FROM  products
24	WHERE  productCode = prd_code;

25
26	IF  quantity_in_stock < 100 THEN

27	INSERT  INTO infologs(msg)
28	VALUES  (prd_code);

29	END  IF;
30	FETCH  cur_product INTO prd_code;

31	UNTIL  no_more_products = 1
32	END REPEAT;

33	CLOSE  cur_product;
34	SELECT *  FROM infologs;

35	DROP TABLE  infologs;
36	END$$

37	DELIMITER;

The stored procedure is very simple and can archive the same result by SQL query. We use it only for demonstrating how cursors work.

We use a cursor for products table and loop though the products result set. If the quantity in stock of a product is less than 100, we log it into to a temporary table and after the loop we select all products to print it on screen.

Remember you must declare cursor first and then declare a NOT FOUND handler; otherwise you will get an error.