微信订阅号二维码
本栏目热门内容
  • Acrobat虚拟PDF打印机执行...
  • LINKSYS交换机登录WEB界面...
  • 又一次RAID 5阵列故障记录...
  • 解决VMware vSphere ESXi ...
  • 修改CentOS发行信息以绕过...
  • Windows Server 2008 重命...
  • 解决虚拟化运行的 Windows...
  • Intel Nehalem CPU Errata...
  • 某卢瑟装机搞得一踏糊涂,...
  • 解决MySQL Cluster 备份总...
  • MegaCli安装及使用杂记
  • 解决WSUS显示客户端不全的...
  • 解决 VMWare vSphere 6 客...
  • 解决Windows Server 2008 ...
  • 本站服务器RAID 5阵列双硬...
  • 网站数据库从MySQL 5.0升...
  • 解决MariaDB使用Percona X...
  • 修改arpwatch使通知邮件主...
  • Linux 下的分区调整工具GP...
  • DELL PowerEdge 820 报CPU...
  • 程序员漫画:如何用8种不...
  • 解决很好用的多合一即时通...
  • 使用 GParted 进行虚拟机...
  • 解决Samba WINS服务的错误...
  • 解决Squid代理HTTP时在浏...
  • 用Delphi编写使用到ADO的D...
  • 网站简单改版
  • 索尼系列手提电脑备份失败...
  • Dell R900服务器 BMC firm...
  • 更多...

    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是不是......只是,现在看还是要搬运回来。

    原文链接如下:
    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的存储过程如何在游标声明中用变量去存储和引用数据库名称