存储过程是在数据库服务器中执行的程序，它们通常使用数据库语言如PL/SQL或ANSI SQL：2003 SQL/PSM编写（有些数据库还支持Java编写存储过程，但我这里不对其进行分析），目前市面上也有大量的学习编写存储过程的书籍，笔者推荐阅读Guy Harrison的《MySQL存储过程编程》，以及Jonathan Gennick 和Tom Luers合著的《21天自学PL/SQL》，为什么要编写存储过程呢，归纳起来大概有三方面的原因： 1、正在实现的逻辑可能是数据库逻辑，因此使用数据库语言比使用常规编程语言如Java更容易解决问题； 2、存储过程比Java执行速度要快得多，并且可以多由数据库多次调用； 3、存储过程更安全。无论选择编写存储过程的原因是什么，现在的问题是该如何进行问题调试。
行不通的办法
在存储过程中调试SQL：假设你的存储过程的主要逻辑是DDL和DML操作，这个方法才能工作，换句话说就是只有查询、插入等语句，同时假设其它存储过程主要承担支持数据库操作，在多数情况下这个假设是成立的，毕竟如果存储过程不操作数据库，你也多半不会为此专门编写存储过程了。
毫无疑问，无论存储过程中包括了多少非SQL代码，你都需要对SQL本身进行验证，特别是因为这一级别的测试相对要简单些，只需要启动数据库命令行工具或查询GUI浏览器，将SQL语句粘贴进去执行一下就知道是否正确没有，当然，这已经超出了简单的语法正确性检查，你必须亲自验证其语法的正确性。
但在某些情况下并是想象中那么简单，原因有两个。首先，你的SQL代码可以(通常都是“会”)依赖存储过程定义的和/或操作的变量和参数，如果你有一条SELECT语句，它将它的结果存储到一个变量中，后面的SQL语句再来使用该变量，那么这种粘贴到命令行的测试方法可能就行不通了。这个时候，你不得的插入一条或多条语句，执行它们，也许还需要创建临时变量，并且在真正测试的时候可能还要修改SQL，这种情况会逐渐发生，但你可以达到某个点，不用再从头开始测试了。
使用这种方法的第二个问题是通常情况下，存储过程的逻辑是放在过程的程序代码中的而不是SQL语句中的，存储过程常常用于实例化业务逻辑，通常是通过存储过程嵌入到代码流中的，在这种情况下只是对SQL语句进行一下简单的替补测试，而不是真正的测试存储过程。
在存储过程中插入Print语句
另一个方法是在你的存储过程中放置大量的Print语句，这种做法在实际中非常有用，特别是在早期开发阶段，每个数据库都有其自己的处理打印语句的方法，例如，当你使用MySQL concat( )调用构建一个字符串输出时，你必须小心空值，它将你的整个字符串转为空值，象下面这样的代码就很危险：
select someColumn from someTable into myVar where.
concat('better hope myVar is not null', myVar);
如果where条件过滤结果为空(没有返回一行数据)，那么myVar可能就为空，concat的输出也就为空了，因此，最好使用concat_ws("delimiter", "text to store")，它可以很好地处理空值。
按照这种方法使用Print语句有两个主要的缺点，首先，在生产过程中Print语句是活动的，除非你为每个Print语句配一个标签加以保护，这就意味着如果你要一直记录的话，就需要牺牲大量的性能。
第二个缺点更严重，如果你的存储过程是从Java应用程序中调用的，Print语句不会有任何动作，只有从命令行执行存储过程时Print语句才会被接受。
制定严格的返回代码
在这个方法中，你需要为所有情况制定详细的返回代码，通过给定的代码你就知道存储过程执行时发生的情况了，理论上这是一个非常棒的方法，但在实际生产中可能会让人崩溃的，返回代码可能告诉你最后出错的存储过程，但更重要的是要知道导致失败的原因。
换句话说，如果你在凌晨3点接到一个非常重要的客户打来的技术支持电话，此时你肯定会希望客户告诉你返回的错误代码的好多。
推荐的方法
目前的方法要使用多个MySQL特殊功能创建日志模式，既强壮又自由。首先，我们创建两个表：一个使用Memory引擎的临时日志表，一个使用MyISAM引擎的永久性日志表。MySQL支持为不同的表使用不同的存储引擎，MySQL存储引擎包括那些处理事务安全的表和非事务安全的表。Memory引擎在内存中执行所有的操作，永远都不会写到磁盘上去，因此它的速度非常快，但却是临时的。MyISAM引擎是一个非事务安全引擎，事务可以包括事务型和非事务型表(非事务型表忽略事务命令)。
日志消息插入到tmplog表中，因为表在内存中，实际上它是一个非常自由的操作，这个操作的成本非常低，因此开发人员可以很详细地使用日志记录，不用再为是否要记录什么或不记录什么烦恼了。
通常情况下不会发现什么问题，存储过程没有做任何事情，临时表也仅仅在连接时存在，在典型的J2EE使用模式中，当一个外部请求抵达系统中，首先从连接池取出一个连接，然后又返回到连接池，当连接返回到连接池时，临时表就被删除了，代码并未作出删除日志的任何操作，以这种方式系统几乎没有什么性能开销。
当存储过程检测到故障时，它会从临时内存表中向永久性MyISAM表中插入记录，因此它将所有写入到临时表中的日志消息都插入到MyISAM表了，这样，系统就记录了所有的信息，但只有在需要的时候才需要用到。
一个重要的值得注意的事情是要为永久性日志表选择MyISAM引擎，记住，通常是事情变得糟糕的时候才会向这个表写入数据，这常常会导致当前的事务回滚，MyISAM引擎不是事务型引擎，这意味着即使一个事务回滚了，插入到日志表中的日志仍然保留下来了，这正是期望的行为。
准则
在debugLogging.sql包中定义了4个存储过程，其中两个用于创建将要用到的表，一个执行临时日志记录，最后一个将日志消息拷贝到永久性表中。
第一个存储过程创建临时表和永久性表，注意使用的引擎类型以便区分这两个表的类型，临时日志表只包括一个列msg，永久性表添加了自动时间戳和thingID列，假设在创建、破坏、修改某些对象期间写入了日志，并且那些对象有唯一的标识符，在我的视频点播空间中，它可能是流向客户机顶盒的电影的标识符。
Create Procedure setupLogging()
BEGIN
create temporary table if not exists tmplog (msg varchar(512)) engine = memory;
create table if not exists log (ts timestamp default current_timestamp, thingID bigint,
msg varchar(512)) engine = myisam;
END;
第二个存储过程只创建临时日志表，我们看到在我们需要插入数据时临时表不存在，此时我们可以使用一个存储过程来重新创建。
Create Procedure setupTmpLog()
BEGIN
create temporary table if not exists tmplog (msg varchar(512)) engine = memory;
END;
第三个存储过程是执行次数最多的，它真正用于记录日志，一个消息参数被写到临时表中，如果临时表不存在，这里提供了一个继续创建临时表的处理程序。
日志消息插入到tmplog表中，因为表在内存中，实际上它是一个非常自由的操作，这个操作的成本非常低，因此开发人员可以很详细地使用日志记录，不用再为是否要记录什么或不记录什么烦恼了。
通常情况下不会发现什么问题，存储过程没有做任何事情，临时表也仅仅在连接时存在，在典型的J2EE使用模式中，当一个外部请求抵达系统中，首先从连接池取出一个连接，然后又返回到连接池，当连接返回到连接池时，临时表就被删除了，代码并未作出删除日志的任何操作，以这种方式系统几乎没有什么性能开销。
当存储过程检测到故障时，它会从临时内存表中向永久性MyISAM表中插入记录，因此它将所有写入到临时表中的日志消息都插入到MyISAM表了，这样，系统就记录了所有的信息，但只有在需要的时候才需要用到。
一个重要的值得注意的事情是要为永久性日志表选择MyISAM引擎，记住，通常是事情变得糟糕的时候才会向这个表写入数据，这常常会导致当前的事务回滚，MyISAM引擎不是事务型引擎，这意味着即使一个事务回滚了，插入到日志表中的日志仍然保留下来了，这正是期望的行为。
准则
在debugLogging.sql包中定义了4个存储过程，其中两个用于创建将要用到的表，一个执行临时日志记录，最后一个将日志消息拷贝到永久性表中。
第一个存储过程创建临时表和永久性表，注意使用的引擎类型以便区分这两个表的类型，临时日志表只包括一个列msg，永久性表添加了自动时间戳和thingID列，假设在创建、破坏、修改某些对象期间写入了日志，并且那些对象有唯一的标识符，在我的视频点播空间中，它可能是流向客户机顶盒的电影的标识符。
Create Procedure setupLogging()
BEGIN
create temporary table if not exists tmplog (msg varchar(512)) engine = memory;
create table if not exists log (ts timestamp default current_timestamp, thingID bigint,
msg varchar(512)) engine = myisam;
END;
第二个存储过程只创建临时日志表，我们看到在我们需要插入数据时临时表不存在，此时我们可以使用一个存储过程来重新创建。
Create Procedure setupTmpLog()
BEGIN
create temporary table if not exists tmplog (msg varchar(512)) engine = memory;
END;
第三个存储过程是执行次数最多的，它真正用于记录日志，一个消息参数被写到临时表中，如果临时表不存在，这里提供了一个继续创建临时表的处理程序。
Create Procedure doLog(in logMsg varchar(512))
BEGIN
Declare continue handler for 1146 -- Table not found
BEGIN
call setupTmpLog();
insert into tmplog values('resetup tmp table');
insert into tmplog values(logMsg);
END;
insert into tmplog values(logMsg);
END;
最后一个存储过程当用户的存储过程中检测到错误时会调用它，以及所有日志需要进行永久性写入便于以后分析时会调用它，这个存储过程产生最后的消息，大概是关于错误的原因是什么的一句话，然后将临时表中的所有记录插入到永久性表中，并自动设置时间戳。为了解决遇到错误时用户选择继续，然后进入到下一个错误的情况，当全部插入到永久性表后，临时表中的数据将全部删除。
Create Procedure saveAndLog(in thingId int, in lastMsg varchar(512))
BEGIN
call dolog(lastMsg);
insert into log(thingId, msg) (select thingId, msg from tmplog);
truncate table tmplog;
END;
使用举例
下面的代码示例显示了一个可能的日志存储过程的使用情况，这个例子是基于一个存储过程可以调用另一个存储过程的概念设计的，存储过程的主要任务是解析一个以逗号分隔的值列表，并将每个值插入到缓存表中，然后调用另一个存储过程进行查询，它说明了值能够被记录，而不仅仅是最终结果，按照这个步骤循环下去，就可以知道究竟是在哪一步出现的故障。
Create Procedure parseAndStoreList(in thingId int, in i_list varchar (128), out returnCode smallInt)
BEGIN
DECLARE v_loopIndex default 0;
DECLARE Exit Handler for SQLEXCEPTION BEGIN call saveAndLog(thingId, 'got exception parsing list');
-- save the logs if things go badly set returnCode = -1;
END;
call dolog(concat_ws('got list:', i_list));
-- say we got to the start
pase_loop: LOOP
set v_loopIndex = v_loopIndex + 1;
call dolog(concat_wc(',', 'at loop iteration ', v_loopIndex);
-- say we got to nth iteration
-- actually do the parsing, or whatever
END LOOOP parse_loop;
set returnCode = 0;
END;
小结
无开销或开销极低的存储过程日志记录被证明是一种极其有用的技术，你可以利用它对代码进行广泛的检查，可以讲起运用到生产环境，但建议当你确实需要时才运用。
扫一扫
317
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
