[範例] 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)