存儲(chǔ)過(guò)程

1.存儲(chǔ)過(guò)程的基本概念

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

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

2.創(chuàng)建存儲(chǔ)過(guò)程

DELIMITER命令

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

使用CREATE PROCEDURE語(yǔ)句創(chuàng)建存儲(chǔ)過(guò)程

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

在mysql_test中創(chuàng)建一個(gè)存儲(chǔ)過(guò)程，用于實(shí)現(xiàn)給定表customers中一個(gè)客戶(hù)id號(hào)即可修改該客戶(hù)的性別為指定性別：

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.存儲(chǔ)過(guò)程體

1. 使用DECLARE語(yǔ)句聲明局部變量

   1. 只能在存儲(chǔ)過(guò)程體的BEGIN...END語(yǔ)句塊中使用
   2. 必須在存儲(chǔ)過(guò)程的開(kāi)頭處聲明
   3. 作用范圍僅限制在聲明它的BEGIN...END語(yǔ)句塊中
   4. 不同于用戶(hù)變量

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

   1. 局部變量聲明時(shí)，在其前面沒(méi)有@符號(hào)，并且它只能在聲明它的BEGIN...END語(yǔ)句塊中的語(yǔ)句使用。
   2. 用戶(hù)變量在聲明時(shí)，會(huì)在其名稱(chēng)前使用@符號(hào)，同時(shí)已聲明的用戶(hù)變量存在于整個(gè)會(huì)話(huà)之中。

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

   例：聲明一個(gè)局部變量cid：

   declare cid int(10) default 1;
   

2. 使用SET語(yǔ)句為局部變量賦值：

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

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

   set cid = 910;
   

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

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

4. 流程控制語(yǔ)句

   1. 條件判斷語(yǔ)句

      1. IF...THEN...ELSE語(yǔ)句

         IF 條件 THEN 
             表達(dá)式1
         ELSE
             表達(dá)式2
         END IF;
         

      2. CASE語(yǔ)句

   2. 循環(huán)語(yǔ)句

      1. WHILE語(yǔ)句

         WHILE 條件
             表達(dá)式
         END WHILE;
         

      2. REPEAT語(yǔ)句

         repeat
             表達(dá)式
         END repeat;
         

      3. LOOP語(yǔ)句

         loop
             表達(dá)式
         END loop;
         

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

5. 使用DECLARE CURSOR語(yǔ)句創(chuàng)建游標(biāo)

   //cursor_name：指定要?jiǎng)?chuàng)建的游標(biāo)名稱(chēng)
   DECLARE cursor_name CURSOR FRO select_statement
   

6. 使用OPEN語(yǔ)句打開(kāi)游標(biāo)

   OPEN cursor_name
   

7. 使用FETCH...INTO語(yǔ)句來(lái)讀取數(shù)據(jù)

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

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

   CLOSE cursor_name
   

4.調(diào)用存儲(chǔ)過(guò)程

使用CALL語(yǔ)句調(diào)用存儲(chǔ)過(guò)程

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

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

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

5.刪除存儲(chǔ)過(guò)程

使用DROP PROCEDURE語(yǔ)句刪除存儲(chǔ)過(guò)程

drop procedure [if exists] sp_name;

存儲(chǔ)函數(shù)

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

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

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

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

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

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

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)用存儲(chǔ)函數(shù)

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

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

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

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

DROP FUNCTION [IF EXISTS] fn_name;