MySQL学习笔记十:游标/动态SQL/临时表/事务

时间:2020-09-10 14:50:00 来源:互联网 作者: 神秘的大神 字体:

一、游标

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代表无报错。