MySQL儲存過程及語法詳解

2022-08-04 14:01:33

1. 儲存過程基本用法

1.1 概念

儲存過程，也叫做儲存程式，是一條或者多條SQL語句的集合，可以視為批次處理，但是其作用不僅僅侷限於批次處理。
其中針對儲存過程也有多種操作：如何建立儲存過程，以及如何呼叫、檢視、修改、刪除儲存過程。儲存過程也可以呼叫其他儲存過程。(類似於Java函數之間的相互呼叫)
儲存過程和函數是：事先經過編譯並儲存在資料庫中的一段SQL語句的集合，呼叫儲存過程和函數可以簡化應用開發人員的很多工作，減少資料庫和應用之間的傳輸，對於提高資料處理的效率是非常有好處的。

函數：是一個有返回值的過程；過程：是一個沒有返回值的函數

儲存過程和自定義函數的區別：

儲存過程實現的功能要複雜一些；而函數的針對性更強。
儲存過程可以返回多個值；函數只能有一個返回值。
儲存過程一般獨立的來執行；而函數可以作為其他SQL語句的組成部分實現出來。

1.2 建立儲存過程

1.2.1 語法格式

# 這個地方其實是用來宣告SQL語句的結束符號的
delimiter //

# 這個地方此時真正的用來建立一個儲存過程的
create procedure 儲存過程名稱(參數列)
begin
    -- sql語句
end// 

# 當建立完一個儲存過程之後再將分隔符替換為分號，為了不影響其他的操作
delimiter ;

2.2.2 語法介紹

delimiter : 用於設定sql語句分割符，預設為分號。因為在MySQL中每一條SQL語句都必須以；進行結束，當我們換行的時候就會執行這條SQL語句，但是我們此時的儲存過程並沒有結束，就會造成直接執行沒有寫完的儲存過程造成報錯，所以此時需要宣告其他的結束符，不讓其使用預設的分隔符結束SQL語句。
sql語句 ：在這個部分編寫sql語句，編寫的語句需要以分號結尾，此時回車會直接執行，所以要建立儲存過程前需要指定其他符號作為分割符，此時使用 // , 也可以使用其它字元。
// : 宣告結束符號，這個符號可以是任意的，是自定義的。相當於就是把分號替換為 //

建立儲存過程查詢學生資訊

# 將SQL語句的結束符號分隔符替換為//
delimiter //

create procedure proc_stu()
begin
select * from students; # 此時這個分號並不會結束這個語句,儲存過程中的SQL語句還是用分隔符進行分隔
end // # 這裡使用這個結束符號代表這個儲存過程建立完成

delimiter; # 建立完一個儲存過程之後將結束符號替換為分號，防止進行其他操作的時候有問題

1.3 呼叫儲存過程

語法格式：

call proc_stu(); # 呼叫的時候需要加上括號，因為可能存在引數

1.4 檢視儲存過程

語法格式：

# 查詢 studnet 資料庫中的所有的儲存過程
select name from mysql.proc where db='studnet';

# 查詢儲存過程的狀態資訊
show procedure status;

1.5 刪除儲存過程

語法格式：

drop procedure proc_stu; # 刪除的時候不要加小括號，直接給定儲存過程的名字即可。
drop procedure if exists proc_stu; # 如果儲存刪除，不存在不刪除並且不會報錯

2. 儲存過程中的語法結構

儲存過程是可以程式設計的，意味著可以使用變數、表示式、控制語句來完成比較複雜的功能。

2.1 變數的宣告以及賦值

2.1.1 DECLARE 宣告變數

DECLARE : 通過 DECLARE 關鍵字可以定義一個區域性變數，該變數的作用範圍只能在 BEGIN..,END 塊中。

語法格式：

DECLARE 變數名[,...] type [DEFAULT value]

注意：宣告變數的時候可以一次性宣告多個，使用逗號隔開。

範例：

delimiter $
create procedure proc_stu()
begin
    declare num int default 5;
    select num + 10; # 輸出結果為15
end $
delimiter ;

2.1.2 SET 變數賦值

SET : 直接賦值使用SET關鍵字，可以賦常數或者是表示式，具體語法如下：

SET 變數名 = 變數值 [,變數名 = 變數值] ...

注意：一次可以給多個變數賦值，中間使用逗號隔開。

delimiter $
create procedure proc_stu()
begin
    declare name varchar(20);  # 可以指定變數型別以及變數的範圍
    set name = 'MySQL';  # 給變數直接賦值
    select name; # 輸出結果為：MySQL
end $

delimiter ;

2.1.3 select...into 賦值

delimiter $
create procedure proc_student()
begin
    declare count_num int(10);
    select count(*) into count_num from student;
    select count_num;
end $

delimiter ;

2.2 條件判斷

2.2.1 if條件判斷

語法結構：

# 只有滿足差選條件才會執行 then 後面的SQL語句
if search_condition(查詢條件) then statement_list(SQL語句)
    [else if search_condition(查詢條件) then statement_list(SQL語句)]...
    [else statement_list(SQL語句)]
end if;

需求：

根據身高，判斷當前身高所屬的身材型別
180及以上 --------> 身材高挑
170 - 180 --------> 標準身材
170以下 ----------> 一般身材

實現這個簡單的邏輯：

delimiter $
create procedure pro_figure()
begin 
    # 定義一個身高的變數
    declare height int(11) default 175;
    # 定義一個儲存身高型別的變數
    declare figure varchar(50) default '';
    # 使用 if 語句判斷身材型別
    if height >= 180 then 
        set figure = '身材高挑';
    else if height < 180 and height >= 170 then
        set figure = '標準身材';
    else set figure = '一般身材';
    end if;
    # 輸出結果
    select concat(height + '身高的身材為：' + figure);
end $

delimiter ;

2.3 傳遞引數

語法格式：

delimiter $

# 我們可以不指定 [in/out/inout] , 預設為 in,輸入引數  
create procedure pro_name([in/out/inout]引數名 引數型別)
begin 
    -- sql語句
end $
delimiter ;

# in : 該引數可以作為輸入，呼叫該儲存過程需要傳入的值，預設
# out : 該引數作為輸出，呼叫該儲存過程之後返回的值。
# inout : 既可以作為輸入引數也可以作為輸出引數。

2.3.1 IN - 輸入引數

需求：根據輸入的身高變數的值，判斷當前身高對應的身材型別

實現：

delimiter $
# 此時呼叫者在呼叫這個儲存過程的時候必須傳遞身高的變數值
create procedure pro_name(in height int(11))
begin 
    # 定義一個儲存身高型別的變數
    declare figure varchar(50) default '';
    # 使用 if 語句判斷身材型別
    if height >= 180 then 
        set figure = '身材高挑';
    else if height < 180 and height >= 170 then
        set figure = '標準身材';
    else set figure = '一般身材';
    end if;
    # 輸出結果
    select concat(height + '身高的身材為：' + figure);
end $

delimiter ;

呼叫：

# 呼叫該儲存過程。需要傳遞其中的身高值
call pro_name(175);  # 輸出結果為：

2.3.2 out - 輸出引數

需求：根據輸入的身高，返回當前身高所處的身材型別

實現：

delimiter $
create procedure pro_output(in height int(11) , out figure varchar(100))
begin 
    # 使用 if 語句判斷身材型別
    if height >= 180 then 
        set figure = '身材高挑';
    else if height < 180 and height >= 170 then
        set figure = '標準身材';
    else set figure = '一般身材';
    end if;
end $
delimiter ;

呼叫：

# @識別符號：在MySQL中代表的就是使用者定義的一個變數，這裡我們使用這個變數來接收這個儲存過程的返回值
call pro_output(175 , @figure)；

# 檢視儲存過程返回的結果
select @figure;

@識別符號的作用

@figure ：這種在變數名前面加上”@“符號，叫做使用者對談變數，代表整個對談過程他都是有作用的，這個類似於全域性變數一樣。當前對談就是代表的，比如我們在命令提示視窗中給好多帶有 @ 符號變數進行賦值，此時這些變數的值只作用於當前的對談，當我們把這個視窗關閉的時候，此時這些變數的值就會釋放掉。
@@global : 這種在變數名前加上 "@@" 符號，叫做系統變數。

2.4 case 結構

語法格式：

# 方式一
case case_value(判斷的值)
    when when_value(比較的值) then statement_list(SQL語句)
    [when when_value(比較的值) then statement_list(SQL語句)]...
    [else statement_list(SQL語句)]
end case;

# 方式二
case 
    when search_condition(查詢條件) then statement_list(SQL語句)
    [when search_condition(查詢條件) then statement_list(SQL語句)]...
    [else statement_list(SQL語句)]
end case;

需求：給定一個月份，判斷該月份所屬的季度

實現：

delimiter $
create procedure pro_quarter(in mon int(11))
begin 
    # 定義儲存季度的變數
    declare result varchar(10);
    case 
        when mon >= 1 and mon <= 3 then
            set result = '第一季度';
        when mon >= 4 and mon <= 6 then
            set result = '第一季度';
        when mon >= 7 and mon <= 9 then
            set result = '第一季度';
        else
            set result = '第四季度';
        end case;
    # 輸出結果
    select result;  
end $

delimiter ;

2.5 while迴圈

有條件的迴圈控制語句，當滿足條件的時候進入迴圈，不滿足條件的時候退出迴圈。

語法結構：

# 只要查詢條件一直成立就會一直指定do後面的SQL語句，當查詢條件不成立的時候直接跳出while迴圈
while search_condition(查詢條件) do
    statement_list(SQL語句)
end while;

需求：計算從1加到n的值

實現：

delimiter $
create procedure pro_sum(in num int(11))
begin 
    # 定義儲存總數的變數
    declare total int(255) default 0;
    # 定義儲存迴圈次數的數量
    declare number int(255) default 1;
    while number <= num do
        set total = total + number;
        set number = number + 1;
    end while;
    select total;
end $

delimiter ;

2.6 repeat迴圈

有條件的迴圈控制語句，當不滿足條件的時候進入迴圈，滿足條件的時候跳出迴圈。他和while迴圈是反著的

語法結構：

repeat 
    statement_list(SQL語句)
    until search_condition(查詢新增)
end repeat;

需求：計算從1加到n的值

實現：

delimiter $
create procedure pro_sum(in num int(11))
begin 
    # 定義儲存總數的變數
    declare total int(255) default 0;
    repeat 
        set total = total + number;
        set num = num - 1;
        # 注意：這個 unti 後的查詢條件不要加分號，加分號會報錯。
        until num = 0
    end repeat;
    select total;
end $

delimiter ;

2.7 loop迴圈

loop實現簡單的迴圈，退出迴圈的條件需要使用其他的語句定義，通常可以使用leave語句實現，具體語法如下：

語法格式：

[begin_label:] loop
    statement_list
end loop [end_label]

如果不在statement_list中增加退出迴圈的語句，那麼loop語句可以永安裡實現簡單的死迴圈。

2.8 leave語句

用來從標註的流程構造中退出，通常和 begin...end 或迴圈一起使用。下面是一個使用loop和leave的簡單例子，退出迴圈：

需求：計算從1加到n的值 ---> 使用loop...leave的形式進行退出迴圈

實現：

delimiter $
create procedure pro_sum(in num int(11))
begin 
    # 定義儲存總數的變數
    declare total int(255) default 0;
    
    c(該回圈的別名):loop
        set total = total + num;
        set num = num - 1;
        # 藉助leave組織退出條件
        if num <= 0 then
            leave c;
        end if; 
    end loop c;
    select total;
end $
delimiter ;

到此這篇關於MySQL儲存過程及語法詳解的文章就介紹到這了,更多相關MySQL儲存語法內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！