8.存儲過程實驗
8.1存儲過程定義
存儲過程是一組為了完成特定功能的 SQL 語句集合。使用存儲過程的目的是将常用或複雜的工作預先用 SQL 語句寫好并用一個指定名稱存儲起來，這個過程經編譯和優化後存儲在資料庫伺服器中，是以稱為存儲過程。當以後需要資料庫提供與已定義好的存儲過程的功能相同的服務時，隻需調用“CALL存儲過程名字”即可自動完成。
8.2存儲過程的優點
1) 封裝性
通常完成一個邏輯功能需要多條 SQL 語句，而且各個語句之間很可能傳遞參數，是以，編寫邏輯功能相對來說稍微複雜些，而存儲過程可以把這些 SQL 語句包含到一個獨立的單元中，使外界看不到複雜的 SQL 語句，隻需要簡單調用即可達到目的。并且資料庫專業人員可以随時對存儲過程進行修改，而不會影響到調用它的應用程式源代碼。
2) 可增強 SQL 語句的功能和靈活性
存儲過程可以用流程控制語句編寫，有很強的靈活性，可以完成複雜的判斷和較複雜的運算。
3) 可減少網絡流量
由于存儲過程是在伺服器端運作的，且執行速度快，是以當客戶計算機上調用該存儲過程時，網絡中傳送的隻是該調用語句，進而可降低網絡負載。
4) 高性能
當存儲過程被成功編譯後，就存儲在資料庫伺服器裡了，以後用戶端可以直接調用，這樣所有的 SQL 語句将從伺服器執行，進而提高性能。但需要說明的是，存儲過程不是越多越好，過多的使用存儲過程反而影響系統性能。
5) 提高資料庫的安全性和資料的完整性
存儲過程提高安全性的一個方案就是把它作為中間元件，存儲過程裡可以對某些表做相關操作，然後存儲過程作為接口提供給外部程式。這樣，外部程式無法直接操作資料庫表，隻能通過存儲過程來操作對應的表，是以在一定程度上，安全性是可以得到提高的。
6) 使資料獨立
資料的獨立可以達到解耦的效果，也就是說，程式可以調用存儲過程，來替代執行多條的 SQL 語句。這種情況下，存儲過程把資料同使用者隔離開來，優點就是當資料表的結構改變時，調用表不用修改程式，隻需要資料庫管理者重新編寫存儲過程即可。
8.3建立存儲過程
文法格式如下：
CREATE PROCEDURE <過程名> ( [過程參數[,…] ] ) <過程體>
[過程參數[,…] ] 格式
[ IN | OUT | INOUT ] <參數名> <類型>
文法說明如下：
1) 過程名
存儲過程的名稱，預設在目前資料庫中建立。若需要在特定資料庫中建立存儲過程，則要在名稱前面加上資料庫的名稱，即 db_name.sp_name。需要注意的是，名稱應當盡量避免選取與 MySQL 内置函數相同的名稱，否則會發生錯誤。
2) 過程參數
存儲過程的參數清單。其中，
< 參數名 >
為參數名，
< 類型 >
為參數的類型（可以是任何有效的 MySQL 資料類）。當有多個參數時，參數清單中彼此間用逗号分隔。存儲過程可以沒有參數（此時存儲過程的名稱後仍需加上一對括号），也可以有 1 個或多個參數。
MySQL 存儲過程支援三種類型的參數，即輸入參數、輸出參數和輸入/輸出參數，分别用 IN、OUT 和 INOUT 三個關鍵字辨別。其中，輸入參數可以傳遞給一個存儲過程，輸出參數用于存儲過程需要傳回一個操作結果的情形，而輸入/輸出參數既可以充當輸入參數也可以充當輸出參數。
需要注意的是，參數的取名不要與資料表的列名相同，否則盡管不會傳回出錯資訊，但是存儲過程的 SQL 語句會将參數名看作列名，進而引發不可預知的結果。
3) 過程體
存儲過程的主體部分，也稱為存儲過程體，包含在過程調用的時候必須執行的 SQL 語句。這個部分以關鍵字 BEGIN 開始，以關鍵字 END 結束。若存儲過程體中隻有一條 SQL 語句，則可以省略 BEGIN-END 标志。
在存儲過程的建立中，經常會用到一個十分重要的 MySQL 指令，即 DELIMITER 指令，特别是對于通過指令行的方式來操作 MySQL 資料庫的使用者，更是要學會使用該指令。
注意：在 MySQL 中，伺服器處理 SQL 語句預設是以分号作為語句結束标志的。然而，在建立存儲過程時，存儲過程體可能包含有多條 SQL 語句，這些 SQL 語句如果仍以分号作為語句結束符，那麼 MySQL 伺服器在處理時會以遇到的第一條 SQL 語句結尾處的分号作為整個程式的結束符，而不再去處理存儲過程體中後面的 SQL 語句，這樣顯然不行。
為解決以上問題，通常使用 DELIMITER 指令将結束指令修改為其他字元。文法格式如下：
DELIMITER $$
文法說明如下：
- $$ 是使用者定義的結束符，通常這個符号可以是一些特殊的符号，如兩個“?”或兩個“￥”等。
- 當使用 DELIMITER 指令時，應該避免使用反斜杠“\”字元，因為它是 MySQL 的轉義字元。
在 MySQL 指令行用戶端輸入如下 SQL 語句。
mysql > DELIMITER ??
成功執行這條 SQL 語句後，任何指令、語句或程式的結束标志就換為兩個問号“??”了。
若希望換回預設的分号“;”作為結束标志，則在 MySQL 指令行用戶端輸入下列語句即可：
注意：DELIMITER 和分号“;”之間一定要有一個空格。在建立存儲過程時，必須具有 CREATE ROUTINE 權限。
8.3.1建立存儲過程例子
mysql> delimiter $$
mysql> create procedure show_employeeInfo()
-> begin
-> select *from employee;
-> end$$
8.4檢視存儲過程結構：
8.5删除存儲過程：
文法說明如下：
①過程名：指定要删除的存儲過程的名稱。
②IF EXISTS：指定這個關鍵字，用于防止因删除不存在的存儲過程而引發的錯誤。
**注意：**存儲過程名稱後面沒有參數清單，也沒有括号，在删除之前，必須确認該存儲過程沒有任何依賴關系，否則會導緻其他與之關聯的存儲過程無法運作。
8.6修改存儲過程的文法格式如下：
特征
指定了存儲過程的特性，可能的取值有：
①CONTAINS SQL 表示子程式包含 SQL 語句，但不包含讀或寫資料的語句。
②NO SQL 表示子程式中不包含 SQL 語句。
③READS SQL DATA 表示子程式中包含讀資料的語句。
④MODIFIES SQL DATA 表示子程式中包含寫資料的語句。
⑤SQL SECURITY { DEFINER |INVOKER } 指明誰有權限來執行。
⑥DEFINER 表示隻有定義者自己才能夠執行。
⑦INVOKER 表示調用者可以執行。
⑧COMMENT ‘string’ 表示注釋資訊。
8.7存儲過程調用
8.8存儲函數
和存儲過程一樣，都是在資料庫中定義一些 SQL 語句的集合。存儲函數可以通過 return 語句傳回函數值，主要用于計算并傳回一個值。而存儲過程沒有直接傳回值，主要用于執行操作。
8.1.8.1文法在 MySQL 中，使用 CREATE FUNCTION 語句來建立存儲函數，其文法形式如下：
CREATE FUNCTION sp_name ([func_parameter[...]])
RETURNS type
[characteristic ...] routine_body
其中：
①sp_name 參數：表示存儲函數的名稱；
②func_parameter：表示存儲函數的參數清單；
③RETURNS type：指定傳回值的類型；
④characteristic 參數：指定存儲函數的特性，該參數的取值與存儲過程是一樣的；
⑤routine_body 參數：表示 SQL 代碼的内容，可以用 BEGIN…END 來标示 SQL 代碼的開始和結束。
**注意：**在具體建立函數時，函數名不能與已經存在的函數名重名。除了上述要求外，推薦函數名命名（辨別符）為 function_xxx 或者 func_xxx。
func_parameter 可以由多個參數組成，其中每個參數由參數名稱和參數類型組成，其形式如下：
[IN | OUT | INOUT] param_name type;
其中：
①IN 表示輸入參數，OUT 表示輸出參數，INOUT 表示既可以輸入也可以輸出；
②param_name 參數是存儲函數的參數名稱；
③type 參數指定存儲函數的參數類型，該類型可以是 MySQL 資料庫的任意資料類型。
8.8.2例子
Delimiter $$;
create function real_income(employeeID_1 varchar(6),employeeID_2 varchar(6)) returns tinyint(1) begin if (select income - outcome from salary where employeeID=employeeID_1) > (select income - outcome from salary where employeeID=employeeID_2) then return 0; else return 1; end if ; end$$
8.9定義變量
MySQL 中可以使用 DECLARE 關鍵字來定義變量，其基本文法如下：
DECLARE var_name[,…] type [DEFAULT value]
其中：
- DECLARE 關鍵字是用來聲明變量的；
- var_name 參數是變量的名稱，這裡可以同時定義多個變量；
- type 參數用來指定變量的類型；
- DEFAULT value 子句将變量預設值設定為 value，沒有使用 DEFAULT 子句時，預設值為 NULL。
8.9.1例 1
下面定義變量 my_sql，資料類型為 INT 類型，預設值為 10。SQL 語句如下：
8.9.2. 為變量指派
MySQL 中可以使用 SET 關鍵字來為變量指派，SET 語句的基本文法如下：
SET var_name = expr[,var_name = expr]...
其中：
- SET 關鍵字用來為變量指派；
- var_name 參數是變量的名稱；
- expr 參數是指派表達式。
注意：一個 SET 語句可以同時為多個變量指派，各個變量的指派語句之間用逗号隔開。
8.9.3例 2
下面為變量 my_sql 指派為 30。SQL 語句如下：
MySQL 中還可以使用 SELECT…INTO 語句為變量指派。其基本文法如下：
SELECT col_name [...] INTO var_name[,...]
FROM table_name WEHRE condition
其中：
- col_name 參數表示查詢的字段名稱；
- var_name 參數是變量的名稱；
- table_name 參數指表的名稱；
- condition 參數指查詢條件。
注意：當将查詢結果指派給變量時，該查詢語句的傳回結果隻能是單行。
8.10例子
8.10.1.建立一個存儲過程執行個體
mysql> delimiter $$
mysql> create procedure show_employeeInfo()
-> begin
-> select *from employee;
-> end$$
Query OK, 0 rows affected (0.49 sec)
然後檢視過程存儲建立結構：
最後調用存儲過程函數：
8.10.2.删除存儲過程：使用DROP PROCEDURE語句删除存儲過程，文法格式為：
DROP PROCEDURE [IF EXISTS] 存儲過程名;
8.10.3建立一個存儲過程，計算employee表中的員勞工數，并存儲到一個局部變量中，調用存儲過程，并檢視該變量結果（使用select @variable）
create procedure count_employee(out count_emp int) begin set count_emp = (select count(*) from employee) ; end $$
調用并查詢結果：
8.10.4.建立一個存儲過程，比較兩個員工的實際收入，若前者比後者高就輸出0，否則輸出1，員工用其員工編号識别。
Delimiter $$;
create function real_income(employeeID_1 varchar(6),employeeID_2 varchar(6)) returns tinyint(1) begin if (select income - outcome from salary where employeeID=employeeID_1) > (select income - outcome from salary where employeeID=employeeID_2) then return 0; else return 1; end if ; end$$
這時出現報錯：
這是我們開啟了bin-log, 我們就必須指定我們的函數是否是
1 DETERMINISTIC 不确定的
2 NO SQL 沒有SQl語句，當然也不會修改資料
3 READS SQL DATA 隻是讀取資料，當然也不會修改資料
4 MODIFIES SQL DATA 要修改資料
5 CONTAINS SQL 包含了SQL語句
其中在function裡面，隻有 DETERMINISTIC, NO SQL 和 READS SQL DATA 被支援。如果我們開啟了 bin-log, 我們就必須為我們的function指定一個參數。
在MySQL中建立函數時出現這種錯誤的解決方法：
set global log_bin_trust_function_creators=TRUE;
問題解決。
然後開始調用函數：
成功實作需要的功能。