MySQL存儲過程基本語法是什么

本篇內容介紹了“MySQL存儲過程基本語法是什么”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！

一、存儲過程的介紹

存儲過程是事先經經過編譯并存儲在數據庫中的一段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存儲過程基本語法是什么”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！