返回首頁
當前位置: 主頁 > 網絡編程 > Php實例教程 >

mysql 存儲過程

時間:2012-07-16 00:29來源:知行網www.wzliqi.com 編輯:麥田守望者

今天要做一個統計報表,想到使用存儲過程來做。
mysql的存儲過程以前沒有用過,這次想試試,在編寫的過程中遇到了不少問題:
首先,看了官方的文檔 ,不過對于存儲過程,還是要使用才知道。文檔是死的,人是活的。

1.怎么創建存儲過程,自然也是使用一定語法,然后運行,如果成功,會有一個對應的文

件產生,并可以通過 call 來調用。

2.設定變量要注意,不是全局變量(@)時,要先定義(DECLARE year_months VARCHAR(10))

3.設置變量時的語法,在變量前面加 SET 關鍵字,不然報錯

4.直接執行SQL,是可以的,但是如果我們的SQL是動態的,比如我們表名是通過參數傳進來的。

這時間要另外一種方式了,要將sql,使用CONCAT(,,,),來講sql連接起來。還有就是字符串拼接時,

最好使用CONCAT(見下面的)函數,不然會有問題。

下面是我寫的一個實例:

DROP PROCEDURE IF EXISTS `crm_sms_stat`;

CREATE DEFINER=`boss`@`%` PROCEDURE `crm_sms_stat`(IN years varchar(10),IN m varchar(10),IN last_months varchar(10))
BEGIN
DECLARE year_months VARCHAR(10);
DECLARE year_m VARCHAR(10);
DECLARE mt_sms_yearmonth VARCHAR(20);
DECLARE r_year_month VARCHAR(20);
DECLARE last_r_year_month VARCHAR(20);


SET year_months = CONCAT(years,m);
SET year_m = CONCAT(years,'-',m);
SET mt_sms_yearmonth = CONCAT("mt_sms_",year_months);
SET r_year_month = CONCAT(years,"_",m);

IF m = "01" THEN
SET years = years - 1;
SET last_months = "12";
END IF;
SET last_r_year_month = CONCAT(years,"_",last_months);

IF years = "2008" THEN
SET r_year_month = CONCAT("R",r_year_month);
SET last_r_year_month = CONCAT("R",last_r_year_month);
END IF;


/*---------------------------------------------------------------- */
SET @insertStat = CONCAT(' INSERT INTO crm_sms_stat(months,user_id,username,agentid) ',
' select "',year_months,'",u.user_id,u.username,u.agentid from ',mt_sms_yearmonth,
' mt inner join users u on mt.user_id=u.user_id group by mt.user_id ');
PREPARE insertS FROM @insertStat;
EXECUTE insertS;


SET @tempupdateRemain = CONCAT(' UPDATE crm_monthRemain cm,crm_sms_stat css SET css.last_remain = ifnull(',
last_r_year_month,',0),css.this_remain = ifnull(', r_year_month,',0) ',
' WHERE cm.id = css.user_id and css.months = "',year_months,'" ');
PREPARE remain FROM @tempupdateRemain;
EXECUTE remain;

-- 更新每個月用戶所沖條數
SET @tempadd = CONCAT(' UPDATE crm_sms_stat css inner join (select name,sum(total_count) addNum from boss_addmoney ',
' where left(add_date,7)= "',year_m,'" and total_count >=0 and name not like "%=%" and name not like "%:%" group by name) ',
' x on css.username= x.name and css.months = "',year_months ,'" ',
' SET css.add_num = ifnull(x.addNum,0) ');
PREPARE addnum FROM @tempadd;
EXECUTE addnum;




IF years = "08" THEN
-- 更新通道發送數(200901之前是不分卡發和通道的)
SET @tempChannel08 = CONCAT('UPDATE crm_sms_stat css inner join (select mt.user_id,',
' sum(length(replace(mt.dest_mobile,";","")))/11 chennel_num',
' from ',mt_sms_yearmonth,' mt ',
' group by mt.user_id) x on css.user_id = x.user_id and css.months = "',year_months,'"',
' set css.channel_num = ifnull(x.chennel_num,0) ');

PREPARE channel08 FROM @tempChannel08;
EXECUTE channel08;
ELSE
-- channel send number
SET @tempChannel = CONCAT('UPDATE crm_sms_stat css inner join (select mt.user_id,',
' sum(length(replace(mt.dest_mobile,";","")))/11 chennel_num',
' from ',mt_sms_yearmonth,' mt WHERE mt.channel_id != 312 ',
' group by mt.user_id) x on css.user_id = x.user_id and css.months = "',year_months,'"',
' set css.channel_num = ifnull(x.chennel_num,0) ');

PREPARE channel FROM @tempChannel;
EXECUTE channel;
-- card send number
SET @tempcard = CONCAT('UPDATE crm_sms_stat css inner join (select mt.user_id,',
' sum(SUBSTRING_INDEX(SUBSTRING_INDEX(mt.dest_mobile, "real", 1),":",-1)) as submit_card_num,',
' sum(SUBSTRING_INDEX(mt.dest_mobile,":",-1)) as real_card_num',
' from ',mt_sms_yearmonth,' mt WHERE mt.channel_id = 312 ',
' group by mt.user_id) x on css.user_id = x.user_id and css.months = "',year_months,'"',
' set css.submit_card_num = ifnull(x.submit_card_num,0),css.real_card_num = ifnull(x.real_card_num,0) ');
PREPARE card FROM @tempcard;
EXECUTE card;
END IF;

END;
 

------分隔線----------------------------
標簽(Tag):數據庫 MYSQL mysql數據庫
------分隔線----------------------------
推薦內容
猜你感興趣
久久99久久99精品免视看