MySQL 5.0 Stored Procedures : Part 4

OK, it's been nearly 2 weeks since the last item on stored procedures, so sorry to those who had been following along, I'm sure by this point you will have gone elsewhere for your Stored Procedure fix, but I'll pickup where I stopped with Part 3.

Today we will be looking at selecting records within a stored procedure. I'm sure if you looking at MySQL 5.0.13 you may have been using MySQL 4 for sometime or at least have some experince of writing SQL statements with other database technologies. Therefore I'll assume you know what one looks like and how to construct one using your database tables.

I'll be using a table called emps in this example..

mysql> desc emps;
+----------+---------------+------+-----+---------+
| Field    | Type          | Null | Key | Default |
+----------+---------------+------+-----+---------+
| emp_id   | int(11)       | NO   | PRI |         |
| emp_name | varchar(30)   | YES  |     | NULL    |
| dept_id  | int(11)       | YES  |     | NULL    |
| salary   | decimal(5,2)  | YES  |     | NULL    |
| bonus    | decimal(12,2) | YES  |     | NULL    |
+----------+---------------+------+-----+---------+
5 rows in set (1.72 sec)

In pervious procedure examples we used a parameter to pass in a name to include in our output string, what might be more usefully from an application persepctive is to be able to pass in an employee's ID number (in our example table this is the emp_id column) and then display the associated employee's name.

So how do we do that in a procedure, in a normal command line environment we would just issue a select statement as follows

mysql> select emp_name from emps where emp_id = 1;
+----------+
| emp_name |
+----------+
| Paul     |
+----------+
1 row in set (0.14 sec)

But in our procedure we want to use the name as part of our output string, to do this we need to use INTO in our select statement. INTO passes the value of the select into a variable, "Hold on" you say "what's all this variable buisness?". In general and simplistic terms a variable is a data store, a named area of memory we can use to hold values during the execution of a procedure

You can find more information on variables here...

http://mysql.gilfster.com/page.php?parent_id=1.1&page_id=1.1.5

To keep things short I'll let you look into variables if you need to, but I'll press on. So the first thing we need to do is create the variable, then we issue the select statement passing the output into the variable using INTO, then we reference the variable in much the same way as we referenced the parameter in previous examples.

mysql> delimiter //
mysql> create procedure 
    -> hello_employee(IN p_emp_id INT)
    -> begin
    -> declare p_name varchar(30);
    -> select emp_name into p_name from emps 
    -> where emp_id = p_emp_id;
    -> select concat('Hello ',p_name);
    -> end;
    -> //
Query OK, 0 rows affected (0.30 sec)

mysql> call hello_employee(1)//
+-------------------------+
| concat('Hello ',p_name) |
+-------------------------+
| Hello Paul              |
+-------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Great, so we can now see how to select values from a table in our procedures.
Have a go at constructing your own select statements, any select statement that's valid on the command line is valid in a procedure.

For homework, what happens if you don't use INTO to pass the value into a variable. Those of you who have worked with another procedural language such as Oracle's PL/SQL might expect it to fail, in fact not even compile, what happens in MySQL? I'll discuss what happens and the implications of this tomorrow.