mysql - 如何在存储过程中创建变量游标？

Question

我正在编写一个过程来处理一个 MySQL 表，该表的名称取决于日期，格式为 cdr_20131108。我的程序应该能够从任何给定日期的表中执行一些操作。

所有这些表都具有相同的结构，并包含一个time 字段。对于每一行，我需要检查前一小时有多少行满足某些条件。据我所知，我需要一个游标来查找当前行的时间以及更多数据，然后准备并执行一个查询，从中我会找到我的结果。

在伪代码中:

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

所以当我需要在不调整代码的情况下检查今天的表时，我的问题就出现了，因为 MySQL 不允许我在这里使用变量:

DECLARE c1 cursor for
select required fields
from cdr_20131103
where some criteria;

我尝试创建一个过程，在此处使用正确的表名生成所需的过程，但 MySQL 不允许我从存储例程中删除或更改过程。

在这种情况下有什么解决方法吗？

• 在这种情况下我可以避免使用游标吗？

• 我是否应该手动执行游标的任务，如说明的那样here ?

• 此程序可能会使用 cron 定期调用。我是否应该只编写一些 Java/C/PHP 应用程序，以便在删除旧程序后创建并调用正确的程序？

非常感谢!

---

编辑:

@Sebas 声明可以通过使用单个插入-选择语句来执行此操作。尽管他的回答中的 View 技巧很有魅力，但我想尝试从中学习。我将添加更多信息:

选择有趣的列并使用Where子句过滤数据时， cdr_20131103 看起来如下:

+---------+----------------+--------+
| user_ID | destination_ID |  time  |
+---------+----------------+--------+
|       2 |             56 | 110312 |
|       4 |             53 | 110513 |
|       2 |             56 | 110821 |
|       2 |             56 | 113212 | *
|       2 |             56 | 123001 |
+---------+----------------+--------+

我需要查明同一个 user_ID 何时在一个小时内至少访问了同一个 destination_ID 3 次。因此，带有 * 的行应该连同 now() 字段一起插入到另一个表中。

我的线性思维告诉我应该逐行处理，计算初始时间(time - interval 1 hour，选择在那段时间内具有相同 User_ID 和 destination_ID 的行，计算它们并最终将它们插入到另一个表中。

有没有更好的方法在 SQL 中执行此操作？

非常非常感谢!

Answer 1

我认为您可以使用一个select insert SQL 来完成您想要的所有工作。调查类似这样的事情(假设tabletoinsert，假设time的列类型是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

但是如果你真的需要一个游标，你也可以使用一个肮脏的技巧:

1- 在另一个存储过程中动态创建 View

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- 在主程序的光标中使用它:

DECLARE c1 cursor for
    select required fields
    from v_yourview
    where some criteria;