存儲過程新手指南

存儲過程(Stored Procedure)

一組可編程的函數(shù)，是為了完成特定功能的SQL語句集，經(jīng)編譯創(chuàng)建并保存在數(shù)據(jù)庫中，用戶可通過指定存儲過程的名字并給定參數(shù)(需要時)來調(diào)用執(zhí)行。

為什么要用存儲過程(優(yōu)點)？

對于一些復(fù)用性高或者業(yè)務(wù)復(fù)雜的一些操作，封裝到一個存儲過程中，避免了重復(fù)編寫SQL造成漏寫或錯寫操作，簡化了SQL的調(diào)用
批量處理：SQL + 循環(huán)，減少流量
數(shù)據(jù)遷移，數(shù)據(jù)備份
統(tǒng)一接口，保證數(shù)據(jù)安全(重點，尤其是銀行系統(tǒng))

相對于Oracle數(shù)據(jù)庫來說，Mysql的存儲過程功能相對較弱，運用的較少，在之前的文章中也有提到，Mysql是把存儲過程劃分為函數(shù)體，沒有Oracle那樣細分。

存儲過程的創(chuàng)建與調(diào)用

存儲過程就是有名字的一段代碼，用來完成特定功能的操作
創(chuàng)建的存儲過程保存在數(shù)據(jù)庫的數(shù)據(jù)字典中

創(chuàng)建存儲過程


CREATE

[DEFINER = { user | CURRENT_USER }]

PROCEDURE sp_name ([proc_parameter[,...]])

[characteristic ...] routine_body

proc_parameter:

[ IN | OUT | INOUT ] param_name type

characteristic:

COMMENT 'string'

| LANGUAGE SQL

| [NOT] DETERMINISTIC

| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }

| SQL SECURITY { DEFINER | INVOKER }

routine_body:

Valid SQL routine statement

[begin_label:] BEGIN

[statement_list]

……

END [end_label]

創(chuàng)建數(shù)據(jù)庫，用于示例操作


CREATE DATABASE huafeng_db;

use huafeng_db;

DROP TABLE IF EXISTS `huafeng_db`.`t_scores`;

DROP TABLE IF EXISTS `huafeng_db`.`t_students`;

DROP TABLE IF EXISTS `huafeng_db`.`t_class`;

CREATE TABLE `huafeng_db`.`t_class` (

  `class_id` int(11) NOT NULL,

  `class_name` varchar(32) CHARACTER SET utf8 DEFAULT NULL,

  PRIMARY KEY (`class_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `huafeng_db`.`t_class` (`class_id`, `class_name`) VALUES ('1', '一年級');

INSERT INTO `huafeng_db`.`t_class` (`class_id`, `class_name`) VALUES ('2', '二年級');

INSERT INTO `huafeng_db`.`t_class` (`class_id`, `class_name`) VALUES ('3', '三年級');

INSERT INTO `huafeng_db`.`t_class` (`class_id`, `class_name`) VALUES ('4', '四年級');

INSERT INTO `huafeng_db`.`t_class` (`class_id`, `class_name`) VALUES ('5', '五年級');

INSERT INTO `huafeng_db`.`t_class` (`class_id`, `class_name`) VALUES ('6', '六年級');

CREATE TABLE `t_students` (

  `student_id` int(11) NOT NULL AUTO_INCREMENT,

  `student_name` varchar(32) NOT NULL,

  `sex` int(1) DEFAULT NULL,

  `seq_no` int(11) DEFAULT NULL,

  `class_id` int(11) NOT NULL,

  PRIMARY KEY (`student_id`),

  KEY `class_id` (`class_id`),

  CONSTRAINT `t_students_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `t_class` (`class_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `huafeng_db`.`t_students`(`student_name`,`sex`,`seq_no`,`class_id`) VALUES('小紅',0,1,'1');

INSERT INTO `huafeng_db`.`t_students`(`student_name`,`sex`,`seq_no`,`class_id`) VALUES('小青',0,2,'2');

INSERT INTO `huafeng_db`.`t_students`(`student_name`,`sex`,`seq_no`,`class_id`) VALUES('小明',1,3,'3');

INSERT INTO `huafeng_db`.`t_students`(`student_name`,`sex`,`seq_no`,`class_id`) VALUES('小蘭',0,4,'4');

INSERT INTO `huafeng_db`.`t_students`(`student_name`,`sex`,`seq_no`,`class_id`) VALUES('小米',1,5,'5');

INSERT INTO `huafeng_db`.`t_students`(`student_name`,`sex`,`seq_no`,`class_id`) VALUES('小白',1,6,'6');

CREATE TABLE `huafeng_db`.`t_scores` (

  `score_id` int(11) NOT NULL AUTO_INCREMENT,

  `course_name` varchar(64) DEFAULT NULL,

  `score` double(3,2) DEFAULT NULL,

  `student_id` int(11) DEFAULT NULL,

  PRIMARY KEY (`score_id`),

  KEY `student_id` (`student_id`),

  CONSTRAINT `t_scores_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `t_students` (`student_id`)

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

INSERT INTO `t_scores` (`score_id`, `course_name`, `score`, `student_id`) VALUES ('1', '語文', '90', '1');

INSERT INTO `t_scores` (`score_id`, `course_name`, `score`, `student_id`) VALUES ('2', '數(shù)學(xué)', '97', '1');

INSERT INTO `t_scores` (`score_id`, `course_name`, `score`, `student_id`) VALUES ('3', '英語', '95', '1');

INSERT INTO `t_scores` (`score_id`, `course_name`, `score`, `student_id`) VALUES ('4', '語文', '92', '2');

INSERT INTO `t_scores` (`score_id`, `course_name`, `score`, `student_id`) VALUES ('5', '數(shù)學(xué)', '100', '2');

INSERT INTO `t_scores` (`score_id`, `course_name`, `score`, `student_id`) VALUES ('6', '英語', '98', '2');

示例：創(chuàng)建一個存儲過程，根據(jù)學(xué)生編號刪除學(xué)生信息


DELIMITER $$ #將語句的結(jié)束符號從分號;臨時改為兩個$$(可以是自定義)

CREATE PROCEDURE p_delstudent_by_no(IN seqNo INT)

BEGIN

DELETE FROM t_students WHERE seq_no = seqNo;

END $$

DELIMITER ; #將語句的結(jié)束符號恢復(fù)為分號

注: 在定義過程中，使用DELIMITER $$ 命令將語句的結(jié)束符號從分號 ; 臨時改為兩個$$，使得過程體中使用的分號被直接傳遞到服務(wù)器，而不會被客戶端（如mysql）解釋。

調(diào)用存儲過程：call sp_name[(傳參)]


mysql> select *from t_students;

+------------+--------------+------+--------+----------+

| student_id | student_name | sex  | seq_no | class_id |

+------------+--------------+------+--------+----------+

|          1 | 小紅        |    0 |      1 |        1 |

|          2 | 小青        |    0 |      2 |        2 |

|          3 | 小明        |    1 |      3 |        3 |

|          4 | 小蘭        |    0 |      4 |        4 |

|          5 | 小米        |    1 |      5 |        5 |

|          6 | 小白        |    1 |      6 |        6 |

+------------+--------------+------+--------+----------+

6 rows in set (0.00 sec)

mysql> call p_delstudent_by_no(2);

Query OK, 1 row affected (0.01 sec)

mysql> select *from t_students;

+------------+--------------+------+--------+----------+

| student_id | student_name | sex  | seq_no | class_id |

+------------+--------------+------+--------+----------+

|          1 | 小紅        |    0 |      1 |        1 |

|          3 | 小明        |    1 |      3 |        3 |

|          4 | 小蘭        |    0 |      4 |        4 |

|          5 | 小米        |    1 |      5 |        5 |

|          6 | 小白        |    1 |      6 |        6 |

+------------+--------------+------+--------+----------+

5 rows in set (0.00 sec)

注: 在存儲過程中設(shè)置了需要傳入的參數(shù)seqNo，在調(diào)用存儲過程時，把2傳參到seqNo，然后執(zhí)行存儲過程的SQL操作。

存儲過程體

存儲過程體包含了在過程調(diào)用時必須執(zhí)行的語句，例如：DML、DDL語句，IF-THEN-ELSE和WHILE-DO語句、聲明變量的DECLARE語句等
過程體格式：以BEGIN開始，以END結(jié)束(可嵌套)


BEGIN

BEGIN

BEGIN

#業(yè)務(wù)操作

END

END

END

注：每個嵌套塊及其中的每條語句，必須以分號結(jié)束，表示過程體結(jié)束的BEGIN-END塊(又叫做復(fù)合語句compound statement)，則不需要分號。

標(biāo)簽化


[begin_label:] BEGIN

[statement_list]

END [end_label]

給每個過程體命名，以增強代碼的可讀性，示例：


label1:BEGIN

label2:BEGIN

label3:BEGIN

#業(yè)務(wù)操作

END label3;

END label2;

END label1

存儲過程參數(shù) (proc_parameter)

在存儲過程創(chuàng)建的時候，參數(shù)可有0到多個，參數(shù)的屬性可分為以下3種：

IN 輸入?yún)?shù)：表示調(diào)用者向存儲過程傳入值（傳入值可以是字面量或變量）
OUT 輸出參數(shù)：表示過程向調(diào)用者傳出值(可以返回多個值)（傳出值只能是變量）
INOUT輸入輸出參數(shù)：既表示調(diào)用者向過程傳入值，又表示過程向調(diào)用者傳出值（值只能是變量）

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


mysql> DELIMITER $$

mysql> CREATE PROCEDURE p_prinlt(IN num INT)

->  BEGIN

->    SELECT num;

->  END $$

Query OK, 0 rows affected (0.01 sec)

mysql>  DELIMITER ;

mysql> call p_prinlt(2);

+------+

| num  |

+------+

|    2 |

+------+

OUT 輸出參數(shù)


mysql> delimiter $$

mysql> create procedure out_param(out result int)

-> begin

-> set result = 2;

-> select result;

-> end $$

Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> set @result = 1;

Query OK, 0 rows affected (0.00 sec)

mysql> call out_param(@result);

+--------+

| result |

+--------+

|      2 |

+--------+

INOUT 輸入輸出參數(shù)


mysql> delimiter $$

mysql> create procedure inout_param(inout p_inout int)

->  begin

->    select p_inout;

->    set p_inout=2;

->    select p_inout;

->  end

-> $$

mysql> delimiter ;

mysql> set @p_inout=1;

mysql> call inout_param(@p_inout);

+---------+

| p_inout |

+---------+

|      1 |

+---------+

+---------+

| p_inout |

+---------+

|      2 |

+---------+

mysql> select @p_inout;

+----------+

| @p_inout |

+----------+

|        2 |

+----------+

注：在設(shè)計傳參時應(yīng)注意確保參數(shù)名字不能與數(shù)據(jù)庫表列名相同，否則在過程體中，參數(shù)名被當(dāng)做列名來處理

作為一個追求完美的人，我們都應(yīng)該做到專詞專用，傳入?yún)?shù)使用 IN ，傳出參數(shù)使用 OUT ,盡量不用INOUT

高手進階

前面我們已經(jīng)初步了解了存儲過程的創(chuàng)建與調(diào)用，但是我們編寫存儲過程的目的是為了解決一些復(fù)用性高或者業(yè)務(wù)復(fù)雜的一些操作；

統(tǒng)一接口，保證數(shù)據(jù)安全等。所以需要使用事務(wù)管理 DML、DDL語句，IF-THEN-ELSE和WHILE-DO語句、聲明變量的DECLARE語句等

全局變量

全局變量可用于初始化一些數(shù)據(jù)，作為公共資源，使用方法如下：


mysql> set @num = 2;

mysql> DELIMITER $$

mysql> CREATE PROCEDURE p_prinlt(IN num INT)

->  BEGIN

->    SELECT num;

->  END $$

Query OK, 0 rows affected (0.01 sec)

mysql>  DELIMITER ;

mysql> call p_prinlt(@num);

+------+

| num  |

+------+

|    2 |

+------+

注：設(shè)置全局參數(shù)名字一定要帶 @ 前綴

聲明語句(DECLARE)

對于一些復(fù)雜的業(yè)務(wù)操作，過程體需要經(jīng)常用到相同的變量時，則需要聲明一個或多個局部變量以滿足業(yè)務(wù)需求，使用方法如下：


DELIMITER $$

CREATE PROCEDURE p_get_classId(IN seqNo int)

BEGIN

    DECLARE classId INT;

    DECLARE result VARCHAR(256) CHARACTER set utf8;#解決中文亂碼問題

set classId = (SELECT class_id FROM t_students WHERE seq_no = seqNo);

SELECT classId;

END $$

事務(wù)機制(TRANSACTION)

為了保證數(shù)據(jù)的安全，以及接口的統(tǒng)一，防止臟數(shù)據(jù)產(chǎn)生，這樣的存儲過程都應(yīng)該使用事務(wù)。

銀行轉(zhuǎn)賬就是其中的經(jīng)典例子，需要雙方都成功才會提交事務(wù)，否則回滾(ROLLBACK)

下面我們用之前創(chuàng)建好的數(shù)據(jù)庫作為例子，編寫一個根據(jù)學(xué)生編號刪除學(xué)生的學(xué)習(xí)成績，并把對應(yīng)的學(xué)生的信息刪除，這樣的一個存儲過程。方法如下：


DELIMITER $$

CREATE PROCEDURE p_del_studentInfo_bySeqNo(IN seqNo INT)

BEGIN

    DECLARE studentId INT;

START TRANSACTION;#啟動事務(wù)

set studentId = (SELECT student_id FROM t_students WHERE seq_no = seqNo);

DELETE FROM t_scores WHERE student_id = studentId;

DELETE FROM t_students WHERE student_id = studentId;

COMMIT ;#提交事務(wù)

END $$

DELIMITER ;

條件判斷語句

在存儲過程中，條件判斷語句是必不可少的，經(jīng)常使用的有IF-THEN-ELSE和WHILE-DO語句，條件語句可以減少很多數(shù)據(jù)庫不必要的內(nèi)存開銷，

現(xiàn)在我們來優(yōu)化一下上面寫的刪除學(xué)生信息的存儲過程，代碼如下：


DROP PROCEDURE IF EXISTS `p_del_studentInfo_bySeqNo`;

DELIMITER $$

CREATE PROCEDURE p_del_studentInfo_bySeqNo(IN seqNo INT)

BEGIN

    DECLARE studentId INT;

START TRANSACTION;#啟動事務(wù)

set studentId = (SELECT student_id FROM t_students WHERE seq_no = seqNo);

IF studentId >0 THEN

DELETE FROM t_scores WHERE student_id = studentId;

DELETE FROM t_students WHERE student_id = studentId;

COMMIT ;#提交事務(wù)

ELSE

ROLLBACK;#回滾

END IF;

END $$

DELIMITER ;

注： IF條件判斷語句一定是以 “END IF; ”結(jié)束的，中間可以有多層條件判斷。格式：IF 條件體 THEN 執(zhí)行業(yè)務(wù)結(jié)果 ELSE 執(zhí)行業(yè)務(wù)結(jié)果 END IF

異常處理(SQLEXCEPTION)

每個存儲過程都應(yīng)該有報錯處理的能力，我們還是使用上面的存儲過程進行優(yōu)化，使用方法如下：


DROP PROCEDURE IF EXISTS `p_del_studentInfo_bySeqNo`;

DELIMITER $$

CREATE PROCEDURE p_del_studentInfo_bySeqNo(IN seqNo INT)

BEGIN

    DECLARE studentId INT;

    DECLARE e_code INTDEFAULT 0;#初始化報錯碼為0

    DECLARE result VARCHAR(256) CHARACTER set utf8;#初始化返回結(jié)果，解決中文亂碼問題

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET e_code=1;#遇到錯誤后繼續(xù)執(zhí)行;(需要返回執(zhí)行結(jié)果時用這個)

START TRANSACTION;#啟動事務(wù)

set studentId = (SELECT student_id FROM t_students WHERE seq_no = seqNo);

IF studentId >0 THEN

DELETE FROM t_scores WHERE student_id = studentId;

DELETE FROM t_students WHERE student_id = studentId;

ELSE

SET e_code = 1;

SET result = '該學(xué)生不存在！';

END IF;

IF e_code=1 THEN

ROLLBACK;  #回滾

ELSE

COMMIT;

SET result = '該學(xué)生已被刪除成功';

END IF;

SELECT result;

END $$

DELIMITER ;

定義報錯代碼變量名并設(shè)置初始化值為0 DECLARE e_code INT DEFAULT 0;
定義化返回結(jié)果變量名并設(shè)置編碼為utf-8 DECLARE result VARCHAR(256) CHARACTER set utf8;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET e_code=1; 遇到錯誤后繼續(xù)執(zhí)行;(需要返回執(zhí)行結(jié)果時用這個)
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET e_code=1;遇到錯誤后退出當(dāng)前塊;

聲明該存儲過程的使用對象(SQL SECURITY INVOKER)

在創(chuàng)建存儲過程的時候，默認是當(dāng)前用戶可使用所有權(quán)限，其他用戶不能使用或者不能執(zhí)行存儲過程中的DML語句，為了使其他用戶也能使用該存儲過程

則需要在創(chuàng)建的時候聲明一下，使用方法如下：


DROP PROCEDURE IF EXISTS `p_del_studentInfo_bySeqNo`;

DELIMITER $$

CREATE PROCEDURE p_del_studentInfo_bySeqNo(IN seqNo INT)

SQL SECURITY INVOKER  #允許其他用戶運行

BEGIN

    DECLARE studentId INT;

    DECLARE e_code INTDEFAULT 0;#初始化報錯碼為0

    DECLARE result VARCHAR(256) CHARACTER set utf8;#初始化返回結(jié)果，解決中文亂碼問題

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET e_code=1;#遇到錯誤后繼續(xù)執(zhí)行;(需要返回執(zhí)行結(jié)果時用這個)

START TRANSACTION;#啟動事務(wù)

set studentId = (SELECT student_id FROM t_students WHERE seq_no = seqNo);

IF studentId >0 THEN

DELETE FROM t_scores WHERE student_id = studentId;

DELETE FROM t_students WHERE student_id = studentId;

ELSE

SET e_code = 1;

SET result = '該學(xué)生不存在！';

END IF;

IF e_code=1 THEN

ROLLBACK;  #回滾

ELSE

COMMIT;

SET result = '該學(xué)生已被刪除成功';

END IF;

SELECT result;

END $$

DELIMITER ;

拓展

習(xí)慣性建議

默認情況下，存儲過程和默認數(shù)據(jù)庫相關(guān)聯(lián)，如果想指定存儲過程創(chuàng)建在某個特定的數(shù)據(jù)庫下，那么在過程名前面加數(shù)據(jù)庫名做前綴；
代碼盡量簡潔，有序，該縮進的地方使用縮進
過程體有必要的時候盡量采用標(biāo)簽
存儲過程傳參數(shù)時盡量使用對應(yīng)的名詞，盡量不用INOUT
每次創(chuàng)建表或者函數(shù)和存儲過程的時候盡量先判斷是否存在再刪除以免造成報錯，采用 DROP … IF EXISTS …格式

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九欧美,1769亚洲,黄色成人av

淺談mysql數(shù)據(jù)庫技術(shù)，輕松玩轉(zhuǎn)存儲過程

淺談mysql數(shù)據(jù)庫技術(shù)，輕松玩轉(zhuǎn)存儲過程

存儲過程新手指南

存儲過程(Stored Procedure)

為什么要用存儲過程(優(yōu)點)？

存儲過程的創(chuàng)建與調(diào)用

創(chuàng)建存儲過程

示例：創(chuàng)建一個存儲過程，根據(jù)學(xué)生編號刪除學(xué)生信息

調(diào)用存儲過程：call sp_name[(傳參)]

存儲過程體

標(biāo)簽化

存儲過程參數(shù) (proc_parameter)

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

OUT 輸出參數(shù)

INOUT 輸入輸出參數(shù)

高手進階

全局變量

聲明語句(DECLARE)

事務(wù)機制(TRANSACTION)

條件判斷語句

異常處理(SQLEXCEPTION)

聲明該存儲過程的使用對象(SQL SECURITY INVOKER)

拓展

習(xí)慣性建議

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九 欧美,1769亚洲,黄色成人av

淺談mysql數(shù)據(jù)庫技術(shù)，輕松玩轉(zhuǎn)存儲過程

存儲過程新手指南

存儲過程(Stored Procedure)

為什么要用存儲過程(優(yōu)點)？

存儲過程的創(chuàng)建與調(diào)用

創(chuàng)建存儲過程

示例：創(chuàng)建一個存儲過程，根據(jù)學(xué)生編號刪除學(xué)生信息

調(diào)用存儲過程：call sp_name[(傳參)]

存儲過程體

標(biāo)簽化

存儲過程參數(shù) (proc_parameter)

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

OUT 輸出參數(shù)

INOUT 輸入輸出參數(shù)

高手進階

全局變量

聲明語句(DECLARE)

事務(wù)機制(TRANSACTION)

條件判斷語句

異常處理(SQLEXCEPTION)

聲明該存儲過程的使用對象(SQL SECURITY INVOKER)

拓展

習(xí)慣性建議

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九欧美,1769亚洲,黄色成人av

淺談mysql數(shù)據(jù)庫技術(shù)，輕松玩轉(zhuǎn)存儲過程

為什么要用存儲過程(優(yōu)點)？

示例：創(chuàng)建一個存儲過程，根據(jù)學(xué)生編號刪除學(xué)生信息