MYSQL學習與實驗（八）——儲存過程實驗

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 命令列客戶端輸入下列語句即可：

mysql > DELIMITER ;

注意：DELIMITER 和分號“;”之間一定要有一個空格。在建立儲存過程時，必須具有 CREATE ROUTINE 許可權。

8.3.1建立儲存過程例子

mysql> delimiter $$
mysql> create procedure show_employeeInfo()
    -> begin
    -> select *from employee;
    -> end$$

8.4檢視儲存過程結構：

show create procedure  show_employeeInfo \G;

8.5刪除儲存過程：

DROP PROCEDURE [ IF EXISTS ] <過程名>

語法說明如下：

①過程名：指定要刪除的儲存過程的名稱。

②IF EXISTS：指定這個關鍵字，用於防止因刪除不存在的儲存過程而引發的錯誤。

**注意：**儲存過程名稱後面沒有引數列表，也沒有括號，在刪除之前，必須確認該儲存過程沒有任何依賴關係，否則會導致其他與之關聯的儲存過程無法執行。

8.6修改儲存過程的語法格式如下：

ALTER PROCEDURE 儲存過程名 [ 特徵 ... ]

特徵指定了儲存過程的特性，可能的取值有：

①CONTAINS SQL 表示子程式包含 SQL 語句，但不包含讀或寫資料的語句。

②NO SQL 表示子程式中不包含 SQL 語句。

③READS SQL DATA 表示子程式中包含讀資料的語句。

④MODIFIES SQL DATA 表示子程式中包含寫資料的語句。

⑤SQL SECURITY { DEFINER |INVOKER } 指明誰有許可權來執行。

⑥DEFINER 表示只有定義者自己才能夠執行。

⑦INVOKER 表示呼叫者可以執行。

⑧COMMENT ‘string’ 表示註釋資訊。

8.7儲存過程呼叫

CALL 儲存過程名([引數…]);

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 語句如下：

DECLARE my_sql INT DEFAULT 10;

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 語句如下：

SET my_sql=30;

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;

問題解決。
然後開始呼叫函式：

成功實現需要的功能。

8.11本部落格已經同步到個人部落格，如有需要請移步：http://moyisuiying.com/index.php/experiment/mysqlexperiment/380.html