存儲過程是一組為了完成某項特定功能的SQL語句集， 其實質就是一段存儲在資料庫中的代碼。 它可以由聲明式的sql語句和過程式sql語句組成。 ...
一、存儲過程
概念：存儲過程是一組為了完成某項特定功能的SQL語句集， 其實質就是一段存儲在資料庫中的代碼。 它可以由聲明式的sql語句和過程式sql語句組成。
特點：
- 可增強SQL語言的功能和靈活性
- 良好的封裝性
- 高性能
- 可減少網路流量
- 可作為一種安全機制來確保資料庫的安全性和數據的完整性
用戶定義的結束符（ELIMITER）
- ELIMITER $$：
- 例句：將MySQL結束符修改為兩個感嘆號“!!” （DELIMITER !!）
使用CREATE PROCEDURE語句創建存儲過程：CREATE PROCEDURE sp_name([proc_parameter[,…]]) routine_body ；
proc_paramete：指定存儲過程的參數列表
routine_body：存儲過程的主體部分，也稱為存儲過程體
[IN | OUT | INOUT] param_name type：參數 名，參數類型
例如：在mysql_test中創建一個存儲過程，用於實現給定表customers中一個客戶id號即可修改表customers中該客戶的性別為一個指定的性別
使用DECLARE語句聲明局部變數：DECLARE var_name[,…] type [DEFAULT value]
例如：聲明一個整型局部變數cid： DECLARE cid INT(10);
使用DECLARE語句聲明局部變數的規範
- 1）只能在存儲過程體的BEGIN…END語句塊中聲明；
- 2）必須在存儲過程的開頭處聲明；
- 3）作用範圍僅限於聲明它的BEGIN…END語句塊；
- 4）不同於用戶變數
局部變數與用戶變數的區別：
- 1）局部變數聲明時，在其前面沒有@符號，並且它只能 被聲明它的BEGIN…END語句塊中的語句所使用；
- 2）用戶變數在聲明時，會在其名稱前面使用@符號，同 時已聲明的用戶變數存在於整個會話之中。
使用SET語句為局部變數賦值：SET var_name=expr[,var_name=expr]… SET cid=910;
使用SELECT…INTO語句把選定列的值直接存儲到局部變數中
流程式控制制語句
- 1、條件判斷語句 IF…THEN …ELSE語句 CASE語句 （IF 條件 THEN 表達式1 ELSE 表達式2 END IF;）
- 2、迴圈語句 WHILE語句 REPEAT語句 LOOP語句
WHILE 條件 表達式 END WHILE
repeat 表達式 END repeat
loop 表達式 END loop
ITERATE語句 用於表示退出當前迴圈
****************************游標CURSOR*****************************************
使用DECLARE CURSOR語句創建游標
使用OPEN語句打開游標：OPEN cursor_name
使用FETCH…INTO語句讀取數據：
使用CLOSE語句關閉游標 ：CLOSE cursor_name
**********************存儲過程的使用************************
使用CALL語句調用存儲過程
調用資料庫mysql_test中的存儲過程sp_update_sex，將客戶id號為909的客戶性別修改為男性“M”
CALL sp_update_sex(909,’M’);
使用DROP PROCEDURE語句刪除存儲過程
DROP PROCEDURE[IF EXISTS] sp_name
二、存儲函數
存儲函數與存儲過程一樣，是由SQL語句和過程式語句組成的代碼片段
使用CREATE FUNCTION語句創建存儲函數
CREATE FUNCTION sp_name([func_parameter[,…]]) RETURNS type routine_body
- sp_name：指定存儲函數的名稱
- func_parameter：指定存儲函數的參數
- RETURNS type ：聲明存儲函數返回值的數據類型； type指定返回值的數據類型
- routine_body 指定存儲函數的主體部分，也稱為存儲函數體
在資料庫mysql_test中創建 一個存儲函數，要求該函數能根據 給定的客戶id號返回客戶的性別， 如果資料庫中沒有給定的客戶id號 ，則返回“沒有該客戶”。
Use mysql_test; DELIMITER $$ CREATE FUNCTION fn_search(cid INT) RETURNS CHAR(20) DETERMINISTIC BEGIN DECLARE SEX CHAR(20); SELECT cust_sex INTO SEX FROM customers WHERE cust_id=cid; IF SEX IS NULL THEN RETURN(SELECT’沒有該客戶’); ELSE IF SEX=‘F’ THEN RETURN(SELECT’女’); ELSE RETURN(SELECT ‘男’); END IF; END IF; END $$
使用關鍵字SELECT調用存儲函數：SELECT sp_name([func_parameter[, …]])
使用DROP FUNCTION語句刪除存儲函數：DROP FUNCTION [IF EXISTS] sp_name
