Home arrow MySQL arrow Page 2 - A DIY Approach to Stored Procedures in MySQL

Create a Stored Procedure - MySQL

If you have avoided using MySQL in the past due to its lack of support for stored procedures, here’s good news. The latest developer release of MySQL (MySQL 5.0) supports stored procedures. Drum roll, please! If you want to jog your memory before you begin to create those cretins or want to know more about them, read on. In this article, we'll learn what stored procedures are and create our first stored procedure in MySQL and learn some useful commands while we're at it.

  1. A DIY Approach to Stored Procedures in MySQL
  2. Create a Stored Procedure
  3. Calling and Dropping a Procedure
  4. Alter Procedure and Conclusion
By: Subha Subramanian
Rating: starstarstarstarstar / 92
August 31, 2004

print this article



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).

    ->  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;
|         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 |
    -> (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;
-> 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


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.

>>> More MySQL Articles          >>> More By Subha Subramanian

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates


Dev Shed Tutorial Topics: