一个分享个人学习、开发经验的Blog,http://www.joyphper.net

Mysql存储过程中临时表的建立及游标遍历

posted @ 2011-08-03 13:16 | 阅读:6424 | 评论:1 | 分类: 数据库

 最近在做公司月报表的时候出现了一个很让人为难的问题,查询一个支付的过程,内容如下:

两个表:

支付记录表A,支付日志表B

A表内容如下:

B表内容如下:

现在要做的事把A表中的记录关联到B表中字段operate每次checkNo操作的最后一步,当然,这个用程序实现是一个小儿科的事,可是我最也是脑子发热,偏偏只想用sql来实现,在两天研究中终于是实现了。

用到的方法主要有两个,一个是游标的遍历和临时表插入查询

实现代码如下:

 

CREATE DEFINER=`root`@`localhost` PROCEDURE `Jfind`(StartTime DateTime,EndTime DateTime) 
BEGIN 
DECLARE  r int;  
     
    DECLARE Done    INT DEFAULT 0; 
    DECLARE RCode int(3) DEFAULT 0; 
    DECLARE Sid varchar(32); 
    DECLARE SphoneNumber varchar(10); 
    DECLARE ScutPaymentDate datetime; 
    DECLARE Smoney double(10,2); 
    DECLARE SisCutPaymentSucceed  int(2); 
    DECLARE ScheckNO  varchar(32); 
    DECLARE SipAddress  varchar(20); 
     
    /*建立游标*/ 
    DECLARE rs CURSOR FOR SELECT * FROM A where cutPaymentDate> STR_TO_DATE(StartTime,'%Y-%m-%d %H:%i:%s') and cutPaymentDate<STR_TO_DATE(EndTime,'%Y-%m-%d %H:%i:%s') ; 
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done = 1; 
 
    /*创建临时表*/ 
    DROP TABLE IF EXISTS `tmp_paymentReport`; 
    CREATE TEMPORARY TABLE `tmp_paymentReport` (  
        `id` varchar(32) NOT NULL, 
        `phoneNumber` varchar(10) NOT NULL, 
        `cutPaymentDate` datetime NOT NULL, 
        `money` double(10,2) NOT NULL default '0.00', 
        `isCutPaymentSucceed` int(2) NOT NULL, 
        `checkNO` varchar(32) NOT NULL, 
        `ipAddress` varchar(20) NOT NULL, 
        `returnCode` int(3) NOT NULL, 
        PRIMARY KEY  (`ID`) 
        )  TYPE = HEAP; 
 
 
    OPEN rs; /*开启游标*/ 
     
    FETCH NEXT FROM rs INTO Sid,SphoneNumber,ScutPaymentDate,Smoney,SisCutPaymentSucceed,ScheckNO,SipAddress; 
 
    REPEAT 
        IF NOT Done THEN 
 
            SELECT `returnCode`  into RCode from `B` where `checkNO`=ScheckNO order by `operate` desc limit 0,1; 
             
                    INSERT INTO `tmp_paymentReport` set `id`=Sid,`phoneNumber`=SphoneNumber,`cutPaymentDate`=ScutPaymentDate,`money`=Smoney,`isCutPaymentSucceed`=SisCutPaymentSucceed,`checkNO`=ScheckNO,`ipAddress`=SipAddress,`returnCode`=RCode; 
             
                    FETCH NEXT FROM rs INTO Sid,SphoneNumber,ScutPaymentDate,Smoney,SisCutPaymentSucceed,ScheckNO,SipAddress; 
            END IF;      
    UNTIL Done END REPEAT; 
    CLOSE rs; 
     
    set @RunSQL = "select *  from tmp_paymentReport"; 
    prepare smtm from @RunSQL; 
    execute smtm;    
     
END 

 

调用方法是:

 

call Jfind('2011-06-01 00:00:00','2011-07-01 00:00:00'); /*查询六月的记录*/

 

 

TAG: 存储过程 , mysql , 临时表 , 游标遍历

共有1条评论 发表评论>>

到底 发表于:2011-12-16 11:26
滴答滴答滴答滴答
点击换一张验证码