coderamblings.com

In 2004, stored procedures were introduced to MySQL 5 with a team under the direction of Mr Per-Erik Martin.

Here is the example Peter Gulutzan listed


DELIMITER //

CREATE PROCEDURE payment [2]
(payment_amount DECIMAL(6,2),
payment_seller_id INT)
BEGIN
DECLARE n DECIMAL(6,2);
SET n = payment_amount - 1.00;
INSERT INTO Moneys VALUES (n, CURRENT_DATE);
IF payment_amount > 1.00 THEN
UPDATE Sellers
SET commission = commission + 1.00
WHERE seller_id = payment_seller_id;
END IF;
END;
//

Looping
Statement labels can be used before BEGIN, WHILE, REPEAT OR LOOP. The statement 'LEAVE {labelname}' will jump out of the compound statement.
ITERATE used before the label name, is like 'CONTINUE'

Variable Limit clause in Select
Stored procedures do not handle the 'LIMIT 0,30' yet.
However, a few work-arounds have been suggested in this bug.

One of the ideas suggest to use a prepared statement, which can handle the LIMIT clause just fine, inside the stored procedure.

PHP 5 calling Stored Procedures
Using MySQLi:
* Create a new mysqli ocnnection
$dbh = new mysqli($hostname, $username, $password, $database);
* Check that the mysqli connection is properly connected using mysqli_connect_errno()
* Call the stored procedure using $dbh->query("call procedure_name( $variables )") and assign it to a result set else, print use errno to print
* Iterate over the result set with fetch_object

Using PDO:

$pdo = new PDO('mysql:localhost;dbname=pdotest', 'root', '123182');
$pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_WARNING);
$stmt = $pdo->query('CALL
pdotest.spIsStringComparisonCaseInsensitive()');

foreach($stmt as $row)
{
echo $row['isInsensitive'];
}
$stmt->closeCursor();
$stmt = null;

Recommended Resources
I highly recommend reading the book:
MySQL Stored Procedure Programming
By Guy Harrison, Steven Feuerstein

Myself, I admit have not touched the book, except download the excellent examples, which includes numerous sql examples, as well as php, java, python, amongst others.

Another excellent resource is:
MySQL 5.0 Stored Procedures
Peter Gulutzan

Useful commands
List of all procedures: SHOW PROCEDURE STATUS;
Narrowed down: SHOW PROCEDURE STATUS LIKE '%Test%';
List of all functions: SHOW FUNCTION STATUS;
Display details of a sp: SHOW CREATE PROCEDURE sprocTest;
Change name of a sp: ALTER PROCEDURE sp_myfirst NAME sp_second;

Restrictions on Stored Routines and Triggers
Stored routines cannot contain arbitrary SQL statements. The following statements are disallowed:
* The locking statements LOCK TABLES, UNLOCK TABLES.
* LOAD DATA and LOAD TABLE.
* SQL prepared statements (PREPARE, EXECUTE, DEALLOCATE PREPARE), only applies to versions pre 5.0.13
Read about more restrictions here:
Stored routine restrictions

Reference:
MySQL Stored Procedure Programming
Mysql.org Reference on Stored Procedures
Stored Procedures in MySQL
Stored Procedures in MySQL 5.0
MySQL and Stored Procedures
A DIY approach to Stored Procedures in MySQL
Stored Procedure Programming for MySQL 5

Bugs
http://phpbuilder.com/board/showthread.php?t=10332897
http://bugs.mysql.com/bug.php?id=11918