MySQL由淺入深探究儲存過程

2022-12-01 14:02:05

一、儲存過程的介紹

儲存過程是事先經經過編譯並儲存在資料庫中的一段SQL語句的集合，呼叫儲存

過程可以簡化應用開發人員的很多工作，減少資料在資料庫和應用伺服器之間的傳輸，對於高效資料處理

的效率是有好處的。

儲存過程思想上很簡單，就是資料庫SQL語言層面的程式碼封裝與重用，你可以將

它和C語言中的函數類比，注意是類比而不是相同。

特點：封裝，複用，可以接受引數，也可以返回資料，減少網路互動，效率提升

二、儲存過程的基本語法

建立儲存過程：
create procedure 儲存過程名稱(參數列)
begin
-SQL語句
end;
呼叫儲存過程：
call 儲存過程名稱（引數）
檢視在哪個資料庫下的儲存過程的語句：
select *from information_schema.routines where routine_schema='資料庫名';
檢視某個儲存過程的定義，也就是建立儲存過程的語句
show create procedure 儲存過程名稱;
刪除儲存過程：
drop procedure if exists 儲存過程名稱:

舉例：

#使用class_first資料庫
use class_first;
# 開始建立儲存過程
create procedure p1()
begin
    select *from s;
end;
create procedure p2()
begin
    select *from p;
end;
# 呼叫其中一個儲存過程p1
call p1();
# 檢視當前資料庫存在的儲存過程
select *from information_schema.ROUTINES where routine_schema='class_first';
# 檢視某一個建立某一個儲存過程的語句，假如檢視的是儲存過程p1
show create procedure p1;

三、變數

（1）系統變數

系統變數是MySQL伺服器提供，不是使用者自定義的，屬於伺服器層面，分為全域性變數(global)和對談變數(session)，對談變數指的是在當前控制檯的變數，假如修改了話變數，但是重新開啟了另外一個控制檯，檢視時會發現並未修改。

檢視系統變數
show [session/global] variables; 檢視所有系統變數
show [session/global] variables like '...'; 可以通過like模糊匹配方式查詢變數
select @@[session/global].系統變數名檢視指定變數的值
設定系統變數
set [session/global] 系統變數名=值;
set @@[session/global]系統變數=值;

show session variables;
show session variables like 'auto%';
set session autocommit=0;
關閉了當前對談的自動提交，但是其他對談並未關閉

全域性變數的修改在MySQL伺服器重新啟動後還是會回到初始值，想要永久修改的話，要修改MySQL的部分組態檔。

（2）使用者自定義變數

使用者自定義變數是使用者根據需要自己定義的變數，使用者變數不用提前宣告，在用的時候直接用"@變數名"即可，假如這個時候並未賦值，那麼得到的值就是NULL，其作用域為當前連線。

賦值
set @變數名=值;
set @變數名:=值;
select @變數名:=值；
從表格查詢將查詢的資料賦值給變數
select 欄位名 into @變數名 from 表名;
使用變數
select @變數名;

select @s;#並未給s賦值，得到的是NULL

set @ss:=2;
select @io:='opop';
select @ss,@io;

（3）區域性變數

區域性變數是根據需要定義的在區域性生效的變數，存取之前，需要declare宣告，可以作儲存過程

內的區域性變數和輸入引數，區域性變數的範圍是在其內宣告的begin...end塊。

宣告：
declare 變數名變數型別（如果有預設值則 default...）
變數型別：int,bigint,char,varchar,dae,time
賦值
set 變數名=值
set 變數名:=值
select 欄位名 into 變數名 from 表名...；

create procedure p3()
begin
    declare st int default 1;
    declare sss int;
    select  count(*) into sss from s;
    select sss;
end;
call p3();

四、儲存過程的語法詳解

（1）if判斷

1:if判斷
if 條件 then
...
end if
2:if...elseif判斷
if 條件 then
...
elseif 條件2 then
...
end if
3:if...else判斷
if 條件 then
...
else
...
end if

（2）引數

引數：
in 該類引數作為輸入，也就是需要呼叫時傳入值（什麼也沒有是預設是in引數）
out 該類引數作為輸出，也就是該引數可以作為返回值
inout 既可以作為輸入引數，也可以作為輸出引數
用法：
create procedure 儲存過程名稱([in/out/inout]引數名引數型別)
begin
SQL語句
end;

舉個例子，輸入成績，得到成績的等級

create procedure p1(in score int,out result varchar(10))
begin
     if score>=80&&score<=100 then
         set result:='優秀';
    elseif score>=60&&score<=100 then
         set result:='及格';
    elseif score>=0&&score<=100 then
         set result:='不及格';
    else
         set result:='輸入的引數是非法引數';
     end if;
end;
call p1(819,@ioio);//這裡第二個返回的引數是使用者自定義的變數，記得要用@哦
select @ioio;

第二個例子是關於inout的使用

create procedure p1(inout result int)
begin
     set result:=result*0.5;
end;
set @9:=100;
call p1(@9);
select @9;

（3）條件判斷case語句

case
when 條件表示式1 then
...
when 條件表示式2 then
...
...
else
...
end case;

需求：一月到三月是第一季度，每三個月是一個季度，現在輸入一個月份，判斷是第幾季度。

create procedure p1(in res int,out ul varchar(10))
begin
     case
         when res>=1&&res<=3 then
           set ul:='第一季度';
        when res>=4&&res<=6 then
           set ul:='第二季度';
        when res>=7&&res<=9 then
           set ul:='第三季度';
        when res>=10&&res<=12 then
           set ul:='第四季度';
        else
           set ul:='你輸入的是非法引數';
        end case;
end;
call p1(-1,@res);
select  @res;

（4）while迴圈語句

如果條件是true就繼續下去迴圈知道為false
while 條件 do
SQL語句
end while;

需求：求1到n的和：

create procedure p1(in n int)
begin
    declare sum int default 0;
    declare i int default 1;
    while i<=n do
        set sum:=sum+i;
        set i:=i+1;
        end while;
    select sum;
end;
call p1(100);

（5）repeat迴圈語句

repeat和while迴圈不一樣，while迴圈滿足條件繼續迴圈，而repeat迴圈滿足條件則跳出迴圈。

repeat
SQL邏輯
until 條件
end repeat：

如：求1到n的和

create procedure p1(in n int)
begin
    declare sum int default 0;
    declare i int default 1;
    repeat
        set sum:=sum+i;
        set i=i+1;
    until i>n
        end repeat;
    select sum;
end;
call p1(10);

（6）loop迴圈語句

loop可以配合一下兩個語句實現簡單的退出迴圈
leave：退出當前的迴圈
iterate：結束本次迴圈，直接進行下一次的迴圈
語法：
迴圈名稱:loop
迴圈體
end loop;

求1到n之間的和（使用loop）

create procedure p1(in n int)
begin
    declare sum int default 0;
    declare i int default 1;
    su:loop
        if i>n then
            leave su;
        end if;
        set sum:=sum+i;
        set i:=i+1;
    end loop;
    select sum;
end;
call p1(100);

求1到n之間偶數的和

create procedure p2(in n int)
begin
     declare sum int default 0;
     declare i int default 0;
     su:loop
         set i:=i+1;
        if i%2=1 then
          iterate su;
        end if;
        if i>n then
             leave su;
         end if;
        set sum:=sum+i;
     end loop;
     select sum;
end;
call p2(10);

（7）cursor遊標

遊標是用來莻查詢結果集的資料型別，在儲存過程和函數中可以使用遊標對結果集進行迴圈

的處理。遊標的使用包括遊標的宣告，open，fetch和close。也就是說遊標可以歌劇

自己想要的條件得到一個篩選過的結果集。其用法分別如下：

1：宣告遊標
declare 遊標名稱 cursor for 查詢語句;
2：開啟遊標
open 遊標名稱
3：獲取遊標記錄
fetch 遊標名稱 into 變數,[變數];
4:關閉遊標
close 遊標名

再具體舉例之前還得說一下條件處理處理程式，為什麼要說呢？在獲取遊標記錄時我們使用迴圈來獲取，直到遊標中的資料獲取完了，但要怎麼判斷獲取結束，這時候就需要條件處理程式了。

條件處理程式可以用來定義在流程控制結構執行過程中遇到問題時相對應的處理步驟。
語法：
declare 行為 handler for 狀態碼 +sql邏輯語句
行為：
continue 繼續執行當前程式
exit 終止執行當前程式
狀態碼
如02000之類
sqlwarning sql警告，所有以01開頭的程式碼簡寫
not found 未找到資料，所以以02開頭
sqlexception 沒有被sqlwarning和not found捕獲的程式碼簡寫

具體我們來舉個例子

這裡我建立了一張表，現在我要將年齡小於自定義輸入的值再重新放入一個表格中（如年齡小於20歲）：

create table sp(
    age int,
    name varchar(10)
);
insert into sp values (18,'李四'),
(20,'張三'),
(12,'王二麻子'),
(80,'趙雲'),
(26,'查類'),
(40,'謝遜'),
(63,'李白'),
(52,'杜甫'),
(19,'韓信');

create procedure p1(in uage int)
begin
    declare usname varchar(10);
    declare u_age int;
    declare u_cursor cursor for select name,age from sp where age<uage;
    declare exit handler for not found close u_cursor;
    drop table if exists stu;
    create table stu(
        u_name varchar(10),
        u_age int
    );
    open u_cursor;
    while true do
        fetch u_cursor into usname,u_age;
        insert into stu(u_name, u_age) values(usname,u_age);
        end while;
    close u_cursor;
end;
call p1(20);

同時資料庫中也出現了stu表

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