1、创建:create procedure name (out/in/inout varible_name varible_type)
2、调用:call name(@t);select @t;
3、创建:create function name(varible_name varible_type) returns return_type
4、调用: select name(20);
5、查看过程:
show create procedure p1;
show create function p1;
6、删除过程
drop procedure p1;
drop function p1;
7、声明局部变量:
declare tax float default 0.10;
代码:
delimiter // #暂时将分隔符改变成//
create function largest_order() returns int
BEGIN
declare this_id int;
declare this_amount float;
declare l_amount float default 0.0;
declare l_id int;
declare done int default 0; #done作为循环标记,初始值为0(false),终止值为1(true)
declare continue handler for sqlstate '02000' set done=1;
#continue handler句柄,sqlstate‘0200’为一个异常(无法再找到行时)
declare c1 cursor for select orderid,amount from orders;
#cursor游标,对后面的sql语句当下指针标记为一个数组;
open c1; #执行游标
repeat
fetch c1 into this_id,this_amount; #将游标的标记装入this_id,this_amount
if not done then
if this_amount>l_amount then
set l_amount = this_amount;
set l_id = this_id;
end if;
end if;
until done end repeat;
close c1; #关闭游标
return this_id;
END//
delimiter ; #将分隔符改回;
select largest_order();