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
