mysql 存储过程基础_MySQL开发基础 存储过程和函数(下)

1. 光标的使用(游标)

在存储过程和函数中可以使用光标对结果集进行循环的处理，光标使用包括光标的声明，open ,fetch,close。

下面在存储过程中使用一个光标， 这个举例中光标里的逻辑不重要， 重点在于如何使用光标：

SELECT city_id FROM test.city

DELIMITER $$

CREATE PROCEDURE proc_demo_cursor()

BEGIN

-- 定义变量，获取光标中的数据

DECLARE city_id_staff SMALLINT DEFAULT 0;

-- 声明光标

DECLARE cur_city CURSOR FOR SELECT city_id FROM test.city;

-- 定义条件 没有找到数据时退出光标 NOT FOUND CLOSE cur_city

DECLARE EXIT HANDLER FOR NOT FOUND CLOSE cur_city;

SET @result=0;

-- 打开光标

OPEN cur_city;

REPEAT

FETCH cur_city INTO city_id_staff;

IF city_id_staff < 10 THEN

SET @result=@result+1;

ELSE

SET @result=@result+2;

END IF;

UNTIL 0 END REPEAT;

-- 关闭光标

CLOSE cur_city;

END $$

DELIMITER ;

-- 调用

CALL proc_demo_cursor;

SELECT @result;

2. 流程控制

包括 if ,case, loop, leave, iterate, repeat, while 语句进行流程控制

2.1  IF 语句

-- 语法如下

IF search_condition THEN statement_list

[ELSE IF search_condition THEN statement_list] ...

[ELSE statement_list]

END IF

-- 举例

IF city_id_staff < 10 THEN

SET @result=@result+1;

ELSE

SET @result=@result+2;

END IF;

2.2 CASE 语句

-- 举例二种用法

CASE

WHEN i_staff_id = 2 THEN

SET @x1 = @x1 + d_amount;

ELSE

SET @x2 = @x2 + d_amount;

END CASE;

-- 或者：

CASE i_staff_id

WHEN 2 THEN

SET @x1 = @x1 + d_amount;

ELSE

SET @x2 = @x2 + d_amount;

END CASE;

2.3  loop 语句

loop 实现简单的循环，退出循环条件可以使用leave语句。

DELIMITER $$

CREATE PROCEDURE proc_demo_loop()

BEGIN

DECLARE increase INT DEFAULT 1;

SET @x=0;

ins: LOOP

SET increase=increase+1;

IF increase =100 THEN

SET @x=increase;

-- 退出

LEAVE ins;

END IF;

END LOOP ins;

END $$

DELIMITER ;

-- 调用

CALL proc_demo_loop;

SELECT @x;

2.4  ITERATE语句

ITERATE该语句必须用在循环中，作用相当于for 中的continue, 跳过当前循环剩下的语句，直接进入下一轮循环。

DELIMITER $$

CREATE PROCEDURE proc_demo_iterate()

BEGIN

DECLARE increase INT DEFAULT 1;

SET @x=0;

ins: LOOP

SET increase=increase+1;

IF increase =100 THEN

-- 退出

LEAVE ins;

ELSEIF MOD(increase,2)=0 THEN

-- 返回

ITERATE ins;

END IF;

SET @x=@x+1;

END LOOP ins;

END $$

DELIMITER ;

-- 调用

CALL proc_demo_iterate;

SELECT @x;

2.5 repeat 语句

有条件的循环控制语句，当满足条件的时候退出循环,在上面的光标也是一种循环,使用repeat来退出光标，使用close来关闭光标。

-- 语法

[begin_label:] REPEAT

statement_list

UNTIL search_condition

END REPEAT [end_label]

-- 举例(UNTIL 0 END REPEAT)退出 当search_condition为0时，使用END REPEAT退出

REPEAT

FETCH cur_city INTO city_id_staff;

IF city_id_staff < 10 THEN

SET @result=@result+1;

ELSE

SET @result=@result+2;

END IF;

UNTIL 0 END REPEAT;

2.6 while 语句

也是有条件的循环控制语句，当满足条件的时候退出循环。WHILE 循环和REPEAT 循环的区别在于：WHILE 是满足条件才执行循环，REPEAT 是满足条件退出循环；WHILE 在首次循环执行之前就判断条件，所以循环最少执行0 次，而REPEAT 是在首次执行循环之后才判断条件，所以循环最少执行1 次。 相当于开发语言的while(bool) 和do while(bool)。

DELIMITER $$

CREATE PROCEDURE proc_demo_while()

BEGIN

DECLARE increase INT DEFAULT 1;

SET @x=0;

WHILE increase<=10 DO

SET increase=increase+1;

SET @x=@x+increase;

END WHILE;

END $$

DELIMITER ;

-- 调用

CALL proc_demo_while;

SELECT @x;