user_servers中同一用户有很多条记录,如何判断哪一条先减去,哪一条后减去呢?思路:
1、用游标取出User表中的UserID
While(@@sqlstatus!=2)
begin
1)用游标取出表user_servers中该用户的一条记录的money
While(@@sqlstatus!=2)
begin
2)取出money中的money
3)求两者的差值
if(差值>0)
begin
insert log表;
end
else
begin
insert owe_log表
end
update money表;
end
end
1、用游标取出User表中的UserID
While(@@sqlstatus!=2)
begin
1)用游标取出表user_servers中该用户的一条记录的money
While(@@sqlstatus!=2)
begin
2)取出money中的money
3)求两者的差值
if(差值>0)
begin
insert log表;
end
else
begin
insert owe_log表
end
update money表;
end
end
declare @UserId varchar
declare User_cursor cursor for
select id from User
open User_cursor
fetch User_cursor into @UserId
While(@@sqlstatus!=2)
begin
……
fetch User_cursor into @UserId
end
Close User_cursor 不过嵌套使用游标好像要注意sqlstatus的判断,忘记具体怎么弄的了,楼主可以自己琢磨一下
as
cursor userinfo is select * from user;
cursor userserv(uid nubmer) is select * from
moneycount number;
begin
for uinfo in userinfo loop
for us in userserv(uinfo.id) loop
select money into moneycount from money where id=uinfo.id;
if moneycount>us.server_money then
update money set money=money-us.server_money where id=uinfo.id;
insert into log
end if;
if moneycount<us.server_money and moneycount<>0 then
update money set money=0 where id=uinfo.id
insert into owe_log values(uinfo.id,us.server_money-moneycount,sysdate);
else
insert into owe_log values(uinfo.id,us.server_money-moneycount,sysdate);
end if;
end loop;
end loop;
end;