存儲過程新手指南

存儲過程(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 …格式

淺談mysql資料庫技術，輕松玩轉存儲過程存儲過程新手指南

存儲過程新手指南

存儲過程(Stored Procedure)

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

存儲過程的建立與調用

建立存儲過程

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

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

存儲過程體

标簽化

存儲過程參數 (proc_parameter)

IN 輸入參數

OUT 輸出參數

INOUT 輸入輸出參數

高手進階

全局變量

聲明語句(DECLARE)

事務機制(TRANSACTION)

條件判斷語句

異常處理(SQLEXCEPTION)

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

拓展

習慣性建議

繼續閱讀

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

mysql使用source指令導入.sql檔案

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

SequoiaDB巨杉資料庫C++驅動概述