MySQL存儲過程及語法詳解

更新時間：2022年08月04日 09:27:00 作者：Java學術(shù)趴

這篇文章主要介紹了MySQL存儲過程及語法詳解，存儲過程，也叫做存儲程序，是一條或者多條SQL語句的集合，可以視為批量處理，但是其作用不僅僅局限于批量處理

1. 存儲過程基本用法

1.1 概念

存儲過程，也叫做存儲程序，是一條或者多條SQL語句的集合，可以視為批量處理，但是其作用不僅僅局限于批量處理。
其中針對存儲過程也有多種操作：如何創(chuàng)建存儲過程，以及如何調(diào)用、查看、修改、刪除存儲過程。存儲過程也可以調(diào)用其他存儲過程。(類似于Java函數(shù)之間的相互調(diào)用)
存儲過程和函數(shù)是：事先經(jīng)過編譯并存儲在數(shù)據(jù)庫中的一段SQL語句的集合，調(diào)用存儲過程和函數(shù)可以簡化應(yīng)用開發(fā)人員的很多工作，減少數(shù)據(jù)庫和應(yīng)用之間的傳輸，對于提高數(shù)據(jù)處理的效率是非常有好處的。

函數(shù)：是一個有返回值的過程；過程：是一個沒有返回值的函數(shù)

存儲過程和自定義函數(shù)的區(qū)別：

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

1.2 創(chuàng)建存儲過程

1.2.1 語法格式

# 這個地方其實是用來聲明SQL語句的結(jié)束符號的
delimiter //
?
# 這個地方此時真正的用來創(chuàng)建一個存儲過程的
create procedure 存儲過程名稱(參數(shù)列表)
begin
    -- sql語句
end// 
?
# 當創(chuàng)建完一個存儲過程之后再將分隔符替換為分號，為了不影響其他的操作
delimiter ;

2.2.2 語法介紹

delimiter : 用于設(shè)置sql語句分割符，默認為分號。因為在MySQL中每一條SQL語句都必須以；進行結(jié)束，當我們換行的時候就會執(zhí)行這條SQL語句，但是我們此時的存儲過程并沒有結(jié)束，就會造成直接執(zhí)行沒有寫完的存儲過程造成報錯，所以此時需要聲明其他的結(jié)束符，不讓其使用默認的分隔符結(jié)束SQL語句。
sql語句 ：在這個部分編寫sql語句，編寫的語句需要以分號結(jié)尾，此時回車會直接執(zhí)行，所以要創(chuàng)建存儲過程前需要指定其他符號作為分割符，此時使用 // , 也可以使用其它字符。
// : 聲明結(jié)束符號，這個符號可以是任意的，是自定義的。相當于就是把分號替換為 //

創(chuàng)建存儲過程查詢學生信息

# 將SQL語句的結(jié)束符號分隔符替換為//
delimiter //
?
create procedure proc_stu()
begin
select * from students; # 此時這個分號并不會結(jié)束這個語句,存儲過程中的SQL語句還是用分隔符進行分隔
end // # 這里使用這個結(jié)束符號代表這個存儲過程創(chuàng)建完成
?
delimiter; # 創(chuàng)建完一個存儲過程之后將結(jié)束符號替換為分號，防止進行其他操作的時候有問題

1.3 調(diào)用存儲過程

語法格式：

call proc_stu(); # 調(diào)用的時候需要加上括號，因為可能存在參數(shù)

1.4 查看存儲過程

語法格式：

# 查詢 studnet 數(shù)據(jù)庫中的所有的存儲過程
select name from mysql.proc where db='studnet';
?
# 查詢存儲過程的狀態(tài)信息
show procedure status;

1.5 刪除存儲過程

語法格式：

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

2. 存儲過程中的語法結(jié)構(gòu)

存儲過程是可以編程的，意味著可以使用變量、表達式、控制語句來完成比較復雜的功能。

2.1 變量的聲明以及賦值

2.1.1 DECLARE 聲明變量

DECLARE : 通過 DECLARE 關(guān)鍵字可以定義一個局部變量，該變量的作用范圍只能在 BEGIN..,END 塊中。

語法格式：

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

注意：聲明變量的時候可以一次性聲明多個，使用逗號隔開。

示例：

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

2.1.2 SET 變量賦值

SET : 直接賦值使用SET關(guān)鍵字，可以賦常量或者是表達式，具體語法如下：

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

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

delimiter $?
create procedure proc_stu()
begin
    declare name varchar(20); ?# 可以指定變量類型以及變量的范圍
    set name = 'MySQL'; ?# 給變量直接賦值
    select name; # 輸出結(jié)果為：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條件判斷

語法結(jié)構(gòu)：

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

需求：

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

實現(xiàn)這個簡單的邏輯：

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;
 ? ?# 輸出結(jié)果
 ? ?select concat(height + '身高的身材為：' + figure);
end $
?
delimiter ;

2.3 傳遞參數(shù)

語法格式：

delimiter $
?
# 我們可以不指定 [in/out/inout] , 默認為 in,輸入?yún)?shù)  
create procedure pro_name([in/out/inout]參數(shù)名 參數(shù)類型)
begin 
    -- sql語句
end $
delimiter ;

# in : 該參數(shù)可以作為輸入，調(diào)用該存儲過程需要傳入的值，默認
# out : 該參數(shù)作為輸出，調(diào)用該存儲過程之后返回的值。
# inout : 既可以作為輸入?yún)?shù)也可以作為輸出參數(shù)。

2.3.1 IN - 輸入?yún)?shù)

需求：根據(jù)輸入的身高變量的值，判斷當前身高對應(yīng)的身材類型

實現(xiàn)：

delimiter $
# 此時調(diào)用者在調(diào)用這個存儲過程的時候必須傳遞身高的變量值
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;
 ? ?# 輸出結(jié)果
 ? ?select concat(height + '身高的身材為：' + figure);
end $
?
delimiter ;

調(diào)用：

# 調(diào)用該存儲過程。需要傳遞其中的身高值
call pro_name(175); ?# 輸出結(jié)果為：

2.3.2 out - 輸出參數(shù)

需求：根據(jù)輸入的身高，返回當前身高所處的身材類型

實現(xiàn)：

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 ;

調(diào)用：

# @標識符：在MySQL中代表的就是用戶定義的一個變量，這里我們使用這個變量來接收這個存儲過程的返回值
call pro_output(175 , @figure)；
?
# 查看存儲過程返回的結(jié)果
select @figure;

@標識符的作用

@figure ：這種在變量名前面加上”@“符號，叫做用戶會話變量，代表整個會話過程他都是有作用的，這個類似于全局變量一樣。當前會話就是代表的，比如我們在命令提示窗口中給好多帶有 @ 符號變量進行賦值，此時這些變量的值只作用于當前的會話，當我們把這個窗口關(guān)閉的時候，此時這些變量的值就會釋放掉。
@@global : 這種在變量名前加上 "@@" 符號，叫做系統(tǒng)變量。

2.4 case 結(jié)構(gòu)

語法格式：

# 方式一
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;

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

實現(xiàn)：

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;
    # 輸出結(jié)果
    select result;  
end $
?
delimiter ;

2.5 while循環(huán)

有條件的循環(huán)控制語句，當滿足條件的時候進入循環(huán)，不滿足條件的時候退出循環(huán)。

語法結(jié)構(gòu)：

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

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

實現(xiàn)：

delimiter $?
create procedure pro_sum(in num int(11))
begin 
    # 定義存儲總數(shù)的變量
    declare total int(255) default 0;
    # 定義存儲循環(huán)次數(shù)的數(shù)量
    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循環(huán)

有條件的循環(huán)控制語句，當不滿足條件的時候進入循環(huán)，滿足條件的時候跳出循環(huán)。他和while循環(huán)是反著的

語法結(jié)構(gòu)：

repeat 
    statement_list(SQL語句)
    until search_condition(查詢添加)
end repeat;

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

實現(xiàn)：

delimiter $?
create procedure pro_sum(in num int(11))
begin 
    # 定義存儲總數(shù)的變量
    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循環(huán)

loop實現(xiàn)簡單的循環(huán)，退出循環(huán)的條件需要使用其他的語句定義，通?？梢允褂胠eave語句實現(xiàn)，具體語法如下：

語法格式：

[begin_label:] loop
    statement_list
end loop [end_label]

如果不在statement_list中增加退出循環(huán)的語句，那么loop語句可以永安里實現(xiàn)簡單的死循環(huán)。

2.8 leave語句

用來從標注的流程構(gòu)造中退出，通常和 begin...end 或循環(huán)一起使用。下面是一個使用loop和leave的簡單例子，退出循環(huán)：

需求：計算從1加到n的值 ---> 使用loop...leave的形式進行退出循環(huán)

實現(xiàn)：

delimiter $
create procedure pro_sum(in num int(11))
begin 
    # 定義存儲總數(shù)的變量
    declare total int(255) default 0;
    
    c(該循環(huán)的別名):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 ;

到此這篇關(guān)于MySQL存儲過程及語法詳解的文章就介紹到這了,更多相關(guān)MySQL存儲語法內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: