mysql 存储过程笔记_MySQL存储过程笔记

DELIMITER &&

create procedure firefox98(IN count_id INT,OUT count_num INT)BEGIN

SELECT COUNT(id) INTOcount_numfrom 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

存储过程引入变量；

DELIMITER $create procedureP2()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 procedurep3()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 procedurep4()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) asarea;if width > height then

select '你很胖';

elseif width< height then

select '你挺瘦';else

select '你挺方';end if;end$--p6--顺序，选择，循环;

create procedurep6()begin

declare total int default 0;declare num int default 0;while num<=100do--add num to total ,adn incr the value of num

set num :=num+1;set total := total+num;end while;selecttotal;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;selecttotal;end$

存储过程数据输出;--p8

create procedure p8(in n int,out total int)begin

declare num int default 0;set total := 0;while num

create procedurep10()begin

declare pos int default 0;set pos := floor(5*rand());caseposwhen 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 procedurep12()begin

declare total int default 0;declare i int default 0;

repeatset i := i+1;set total := total +i;

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

create procedurep13()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;opengetuser;fetch getuser intorow_uid,row_name,row_mobile,row_cityid;selectrow_uid,row_name,row_mobile,row_cityid;fetch getuser intorow_uid,row_name,row_mobile,row_cityid;selectrow_uid,row_name,row_mobile,row_cityid;fetch getuser intorow_uid,row_name,row_mobile,row_cityid;selectrow_uid,row_name,row_mobile,row_cityid;fetch getuser intorow_uid,row_name,row_mobile,row_cityid;selectrow_uid,row_name,row_mobile,row_cityid;closegetuser;end$create procedurep14()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;opengetuser;

repeatset i := i+1;fetch getuser intorow_uid,row_name,row_mobile,row_cityid;selectrow_uid,row_name,row_mobile,row_cityid;

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

create procedurep15()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;opengetuser;

repeatfetch getuser intorow_uid,row_name,row_mobile,row_cityid;selectrow_uid,row_name,row_mobile,row_cityid;

until you=0 endrepeat;closegetuser;end$create procedurep16()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;opengetuser;

repeatfetch getuser intorow_uid,row_name,row_mobile,row_cityid;selectrow_uid,row_name,row_mobile,row_cityid;

until you=0 endrepeat;closegetuser;end$--declare exit handler for NOT FOUND statement；--exit与continue的区别是,exit触发后;后面的语句不再执行;

create procedurep16()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;opengetuser;

repeatfetch getuser intorow_uid,row_name,row_mobile,row_cityid;selectrow_uid,row_name,row_mobile,row_cityid;

until you=0 endrepeat;closegetuser;end$--除continue，exit外，还有一种undo handler

逻辑升级create procedurep17()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;opengetuser;fetch getuser intorow_uid,row_name,row_mobile,row_cityid;

repeatselectrow_uid,row_name,row_mobile,row_cityid;fetch getuser intorow_uid,row_name,row_mobile,row_cityid;

until you=0 endrepeat;closegetuser;end$while循环create procedurep18()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;opengetuser;fetch getuser intorow_uid,row_name,row_mobile,row_cityid;while you=1doselectrow_uid,row_name,row_mobile,row_cityid;fetch getuser intorow_uid,row_name,row_mobile,row_cityid;end while;closegetuser;end$

tyclbtF0