【Mysql系列】游标/光标简介

游标/光标概述

游标是用来存储查询结果集的数据类型、在存储的过程和函数中可以使用光标对结果集进行循环的处理、光标的使用包括光标的声明、OPEN、TETCH和CLOSE。

声明光标：

DECLARE cursor_name CURSOR FOR select_statement

OPEN光标

OPEN cursor_name;

FETCH光标

TETCH cursor_name INFO var_name [,var_name] ...

CLOSE光标

CLOSE cursor_name;

示例

CREATE TABLE `emp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL COMMENT '姓名',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `salary` int(11) DEFAULT NULL COMMENT '薪水',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8

CREATE procedure pro_test12()
BEGIN
	declare e_id int(11);
	declare e_name varchar(50);
	declare e_age int(11);
	declare e_salary int(11);
	declare has_data int default 1;
	
	declare emp_result cursor for select * from emp;
	DECLARE EXIT HANDLER FOR NOT FOUND set has_data = 0;
	
	open emp_result;

	repeat
		fetch emp_result into e_id,e_name,e_age,e_salary;
		select concat(' id = ',e_id,' name = ',e_name,' age = ',e_age,' salary = ',e_salary);
		until has_data = 0
	end repeat;
	close emp_result;

END

call pro_test12()

函数简介

CREATE FUNCTION fun1(countryId int)
RETURNS int
BEGIN
	declare cnum int;
	select count(*) into cnum from city where country_id = country_id;
	return num;
END

select fun1(1);