九、MySQL存儲過程和函數

 存儲過程就是一條或者多條SQL語句的集合，可視為批文件，但是其作用不僅限于批處理。

9.1、創建存儲過程和函數

  存儲程序可以分為存儲過程和函數，MySQL中創建存儲過程和函數使用的語句分別是CREATE PROCEDURE和CREATE FUNCTION。使用CALL語句來調用存儲過程，只能用輸出變量返回值。函數可以從語句外調用( 即通過引用函數名)，也能返回標量值。存儲過程也能調用其他存儲過程。

創建存儲過程

  創建存儲過程，需要使用CREATE PROCEDURE語句，基本語法為：

CREATE PROCEDURE sp_name ([proc_parameter])
    [characteristic ...] routine_body

創建查看fruits表的存儲過程

mysql> DELIMITER //

mysql> CREATE PROCEDURE Proc()
    ->      BEGIN
    ->      SELECT * FROM fruits;
    ->      END //
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

創建名稱為CountProc的存儲過程

mysql> DELIMITER //

mysql>  CREATE PROCEDURE CountProc(OUT param1 INT)
    ->   BEGIN
    ->   SELECT COUNT(*) INTO param1 FROM fruits;
    ->   END //
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

 'DELIMITER //'作用是將MySQL的結束符設置為//，當使用DELIMITER命令時，應該避免使用反斜杠（’\’）字符，因為反斜線是MySQL的轉義字符。

創建存儲函數

 創建存儲函數，需要使用CREATE FUNCTION語句，基本語法為：

CREATE FUNCTION func_name ([func_parameter])
  RETURNS type
  [characteristic ...] routine_body

創建存儲函數，名稱為NameByZip，該函數返回SELECT語句的查詢結果，數值類型為字符串型

mysql> DELIMITER //

mysql>  CREATE FUNCTION NameByZip()
    ->  RETURNS CHAR(50)
    ->  RETURN   (SELECT s_name FROM suppliers WHERE s_call= '48075');
    ->  //
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

變量的使用

  在存儲過程中使用DECLARE語句定義變量，語法格式為：

DECLARE var_name[,varname]... date_type [DEFAULT value];

定義名稱為myparam的變量，類型為INT類型，默認值為100

DECLARE  myparam  INT  DEFAULT 100;

  定義變量后，為變量賦值可以改變變量的默認信息，MySQL使用SET語句為變量賦值，語法格式為：

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

聲明3個變量，分別為var1、var2和var3，數據類型為INT，使用SET為變量賦值

DECLARE var1, var2, var3 INT;
SET var1 = 10, var2 = 20;
SET var3 = var1 + var2;

  MySQLhankeyishiyong SELECT...INTO為一個或多個變量賦值，語法為：

SELECT col_name[,...] INTO var_name[,...] table_expr;

聲明變量fruitname和fruitprice，通過SELECT ... INTO語句查詢指定記錄并為變量賦值

DECLARE fruitname CHAR(50);
DECLARE fruitprice DECIMAL(8,2);

SELECT f_name,f_price INTO fruitname, fruitprice
FROM fruits WHERE f_id ='a1';

定義條件和處理程序

 特定條件需要特定處理。這些條件可以聯系到錯誤，以及子程序的一般流程控制。定義條件是事先定義程序執行過程中遇到的問題，處理程序定義了在遇到這些問題時應當采取的處理方式，并且保證存儲過程或函數在遇到警告或錯誤時能繼續執行。使用DECLARE關鍵字來定義條件和處理程序。定義條件的語法格式為：

DECLARE condition_name CONDITION FOR [condition_type]
 [condition_type]:
   SQLSTATE [VALUE] sqlstate_value | mysql_error_code

定義"ERROR 1148(42000)"錯誤，名稱為command_not_allowed

//方法一：使用sqlstate_value 
DECLARE  command_not_allowed CONDITION FOR SQLSTATE '42000';

//方法二：使用mysql_error_code 
DECLARE  command_not_allowed CONDITION  FOR  1148

  定義處理程序時，使用DECLARE語句的語法為：

DECLARE handler_action HANDLER FOR condition_value statement
    
handler_action:
    CONTINUE| EXIT| UNDO
    
condition_value:
    mysql_error_code
  | SQLSTATE [VALUE] sqlstate_value
  | condition_name
  | SQLWARNING
  | NOT FOUND
  | SQLEXCEPTION

定義處理程序的幾種方式

//方法一：捕獲sqlstate_value 
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info='NO_SUCH_TABLE';

//方法二：捕獲mysql_error_code
DECLARE CONTINUE HANDLER FOR 1146 SET @info=' NO_SUCH_TABLE ';

//方法三：先定義條件，然后調用
DECLARE  no_such_table  CONDITION  FOR  1146;
DECLARE CONTINUE HANDLER FOR NO_SUCH_TABLE SET @info=' NO_SUCH_TABLE ';

//方法四：使用SQLWARNING
DECLARE EXIT HANDLER FOR SQLWARNING SET @info='ERROR';

//方法五：使用NOT FOUND
DECLARE EXIT HANDLER FOR NOT FOUND SET @info=' NO_SUCH_TABLE ';

//方法六：使用SQLEXCEPTION
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info='ERROR';

定義條件和處理程序

mysql> CREATE TABLE test.t (s1 int,primary key (s1));
Query OK, 0 rows affected (0.05 sec)

mysql> DELIMITER //

mysql> CREATE PROCEDURE handlerdemo ()
    ->       BEGIN
    ->        DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1;
    ->        SET @x = 1;
    ->        INSERT INTO test.t VALUES (1);
    ->        SET @x = 2;
    ->        INSERT INTO test.t VALUES (1);
    ->        SET @x = 3;
    ->      END;
    ->      //
Query OK, 0 rows affected (0.00 sec)

mysql>  DELIMITER ;

mysql> CALL handlerdemo();         /*調用存儲過程*/
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> SELECT @x;                  /*查看調用過程結果*/
+------+
| @x   |
+------+
|    3 |
+------+
1 row in set (0.00 sec)

   '@var_name'表示用戶變量，使用SET語句為其賦值，用戶與連接有關，一個客戶端定義的變量不能被其他客戶端看到或使用。客戶端退出時，該客戶端連接的所有變量自動釋放。

光標的使用

 查詢語句可能返回多條記錄，如果數據非常大，需要在存儲過程和存儲函數中使用光標來逐條讀取查詢結果集中的記錄。光標必須在聲明處理程序之前被聲明，并且變量和條件還必須在聲明光標或處理程序之前聲明。MySQL中光標只能在存儲過程和函數中使用。

  MySQL中使用DECLARE關鍵字聲明光標，語法基本形式為：

DECLARE cursor_name CURSOR FOR select_statement

聲明名稱為cursor_fruit的光標

DECLARE cursor_fruit CURSOR FOR SELECT f_name, f_price FROM fruits ;

  打開光標的語法為：

OPEN cursor_name [光標名稱]

打開名稱為cursor_fruit的光標

OPEN cursor_fruit

  使用光標的語法為：

FETCH cursor_name INTO var_name [,var_name] ...[參數名稱]

使用名稱為cursor_fruit的光標。將查詢出來的數據存入fruit_name和fruit_price這兩個變量

FETCH  cursor_fruit INTO fruit_name, fruit_price ;

 關閉名稱為cursor_fruit的光標

CLOSE  cursor_fruit;

流程控制的使用

  流程控制語句用來控制條件語句的執行。MySQL終于來控制流程的于具有IF、CASE、LOOP、LEAVE、ITERATE、REPEAT和WHERE語句。

• IF

 IF語句包含多個條件判斷，根據判斷的結果為TRUE或FALSE執行相應的語句，語法格式為：

IF search_condition THEN statement_list
    [ELSEIF search_condition THEN statement_list] ...
    [ELSE statement_list]
END IF

IF語句的示例

IF val IS NULL
  THEN SELECT 'val is NULL';
  ELSE SELECT 'val is not NULL';
END IF;

• CASE

  CASE是另一個進行條件判斷的語句，該語句有兩種格式：

CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE

Or:
CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE

使用CASE流程控制語句第1種格式，判斷val值等于1、等于2，或者兩者都不等

CASE val
  WHEN 1 THEN SELECT 'val is 1';
  WHEN 2 THEN SELECT 'val is 2';
  ELSE SELECT 'val is not 1 or 2';
END CASE;
當val值為1時，輸出字符串“val is 1”；當val值為2時，輸出字符串“val is 2”；否則輸出字符
串“val is not 1 or 2”。

使用CASE流程控制語句的第2種格式，判斷val是否為空、小于0、大于0或者等于0

CASE
  WHEN val IS NULL THEN SELECT 'val is NULL';
  WHEN val < 0 THEN SELECT 'val is less than 0';
  WHEN val > 0 THEN SELECT 'val is greater than 0';
  ELSE SELECT 'val is 0';
END CASE;
當val值為空，輸出字符串“val is NULL”；當val值小于0時，輸出字符串“val is less than 0”；
當val值大于0時，輸出字符串“val is greater than 0”；否則輸出字符串“val is 0”。

• LOOP

  LOOP循環語句用來重復執行某些語句，與IF和CASE相比，LOOP只是創建一個循環的過程，并不進行條件判斷。LOOP內的語句一直重復只i系那個知道循環被退出，跳出循環過程使用LEAVE子句，LOOP語法格式為：

[begin_label:] LOOP
    statement_list
END LOOP [end_label]

使用LOOP語句進行循環操作，id值小于等于10之前，將重復執行循環過程

DECLARE id INT DEFAULT 0;
add_loop: LOOP  
SET id = id + 1;
  IF id >= 10 THEN  LEAVE add_loop;
  END IF;
END LOOP add_ loop;

• LEAVE

  LEAVE語句用來退出任何被標注的流程控制構造，其語法格式為：

LEAVE label

使用LEAVE語句退出循環

add_num: LOOP  
SET @count=@count+1;
IF @count=50 THEN LEAVE add_num ;
END LOOP add_num ;

• ITERATE

  ITERATE語句將執行順序轉到語句段開頭處，語法格式為：

ITERATE label

  ITERATE只可以出現在LOOP、REPEAT、WHERE語句內。

ITERATE語句示例

CREATE PROCEDURE doiterate()
BEGIN
DECLARE p1 INT DEFAULT 0;
my_loop: LOOP
  SET p1= p1 + 1;
  IF p1 < 10 THEN ITERATE my_loop;
  ELSEIF p1 > 20 THEN LEAVE my_loop;
  END IF;
  SELECT 'p1 is between 10 and 20';
END LOOP my_loop;
END

• REPEAT

  REPEAT語句創建一個帶條件判斷的循環過程，每次語句執行完畢之后，會對條件表達式進行判斷，如果為真，則結束循環，其語法格式為：

[begin_label:] REPEAT
    statement_list
UNTIL search_condition
END REPEAT [end_label]

REPEAT語句示例，id值小于等于10之前，將重復執行循環過程

DECLARE id INT DEFAULT 0;
REPEAT
SET id = id + 1;
UNTIL  id >= 10
END REPEAT;

• WHILE

  WHILE語句創建一個帶條件判斷的循環過程，與REPEAT不同，WHERE在執行語句時，先對指定表達式進行判斷，如果為真，則執行循環的語句，其語法格式為：

[begin_label:] WHILE search_condition DO
    statement_list
END WHILE [end_label]

WHILE語句示例，id值小于等于10之前，將重復執行循環過程

DECLARE i INT DEFAULT 0;
WHILE i < 10 DO
SET i = i + 1;
END WHILE;

9.2、調用存儲過程和函數

調用存儲過程

 存儲過程通過CALL語句進行調用，語法格式為：

CALL sp_name([parameter[,...]])

定義名為CountProc1的存儲過程，然后調用這個存儲過程

mysql> DELIMITER //
mysql> CREATE PROCEDURE CountProc1 (IN sid INT, OUT num INT)
    -> BEGIN
    -> SELECT COUNT(*) INTO num FROM fruits WHERE s_id = sid;
    -> END //
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> CALL CountProc1 (101, @num);
Query OK, 1 row affected (0.00 sec)

mysql> select @num;
+------+
| @num |
+------+
|    3 |
+------+
1 row in set (0.02 sec)

調用存儲函數

定義存儲函數CountProc2，然后調用這個函數

mysql> DELIMITER //
mysql> CREATE FUNCTION  CountProc2 (sid INT)
    -> RETURNS INT
    -> BEGIN
    -> RETURN (SELECT COUNT(*) FROM fruits WHERE s_id = sid);
    -> END //
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

mysql> SELECT CountProc2(101);
+--------------------+
| Countproc(101) |
+--------------------+
|             3 |
+-------------------+

9.3、查看存儲過程和函數

  MySQL存儲了存儲過程和函數的狀態信息，用戶可以使用SHOW STATUS語句或SHOW CREATE語句查看，也可以直接從系統information_schema數據庫中查看。

  使用SHOW STATUS語句可以查看存儲過程和函數狀態，基本語法為：

SHOW [PROCEDURE | FUNCTION] STATUS [LIKE 'pattern']

SHOW STATUS語句示例

mysql> SHOW PROCEDURE STATUS LIKE 'C%'\G
*************************** 1. row ***************************
                  Db: test
                Name: CountProc
                Type: PROCEDURE
             Definer: root@localhost
            Modified: 2017-08-04 11:32:08
             Created: 2017-08-04 11:32:08
       Security_type: DEFINER
             Comment: 
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

  使用SHOW CREATE語句可以查看存儲過程和函數狀態，基本語法為：

SHOW CREATE [PROCEDURE | FUNCTION] sp_name

SHOW CREATE語句示例

mysql> SHOW CREATE PROCEDURE test.CountProc \G        
*************************** 1. row ***************************
           Procedure: CountProc
            sql_mode: 
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `CountProc`(OUT param1 INT)
BEGIN
  SELECT COUNT(*) INTO param1 FROM fruits;
  END
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

  MySQL中存儲過程和函數的信息存儲在information_schema數據庫下的Routines表中，可以通過查詢該表的記錄來查詢存儲過程和函數，其基本語法為：

SELECT * FROM information_schema.Routines
  WHERE ROUTINE_NAME= 'sp_name';

從Routines表中查詢名稱為CountProc的存儲過程的信息

mysql> SELECT * FROM information_schema.Routines
    -> WHERE ROUTINE_NAME='CountProc'  AND  ROUTINE_TYPE = 'PROCEDURE' \G        
*************************** 1. row ***************************
           SPECIFIC_NAME: CountProc
         ROUTINE_CATALOG: def
          ROUTINE_SCHEMA: test
            ROUTINE_NAME: CountProc
            ROUTINE_TYPE: PROCEDURE
               DATA_TYPE: 
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
          DTD_IDENTIFIER: NULL
            ROUTINE_BODY: SQL
      ROUTINE_DEFINITION: BEGIN
  SELECT COUNT(*) INTO param1 FROM fruits;
  END
           EXTERNAL_NAME: NULL
       EXTERNAL_LANGUAGE: NULL
         PARAMETER_STYLE: SQL
        IS_DETERMINISTIC: NO
         SQL_DATA_ACCESS: CONTAINS SQL
                SQL_PATH: NULL
           SECURITY_TYPE: DEFINER
                 CREATED: 2017-08-04 11:32:08
            LAST_ALTERED: 2017-08-04 11:32:08
                SQL_MODE: 
         ROUTINE_COMMENT: 
                 DEFINER: root@localhost
    CHARACTER_SET_CLIENT: utf8
    COLLATION_CONNECTION: utf8_general_ci
      DATABASE_COLLATION: utf8_general_ci
1 row in set (0.00 sec)

9.4、修改存儲過程和函數

  使用ALTER語句可以修改存儲過程或函數的特性，基本語法為：

ALTER [PROCEDURE | FUNCTION ] sp_name [characteristic ...]

 修改存儲過程CountProc的定義。將讀寫權限改為MODIFIES SQL DATA，并指明調用者可以執行

mysql> ALTER  PROCEDURE  CountProc
    ->      MODIFIES SQL DATA  
    ->      SQL SECURITY INVOKER ;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT SPECIFIC_NAME,SQL_DATA_ACCESS,SECURITY_TYPE
    ->       FROM information_schema.Routines
    ->      WHERE ROUTINE_NAME='CountProc' AND ROUTINE_TYPE='PROCEDURE';
+---------------+-------------------+---------------+
| SPECIFIC_NAME | SQL_DATA_ACCESS   | SECURITY_TYPE |
+---------------+-------------------+---------------+
| CountProc     | MODIFIES SQL DATA | INVOKER       |
+---------------+-------------------+---------------+
1 row in set (0.00 sec)

9.5、刪除存儲過程和函數 

  刪除存儲過程和函數，可以使用DROP語句，其語法格式為：

DROP[PROCEDURE | FUNCTION ] [IF EXISTS] sp_name

刪除存儲過程和存儲函數

mysql> DROP PROCEDURE CountProc;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP FUNCTION CountProc;
Query OK, 0 rows affected (0.00 sec)