MySQL——存儲過程與函數（個人筆記整理）

存儲過程的定義

存儲過程是一組為了完成特定功能的SQL語句集，它存儲在資料庫中，一次編譯後永久有效，使用者通過指定存儲過程的名字并給出參數來執行它。

應用場景

例如需要大串複雜的SQL語句，或者說在編寫SQL語句的過程中還需要設定一些變量的值或者循環，這個時候就完全有必要編寫一個存儲過程。

存儲過程的建立和調用

文法：

【建立過程

        開始

        聲明 （變量）

        結束】

CREATE PROCEDURE [name](參數)

BEGIN

DECLARE(聲明)  variable   →定義變量

        [SQL語句塊]

END;

調用：CALL procedure_name(參數) ;

存儲過程的變量

變量定義： DECLARE 變量名1 類型1    【即，聲明變量】

變量指派： SET 變量名1 = 表達式1      【即，設定變量】

# 在查詢視窗使用變量（不需要定義）

SET @a = 1;

SELECT @a;

SET @b = @a*10+3;

SELECT @a,@b;

示例

SET @p_in=10,@p_out=20,@p_inout=30

#建立存儲過程
CREATE PROCEDURE proc_param(
IN p_in INT,OUT p_out INT,INOUT p_inout INT)
BEGIN
SET p_in=p_in+2, p_out=p_out+2, p_inout=p_inout+2;
SELECT p_in,p_out,p_inout FROM dual;
END;

#調用存儲過程
call proc_param(@p_in,@p_out,@p_inout);
select @p__in,@p_out,@p_inout;         #檢視初始變量
           

➢ IN 輸入參數：調用者向過程傳入值，本身不改變。

➢ OUT 輸出參數：調用者向過程傳出值，本身改變。

 ➢ INOUT 輸入輸出參數：既傳入也傳出。

 ➢ 也可以無參數

存儲過程的IF條件判斷

IF語句一般在存儲過程或者函數中使用，如下：

# 建立存儲過程
CREATE PROCEDURE proc_if(in input int)
BEGIN
    IF input>1 then select '大于1';
    elseif input>0 then select '負數';
    else select '其他';
    END if;
END;

#調用存儲過程
call proc_if(10);
           

 存儲過程的while循環

WHILE語句，先判斷後運作

文法

WHILE [判斷語句] DO

[執行内容]  

END WHILE;

示例

CREATE PROCEDURE proc_while(in input int)
BEGIN
DECLARE var int DEFAULT 0;
DROP TABLE IF EXISTS twhile;
CREATE TABLE IF NOT EXISTS twhile(var CHAR(10));
    WHILE var<input DO
    SET var=var+1;
    INSERT INTO twhile VALUES(var);
    END WHILE;
SELECT * FROM twhile;
END;

#調用存儲過程
call proc_while(10);
           

存儲過程的REPEAT循環

REPEAT（意為：重複）語句，先運作後判斷

文法

REPEAT[執行内容]

UNTIL[判斷語句]     -->此處無分号

END REPEAT；

示例 

CREATE PROCEDURE proc_rep(in input int)
BEGIN
DECLARE var int DEFAULT 0;
DROP TABLE IF EXISTS twhile;
CREATE TABLE IF NOT EXISTS twhile(var CHAR(10));
    repeat SET var = var+1;
    INSERT INTO twhile VALUES(var);
    UNTIL var>= input 
    END REPEAT;
SELECT * from twhile;
END;

call proc_rep(3);
           

 存儲過程的LOOP循環

LOOP語句（意為：循環），運作直到遇到LEAVE

文法

[标簽名]:LOOP

[執行内容]

LEAVE[标簽名]

[執行内容]

END LOOP;

示例 

CREATE PROCEDURE proc_loop(in input int)
BEGIN
DELCARE var int DEFAULT 0;
DROP TABLE IF EXISTS twhile;
CREATE TABLE IF NOT EXISTS twhile(var CHAR(10));
    loop1:LOOP    # loop1為标簽名
    SET var=var+1;
    INSERT INTO twhile VALUES(var);
    IF var = input THEN LEAVE loop1;
    END IF;    #結束if語句
    END LOOP;  #結束LOOP循環
SELECT * FROM twhile;
END;      # 結束存儲過程

call proc_loop(5);      #調用存儲過程
           

遊标

定義

遊标的設計是一種資料緩沖區的思想，用來存放SQL語句執行的結果，是在先從資料表中檢索出資料再逐條讀取查詢結果集中的記錄。

在MySQL中并沒有一種描述表中單一記錄的表達形式，除非使

用 WHERE子句來限制隻有一條記錄被選中，是以有時必須借助于遊标來進行逐條記錄的資料處理

 ➢ 在循環處理、存儲過程、函數中使用，用來循環擷取結果集的單個結果。

➢ 特點：1.隻讀：不能被更新    2.不可卷動：即隻能沿一個方向，且不能跳行

➢ 遊标的使用一般分為4個步驟，主要是：定義遊标-->打開遊标-->使用遊标-->關閉遊标

➢ 遊标的使用涉及到4個關鍵詞：DECLARE、OPEN、FETCH（擷取）、CLOSE

1. 定義遊标

文法：

DECLARE <遊标名> CURSOR FOR SELECT 語句        #cursor意為光标

# 示例
DECLARE mycursor CURSOR FOR SELECT id FROM student;
           

2.打開遊标

• open<遊标名> 

3.使用遊标

• 使用遊标需要關鍵字FETCH....INTO來取出資料，取出的資料需要用變量儲存
• FETCH  [NEXT | PRIOR | FIRST | LAST ] FROM <遊标名>INTO [變量名1,變量名2…]
• 變量參數即[變量名1,變量名2,變量名3] 必須在遊标使用之前定義

4.關閉遊标

CLOSE mycursor

CLOSE 釋放遊标使用的所有内部記憶體和資源，每個遊标不再需要時都應該關閉 

執行個體（未使用循環）

CREATE PROCEDURE proc_cur()    #建立存儲過程
BEGIN              
    DECLARE snamel varchar(10);   #聲明變量
    DECLARE id1 INT;
    #1.定義一個遊标mucursor
    DECLARE mycursor CURSOR FOR SELECT id,sname from student;
    #2.打開遊标
    OPEN mycursor;   
    #3.使用遊标
    FETCH NEXT FROM mycursor INTO id1,sname1;
    #4.展示結果
    CLOSE mycursor  #5.關閉遊标
END；          #結束存儲過程

call proc_cur();    #調用存儲過程

    
           

注：不使用循環則隻能傳回一個結果

➢ FETCH是擷取遊标目前指向的資料行，并将指針指向下一行，當遊标已經指向最後一行時繼續執行會造成遊标

溢出的錯誤。

➢ 遊标溢出時會引發MySQL預定義的NOT FOUND錯誤，可以指定當引發NOT FOUND錯誤時定義一個CONTINUE

事件，當這個事件發生時修改DONE變量的值。

✓ DECLARE DONE INT DEFAULT 0; --建立結束标志變量

✓ DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE=1; --指定遊标循環結束時的傳回值

執行個體（使用循環）

使用循環之後可以傳回遊标的所有結果

CREATE PROCEDURE proc_cur_while()       #建立存儲過程
BEGIN                                   #開始
    DECLARE sname VARCHAR(10);          #聲明變量
    DECLARE id INT；
    DECLARE done int default 0;       #建立結束标志變量
    #1.定義遊标
    DECLARE mycursor CURSOR FOR SELECT id,sname from student;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE = 1;    #指定遊标循環結束時的傳回值
    #2.打開遊标
    open mycursor;
    WHILE done = 0 DO
    #3.使用遊标
    FETCH NEXT FROM mycursor INTO id，sname;  
    #4.展示結果
    SELECT id,sname;
    END WHILE;    #結束while循環
    CLOSE mycursor2；    #關閉遊标
END;

call proc_cur_while();
           

檢視存儲過程

#檢視有哪些存儲過程

SELECT name FROM MySQL.proc WHERE db='test1';

#檢視存儲過程的狀态

SHOW procedure status WHERE db='test1';

# 檢視建立存儲過程的代碼

SHOW CREATE PROCEDURE test1.proc_cur;

#删除存儲過程

DROP PROCEDURE IF EXISTS proc_cur_while2;

➢ 優點

• 存儲過程可封裝，并隐藏複雜的商業邏輯。

• 存儲過程可以回傳值，并可以接受參數。

• 增強SQL語言的功能和靈活性。

• 存儲過程可以用在資料檢驗，強制實行商業邏輯等。

➢ 缺點

• 調試麻煩。

• 可移植性差、可維護性差。