« The Evolution of MySQL at Yahoo | Main | MySQL and PHP: Best Practices »
April 16, 2004
Stored Procedures in MySQL
In the lab watching Peter Gulutzan demonstrate stored procedures and functions at MySQL 2004. Won't write everything (like the other sessions), just interesting notes not from the docs.
The nice thing about using a standard is the book is already written, even before MySQL has the functionality. Jim Melton's Understanding SQL Stored Procedures is recommended.
Before writing stored procs change the DELIMITER (Peter uses //) before starting because you'll be using ; in the procedures.
While having some trouble cutting and pasting Peter says, "It's time to confess I'm not the mac expert in the MySQL community, but I have [holding up the mouse] figured out how to use this nifty device."
"The computer is fairly predictable and does things in a robot-like fashion".
BEGIN and END create statement blocks, or compound statements. Each has a semicolon. DECLARE is a statement, not a procedure variable, can be overwritten in future statements, scoped by statement blocks.
Goes over syntax for CREATE PROCEDURE, CREATE FUNCTION, BEGIN, END, DECLARE, SET, IF, CASE, WHILE, REPEAT, loop_label, ALTER PROCEDURE, ALTER FUNCTION.
Peter shows declaring an EXIT HANDLER FOR 1216, which catches any error 1216 and inserts the test into a separate table. Pretty cool.
A few commands Peter uses:
select * from mysql.proc where name = '<procedure name>' //
select current_user() //
show warnings //
There are several things that are in the standard but not in MySQL, but there is quite a bit in MySQL.
Posted by mike at April 16, 2004 2:02 PM
Hard Drive Recovery Group offers hard disk data recovery services for RAID, laptops and servers. Complete clean room and hard drive repair service.Trackback Pings
TrackBack URL for this entry:
http://mike.kruckenberg.com/mt/mt-tb.cgi/559