MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

MySQL Stored Procedure

with one comment

A friend, who’s a MySQL-only developer, asked me a question that I couldn’t resist sharing. He wanted to know how to simplify his PHP code by making a single call to the database for a set of inserts to multiple tables.

You can imagine my delight when I got to say a stored procedure. He said, “It’s not Oracle; that won’t work.” I said, “MySQL supports stored procedures too.” Then, he asked how do you do that. Since the reference manual is a bit sparse on that, here’s a quick example.

Here are the detailed steps, even though there are some other blog entries with more information on these topics.

1. Sign on as the root user, if you need more on that go here. Create a database – storeddb:

CREATE DATABASE storeddb;

2. Create a user and grant them permissions:

mysql> CREATE USER 'student' IDENTIFIED BY 'student';
mysql> GRANT ALL ON storeddb.* TO 'student'@'localhost' IDENTIFIED BY 'student';

3. Create a create_mysql_procedure.sql file with the following contents:

-- Select the database.
USE storeddb;
 
-- Conditionally drop the objects to make this rerunnable.
DROP TABLE IF EXISTS a;
DROP TABLE IF EXISTS b;
DROP PROCEDURE IF EXISTS double_insert;
 
-- Create the tables.
CREATE TABLE a
( a_id   INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, a_text CHAR(12));
 
CREATE TABLE b
( b_id   INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, a_id   INT UNSIGNED 
, b_text CHAR(12));
 
-- Reset the execution delimiter to create a stored program.
DELIMITER $$
 
-- Create a stored procedure.
CREATE PROCEDURE double_insert
( input_a CHAR(12), input_b CHAR(12))
BEGIN
  START TRANSACTION;
  INSERT INTO a VALUES (NULL, input_a);
  INSERT INTO b VALUES (NULL, last_insert_id(), input_b);
  COMMIT;
END;
$$
 
-- Reset the delimiter to the default.
DELIMITER ;
 
-- Declare a couple local session variables.
SET @text1 = 'This is one.';
SET @text2 = 'This is two.';
 
-- Call the local procedure.
CALL double_insert(@text1,@text2);
 
SELECT * FROM a;
SELECT * FROM b;

4. Quit the session as the root user.

mysql> QUIT;

5. Sign on as the student user.

C:\> mysql -ustudent -pstudent

6. As the student user, source the file. You have two ways to do that. One leverage csh/Tcsh shell syntax and the other uses Bourne, Korn, or BASH shell syntax.

6(a). The csh/Tcsh syntax:

mysql> SOURCE create_mysql_procedure.sql

6(b). The Bourne, Korn, or BASH syntax:

mysql> \. create_mysql_procedure.sql

When you source it, you should see the following. Don’t worry if you see the three warnings because when you rerun the script they won’t be there. There telling you that the tables didn’t exist to be dropped the first time.

DATABASE changed
Query OK, 0 ROWS affected, 1 warning (0.00 sec)
Query OK, 0 ROWS affected, 1 warning (0.00 sec)
Query OK, 0 ROWS affected, 1 warning (0.00 sec)
Query OK, 0 ROWS affected (0.14 sec)
Query OK, 0 ROWS affected (0.00 sec)
Query OK, 0 ROWS affected (0.08 sec)
Query OK, 0 ROWS affected (0.00 sec)
Query OK, 0 ROWS affected (0.00 sec)
Query OK, 0 ROWS affected (0.09 sec)
 
+------+--------------+
| a_id | a_text       |
+------+--------------+
|    1 | This IS one. |
+------+--------------+
1 ROW IN SET (0.00 sec)
 
+------+------+--------------+
| b_id | a_id | b_text       |
+------+------+--------------+
|    1 |    1 | This IS two. |
+------+------+--------------+
1 ROW IN SET (0.00 sec)

A quick note to those new to automatic numbering in MySQL. You use a null when you don’t want to write an override signature for the INSERT statement. You can find more on SQL Automated Numbering for Oracle, MySQL, and SQL Server in this blog post.

7. Write the following PHP program, and name it call_mysql_procedure.php. We’re going to call it from the command line, but you shouldn’t have to modify it when you call it from a browser.

<?php
  // Attempt to connect to your database.
  $c = @mysqli_connect("localhost", "student", "student", "storeddb");
  if (!$c) {
    print "Sorry! The connection to the database failed. Please try again later.";
    die();
  }
  else {
    // Initialize a statement in the scope of the connection.
    $stmt = mysqli_stmt_init($c);
 
    // Declare two variables for the test procedure call.
    $val1 = "Hello Hal!";
    $val2 = "Hello Dave!";
 
    // Set the call statement, like a SQL statement.
    $sql = "CALL double_insert(?,?)";
 
    // Prepare the statement and bind the two strings.
    if (mysqli_stmt_prepare($stmt, $sql)) {
      mysqli_stmt_bind_param($stmt, "ss", $val1, $val2);
 
      // Execute it and print success or failure message.
      $success = mysqli_stmt_execute($stmt);
      if ($success) {
        print "Congrats! You've executed a MySQL stored procedure from PHP!";
    }
    else {
      print "Sorry, I can't do that Dave...";
    }
  }
}
?>

8. Run the call_mysql_procedure.php from the command line, like this:

php call_mysql_procedure.php

You should see the following message:

Congrats! You've executed a MySQL stored procedure from PHP!

9. You can now connect to the MySQL database storeddb and re-query the tables a and b. You should see the following, which tells you that your PHP code worked.

mysql> SELECT * FROM a;
+------+--------------+
| a_id | a_text       |
+------+--------------+
|    1 | This IS one. |
|    2 | Hello Hal!   |
+------+--------------+
2 ROWS IN SET (0.00 sec)
 
mysql> SELECT * FROM b;
+------+------+--------------+
| b_id | a_id | b_text       |
+------+------+--------------+
|    1 |    1 | This IS two. |
|    2 |    2 | Hello Dave!  |
+------+------+--------------+
2 ROWS IN SET (0.00 sec)

I guess Alice that writing PL/SQL may actually be portable to other databases? Actually, there are some severe limits on loops in MySQL’s implementation but you can move basic stored procedures as of MySQL 5.1. Officially, MySQL 5.0 supports them.

As always, I hope this helps some folks.

Written by maclochlainn

June 13th, 2009 at 5:22 pm

Posted in LAMP,MAMP,MySQL,PHP,sql,Stored Procedures

«
»