Mysql之存儲過程和函數

Mysql之存儲過程和函數

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

存儲程序分為：1、存儲過程 2、函數

使用Call語句來調用存儲過程，只能用輸出變量返回值。

一、創建存儲過程

語法：

create procedure sp_name(proc_parameter)
[characteristics……] routine_body
創建存儲函數名為sp_name，存儲過程的名為：proc_parameter

指定存儲參數列表為：

[IN | OUT | INOUT] param_name type

其中IN表示輸入參數，OUT表示輸出參數，INOUT表示即可輸入也可輸出

param_name表示參數名稱

type 表示參數類型，該類型可以是Mysql數據庫中的任意類型。

characteristics 指定存儲過程的特性，有以下取值：

LANGUAGE SQL：說明routine_body部分是由SQL語句組成，當前系統支持的語言為SQL，SQL是LANGUAGE特性的唯一值。

[NOT] DETERMINISTIC：指明存儲過程執行的結果是否正確。

    1. DETERMINISTIC表示結果是正確的。每次執行存儲過程時，相同輸入會得到相同的輸出。

    2. NOT DETERMINISTIC表示結果是不確定的，相同的輸入可能得到不同的輸出。如果沒有指定任意一個值，默認為NOT DETERMINISTIC。

{ CONTAINS SQL | NO SQL |REDAS SQL DATA | MODIFIES SQL DATA }:指明子程序使用SQL語句的限制。

    1. CONTAINS SQL：表示子程序包含SQL語句，但是不包含讀寫數據的語句。

    2. NO SQL：表示子程序不包含SQL語句。

    3. REDAS SQL DATA ：說明子程序包含數據的語句。

    4. MODIFIES SQL DATA：表明子程序包含寫數據的語句。默認為CONTAINS SQL。

SQL SECURITY { DEFINER | INVOKER}:指明誰有權限來執行。

    1. DEFINER表示只有定義者才能執行。

    2. INVOKER表示擁有權限的調用者可以執行。默認情況下，系統指定為DEFINER

COMMENT ‘string’：注釋信息，可以用來描述存儲過程或函數。

routine_body是SQL代碼的內容。通常用begin……end表示SQL代碼的開始和結束。

編寫存儲過程并不是簡單的事情，可能存儲過程中需要復雜的SQL語句，并且要創建存儲過程的權限；但是使用存儲過程將簡化操作，減少冗余的操作步驟，同時，還可以減少操作過程中的失誤、提高效率，因此存儲過程非常的有用，而且應該盡量學會使用。

例1：

mysql> delimiter //      # 定義SQL語句的結束符號為//，使用這條命令時，應該避免（‘\’）字符，因為反斜線是Mysql的轉意符。
mysql> create procedure p1()
  -> begin
  -> select  * from t;
  -> end //
mysql> delimiter ;
mysql> show procedure status \G        # 查看存儲過程信息
mysql> call p1    # 讀取這個存儲過程

例2：

mysql> delimiter //
mysql> create procedure p2(n int)
  -> begin
  -> select * from t where id = n;
  -> end //
mysql> delimiter ;
mysql> show procedure status \G
mysql> call p2(1)         # 需要帶入取值

例3：

mysql> create database db_proc;
mysql> use db_proc
mysql> CREATE TABLE `proc_test` (
  -> `id` tinyint(4) NOT NULL AUTO_INCREMENT,
  -> `username` varchar(20) NOT NULL,
  -> `password` varchar(20) NOT NULL,
  -> PRIMARY KEY (`id`)
  -> ) ENGINE=MyISAM AUTO_INCREMENT=50 DEFAULT CHARSET=utf8;

mysql> delimiter //
mysql> create procedure mytest(in name varchar(20),in pwd varchar(20))
  -> begin
  -> insert into proc_test(username,password) values(name,pwd);
  -> end //
mysql> delimiter ;
mysql> call mytest('lxq','password') ;
mysql> select * from proc_test;      # 驗證插入了數據

************************

mysql存儲過程的參數用在存儲過程的定義，共有三種參數類型,IN,OUT,INOUT

Create procedure|function([[IN |OUT |INOUT ] 參數名 數據類形...])

IN 輸入參數

表示該參數的值必須在調用存儲過程時指定，在存儲過程中修改該參數的值不能被返回，為默認值

OUT 輸出參數

該值可在存儲過程內部被改變，并可返回

INOUT 輸入輸出參數

調用時指定，并且可被改變和返回

IN參數例子：

mysql> DELIMITER //
mysql> CREATE PROCEDURE sp_demo_in_parameter(IN p_in INT)
    -> BEGIN
    -> SELECT p_in;     # 查詢輸入參數
    -> SET p_in=2;      # 修改
    -> select p_in;     #查看修改后的值
    -> END //
mysql> DELIMITER ;

執行結果:

mysql> set @p_in=1;
mysql> call sp_demo_in_parameter(@p_in);
mysql> select @p_in;
以上可以看出，p_in雖然在存儲過程中被修改，但并不影響@p_id的值

OUT參數例子

mysql> DELIMITER //
mysql> CREATE PROCEDURE sp_demo_out_parameter(OUT p_out INT)
    -> BEGIN
    -> SELECT p_out;    # 查看輸出參數
    -> SET p_out=2;     # 修改參數值
    -> SELECT p_out;    # 看看有否變化
    -> END //
mysql> DELIMITER ;

執行結果:

mysql> SET @p_out=1;
mysql> CALL sp_demo_out_parameter(@p_out);
mysql> SELECT @p_out;
p_out在存儲過程中被修改,直接影響@p_out的值

INOUT參數例子：

mysql> DELIMITER //
mysql> CREATE PROCEDURE sp_demo_inout_parameter(INOUT p_inout INT)
    -> BEGIN
    -> SELECT p_inout;
    -> SET p_inout=2;
    -> SELECT p_inout;
    -> END;
mysql> DELIMITER ;

執行結果：

set @p_inout=1;
call sp_demo_inout_parameter(@p_inout);
select @p_inout;

****************************

二、特定異常

在MySQL中，特定異常需要特定處理。這些異?？梢月撓档藉e誤，以及子程序中的一般流程控制。定義異常是事先定義程序執行過程中遇到的問題，異常處理定義了在遇到問題時對應當采取的處理方式，并且保證存儲過程或者函數在遇到錯誤時或者警告時能夠繼續執行。

1 異常定義

1.1 語法

DECLARE condition_name CONDITION FOR [condition_type];

1.2 說明

condition_name 參數表示異常的名稱；
condition_type 參數表示條件的類型，condition_type由SQLSTATE [VALUE] sqlstate_value|mysql_error_code組成：
    sqlstate_value和mysql_error_code都可以表示MySQL的錯誤；
    sqlstate_value為長度為5的字符串類型的錯誤代碼；
    mysql_error_code為數值類型錯誤代碼；

1.3 示例

定義“ERROR 1148(42000)”錯誤，名稱為command_not_allowed?？梢杂幸韵聝煞N方法：

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

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

2 自定義異常處理

2.1 異常處理語法

DECLARE handler_type HANDLER FOR condition_value [,...] sp_statement

2.2 參數說明

handler_type: CONTINUE|EXIT|UNDO
    handler_type為錯誤處理方式，參數為3個值之一；
    CONTINUE表示遇到錯誤不處理，繼續執行；
    EXIT表示遇到錯誤時馬上退出；
    UNDO表示遇到錯誤后撤回之前的操作，MySQL暫不支持回滾操作；
    
condition_value: SQLSTATE [VALUE] sqlstate_value| condition_name|SQLWARNING|NOT FOUND|SQLEXCEPTION|mysql_error_code
    condition_value表示錯誤類型；
    SQLSTATE [VALUE] sqlstate_value為包含5個字符的字符串錯誤值；
    condition_name表示DECLARE CONDITION定義的錯誤條件名稱；
    SQLWARNING匹配所有以01開頭的SQLSTATE錯誤代碼；
    NOT FOUND匹配所有以02開頭的SQLSTATE錯誤代碼；
    SQLEXCEPTION匹配所有沒有被SQLWARNING或NOT FOUND捕獲的SQLSTATE錯誤代碼；
    mysql_error_code匹配數值類型錯誤代碼；

2.3 異常捕獲方法

方法一：捕獲sqlstate_value異常
這種方法是捕獲sqlstate_value值。如果遇到sqlstate_value值為"42S02"，執行CONTINUE操作，并輸出"NO_SUCH_TABLE"信息
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info='NO_SUCH_TABLE';

方法二：捕獲mysql_error_code異常
這種方法是捕獲mysql_error_code值。如果遇到mysql_error_code值為1146，執行CONTINUE操作，并輸出"NO_SUCH_TABLE"信息；
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));
mysql> delimiter //
mysql> create procedure handlerdermo()
    -> 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 //
mysql> delimiter ;
mysql> call handlerdermo();
mysql> select @x;
mysql> select * from test.t;

三、函數

函數的作用：提高代碼的復用率

函數可以調用函數中的方法來實現某些功能

利用now()來實現空參數函數：

mysql> select now();
+---------------------+
| now()          |
+---------------------+
| 2018-08-16 18:19:09 |
+---------------------+

mysql> select date_format(now(),'%Y年%m月%d號 %H點%i分%s秒');
+------------------------------------------------------+
| date_format(now(),'%Y年%m月%d號 %H點%i分%s秒')       |
+------------------------------------------------------+
| 2018年08月16號 18點19分57秒                          |
+------------------------------------------------------+

------------------------------------------------------------------------------------

mysql> create function my_time() returns varchar(50)
   -> return   date_format(now(),'%Y-%m-%d %H-%i-%s');
Query OK, 0 rows affected (0.00 sec)

mysql> select my_time();
+---------------------+
| my_time()           |
+---------------------+
| 2018-08-16 18-22-10 |
+---------------------+

函數分為空參數函數和傳參函數

注意：函數必需要有返回值類型用returns描述

returns后面跟的是函數體

如果函數體只有單條就直接描述

函數體如果有多條 在returns后面 begin開始  函數體結束后要寫end結束

end之前一定要確定返回值

-----------------------------------------------------------------------------------------------

創建傳參函數：

mysql> CREATE FUNCTION cont_AVG(num1 int,num2 int) RETURNS decimal(8,2)
    -> RETURN (num1+num2)/2;
Query OK, 0 rows affected (0.00 sec)

mysql> select cont_AVG(2,2);
+---------------+
| cont_AVG(2,2) |
+---------------+
|    2.00     |
+---------------+
1 row in set (0.00 sec)

mysql> select cont_AVG(3,2);
+---------------+
| cont_AVG(3,2) |
+---------------+
|     2.50     |
+---------------+

創建給stu表添加用戶的多函數體傳參函數：

mysql> desc stu;
+-------+-------------+------+-----+---------+-------+
| Field | Type      | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id   | int(11)    | NO   |    | NULL   |     |
| name  | varchar(10) | NO   |    | NULL   |     |
+-------+-------------+------+-----+---------+-------+
mysql> select * from stu;
Empty set (0.00 sec)

mysql> delimiter //
mysql> create function adduse(u_id int unsigned,u_name varchar(10))
    -> returns int unsigned
    -> begin
    -> insert stu values(u_id,u_name);
    -> return last_insert_id();
    -> end //
mysql> delimiter ;
mysql> select adduse(1,'zs');
+----------------+
| adduse(1,'zs') |
+----------------+
|      0      |
+----------------+
1 row in set (0.02 sec)

mysql> select adduse(2,'ls');
+----------------+
| adduse(2,'ls') |
+----------------+
|      0      |
+----------------+
1 row in set (0.01 sec)

mysql> select adduse(3,'ww');
+----------------+
| adduse(3,'ww') |
+----------------+
|      0      |
+----------------+
1 row in set (0.02 sec)

mysql> select * from stu;
+----+------+
| id | name |
+----+------+
| 1  | zs   |
| 2  | ls   |
| 3  | ww   |
+----+------+
3 rows in set (0.00 sec)