存儲過程

1.存儲過程的基本概念

存儲過程是一組為了完成某項特定功能的SQL語句集，其實質(zhì)就是存儲在數(shù)據(jù)庫中的代碼。它可以由聲明式的sql語句和過程式的sql語句組成。

• 優(yōu)點：
  • 可增強sql語言的功能和靈活性
  • 良好的封裝性
  • 高性能
  • 可減少網(wǎng)絡(luò)流量
  • 可作為一種安全機制確保數(shù)據(jù)庫的安全性和數(shù)據(jù)的完整性

2.創(chuàng)建存儲過程

DELIMITER命令

DELIMITER ?; //?:用戶定義的結(jié)束符
DELIMITER ; //重新使用';'作為結(jié)束符

使用CREATE PROCEDURE語句創(chuàng)建存儲過程

/*
sp_name：指定存儲過程的名稱，默認在當前數(shù)據(jù)庫中創(chuàng)建
proc_parameter：指定存儲過程中的參數(shù)列表，格式為：[IN | OUT | INOUT] parameter_name type
routine_body：存儲過程的主體部分，也成為存儲過程體
*/
CREATE PROCEDURE sp_name([proc_parameter[, ...]])
    routine_body

在mysql_test中創(chuàng)建一個存儲過程，用于實現(xiàn)給定表customers中一個客戶id號即可修改該客戶的性別為指定性別：

mysql> delimiter ?
mysql> create procedure sp_update_sex(in cid int, in csex char(1))
    -> begin
    -> update mysql_test.customers
    -> set c_sex = csex
    -> where c_id = cid;
    -> end ?

3.存儲過程體

1. 使用DECLARE語句聲明局部變量

   1. 只能在存儲過程體的BEGIN...END語句塊中使用
   2. 必須在存儲過程的開頭處聲明
   3. 作用范圍僅限制在聲明它的BEGIN...END語句塊中
   4. 不同于用戶變量

   局部變量與用戶變量的區(qū)別：

   1. 局部變量聲明時，在其前面沒有@符號，并且它只能在聲明它的BEGIN...END語句塊中的語句使用。
   2. 用戶變量在聲明時，會在其名稱前使用@符號，同時已聲明的用戶變量存在于整個會話之中。

   /*
   var_name：指定局部變量的名稱
   type：指定局部變量的類型
   DEFAULT value：局部變量設(shè)置默認值
   */
   DECLARE var_name[, ...] type [DEFAULT value]
   

   例：聲明一個局部變量cid：

   declare cid int(10) default 1;
   

2. 使用SET語句為局部變量賦值：

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

   例：為局部變量cid賦予一個整數(shù)值910

   set cid = 910;
   

3. 使用SELECT...INTO語句把選定列的值直接存儲在局部變量中

   /*
   col_name：指定列名
   var_name：指定要賦值的變量名
   table_expr：select語句中from子句及后面的語法部分
   */
   SELECT col_name[, ...] INTO var_name[, ...] table_expr
   

4. 流程控制語句

   1. 條件判斷語句

      1. IF...THEN...ELSE語句

         IF 條件 THEN 
             表達式1
         ELSE
             表達式2
         END IF;
         

      2. CASE語句

   2. 循環(huán)語句

      1. WHILE語句

         WHILE 條件
             表達式
         END WHILE;
         

      2. REPEAT語句

         repeat
             表達式
         END repeat;
         

      3. LOOP語句

         loop
             表達式
         END loop;
         

   3. ITERATE語句：用于表示退出當前循環(huán)

5. 使用DECLARE CURSOR語句創(chuàng)建游標

   //cursor_name：指定要創(chuàng)建的游標名稱
   DECLARE cursor_name CURSOR FRO select_statement
   

6. 使用OPEN語句打開游標

   OPEN cursor_name
   

7. 使用FETCH...INTO語句來讀取數(shù)據(jù)

   /*
   cursor_name：指定已打開的游標
   var_name：指定存放數(shù)據(jù)的變量名
   */
   FETCH cursor_name INTO var_name[, var_name]...
   

8. 使用CLOSE語句關(guān)閉游標

   CLOSE cursor_name
   

4.調(diào)用存儲過程

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

/*
sp_name：指定被調(diào)用的存儲過程的名稱
parameter：指定調(diào)用存儲過程所需要的參數(shù)
*/
CALL sp_name([parameter[, ...]])
CALL sp_name[()]

調(diào)用數(shù)據(jù)庫mysql_test中的存儲過程sp_update_sex，將客戶id為909的客戶性別改為f：

mysql> call sp_update_sex(909, 'f');

5.刪除存儲過程

使用DROP PROCEDURE語句刪除存儲過程

drop procedure [if exists] sp_name;

存儲函數(shù)

1.什么是存儲函數(shù)

存儲函數(shù)與存儲過程一樣，是由sql語句和過程式語句組成的代碼片段。

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

使用CREATE FUNCTION語句創(chuàng)建存儲函數(shù)

/*
fn_name：指定存儲函數(shù)的名稱
func_parameter：指定存儲函數(shù)的參數(shù)，格式：parameter_name type
RETURNS type：聲明存儲函數(shù)返回值的數(shù)據(jù)類型
routine_body：指定存儲函數(shù)的主體部分，也稱存儲函數(shù)體
*/
CREATE FUNCTION fn_name([func_parameter[, ...]])
RETURNS type
routine_body

在數(shù)據(jù)庫mysql_test中創(chuàng)建一個存儲函數(shù)，要求該函數(shù)能根據(jù)給定的客戶id返回該客戶的性別，如果數(shù)據(jù)庫中沒有給定的客戶id，則返回沒有該客戶：

mysql> use mysql_test;
mysql> delimiter ?
mysql> create function fn_sex_with_id(cid int)
    -> returns char(20)
    -> deterministic
    -> begin
    -> declare sex char(20);
    -> select c_sex into sex from mysql_test.customers
    -> where c_id = cid;
    -> if sex is null then
    -> return(select 'not exists');
    -> else if sex = 'm' then
    -> return(select 'male');
    -> else 
    -> return(select 'female');
    -> end if;
    -> end if;
    -> end ?

3.調(diào)用存儲函數(shù)

使用關(guān)鍵字SELECT調(diào)用存儲函數(shù)

SELECT fn_name([fn_param[, ...]])

4.刪除存儲函數(shù)

使用DROP FUNCTION語句刪除存儲函數(shù)

DROP FUNCTION [IF EXISTS] fn_name;