【正文】 mit。YYYYMMDD39。201211239。E0439。保和丸39。S1139。B2739。end。)。限制采購量更新量不小于100create or replace trigger t5 before insert on buylist for each rowbegin if (:100)then (39。))。,39。,3,to_date(39。,39。,39。測試結(jié)果insert into Goodsvalues(39。 end if。藥品已過期39。YYYYMMDD39。20101139。)mit。,39。,39。,39。測試結(jié)果insert into Goodsvalues(39。 end if。YYYYMMDD39。201212439。E1639。H0139。舒必利39。I2639。end。)。限制入庫量不少于400create or replace trigger t2 before insert on inlist for each rowbegin if (:400)then (39。測試結(jié)果delete from cbacklistwhere cusmumber=39。c0239。end。穿心蓮片39。end 。更新某藥品入庫量create or replace procedure p6(v_gname in %type) isbegin update inlist set inliang=inliang+100 where v_gname=gname。測試結(jié)果begin p5。 close c1。||v_saleliang)。 (v_gname||39。 loop fetch c1 into v_gname,v_saleliang。 cursor c1 is select ,saleliang from salelist,goods where = order by Saleliang desc。按銷售量排序create or replace procedure p5is v_gname %type。begin p4(v_gname)。測試結(jié)果declare v_gname varchar2(30):=39。 mit。end。)。,39。 v_gshelf date:= to_date(39。顆粒39。處方藥39。感冒膠囊39。end。插入一條藥品信息Create or replace procedure p3(v_gname %type,v_gkind %type,v_gtype %type,v_gprice %type,v_gshelf %type)asbegin insert into goods(gname,gkind,gtype,gprice,gshelf) values(v_gname,v_gkind,v_gtype,v_gprice,v_gshelf)。begin p2(v_type)。測試結(jié)果declare v_type varchar2(10):=39。 close c1。|| v_inliang)。 (v_gname||39。 loop fetch c1 into v_gname,v_inliang。 cursor c1 is select , inliang from goods,inlist where = and gtype =v_type。輸出指定劑型的藥品的入庫量create or replace procedure p2(v_type %type)is v_gname %type。測試結(jié)果begin p1。||v_sbackreas)。||v_sbackliang||39。||v_empnumber||39。||v_gname||39。||v_supnumber||39。(v_sbacknumBer||39。 begin select sbacknumber,supnumber,gname,empnumber,sbackling,sbackreas into v_sbacknumber,v_supnumber, v_gname,v_empnumber,v_sbackliang,v_sbackreas from sbacklist where sbacknumber=39。 v_sbackliang %type。 v_gname %type。按銷售量排序create procedure pro_orderSaleasselect 藥品名,Saleliang 銷售量from Goods,Salelistwhere =order by Saleliang desc測試執(zhí)行結(jié)果exec pro_orderSale更新入庫量create procedure pro_updInasupdate Inlistset Inliang=Inliang+500測試執(zhí)行結(jié)果exec pro_updIndrop procedure pro_updIn 附錄四 Sql語句驗證視圖過程驗證： 視圖驗證結(jié)果（Vi_supplier） 視圖驗證結(jié)果（Vi_sale） 視圖驗證結(jié)果（Vi_pancun）存儲過程驗證：存儲過程驗證（Goods_shelf） 存儲過程驗證（pro_Gtype）存儲過程驗證（pro_cback）存儲過程驗證（pro_orderSale）存儲過程驗證（pro_updIn）觸發(fā)器驗證：觸發(fā)器驗證（tri_C02）觸發(fā)器驗證（tri_Acc）觸發(fā)器驗證（tri_gprice）觸發(fā)器驗證（tri_gshelf）觸發(fā)器驗證（tri_buy） Oracle語句視圖：create or replace view Vi_supplierasselect *from Suppliercreate or place view Vi_pancunasselect Gname,Pancliangfrom Panclist存儲過程：輸出退貨單為B11的信息create or replace procedure p1is v_sbacknumber %type。刪除一條藥品記錄create procedure pro_delgoodsGname char(30)asdeletefrom Goodswhere Gname=Gname測試執(zhí)行結(jié)果exec pro_delgoods 39。,39。,39。,39。插入一條藥品信息create procedure pro_addgoodsGname varchar(30),Gkind varchar(20),Gtype varchar(20),Gprice float,Gshelf dateasinsert into Goodsvalues(Gname,Gkind,Gtype,Gprice,Gshelf)測試執(zhí)行結(jié)果exec pro_addgoods 39。exec pro_cback 39。測試執(zhí)行結(jié)果exec pro_Gtype查詢某種原因的藥品的退貨量create procedure pro_cbackBackSake varchar(30)asselect 藥品名,Cbackliang 退貨量,Cbackreas 退貨原因from Goods,Cbacklistwhere = and Cbackreas=BackSake測試執(zhí)行結(jié)果exec pro_cback 39。查詢劑型為普通片的藥品create procedure pro_Gtypeasselect Gname 藥品名,Gtype 劑型from Goodswhere Gtype=39。)存儲過程查詢某一藥品保質(zhì)期create procedure Goods_shelf Gname varchar(30)asselect Gname 藥品名, Gshelf 保質(zhì)期from Goodswhere Gname=Gname測試執(zhí)行結(jié)果exec Goods_shelf Gname=39。,50,39。,39。,39。,39。end測試insert into Buylistvalues(39。)限制采購量更新量不小于100create trigger tri_buyon Buylistfor insert,updateasif exists( select * from inserted where Buyliang100)begin print39。,3,39。,39。,39。 rollbackend測試insert into Goodsvalues(39。)保質(zhì)期到2010年的不允許插入create trigger tri_gshelfon Goodsfor insertasif exists ( select * from Goods where Year(Gshelf)2012 )begin print 39。,39。,39。,39。)限制更新的藥品價格不小于1元，小于1元的改為1元create trigger tri_gpriceon Goodsinstead of insert,updateasdeclare Gname varchar(30), Gprice floatselect Gname=Gname, Gprice=Gpricefrom insertedif Gprice1update Goodsset Gprice=1where Gname=Gname測試insert into Goodsvalues(39。,300,39。,39。,39。,39。測試insertinto Inlistvalues(39。限制某一藥品入庫量不小于400create trigger tri_Accon Inlistinstead of insert,updateas declare Gname char(30), Inliang intselect Gname=Gname, Inliang=Inliangfrom insertedif Inliang400print 39。end測試deletefrom Cbacklistwhere Cusnumber=39。 ) print 39。)),Cusphone varchar(20))create table Buylist(Buynumber varchar(10) primary key,Supnumber varchar(10),Gname varchar(30),Empnumber varchar(10),Buyliang int not null,Buydate date,foreign key (Supnumber) references Supplier(Supnumber), foreign key (Gname) references Goods(Gname),foreign key (Empnumber) references Employer(Empnumber))create table Sendlist(Sendnumber varchar(10) primary key,Supnumber varchar(1