END tri_delCList;
(4)2.1.4
CREATE OR REPLACE TRIGGER tri_all BEFORE INSERT OR UPDATE OR DELETE ON CList FOR EACH ROW DECLARE
t_Smoney C.Smoney%type; t_SPsum C.SPsum%type; BEGIN
IF INSERTING THEN
Select Mname,Mprice,Mcost,:new.Cqty*Mprice into :new.Mname,:new.Mprice,:new.Mcost,:new.Cmoney from Mlist
where Mlist.Mid=:new.Mid;
Select nvl(sum(Cqty*Mprice),0),nvl(sum(Cqty*(Mprice-Mcost)),0) into t_Smoney,t_SPsum from Clist
where Clist.Cid=:new.Cid;
update C
set Smoney=t_Smoney+:new.Cmoney,SPsum=t_SPsum+(:new.Cqty*(:new.Mprice-:new.Mcost)) where C.Cid=:new.Cid; ELSIF UPDATING THEN
select Mname,Mprice,Mcost
into :new.Mname,:new.Mprice,:new.Mcost from Mlist
where Mlist.Mid=:new.Mid;
:new.Cmoney:=nvl(:new.Cqty,:old.Cqty)*nvl(:new.Mprice,:old.Mprice); Update C set Smoney=Smoney-:old.Cmoney+:new.Cmoney,
SPsum=SPsum-:old.Cqty*(:old.Mprice-:old.Mcost)+:new.Cqty*(:new.Mprice-:new.Mcost) where C.Cid=:old.Cid; ELSE
UPDATE C SET
C.Smoney=C.Smoney-:old.Cmoney,C.SPsum=C.SPsum-(:old.Cqty*(:old.Mprice-:old.Mcost));
9 / 13
END IF; END tri_all;
3.3.4 完成【2.2 存储过程、自定义函数设计】 (1)2.2.1
CREATE OR REPLACE FUNCTION fGetDTSum (Cyear char,Ctable dk.dkname%type) RETURN C.spsum%TYPE AS
cons C.spsum%TYPE; BEGIN
select nvl(sum(spsum),0)into cons from C where did in(
select did from dinfo where dkid in ( select dkid from dk where dkname=Ctable)) and (to_char(endtime,'YYYY')=Cyear); RETURN cons; EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('The data is invalid!'); END fGetDTSum;
(2)2.2.2
CREATE OR REPLACE PROCEDURE pGetKindSum( Cyear char,Cname char, cons out clist.mcost%TYPE) AS BEGIN
select nvl(sum(mprice-mcost),0) into cons from clist where mid in
(select mid from mlist where mkid in (select mkid from mk where mkname=Cname)) and cid in
(select cid from C where to_char(endtime,'YYYY')=Cyear); EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('The data doesn’’t exists!'); END pGetKindSum;
(3)2.2.3
DECLARE
cons1 C.spsum%TYPE; cons2 clist.mcost%TYPE;
10 / 13
BEGIN
cons1:=fGetDTSum('2013','包间'); pGetKindSum('2013','鱼类',cons2);
DBMS_OUTPUT.PUT_LINE('cons1 '||cons1||'cons2 '||cons2||''); END;
3.3.5 完成【2.3 程序包设计】 (1)2.3.1
CREATE OR REPLACE PACKAGE pkSUM AS
FUNCTION fGetDTSum
(Cyear char,Ctable dk.dkname%type) RETURN C.spsum%TYPE; PROCEDURE pGetKindSum( Cyear char,Cname char, cons out clist.mcost%TYPE); END pkSUM;
CREATE OR REPLACE PACKAGE BODY pkSUM AS
CREATE OR REPLACE FUNCTION fGetDTSum (Cyear char,Ctable dk.dkname%type) RETURN C.spsum%TYPE AS
cons C.spsum%TYPE; BEGIN
select nvl(sum(spsum),0)into cons from C where did in(
select did from dinfo where dkid in ( select dkid from dk where dkname=Ctable)) and (to_char(endtime,'YYYY')=Cyear); RETURN cons; EXCEPTION
WHEN NO_DATA_FOUND THEN
11 / 13
DBMS_OUTPUT.PUT_LINE('The data is invalid!'); END fGetDTSum;
CREATE OR REPLACE PROCEDURE pGetKindSum( Cyear char,Cname char, cons out clist.mcost%TYPE) AS BEGIN
select nvl(sum(mprice-mcost),0) into cons from clist where mid in
(select mid from mlist where mkid in (select mkid from mk where mkname=Cname)) and cid in
(select cid from C where to_char(endtime,'YYYY')=Cyear); EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('The data doesn’’t exists!'); END pGetKindSum; END pkSUM;
CREATE OR REPLACE PACKAGE BODY pkSUM AS
CREATE OR REPLACE FUNCTION fGetDTSum (Cyear char,Ctable dk.dkname%type) RETURN C.spsum%TYPE AS
cons C.spsum%TYPE; BEGIN
select nvl(sum(spsum),0)into cons from C where did in(
select did from dinfo where dkid in ( select dkid from dk where dkname=Ctable)) and (to_char(endtime,'YYYY')=Cyear); RETURN cons; EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('The data is invalid!'); END fGetDTSum;
CREATE OR REPLACE PROCEDURE pGetKindSum( Cyear char,Cname char, cons out clist.mcost%TYPE) AS
12 / 13
BEGIN
select nvl(sum(mprice-mcost),0) into cons from clist where mid in
(select mid from mlist where mkid in (select mkid from mk where mkname=Cname)) and cid in
(select cid from C where to_char(endtime,'YYYY')=Cyear); EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('The data doesn’’t exists!'); END pGetKindSum; END pkSUM;
(2)2.3.2
DECLARE
cons1 C.spsum%TYPE; cons2 clist.mcost%TYPE; BEGIN
cons1:=pkSUM.fGetDTSum1('2013','散台'); pkSUM.pGetKindSum1('2013','蔬菜类',cons2);
DBMS_OUTPUT.PUT_LINE('cons1:'||cons1||' cons2:'||cons2||''); END;
4. 实验总结
这次实验让我学会了许多东西，比如PLSQL Developer的使用，高级PL/SQL程序的编写，其中包括触发器，储存过程，自定义函数，匿名函数，程序包的设计与编写。另外，也让我又温习了一遍基础SQL语言。我也明白了一个道理，“纸上得来终觉浅，绝知此事要躬行”，学好这门课最好的办法还是上级亲自实践一遍。总之，这门课令我受益匪浅。
13 / 13