一、概述
變量在存儲(chǔ)過程中會(huì)經(jīng)常被使用，變量的使用方法是一個(gè)重要的知識(shí)點(diǎn)，特別是在定義條件這塊比較重要。
mysql版本：5.6
二、變量定義和賦值
#創(chuàng)建數(shù)據(jù)庫DROP DATABASE IF EXISTS Dpro;CREATE DATABASE DproCHARACTER SET utf8;USE Dpro;#創(chuàng)建部門表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定義變量
在存儲(chǔ)過程和函數(shù)中通過declare定義變量在BEGIN...END中，且在語句之前。并且可以通過重復(fù)定義多個(gè)變量
注意：declare定義的變量名不能帶‘@'符號(hào)，mysql在這點(diǎn)做的確實(shí)不夠直觀，往往變量名會(huì)被錯(cuò)成參數(shù)或者字段名。
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除了可以給已經(jīng)定義好的變量賦值外，還可以指定賦值并定義新變量，且SET定義的變量名可以帶‘@'符號(hào)，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語句可以將值賦予變量，也可以之間將該值賦值存儲(chǔ)過程的out參數(shù),上面的存儲(chǔ)過程select into就是之間將值賦予out參數(shù)。
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);
這個(gè)存儲(chǔ)過程就是select into將值賦予變量；
表中并沒有depid=101 and name='陳'的記錄。
三、條件
條件的作用一般用在對(duì)指定條件的處理，比如我們遇到主鍵重復(fù)報(bào)錯(cuò)后該怎樣處理。
定義條件
定義條件就是事先定義某種錯(cuò)誤狀態(tài)或者sql狀態(tài)的名稱，然后就可以引用該條件名稱開做條件處理，定義條件一般用的比較少，一般會(huì)直接放在條件處理里面。
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 ;#執(zhí)行存儲(chǔ)過程CALL Pro_Employee_insert();#查詢變量值SELECT @ID,@X;
報(bào)主鍵重復(fù)的錯(cuò)誤，其中1062是主鍵重復(fù)的錯(cuò)誤代碼，23000是sql錯(cuò)誤狀態(tài)
2.定義處理?xiàng)l件
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;
在執(zhí)行存儲(chǔ)過程的步驟中并沒有報(bào)錯(cuò)，但是由于我定義的是exit，所以在遇到報(bào)錯(cuò)sql就終止往下執(zhí)行了。
接下來看看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;
其中紅色標(biāo)示的是和上面不同的地方，這里定義條件使用的是SQL狀態(tài)，也是主鍵重復(fù)的狀態(tài)；并且這里使用的是CONTINUE就是遇到錯(cuò)誤繼續(xù)往下執(zhí)行。
條件處理
條件處理就是之間定義語句的錯(cuò)誤的處理，省去了前面定義條件名稱的步驟。
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：遇到錯(cuò)誤是繼續(xù)往下執(zhí)行還是終止，目前UNDO還沒用到。
CONTINUE:繼續(xù)往下執(zhí)行
EXIT：終止執(zhí)行
condition_values:錯(cuò)誤狀態(tài)
SQLSTATE [VALUE] sqlstate_value：就是前面講到的SQL錯(cuò)誤狀態(tài)，例如主鍵重復(fù)狀態(tài)SQLSTATE '23000'
condition_name：上面講到的定義條件名稱；
SQLWARNING：是對(duì)所有以01開頭的SQLSTATE代碼的速記，例如：DECLARE CONTINUE HANDLER FOR SQLWARNING。
NOT FOUND:是對(duì)所有以02開頭的SQLSTATE代碼的速記。
SQLEXCEPTION:是對(duì)所有沒有被SQLWARNING或NOT FOUND捕獲的SQLSTATE代碼的速記。
mysql_error_code:是錯(cuò)誤代碼，例如主鍵重復(fù)的錯(cuò)誤代碼是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;#開始事務(wù)必須在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 ;#執(zhí)行存儲(chǔ)過程CALL Pro_Employee_insert();#查詢SELECT @ID,@X;
通過SELECT @ID,@X可以知道存儲(chǔ)過程已經(jīng)執(zhí)行到了最后，但是因?yàn)榇鎯?chǔ)過程后面有做回滾操作整個(gè)語句進(jìn)行了回滾，所以ID=7的符合條件的記錄也被回滾了。
總結(jié)
變量的使用不僅僅只有這些，在光標(biāo)中條件也是一個(gè)很好的功能，剛才測試的是continue如果使用EXIT的話語句執(zhí)行完“SET @ID=2;”就不往下執(zhí)行了，后面的IF也不被執(zhí)行整個(gè)語句不會(huì)被回滾，但是使用CONTINE當(dāng)出現(xiàn)錯(cuò)誤后還是會(huì)往下執(zhí)行如果后面的語句還有很多的話整個(gè)回滾的過程將會(huì)很長，在這里可以利用循環(huán)，當(dāng)出現(xiàn)錯(cuò)誤立刻退出循環(huán)執(zhí)行后面的if回滾操作，在下一篇講循環(huán)語句會(huì)寫到，歡迎關(guān)注。
