游標(biāo)

游標(biāo)(Cursor)是用于查看或者處理結(jié)果集中的數(shù)據(jù)的一種方法。游標(biāo)提供了在結(jié)果集中一次一行或者多行前進(jìn)或向后瀏覽數(shù)據(jù)的能力。

游標(biāo)的使用方式

定義游標(biāo)：Declare 游標(biāo)名稱(chēng) CURSOR for table;(table也可以是select出來(lái)的結(jié)果集)
打開(kāi)游標(biāo)：Open 游標(biāo)名稱(chēng);
從結(jié)果集獲取數(shù)據(jù)到變量：fetch 游標(biāo)名稱(chēng) into field1,field2;
執(zhí)行語(yǔ)句：執(zhí)行需要處理數(shù)據(jù)的語(yǔ)句
關(guān)閉游標(biāo)：Close 游標(biāo)名稱(chēng);

BEGIN
  # 聲明自定義變量
  declare c_stgId int;
  declare c_stgName varchar(50);
  # 聲明游標(biāo)結(jié)束變量
  declare done INT DEFAULT 0;

  # 聲明游標(biāo) cr 以及游標(biāo)讀取到結(jié)果集最后的處理方式
  declare cr cursor for select Name,StgId from StgSummary limit 3;
  declare continue handler for not found set done = 1;

  # 打開(kāi)游標(biāo)
  open cr;

  # 循環(huán)
  readLoop:LOOP
    # 獲取游標(biāo)中值并賦值給變量
    fetch cr into c_stgName,c_stgId;
    # 判斷游標(biāo)是否到底，若到底則退出游標(biāo)
    # 需要注意這個(gè)判斷
    IF done = 1 THEN
      LEAVE readLoop; 
    END IF; 
    
      SELECT c_stgName,c_stgId;
    
  END LOOP readLoop;
  -- 關(guān)閉游標(biāo)
  close cr;
END

聲明變量Declare語(yǔ)句注意點(diǎn)：

• Declare語(yǔ)句通常用來(lái)聲明本地變量、游標(biāo)、條件或者h(yuǎn)andler
• Declare語(yǔ)句只允許出現(xiàn)在BEGIN...END語(yǔ)句中而且必須出現(xiàn)在第一行
• Declare的順序也有要求，通常是先聲明本地變量，再是游標(biāo)，然后是條件和handler
  

自定義變量命名注意點(diǎn)：

自定義變量的名稱(chēng)不要和游標(biāo)的結(jié)果集字段名一樣。若相同會(huì)出現(xiàn)游標(biāo)給變量賦值無(wú)效的情況。

臨時(shí)表

臨時(shí)表只在當(dāng)前連接可見(jiàn)，當(dāng)關(guān)閉連接時(shí)，Mysql會(huì)自動(dòng)刪除表并釋放所有空間。因此在不同的連接中可以創(chuàng)建同名的臨時(shí)表，并且操作屬于本連接的臨時(shí)表。
與普通創(chuàng)建語(yǔ)句的區(qū)別就是使用 TEMPORARY 關(guān)鍵字

CREATE TEMPORARY TABLE StgSummary(
 Name VARCHAR(50) NOT NULL,
 StgId INT NOT NULL DEFAULT 0
);

臨時(shí)表使用限制

1. 在同一個(gè)query語(yǔ)句中，只能查找一次臨時(shí)表。同樣在一個(gè)存儲(chǔ)過(guò)程中也不能多次查詢(xún)臨時(shí)表。但是不同的臨時(shí)表可以在一個(gè)query中使用。
2. 不能用RENAME來(lái)重命名一個(gè)臨時(shí)表，但是可以用ALTER TABLE代替

ALTER TABLE orig_name RENAME new_name;

• 臨時(shí)表使用完以后需要主動(dòng)Drop掉
  

DROP TEMPORARY TABLE IF EXISTS StgTempTable;

存儲(chǔ)過(guò)程中使用游標(biāo)循環(huán)讀取臨時(shí)表數(shù)據(jù)

BEGIN
## 創(chuàng)建臨時(shí)表
CREATE TEMPORARY TABLE if not exists StgSummary(
 Name VARCHAR(50) NOT NULL,
 StgId INT NOT NULL DEFAULT 0
);
TRUNCATE TABLE StgSummary;

## 新增臨時(shí)表數(shù)據(jù)
INSERT INTO StgSummary(Name,StgId)
select '臨時(shí)數(shù)據(jù)',1

BEGIN

# 自定義變量
declare c_stgId int;
declare c_stgName varchar(50);
declare done INT DEFAULT 0;

declare cr cursor for select Name,StgId from StgSummary ORDER BY StgId desc LIMIT 3;
declare continue handler for not found set done = 1;

-- 打開(kāi)游標(biāo)
open cr;
testLoop:LOOP
	-- 獲取結(jié)果
	fetch cr into c_stgName,c_stgId;
	IF done = 1 THEN
		LEAVE testLoop; 
	END IF; 
	
  
  SELECT c_stgName,c_stgId;
	
END LOOP testLoop;
-- 關(guān)閉游標(biāo)
close cr;

End;
DROP TEMPORARY TABLE IF EXISTS StgSummary;
End;

最開(kāi)始的時(shí)候，先創(chuàng)建臨時(shí)表，再定義游標(biāo)。但是存儲(chǔ)過(guò)程無(wú)論如何都保存不了。直接報(bào)錯(cuò)You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE ...根本原因就是上面提到的注意點(diǎn)（Declare語(yǔ)句只允許出現(xiàn)在BEGIN...END語(yǔ)句中而且必須出現(xiàn)在第一行）。所以最后只能多個(gè)加一對(duì)BEGIN...END進(jìn)行隔開(kāi)。

總結(jié)

以前寫(xiě)SQL Server的存儲(chǔ)過(guò)程，沒(méi)有仔細(xì)注意過(guò)這個(gè)問(wèn)題，定義變量一般都在程序中部，MySQL就想當(dāng)然的隨便寫(xiě)，最后終于踩坑了。這兩個(gè)語(yǔ)法上差別不大，但是真遇到差別還是挺突然的。不過(guò)也好久沒(méi)有寫(xiě)SQL語(yǔ)句，有點(diǎn)生疏了啊。還是趕緊把坑給記下來(lái)，加深下印象吧。

以上就是Mysql 存儲(chǔ)過(guò)程中使用游標(biāo)循環(huán)讀取臨時(shí)表的詳細(xì)內(nèi)容，更多關(guān)于MySQL 游標(biāo)循環(huán)讀取臨時(shí)表的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

最新評(píng)論