一、游标
1.1、越界标志
在MySQL游标中,可以声明DECLARE CONTINUE HANDLER来操作1个越界标志。
语法:DECLARE CONTINUE HANDLER FOR NOT FOUND STATEMENT;
1.2、REPEAT方式
DELIMITER $$CREATE PROCEDURECHANGESEX ()BEGIN
DECLARE HAVE INT DEFAULT 1;DECLARE PID INT;DECLARE CURT CURSOR FOR SELECT ID FROMSTUDY11;DECLARE CONTINUE HANDLER FOR NOT FOUND SET HAVE=0;OPENCURT;FETCH CURT INTOPID;
REPEATUPDATE STUDY11 SET SEX=CASE SEX WHEN '男' THEN 'MALE' ELSE 'FEMALE' END WHERE ID=PID;FETCH CURT INTOPID;
UNTIL HAVE=0 ENDREPEAT;CLOSECURT;END$$
DELIMITER ;
需要注意的是:MySQL不能像SQL Server那样,使用DEALLOCATE的方式释放游标资源。
1.3、WHILE方式
DELIMITER $$CREATE PROCEDURECHANGESEX1 ()BEGIN
DECLARE HAVE INT DEFAULT 1;DECLARE PID INT;DECLARE CURT CURSOR FOR SELECT ID FROMSTUDY11;DECLARE CONTINUE HANDLER FOR NOT FOUND SET HAVE=0;OPENCURT;FETCH CURT INTOPID;WHILE HAVE=1DOUPDATE STUDY11 SET SEX=CASE SEX WHEN 'MALE' THEN '男' ELSE '女' END WHERE ID=PID;FETCH CURT INTOPID;END WHILE;CLOSECURT;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;
#释放PREPAREDEALLOCATE PREPARESTMT;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 PROCEDURETempTest1 ()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 * FROMEMP_T2;
#临时表删除DROP TABLEEMP_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 PROCEDURETranTest1 ()BEGIN#临时表创建CREATE TEMPORARY TABLE EMP_T1 SELECT * FROM EMP WHERE 1=2;
#开启事务
STARTTRANSACTION;
#数据插入INSERT INTO EMP_T1 VALUES (1,'HELLO',18);INSERT INTO EMP_T1 VALUES (2,'WORLD',19);
#提交事务COMMIT;
#结果返回SELECT * FROMEMP_T1;
#临时表删除DROP TABLEEMP_T1;END$$
DELIMITER ;
4.5.2、有判断语句事务(推荐)
DELIMITER $$CREATE PROCEDURETranTest2 ()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;
#开启事务
STARTTRANSACTION;
#数据插入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 * FROMEMP_T1;
#临时表删除DROP TABLEEMP_T1;END$$
DELIMITER ;
说明:SQL Server中事务执行是否有报错,可以使用@@ERROR来判断,@@ERROR=0代表无报错。