精通
英语
和
开源
,
擅长
开发
与
培训
,
胸怀四海
第一信赖
Workbench是mysql公司开发的mysql开发客户端,功能强大,兼容性好,使用Workbench开发存储过程的界面如下:
在Apply后,会检查代码,如果有错,下面提示窗口会有红色叉图标行,也会弹出错误窗口,如下:
上面提示You have an error in your SQL syntax,near',单引号后是错误的存储过程代码。
存储过程里不支持return,用LEAVE proc_label; -- 跳出存储过程,proc_label是个别名,在begin前起的别名
Select into2个变量时,不能分开写,不能写成2个into,要into前后带逗号 ,比如
acolume into av,bcolume into bv,这样不对。
改成acolume,bcolume into av,bv,这样可以。
行结束带分号,除非控制语句后,比如then不用带。
字段名前加`,和变量区分开。
赋值语句记的用set开头,不支持象编程语言哪样直接等号赋值。
CREATE DEFINER=`root`@`localhost` PROCEDURE `TicketMoney`(in dmoney double,IN pcompany varchar(128),in serveruser varchar(32),in num1 varchar(128),in localuser varchar(32),in lastcode varchar(64),out tmstat varchar(32),out dgap double)
tmbegin:BEGIN
DECLARE t_error INTEGER DEFAULT 1;
declare flowid integer;
declare tmrunmny decimal(19,6) default -1;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=-1;
set tmstat='未定义';
set dgap=-1;
select tmstatus,`ticketmoney`.`runmoney` into tmstat,tmrunmny from `ticketmoney` where `ticketmoney`.`company`=pcompany and `ticketmoney`.etermuser=serveruser;
if tmstat='未定义' or tmstat='受限' then
leave tmbegin;
END IF;
start transaction;
insert into tickethis(thtime,etermuser,ticketnum,runmoney,lastmoney,lastlocaluser,company,lastaction)
values(now(),serveruser,num,tmrunmny,dmoney,localuser,pcompany,lastcode);
IF t_error = -1 THEN
begin
set tmstat='his失败';
ROLLBACK;
leave tmbegin;
end;
end if;
select LAST_INSERT_ID() into flowid;
update ticketmoney set `runmoney`=`runmoney`+dmoney,
ticketmoney.ticketnum=num,
ticketmoney.lastmoney=dmoney,
ticketmoney.lastlocaluser=localuser,
ticketmoney.lastaction=lastcode,
ticketmoney.lastflowid=flowid,
lastthtime=now(),
tmstatus=case when `limit`>(runmoney+dmoney) then '可用' else '受限' end
where ticketmoney.company=company and etermuser=serveruser;
IF t_error = -1 THEN
begin
set tmstat='update失败';
ROLLBACK;
leave tmbegin;
end;
end if;
commit;
select tmstatus,`limit`-`runmoney` into tmstat,dgap from ticketmoney where ticketmoney.company=pcompany and etermuser=serveruser;
END