Cursors are basically named SQL statement which we can be defined in our procedures. We can then easily look at the contents of the cursor by fetching the records from it. It’s a way for us to get information from our tables in one big chunk and then work on it. As always there is no better way to find out about them than actually doing one but because cursors require a little more code than things we have looked at previously we will need to look at the various stages before writing a procedure which uses cursors.
Declaring Cursors
As with variables, conditions and handlers we need to declare a cursor before we can use it. The syntax for this is as follows.
DECLARE cursor_name CURSOR FOR select_statement;
The first thing we need to do is give the cursor a name, this is how we will refer to it later in the procedure. We can have more than one cursor in a single procedure so it’s important to give it a name that will in some way tell us what it’s doing. We then need to specify the select statement we want to associate with the cursor. The SQL statement can be any valid SQL statement and it is possible to use a dynamic where clause using variable or parameters as we have seen previously.
Open, Fetch and Close
Once the cursor has been declared we can then use it. The first thing we need to do is open the cursor. The syntax to do so is very simple.
OPEN cursor_name;
The cursor name is simply the name of the cursor that you defined earlier. Once opened, we can then fetch values from the cursor. To do this we need to have set up variables to hold the values, we need one variable per column defined in our cursor select statement, and we will look at this in more detail in few moments. The syntax for fetch is as follows.
FETCH cursor_name INTO var_name [, var_name] …
Again cursor_name is the name of the cursor we want to fetch from, var_name is the name of the variable we have defined to accept the value. Once we have fetched the rows we need, we then need to close the cursor. The syntax for this is as simple as opening the cursor.
CLOSE cursor_name
So we are now at the point where we can define a simple cursor, so let’s do just that.
drop procedure if exists cursorproc
create procedure cursorproc(IN p_in INT, OUT p_out VARCHAR(30))
begin
declare l_emp_name VARCHAR(30);
declare cur_1 cursor for select emp_name from emps where emp_id = p_in;
open cur_1;
fetch cur_1 into l_emp_name;
close cur_1;
set p_out = l_emp_name;
end;
Here we have created a very basic cursor. We declared the cursor and gave it a name of cur_1. We then defined the SQL statement we wanted to use, in this case we used a where clause using an in parameter. We then opened the cursor fetched the single column value into a variable then closed the cursor. Finally we passed the value back out. Let’s see it in action.
set @a = 1 //
Query OK, 0 rows affected (0.05 sec)
call cursorproc(@a,@b) //
Query OK, 0 rows affected (0.22 sec)
select @b //
+——-+
| @b |
+——-+
| Roger |
+——-+
1 row in set (0.00 sec)
Our cursor returned a value in the same way a select into would. It’s unlikely that you would use a cursor like this. Cursors are useful for processing multiple rows rather than single values. To do this we need to add some more code to our procedure to handle the processing of the cursor.
drop procedure if exists cursorproc
create procedure cursorproc(OUT p_out DECIMAL(5,2))
begin
declare l_loop_end INT default 0;
declare l_salary, l_total DECIMAL(5,2);
declare cur_1 cursor for select salary from emps;
declare continue handler for sqlstate ´02000´ set l_loop_end = 1;
open cur_1;
set l_total = 0;
repeat
fetch cur_1 into l_salary;
if not l_loop_end then
set l_total = l_total + l_salary;
end if;
until l_loop_end end repeat;
close cur_1;
set p_out = l_total;
end;
Our procedure has become a lot more complicated and needs a lot of explanation. You will be able to see a number of line of code which we haven´t come across before. We won´t dwell on those too much for now as we will be looking at them in detail in later sessions.
The first thing we have done is declare 3 variables, the first l_loop_end is in effect a boolean variable (true or false). This is implemented in MySQL using an INT variable where 0 is false and 1 is true, this variable will help us find out when we come to the end of the cursor. We then declare 2 decimal values and these will be used to accept the value from the cursor during the fetch and to keep a running total. We then declare the cursor in much the same way as we did before the main difference being we are selecting the salary value and that we don´t have a where clause so we will get the full table. Next we declare a handler, this is why we looked at handlers and conditions before we dealt with cursors. The handler will be dealing with the SQLSTATE 02000 which is raised when there is no further data to be fetched, this will allow us to catch the event and handle it in a controlled manner. We then open the cursor, set the value in l_total to 0 and then use the repeat keyword. We will be looking at repeat in a later session but for now it´s enough to know that it simply repeats the code between repeat and end repeat until an exit condition is found. We then fetch the value from the cursor into the variable, its at this stage that we may encounter SQLSTATE 0200, if we have the SQLSTATE associated with the handler it will fire and l_loop_end will be set to 1 (true). We then use an if statement. If allows us to apply conditional logic to the program, again we will be looking at this in more detail, for the moment we just need to know that if l_loop_end is false (SQLSTATE 0200 hasn´t been raised) we will add l_salary to the running total. We then get to until, as we mentioned earlier this is linked to the repeat, at this stage it checks to see if l_loop_end is true, if it is then the repeat will be stopped if not it will repeat the code again.
In our example we would expect the repeat to be performed 3 times as we have 3 rows in our emps table. This will result in the l_total variable adding the salary from the 3 rows together. Lets run the procedure and see what we get.
call cursorproc(@a) //
Query OK, 0 rows affected (0.00 sec)
mysql> select @a //
+——+
| @a |
+——+
| 6600 |
+——+
1 row in set (0.00 sec)
This is another case where we could have simply done this using an SQL statement in the database, but as you have seen cursors are a little complicated and this was a simple example so that you could get the basic syntax and functionality without being too overwhelmed.
MySQL´s implementation of cursors is a little limited at present and does not offer the extended functionality of languages such as T-SQL and PL/SQL, but they are an important part of stored procedure development and hopefully in future releases AB will extend the functionality of cursors. A few additional points to note about cursors within MySQL, they are non-scrolling, read only and sensitive. Non-scrolling means that we can only go through a cursor from top to bottom, we can´t go to a particular row or go back a row. Asensitive means that the server may or may not make copy of the results table.
We will move on now as further talk of cursors may become confusing without knowing a little more about conditional logic. We will return to cursors a little later.
No comments:
Post a Comment