了解存儲過程之前,先了解一下mysql的控制結構。
類似C語言(if……else、while循環等)SQL也有自己的控制結構。
if……else控制結構:
例如:
(1)
<span style="font-family:FangSong_GB2312;">if 判斷表達式 then 執行語句; end if; 與c語言進行比較 if(判斷表達式) 執行語句;</span>
(2)
<span style="font-family:FangSong_GB2312;">if 判斷表達式1 then 執行語句1; else then 執行語句2; end if; 與c語言進行比較 if(判斷表達式1) 執行語句1; else 執行語句2;</span>
(3)
<span style="font-family:FangSong_GB2312;">if 判斷表達式1 then 執行語句1; elseif 判斷表達式2 then 執行語句2; …… elseif 判斷表達式N then 執行語句N; else 執行語句N+1; end if; 與c語言進行比較 if(判斷表達式1) 執行語句1; else if(判斷表達式2) 執行語句2; …… else if(判斷表達式N) 執行語句N; else 執行語句N+1;</span>
需要注意所有的執行語句和end if都要以‘;’結束,而且判斷表達式之後接then,還有一點與C語言不同的是elseif之間沒有空格。
mysql中還有一些與if相關的函數
if(判斷表達式,值1,值2) 如果表達式為“true”返回“值1”,表達式為“false”返回“值2”。類似於C語言中的三目運算符。
ifnull(表達式1,表達式2)如果表達式1不為空,則返回表達式1。如果表達式1為空,則返回表達式2
nullif(表達式1,表達式2)如果表達式1=表達式2,返回null ,否則返回表達式1。
case when控制結構:
有兩種形式
(1)
<span style="font-family:FangSong_GB2312;">case 待判斷值 when 值1 then 輸出1 when 值2 then 輸出2 …… when 值N then 輸出N else 默認輸出 end; #如果輸出時語句的話,最後的結尾要改成end case。輸出的是值則是end 同C語言的switch相比較 switch(待判斷值){ case 值1:輸出1 break; case 值2:輸出2 break; …… case 值N:輸出N break; default:默認輸出 }</span>
(2)
<span style="font-family:FangSong_GB2312;">case when 判斷表達式1 then 輸出1 when 判斷表達式2 then 輸出2 …… when 判斷表達式N then 輸出N else 默認輸出 end case; #如果輸出時語句的話,最後的結尾要是end case。輸出的是值則是end。</span>
while循環結構:
<span style="font-family:FangSong_GB2312;">while 判斷表達式 do 循環體 end while; C語言中的while循環 while(判斷表達式){ 循環體; }</span>
loop循環結構:無條件循環
<span style="font-family:FangSong_GB2312;">標簽:loop 循環體; end loop; 可以通過"leave 標簽"來跳出loop循環。</span>
repeat循環結構:
<span style="font-family:FangSong_GB2312;">repeat 循環體; until 判斷表達式 end repeat;</span>
現在開始介紹存儲過程,其實存儲過程跟函數很像
查看當前存儲過程的狀態:show procedure status;
創建存儲過程:
<span style="font-family:FangSong_GB2312;">create procedure 名稱(參數列表) begin 語句集 end;</span>
參數列表總是存在的,如果沒有參數則應該是空參數列表(),參數必須指定數據類型而且每個參數默認都是一個in參數。要指定為其他參數,可以在參數前面加上out或inout關鍵字。默認的in類似於按值傳遞,在存儲過程中對參數進行修改,調用者是看不到的。out參數只是用來從存儲過程傳回數據的,無論給參數傳入什麼值,這個參數的初始值始終是null。對於inout參數,調用者不僅可以設置參數的初始值,而且在過程中修改參數,調用者是看得到的類似與按地址傳遞。
刪除存儲過程:drop procedure 名稱;
查看存儲過程:show create procedure 名稱\G 類似於show create table 表名 \G的作用是橫向顯示
調用存儲過程:call 名稱(參數);
聲明變量:
(1)declare變量名 變量類型 默認值; 聲明變量必須在開頭定義,如果沒有默認值,初始值為null。作用范圍是在begin……end內
(2)set @變量名=初始值;定義的變量是用戶變量,在存儲過程之外的sql也是可以調用的
變量賦值:set 變量名=變量值 切忌直接給變量賦值(變量名= 變量值)
還有一種給一個或多個變量賦值的方法:利用“select 指定列 into 指定變量”,所以select的結果必須是單行。
示例:
所有示例,都實現將分界符設置為'$'
delimiter $
1、測試if-else控制結構
2、測試case……when
第一種情況:
輸出是值,結尾用end。一般用於select
輸出是語句,結尾用end case。一般用於存儲過程
第二種情況:
輸出是語句,結尾用end case。一般用於存儲過程
輸出是值,結尾用end。一般用於select
3、測試while循環
4、測試loop
5、測試repeat
6、帶參數的存儲過程
默認為in的參數:按值傳遞
初始值為0的變量tmp作為參數傳入存儲過程後,雖然在存儲過程內對其進行修改,但調用者再次查看tmp時,值仍然為0,沒有變化
out參數:
由第一個select可以看出,out參數不允許將實參的值傳入存儲過程。通過第二個和第三個select可以看出,存儲過程內部修改變量後可以返回給調用者。
與按地址傳遞還有所不同,out只允許返回值,不允許傳入值。
inout參數:按地址傳遞,形參值改變會改變實參的值
第一個select結果為0,說明實參的值傳進存儲過程。第二個和第三個select結果表明,inout可以在存儲過程內部修改形參的值,從而影響實參,類似於按地址傳遞