MySQL Stored Procedures

MySQL stored procedures are very similar to the DB2 implementation, as both are based on the SQL:2003 standard. A sample stored procedure:

DELIMITER |
CREATE PROCEDURE productpricing()
SELECT AVG(prod_price) AS priceaverage FROM products;
END;
DELIMITER ;
CALL productpricing();

A few useful commands for viewing stored procedures:

a.       SHOW PROCEDURE STATUS \G

– shows list of procedures & metadata

b.      SHOW CREATE PROCEDURE {procedure name} \G

– shows the definition of a particular procedure

c. SELECT * FROM INFORMATION_SCHEMA.ROUTINES \G

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s