mysql学习

游标:

一般，select 这些查询语句都是对一行记录而言，如果想查询多行记录的值，则需要使用游标或者是while()等循环

游标实际上是一个能够从包括多条数据记录的结果集中每次提取一条记录的机制，游标充当指针的作用，尽管游标能够遍历所有的行，但是每次只能遍历一次
使用游标的步骤:

1.必须先声明
declare 游标名 cursor for select_statement;
2.打开游标
open 游标名
3.通过游标根据需要取出数据
fetch 游标名 into var1,var2,var[….]

4.使用完必须关闭游标
close 游标名

 create procedure p2()  begin declare row_code int ;  declare row_name varchar(20);  declare row_age int;  declare row_sex varchar(10); declare row_score int; declare row_grade int; declare row_address varchar(20); declare row_major varchar(20);  declare leveler cursor for select code,name,age,sex,score,grade,address,major from  student3; open leveler; fetch leveler into row_code,row_name,row_age,row_sex,row_score,row_grade,row_address,row_major; select    row_code,row_name,row_age,row_sex,row_score,row_grade,row_address,row_major;fetch leveler into row_code,row_name,row_age,row_sex,row_score,row_grade,row_address,row_major;select row_code,row_name,row_age,row_sex,row_score,row_grade,row_address,row_major; fetch leveler into row_code,row_name,row_age,row_sex,row_score,row_grade,row_address,row_major;select row_code,row_name,row_age,row_sex,row_score,row_grade,row_address,row_major; close leveler;end $$Query OK, 0 rows affected (0.00 sec)mysql> call p2();    -> $$1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)

create procedure p5() begin declare row_code int ;declare row_name varchar(20); declare row_age int; declare row_sex varchar(10); declare row_score int; declare row_grade int; declare row_address varchar(20); declare row_major varchar(20); declare cnt int default 0;declare i int default 0;declare leveler cursor for select code,name,age,sex,score,grade,address,major from  student3;select count(*) into cnt from student3; open leveler;repeat set i:=i+1;fetch leveler into row_code,row_name,row_age,row_sex,row_score,row_grade,row_address,row_major; select row_code,row_name,row_age,row_sex,row_score,row_grade,row_address,row_major;until i>=cnt end repeat ;close leveler; end $$Query OK, 0 rows affected (0.00 sec)mysql> call p3();    -> $$Query OK, 0 rows affected (0.00 sec)

在mysql游标中，可以定义continue handler 来操作一个越界标志，使用语法：
declare continue handler for NOT FOUND statment

当内有数据的时候要执行的语句:continue handler

 create procedure p8()  begin  declare row_code int;  declare row_name varchar(20);   declare row_age int;  declare row_sex varchar(10);  declare row_score int; declare row_grade int;   declare row_address varchar(20);  declare row_major varchar(20);  declare go_on int default 1;  declare leveler cursor for select code,name,age,sex,score,grade,address,major from student3;   declare continue handler for NOT FOUND set go_on:=0; open leveler; repeat fetch leveler into row_code,row_name,row_age,row_sex,row_score,row_grade,row_address,row_major;   if(go_on=1)  then select row_code,row_name,row_age,row_sex,row_score,row_grade,row_address,row_major; end if; until go_on=0 end repeat; close leveler;  end $$

注:游标在获取数据的时候十一行一行向下获取的，获取到最后一行，会继续向下获取，但是这个时候会触发越界标志，如果在打印则会获取上一次的结果，
会出现多打印一行的情况。