SelectInto
So far our programs have done little more than return static strings. We have seen that we can call sql statements from within procedures which displays the results on the command line, but what would be more useful would be if we could select data from tables within the database and use them within the procedure.
So far we haven´t really use the tables we have set up but in this section we will be using them more and more, so if haven´t set them up yet now would be a good time.
Download setup.myp
You may remember we looked at variables in an earlier section, at the time we just placed static information in them but we can also select values from a table into them. We do this using the select into syntax. Lets create a simple procedure to do just that.
drop procedure if exists selectintoproc // create procedure selectintoproc(OUT p_out VARCHAR(10)) begin declare l_emp_name varchar(30); select emp_name into l_emp_name from emps; set p_out := l_emp_name; end; //
Download selectintoproc1.myp
Lets run this and see what we get.
call selectintoproc(@a) // ERROR 1172 (42000): Result consisted of more than one row
Oops what happened there? We got an error message because we tried to place the entire contents of the emps table into a single variable. You may have noticed that the emps table has 3 records in it and I´m sure you know that 3 into 1 just won´t go.
There are a few ways we can get round this, we could apply what we learnt in the previous sections and create a handler to deal with this but in that case the procedure would be useless as we wouldn´t get our variable populated. We could use a where clause to limit the amount of rows returned and this would be the best method, however we will wait until the next section to look at this. For now we will use the limit keyword. This will run the SQL statement but then return only the number of rows we specify, in our case 1. Lets try that now.
drop procedure if exists selectintoproc // create procedure selectintoproc(OUT p_out VARCHAR(10)) begin declare l_emp_name varchar(30); select emp_name into l_emp_name from emps limit 1; set p_out := l_emp_name; end; // call selectintoproc(@a) // Query OK, 0 rows affected (0.08 sec) select @a // +-------+ | @a | +-------+ | Roger | +-------+ 1 row in set (0.00 sec)
Download Selectintoproc2.myp
So as you can see our procedure now completes successfully and we can see the result of the select. We can select any number of columns from a table or in fact tables at once.
drop procedure if exists selectintoproc // create procedure selectintoproc(OUT p_out VARCHAR(10)) begin declare l_emp_name, l_dept_id varchar(30); select emp_name, dept_id into l_emp_name, l_dept_id from emps limit 1; set p_out := concat(l_emp_name,´ ´,l_dept_id); end; // call selectintoproc(@a) // Query OK, 0 rows affected (0.00 sec) select @a // +---------+ | @a | +---------+ | Roger 1 | +---------+ 1 row in set (0.00 sec)
Download Selectintoproc3.myp
drop procedure if exists selectintoproc // create procedure selectintoproc(OUT p_out VARCHAR(10)) begin declare l_emp_name, l_dept_name varchar(30); select emp_name, description into l_emp_name, l_dept_name from emps, dept where emps.dept_id = dept.dept_id limit 1; set p_out := concat(l_emp_name,´ ´,l_dept_name); end; //
Download Selectintoproc4.myp
You can see from these 2 examples how easy it is to select data from tables and place them in variables using select into. One thing to be aware of is that we need to try and make sure that the variable is the same type as the value in the column we want to populate it with. In some cases MySQL will convert the value into the same data type as the variable but this will some times cause the procedure to return incorrect information. For example if you try and place a alpha character into a number variable.
It may be that your procedure is simple enough that it doesn´t need to use a variable. In this case we can assign the value directly to an out parameter.
drop procedure if exists selectintoproc // create procedure selectintoproc(OUT p_out VARCHAR(10)) begin select emp_name into p_out from emps limit 1; end; // call selectintoproc(@a) // Query OK, 0 rows affected (0.00 sec) select @a // +-------+ | @a | +-------+ | Roger | +-------+ 1 row in set (0.00 sec)
Download Selectintoproc5.myp
To select into more than one variable the syntax is as follows
select emp_name,emp_id into p_out1,p_out2 from emps limit 1;
We have now started to see how we can interact with the data within MySQL using procedures, but what would be more useful is if we could alter the select statement on the fly to give us different results depending on a parameter we have passed in. We will be doing just that in our next section....