第二个题目主要考查了触发器以及存储过程的使用，题目描述如下：
—————————————————————————————
2、医院药品都实行效期管理，效期早的药品先使用。请设计一个程序来按效期使用药品，要求：1、使用触发器来维护两个表之间的库存的一致性。2、对药品出库时,能正确选择批次。
用到的表以及数据如下所示：
--药品库存表kc
create table kc(
id number(2) primary key, --药品id
ypname varchar2(20),--药品名称
num number(5) --数量
);
insert into kc values (1,'青霉素',3000);
insert into kc values (2,'双黄连',4000);
--药品批次库存表batchkc
create table batchkc(
id number(2) references kc(id), --药品id
validdate date, --到期时间
bnum number(5), --数量
primary key (id,validdate)
);
insert into batchkc values (1,'01-5月-04',2500);
insert into batchkc values (1,'01-12月-04',500);
insert into batchkc values (2,'12-1月-04',1000);
insert into batchkc values (2,'07-6月-04',1000);
insert into batchkc values (2,'13-8月-04',1000);
insert into batchkc values (2,'13-12月-05',1000);
这个还好，至少对每个字段进行了说明。。就比较清晰了。
按照要求写一个存储过程，传入两个参数，一个是药品的名称，另外一个就是要出库的数量。代码如下：
--chuku.prc
create or replace procedure chuku(c_ypname varchar2,c_num number)
as
begin
declare
kc_num number ;--库存中的数量
needLeft number;--临时变量，还需要出库的数量
yp_id number;--库存药品id
cursor cur_batchkc(kc_id number) is
select bnum,id,validdate from batchkc where id=kc_id order by validdate asc --时间升序
for update;
begin
select num,id into kc_num,yp_id from kc where ypname=c_ypname;
if(kc_num >= c_num) then --如果库存中的药品数量>要出库的数量的话 就出库
DBMS_OUTPUT.PUT_LINE('正在计算出库顺序：');
DBMS_OUTPUT.PUT_LINE('出库列表如下：');
needLeft := c_num; --还需要最初始出库那么多
for it in cur_batchkc(yp_id) loop --遍历
if(it.bnum > needLeft) then --还有可以用来出库滴
update batchkc set bnum = bnum-needLeft where current of cur_batchkc;
DBMS_OUTPUT.PUT_LINE('batchkc-ID 出库数量 过期时间');
DBMS_OUTPUT.PUT_LINE(it.id||' '||needLeft||' '||it.validdate);
exit;--第一条记录就满足出库数量的提交 可以退出了
elsif (it.bnum=needLeft) then --刚好满足
delete from batchkc where current of cur_batchkc;
DBMS_OUTPUT.PUT_LINE('batchkc-ID 出库数量 过期时间');
DBMS_OUTPUT.PUT_LINE(it.id||' '||needLeft||' '||it.validdate);
exit;--刚好 满足 也退出
else --第一条记录满足不了了。
delete from batchkc where current of cur_batchkc;--满足不了先出库
DBMS_OUTPUT.PUT_LINE('batchkc-ID 出库数量 过期时间');
DBMS_OUTPUT.PUT_LINE(it.id||' '||it.bnum||' '||it.validdate);
needLeft := needLeft - it.bnum;--还需要的数量减少
end if;
end loop;
else --库存不足啦
DBMS_OUTPUT.PUT_LINE('药品不够啦');
end if;
end;
end;
/
上面有注释，应该能看懂吧。然后还得有个触发器，让去update或者delete的时候自动去维护两张表的关系。代码如下：
create or replace trigger tri_batchkc
after delete or update on batchkc for each row--行级触发器
declare
v_num NUMBER;
begin
if DELETING then
update kc set num=num-:old.bnum where id=:old.id;
end if;
select num into v_num from kc where id=:old.id;
if v_num=0 then
--刚好出库完毕 就可以把当前的记录删掉啦
delete from kc where id=:old.id;
end if;
if UPDATING then
update kc set num=num-(:old.bnum-:new.bnum) where id=:old.id;
end if;
end;
/
实现的效果如下：
注意的是，为了实验中的方便。这里在存储过程中都没有去commit，免得一会又得重新@导入数据。
上上一个题目中也是滴。
