一、游標
1.1、越界標志
在MySQL游標中，可以宣告DECLARE CONTINUE HANDLER來操作1個越界標志，
語法：DECLARE CONTINUE HANDLER FOR NOT FOUND STATEMENT;
1.2、REPEAT方式
DELIMITER $$ CREATE PROCEDURE CHANGESEX () BEGIN DECLARE HAVE INT DEFAULT 1; DECLARE PID INT; DECLARE CURT CURSOR FOR SELECT ID FROM STUDY11; DECLARE CONTINUE HANDLER FOR NOT FOUND SET HAVE=0; OPEN CURT; FETCH CURT INTO PID; REPEAT UPDATE STUDY11 SET SEX=CASE SEX WHEN '男' THEN 'MALE' ELSE 'FEMALE' END WHERE ID=PID; FETCH CURT INTO PID; UNTIL HAVE=0 END REPEAT; CLOSE CURT; END$$ DELIMITER ;
需要注意的是：MySQL不能像SQL Server那樣，使用DEALLOCATE的方式釋放游標資源，
1.3、WHILE方式
DELIMITER $$ CREATE PROCEDURE CHANGESEX1 () BEGIN DECLARE HAVE INT DEFAULT 1; DECLARE PID INT; DECLARE CURT CURSOR FOR SELECT ID FROM STUDY11; DECLARE CONTINUE HANDLER FOR NOT FOUND SET HAVE=0; OPEN CURT; FETCH CURT INTO PID; WHILE HAVE=1 DO UPDATE STUDY11 SET SEX=CASE SEX WHEN 'MALE' THEN '男' ELSE '女' END WHERE ID=PID; FETCH CURT INTO PID; END WHILE; CLOSE CURT; END$$ DELIMITER ;
二、動態SQL
2.1、動態SQL格式
SET SQL= (預處理的SQL陳述句，可以使用CONCAT拼接的陳述句，引數用 ？代替 ，); SET @SQL=SQL; PREPARE STMT_NAME FROM @SQL; SET @VAR_NAME=XXX; EXECUTE STMT_NAME [USING @VAR_NAME[,@VAR_NAME]...]; {DEALLOCATE | DROP} PREPARE STMT_NAME;
2.2、動態SQL示例
DELIMITER $$ CREATE PROCEDURE GETNAME (IN PID INT) BEGIN #定義預處理SQL陳述句 DECLARE STRSQL VARCHAR(1000); #拼接SQL陳述句 SET STRSQL="SELECT NAME FROM EMP WHERE ID=?"; #將自定義變數賦值給用戶變數 SET @SQL=STRSQL; #預處理動態SQL陳述句 PREPARE STMT FROM @SQL; #傳遞動態SQL引數 SET @PARAM1=PID; #執行動態SQL陳述句 EXECUTE STMT USING @PARAM1; #釋放PREPARE DEALLOCATE PREPARE STMT; END$$ DELIMITER ;
可以看出，MySQL動態SQL支持""，
2.3、動態SQL注意事項
1）存盤動態SQL的值的變數不能是自定義變數，必須是用戶變數或者全域變數 ，如：SET SQL='XXX';PREPARE STMT FROM SQL;都是錯誤的寫法，正確的寫法為：SET @SQL='XXX';PREPARE STMT FROM @SQL;
2）即使PREPARABLE_STMT陳述句中的 ? 所代表的是一個字串，也不需要用引號將 ? 兩邊包起來，
3）如果動態陳述句中用到了 IN ，則SQL陳述句可以寫成：SET STRSQL="SELECT NAME FROM EMP WHERE ID IN (?,?,?)";
三、臨時表
3.1、臨時表創建
方法一：
CREATE TEMPORARY TABLE `emp_t1` ( `ID` int(11) DEFAULT NULL, `NAME` varchar(50) DEFAULT NULL, `AGE` int(11) DEFAULT NULL, KEY `ID_INDEX` (`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
方法二：
CREATE TEMPORARY TABLE emp_t2 SELECT * FROM EMP WHERE ID<=10;
可以看出，MySQL并不支持像SQL Server那樣使用SELECT * INTO #EMP FROM EMP的方式來創建臨時表，
3.2、臨時表洗掉
DROP TABLE EMP_T1,EMP_T2;
3.3、臨時表測驗（存盤程序）
1）創建：
DELIMITER $$ CREATE PROCEDURE TempTest1 () BEGIN #臨時表創建方式一測驗 CREATE TEMPORARY TABLE `emp_t1` ( `ID` int(11) DEFAULT NULL, `NAME` varchar(50) DEFAULT NULL, `AGE` int(11) DEFAULT NULL, KEY `ID_INDEX` (`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO EMP_T1 SELECT * FROM EMP LIMIT 10; #臨時表創建方式二測驗 CREATE TEMPORARY TABLE emp_t2 SELECT * FROM EMP WHERE ID<=10; #結果回傳 SELECT * FROM EMP_T1 UNION SELECT * FROM EMP_T2; #臨時表洗掉 DROP TABLE EMP_T1,EMP_T2; END$$ DELIMITER ;
2）呼叫：
CALL TempTest1();
3）結果：
3.4、自定義函式能使用臨時表嗎？
不可以！自定義函式不支持創建表，不管是正常表還是臨時表，（SQL Server自定義函式，也不支持臨時表，但是支持表變數，）
四、事務
4.1、基礎概念
事務（Transaction）是訪問和更新資料庫的程式執行單元；事務中可能包含一個或多個sql陳述句，這些陳述句要么都執行，要么都不執行，
4.2、MySQL邏輯架構與存盤引擎
如上圖所示，MySQL邏輯架構從上往下可以分為三層：
1）第一層：處理客戶端連接、授權認證等，
2）第二層：服務器層，負責查詢陳述句的決議、優化、快取以及內置函式的實作、存盤程序等，
3）第三層：存盤引擎，負責MySQL中資料的存盤和提取，
說明1：MySQL中服務器層不管理事務，事務是由存盤引擎實作的，
說明2：MySQL支持事務的存盤引擎有InnoDB、NDB Cluster等，其中InnoDB的使用最為廣泛；其他存盤引擎不支持事務，如MyIsam、Memory等，
4.3、事務控制陳述句
1）BEGIN 或 START TRANSACTION 顯式地開啟一個事務；
2）COMMIT 也可以使用 COMMIT WORK，不過二者是等價的，COMMIT 會提交事務，并使已對資料庫進行的所有修改成為永久性的；
3）ROLLBACK 也可以使用 ROLLBACK WORK，不過二者是等價的，回滾會結束用戶的事務，并撤銷正在進行的所有未提交的修改；
4）SAVEPOINT identifier，SAVEPOINT 允許在事務中創建一個保存點，一個事務中可以有多個 SAVEPOINT；
5）RELEASE SAVEPOINT identifier 洗掉一個事務的保存點，當沒有指定的保存點時，執行該陳述句會拋出一個例外；
6）ROLLBACK TO identifier 把事務回滾到標記點；
7）SET TRANSACTION 用來設定事務的隔離級別，InnoDB 存盤引擎提供事務的隔離級別有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE，
4.4、事務提交模式
SET AUTOCOMMIT=1 開啟自動提交
SET AUTOCOMMIT=0 禁止自動提交
默認是開啟自動提交，可以使用以下命令查詢：
SHOW VARIABLES LIKE 'AUTOCOMMIT';
4.5、事務示例
4.5.1、無判斷陳述句事務
DELIMITER $$ CREATE PROCEDURE TranTest1 () BEGIN #臨時表創建 CREATE TEMPORARY TABLE EMP_T1 SELECT * FROM EMP WHERE 1=2; #開啟事務 START TRANSACTION; #資料插入 INSERT INTO EMP_T1 VALUES (1,'HELLO',18); INSERT INTO EMP_T1 VALUES (2,'WORLD',19); #提交事務 COMMIT; #結果回傳 SELECT * FROM EMP_T1; #臨時表洗掉 DROP TABLE EMP_T1; END$$ DELIMITER ;
4.5.2、有判斷陳述句事務（推薦）
DELIMITER $$ CREATE PROCEDURE TranTest2 () BEGIN #判斷事務是否例外的錯誤變數 DECLARE PERROR INT DEFAULT 0; DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET PERROR=1; #臨時表創建 CREATE TEMPORARY TABLE EMP_T1 SELECT * FROM EMP WHERE 1=2; #開啟事務 START TRANSACTION; #資料插入 INSERT INTO EMP_T1 VALUES (1,'HELLO',18); INSERT INTO EMP_T1 VALUES (2,'WORLD',19); #提交事務 IF (PERROR=0) THEN COMMIT; ELSE ROLLBACK; END IF; #結果回傳 SELECT * FROM EMP_T1; #臨時表洗掉 DROP TABLE EMP_T1; END$$ DELIMITER ;
說明：SQL Server中事務執行是否有報錯，可以使用@@ERROR來判斷，@@ERROR=0代表無報錯，
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/4082.html
標籤：MySQL
下一篇：事務的隔離級別與所帶來的問題