一、存儲過程的介紹

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

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

的效率是有好處的。

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

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

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

二、存儲過程的基本語法

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

舉例：

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

三、變量

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

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

查看系統(tǒng)變量
  show [session/global] variables;             查看所有系統(tǒng)變量
  show [session/global] variables like '...';  可以通過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)閉了當前會話的自動提交，但是其他會話并未關(guān)閉

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

（2）用戶自定義變量

用戶自定義變量是用戶根據(jù)需要自己定義的變量，用戶變量不用提前聲明，在用的時候直接用"@變量名"即可，假如這個時候并未賦值，那么得到的值就是NULL，其作用域為當前連接。

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

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

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

（3）局部變量

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

內(nèi)的局部變量和輸入?yún)?shù)，局部變量的范圍是在其內(nèi)聲明的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）參數(shù)

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

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

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);//這里第二個返回的參數(shù)是用戶自定義的變量，記得要用@哦
select @ioio;

第二個例子是關(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語句

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

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

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)語句

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

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)語句

loop可以配合一下兩個語句實現(xiàn)簡單的退出循環(huán)
leave：退出當前的循環(huán)
iterate：結(jié)束本次循環(huán)，直接進行下一次的循環(huán)
語法：
  循環(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游標

游標是用來莻查詢結(jié)果集的數(shù)據(jù)類型，在存儲過程和函數(shù)中可以使用游標對結(jié)果集進行循環(huán)

的處理。游標的使用包括游標的聲明，open，fetch和close。也就是說游標可以歌劇

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

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

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

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

具體我們來舉個例子

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

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

最新評論