mysql存儲過程如何利用臨時表返回結(jié)果集
更新時間：2022年09月26日 09:58:47 作者：你好龍卷風(fēng)?。?！
這篇文章主要介紹了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)到達(dá)最后一行的情況
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)到達(dá)了最后作為循環(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
運(yùn)行結(jié)果：
真實(shí)需求，查找出所有用建單情況
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, #機(jī)會點(diǎn)總數(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)到達(dá)最后一行的情況
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)到達(dá)了最后作為循環(huán)條件
while s <> 1 do
#讀取下一行的數(shù)據(jù)
#聲明輸入變量，以便在sql串中拼接
set @userId=a_id;
set @beginDate=begin_date;
set @endDate=end_date;
#收集總機(jī)會點(diǎn) 有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運(yùn)行結(jié)果
以上為個人經(jīng)驗(yàn)，希望能給大家一個參考，也希望大家多多支持腳本之家。
相關(guān)文章
這篇文章主要介紹了Mysql隱式類型轉(zhuǎn)換方式,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2024-04-04
這篇文章主要介紹了MySQL中的嵌套查詢方式,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2024-04-04
這篇文章主要給大家介紹了關(guān)于mysql中包含查詢的五種方法, 在MySQL中,包含語句是一種用來包含其他查詢語句的語句,通常用在WHERE語句中,需要的朋友可以參考下2023-08-08
Navicat Premium15連接云服務(wù)器中的數(shù)據(jù)庫問題及遇到坑
這篇文章主要介紹了Navicat Premium15連接云服務(wù)器中的數(shù)據(jù)庫問題及遇到坑，本文通過圖文并茂的形式給大家介紹的非常詳細(xì)，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值，需要的朋友可以參考下2021-03-03
MySql中取前幾行數(shù)據(jù)使用limit來完成
在mysql中是沒有top關(guān)鍵字的，不過可以用limit來完成此功能，下面舉例為大家詳細(xì)介紹下它的使用方法，不會的朋友可以學(xué)習(xí)下2013-07-07
SQL實(shí)現(xiàn)LeetCode(197.上升溫度)
這篇文章主要介紹了SQL實(shí)現(xiàn)LeetCode(197.上升溫度),本篇文章通過簡要的案例,講解了該項(xiàng)技術(shù)的了解與使用,以下就是詳細(xì)內(nèi)容,需要的朋友可以參考下2021-08-08
詳解MySQL中事務(wù)的持久性實(shí)現(xiàn)原理
這篇文章主要介紹了詳解MySQL中事務(wù)的持久性實(shí)現(xiàn)原理，幫助大家更好的理解和使用MySQL數(shù)據(jù)庫，感興趣的朋友可以了解下2021-01-01
mysql中替代null的IFNULL()與COALESCE()函數(shù)詳解
這篇文章主要給大家介紹了關(guān)于mysql中替代null的IFNULL()與COALESCE()函數(shù)的相關(guān)資料，文中通過示例代碼介紹的非常詳細(xì)，對大家具有一定的參考學(xué)習(xí)價值，需要的朋友們下面來一起看看看吧。2017-06-06
最近看到很多sql里用到where 1=1，原來覺得這沒用嘛，但是又想到如果沒用為什么要寫呢？2011-09-09
MySQL中文亂碼問題的解決...2006-12-12