MYSQL的存储过程如何在游标声明中用变量去存储和引用数据库名称

作者:Sender  来源:WaveCN.com  发布日期:2022-02-12  最后修改日期:2022-02-12

多年前,stackoverflow上有人问:“MySQL Stored Procedures : Use a variable as the database name in a cursor declaration”,MYSQL的存储过程如何在游标声明中用变量去存储和引用数据库名称。对于这个问题,我的回答内容是唯一正确的做法,且以后STACKOVERFLOW上再无类似的问题产生。当时回答完了也懒得搬运到自己的网站,反正大家都懂英文是不是,都能上STACKOVERFLOW是不是......只是,现在看还是要搬运回来。

article banner

笔者:国际认证信息系统审计师、软考系统分析师

原文链接如下:
https://stackoverflow.com/questions/1680850/mysql-stored-procedures-use-a-variable-as-the-database-name-in-a-cursor-declar

这个问题产生的时候,刚好我就在研究学习如何写MYSQL的存储过程。于是进行了解答。当然,现在需要先说明的是,无论是什么数据库,存储过程、触发器,这些都是可以发挥强大作用但又极其容易踩坑的功能。尤其是在事务操作中如果涉及调用存储过程或触发器,尤其要小心会否产生死锁。

言归正传。当时提出的问题是:

在声明游标时,需要通过变量实现在查询中给出数据库名称。但MYSQL不允许。因为MYSQL仅允许在游标声明时给出符合标准SQL语法的语句。

提问人尝试了如下两种做法都失败:

尝试1:
CREATE PROCEDURE `update_cdrs_lnp_data`(IN dbName VARCHAR(25), OUT returnCode SMALLINT)
cdr_records: BEGIN
  DECLARE cdr_record_cursor CURSOR FOR
  SELECT cdrs_id, called, calling FROM dbName.cdrs WHERE lrn_checked = 'N';

  # Setup logging
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    #call log_debug('Got exception in update_cdrs_lnp_data');
    SET returnCode = -1;
  END;
END;

尝试2:
CREATE PROCEDURE `update_cdrs_lnp_data`(IN dbName VARCHAR(25), OUT returnCode SMALLINT)
cdr_records:BEGIN
  DECLARE cdr_record_cursor CURSOR FOR
  SET @query = CONCAT("SELECT cdrs_id, called, calling FROM " ,dbName, ".cdrs WHERE lrn_checked = 'N' ");
  PREPARE STMT FROM @query;
  EXECUTE STMT;
  # Setup logging
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    #call log_debug('Got exception in update_cdrs_lnp_data');
    SET returnCode = -1;
  END;
END;

我给出的解决办法,解释如下:

要实现这个想法,必须用3个存储过程去实现。

存储过程1,即proc1,功能类似“数据收集器”。需要把保存了数据库名称的变量,传递给proc1,并由proc1为存储过程proc2创建临时表。需要注意临时表要用

CREATE TEMPORARY TABLE tmp_table_name SELECT ...

这种语法,实现线程安全的临时表。这个临时表名无法随机。

存储过程2,即proc2,对存储过程proc1创建的临时表,声明游标。由于临时表名称是已知并硬编码到声明中,这样就不会出现“table not found”的错误。

存储过程3,即proc3,就像一个“主函数”,带有所有需要传递给proc1和proc2的参数。proc3简单地,首先调用proc1,然后调用proc2,从而完成整个处理过程。

另外,需要设置MYSQL的系统参数变量 sql_notes 为0,否则proc1在执行到 DROP TABLE 命令时就会停止。

最后,代码如下:


CREATE PROCEDURE `proc1`(SourceDBName CHAR(50), SourceTableName CHAR(50))
BEGIN
  DECLARE SQLStmt TEXT;

  SET @SQLStmt = CONCAT('DROP TEMPORARY TABLE IF EXISTS tmp_table_name');
  PREPARE Stmt FROM @SQLStmt;
  EXECUTE Stmt;
  DEALLOCATE PREPARE Stmt;

  SET @SQLStmt = CONCAT('CREATE TEMPORARY TABLE tmp_table_name SELECT ... FROM ',SourceDBName,'.',SourceTableName,' WHERE ... ');
  PREPARE Stmt FROM @SQLStmt;
  EXECUTE Stmt;
  DEALLOCATE PREPARE Stmt;
END$$

CREATE PROCEDURE `proc2`(TargetDBName CHAR(50), TargetTemplateTableName CHAR(50))
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE FieldValue CHAR(50);
  DECLARE CursorSegment CURSOR FOR SELECT ... FROM tmp_table_name;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  OPEN CursorSegment;
  REPEAT
    FETCH CursorSegment INTO FieldValue;
    IF NOT done THEN
    ...
    END IF;
  UNTIL done END REPEAT;
  CLOSE CursorSegment;
END$$

CREATE PROCEDURE `proc3`(SourceDBName CHAR(50), SourceTableName CHAR(50), TargetDBName CHAR(50), TargetTemplateTableName CHAR(50))
BEGIN
  CALL proc1(SourceDBName, SourceTableName);
  CALL proc2(TargetDBName, TargetTemplateTableName);
END$$

本栏目相关
  •  2010-11-04 解决MySQL Cluster 备份总是失败,提示文件已存在的问题
  •  2014-11-02 网站数据库从MySQL 5.0升级到5.6的记录
  •  2009-05-31 放完假回来发现MySQL那边把Bug给确认了。好事!
  •  2009-05-27 发现了MySQL的一个BUG,报告了却没得到处理。
  •  2008-04-30 关于MySQL和Microsoft“合作伙伴”的一些挖掘
  •  2008-04-28 乱想Microsoft 被MySQL选为2008 年度合作伙伴
  •  2022-02-12 MYSQL的存储过程如何在游标声明中用变量去存储和引用数据库名称
  •  2023-08-27 MySQL/MariaDB全量增量备份及合并脚本
  • 微信订阅号二维码

    本页网址二维码:

    本栏目热门内容
  • Acrobat虚拟PDF打印机执行打印时挂起,解决办法竟然...
  • LINKSYS交换机登录WEB界面显示不正确的解决方法
  • 又一次RAID 5阵列故障记录
  • 解决VMware vSphere ESXi 5.0 Update 1 中虚机不能...
  • 修改CentOS发行信息以绕过Dell服务器BIOS更新和DSET...
  • 解决虚拟化运行的 Windows Server 2003 标准版出现...
  • Windows Server 2008 重命名域和域控制器
  • Intel Nehalem CPU Errata 导致 VMWare ESXi(vSpher...
  • 一次很精神的电脑组装过程记录(但不是自己的电脑)...
  • 解决MySQL Cluster 备份总是失败,提示文件已存在的...
  • MegaCli安装及使用杂记
  • 解决WSUS显示客户端不全的问题
  • 解决 VMWare vSphere 6 客户端无法修改用户密码
  • 解决Windows Server 2008 R2域控制器显示无法连接到...
  • 本站服务器RAID 5阵列双硬盘失效挽救记录
  • 网站数据库从MySQL 5.0升级到5.6的记录
  • 解决MariaDB使用Percona XtraBackup增量备份出错
  • DELL PowerEdge 820 报CPU3 INTERNAL ERROR 的解决...
  • Linux 下的分区调整工具GParted实战
  • 修改arpwatch使通知邮件主题显示IP地址
  • 程序员漫画:如何用8种不同的编程语言去解救公主
  • 解决很好用的多合一即时通信软件pidgin的崩溃问题
  • 解决Samba WINS服务的错误解释问题
  • 使用 GParted 进行虚拟机硬盘分区调整操作
  • 解决Squid代理HTTP时在浏览器出现Content Encoding ...
  • 用Delphi编写使用到ADO的DLL的一些问题
  • 网站简单改版
  • 索尼系列手提电脑备份失败,出现700错误的解决办法
  • Dell R900服务器 BMC firmware incompatible with C...
  • 更多...