一、存儲(chǔ)過(guò)程的介紹

存儲(chǔ)過(guò)程是事先經(jīng)經(jīng)過(guò)編譯并存儲(chǔ)在數(shù)據(jù)庫(kù)中的一段SQL語(yǔ)句的集合，調(diào)用存儲(chǔ)

過(guò)程可以簡(jiǎn)化應(yīng)用開(kāi)發(fā)人員的很多工作，減少數(shù)據(jù)在數(shù)據(jù)庫(kù)和應(yīng)用服務(wù)器之間的傳輸，對(duì)于高效數(shù)據(jù)處理

的效率是有好處的。

存儲(chǔ)過(guò)程思想上很簡(jiǎn)單，就是數(shù)據(jù)庫(kù)SQL語(yǔ)言層面的代碼封裝與重用，你可以將

它和C語(yǔ)言中的函數(shù)類比，注意是類比而不是相同。

特點(diǎn)：封裝，復(fù)用，可以接受參數(shù)，也可以返回?cái)?shù)據(jù)，減少網(wǎng)絡(luò)交互，效率提升

二、存儲(chǔ)過(guò)程的基本語(yǔ)法

 創(chuàng)建存儲(chǔ)過(guò)程：
  create procedure 存儲(chǔ)過(guò)程名稱(參數(shù)列表)
  begin
    -SQL語(yǔ)句
  end;
  調(diào)用存儲(chǔ)過(guò)程：
  call 存儲(chǔ)過(guò)程名稱（參數(shù)）
  查看在哪個(gè)數(shù)據(jù)庫(kù)下的存儲(chǔ)過(guò)程的語(yǔ)句：
  select *from information_schema.routines where routine_schema='數(shù)據(jù)庫(kù)名';
  查看某個(gè)存儲(chǔ)過(guò)程的定義，也就是創(chuàng)建存儲(chǔ)過(guò)程的語(yǔ)句
  show create procedure 存儲(chǔ)過(guò)程名稱; 
  刪除存儲(chǔ)過(guò)程：
  drop procedure if exists 存儲(chǔ)過(guò)程名稱:

舉例：

#使用class_first數(shù)據(jù)庫(kù)
use class_first;
# 開(kāi)始創(chuàng)建存儲(chǔ)過(guò)程
create procedure p1()
begin
    select *from s;
end;
create procedure p2()
begin
    select *from p;
end;
# 調(diào)用其中一個(gè)存儲(chǔ)過(guò)程p1
call p1();
# 查看當(dāng)前數(shù)據(jù)庫(kù)存在的存儲(chǔ)過(guò)程
select *from information_schema.ROUTINES where routine_schema='class_first';
# 查看某一個(gè)創(chuàng)建某一個(gè)存儲(chǔ)過(guò)程的語(yǔ)句，假如查看的是存儲(chǔ)過(guò)程p1
show create procedure p1;

三、變量

（1）系統(tǒng)變量

系統(tǒng)變量是MySQL服務(wù)器提供，不是用戶自定義的，屬于服務(wù)器層面，分為全局變量(global)和會(huì)話變量(session)，會(huì)話變量指的是在當(dāng)前控制臺(tái)的變量，假如修改了話變量，但是重新打開(kāi)了另外一個(gè)控制臺(tái)，查看時(shí)會(huì)發(fā)現(xiàn)并未修改。

查看系統(tǒng)變量
  show [session/global] variables;             查看所有系統(tǒng)變量
  show [session/global] variables like '...';  可以通過(guò)like模糊匹配方式查找變量
  select @@[session/global].系統(tǒng)變量名         查看指定變量的值
設(shè)置系統(tǒng)變量
  set [session/global] 系統(tǒng)變量名=值;
  set @@[session/global]系統(tǒng)變量=值;

show session variables;
show session variables like 'auto%';
set session autocommit=0;
關(guān)閉了當(dāng)前會(huì)話的自動(dòng)提交，但是其他會(huì)話并未關(guān)閉

全局變量的修改在MySQL服務(wù)器重新啟動(dòng)后還是會(huì)回到初始值，想要永久修改的話，要修改MySQL的部分配置文件。

（2）用戶自定義變量

用戶自定義變量是用戶根據(jù)需要自己定義的變量，用戶變量不用提前聲明，在用的時(shí)候直接用"@變量名"即可，假如這個(gè)時(shí)候并未賦值，那么得到的值就是NULL，其作用域?yàn)楫?dāng)前連接。

賦值
   set @變量名=值;
   set @變量名:=值;
   select @變量名:=值；
   從表格查詢將查詢的數(shù)據(jù)賦值給變量
   select 字段名 into @變量名 from 表名; 
  使用變量
   select @變量名;

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

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

（3）局部變量

局部變量是根據(jù)需要定義的在局部生效的變量，訪問(wèn)之前，需要declare聲明，可以作存儲(chǔ)過(guò)程

內(nèi)的局部變量和輸入?yún)?shù)，局部變量的范圍是在其內(nèi)聲明的begin...end塊。

聲明：
  declare 變量名 變量類型 （如果有默認(rèn)值則 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();

四、存儲(chǔ)過(guò)程的語(yǔ)法詳解

（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）參數(shù)

參數(shù)：
   in     該類參數(shù)作為輸入，也就是需要調(diào)用時(shí)傳入值（什么也沒(méi)有是默認(rèn)是in參數(shù)）
   out    該類參數(shù)作為輸出，也就是該參數(shù)可以作為返回值
   inout  既可以作為輸入?yún)?shù)，也可以作為輸出參數(shù)
用法：
  create procedure 存儲(chǔ)過(guò)程名稱([in/out/inout]參數(shù)名 參數(shù)類型)
 begin
    SQL語(yǔ)句
 end;

舉個(gè)例子，輸入成績(jī)，得到成績(jī)的等級(jí)

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

第二個(gè)例子是關(guān)于inout的使用

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

（3）條件判斷case語(yǔ)句

case
  when 條件表達(dá)式1 then
    ...
  when 條件表達(dá)式2 then
   ...
  ...
  else
   ...
end case;

需求：一月到三月是第一季度，每三個(gè)月是一個(gè)季度，現(xiàn)在輸入一個(gè)月份，判斷是第幾季度。

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:='你輸入的是非法參數(shù)';
        end case;
end;
call p1(-1,@res);
select  @res;

（4）while循環(huán)語(yǔ)句

如果條件是true就繼續(xù)下去循環(huán)知道為false
while 條件 do
 SQL語(yǔ)句
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循環(huán)語(yǔ)句

repeat和while循環(huán)不一樣，while循環(huán)滿足條件繼續(xù)循環(huán)，而repeat循環(huán)滿足條件則跳出循環(huán)。

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循環(huán)語(yǔ)句

loop可以配合一下兩個(gè)語(yǔ)句實(shí)現(xiàn)簡(jiǎn)單的退出循環(huán)
leave：退出當(dāng)前的循環(huán)
iterate：結(jié)束本次循環(huán)，直接進(jìn)行下一次的循環(huán)
語(yǔ)法：
  循環(huán)名稱:loop
     循環(huán)體
   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之間偶數(shù)的和

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游標(biāo)

游標(biāo)是用來(lái)莻查詢結(jié)果集的數(shù)據(jù)類型，在存儲(chǔ)過(guò)程和函數(shù)中可以使用游標(biāo)對(duì)結(jié)果集進(jìn)行循環(huán)

的處理。游標(biāo)的使用包括游標(biāo)的聲明，open，fetch和close。也就是說(shuō)游標(biāo)可以歌劇

自己想要的條件得到一個(gè)篩選過(guò)的結(jié)果集。其用法分別如下：

1：聲明游標(biāo)
 declare 游標(biāo)名稱 cursor for 查詢語(yǔ)句;
2：打開(kāi)游標(biāo)
 open 游標(biāo)名稱
3：獲取游標(biāo)記錄
 fetch 游標(biāo)名稱 into 變量,[變量];
4:關(guān)閉游標(biāo)
 close 游標(biāo)名

再具體舉例之前還得說(shuō)一下條件處理處理程序，為什么要說(shuō)呢？在獲取游標(biāo)記錄時(shí)我們使用循環(huán)來(lái)獲取，直到游標(biāo)中的數(shù)據(jù)獲取完了，但要怎么判斷獲取結(jié)束，這時(shí)候就需要條件處理程序了。

條件處理程序可以用來(lái)定義在流程控制結(jié)構(gòu)執(zhí)行過(guò)程中遇到問(wèn)題時(shí)相對(duì)應(yīng)的處理步驟。
語(yǔ)法：
  declare  行為   handler for 狀態(tài)碼 +sql邏輯語(yǔ)句
行為：
  continue 繼續(xù)執(zhí)行當(dāng)前程序
  exit 終止執(zhí)行當(dāng)前程序
狀態(tài)碼
  如02000之類
  sqlwarning sql警告，所有以01開(kāi)頭的代碼簡(jiǎn)寫
  not found  未找到數(shù)據(jù)，所以以02開(kāi)頭
  sqlexception 沒(méi)有被sqlwarning和not found捕獲的代碼簡(jiǎn)寫

具體我們來(lái)舉個(gè)例子

這里我創(chuàng)建了一張表，現(xiàn)在我要將年齡小于自定義輸入的值再重新放入一個(gè)表格中（如年齡小于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);

同時(shí)數(shù)據(jù)庫(kù)中也出現(xiàn)了stu表

到此這篇關(guān)于MySQL由淺入深探究存儲(chǔ)過(guò)程的文章就介紹到這了,更多相關(guān)MySQL存儲(chǔ)過(guò)程內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論