變數、流程控制和游標

變數

在MySQL資料庫的存盤程序和函式中，可以使用變數來存盤查詢或計算的中間結果資料，或者輸出最終的結果的資料

系統變數

變數由系統定義，屬于服務器層面

系統變數的分類

每一個MySQL客戶機成功連接服務器后，都會產生與之對應的會話（建立一次連接相當于一次會話），MySQL服務實體會在服務器記憶體中生成與該會話對應的系統變數，他們的初值都是全域系統變數值的復制

全域變數（global）

修改針對所有的會話有效，但不能跨重啟（重啟后修改值全面恢復默認值）

會話變數（session）

修改針對當前的會話有效，不會影響其他同一會話系統變數的值

注

如果不寫關鍵字，則默認會話級別

有些系統變數只是全域，有些只是會話，有些既是全域又是會話

查看系統變數

查看所有或部分系統變數

#所有

#查看所有全域變數
SHOW GLOBAL VARIABLES;
#查看所有會話變數
SHOW SESSION VARIABLES;
或是
SHOW VARIABLES;

查詢效果：

#部分

#查看部分會話變數
SHOW GLOBAL VARIABLES LIKE '模糊查詢';
#查看部分會話變數
SHOW SESSION VARIABLES LIKE '模糊查詢'；

查看指定系統變數

MySQL中是以兩個@開始；@@global是全域，@@session是會話 \ 既全域又會話，@@符首先標記會話變數，要是沒有會話變數，則標記系統變數

#查看指定的系統變數
SELECT @@global.變數名;
#查看指定會話變數
SELECT @@session.變數名;
或是
SELECT @@變數名;

修改系統變數

有時要修改系統變數的值，以便修改當前會話或是MySQL服務實體的屬性、特征

具體方法：

方法一：修改組態檔，修改后要重啟服務(服務器如果已經運行，則毫無意義)

方法二：在運行期間，使用 SET 指令

#為某個系統變數賦值：

#全域系統變數：針對當前資料庫實體是有效的，一旦重啟服務器，就失效了
#方式一：
SET @@global.變數名 = 要賦的值;
#方式二：
SET GLOBAL 變數名 = 要賦的值;

#會話系統變數：針對當前會話是有效的，一旦建立起新的會話，就失效了
#方式一：
SET @@session.變數名 = 要賦的值;
#方式二：
SET SESSION 變數名 = 要賦的值;

用戶變數

用戶變數的分類

用戶變數是用戶自定義，在MySQL以一個'@'開頭(主要修飾會話用戶變數)

會話用戶變數：作用域和會話變數一樣，只對當前來連接會話有效
區域變數：只在BEGIN和END中有效，只在存盤程序和函式中使用

會話用戶變數

變數的定義與賦值

#方式一：:=或=
SET @變數名 := 值;
SET @變數名 = 值;

#方式二：:=或INTO
SELECT @變數名 := 運算式[FROM 等句];
SELECT 運算式 INTO @變數名 [FROM 等句];

使用

SELECT @變數名;

區域變數

定義：使用 DECLARE 去定義一個區域變數

作用域：僅在它的 BEGIN...END 中有效

位置：只能放在 BEGIN...END 中，且在第一句

變數定義

DECLARE 變數名 型別 [default 值]#如果沒有默認值，則初始值null

賦值

SET 變數名 = 值；
SET 變數名 := 值；

SELECT 變數名 := 運算式[FROM 等句];
SELECT 運算式 INTO 變數名 [FROM 等句];

使用

SELECT 區域變數名;

舉個例子：

delimiter $
create procedure test_var()
begin 
      declare a int default 0;
      declare b int default 0;
	  #declare a,b int default 0;
      declare emp_name varchar(15);
      
      set a = 1,b := 2; 
      select name into emp_name from emp1 where id = 3;
      select a,b,emp_name;
end $
delimiter ;
call test_var();

對比會話用戶與區域變數

定義條件與處理程式（例外處理）

定義條件：事先定義程式執行程序中可能遇到的問題

處理程式：定義了在遇到問題時應當采取的處理方式，并且保證存盤程序或函式的繼續執行

注：定義條件和處理程式在存盤函式中都是支持的

定義條件

定義條件就是給錯誤代碼命名，將一個錯誤名字和一個指定錯誤條件關聯起來，這個名字可以隨后被用在定義處理程式的DECLARE HANDLER陳述句中

語法格式：

DECLARE 錯誤名字 CONDITION FOR 錯誤碼或錯誤條件(數值型別的錯誤碼);
或是
DECLARE 錯誤名字 CONDITION FOR sqlstate '(字串型別的錯誤代碼)';

錯誤碼說明：

MySQL_error_code和sqlstate_value都可以表示MySQL的錯誤

MySQL_error_code：數值型別錯誤代碼

sqlstate_value：長度為s的字串型別的錯誤代碼

舉個例子

DECLARE Field_Not_Be_NULL CONDITION FOR 1048;
        #錯誤名字                       錯誤碼

定義處理程式

可以為MySQL執行程序中發生的某種型別的錯誤定義特殊的處理程式，(在一開頭就撰寫)

語法格式：

DECLARE 處理方式 HANDLER FOR 錯誤型別 處理陳述句;

處理方式：

CONTINUE:遇到錯誤不處理，繼續執行

EXIT:遇到錯誤馬上退出

UNDD:遇到錯誤后撤回之前的操作，MySQL暫時不支持這樣的操作

錯誤型別（條件）：

SQLSTATE'字串錯誤碼'：長度是s的字串型別錯誤碼

MySQL_error_code:數值型別錯誤碼

錯誤名稱：定義條件起的名字

SQLWARNING:匹配01開頭的SQLSTATE錯誤碼

NOT FOUND:匹配02開頭的SQLSTATE錯誤碼

SQLEXCEPTION:匹配既不是SQLWARNING，也不是NOT FOUND的SQLSTATE型別錯誤碼

處理陳述句

簡單句：SET 變數 = 值;

復雜句：BEGING ... END;

舉個例子

DECLARE CONTINUE HANDLER FOR 1048 SET @error = -1;
      #處理方式：繼續         錯誤碼    讓error = 1

流程控制

類比其他語言的流程控制（我這里會寫的簡單一點），在執行陳述句時記得加上WHERE來限制

控制存盤程序中SQL陳述句的執行順序，

只要是程式，流程就分為三大類

順序結構：從上到下執行
分支結構：按給出的條件執行，二選一或是多選一
回圈結構：在一定條件下，執行一組陳述句

針對于MySQL，的流程控制主要有三類（只用于存盤程式）

條件判斷陳述句：IF陳述句和CASE陳述句
回圈陳述句：LOOP、WHILE、REPEAT陳述句
跳轉陳述句：ITERATE、LEAVE陳述句

分支結構1（IF）

陳述句中可以沒有else

語法格式;

IF 運算式1 THEN 操作1
ELSEIF 運算式2 THEN 操作2
...
ELSE 操作N
END IF;

分支結構2（CASE）

語法格式：

#情況一：
CASE 運算式
WHEN 值1 THEN 操作1;
WHEN 值2 THEN 操作2;
...
ELSE 操作N;
END[case];#BEGIN END 中要加case

#情況二：
CASE 
WHEN 條件1 THEN 操作1;
WHEN 條件2 THEN 操作2;
...
ELSE 操作N;
END[case];#BEGIN END 中要加case

回圈結構1（LOOP）

回圈陳述句有四個條件：

初始條件
回圈條件
回圈體
迭代條件

LOOP內陳述句一直重復執行，知道回圈退出（使用LEAVE子句）

語法格式：

[loop_label:] LOOP
    回圈體;
END LOOP [loop_label];

舉個例子：

#從1一每次加1直到為10輸出
BEGIN
#初始化
SET a int default 1;
loop_label:LOOP
    #回圈主體（此時省略了，程式太過于簡單）
    
    #迭代條件
    SET a = a + 1;
    #回圈條件
    IF a >= 10 THEN LEAVE loop_label;
    END IF;
    
END LOOP loop_label;

END $

回圈結構2（WHILE）

while不控制回圈：while true

語法格式：

[while_label:]WHILE 回圈條件 DO 
        回圈體;
END WHILE [while_label];

回圈結構3（REPEAT）

類似于DO WHILE，至少執行一次

語法格式：

[repeat_label:]REPEAT
        回圈體;
UNTILL 結束回圈陳述句 #沒有;
END REPEAT [repeat_label];

跳轉陳述句1（LEAVE）

類似于break，用在回圈陳述句內或是在BEGIN ...END 中使用，可以跳出回圈體或是程式

要跳出誰，給誰加標簽

語法格式：

LEAVE 標簽名;

舉個例子：

 #部分代碼不完整
 SET a = a + 1;
 IF a >= 10 THEN LEAVE loop_label;
                         #標簽名
 END IF;

跳轉陳述句2（ITERATE）

類似于 continue ,只能在回圈陳述句中使用，跳過本次回圈，進入下一次回圈

語法格式：

ITERATE 標簽名;

游標

什么是游標（游標）

可以定位指定的記錄并可以對其操作（充當指標）

使用游標的步驟

游標必須在宣告處理程式之前宣告，并且變數和條件也必須在宣告游標或是處理程式之前宣告

第一步：宣告游標

MySQL中，使用DECLARE關鍵字來宣告游標，語法格式如下：

DECLARE 游標名 CURSOR FOR 查詢陳述句（結果集）;

如果是Oracle或是PostgreSQL中，語法格式如下：

DECLARE 游標名 CURSOR IS 查詢陳述句（結果集）;

第二步：打開游標

OPEN 游標名;

第三步：使用游標

FETCH 游標名 INTO 查詢結果的欄位1,欄位2,...（要一一對應，名字之間要有關聯，這樣易呼叫）;
#讓游標讀取當前行，游標指標指向下一行

第四步：關閉游標

CLOSE 游標名;

如果不及時關閉，游標會占用系統資源，影響系統運行效率

舉個例子：

#給出一個工資總和的上限數，讓所有員工的工資進行降序排列，并依次相加，直到相加的工資總數大于所給的工資總上限數，計算相加人數

#部分代碼（存盤程序未寫）
上限數：limit_total_salry double(in)
相加人數：total_count int(out)

#宣告區域變數（保存每個人的工資，保存相加的數,保存相加人數）：
DECLARE emp_sal double;
DECLARE sum_sal int default 0;
DECLARE emp_count int default 0;

#宣告游標
DECLARE emp_cursor CURSOR FOR SELECT salary FROM employees ORDER BY salary DESC; 

#打開游標
OPEN emp_cursor;

#使用游標
WHILE sum <= limit_total_salry DO
      FETCH emp_cursor INTO emp_sal;
      SET sum_sal = emp_sal + sum_sal;
      emp_count = emp_count + 1;
END WHILE;

#賦值相加人數
SET total_count = emp_count;

#關閉游標
CLOSE emp_cursor;

游標小結

優點

游標為逐條讀取資料提供了解決方案，

可以在存盤程式中使用，效率高，程式也會更簡潔

不足

會帶來一些性能的問題，使用游標會對資料加鎖，在業務并發量大的時候，會損耗系統資源（所以要養成關閉的習慣）

補充：MySQL8.0的新特性—全域變數的持久化

重啟服務器后，修改的全域變數依舊有效

MySQL8.0新增了 SET PERSIST 命令，格式如下：

SET PERSIST global 全域變數名字 = 1000;

MySQL會將給命令的配置保存到資料目錄下的mysql-auto.cnf檔案中，用其中組態檔來覆寫默認的組態檔

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/445602.html

標籤：其他

上一篇：MySQL基礎_索引

下一篇：如何本地navicat連接虛擬機安裝的linux 的mysql

MySQL變數、流程控制和游標

變數、流程控制和游標

變數

系統變數

系統變數的分類

查看系統變數

修改系統變數

用戶變數

用戶變數的分類

會話用戶變數

區域變數

對比會話用戶與區域變數

定義條件與處理程式（例外處理）

定義條件

定義處理程式

流程控制

分支結構1（IF）

分支結構2（CASE）

回圈結構1（LOOP）

回圈結構2（WHILE）

回圈結構3（REPEAT）

跳轉陳述句1（LEAVE）

跳轉陳述句2（ITERATE）

游標

什么是游標（游標）

使用游標的步驟

游標小結

補充：MySQL8.0的新特性—全域變數的持久化