之前在項(xiàng)目上遇到一個(gè)問題袜茧，實(shí)施人員在數(shù)據(jù)庫中建了許多臨時(shí)的測試數(shù)據(jù)页眯，在正式客戶環(huán)境中是要?jiǎng)h掉的，但是產(chǎn)品頁面上沒有刪除選項(xiàng)匙瘪，只能手動(dòng)在數(shù)據(jù)庫中刪除铆铆。不僅數(shù)據(jù)多，而且表之間關(guān)系復(fù)雜丹喻，一條一條刪除估計(jì)客戶的黃花菜都涼了薄货。所以就有了這篇文章，記錄存儲(chǔ)過程的一些語法碍论，也便于大家參考谅猾。

什么是存儲(chǔ)過程？

存儲(chǔ)過程（Stored Procedure）是在大型數(shù)據(jù)庫系統(tǒng)中鳍悠，一組為了完成特定功能的SQL 語句集税娜，存儲(chǔ)在數(shù)據(jù)庫中，經(jīng)過第一次編譯后再次調(diào)用不需要再次編譯藏研，用戶通過指定存儲(chǔ)過程的名字并給出參數(shù)（如果該存儲(chǔ)過程帶有參數(shù)）來執(zhí)行它敬矩。存儲(chǔ)過程是數(shù)據(jù)庫中的一個(gè)重要對象。

說的直白一點(diǎn)就是蠢挡，存儲(chǔ)過程是一組特定功能sql的集合弧岳，第一次編譯后無需再編譯凳忙，后續(xù)需要時(shí)直接調(diào)用即可。

所以這個(gè)時(shí)候存儲(chǔ)過程就派上了用場缩筛。

在linux怎么創(chuàng)建存儲(chǔ)過程

先來看一個(gè)最簡單的存儲(chǔ)過程示例

create procedure demo()
begin
    select id, name from user;
    update user set age = 20 where name = 'zhangsan';
end

這個(gè)存儲(chǔ)過程的名稱就是demo消略，功能就是查出user表中所有的id和name，并把張三的年齡改為20瞎抛，存儲(chǔ)過程中的sql集合就寫在begin...end之間

第一次在linux命令行中執(zhí)行上述操作艺演，可能都會(huì)報(bào)如下的錯(cuò)

告訴你第三行有語法錯(cuò)誤，那是因?yàn)閙ysql認(rèn)為你的語句到第一個(gè) ; 就結(jié)束了桐臊，所以存儲(chǔ)過程創(chuàng)建失敗胎撤。對上述語句做如下改動(dòng)

delimiter //
create procedure demo()
begin
    select id, name from user;
    update user set age = 20 where name = 'zhangsan';
end
//

第一行的作用就是把mysql默認(rèn)分隔符改為//（當(dāng)然你可以改為其他符號(hào)比如@，$等断凶，但是不要改成 * 等中間語句會(huì)用到的）伤提，讀到 // 時(shí)mysql才會(huì)認(rèn)為一個(gè)完整語句結(jié)束

最后要記得創(chuàng)建完存儲(chǔ)過程后，要執(zhí)行 delimiter ;

存儲(chǔ)過程常用語法

代碼清單1

delimiter //
create procedure demo1(
    in input int,
    out output int,
    inout param int
)
begin
    declare num int default 0;
    set output = 0;
    select age into @myage from user where id = input;

    if @myage = 20 then
        set output = 200;
        set num = 2;
    elseif @myage = 21 then
        set output = 300;
        set num = 3;
    else
        set output = 400;
        set num = 4;
    end if;
    
    while param < num do
        set param = param + 10;
    end while;
end//
delimiter ;

# 給inout類型變量賦初值
set @param = 1;
# 調(diào)用存儲(chǔ)過程
call demo1(3, @myout, @param);

# 查看變量值和結(jié)果
select @myage;
select @myout;
select @param;

• 參數(shù)

  存儲(chǔ)過程的參數(shù)有3種：in认烁，out肿男，inout
  • in 是入?yún)?biāo)識(shí)，input是變量名稱却嗡，int代表類型
    輸入?yún)?shù)傳對應(yīng)類型的值即可（當(dāng)然也可以傳遞一個(gè)賦值后的變量）
  • out 代表輸出參數(shù)其弊，你可以理解為方法的返回值
    輸出參數(shù)需要傳變量几蜻，且要加上@
  • inout 代表輸入輸出變量，即你把一個(gè)變量輸入，處理后再輸出
    inout類型和out一樣愉耙，也是傳變量钞支，但是需要先對變量賦值

三種參數(shù)調(diào)用方式

• 變量聲明和賦值

  聲明

  • declare
    用declare聲明變量督弓，需要指明變量類型芦圾，后續(xù)可以直接使用變量名，詳見代碼清單1
  • @
    變量名前面加@表示聲明為變量帝牡，而后續(xù)使用該變量也需要加上@符號(hào)往毡，詳見代碼清單1

  賦值

  set @myage = 20;
  # 直接set一個(gè)值
  
  select age into @myage from user where ...;
  # select ... into @...  給變量賦值
  
  set @myage = (select age from user where ...);
  # set @... = (select ...) 給變量賦值
  

  如果select多個(gè)字段，給多個(gè)變量賦值靶溜，就必須用into

  select id, age into @myid, @myage from user where ...;
  

• 循環(huán)控制

  • while

  while 條件 do            # 滿足條件進(jìn)入循環(huán)
      do something;
  end while;
  

  • loop

  my_loop: LOOP                      # 定義循環(huán) (my_loop是自定義循環(huán)名稱)
      do something;                  # 循環(huán)內(nèi)執(zhí)行操作
      if 條件 then                    # 跳出循環(huán)判斷
          leave my_loop;             # 跳出循環(huán)
      end if;
  end LOOP;                          # 循環(huán)結(jié)束
  

  • repeat （注意：until跳出repeat 語句后面不能加分號(hào)）

  repeat
      do something;
      until 條件                  #滿足條件結(jié)束repeat
  end repeat;
  

• 條件判斷

  if ... then
      do something;
  elseif
      do something;
  end if;
  

• 游標(biāo)操作

  游標(biāo)（Cursor）是處理數(shù)據(jù)的一種方法开瞭，為了查看或者處理結(jié)果集中的數(shù)據(jù)，游標(biāo)提供了在結(jié)果集中一次一行或者多行前進(jìn)或向后瀏覽數(shù)據(jù)的能力墨技〕徒祝可以把游標(biāo)當(dāng)作一個(gè)指針，它可以指定結(jié)果中的任何位置扣汪，然后允許用戶對指定位置的數(shù)據(jù)進(jìn)行處理

  在存儲(chǔ)過程中断楷，是不能夠?qū)⒍嘟Y(jié)果集賦值給一個(gè)變量的，所以這個(gè)時(shí)候就需要用到游標(biāo)了

  # 聲明接收游標(biāo)遍歷結(jié)果的變量
  declare msg varchar(20);
  
  # 聲明控制遍歷游標(biāo)的變量
  declare done int default false;
  
  # 聲明游標(biāo)崭别，for后面的select語句查詢結(jié)果為多結(jié)果
  declare cur cursor for select content from user_msg where ...;
  
  # 設(shè)定游標(biāo)結(jié)束標(biāo)志冬筒，含義是游標(biāo)在結(jié)果集取不到數(shù)據(jù)時(shí)
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  
  # 打開游標(biāo)
  open cur;
  
  # 游標(biāo)遍歷結(jié)果
  my_loop: loop
      fetch next from cur into msg;      # 從游標(biāo)拿結(jié)果到msg變量
      if done then                       # 游標(biāo)結(jié)果遍歷完時(shí)恐锣，跳出循環(huán)
            leave my_loop;
      end if;
      do something;                      # 一些要執(zhí)行的sql語句
  end loop;
  
  # 關(guān)閉游標(biāo)
  close cur;
  
  # 可以聲明多個(gè)游標(biāo)，聲明方式一樣
  # 兩個(gè)游標(biāo)情況下舞痰，注意在遍歷第二個(gè)之前把done標(biāo)志設(shè)為false
  set done = false;