存儲過程
什麼是存儲過程??
簡單來說,存儲過程就是為了以後使用而儲存的一條或多條SQL語句的集合 .
和我們程式設計語言中的函數這個概念比較像。
在程式設計語言中,我們經常要完成某個功能,我們都會将它寫成一個函數,然後在需要這個函數的時候調用它就好了。
同樣的道理,存儲過程也是這樣,我們首先先聲明這個存儲過程,在我們需要這個存儲過程的時候,我們調用這個存儲過程就行了。
例如:在C語言中,我們想求兩個數值a/b中的最大值。則我們可以寫如下這樣一個函數:
int myMax(int a,int b){
return a>b?a:b;
}
在main函數中相應的位置調用即可。
int main(void){
int a,b;
scanf("%d%d",&a,&b);
int maxValue=myMax(a,b);//調用
printf("max value %d",maxValue);
}
存儲過程的定義和調用上面函數的定義是類似的。
存儲過程的定義中,也可以有輸入參數,也可以有傳回值。下面我們介紹下存儲過程的定義和調用。
還是以例子來進行說明:
在student表中的最後一列為學生成績score,現在我們想為成績寫一個存儲過程,以供以後一直使用。
在指令行應該這樣來建立和調用如下:
delimiter // 改變MySQL語句的結束分隔符為//
create procedure p_scoreAvg()
begin
select avg(score) as avg_score from student;
end //
delimiter ; 恢複MySQL語句的結束分隔符為分号
注:
1、這裡最要注意的是開始和結束位置的delimiter //和delimiter ;這兩句,delimiter是分隔符的意思,由于MySQL預設的是以”;”z作為分隔符,而存儲過程中會出現分号作為語句的一部分,如果我們之前不改變分隔符的号,MySQL還是會以分号作為語句分隔符結束。分隔符除了,其它的符号都可以作為分隔符,例如 $$
2、存儲過程可以有輸入參數,輸出參數,也可以都沒有,但是存儲過程名後面的一對圓括号是不能少的。這一點和程式設計語言中的函數一緻。
3、存儲過程的過程體開始與結束使用begin和end進行辨別。
上面就定義了一個無輸入參數,也沒有傳回值的存儲過程,調用此存儲過程的方法如下:
call p_scoreAvg();
下面分别舉一個既有輸入也有輸出的例子。
在舉例子之前,我student2表中,插入了一些資料,這次是借助于SQLyog工具來做的,确實友善多了。
現在我們想根據學生的學号來得到學生的各科成績以平均成績。存儲過程如下:
有了如上的存儲過程之後,調用方法如下:
call p_student1(1,@avgScore);
select @avgScore;//顯示出變量avgScore的值
在SQLyog中實踐如下:
下面将講解下存儲過程的參數:
共有三種參數,形如:
create procedure ([IN|OUT|INOUT] 參數名 資料類型)
1、IN 輸入參數
2、OUT 輸出參數,可以被傳回
3、INOUT 輸入輸出參數
MySQL存儲過程的調用
用call和你過程名以及一個括号,括号裡面根據需要,加入參數,參數包括輸入參數、輸出參數、輸入輸出參數。具體如下:
call procedure_name();//如果有參數,傳入參數即可。
call procedure_name(@p1,@p2);//參數都是以@開頭
變量
變量定義
DECLARE variable_name [,variable_name …] datatype [DEFAULT value];
其中datatype為MySQL的資料類型,如INT 、DECIMAL VARCHAR(length)等;
變量指派
set varible_name=expression;
使用者變量
1、在MySQL用戶端使用使用者變量
主要有select 和set為變量指派:
例如:當有如下的存儲過程:
當調用時,可以給輸入參數作為一個變量,如下:
SET @id=1;//變量
CALL p_student1(@id,@avgScore);
SELECT @avgScore;
注意:使用者變量名一般以@開頭
存儲過程中條件語句的應用
前面一直強調MySQL中的存儲過程和其它程式設計語言中的函數類似,是以像IF-ELSE CASE WHILE 等都可以應用于存儲過程中。
下面将分别舉例來進行講解。
IF ELSE的應用
CASE 的應用
上面就是關于case的用法,文法目前還不怎麼熟悉,寫起來怪怪的。
while的應用
調用後的結果如下:
上面隻是簡單的介紹了下IF-ELSE 、WHILE 、case的用法。存儲過程還支援許多其他的文法。如:repeat等
删除存儲過程
在MySQL中删除都是用DROP 來做。
是以,删除存儲過程的指令如下:
drop procedure pro_name;
使用存儲過程的優點
和程式設計語言中使用的函數一樣優點類似,子產品化、重用等。