星期一, 5月 14, 2012

[範例] MySQL stored procedure : 查詢五天內cdr表格

需求:
查詢五天內,cdr表格:
                    cdr20120514 , cdr20120513...cdr20120510表格中, 有透過某電話叫修的紀錄

SQL 虛擬碼 :
select * from
(
select * from test.cdr20120514  where phone_no='0931xxxxxx'
union
select * from test.cdr20120513  where phone_no='0931xxxxxx'
.
.
.
)
where Ans_Time >= '2012-05-10 00:00:00';

我把需求寫成了以下的store procedure

DELIMITER //
DROP PROCEDURE IF EXISTS test.CR_PROC;
CREATE PROCEDURE test.CR_PROC( PHONE_ID VARCHAR(15) )
      BEGIN
              DECLARE day INT;
              DECLARE Ans_Time  VARCHAR(30);
              DECLARE CR_TB_Name  VARCHAR(18);
              DECLARE sql_text  VARCHAR(300);
              SET day = 1;
              SET Ans_Time =   DATE_FORMAT(current_date() , '%Y-%m-%d 00:00:00');
              SELECT Ans_Time ;
              SET @CR_TB_Name := CONCAT('cdr', DATE_FORMAT(current_date() , '%Y%m%d') );
              SET @sql_text := concat('select * from test.',@CR_TB_Name, ' where phone_no=''',PHONE_ID ,'''' );
              select @sql_text;
              WHILE day  < 5 DO
                SET @CR_TB_Name := CONCAT('cdr', DATE_FORMAT(current_date() -day , '%Y%m%d') );
                SET Ans_Time =   DATE_FORMAT(current_date() - day , '%Y-%m-%d 00:00:00');
                SET @sql_text = concat(@sql_text ,' union select * from test.',@CR_TB_Name, ' where phone_no=''',PHONE_ID ,'''' );
                SET  day  = day  + 1;
              END WHILE;          
              select @sql_text;
              SET @sql_text = concat('select * from (',@sql_text ,') as total_cr where total_cr.Answer_Time >=''', Ans_Time,'''');
              select @sql_text;
              PREPARE stmt FROM @sql_text;
              EXECUTE stmt;
              DEALLOCATE PREPARE stmt;

END //
DELIMITER ;

                                                                                      
如何使用(USAGE):

CALL test.CR_PROC('0931xxxxxx');   ==> 這裡面帶的是phone_no


以下就是 select @sql_text; 會印出來的 SQL Statement , 並執行之
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| select * from (select * from test.cdr20120514 where phone_no='0931xxxxxx' union select * from test.cdr20120513 where phone_no='0931xxxxxx' union select * from test.cdr20120512 where phone_no='0931xxxxxx' union select * from test.cdr20120511 where phone_no='0931xxxxxx' union select * from test.cdr20120510 where phone_no='0931xxxxxx') as total_cr where total_cr.Answer_Time >='2012-05-10 00:00:00' |

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)


沒有留言:

LinkWithin-相關文件

Related Posts Plugin for WordPress, Blogger...