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