How to use stored procedure with MYSQL? Basic syntax

quantum

The latest developer release of MySQL (MySQL 5.0) now supports stored procedures. Note that it is still in ALPHA version. So try it on your own risk.

Here is a sample way of how to use stored procedure(pl/sql programming?) with MYSQL 5.0.

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

Code:

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 ;

This is how you call it:

mysql> CALL PARTPRICE( 3, 1, 864);
+-----------+--------------+
| WIDGET_ID | WIDGET_PRICE |
+-----------+--------------+
| 1 | 253.00 |
| 2 | 536.90 |
| 3 | 734.40 |
+-----------+--------------+
3 rows in set (0.00 sec)
Query OK, 0 rows affected (0.02 sec)

This is how you drop a procedure:

mysql> DROP PROCEDURE IF EXISTS PARTPRICE;
Query OK, 0 rows affected (0.00 sec)
mysql> DROP PROCEDURE PARTPRICE
-> ;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+-------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------+
| Warning | 1289 | PROCEDURE part does not exist |
+---------+------+-------------------------------+
1 row in set (0.00 sec)

Some useful statements

mysql> SHOW CREATE PROCEDURE PARTPRICE;

Try this.
mysql> SHOW CREATE PROCEDURE PARTPRICE;

It shows you the statement used to create the procedure. This is useful to have if you didn’t create the procedure in the first place but want to know how.

| Procedure | Create Procedure
| PARTPRICE | 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
|
1 row in set (0.00 sec)

Here are all the details on Mysql Website.
_________________

Dust fills my eyes / Clouds roll by / and I roll with them / Centuries cry / Orders fly / and I fall again
Afford best design, implement best solution. Outsource your web design.

dinangkur

That's the only thing I'm waiting for. When final release will come up with store procedure and trigger :)
They will rock the database world. No more other databases.

-DK
_________________
...we too are stardust...

hasnut · Expert User Joined: 28 Aug 2004 Posts: 201

Yes this is the best addition of mysql, but I think no more database sin't true.

People will not go for desktop App with mysql database.
_________________
Sarder Hasnut
MCSD, CIW A

Need Low Cost Prefessional Hosting Contact me

dinangkur

Hasnut,

I don't think future is not desktop application with standalone database. Don't you think? We're heading to a era where everything will be connected with everything. And few people will have complete access to the database. People will be be able to access the database according to their needs. Think about a database intelegent enough to grow up by itself. A automated databank.

-DK
_________________
...we too are stardust...

hasnut · Expert User Joined: 28 Aug 2004 Posts: 201

You are right, but the big companies will not go for opensource database for that. Still Oracle and sql server are most popular in corporate use.
_________________
Sarder Hasnut
MCSD, CIW A

Need Low Cost Prefessional Hosting Contact me