乐趣区

关于mysql:详解MySQL游标

1、什么是游标(或光标)
尽管咱们也能够通过筛选条件 WHERE 和 HAVING,或者是限定返回记录的关键字 LIMIT 返回一条记录,然而,却无奈在后果集中像指针一样,向前定位一条记录、向后定位一条记录,或者是随便定位到某一条记录,并对记录的数据进行解决。这个时候,就能够用到游标。游标,提供了一种灵便的操作形式,让咱们可能对后果集中的每一条记录进行定位,并对指向的记录中的数据进行操作的数据结构。游标让 SQL 这种面向汇合的语言有了面向过程开发的能力。SQL 中,游标是一种长期的数据库对象,能够指向存储在数据库表中的数据行指针。这里游标充当了指针的作用,咱们能够通过操作游标来对数据行进行操作 MySQL 中游标能够在存储过程和函数中应用比方,咱们查问了 employees 数据表中工资高于 15000 的员工都有哪些:

SELECT employee_id,last_name,salary FROM employees
WHERE salary > 15000;

这里咱们就能够通过游标来操作数据行,如图所示此时游标所在的行是“108”的记录,咱们也能够在后果集上滚动游标,指向后果集中的任意一行。2、如何应用游标游标必须在申明处理程序之前被申明,并且变量和条件还必须在申明游标或处理程序之前被申明。1、申明游标语法格局:DECLARE 游标名 CURSOR FOR 查问语句;
要应用 SELECT 语句来获取数据后果集,而此时还没有开始遍历数据,这里 查问语句代表的是 SELECT 语句,返回一个用于创立游标的后果集举例:

DECLARE emp_cur CURSOR FOR 
SELECT employee_id,salary FROM employees;

2、关上游标语法格局:OPEN 游标名;
当咱们定义好游标之后,如果想要应用游标,必须先关上游标。关上游标的时候 SELECT 语句的查问后果集就会送到游标工作区,为前面游标的逐条读取后果集中的记录做筹备。3、应用游标语法格局:

FETCH 游标名 INTO var_name [, var_name] ...

这句的作用是应用 游标名这个游标来读取以后行,并且将数据保留到 var_name 这个变量中,游标指针指到下一行。如果游标读取的数据行有多个列名,则在 INTO 关键字前面赋值给多个变量名即可。留神:var_name 必须在申明游标之前就定义好。举例:

FETCH emp_cur INTO emp_id, emp_sal ;

留神:游标的查问后果集中的字段数,必须跟 INTO 前面的变量数统一 4、敞开游标语法格局:CLOSE 游标名;
当咱们应用完游标后须要敞开掉该游标。因为游标会占用系统资源,如果不及时敞开,游标会始终放弃到存储过程完结,影响零碎运行的效率。而敞开游标的操作,会开释游标占用的系统资源。敞开游标之后,咱们就不能再检索查问后果中的数据行,如果须要检索只能再次关上游标。3、代码举例 #创立存储过程“get_count_by_limit_total_salary()”,
申明 IN 参数 limit_total_salary,DOUBLE 类型;申明 OUT 参数 total_count,INT 类型。
函数的性能能够实现累加薪资最高的几个员工的薪资值,
直到薪资总和达到 limit_total_salary 参数的值,返回累加的人数给 total_count。

DELIMITER $
CREATE PROCEDURE get_count_by_limit_total_salary(IN limit_total_salary DOUBLE,OUT total_count INT)
BEGIN
    DECLARE count_emp INT DEFAULT 0;    #用来记录人数,默认值是 0
    DECLARE sum_sal DOUBLE DEFAULT 0;    #记录总薪资
    DECLARE one_sal DOUBLE DEFAULT 0;    #记录一个人的薪资
    
    #申明游标,把查问到的工资后果集从高到低排序
    DECLARE emp_cur CURSOR FOR SELECT salary FROM employees ORDER BY salary DESC;
    
    OPEN emp_cur;    #关上游标
    
    #用循环语句遍历,晓得总薪资满足题目要求
    REPEAT
        #开始应用游标
        FETCH emp_cur INTO one_sal;
        
        SET sum_sal = sum_sal + one_sal;
        SET count_emp = count_emp + 1;
        
        UNTIL sum_sal >= limit_total_salary
        
    END REPEAT;
    CLOSE emp_cur;    #敞开游标
    
    #把后果返回给 total_count
    SELECT count_emp INTO total_count;
END $
DELIMITER ;

CALL get_count_by_limit_total_salary(30000,@count);
SELECT @count;

思路剖析:先创立存储过程,而后再依据理论状况 declare 本人须要的变量来满足题目的要求,游标要按步骤来:创立游标、关上游标、应用游标最初再敞开游标;其中要留神应用游标在循环中,能够进步代码的简洁度。4、小结游标是 MySQL 的一个重要的性能,为逐条读取后果集中的数据,提供了完满的解决方案。跟在利用层面实现雷同的性能相比,游标能够在存储程序中应用,效率高,程序也更加简洁。但同时也会带来一些性能问题,比方在应用游标的过程中,会对数据行进行加锁,这样在业务并发量大的时候,不仅会影响业务之间的效率,还会耗费系统资源,造成内存不足,这是因为游标是在内存中进行的解决。
倡议: 养成用完之后就敞开的习惯,这样能力进步零碎的整体效率。感兴趣的敌人能够在 3A 云服务器上本人部署尝试一下

退出移动版