MySQL之存儲(chǔ)過(guò)程和函數(shù)

為橋西等地區(qū)用戶提供了全套網(wǎng)頁(yè)設(shè)計(jì)制作服務(wù)，及橋西網(wǎng)站建設(shè)行業(yè)解決方案。主營(yíng)業(yè)務(wù)為成都做網(wǎng)站、成都網(wǎng)站建設(shè)、成都外貿(mào)網(wǎng)站建設(shè)、橋西網(wǎng)站設(shè)計(jì)，以傳統(tǒng)方式定制建設(shè)網(wǎng)站，并提供域名空間備案等一條龍服務(wù)，秉承以專業(yè)、用心的態(tài)度為用戶提供真誠(chéng)的服務(wù)。我們深信只要達(dá)到每一位用戶的要求，就會(huì)得到認(rèn)可，從而選擇與我們長(zhǎng)期合作。這樣，我們也可以走得更遠(yuǎn)！

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

存儲(chǔ)程序分為：1、存儲(chǔ)過(guò)程 2、函數(shù)

使用Call語(yǔ)句來(lái)調(diào)用存儲(chǔ)過(guò)程，只能用輸出變量返回值。

一、創(chuàng)建存儲(chǔ)過(guò)程

語(yǔ)法：

create procedure sp_name(proc_parameter)
[characteristics……] routine_body
創(chuàng)建存儲(chǔ)函數(shù)名為sp_name，存儲(chǔ)過(guò)程的名為：proc_parameter

指定存儲(chǔ)參數(shù)列表為：

[IN | OUT | INOUT] param_name type

其中IN表示輸入?yún)?shù)，OUT表示輸出參數(shù)，INOUT表示即可輸入也可輸出

param_name表示參數(shù)名稱

type 表示參數(shù)類型，該類型可以是Mysql數(shù)據(jù)庫(kù)中的任意類型。

characteristics 指定存儲(chǔ)過(guò)程的特性，有以下取值：

LANGUAGE SQL：說(shuō)明routine_body部分是由SQL語(yǔ)句組成，當(dāng)前系統(tǒng)支持的語(yǔ)言為SQL，SQL是LANGUAGE特性的唯一值。

[NOT] DETERMINISTIC：指明存儲(chǔ)過(guò)程執(zhí)行的結(jié)果是否正確。

    1. DETERMINISTIC表示結(jié)果是正確的。每次執(zhí)行存儲(chǔ)過(guò)程時(shí)，相同輸入會(huì)得到相同的輸出。

    2. NOT DETERMINISTIC表示結(jié)果是不確定的，相同的輸入可能得到不同的輸出。如果沒(méi)有指定任意一個(gè)值，默認(rèn)為NOT DETERMINISTIC。

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

    1. CONTAINS SQL：表示子程序包含SQL語(yǔ)句，但是不包含讀寫數(shù)據(jù)的語(yǔ)句。

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

    3. REDAS SQL DATA ：說(shuō)明子程序包含數(shù)據(jù)的語(yǔ)句。

    4. MODIFIES SQL DATA：表明子程序包含寫數(shù)據(jù)的語(yǔ)句。默認(rèn)為CONTAINS SQL。

SQL SECURITY { DEFINER | INVOKER}:指明誰(shuí)有權(quán)限來(lái)執(zhí)行。

    1. DEFINER表示只有定義者才能執(zhí)行。

    2. INVOKER表示擁有權(quán)限的調(diào)用者可以執(zhí)行。默認(rèn)情況下，系統(tǒng)指定為DEFINER

COMMENT ‘string’：注釋信息，可以用來(lái)描述存儲(chǔ)過(guò)程或函數(shù)。

routine_body是SQL代碼的內(nèi)容。通常用begin……end表示SQL代碼的開(kāi)始和結(jié)束。

編寫存儲(chǔ)過(guò)程并不是簡(jiǎn)單的事情，可能存儲(chǔ)過(guò)程中需要復(fù)雜的SQL語(yǔ)句，并且要?jiǎng)?chuàng)建存儲(chǔ)過(guò)程的權(quán)限；但是使用存儲(chǔ)過(guò)程將簡(jiǎn)化操作，減少冗余的操作步驟，同時(shí)，還可以減少操作過(guò)程中的失誤、提高效率，因此存儲(chǔ)過(guò)程非常的有用，而且應(yīng)該盡量學(xué)會(huì)使用。

例1：

mysql> delimiter //      # 定義SQL語(yǔ)句的結(jié)束符號(hào)為//，使用這條命令時(shí)，應(yīng)該避免（‘\’）字符，因?yàn)榉葱本€是Mysql的轉(zhuǎn)意符。
mysql> create procedure p1()
  -> begin
  -> select  * from t;
  -> end //
mysql> delimiter ;
mysql> show procedure status \G        # 查看存儲(chǔ)過(guò)程信息
mysql> call p1    # 讀取這個(gè)存儲(chǔ)過(guò)程

例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;      # 驗(yàn)證插入了數(shù)據(jù)

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

mysql存儲(chǔ)過(guò)程的參數(shù)用在存儲(chǔ)過(guò)程的定義，共有三種參數(shù)類型,IN,OUT,INOUT

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

IN 輸入?yún)?shù)

表示該參數(shù)的值必須在調(diào)用存儲(chǔ)過(guò)程時(shí)指定，在存儲(chǔ)過(guò)程中修改該參數(shù)的值不能被返回，為默認(rèn)值

OUT 輸出參數(shù)

該值可在存儲(chǔ)過(guò)程內(nèi)部被改變，并可返回

INOUT 輸入輸出參數(shù)

調(diào)用時(shí)指定，并且可被改變和返回

IN參數(shù)例子：

mysql> DELIMITER //
mysql> CREATE PROCEDURE sp_demo_in_parameter(IN p_in INT)
    -> BEGIN
    -> SELECT p_in;     # 查詢輸入?yún)?shù)
    -> SET p_in=2;      # 修改
    -> select p_in;     #查看修改后的值
    -> END //
mysql> DELIMITER ;

執(zhí)行結(jié)果:

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

OUT參數(shù)例子

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

執(zhí)行結(jié)果:

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

INOUT參數(shù)例子：

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 ;

執(zhí)行結(jié)果：

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

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

二、特定異常

在MySQL中，特定異常需要特定處理。這些異常可以聯(lián)系到錯(cuò)誤，以及子程序中的一般流程控制。定義異常是事先定義程序執(zhí)行過(guò)程中遇到的問(wèn)題，異常處理定義了在遇到問(wèn)題時(shí)對(duì)應(yīng)當(dāng)采取的處理方式，并且保證存儲(chǔ)過(guò)程或者函數(shù)在遇到錯(cuò)誤時(shí)或者警告時(shí)能夠繼續(xù)執(zhí)行。

1 異常定義

1.1 語(yǔ)法

DECLARE condition_name CONDITION FOR [condition_type];

1.2 說(shuō)明

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

1.3 示例

定義“ERROR 1148(42000)”錯(cuò)誤，名稱為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 異常處理語(yǔ)法

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

2.2 參數(shù)說(shuō)明

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

2.3 異常捕獲方法

方法一：捕獲sqlstate_value異常
這種方法是捕獲sqlstate_value值。如果遇到sqlstate_value值為"42S02"，執(zhí)行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，執(zhí)行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;

三、函數(shù)

函數(shù)的作用：提高代碼的復(fù)用率

函數(shù)可以調(diào)用函數(shù)中的方法來(lái)實(shí)現(xiàn)某些功能

利用now()來(lái)實(shí)現(xiàn)空參數(shù)函數(shù)：

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

mysql> select date_format(now(),'%Y年%m月%d號(hào) %H點(diǎn)%i分%s秒');
+------------------------------------------------------+
| date_format(now(),'%Y年%m月%d號(hào) %H點(diǎn)%i分%s秒')       |
+------------------------------------------------------+
| 2018年08月16號(hào) 18點(diǎn)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 |
+---------------------+

函數(shù)分為空參數(shù)函數(shù)和傳參函數(shù)

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

returns后面跟的是函數(shù)體

如果函數(shù)體只有單條就直接描述

函數(shù)體如果有多條 在returns后面 begin開(kāi)始  函數(shù)體結(jié)束后要寫end結(jié)束

end之前一定要確定返回值

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

創(chuàng)建傳參函數(shù)：

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     |
+---------------+

創(chuàng)建給stu表添加用戶的多函數(shù)體傳參函數(shù)：

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)

本文題目：Mysql之存儲(chǔ)過(guò)程和函數(shù)
鏈接分享：http://www.rwnh.cn/article30/ghcopo.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供小程序開(kāi)發(fā)、標(biāo)簽優(yōu)化、網(wǎng)站內(nèi)鏈、手機(jī)網(wǎng)站建設(shè)、企業(yè)網(wǎng)站制作、品牌網(wǎng)站設(shè)計(jì)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)