I am writing a procedure to process a MySQL table which name depends on the day, with the form cdr_20131108. My procedure should be able to do some operations from any given date's table.
All those tables have the same structure, and include a time field. For every row, I need to check how many rows meet some criteria in the previous hour. As far as I know, I need a cursor to find the time of the current row, as well as some more data, and then prepare and execute a query, from which I'll find my results.
In pseudo-code:
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `myproc`()
BEGIN
DECLARE all necessary variables to save the data returned by the cursor
DECLARE c1 cursor for
select required fields
from cdr_20131103
where some criteria;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET _done = TRUE;
set @tablename := concat('rm_cdrs',date_format(curdate(),'%Y%m%d'));
set @entries := 0;
open c1;
c1_loop: loop
fetch c1 into my variables;
if `_done` then leave c1_loop; end if;
set @q := concat('... my query is prepared here: select count(*) into @variable (
select in which i look for rows that meet my criteria that
happened less tan an hour before the one in the cursor)');
PREPARE stmt FROM @q; EXECUTE stmt; DEALLOCATE PREPARE stmt;
if some criteria is met then do some stuff...
end loop c1_loop;
close c1;
END
So my problem arises when I need to check today's table without tweaking the code, as MySQL won't let me use a variable here:
DECLARE c1 cursor for
select required fields
from cdr_20131103
where some criteria;
I tried creating a procedure that generates the required procedure with the correct table name here, but MySQL won't let me DROP or ALTER a procedure from a stored routine.
Is there any workaround in this case?
Can I avoid using a cursor in this case?
Should I perform the cursor's task manually, as explained here?
This procedure will probably called periodically using cron. Should I just code some Java/C/PHP app that creates and calls the correct procedure after dropping the old one?
Thank you very much!
EDIT:
@Sebas states that it's possible to perform this operations by using a single insert-select statement. Although the view trick in his answer works like a charm, I would like to try and learn from this. I'll add a Little more information:
When selecting the interesting columns and using a where clause to filter out the data, cdr_20131103 looks like the following:
+---------+----------------+--------+
| user_ID | destination_ID | time |
+---------+----------------+--------+
| 2 | 56 | 110312 |
| 4 | 53 | 110513 |
| 2 | 56 | 110821 |
| 2 | 56 | 113212 | *
| 2 | 56 | 123001 |
+---------+----------------+--------+
I need to find out when the same user_ID has accessed the same destination_ID at least 3 times within an hour. Therefore, the row with * should be inserted in another table, along with a now() field.
My linear mind tells me I should process the rows one by one, compute the initial time (time - interval 1 hour, select the rows with same User_ID and destination_ID in that time lapse, count them and eventual insert them into the other table.
Is there a better way to do this in SQL?
Thank you very very much!
I think you can do all you want with one single select insert SQL. Investigate something like this (assuming tabletoinsert, assuming the column type of time is VARCHAR)
INSERT INTO tabletoinsert
SELECT user_id, destination_id, SUBSTRING(time, 1, 2) AS hour, NOW()
FROM cdr_20131103
GROUP BY user_id, destination_id, SUBSTRING(time, 1, 2)
HAVING COUNT(*) >= 3
But you can also use a dirty trick if you really need a cursor:
1- Create a view dynamically, in another stored procedure
SET @dyn_sql = CONCAT('CREATE OR REPLACE VIEW `v_yourview` AS ',
SELECT required fields
FROM cdr_', date_format(curdate(),'%Y%m%d'), ' WHERE some criteria;');
PREPARE stmt_dyn_view FROM @dyn_sql;
EXECUTE stmt_dyn_view;
DEALLOCATE PREPARE stmt_dyn_view;
2- use it in the cursor of your main procedure:
DECLARE c1 cursor for
select required fields
from v_yourview
where some criteria;