MySQL Stored Procedures - Simple Examples and Quick Explanation

SQL is truly a powerful language and allows you to do amazing things once you understand its mathematical foundations or once you've worked long enough with it to feel the right way to approach data-related problems.

You can combine SQL and regular programming by using stored procedures.

In everyday coding, however, we sometimes need or can better express our thoughts using regular programming logic. You can combine both styles of programming and, in addition to that, benefit from storing your code in the database itself by using stored procedures.

Note:

(You can run the example code as you would a normal query; in a mysql> prompt or via any tool like PHPMyAdmin or MySQLWorkbench).

(For the next examples, I'll assume we have a table called users).

Executing a Stored Procedure

Just use call. If you have created a stored procedure called my_sp:

CALL my_sp;

Simplest possible example in MySQL

DELIMITER $$
CREATE PROCEDURE sp_example1 () 
BEGIN
  SELECT * from users; 
END

Variables and selecting values into variables

DELIMITER $$
CREATE PROCEDURE sp_example2()
BEGIN
DECLARE my_var VARCHAR(511); 

  SELECT username 
  FROM users 
  WHERE id=4 
  INTO my_var;
  -- my_var now holds your user's name
  -- you could insert it into another table, for instance.

END

Procedure parameters

DELIMITER $$
CREATE PROCEDURE sp_example3(IN vr_name VARCHAR(100))
BEGIN

  SELECT username 
  FROM users u
  WHERE u.username = vr_name;

END

P.S.: A procedure that executes a normal query (with no INTO keyword after) will work just like a normal query - its results will be displayed on the screen as if you had run the query yourself.

Cursors

You use cursors to fetch data and execute some action for each result returned.

For instance, you would use a cursor to fetch all clients and, for each client retrurned, retrieve his/her neighbours given some conditions (much like a for loop in regular programming languages). Here is a simple example:

CREATE PROCEDURE sp_example4()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE vr_name VARCHAR(100);
    DECLARE cur1 CURSOR FOR SELECT username FROM users;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    -- now we open the cursor
    OPEN cur1;
    read_loop: LOOP
        FETCH cur1 INTO vr_name;
        IF done THEN
            LEAVE read_loop;
        END IF;
        -- now do something with the return, vr_name.
    END LOOP;
    CLOSE cur1;
END;

Troubleshooting

• MySQL is waiting for more input lines after END

If you're using the command line, you might need to add an additional delimiter after the END keyword:

    mysql> delimiter $$
    mysql> create procedure sp_example1 ()
        -> begin
        -> select * from users;
        -> end
        -> $$
    Query OK, 0 rows affected (0.00 sec)

• ERROR 1046 (3D000): No database selected

Prior to creating a stored procedure, you need to select a database. If your database is called my_db:

    mysql> use my_db;
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    Database changed