Create a Stored Procedure - MySQL

Creating your first stored procedure

To learn about stored procedures in MySQL, let’s start at the beginning. The first order of business is to create a stored procedure in MySQL. The parser checks for syntax errors when the procedure is being created and checks for existence of variables, parameter count etc. when it is executed for the first time.

Bring up the command window for MySQL. Change to the database that you want to use. Create a widget table (or a table named widget).

mysql> CREATE TABLE WIDGET (
    ->  WIDGET_ID int(11),
    ->  WIDGET_PRICE decimal(6,2));
Query OK, 0 rows affected (0.45 sec)

Populate it with the following values using the INSERT statement.

mysql> INSERT INTO WIDGET VALUES (1,253.00);
Query OK, 1 row affected (0.00 sec)

mysql> select * from widget;
+-----------+--------------+
| WIDGET_ID | WIDGET_PRICE |
+-----------+--------------+
|         1 |       253.00 |
|         2 |       202.00 |
|         3 |       734.40 |
+-----------+--------------+
3 rows in set (0.00 sec)

mysql> INSERT INTO WIDGET VALUES (4,234.00);
Query OK, 1 row affected (0.00 sec)

Create the stored procedure by using the syntax below.
mysql> DELIMITER |
mysql> CREATE PROCEDURE PARTPRICE
    -> (partid INT ,
    -> Quantity INT,
    -> price DECIMAL(6,2)
    -> )
    -> BEGIN
    -> DECLARE discount_percent DECIMAL(6,2);
    -> DECLARE discounted_price DECIMAL(6,2);
    -> SET discount_percent  =  15;
    -> SET discounted_price = price – discount_percent /100*price;
    -> IF quantity > 2 THEN
    -> SET discounted_price = discounted_price - 2.00;
    -> END  IF;
    -> UPDATE WIDGET
-> SET widget_price = discounted_price WHERE widget_id = partid;
-> Select * from widget;
    -> END;
    -> |
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;

The following stored procedure called PARTPRICE, accepts a part id, the number of parts purchased and the price of the part and calculates the discounted price for that part and updates the Widget table. It also displays the table with the updated values, which is possible since MySQL allows the use of simple SELECT statements without cursors or local variables inside a stored procedure.

The DELIMITER statement serves to change the delimiter from the regular “;” to the character that you specify after the DELIMITER keyword. The purpose of this is to allow MySQL to use a different delimiter to indicate the end of the procedure, since the semi-colon is being used within the stored procedure to indicate the end of a statement to the parser. This allows us to use a group of statements inside the BEGIN … END block. You can choose any delimiter you like. When you call a procedure, it can only pass back values using output variables. Stored procedures can also call other stored procedures, thus extending the power of stored procedures many-fold.

The scope of the declared variables is within the BEGIN .. END block. Only one BEGIN .. END block may exist within a stored procedure. However, you may add a label to the BEGIN .. END statements. The labels should match if both are specified. Tables are defaulted to the database used by the caller. A different database can be specified using the Use database directive or the database_name.table_name syntax.

Label: BEGIN

Statement(s)

End Label

Passing parameters

The parameter list should always be present for a procedure even if there are no parameters to be specified. A parameter is an IN parameter by default and is passed by reference; that is, a pointer to the IN parameter is passed to the corresponding formal parameter. However, the OUT and IN OUT parameters are passed by value.