-- 存储过程的创建语法 create procedure procedureName() begin sql语句 end$ -- 查看已有的procedure -- show procedure status -- 调用存储过程 -- call procedure()$ -- 删除存储过程 -- drop procedure procedureName$ -- 创建存储过程p1 create procedure p1() begin select 'hello' from dual; end$ -- 引入变量p2 -- 在存储过程中,用declare声明变量 -- 格式:declare 变量名 变量类型 [default 默认值] create procedure p2() begin declare age int default 90; declare height int default 175; select concat('年龄',age,'升高',height) from dual; end$ -- 使用变量运算p3 -- 存储过程中变量可以使用sql语句中合法的运算,如+-*、 -- 注意的是,运算的结果,如何赋值给变量 -- set 变量名:=expression create procedure p3() begin declare age tinyint unsigned default 90; declare height tinyint unsigned default 175; select age,height from dual; set age:=age+20; select concat('20年后年龄',age,岁) from dual; end$ -- 使用表达式p4 -- if/else控制结构 -- if condition then -- statement -- else -- end create procedure p4() begin declare age tinyint unsigned default 90; declare height tinyint unsigned default 175; if age>70 then select '古稀之年' from dual; else select '风华正茂' from dual; end if; end$ -- 流程控制p6 -- 顺序,选择,循环 create procedure p5() begin declare age int default 90; declare height int default 175; declare gender char(1) default '男'; if gender='男' then select '你是男的' from dual; end if; if height>175 then select '身高中等' from dual; end if; if age<20 then select '小鲜肉' from dual; elseif age<=50 then select '年轻有为' from dual; elseif age<=70 then select '安享天伦' from dual; else select '佩服佩服' from dual; end if; end$ -- 流程控制case create procedure p6() begin declare pos int default 0; set pos:=floor(5*rand()); case pos when 1 then select 'cat'; when 2 then select 'dog'; when 3 then select 'pig'; else select 'human being'; end case end$ -- 小练习求1-100之和 -- 过程名称不能定义为sum create procedure p6() begin declare total int default 0; declare num int default 0; while num<100 do set total:=total+num; set num:=num+1; end while; select total; end$ -- 存储过程传参p5 -- 存储过程的括号里,可以声明参数 -- 语法是[in/out/inout] 参数名 参数类型 -- 求1-N之和(传参输入in) create procedure p7(in n int) begin declare total int default 0; declare num int default 0; while num<n do set num:=num+1; set total:=total+num; end while; select total; end$ -- 将结果输出out -- 调用call p8(100 @sumary) -- 查值select @sumary 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$ -- repeat 循环 create procedure p9() begin declare i int default 0; repeat set i:=i+1; select i; until i>10 end repeat; select i; end$ -- 循环求和 create procedure p10(in n int) begin declare i int default 0; declare total int default 0; repeat set i:=i+1; set total:=total+i; until i=n end repeat; select total; end$ -- cursor 游标 游动的标志 -- 1条sql,对应N条结果集的资源,取出资源的接口/句柄,就是游标 -- 沿着游标,可以一次取出一行,实现步骤 -- declare声明:declare 游标名称 cursor for select_statement -- open打开:open 游标名 -- fetch取值:fetch 游标名 into val1,val2[,...] -- close关闭:close 游标名称 create procedure p11() begin declare row_gid int; declare row_num int; declare row_name varchar(20); declare getgoods cursor for select gid,num,name from goods; open getgoods; fetch getgoods into row_gid,row_num,row_name; select row_num,row_name; close getgoods; end$ -- 游标取值越界时,有没有标识?怎么利用标识来结束 -- 在mysql cursor中可以declare continue handler来操作一个越界标识 -- declare continue handler for NOT FOUND statement; create procedure p12() begin declare row_gid int; declare row_num int; declare row_name varchar(20); declare you int default 1; declare getgoods cursor for select gid,num,name from goods; declare continue handler for NOT FOUND set you:=0; open getgoods; repeat fetch getgoods into row_gid,row_num,row_name; select row_num,row_name; until you>=0 end repeat; close getgoods; end$ -- declare exit handler for NOT FOUND statement; -- exit与continue的区别是,exit触发后,后面的语句不在执行 create procedure p13() begin declare row_gid int; declare row_num int; declare row_name varchar(20); declare you int default 1; declare getgoods cursor for select gid,num,name from goods; declare exit handler for NOT FOUND set you:=0; open getgoods; repeat fetch getgoods into row_gid,row_num,row_name; select row_num,row_name; until you=0 end repeat; close getgoods; end$