TemporaryTables
Temporary tables were introduced in MySQL version 3.23, they are essentially the same as normal tables execpt that the data contained within them is available to your session only and this data is destroyed when your connection is closed. This means that two people can be working on the same data stucture but be viewing and using two separate data sets.
As we have seen in previous sections it’s currently not possible to return result sets from procedures, the only option is to either issue a select against and existing table or return the values via parameters, both of which are far from ideal. However temporary tables could be a suitable alternative, at least in the short term. They are useful because it’s possible for two people to run stored procedures and populate a table without effecting the others results, we could do this with regular tables but it would become messy and there is a strong possibility that data might not be cleared correctly in the event of a loss of connection to the client.
As an example lets say we wanted to create a procedure which returned the first 12 multiples for any given number. The number would be passed in as a parameter and then the results passed out. We could do this using 12 seperate out parameters or a complicated select statement which outputs the results needed. But what would happen if we wanted to increase the number of multiples returned to say 20, we would have to rewrite our procedure when ever we wanted to change the multiples.
A more generic solution would be to use a temporary table which returned the results. That was we can pass in the number of multiples we wanted and store the results in the temporary table.
create temporary table multitab (multiplier int ,num_to_multi int, multi_result int) //
Query OK, 0 rows affected (0.06 sec)
create procedure multiples(IN p_num INTEGER, IN p_multi INTEGER)
begin
declare l_loop int default 1;
delete from multitab;
loop1 : loop
insert into multitab values (l_loop,p_num,l_loop*p_num);
set l_loop := l_loop + 1;
if l_loop < p_multi + 1 then
iterate loop1;
end if;
leave loop1;
end loop loop1;
end
//
The first thing we did was to create a temporary table, this is done in exactly the same was as defining a normal table except we add the TEMPORARY statement.
The procedure accepts two parameters, p_num, the number we want to multiply and p_multi, the number of times we want this number multiplied. We delete any records from our new temporary table and then start a loop. We then insert a record into the temporary table, this will store the record until the user clears the table using a delete or the user logs out. We then increment our loop counter and test to see if the appropriate number of iterations has been completed. If they haven't we iterate the loop and insert again but if it has we leave the loop and exit the procedure.
Let’s call the procedure and see what we get.
call multiples(3,12) // Query OK, 1 row affected (0.00 sec)
The procedure fires successfully but we don’t have any results returned. We need to manually query the table to see our results.
select * from multitab;
-> //
------------ -------------- --------------
| multiplier | num_to_multi | multi_result |
------------ -------------- --------------
| 1 | 3 | 3 |
| 2 | 3 | 6 |
| 3 | 3 | 9 |
| 4 | 3 | 12 |
| 5 | 3 | 15 |
| 6 | 3 | 18 |
| 7 | 3 | 21 |
| 8 | 3 | 24 |
| 9 | 3 | 27 |
| 10 | 3 | 30 |
| 11 | 3 | 33 |
| 12 | 3 | 36 |
------------ -------------- --------------
12 rows in set (0.00 sec)
The procedure now gives us the flexibility to change our parameters to get dynamic results sets, something we couldn’t have done using parameters.
call multiples(5,4) // Query OK, 1 row affected (0.00 sec) select * from multitab // ------------ -------------- -------------- | multiplier | num_to_multi | multi_result | ------------ -------------- -------------- | 1 | 5 | 5 | | 2 | 5 | 10 | | 3 | 5 | 15 | | 4 | 5 | 20 | ------------ -------------- -------------- 4 rows in set (0.00 sec)