mysql存儲過程如何利用臨時表返回結(jié)果集
首先要聲明的是
1,游標(biāo)的方法會很慢在mysql中,在oracle數(shù)據(jù)庫中還可以,除非沒有別的方法,一般不建議在mysql中使用游標(biāo),
2,不建議在mysql中拼接sql,會使存儲過程顯得很臃腫,可以使用or來動態(tài)判別傳入的參數(shù)是否為空
and ?( TTB.office_id=輸入?yún)?shù) or ?輸入?yún)?shù) is null ?or 輸入?yún)?shù) = '') and ?( TTB.office_id=IN_Office_id or ?IN_Office_id is null ?or IN_Office_id = '')
3,不建議使用臨時表來存儲多用戶下經(jīng)常查詢的內(nèi)容,比如報表
4,返回結(jié)果集更好的方法是直接鏈接多個表返回結(jié)果集即可,下面的示例雖然給以得到正確結(jié)果,
但代碼臃腫,速度異常的慢,可以當(dāng)個反面教材
5,優(yōu)化后的存儲過程:http://www.dbjr.com.cn/article/263729.htm
測試示例
BEGIN #創(chuàng)建一個臨時表 DROP TABLE if exists user_temporary; create temporary table if not exists user_temporary ( id VARCHAR(64) primary key,#id user_name VARCHAR(20) #姓名 ) ; begin #定義 變量 接收id和姓名 declare a VARCHAR(64); declare b VARCHAR(20); #這個用于處理游標(biāo)到達最后一行的情況 DECLARE s int default 0; #聲明游標(biāo)cursor_name(cursor_name是個多行結(jié)果集) DECLARE cursor_name CURSOR FOR select id ,name from user ; #設(shè)置一個終止標(biāo)記 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET s=1; #打開游標(biāo) OPEN cursor_name; #獲取游標(biāo)當(dāng)前指針的記錄,讀取一行數(shù)據(jù)并傳給變量a,b fetch cursor_name into a,b; #開始循環(huán),判斷是否游標(biāo)已經(jīng)到達了最后作為循環(huán)條件 while s <> 1 do insert into user_temporary(id,user_name) values(a,b); #讀取下一行的數(shù)據(jù) fetch cursor_name into a,b; end while; #關(guān)閉游標(biāo) CLOSE cursor_name ; #從臨時表中拿到結(jié)果集 SELECT * from user_temporary; #語句執(zhí)行結(jié)束 end; END
注意類型 為存儲過程 procedure 不是存儲函數(shù)function
運行結(jié)果:
真實需求,查找出所有用建單情況
BEGIN DROP TABLE if exists user_temporary; create temporary table if not exists user_temporary ( id VARCHAR(64) primary key,id user_name VARCHAR(20) ,#姓名 company_name VARCHAR(20) ,#所屬公司 worksheet_num INTEGER, #機會點總數(shù) sign_worksheet_num INTEGER ,#簽單數(shù)量 exchange_num INTEGER ,#填寫交流記錄次數(shù) sales_volume double(20,2) #銷售額 ) ; begin #定義 變量 declare a_id VARCHAR(64); declare b_user_name VARCHAR(20); declare c_company_name VARCHAR(20); declare d_worksheet_num int ; declare e_sign_worksheet_num int ; declare f_exchange_num int ; declare g_sales_volume double(20,2) ; #這個用于處理游標(biāo)到達最后一行的情況 DECLARE s int default 0; #聲明游標(biāo)cursor_name(cursor_name是個多行結(jié)果集) DECLARE cursor_name CURSOR FOR select a.id ,a.name ,o.name AS company_name from sys_user a LEFT JOIN sys_office o on a.company_id =o.id; #設(shè)置一個終止標(biāo)記 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET s=1; #打開游標(biāo) OPEN cursor_name; #獲取游標(biāo)當(dāng)前指針的記錄,讀取一行數(shù)據(jù)并傳給變量a,b fetch cursor_name into a_id,b_user_name ,c_company_name; #開始循環(huán),判斷是否游標(biāo)已經(jīng)到達了最后作為循環(huán)條件 while s <> 1 do #讀取下一行的數(shù)據(jù) #聲明輸入變量,以便在sql串中拼接 set @userId=a_id; set @beginDate=begin_date; set @endDate=end_date; #收集總機會點 有if判斷用拼接sql,然后再解析執(zhí)行sql, set @exesqlAll =CONCAT('SELECT count(1) into @handle_num FROM crm_worksheet where create_by=@userId and del_flag=0 '); IF begin_date is not null and begin_date !='' THEN set @exesqlAll =CONCAT(@exesqlAll,' AND TO_DAYS(create_date) >=TO_DAYS(@beginDate)'); END IF; IF end_date is not null and end_date !='' THEN set @exesqlAll =CONCAT(@exesqlAll,' AND TO_DAYS(create_date) <=TO_DAYS(@endDate)'); END IF; prepare allWorksheet from @exesqlAll; #解析sql execute allWorksheet; #執(zhí)行sql DEALLOCATE prepare allWorksheet; #釋放sql SET d_worksheet_num = @handle_num; #變量賦值 IF d_worksheet_num > 0 THEN set @exesqlSign =CONCAT('SELECT count(1) into @sign_num FROM crm_worksheet where create_by=@userId and del_flag=0 and important_degree=''sys_basic_qian_shu_he_tong'' '); IF begin_date is not null and begin_date !='' THEN set @exesqlSign =CONCAT(@exesqlSign,' AND TO_DAYS(create_date) >=TO_DAYS(@beginDate)'); END IF; IF end_date is not null and end_date !='' THEN set @exesqlSign =CONCAT(@exesqlSign,' AND TO_DAYS(create_date) <=TO_DAYS(@endDate)'); END IF; prepare signWorksheet from @exesqlSign; execute signWorksheet; DEALLOCATE prepare signWorksheet; SET e_sign_worksheet_num = @sign_num; #收集交流次數(shù) set @exesqlExchange =CONCAT('SELECT COUNT(1) into @exchange_num from crm_wkst_exchange_record e LEFT JOIN crm_worksheet w on e.worksheet_no= w.worksheet_no where w.create_by=@userId and w.del_flag=0'); IF begin_date is not null and begin_date !='' THEN set @exesqlExchange =CONCAT(@exesqlExchange,' AND TO_DAYS(w.create_date) >=TO_DAYS(@beginDate)'); END IF; IF end_date is not null and end_date !='' THEN set @exesqlExchange =CONCAT(@exesqlExchange,' AND TO_DAYS(w.create_date) <=TO_DAYS(@endDate)'); END IF; prepare exchangeWorksheet from @exesqlExchange; execute exchangeWorksheet; DEALLOCATE prepare exchangeWorksheet; SET f_exchange_num = @exchange_num; #收集銷售額 set @exesqlSales =CONCAT('SELECT (@sumSalary := @sumSalary + solution) AS count into @sales_num FROM crm_worksheet cw , (SELECT @sumSalary := 0) b WHERE cw.create_by=@userId and important_degree=''sys_basic_qian_shu_he_tong'' and cw.del_flag=0 ORDER BY count desc limit 1 '); IF begin_date is not null and begin_date !='' THEN set @exesqlExchange =CONCAT(@exesqlExchange,' AND TO_DAYS(cw.create_date) >=TO_DAYS(@beginDate)'); END IF; IF end_date is not null and end_date !='' THEN set @exesqlExchange =CONCAT(@exesqlExchange,' AND TO_DAYS(cw.create_date) <=TO_DAYS(@endDate)'); END IF; prepare salesWorksheet from @exesqlSales; execute salesWorksheet; DEALLOCATE prepare salesWorksheet; SET g_sales_volume = @sales_num; ELSE SET e_sign_worksheet_num=0; SET f_exchange_num=0; SET g_sales_volume=0; END IF; insert into user_temporary(id,user_name,company_name,sign_worksheet_num,exchange_num,worksheet_num,sales_volume) values(a_id,b_user_name,c_company_name,e_sign_worksheet_num,f_exchange_num,d_worksheet_num,g_sales_volume); #插入臨時表 fetch cursor_name into a_id,b_user_name ,c_company_name; end while; #關(guān)閉游標(biāo) CLOSE cursor_name ; #從臨時表中查出結(jié)果集 set @userIdInput=user_id; set @exesqlResult =CONCAT('SELECT user_name,company_name,sign_worksheet_num,exchange_num,worksheet_num,sales_volume from user_temporary'); IF user_id is not null and user_id !='' THEN set @exesqlResult =CONCAT(@exesqlResult,' where id = @userIdInput'); END IF; prepare resultUser from @exesqlResult; execute resultUser; DEALLOCATE prepare resultUser; #語句執(zhí)行結(jié)束 end; END
運行結(jié)果
以上為個人經(jīng)驗,希望能給大家一個參考,也希望大家多多支持腳本之家。
相關(guān)文章
Navicat Premium15連接云服務(wù)器中的數(shù)據(jù)庫問題及遇到坑
這篇文章主要介紹了Navicat Premium15連接云服務(wù)器中的數(shù)據(jù)庫問題及遇到坑,本文通過圖文并茂的形式給大家介紹的非常詳細(xì),對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值,需要的朋友可以參考下2021-03-03MySql中取前幾行數(shù)據(jù)使用limit來完成
在mysql中是沒有top關(guān)鍵字的,不過可以用limit來完成此功能,下面舉例為大家詳細(xì)介紹下它的使用方法,不會的朋友可以學(xué)習(xí)下2013-07-07mysql中替代null的IFNULL()與COALESCE()函數(shù)詳解
這篇文章主要給大家介紹了關(guān)于mysql中替代null的IFNULL()與COALESCE()函數(shù)的相關(guān)資料,文中通過示例代碼介紹的非常詳細(xì),對大家具有一定的參考學(xué)習(xí)價值,需要的朋友們下面來一起看看看吧。2017-06-06