我用Navicat for MySQL写存储,大概过程 函数-新建函数-过程-下一步-填写参数-完成
以下是参数
`@uid` int,`@title` varchar(20),`@count` int,`@price` decimal,`@state` int,OUT `@result` varchar(20)
以下是代码
BEGIN
#Routine body goes here...
select @acount:=count,@afree_count:=free_count from member_stock where uid=@uid;
if @acount<@count then
SET @result='您持有的股份不足';
Else
BEGIN
#插入出售信息
insert productInfo(uid,title,totalcount,yucount,price,addtime,state)
values (@uid,@title,@count,@count,@price,NOW(),@state);
SELECT @pid:=MAX(id) from productInfo where uid=@uid;
#更新持有股数
update member_stock set count=count-@count,free_count=free_count+@count where uid=@uid;
#写入日志
insert record_sellInfo(uid,pid,rType,count,free_count,AddTime)
values(@uid,@pid,'发布出售信息',@acount-@count,@afree_count+@count,NOW());
SET @result='SUCCESS';
END;
end if;
END
保存不会出错,运行的时候问题就来啦
[SQL]
call SellInfo(1,'',1000,10,0,@result);
[Err] 1048 - Column 'uid' cannot be null
求高手指教。mysql存储
以下是参数
`@uid` int,`@title` varchar(20),`@count` int,`@price` decimal,`@state` int,OUT `@result` varchar(20)
以下是代码
BEGIN
#Routine body goes here...
select @acount:=count,@afree_count:=free_count from member_stock where uid=@uid;
if @acount<@count then
SET @result='您持有的股份不足';
Else
BEGIN
#插入出售信息
insert productInfo(uid,title,totalcount,yucount,price,addtime,state)
values (@uid,@title,@count,@count,@price,NOW(),@state);
SELECT @pid:=MAX(id) from productInfo where uid=@uid;
#更新持有股数
update member_stock set count=count-@count,free_count=free_count+@count where uid=@uid;
#写入日志
insert record_sellInfo(uid,pid,rType,count,free_count,AddTime)
values(@uid,@pid,'发布出售信息',@acount-@count,@afree_count+@count,NOW());
SET @result='SUCCESS';
END;
end if;
END
保存不会出错,运行的时候问题就来啦
[SQL]
call SellInfo(1,'',1000,10,0,@result);
[Err] 1048 - Column 'uid' cannot be null
求高手指教。mysql存储
首先参数部分改成
uid int,title varchar(20),pcount int,price decimal,state int,out result int
跟之前比较去掉@
内容块
BEGIN
#代表注释
#定义变量也不用@
declare acount int;
declare afree_count int;
declare pid int;
select count,free_count from member_stock where uid=uid INTO acount,afree_count;
#查询后给变量赋值得用INTO
if acount<pcount then
SET result=-1;
SELECT result;#输出返回值
Else
BEGIN
#插入出售信息
insert productInfo(uid,title,totalcount,yucount,price,addtime,state)
values (uid,title,pcount,pcount,price,NOW(),state);
SELECT MAX(id) from productInfo where uid=uid INTO pid;
#更新持有股数
update member_stock set count=count-pcount,free_count=free_count+pcount where uid=uid;
#写入日志
insert record_sellInfo(uid,pid,rType,count,free_count,AddTime)
values(uid,pid,CHARSET('发布出售信息'),acount-pcount,afree_count+pcount,NOW());
#字符串必须使用CHARSET(str)这个函数,不然会报错
SET result=1;
SELECT result;
END;
end if;
END也算是个MySQL存储过程的例子吧,希望可以帮助各位新手