理解MySQL變數和條件_Mysql

一、概述 

 變數在預存程序中會經常被使用，變數的使用方法是一個重要的知識點，特別是在定義條件這塊比較重要。

 mysql版本：5.6

二、變數定義和賦值 

#建立資料庫DROP DATABASE IF EXISTS Dpro;CREATE DATABASE DproCHARACTER SET utf8;USE Dpro;#建立部門表DROP TABLE IF EXISTS Employee;CREATE TABLE Employee(id INT NOT NULL PRIMARY KEY COMMENT '主鍵', name VARCHAR(20) NOT NULL COMMENT '人名', depid INT NOT NULL COMMENT '部門id');INSERT INTO Employee(id,name,depid) VALUES(1,'陳',100),(2,'王',101),(3,'張',101),(4,'李',102),(5,'郭',103);

declare定義變數

在預存程序和函數中通過declare定義變數在BEGIN...END中，且在語句之前。並且可以通過重複定義多個變數

注意：declare定義的變數名不能帶‘@'符號，mysql在這點做的確實不夠直觀，往往變數名會被錯成參數或者欄位名。

DECLARE var_name[,...] type [DEFAULT value]
例如：

DROP PROCEDURE IF EXISTS Pro_Employee;DELIMITER $$CREATE PROCEDURE Pro_Employee(IN pdepid VARCHAR(20),OUT pcount INT )READS SQL DATASQL SECURITY INVOKERBEGINDECLARE pname VARCHAR(20) DEFAULT '陳';SELECT COUNT(id) INTO pcount FROM Employee WHERE depid=pdepid;END$$DELIMITER ;

SET變數賦值

SET除了可以給已經定義好的變數賦值外，還可以指定賦值並定義新變數，且SET定義的變數名可以帶‘@'符號，SET語句的位置也是在BEGIN ....END之間的語句之前。

1.變數賦值

SET var_name = expr [, var_name = expr] ...DROP PROCEDURE IF EXISTS Pro_Employee;DELIMITER $$CREATE PROCEDURE Pro_Employee(IN pdepid VARCHAR(20),OUT pcount INT )READS SQL DATASQL SECURITY INVOKERBEGINDECLARE pname VARCHAR(20) DEFAULT '陳';SET pname='王';SELECT COUNT(id) INTO pcount FROM Employee WHERE depid=pdepid AND name=pname;END$$DELIMITER ;CALL Pro_Employee(101,@pcount); SELECT @pcount;

 

2.通過賦值定義變數

DROP PROCEDURE IF EXISTS Pro_Employee;DELIMITER $$CREATE PROCEDURE Pro_Employee(IN pdepid VARCHAR(20),OUT pcount INT )READS SQL DATASQL SECURITY INVOKERBEGINDECLARE pname VARCHAR(20) DEFAULT '陳';SET pname='王';SET @ID=1;SELECT COUNT(id) INTO pcount FROM Employee WHERE depid=pdepid AND name=pname;SELECT @ID;END$$DELIMITER ;CALL Pro_Employee(101,@pcount);

SELECT ... INTO語句賦值

 通過select into語句可以將值賦予變數，也可以之間將該值賦值預存程序的out參數,上面的預存程序select into就是之間將值賦予out參數。

DROP PROCEDURE IF EXISTS Pro_Employee;DELIMITER $$CREATE PROCEDURE Pro_Employee(IN pdepid VARCHAR(20),OUT pcount INT )READS SQL DATASQL SECURITY INVOKERBEGINDECLARE pname VARCHAR(20) DEFAULT '陳';DECLARE Pid INT;SELECT COUNT(id) INTO Pid FROM Employee WHERE depid=pdepid AND name=pname;SELECT Pid;END$$DELIMITER ;CALL Pro_Employee(101,@pcount);

這個預存程序就是select into將值賦予變數；

 

表中並沒有depid=101 and name='陳'的記錄。

三、條件 

條件的作用一般用在對指定條件的處理，比如我們遇到主鍵重複報錯後該怎樣處理。

定義條件

 定義條件就是事先定義某種錯誤狀態或者sql狀態的名稱，然後就可以引用該條件名稱開做條件處理，定義條件一般用的比較少，一般會直接放在條件處理裡面。

DECLARE condition_name CONDITION FOR condition_value condition_value:  SQLSTATE [VALUE] sqlstate_value | mysql_error_code

1.沒有定義條件：

DROP PROCEDURE IF EXISTS Pro_Employee_insert;DELIMITER $$CREATE PROCEDURE Pro_Employee_insert()MODIFIES SQL DATASQL SECURITY INVOKERBEGINSET @ID=1;INSERT INTO Employee(id,name,depid) VALUES(1,'陳',100);SET @ID=2;INSERT INTO Employee(id,name,depid) VALUES(6,'陳',100);SET @ID=3;END$$DELIMITER ;#執行預存程序CALL Pro_Employee_insert();#查詢變數值SELECT @ID,@X;

報主鍵重複的錯誤，其中1062是主鍵重複的錯誤碼，23000是sql錯誤狀態

2.定義處理條件

DROP PROCEDURE IF EXISTS Pro_Employee_insert;DELIMITER $$CREATE PROCEDURE Pro_Employee_insert()MODIFIES SQL DATASQL SECURITY INVOKERBEGIN#定義條件名稱，DECLARE reprimary CONDITION FOR 1062;#引用前面定義的條件名稱並做賦值處理DECLARE EXIT HANDLER FOR reprimary SET @x=1;SET @ID=1;INSERT INTO Employee(id,name,depid) VALUES(1,'陳',100);SET @ID=2;INSERT INTO Employee(id,name,depid) VALUES(6,'陳',100);SET @ID=3;END$$DELIMITER ;CALL Pro_Employee_insert();SELECT @ID,@X;

在執行預存程序的步驟中並沒有報錯，但是由於我定義的是exit，所以在遇到報錯sql就終止往下執行了。

接下來看看continue的不同

DROP PROCEDURE IF EXISTS Pro_Employee_insert;DELIMITER $$CREATE PROCEDURE Pro_Employee_insert()MODIFIES SQL DATASQL SECURITY INVOKERBEGIN#定義條件名稱，DECLARE reprimary CONDITION FOR SQLSTATE '23000';#引用前面定義的條件名稱並做賦值處理DECLARE CONTINUE HANDLER FOR reprimary SET @x=1;SET @ID=1;INSERT INTO Employee(id,name,depid) VALUES(1,'陳',100);SET @ID=2;INSERT INTO Employee(id,name,depid) VALUES(6,'陳',100);SET @ID=3;END$$DELIMITER ;CALL Pro_Employee_insert();SELECT @ID,@X;

其中紅色標示的是和上面不同的地方，這裡定義條件使用的是SQL狀態，也是主鍵重複的狀態；並且這裡使用的是CONTINUE就是遇到錯誤繼續往下執行。

 條件處理

條件處理就是之間定義語句的錯誤的處理，省去了前面定義條件名稱的步驟。

DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement handler_type:  CONTINUE| EXIT| UNDO condition_value:  SQLSTATE [VALUE] sqlstate_value | condition_name | SQLWARNING | NOT FOUND | SQLEXCEPTION | mysql_error_code

handler_type：遇到錯誤是繼續往下執行還是終止，目前UNDO還沒用到。

CONTINUE:繼續往下執行

EXIT：終止執行

condition_values:錯誤狀態

SQLSTATE [VALUE] sqlstate_value：就是前面講到的SQL錯誤狀態，例如主鍵重複狀態SQLSTATE '23000'

condition_name：上面講到的定義條件名稱；

SQLWARNING：是對所有以01開頭的SQLSTATE代碼的速記，例如：DECLARE CONTINUE HANDLER FOR SQLWARNING。

NOT FOUND:是對所有以02開頭的SQLSTATE代碼的速記。

SQLEXCEPTION:是對所有沒有被SQLWARNING或NOT FOUND捕獲的SQLSTATE代碼的速記。

mysql_error_code:是錯誤碼，例如主鍵重複的錯誤碼是1062，DECLARE CONTINUE HANDLER FOR 1062

 語句：

DROP PROCEDURE IF EXISTS Pro_Employee_insert;DELIMITER $$CREATE PROCEDURE Pro_Employee_insert()MODIFIES SQL DATASQL SECURITY INVOKERBEGIN#引用前面定義的條件名稱並做賦值處理DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET @x=2;#開始事務必須在DECLARE之後START TRANSACTION ;SET @ID=1;INSERT INTO Employee(id,name,depid) VALUES(7,'陳',100);SET @ID=2;INSERT INTO Employee(id,name,depid) VALUES(6,'陳',100);SET @ID=3;IF @x=2 THEN ROLLBACK;ELSE COMMIT;END IF; END$$DELIMITER ;#執行預存程序CALL Pro_Employee_insert();#查詢SELECT @ID,@X;

通過SELECT @ID,@X可以知道預存程序已經執行到了最後，但是因為預存程序後面有做復原操作整個語句進行了復原，所以ID=7的合格記錄也被復原了。

總結 

變數的使用不僅僅只有這些，在游標中條件也是一個很好的功能，剛才測試的是continue如果使用EXIT的話語句執行完“SET @ID=2;”就不往下執行了，後面的IF也不被執行整個語句不會被復原，但是使用CONTINE當出現錯誤後還是會往下執行如果後面的語句還有很多的話整個復原的過程將會很長，在這裡可以利用迴圈，當出現錯誤立刻退出迴圈執行後面的if復原操作，在下一篇講迴圈語句會寫到，歡迎關注。