MySQL存储过程笔记

DELIMITER &&
create procedure firefox98(IN count_id INT,OUT count_num INT)
BEGIN
SELECT COUNT(id) INTO count_num 
from user
where id = count_id;
END &&
DELIMITER ;

DELIMITER $
create procedure FireFox12(n int,j char(1))
begin
  if j = 'h' then
  select id,mobile,nickname from user where id > n;
  else
  select  id,mobile,nickname from user where id < n;
  end if;
  end$

--存储过程是可以编程的。
--意味着可以使用变量;表达式,控制结构。
--来完成复杂的功能；
--在存储过程中，用declare声明变量；
--格式declare
存储过程引入变量；
DELIMITER $
create procedure P2()
begin
declare num int default 35678;
declare city_id  int default 22;
select concat('user_id  is ' , num , 'city is',city_id );
end$


--存储过程中，变量可以sql语句合法的运算
create procedure p3()
begin
declare num int default 35678;
declare city_id int default 22;
set num :=num +22;
set city_id :=city_id -21;
select concat('user_id  last 20 is num :' , num , 'city is ',city_id );
end$


--if/else 控制结构
/**
if condtion

**/
create procedure p4()
begin
declare age int default 18;
if age >=18




--p5 给存储过程传传参
/**
存储过程的括号里,可以声明参数;
语法是[in/out/inout] 参数名 参数类型
**/
create procedure p5(width int, height int)
begin 
    select concat ('你的面积是' , width * height) as area;

   if width > height  then
   select '你很胖';
   elseif width < height then
   select '你挺瘦';
   else
   select '你挺方';
   end if;
end$




--p6
--顺序，选择，循环;
create procedure p6()
begin
  declare total int default 0; 
  declare num int default 0;
  while num<=100 do
   -- add num to total ,adn incr the value of num
   set num :=num+1;
   set total  :=  total+num;  
   end while;
   select total;
end$



--p7存储过程传参;
create procedure p7(in n int)
begin
  declare total int default 0; 
  declare num int default 0;
  while num<=n do
   -- add num to total ,adn incr the value of num
   set num :=num+1;
   set total  :=  total+num;  
   end while;
   select total;
end$


存储过程数据输出;
--p8
create procedure p8(in n int,out total int)
begin
declare num int default 0;
set total := 0;
while num < n do
        set num := num+1;
        set total := total + num;
        end while;
end$



--how to use "case"
create procedure p10()
begin
    declare pos int default 0;
    set pos := floor(5*rand());
    case pos
       when 1 then select 'still flying';
       when 2 then select 'fall in sea';
       when 3 then select 'in the island';
       else select 'I dont know';
       end case;
end$


--repeat 循环
/*
repeat
sql statement；
until condition end repeat；
*/

create procedure p12()
begin
    declare total int default 0;
    declare i int default 0;
    
    repeat
    set i := i+1;
    set total := total +i;
    until i >= 100 end repeat;
    select total;
end$


--cursor 游标  游标的标示
--1条sql，对应N条资源，取出资源的接口/句柄,就是游标
--沿着游标 ,可以一次取出1行; 
--declare 声明;declare 游标名 cursor for select_statement;
--open打开; open 游标名
--fetch 取值; fetch游标名 into var1，var2[，....]
--close 关闭，close 游标名;


create procedure p13()
begin
 declare row_uid int;
 declare row_name varchar(20);
 declare row_mobile char(11);
 declare row_cityid int;
 declare getuser cursor for select id,nickname,mobile,city_id from user;
 open getuser;
 
 fetch getuser into row_uid,row_name,row_mobile,row_cityid;
 select row_uid,row_name,row_mobile,row_cityid;
 
 fetch getuser into row_uid,row_name,row_mobile,row_cityid;
 select row_uid,row_name,row_mobile,row_cityid;

 fetch getuser into row_uid,row_name,row_mobile,row_cityid;
 select row_uid,row_name,row_mobile,row_cityid;

 fetch getuser into row_uid,row_name,row_mobile,row_cityid;
 select row_uid,row_name,row_mobile,row_cityid;
 close getuser;
 end$



create procedure p14()
begin 
 declare row_uid int;
 declare row_name varchar(20);
 declare row_mobile char(11);
 declare row_cityid int;
 declare cnt int default 0;
 declare i int default 0;
 declare getuser cursor for select id,nickname,mobile,city_id from user;
 select count(*) into cnt from user;
 open getuser;
 repeat
     set i := i+1;
     fetch getuser into row_uid,row_name,row_mobile,row_cityid;
     select row_uid,row_name,row_mobile,row_cityid;
  until i >= cnt end repeat;

  close getuser;
  end$






--游标取值越界时，有没有标识?利用表示来结束
--在mysql cursor中，可以声明declare continue handler来操作1个越界标识;
--declare continue handler for not found statement；

create procedure p15()
begin 
 declare row_uid int;
 declare row_name varchar(20);
 declare row_mobile char(11);
 declare row_cityid int;

 
 declare you int default 1;

 declare getuser cursor for select id,nickname,mobile,city_id from user;
 declare continue handler for not FOUND set you :=0;

 open getuser;
 repeat
   
     fetch getuser into row_uid,row_name,row_mobile,row_cityid;
     select row_uid,row_name,row_mobile,row_cityid;
  until you=0 end repeat;

  close getuser;
  end$








create procedure p16()
begin 
 declare row_uid int;
 declare row_name varchar(20);
 declare row_mobile char(11);
 declare row_cityid int;

 
 declare you int default 1;

 declare getuser cursor for select id,nickname,mobile,city_id from user;
 declare continue handler for not FOUND set you :=0;

 open getuser;
 repeat
   
     fetch getuser into row_uid,row_name,row_mobile,row_cityid;
     select row_uid,row_name,row_mobile,row_cityid;
  until you=0 end repeat;

  close getuser;
  end$



--declare exit handler for NOT FOUND statement；
--exit与continue的区别是,exit触发后;后面的语句不再执行;

create procedure p16()
begin 
 declare row_uid int;
 declare row_name varchar(20);
 declare row_mobile char(11);
 declare row_cityid int;

 
 declare you int default 1;

 declare getuser cursor for select id,nickname,mobile,city_id from user;
 declare exit handler for not FOUND set you :=0;

 open getuser;
 repeat
   
     fetch getuser into row_uid,row_name,row_mobile,row_cityid;
     select row_uid,row_name,row_mobile,row_cityid;
  until you=0 end repeat;

  close getuser;
  end$

 --除continue，exit外，还有一种undo handler




逻辑升级
create procedure p17()
begin 
 declare row_uid int;
 declare row_name varchar(20);
 declare row_mobile char(11);
 declare row_cityid int;

 
 declare you int default 1;

 declare getuser cursor for select id,nickname,mobile,city_id from user where 0;
 declare exit handler for not FOUND set you :=0;

 open getuser;
     fetch getuser into row_uid,row_name,row_mobile,row_cityid;
 repeat
      
     select row_uid,row_name,row_mobile,row_cityid; 
     fetch getuser into row_uid,row_name,row_mobile,row_cityid;
  until you=0 end repeat;

  close getuser;
  end$




while 循环

create procedure p18()
begin 
 declare row_uid int;
 declare row_name varchar(20);
 declare row_mobile char(11);
 declare row_cityid int;

 
 declare you int default 1;

 declare getuser cursor for select id,nickname,mobile,city_id from user where 0;
 declare exit handler for not FOUND set you :=0;

 open getuser;
     fetch getuser into row_uid,row_name,row_mobile,row_cityid;
     
 
   while you=1 do
     select row_uid,row_name,row_mobile,row_cityid; 
     fetch getuser into row_uid,row_name,row_mobile,row_cityid;
    end while;


  close getuser;
  end$

  tyclbtF0