001、
查看存储过程
show procedure status
查看指定存储过程
show create procedure sp_name
show procedure status where db='数据库名';
调用存储过程
call sp_name()
删除存储过程
DROP PROCEDURE sp_set
002、
一.创建存储过程
1.基本语法:
create procedure sp_name()
begin
………
end
2.参数传递
二.调用存储过程
1.基本语法:call sp_name()
注意:存储过程名称后面必须加括号,哪怕该存储过程没有参数传递
三.删除存储过程
1.基本语法:
drop procedure sp_name
drop procedure if exists sp_name
2.注意事项
(1)不能在一个存储过程中删除另一个存储过程,只能调用另一个存储过程
四.区块,条件,循环
1.区块定义,常用
begin
……
end;
也可以给区块起别名,如:
lable:begin
………
end lable;
可以用leave lable;跳出区块,执行区块以后的代码
2.条件语句
if …… then ……
elseif …… then ……
end if;
3.循环语句
(1)while循环
[label:] WHILE …… DO
……
END WHILE [label] ;
(2)loop循环
[label:] LOOP
……
if …… then leave label ; //跳出循环
END LOOP [label];
(3)repeat until循环
[label:] REPEAT
……
UNTIL expression
END REPEAT [label] ;
003、
Strict Mode的描述:
根据 mysql5.0以上版本 strict mode (STRICT_TRANS_TABLES) 的限制:
1).不支持对not null字段插入null值
2).不支持对自增长字段插入''值,可插入null值
3).不支持 text 字段有默认值
005、
CREATE PROCEDURE dayrepeat()
BEGIN
SELECT a.IntersectionUnit as '城区',
a.IntersectionName as '路口',
a.SCar+a.MCar+a.BCar as '总流量'
FROM allintersection a;
END;
call dayrepeat();
006、
CREATE PROCEDURE test1(out mi DECIMAL,out ma DECIMAL,out av DECIMAL)
BEGIN
SELECT min(allintersection.SCar) AS 'min' INTO mi FROM allintersection ;
SELECT max(allintersection.SCar) AS 'max' INTO ma FROM allintersection ;
SELECT avg(allintersection.SCar) AS 'avg' INTO av FROM allintersection ;
END;
CALL test1(@a,@b,@c);
SELECT @a AS '最小值',
@b AS '最大值',
@c AS '平均值'
007、
DROP PROCEDURE isTotal;
create procedure isTotal(
in InName BLOB,
OUT Total decimal
)
begin
select SUM(SCar+MCar+BCar) into Total
from allintersection
where allintersection.IntersectionName = InName ;
end;
call isTotal('德胜路东新口路',@total);
select @total as ;
008、
DROP PROCEDURE IF EXISTS test2;
CREATE PROCEDURE test2(IN con DOUBLE, OUT result DOUBLE)
BEGIN
DECLARE temp DOUBLE;
IF con=1 THEN SET temp= con*1;
ELSEIF con=2 THEN SET temp= con*2;
ELSEIF con=3 THEN SET temp= con*3;
ELSE SET temp= con*0;
END IF;
SET result= temp;
END;
CALL test2(4,@result);
SELECT @result as '结果';
009、开启事务、异常处理
BEGIN
declare exit Handler for SQLException
begin
set result = -1;
rollback;
end;
START TRANSACTION;
select count(*) into result from role_buffs where model_id = buffModelId and role_id = memberId and (nowTime - quit_time) < remain_time ; #检查是否有退出buff
if result = 0 then
insert into gang_member(member_id,member_gangID,member_post,add_date) values(memberId,gangID,post,now());
update game_role set gang_id = gangID where role_id = memberId;
set result = 1;
else
set result = -2;
end if;
commit;
END