淺談mysql資料庫技術，輕鬆玩轉儲存過程

weixin_34221276發表於2018-04-02
MySql資料庫儲存過程

儲存過程新手指南

儲存過程(Stored Procedure)

一組可程式設計的函式，是為了完成特定功能的SQL語句集，經編譯建立並儲存在資料庫中，使用者可通過指定儲存過程的名字並給定引數(需要時)來呼叫執行。

為什麼要用儲存過程(優點)？

  • 對於一些複用性高或者業務複雜的一些操作，封裝到一個儲存過程中，避免了重複編寫SQL造成漏寫或錯寫操作，簡化了SQL的呼叫

  • 批量處理：SQL + 迴圈，減少流量

  • 資料遷移，資料備份

  • 統一介面，保證資料安全(重點，尤其是銀行系統)

相對於Oracle資料庫來說，Mysql的儲存過程功能相對較弱，運用的較少，在之前的文章中也有提到，Mysql是把儲存過程劃分為函式體，沒有Oracle那樣細分。

儲存過程的建立與呼叫

  • 儲存過程就是有名字的一段程式碼，用來完成特定功能的操作

  • 建立的儲存過程儲存在資料庫的資料字典中

建立儲存過程


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]
  • 建立資料庫，用於示例操作

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', '數學', '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', '數學', '100', '2');

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

示例：建立一個儲存過程，根據學生編號刪除學生資訊


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

CREATE PROCEDURE p_delstudent_by_no(IN seqNo INT)

BEGIN

DELETE FROM t_students WHERE seq_no = seqNo;

END $$

DELIMITER ; #將語句的結束符號恢復為分號

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

呼叫儲存過程：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)

注: 在儲存過程中設定了需要傳入的引數seqNo，在呼叫儲存過程時，把2傳參到seqNo，然後執行儲存過程的SQL操作。

儲存過程體

  • 儲存過程體包含了在過程呼叫時必須執行的語句，例如：DML、DDL語句，IF-THEN-ELSE和WHILE-DO語句、宣告變數的DECLARE語句等

  • 過程體格式：以BEGIN開始，以END結束(可巢狀)


BEGIN

BEGIN

BEGIN

#業務操作

END

END

END

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

標籤化


[begin_label:] BEGIN

[statement_list]

END [end_label]
  • 給每個過程體命名，以增強程式碼的可讀性，示例：

label1:BEGIN

label2:BEGIN

label3:BEGIN

#業務操作

END label3;

END label2;

END label1

儲存過程引數 (proc_parameter)

在儲存過程建立的時候，引數可有0到多個，引數的屬性可分為以下3種：

  • IN 輸入引數：表示呼叫者向儲存過程傳入值（傳入值可以是字面量或變數）

  • OUT 輸出引數：表示過程向呼叫者傳出值(可以返回多個值)（傳出值只能是變數）

  • INOUT輸入輸出引數：既表示呼叫者向過程傳入值，又表示過程向呼叫者傳出值（值只能是變數）

IN 輸入引數


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 輸出引數


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 輸入輸出引數


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 |

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

注： 在設計傳參時應注意確保引數名字不能與資料庫表列名相同，否則在過程體中，引數名被當做列名來處理

  • 作為一個追求完美的人，我們都應該做到專詞專用，傳入引數使用 IN ，傳出引數使用 OUT ,儘量不用INOUT

高手進階

前面我們已經初步瞭解了儲存過程的建立與呼叫，但是我們編寫儲存過程的目的是為了解決一些複用性高或者業務複雜的一些操作；

統一介面，保證資料安全等。所以需要使用事務管理 DML、DDL語句，IF-THEN-ELSE和WHILE-DO語句、宣告變數的DECLARE語句等

全域性變數

全域性變數可用於初始化一些資料，作為公共資源，使用方法如下：


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 |

+------+

注： 設定全域性引數名字一定要帶 @ 字首

宣告語句(DECLARE)

對於一些複雜的業務操作，過程體需要經常用到相同的變數時，則需要宣告一個或多個區域性變數以滿足業務需求，使用方法如下：


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

事務機制(TRANSACTION)

為了保證資料的安全，以及介面的統一，防止髒資料產生，這樣的儲存過程都應該使用事務。

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

下面我們用之前建立好的資料庫作為例子，編寫一個根據學生編號刪除學生的學習成績，並把對應的學生的資訊刪除，這樣的一個儲存過程。方法如下：


DELIMITER $$

CREATE PROCEDURE p_del_studentInfo_bySeqNo(IN seqNo INT)

BEGIN

    DECLARE studentId INT;

START TRANSACTION;#啟動事務

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 ;#提交事務

END $$

DELIMITER ;

條件判斷語句

在儲存過程中，條件判斷語句是必不可少的，經常使用的有IF-THEN-ELSE和WHILE-DO語句，條件語句可以減少很多資料庫不必要的記憶體開銷，

現在我們來優化一下上面寫的刪除學生資訊的儲存過程，程式碼如下：


DROP PROCEDURE IF EXISTS `p_del_studentInfo_bySeqNo`;

DELIMITER $$

CREATE PROCEDURE p_del_studentInfo_bySeqNo(IN seqNo INT)

BEGIN

    DECLARE studentId INT;

START TRANSACTION;#啟動事務

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 ;#提交事務

ELSE

ROLLBACK;#回滾

END IF;

END $$

DELIMITER ;

注： IF條件判斷語句一定是以 “END IF; ”結束的，中間可以有多層條件判斷。格式：IF 條件體 THEN 執行業務結果 ELSE 執行業務結果 END IF

異常處理(SQLEXCEPTION)

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


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;#初始化返回結果，解決中文亂碼問題

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET e_code=1;#遇到錯誤後繼續執行;(需要返回執行結果時用這個)

START TRANSACTION;#啟動事務

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 = '該學生不存在！';

END IF;

IF e_code=1 THEN

ROLLBACK;  #回滾

ELSE

COMMIT;

SET result = '該學生已被刪除成功';

END IF;

SELECT result;

END $$

DELIMITER ;

  • 定義報錯程式碼變數名並設定初始化值為0 DECLARE e_code INT DEFAULT 0;

  • 定義化返回結果變數名並設定編碼為utf-8 DECLARE result VARCHAR(256) CHARACTER set utf8;

  • DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET e_code=1; 遇到錯誤後繼續執行;(需要返回執行結果時用這個)

  • DECLARE EXIT HANDLER FOR SQLEXCEPTION SET e_code=1;遇到錯誤後退出當前塊;

宣告該儲存過程的使用物件(SQL SECURITY INVOKER)

在建立儲存過程的時候，預設是當前使用者可使用所有許可權，其他使用者不能使用或者不能執行儲存過程中的DML語句，為了使其他使用者也能使用該儲存過程

則需要在建立的時候宣告一下，使用方法如下：


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;#初始化返回結果，解決中文亂碼問題

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET e_code=1;#遇到錯誤後繼續執行;(需要返回執行結果時用這個)

START TRANSACTION;#啟動事務

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 = '該學生不存在！';

END IF;

IF e_code=1 THEN

ROLLBACK;  #回滾

ELSE

COMMIT;

SET result = '該學生已被刪除成功';

END IF;

SELECT result;

END $$

DELIMITER ;

擴充

習慣性建議

  • 預設情況下，儲存過程和預設資料庫相關聯，如果想指定儲存過程建立在某個特定的資料庫下，那麼在過程名前面加資料庫名做字首；

  • 程式碼儘量簡潔，有序，該縮排的地方使用縮排

  • 過程體有必要的時候儘量採用標籤

  • 儲存過程傳引數時儘量使用對應的名詞，儘量不用INOUT

  • 每次建立表或者函式和儲存過程的時候儘量先判斷是否存在再刪除以免造成報錯，採用 DROP … IF EXISTS …格式

相關文章