細談Mysql的存儲過程和存儲函數

1 存儲過程

1.1 什么是存儲過程

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

1.2 存儲過程的優缺點

優點:

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

2.良好的封裝性
存儲過程被創建后，可以在程序中被多次調用，而不必擔心重寫編寫該存儲過程的sql語句。

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

缺點:

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

1.3 創建存儲過程

1.3.1 DELIMITER定界符

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

DELIMITER語法格式：

DELIMITER $$

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

DELIMITER ;

1.3.2 存儲過程創建

在Mysql中，使用CREATE PROCEDURE語句來創建存儲過程。

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

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

[IN|OUT|INOUT]parame_name type

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

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

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

1.3.3 局部變量

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

DECLARE var_name type [DEFAULT value]

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

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

1.3.4 用戶變量

用戶變量一般以@開頭。

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

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語句返回的結果集只能有一行數據。

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 ; 

循環語句
while ···· end while:

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

repeat···· end repea：

它在執行操作后檢查結果，而 while 則是執行前進行檢查。

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
 --循環體
 until 循環條件 
end repeat;

loop ·····endloop:

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

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 游標

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

MySQL游標的特點：
1.只讀：無法通過光標更新基礎表中的數據。
2.不可滾動：只能按照select語句確定的順序獲取行。不能以相反的順序獲取行。 此外，不能跳過行或跳轉到結果集中的特定行。
3.敏感：有兩種游標：敏感游標和不敏感游標。敏感游標指向實際數據，不敏感游標使用數據的臨時副本。敏感游標比一個不敏感的游標執行得更快，因為它不需要臨時拷貝數據。MySQL游標是敏感的。

1.聲明游標

游標聲明必須在變量聲明之后。如果在變量聲明之前聲明游標，MySQL將會發出一個錯誤。游標必須始終與select語句相關聯。

declare cursor_name cursor for select_statement;

2.打開游標

使用open語句打開游標，只有先打開游標才能讀取數據。

open cursor_name;

3.讀取游標

使用fetch語句來檢索游標指向的一行數據，并將游標移動到結果集中的下一行。

fetch cursor_name into var_name;

4.關閉游標

使用close語句關閉游標。

close cursor_name;

當游標不再使用時，應該關閉它。   當使用MySQL游標時，還必須聲明一個notfound處理程序來處理當游標找不到任何行時的情況。 因為每次調用fetch語句時，游標會嘗試依次讀取結果集中的每一行數據。 當游標到達結果集的末尾時，它將無法獲得數據，并且會產生一個條件。 處理程序用于處理這種情況。

declare continue handler for not found set type = 1;

type是一個變量，示游標到達結果集的結尾。

delimiter $$
create PROCEDURE phoneDeal()
BEGIN
 DECLARE id varchar(64); -- id
 DECLARE phone1 varchar(16); -- phone
 DECLARE password1 varchar(32); -- 密碼
 DECLARE name1 varchar(64); -- id
 -- 遍歷數據結束標志
 DECLARE done INT DEFAULT FALSE;
 -- 游標
 DECLARE cur_account CURSOR FOR select phone,password,name from account_temp;
 -- 將結束標志綁定到游標
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
 
 -- 打開游標
 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;
 
 -- 關閉游標
 CLOSE cur_account;
END $$

1.3.7 調用存儲過程

使用call語句調用存儲過程

call sp_name[(傳參)];

1.3.8 刪除存儲過程

使用drop語句刪除存儲過程

DROP PROCEDURE sp_name

2 存儲函數

2.1 什么是存儲函數

存儲函數和存儲過程一樣，都是sql和語句組成的代碼塊。
存儲函數不能有輸入參數，并且可以直接調用，不需要call語句，且必須有一條包含RETURN語句。

2.2 創建存儲函數

在Mysql中使用CREATE FUNCTION語句創建：

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

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

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

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;

-- 調用
select getAnimalName(4)

以上就是本文的全部內容，希望對大家的學習有所幫助，也希望大家多多支持億速云。