我们最常见的一个业务：仓库出货；
涉及的表就两个：库存表，出库流水表(其实还有很多，我就用最简单的模型来举例)
比如 ：
商品：方便面； 数量:10
如果这时候同时有两个人买8个；那么就是16个，理论上只能第一个成功；
但是以下的程序却可能让两个人都成功：
declareiCnt integer;
begin
select数量 into iCntfrom 库存where 商品='方便面';
if iCnt>=8 then
insert into 出库流水 values ('方便面',8);
update库存 set 数量=iCnt-8where 商品='方便面';
dbms_output.putline('成功卖出8个方便面，库存还剩余'||to_char(iCnt-8 )||'个');
else
dbms_output.putline('需要卖8个方便面,但是库存只有'||to_char(iCnt)||’个,缺||'to_char(8-iCnt)||'个');
end if;
end;
显然，上面写法非常容易产生脏数据；
有点遗憾的，我周围的很多朋友的大量代码都是以上的风格，而且都是游标 ；
以上的代码：
我觉得可以用来两种方法来改进：
1.
把最后一个关键语句： update库存 set 数量=iCnt-8where 商品='方便面';
换成 update库存 set 数量= 数量-8where 商品='方便面';
这样稍微好些，然后再执行第一句：
select数量 into iCntfrom 库存where 商品='方便面';
(当然，如果库存表上的数量字段有约束，必须大于0，这一步可能需要未必执行，可以这样做：
begin
update库存 set 数量= 数量-8where 商品='方便面';
exception--估计是数量小于0 ，而引起的数据约束错误
when others then--最好换成相应的异常关键字；
--提示库存不足；
end;
)
之后判断 iCnt 是否大于0，如果大于0 ，就OK，否则提示失败；
2.
把第一个语句：
select数量 into iCntfrom 库存where 商品='方便面';
换成select数量 into iCntfrom 库存where 商品='方便面' for update;
但是以上两种方法我都不太喜欢，我个人觉得即严谨又方便的方法是：
declareiCnt integer;
begin
update库存 set 数量= 数量-8where 商品='方便面';-- 一上来就扣库存，这样改纪录就锁住，其他session此时更新就会锁住
select数量 into iCntfrom 库存where 商品='方便面';
if iCnt>=0 then
insert into 出库流水 values ('方便面',8);
dbms_output.putline('成功卖出8个方便面，库存还剩余'||to_char(iCnt)||'个');
else
dbms_output.putline('需要卖8个方便面,但是库存只有'||to_char(iCnt+8)||’个,缺||'to_char(-iCnt)||'个');
end if;
end;
当然，最符合人们大脑思维的方法是：
declareiCnt integer;
begin
select数量 into iCntfrom 库存where 商品='方便面' for update ; --锁住，保证别人同时在作业；
if iCnt>=8 then
insert into 出库流水 values ('方便面',8);
update库存 set 数量=iCnt-8where 商品='方便面';
dbms_output.putline('成功卖出8个方便面，库存还剩余'||to_char(iCnt-8 )||'个');
else
dbms_output.putline('需要卖8个方便面,但是库存只有'||to_char(iCnt)||’个,缺||'to_char(8-iCnt)||'个');
end if;
end;
这个方法没有我个人喜欢的上面那个方法效率高；
而且，老实说，我非唱不喜欢用 select ... for update 的，万不得已，我不轻易用；
为什么不喜欢用，把上面的那个例子变换一下：
加入这个超市只卖方便面这一个品种，而且分布在很多货架上：
比如
A货架4个，B货架3个，C货架7个.......
那么只时候我要出库 ，那最常见的方法就是做个游标：
如果用 for update ，那么就得 一下在把所有的库存都锁住，别人想做个其他不相干的库存操作都不行(比如这时候某个做个货架间的商品移库)；
就按上面的例子来做，同时两个人都需要8个方便面；
仓库里有很多货位，所有货位上都是方便面，出库的时候按照货位的编号从小到大排序；
declare
iHasOut integer :=0; --已经出库的数量；
begin
forrec in (select 货位,数量 from 库存 where 商品='方便面' order by 货位)
loop
if iHasOut +rec.数量0)
loop
--找到当前最小货位；
begin
select min(货位) intosMinSeat from 库存where商品='方便面' ;-- 很可惜，这里也是全表扫描， 但是比 select货位,数量 from 库存 要好很多；
exception
when no_data_found then
rollback;
dbms_output.putline('库存不足);
return;
end;
update 库存 set数量-iRemain where 货位=sMinSeatand 商品='方便面';
if sql%rowcount=1
then
select数量 into iNumfrom 库存 where 货位=sMinSeatand 商品='方便面';
case
insert into 出库流水 values ('方便面',sMinSeat，least(iRemain ,iRemain+iNum));
when iNum>0then--该货位库存有结余
iRemain:=0;
exit;
when iNum=0 then--该货位库存刚好用完
iRemain:=0;
delete from 库存where 货位=sMinSeatand 商品='方便面';
exit;
when iNum0
then
rollback;
dbms_output.putline('库存不足);
else
dbms_output.putline('出库成功');
end;
以上的方法，都是先不做判断，直接减去要出库的库存，然后再做判断，有点不符合习惯的逻辑，
老实说，可能是我太在意并发造成的脏数据问题，对一切数据，除非锁住，否则都认为是不可靠的；
先不做判断，直接减去要出库的库存 ;
这样做的目的有两个作用：1.删除库存数量，2.同时锁住该库存记录，为下面的select 的结果保证正确性；
也就是一箭双雕；
我周围也有朋友 和我的想法一样，认为select 的值不可靠；
但是方法我却不喜欢，比如：
update库存set 备注=备注 where ..... ;--什么都不做，仅仅是为了锁住该记录
select .....into ...from 库存 where .....; --这样保证 这里的到的数据是不会被其他session影响的；
其实以上两句还不如并成一句：
select .....into ...from 库存 where .....for update ;
当然我上面也说了，我是不喜欢用for update 的；
如果把update库存set 备注=备注 where ..... 这个仅仅发挥锁功能的语句里在做点实事，就最好不过了，就有锁，又有相应的功能；
上面方法一中的第一句：找最小货位；
select min(货位) intosMinSeat from 库存 where商品='方便面';
因为我认为它不可靠(因为该货位此时没有锁住,而这里因为有min 函数，而不能使用for update)，所以在下面更新该货位库存的时候，就考虑了更新不中的情况；
下面说说第二种方法：
上面说了，能不用游标尽量不用游标； 但是方法一中还是用了一个类似游标的功能，就是循环；
现在我又想说了，能不用循环，就尽量不要用循环；
几百年前，伟大的数学家高斯在和其他小朋友在做1+2+....+100 这个算术题的时候，别人就用了循环的方法，从头做到尾，
而高斯确实造出了规律，避免了循环；他想到1+100=101，2+99=101 以此首尾搞下去，就是50个101， 所以，即使心算也能很快得到结果 5050；
所以，找规律是个很好的方法：
上面这个题目，其实也有规律：
A货架4个，B货架3个，C货架7个.......
也就是
货位
数量累计
A
4
4
B
3
7
C
7 14
......................................
当我们需要8个的时候，一眼就能看出 A ，B这两个货位是需要的，C货位取一个；
所以就是通过累计来去除循环；
方法二：
declare
iNum integer;
sMinSeat库存.货位%type; --库存中最小货位
begin
--生成全部要出库的库存库存流水；
insert into 出库流水
select商品, 货位,数量
from (select 商品, 货位,数量,累计
from (select商品, 货位,数量, sum(数量) over (order by 货位) as 累计 from 库存 where 商品='方便面')
where 累计<=8);
-- 判断是否有零头要出库；
select累计 into iNum
from (select商品, 货位,数量, sum(数量) over (order by 货位) as 累计 from 库存 where 商品='方便面')
where 累计<=8);
--删除全部出库的库存；
delete from 库存 where (商品,货位)=
(
select商品, 货位
from (select 商品, 货位,累计
from (select商品, 货位,数量, sum(数量) over (order by 货位) as 累计 from 库存 where 商品='方便面')
where 累计<=8);
);
ifiNum =8--正好没有零头；
then
dbms_output.putline('出库成功');
return;
end if;
--还剩一个零头；
--找到当前最小货位；
begin
select min(货位) intosMinSeat from 库存where商品='方便面' ;
exception
when no_data_found then
rollback;
dbms_output.putline('库存不足);
return;
end;
update 库存 set 数量=数量-(8-iNum) where 货位= sMinSeat and 商品='方便面' ;
insert into 出库流水
values('方便面', sMinSeat ,8-inum);
dbms_output.putline('出库成功');
end;
以上的方法，有两个问题：
1.没有考虑并发造成的脏数据；
2.反复的使用那个 分析函数语句，而没有办法把它记下来以便多次使用；
于是对于方法二，我想到了一个能解决上面两个问题的方法：通过标签记忆；
这个标签就是在库存表里有个字段，比如名字叫“正在使用”字段
那么方法二可以做如下改进：
declare
iNum integer;
sMinSeat库存.货位%type; --库存中最小货位
begin
--锁住需要全部出库的库存，并对每条库存打个标记；
update库存 set 正在使用='1' where (商品,货位)=
(
select商品, 货位
from (select 商品, 货位,累计
from (select商品, 货位,数量, sum(数量) over (order by 货位) as 累计 from 库存 where 商品='方便面')
where 累计<=8);
) ;
--生成相应的出库流水
insert into 出库流水
select 商品, 货位,数量 from 库存 where正在使用='1';
- 判断是否有零头要出库；
select sum(数量) into iNum from 库存 where 正在使用='1';
--删除全部出库的库存
delete from 库存 where 正在使用='1';
ifiNum =8--正好没有零头；
then
dbms_output.putline('出库成功');
return;
end if;
--还剩一个零头；
--找到当前最小货位；
begin
select min(货位) intosMinSeat from 库存where商品='方便面' ;
exception
when no_data_found then
rollback;
dbms_output.putline('库存不足);
return;
end;
update 库存 set 数量=数量-(8-iNum) where 货位= sMinSeat and 商品='方便面' ;
insert into 出库流水
values('方便面', sMinSeat ,8-inum);
dbms_output.putline('出库成功');
end;
虽然我倾向于使用改进后的方法二，
可是该方法没有方法一保险，我个人认为方法一是无懈可击的；
但是方法二，有个问题，就是单通过累计，把满足全部出库的那个做完后，就出那个零头，理论上来说，这个零头可能是下一个货位就够了，但是假如正在这个时候，突然在某个已空的最小的货位插入一个很小的库存，而不满足于这个零头，那怎么办呢？老实说我也没哟想好，希望有朋友把我的方法二修改的更严谨些。
我对方法二在实战过程中，又做了以下两种改进方法：
1.通过数据库库存的数量字段必须大于0的约束，如果出现上面的情况就会报错回滚，因为这种并发的异常在实际过程中非常少见，所以通过这个失败后，可以再执行一次；
2.方法二的后半部分在用方法一，那么有人要问，不如直接的都用方法一了，其实我是考虑效率，方法二在有的时候大大高于方法一；
__________________
[ 本帖最后由 qingyun 于 2008-9-2 18:15 编辑 ]
|
