細談Mysql的存儲過程和存儲函數(shù)

1 存儲過程

1.1 什么是存儲過程

存儲過程是一組為了完成某項特定功能的sql語句集，其實質(zhì)上就是一段存儲在數(shù)據(jù)庫中的代碼，他可以由聲明式的sql語句（如CREATE,UPDATE,SELECT等語句）和過程式sql語句（如IF...THEN...ELSE控制結(jié)構(gòu)語句）組成。存儲過程思想上很簡單，就是數(shù)據(jù)庫 SQL 語言層面的代碼封裝與重用。

1.2 存儲過程的優(yōu)缺點

優(yōu)點:

1.可增強sql語言的功能和靈活性
存儲過程可以用流程控制語言編寫，有很強的靈活性，可以完成復(fù)雜的判斷和較復(fù)雜的運算。

2.良好的封裝性
存儲過程被創(chuàng)建后，可以在程序中被多次調(diào)用，而不必擔(dān)心重寫編寫該存儲過程的sql語句。

3.高性能
存儲過程執(zhí)行一次后，其執(zhí)行規(guī)劃就駐留在高速緩沖存儲器中，以后的操作中只需要從高速緩沖器中調(diào)用已編譯好的二進制代碼執(zhí)行即可，從而提高了系統(tǒng)性能。

缺點:

存儲過程，往往定制化于特定的數(shù)據(jù)庫上，因為支持的編程語言不同。當(dāng)切換到其他廠商的數(shù)據(jù)庫系統(tǒng)時，需要重寫原有的存儲過程。

1.3 創(chuàng)建存儲過程

1.3.1 DELIMITER定界符

在sql中服務(wù)器處理sql語句默認是以分號作為語句的結(jié)束標(biāo)志，然而在創(chuàng)建存儲過程時，存儲過程體中可能包含多條sql語句，這些sql語句如果仍以分號作為語句結(jié)束符，那么服務(wù)器在處理時會以第一條sql語句處的分號作為整個程序的結(jié)束符，而不再去處理后面的sql。
為解決這個問題，通常使用DELIMITER命令，將sql語句的結(jié)束符臨時修改為其他符號。

DELIMITER語法格式：

DELIMITER $$

$$是用戶定義的結(jié)束符，通常這個符號可以是一些特殊的符號。另外應(yīng)避免使用反斜杠,因為他是轉(zhuǎn)義字符。
若希望換回默認的分號作為結(jié)束標(biāo)記，只需再在命令行輸入下面的sql語句即可。

DELIMITER ;

1.3.2 存儲過程創(chuàng)建

在Mysql中，使用CREATE PROCEDURE語句來創(chuàng)建存儲過程。

CREATE PROCEDURE p_name([proc_parameter[,...]])
routine_body

其中，語法項“proc_parameter”的語法格式是：

[IN|OUT|INOUT]parame_name type

1."p_name"用于指定存儲過程的名稱。

2."proc_parameter"用于指定存儲過程中的參數(shù)列表。其中，語法項"parame_name"為參數(shù)名，"type"為參數(shù)的類型（類型可以是Mysql中任意的有效數(shù)據(jù)類型）。Mysql的存儲過程支持三種類型的參數(shù)，即輸入?yún)?shù)IN，輸出參數(shù)OUT,輸入輸出參數(shù)INOUT。輸入?yún)?shù)是使數(shù)據(jù)可以傳遞給一個存儲過程；輸出參數(shù)是用于存儲過程需要返回的一個操作結(jié)果；輸入輸出參數(shù)既可以充當(dāng)輸入?yún)?shù)也可以充當(dāng)輸出結(jié)果。
參數(shù)的取名不要和表中的列名相同，否則盡管不會返回出錯信息，但儲存過程中的sql語句會將參數(shù)名當(dāng)做列名，從而引發(fā)不可預(yù)知的錯誤。

3.語法項"rountine_body"表示存儲過程的主體部分，也成為存儲過程體，其包含了需要執(zhí)行的sql。過程體以關(guān)鍵字BEGIN開始，以關(guān)鍵字END結(jié)束。若只有一條sql可以忽略BEGIN....END標(biāo)志。

1.3.3 局部變量

在存儲過程體中可以聲明局部變量，用來存儲過程體中的臨時結(jié)果。在Mysql中使用DECLARE語句來聲明局部變量。

DECLARE var_name type [DEFAULT value]

"var_name"用于指定局部變量的名稱；"type"用來聲明變量的類型；"DEFAULT"用來指定默認值，如果沒有指定則為NULL。

注意：局部變量只能在存儲過程體的BEGIN...END語句塊中;局部變量必須在存儲過程體的開頭處聲明;局部變量的作用范圍僅限于聲明它的BEGIN...END語句塊，其他語句塊中的語句不可以使用它。

1.3.4 用戶變量

用戶變量一般以@開頭。

注意：濫用用戶變量會導(dǎo)致程序難以理解及管理。

1.3.5 SET語句

在Mysql中通過SET語句對局部變量賦值，其格式是：

SET var_name = expr[,var_name2 = expr]....

1.3.6 SELECT....INTO語句

在Mysql中，可以使用SELECT...INTO語句把選定的列的值存儲到局部變量中。格式是：

SELECT col_name[,..] INTO var_name[,....] table_expr

其中"col_name"用于指定列名；"var_name"用于指定要賦值的變量名；"table_expr"表示SELECT語句中FROM后面的部分。

注意：SELECT...INTO語句返回的結(jié)果集只能有一行數(shù)據(jù)。

1.3.7 流程控制語句

條件判斷語句

if-then-else 語句:

mysql > DELIMITER  
mysql > CREATE PROCEDURE proc2(IN parameter int) 
 -> begin 
 -> declare var int; 
 -> set var=parameter+1; 
 -> if var=0 then 
 -> insert into t values(17); 
 -> end if; 
 -> if parameter=0 then 
 -> update t set s1=s1+1; 
 -> else 
 -> update t set s1=s1+2; 
 -> end if; 
 -> end; 
 ->  
mysql > DELIMITER ; 

case語句:

mysql > DELIMITER  
mysql > CREATE PROCEDURE proc3 (in parameter int) 
 -> begin 
 -> declare var int; 
 -> set var=parameter+1; 
 -> case var 
 -> when 0 then 
 -> insert into t values(17); 
 -> when 1 then 
 -> insert into t values(18); 
 -> else 
 -> insert into t values(19); 
 -> end case; 
 -> end; 
 ->  
mysql > DELIMITER ; 

循環(huán)語句
while ···· end while:

mysql > DELIMITER  
mysql > CREATE PROCEDURE proc4() 
 -> begin 
 -> declare var int; 
 -> set var=0; 
 -> while var6 do 
 -> insert into t values(var); 
 -> set var=var+1; 
 -> end while; 
 -> end; 
 ->  
mysql > DELIMITER ;

repeat···· end repea：

它在執(zhí)行操作后檢查結(jié)果，而 while 則是執(zhí)行前進行檢查。

mysql > DELIMITER  
mysql > CREATE PROCEDURE proc5 () 
 -> begin 
 -> declare v int; 
 -> set v=0; 
 -> repeat 
 -> insert into t values(v); 
 -> set v=v+1; 
 -> until v>=5 
 -> end repeat; 
 -> end; 
 ->  
mysql > DELIMITER ;

repeat
 --循環(huán)體
 until 循環(huán)條件 
end repeat;

loop ·····endloop:

loop 循環(huán)不需要初始條件，這點和 while 循環(huán)相似，同時和 repeat 循環(huán)一樣不需要結(jié)束條件, leave 語句的意義是離開循環(huán)。

mysql > DELIMITER  
mysql > CREATE PROCEDURE proc6 () 
 -> begin 
 -> declare v int; 
 -> set v=0; 
 -> LOOP_LABLE:loop 
 -> insert into t values(v); 
 -> set v=v+1; 
 -> if v >=5 then 
 -> leave LOOP_LABLE; 
 -> end if; 
 -> end loop; 
 -> end; 
 ->  
mysql > DELIMITER ;

ITERATE迭代：

mysql > DELIMITER  
mysql > CREATE PROCEDURE proc10 () 
 -> begin 
 -> declare v int; 
 -> set v=0; 
 -> LOOP_LABLE:loop 
 -> if v=3 then 
 -> set v=v+1; 
 -> ITERATE LOOP_LABLE; 
 -> end if; 
 -> insert into t values(v); 
 -> set v=v+1; 
 -> if v>=5 then 
 -> leave LOOP_LABLE; 
 -> end if; 
 -> end loop; 
 -> end; 
 ->  
mysql > DELIMITER ;

1.3.8 游標(biāo)

MySQL中的游標(biāo)可以理解成一個可迭代對象(類比Python中的列表、字典等可迭代對象)，它可以用來存儲select 語句查詢到的結(jié)果集，這個結(jié)果集可以包含多行數(shù)據(jù)，從而使我們可以使用迭代的方法從游標(biāo)中依次取出每行數(shù)據(jù)。

MySQL游標(biāo)的特點：
1.只讀：無法通過光標(biāo)更新基礎(chǔ)表中的數(shù)據(jù)。
2.不可滾動：只能按照select語句確定的順序獲取行。不能以相反的順序獲取行。 此外，不能跳過行或跳轉(zhuǎn)到結(jié)果集中的特定行。
3.敏感：有兩種游標(biāo)：敏感游標(biāo)和不敏感游標(biāo)。敏感游標(biāo)指向?qū)嶋H數(shù)據(jù)，不敏感游標(biāo)使用數(shù)據(jù)的臨時副本。敏感游標(biāo)比一個不敏感的游標(biāo)執(zhí)行得更快，因為它不需要臨時拷貝數(shù)據(jù)。MySQL游標(biāo)是敏感的。

1.聲明游標(biāo)

游標(biāo)聲明必須在變量聲明之后。如果在變量聲明之前聲明游標(biāo)，MySQL將會發(fā)出一個錯誤。游標(biāo)必須始終與select語句相關(guān)聯(lián)。

declare cursor_name cursor for select_statement;

2.打開游標(biāo)

使用open語句打開游標(biāo)，只有先打開游標(biāo)才能讀取數(shù)據(jù)。

open cursor_name;

3.讀取游標(biāo)

使用fetch語句來檢索游標(biāo)指向的一行數(shù)據(jù)，并將游標(biāo)移動到結(jié)果集中的下一行。

fetch cursor_name into var_name;

4.關(guān)閉游標(biāo)

使用close語句關(guān)閉游標(biāo)。

close cursor_name;

當(dāng)游標(biāo)不再使用時，應(yīng)該關(guān)閉它。   當(dāng)使用MySQL游標(biāo)時，還必須聲明一個notfound處理程序來處理當(dāng)游標(biāo)找不到任何行時的情況。 因為每次調(diào)用fetch語句時，游標(biāo)會嘗試依次讀取結(jié)果集中的每一行數(shù)據(jù)。 當(dāng)游標(biāo)到達結(jié)果集的末尾時，它將無法獲得數(shù)據(jù)，并且會產(chǎn)生一個條件。 處理程序用于處理這種情況。

declare continue handler for not found set type = 1;

type是一個變量，示游標(biāo)到達結(jié)果集的結(jié)尾。

delimiter $$
create PROCEDURE phoneDeal()
BEGIN
 DECLARE id varchar(64); -- id
 DECLARE phone1 varchar(16); -- phone
 DECLARE password1 varchar(32); -- 密碼
 DECLARE name1 varchar(64); -- id
 -- 遍歷數(shù)據(jù)結(jié)束標(biāo)志
 DECLARE done INT DEFAULT FALSE;
 -- 游標(biāo)
 DECLARE cur_account CURSOR FOR select phone,password,name from account_temp;
 -- 將結(jié)束標(biāo)志綁定到游標(biāo)
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
 
 -- 打開游標(biāo)
 OPEN cur_account; 
 -- 遍歷
 read_loop: LOOP
 -- 取值 取多個字段
 FETCH NEXT from cur_account INTO phone1,password1,name1;
 IF done THEN
 LEAVE read_loop;
 END IF;
 
 -- 你自己想做的操作
 insert into account(id,phone,password,name) value(UUID(),phone1,password1,CONCAT(name1,'的家長'));
 END LOOP;
 
 -- 關(guān)閉游標(biāo)
 CLOSE cur_account;
END $$

1.3.7 調(diào)用存儲過程

使用call語句調(diào)用存儲過程

call sp_name[(傳參)];

1.3.8 刪除存儲過程

使用drop語句刪除存儲過程

DROP PROCEDURE sp_name

2 存儲函數(shù)

2.1 什么是存儲函數(shù)

存儲函數(shù)和存儲過程一樣，都是sql和語句組成的代碼塊。
存儲函數(shù)不能有輸入?yún)?shù)，并且可以直接調(diào)用，不需要call語句，且必須有一條包含RETURN語句。

2.2 創(chuàng)建存儲函數(shù)

在Mysql中使用CREATE FUNCTION語句創(chuàng)建：

CREATE FUNCTION fun_name (par_name type[,...])
RETURNS type
[characteristics] 
fun_body

其中，fun_name為函數(shù)名，并且名字唯一，不能與存儲過程重名。par_name是指定的參數(shù)，type為參數(shù)類型；RETURNS字句用來聲明返回值和返回值類型。fun_body是函數(shù)體，所有存儲過程中的sql在存儲函數(shù)中同樣可以使用。但是存儲函數(shù)體中必須包含一個RETURN 語句。
characteristics指定存儲過程的特性，有以下取值：

• LANGUAGE SQL:說明routine_body部分是由SQL語句組成的，當(dāng)前系統(tǒng)支持的語言為SQL，SQL是LANGUAGE特性的唯一值。
• [NOT] DETERMINISTIC:指明存儲過程執(zhí)行的結(jié)果是否確定。DETERMINISTIC表示結(jié)果是確定的，每次執(zhí)行存儲過程時，相同的輸入會得到相同的輸出，NOT DETERMINISTIC表示結(jié)果是不確定的，相同的輸入可能得到不同的輸出，如果沒有指定任意一個值，默認為NOT DETERMINISTIC。
• [CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA]：指明子程序使用SQL語句的限制。CONTAINS SQL表明子程序包含SQL語句，但不包含讀寫數(shù)據(jù)語句；NO SQL表明子程序不包含SQL語句；READS SQL DATA說明子程序包含讀數(shù)據(jù)的語句；MODIFIES SQL DATA表名子程序包含寫數(shù)據(jù)的語句。默認情況下，系統(tǒng)會指定為CONTAINS SQL。
• SQL SECURITY[DEFINER|INVOKER]：指明誰有權(quán)限來執(zhí)行。DEFINER表示只有定義著才能執(zhí)行。INVOKER表示用友權(quán)限的調(diào)用者可以執(zhí)行。默認情況下，系統(tǒng)指定為DEFINER。
• COMMENT 'string'：注釋信息，用來描述存儲過程或函數(shù)。

delimiter $$
create function getAnimalName(animalId int) RETURNS VARCHAR(50)
DETERMINISTIC
begin
 declare name VARCHAR(50);
 set name=(select name from animal where id=animalId);
 return (name);
end$$
delimiter;

-- 調(diào)用
select getAnimalName(4)

以上就是本文的全部內(nèi)容，希望對大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。