mysql_存储过程&游标、循环

存储过程
    概念: 就是一组为了完成特定功能的sql语句集

创建存储过程
【没有参数】
        drop procedure if exists 名字;
        delimiter $$
		create procedure 名字()
		begin
              sql代码块
		end;
		delimiter $$


【有参数】
           in 参数传进来  


        drop procedure if exists 名字;
        delimiter $$
		create procedure 名字(in 参数1 数据类型1,out 参数2 数据类型2....)
		begin
              sql代码块 
		end;
		delimiter $$

        set @b = "";
        call  名字(值1,@b);
********************************************************************************

调用：
     call 名字();
     call 名字;

     自定义函数是解决特定的需求 

     存储过程会比自定函数更强大。

     drop procedure if exists xiaoyang;
     delimiter $$
	 create procedure xiaoyang()
	 begin
         select "虫虫你好漂亮！！";
	 end $$
     delimiter ;




    drop procedure if exists xiaoyang2;
     delimiter $$
	 create procedure xiaoyang2(in n1 int(11),out n2 varchar(100))
	 begin
        if(n1 <100)
		  then set n2 := concat(n1,"<100");
		elseif n1 = 100
		  then set n2 := concat(n1,"=100");
		else 
		  set n2 := concat(n1,">100");
		end if;
	 end $$
     delimiter ;

     set @m = "";
     call xiaoyang2(100,@m);
     select @m;




自定义函数里面不能返回结果集。【select】
存储过程可以


赋值：
     select 值 into  变量;

     set 变量 = 值;
     set 变量 := 值;  [推荐方式]
********************************************************************************

查看存储过程
show procedure status;
或
select db, name, type from mysql.proc where db = 'test' and type='PROCEDURE';
********************************************************************************

显示创建存储过程的语句
show create procedure p1\G
********************************************************************************

删除存储过程
drop procedure if exists p1;
********************************************************************************

在存储过程里边可以使用declare声明变量
格式: declare 变量名 变量类型 [default 默认值];

drop procedure if exists p2;
delimiter $$
	create procedure p2()
	begin
		declare age int default 20;
		declare name varchar(30) default '狗蛋';
		select concat('姓名:', name, '     年龄:', age);
	end$$
delimiter ;
call p2();
********************************************************************************

可以数算运算
delimiter $$
	create procedure p3()
	begin
		declare age int default 40;
		set age := age + 10;           #赋值
		select age from dual;
	end$$
delimiter ;
call p3();
********************************************************************************

可以使用逻辑判断语句
判断语法:
    if 要判断的语句 then
        要执行的sql(statement)
    else
        要执行的sql(statement)
    end if;

drop procedure if exists p4;
delimiter $$
	create procedure p4()
	begin
		declare age int default 20;
		if age > 60 then
			select '已经老了' from dual;
		else
			select '一枝花' as '威廉' from dual;
		end if;
	end$$
delimiter ;
call p4();
********************************************************************************

还可以给存储过程传参
存储过程里括号可以用来声明参数
语法: [in | out | inout] 参数名 参数数据类型

delimiter $$
	create procedure p5(u_name varchar(30), u_age int)
	begin
		if u_name = '胖胖' then
			select '其实一点也不胖' as '胖胖';
		elseif u_name = '小红' then
			select '她真的很好看' as '小红';
		else
			select '这个我就不知道了' as '未知的';
		end if;
	end$$
delimiter ;
call p5('胖胖', 18);
call p5('小红', 18);
call p5('狗剩', 18);
********************************************************************************

可以使用循环
--在存储过程里使用循环求 1 到 10 的和;
delimiter $$
	create procedure p6()
	begin
		declare num int default 0;
		declare i int default 0;
		while i <= 10 do
			set num := num + i;
			set i := i + 1;
		end while;
		select num as '总和';
	end$$
delimiter ;
call p6();
********************************************************************************

参数 [in | out | inout] 的使用
语法: [in | out | inout] 参数名 参数数据类型

--in
--求1到指定值的和
drop procedure if exists p7;
delimiter $$
	create procedure p7(in n int)
	begin
		declare num int default 0;
		declare i int default 0;
		while i <= n do
			set num := num + i;
			set i := i + 1;
		end while;
		select num as '总和';
	end$$
delimiter ;
call p7(100);


--out 
--需求: 把计算出来的和返回出来
drop procedure if exists p8;
delimiter $$
	create procedure p8(in n int, out num int)
	begin
		declare i int default 0;
		set num := 0;
		while i <= n do
			set num := num + i;
			set i := i + 1;
		end while;
	end$$
delimiter ;
call p8(100, @value);    @name 表示全局的用户变量
select @value as '总和';


--inout
delimiter $$
	create procedure p9(inout age tinyint unsigned)
	begin
		set age := age +18;
	end$$
delimiter ;
set @value = 10;
call p9(@value);
select @value;
********************************************************************************

循环(repeat)
drop procedure if exists p10;
delimiter $$
	create procedure p10()
	begin
		declare i int default 100;
		# repeat
		while i < 10 do
			select i as 'i';
			set i := i + 1;
		end while;
		# until i > 10 end repeat;    #意思是在 i的值 大于10之前 这个条件就满足
	end$$
delimiter ;
call p10();
repeat循环是先执行再判断
while循环是先判断再执行
********************************************************************************

可以使用case 语法
delimiter $$
	create procedure p11(in u_name varchar(30))
	begin
		case u_name
			when '狗蛋' then
				select '狗蛋一点也不蛋' as '狗蛋';
			when '狗剩' then
				select '这是狗蛋他哥' as '狗剩';
			when '铁蛋' then
				select '这狗蛋他老表' as '铁蛋';
			else
				select '不知道他是谁了' as '未知的';
		end case;
	end$$
delimiter ;
call p11('铁蛋');
call p11('狗剩');
call p11('蛋蛋');
********************************************************************************


**游标
一条sql，对应n着条结果集的资源, 取出资源的接口/句柄, 就是游标沿着游标可以一次取出一行
--需求: 使用游标获取到第一行的数据;

delimiter $$
    create procedure p12()
    begin
        declare row_no int;
        declare row_name varchar(30);
        declare row_sex varchar(10);

        declare getstu cursor for select stu_no, stu_name, stu_sex from student;     #定义一个游标

        open getstu;  #打开游标
            fetch getstu into row_no, row_name, row_sex;      #获取游标
            select row_no, row_name, row_sex;
        close getstu;  #关闭游标
    end$$
delimiter ;
call p12();
********************************************************************************

--需求: 使用游标获取到所有的数据;
drop procedure if exists p13;
delimiter $$
    create procedure p13()
    begin
        declare row_no int;
        declare row_name varchar(30);
        declare row_sex varchar(10);

        declare row int default 0;
        declare i int default 0;

        declare getstu cursor for select stu_no, stu_name, stu_sex from student;     #定义一个游标
        select count(*) into row from student;

        open getstu;  #打开游标
            repeat
                fetch getstu into row_no, row_name, row_sex;      #获取游标
                select row_no, row_name, row_sex;
                set i := i + 1;
            until i >= row end repeat;
        close getstu;  #关闭游标
    end$$
delimiter ;
call p13();
********************************************************************************

--declare continue handler for NOT FOUND的使用

drop procedure if exists p14;
delimiter $$
    create procedure p14()
    begin
        declare row_no int;
        declare row_name varchar(30);
        declare row_sex varchar(10);

        declare i int default 0;

        declare getstu cursor for select stu_no, stu_name, stu_sex from student where stu_no = 1;     #定义一个游标
        # declare continue handler for NOT FOUND set i := 1;      #声明如果没有返回时程序会继续执行 并且把i的值设置为1；
        declare exit handler for NOT FOUND set i := 1;            #exit 退出

        open getstu;  #打开游标
            repeat
                fetch getstu into row_no, row_name, row_sex;      #获取游标
                # if i = 0 then
                    select row_no, row_name, row_sex, i;
                # end if;
            until i = 1 end repeat;      
        close getstu;  #关闭游标
    end$$
delimiter ;
call p14();

游标 ：遍历整个表 ，对表做出一些改变

1. 声明游标   declare `名字` cursor for 表名;  【select id,name from xiaomo】  ======打造宝剑

    eg: declare xiaoyang cursor for select id,name from xiaomo;


2. 打开游标 open 游标; 
        eg: open xiaoyang;    # 取出数据    =====  拔出宝剑



3. 取数据 fetch 游标的名字 into 变量1,变量2...;   
       eg: fetch xiaoyang into mid,mname;  ======寻找敌人，斩于剑下
    脏话：
       if(变量1 = "脏话1" or 变量1.... .... ...)
         then update xiaomo set manme = "*****" where id = 变量1;

4.关闭游标 close 游标的名字;     ===== 宝剑回樵
       eg: close xiaoyang;


来一个入门案例：

+----+------------+------+------+------+-------+
| id | name       | age  | sex  | xpri | money |
+----+------------+------+------+------+-------+
|  1 | I love you |    8 | NULL | NULL |  NULL |
| 12 | 风度迷彩   |   17 | NULL | NULL |   600 |
| 13 | 虫虫       |   27 | NULL | NULL |   600 |
+----+------------+------+------+------+-------+


需求：如果用户小于10岁，更改name为  
    如果用户大于10岁小于18岁，更改name为  我是未成年，你别骗我
    如果他大于18岁， 更改name为   我是成年人，知道潜规则


    drop procedure if exists mypro;
    delimiter $$
    create procedure mypro()
    begin
        declare mid int(11); #声明一个变量mid，数据类型是 int(11)
        declare mage int(11); 
        declare mname char(80);
        declare xiaotao cursor for select id,age,name from xiaomo; #声明游标，名字为xiaotao，遍历的是xiaomo表的id和age字段。
        open xiaotao;  #打开游标
        fetch xiaotao into mid,mage,mname;  #从游标里面取出数据，把id取到的值赋给mid，把age取到的值赋给mage

        if(mage<10)
          then update xiaomo set name = concat(mname,"我是小孩子，读书少") where id = mid;
        end if;


        fetch xiaotao into mid,mage,mname;  #从游标里面取出数据，把id取到的值赋给mid，把age取到的值赋给mage

        if(mage>10 and mage<18)
          then update xiaomo set name = concat(mname,"我是未成年，你别骗我") where id = mid;
        end if;

        fetch xiaotao into mid,mage,mname;  #从游标里面取出数据，把id取到的值赋给mid，把age取到的值赋给mage

        if(mage>18)
          then update xiaomo set name = concat(mname,"我是成年人，知道潜规则") where id = mid;
        end if;

        close xiaotao;
    end $$
    delimiter ;


mysql> select * from xiaomo;
+----+------------------------------------------------+------+------+------+-------+
| id | name                                           | age  | sex  | xpri | money |
+----+------------------------------------------------+------+------+------+-------+
|  1 | I love you我是小孩子，读书少我是小孩子，读书少 |    8 | NULL | NULL |  NULL |
| 12 | 风度迷彩我是未成年，你别骗我                   |   17 | NULL | NULL |   600 |
| 13 | 虫虫我是成年人，知道潜规则                     |   27 | NULL | NULL |   600 |
+----+------------------------------------------------+------+------+------+-------+




mysql> call mypro;
+------+------+
| id号 | 年龄 |
+------+------+
|    1 |    8 |
+------+------+



mysql> call mypro;
+------+------+
| id号 | 年龄 |
+------+------+
|    1 |    8 |
+------+------+
1 row in set (0.00 sec)

+------+------+
| id号 | 年龄 |
+------+------+
|   12 |   17 |
+------+------+
1 row in set (0.01 sec)

+------+------+
| id号 | 年龄 |
+------+------+
|   13 |   27 |
+------+------+
1 row in set (0.01 sec)



    循环

    repeat
      业务代码
      until 条件  【退出】
    end repeat;


存储过程
    第一次加载到内存的时候慢，以后调用的时候就好啦。


改进版本
drop procedure if exists mypro;
    delimiter $$
    create procedure mypro()
    begin
        declare mid int(11); #声明一个变量mid，数据类型是 int(11)
        declare mage int(11); 
        declare mname char(80);
        declare flag int default true;  #申明一个变量，默认值是为true
        declare xiaotao cursor for select id,age,name from xiaomo; #声明游标，名字为xiaotao，遍历的是xiaomo表的id和age字段。

        declare continue handler for not found set flag = false; #遍历没有数据，就更改标志flag为false
                          # declare continue handler for not found 条件;
        open xiaotao;  #打开游标

        repeat
          fetch xiaotao into mid,mage,mname;  #从游标里面取出数据，把id取到的值赋给mid，把age取到的值赋给mage

          if(mage<10)
             then update xiaomo set name = concat(mname,"我是小孩子，读书少") where id = mid;
          elseif(mage>10 and mage<18)
             then update xiaomo set name = concat(mname,"我是未成年，你别骗我") where id = mid;
          else
             update xiaomo set name = concat(mname,"我是成年人，知道潜规则") where id = mid;
          end if;

          until flag = false
        end repeat;

        close xiaotao;  # 关闭游标
    end $$
    delimiter ;


    call mypro;
    call mypro();



mysql> select * from xiaomo;
+----+------------------------------------------------------------------+------+------+------+-------+
| id | name                                                             | age  | sex  | xpri | money |
+----+------------------------------------------------------------------+------+------+------+-------+
|  1 | I love you我是小孩子，读书少我是小孩子，读书少我是小孩子，读书少 |    8 | NULL | NULL |  NULL |
| 12 | 风度迷彩我是未成年，你别骗我我是未成年，你别骗我                 |   17 | NULL | NULL |   600 |
| 13 | 虫虫我是成年人，知道潜规则我是成年人，知道潜规则                 |   27 | NULL | NULL |   600 |
+----+------------------------------------------------------------------+------+------+------+-------+





drop function if exists mypro;
    delimiter $$
    create function mypro()returns varchar(100)
    begin
        declare mid int(11); #声明一个变量mid，数据类型是 int(11)
        declare mage int(11); 
        declare mname char(80);
        declare flag int default true;  #申明一个变量，默认值是为true
        declare xiaotao cursor for select id,age,name from xiaomo; #声明游标，名字为xiaotao，遍历的是xiaomo表的id和age字段。

        declare continue handler for not found set flag = false; #遍历没有数据，就更改标志flag为false 
                          # declare continue handler for not found 条件;
        open xiaotao;  #打开游标

        repeat
          fetch xiaotao into mid,mage,mname;  #从游标里面取出数据，把id取到的值赋给mid，把age取到的值赋给mage

          if(mage<10)
             then update xiaomo set name = concat(mname,"我是小孩子，读书少") where id = mid;
          elseif(mage>10 and mage<18)
             then update xiaomo set name = concat(mname,"我是未成年，你别骗我") where id = mid;
          else
             update xiaomo set name = concat(mname,"我是成年人，知道潜规则") where id = mid;
          end if;

          until flag = false
        end repeat;

        close xiaotao;  # 关闭游标

        return "ok";
    end $$
    delimiter ;



    ERROR 1406 (22001): Data too long for column 'name' at row 1

    1406 : error-code  错误代码

    22001 :  sqlstate   错误状态




mysql> select * from xiaomo;
+----+--------+------+------+------+-------+
| id | name   | age  | sex  | xpri | money |
+----+--------+------+------+------+-------+
|  1 | gezi1  |    8 | NULL | NULL |  NULL |
| 12 | gezi12 |   17 | NULL | NULL |   600 |
| 13 | gezi13 |   27 | NULL | NULL |   600 |
+----+--------+------+------+------+-------+

ERROR 1406 (22001): Data too long for column 'name' at row 1


ERROR 1329 (02000): No data - zero rows fetched, selected, or processed

自定义condition

 declare mycondition condition for 错误代码; 

   eg:  declare mycondition condition for 1406;

 declare mycondition condition for 错误状态; 

   eg:  declare mycondition condition for '22001';

    drop procedure if exists mpro;
    delimiter $$
    create procedure mpro()
    begin 
        declare mid int(11);
        declare mname varchar(100);
        declare flag int default true;

        declare mycondition condition for 1406;  #  错误状态
        declare mycondition1 condition for 1259;  #  错误状态
        declare mycondition2 condition for 1406;  #  错误状态
        declare mycondition3 condition for 1406;  #  错误状态
        declare mycondition4 condition for 1406;  #  错误状态
        declare mycondition5 condition for 1406;  #  错误状态
        declare mycondition6 condition for 1406;  #  错误状态 

        declare cur cursor for select id,name from xiaomo;
        if(.....)
        then
        declare continue handler for mycondition set flag = false;   # not found 属于一种状态【condition】
        elseif(/.///)
          then declare continue handler for mycondition set flag = false;   # not found 属于一种状态【condition】

        open cur;
        repeat
            fetch cur into mid,mname;
            update xiaomo set name = concat(name,"hello!!") where id = mid;
            until flag =false
        end repeat;
        close cur;
    end $$
    delimiter ;





    drop procedure if exists mpro;
    delimiter $$
    create procedure mpro()
    begin 
        declare mid int(11);
        declare mname varchar(100);
        declare flag int default true;

        declare mycondition condition for '22001';  #  错误状态

        declare cur cursor for select id,name from xiaomo;
        declare continue handler for mycondition set flag = false;   # not found 属于一种状态【condition】
        open cur;
        repeat
            fetch cur into mid,mname;
            update xiaomo set name = concat(name,"hello!!") where id = mid;
            until flag =false
        end repeat;
        close cur;
    end $$
    delimiter ;



    600万

    create table bw(
      id int(11) primary key auto_increment,
      name varchar(11),
      age tinyint(4) unsigned,
      birth timestamp
    );



      timestamp: 时间蹉

       while 条件 do
          sql代码
       end while;



-----------------------------------------
默认引擎
| bw    | CREATE TABLE `bw` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(11) DEFAULT NULL,
  `age` tinyint(4) unsigned DEFAULT NULL,
  `birth` timestamp NOT NULL DEFAULT CURR
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |


 innodb 索引的情况

  主键索引  聚簇索引
    =====》 行数据
    页面分裂

------------------------------------------

修改表的引擎
alter table bw engine myisam;

 bw    | CREATE TABLE `bw` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(11) DEFAULT NULL,
 `age` tinyint(4) unsigned DEFAULT NULL,
 `birth` timestamp NOT NULL DEFAULT CURREN
 PRIMARY KEY (`id`)
 ENGINE=MyISAM DEFAULT CHARSET=utf8 |



插入1000条数据
    drop procedure if exists mypro;
    delimiter $$
	create procedure mypro()
	begin
       declare i int(11) default 0;
       declare mage int(11) default 10;
	   while i<1000 do
          set i:= i+1;
          set mage = floor(rand()*110);  # 0 110
          insert into bw(name,age) values(concat("monkey",i),mage);
	   end while;       
	end $$
    delimiter ;

    mysql> call mypro;
    Query OK, 1 row affected (0.03 sec)




    mysql> call mypro();
    Query OK, 1 row affected (27.74 sec)