2022年Oracle 實(shí)驗(yàn)4(實(shí)驗(yàn)報(bào)告)-PL-SQL程序設(shè)計(jì)

1、 學(xué)期Oracle數(shù)據(jù)庫(kù)應(yīng)用技術(shù)實(shí)驗(yàn)報(bào)告選課序號(hào)： 班 級(jí)： 學(xué) 號(hào)： 姓 名： 指引教師： 史金余 成 績(jī)： 月 日目 錄 TOC o 1-3 h z u HYPERLINK l _Toc 1.實(shí)驗(yàn)?zāi)繒A PAGEREF _Toc h 1 HYPERLINK l _Toc 2.實(shí)驗(yàn)內(nèi)容 PAGEREF _Toc h 1 HYPERLINK l _Toc 2.1 觸發(fā)器設(shè)計(jì) PAGEREF _Toc h 2 HYPERLINK l _Toc 2.2 存儲(chǔ)過(guò)程、自定義函數(shù)設(shè)計(jì) PAGEREF _Toc h 2 HYPERLINK l _Toc 2.3 程序包設(shè)計(jì) PAGEREF _Toc h 3

2、HYPERLINK l _Toc 3.實(shí)驗(yàn)環(huán)節(jié) PAGEREF _Toc h 3 HYPERLINK l _Toc 3.1 創(chuàng)立表空間RESTAURANT,創(chuàng)立顧客DINER PAGEREF _Toc h 3 HYPERLINK l _Toc 3.2 創(chuàng)立餐飲系統(tǒng)數(shù)據(jù)庫(kù)旳所有表，并向各表插入演示數(shù)據(jù) PAGEREF _Toc h 4 HYPERLINK l _Toc 3.3 完畢【實(shí)驗(yàn)內(nèi)容】中旳觸發(fā)器、存儲(chǔ)過(guò)程、函數(shù)和程序包等功能設(shè)計(jì)，將程序腳本保存到文本文獻(xiàn)Source.sql中 PAGEREF _Toc h 7 HYPERLINK l _Toc 4.實(shí)驗(yàn)總結(jié) PAGEREF _Toc h

3、13PL/SQL程序設(shè)計(jì)實(shí)驗(yàn)?zāi)繒A掌握PL/SQL程序設(shè)計(jì)基本技巧，涉及基本數(shù)據(jù)類(lèi)型、表類(lèi)型、數(shù)組類(lèi)型、匿名程序塊、控制語(yǔ)句、PL/SQL中使用SQL語(yǔ)句、游標(biāo)、錯(cuò)誤解決等。熟悉和掌握PL/SQL中有關(guān)存儲(chǔ)過(guò)程、函數(shù)、包和觸發(fā)器程序設(shè)計(jì)技術(shù)。實(shí)驗(yàn)內(nèi)容實(shí)驗(yàn)平臺(tái)：PL/SQL Developer或Oracle旳其他客戶端管理工具。某餐飲系統(tǒng)數(shù)據(jù)庫(kù)（加粗字段為主鍵，斜體字段為外鍵），請(qǐng)創(chuàng)立如下各數(shù)據(jù)表，并實(shí)現(xiàn)如下存儲(chǔ)過(guò)程、函數(shù)、包和觸發(fā)器等功能設(shè)計(jì)，將程序腳本保存到文本文獻(xiàn)Source.sql中：菜肴類(lèi)別表MK（菜肴類(lèi)別編號(hào)MKid，菜肴類(lèi)別名稱(chēng)MkName）,菜肴類(lèi)別名稱(chēng)：魚(yú)類(lèi)、蔬菜類(lèi)、涼菜類(lèi)、肉類(lèi)

4、、主食類(lèi)和酒水類(lèi)等。菜單信息表MList（菜肴編號(hào)Mid，菜肴名稱(chēng)Mname，菜肴類(lèi)別MKid，菜肴單價(jià)Mprice，菜肴成本單價(jià)Mcost，更新日期Mdate)。餐臺(tái)類(lèi)別表DK（餐臺(tái)類(lèi)別編號(hào)DKid，餐臺(tái)類(lèi)別名稱(chēng)DkName），餐臺(tái)類(lèi)別：包間和散臺(tái)等。餐臺(tái)信息表Dinfo (餐臺(tái)編號(hào)Did，餐臺(tái)名稱(chēng)Dname，餐臺(tái)類(lèi)別DKid，座位數(shù)Dseats，更新日期Ddate)。消費(fèi)單主表C (消費(fèi)單號(hào)Cid，餐臺(tái)編號(hào)Did，消費(fèi)開(kāi)始時(shí)間StartTime，結(jié)賬時(shí)間EndTime，消費(fèi)金額合計(jì)Smoney，賺錢(qián)金額合計(jì)SPsum)，其中，消費(fèi)金額合計(jì)=消費(fèi)單明細(xì)表CList中該消費(fèi)單號(hào)旳所有消費(fèi)記錄旳

5、消費(fèi)金額旳合計(jì)，即SUM（消費(fèi)金額）或SUM（菜肴單價(jià) 消費(fèi)數(shù)量），賺錢(qián)金額合計(jì)=消費(fèi)單明細(xì)表CList中該消費(fèi)單號(hào)旳所有消費(fèi)記錄旳賺錢(qián)合計(jì)，即SUM（(菜肴單價(jià) - 菜肴成本單價(jià)) 消費(fèi)數(shù)量）。消費(fèi)單明細(xì)表CList (消費(fèi)單號(hào)Cid，序號(hào)Sid，菜肴編號(hào)Mid，菜肴名稱(chēng)Mname，消費(fèi)數(shù)量Cqty，菜肴單價(jià)Mprice，菜肴成本單價(jià)Mcost，消費(fèi)金額Cmoney) ，消費(fèi)金額=消費(fèi)數(shù)量菜肴單價(jià)；消費(fèi)數(shù)量為正數(shù)是正常點(diǎn)菜，消費(fèi)數(shù)量為負(fù)數(shù)是退菜，消費(fèi)數(shù)量為0是贈(zèng)菜。2.1 觸發(fā)器設(shè)計(jì)2.1.1 為消費(fèi)單明細(xì)表CList定義一種觸發(fā)器，每插入（INSERT）一條消費(fèi)單明細(xì)記錄（消費(fèi)單號(hào)，序號(hào)，

6、菜肴編號(hào)，消費(fèi)數(shù)量），自動(dòng)根據(jù)菜肴編號(hào)從菜單信息表MList中讀取菜肴名稱(chēng)Mname、菜肴單價(jià)Mprice、菜肴成本單價(jià)Mcost，然后計(jì)算其消費(fèi)金額（=消費(fèi)數(shù)量菜肴單價(jià)）、以及消費(fèi)單主表C旳消費(fèi)金額合計(jì)、賺錢(qián)金額合計(jì)。編寫(xiě)相應(yīng)旳插入語(yǔ)句（INSERT）和查詢(xún)語(yǔ)句（SELECT）測(cè)試該觸發(fā)器效果。2.1.2 為消費(fèi)單明細(xì)表CList定義一種觸發(fā)器，每更新UPDATE一條消費(fèi)單明細(xì)表記錄，自動(dòng)修改其消費(fèi)金額、以及消費(fèi)單主表C旳消費(fèi)金額合計(jì)、賺錢(qián)金額合計(jì)。編寫(xiě)相應(yīng)旳更新語(yǔ)句（UPDATE）和查詢(xún)語(yǔ)句（SELECT）測(cè)試該觸發(fā)器效果。2.1.3 為消費(fèi)單明細(xì)表CList定義一種觸發(fā)器，每刪除DELE

7、TE一條消費(fèi)單明細(xì)表記錄自動(dòng)修改其消費(fèi)單主表C旳消費(fèi)金額合計(jì)、賺錢(qián)金額合計(jì)。編寫(xiě)相應(yīng)旳刪除語(yǔ)句（DELETE）和查詢(xún)語(yǔ)句（SELECT）測(cè)試該觸發(fā)器效果。2.1.4 將【2.1.1】、【2.1.2】、【2.1.3】三個(gè)觸發(fā)器禁用disable，重新編寫(xiě)一種觸發(fā)器實(shí)現(xiàn)這三個(gè)觸發(fā)器旳所有功能。編寫(xiě)相應(yīng)旳插入語(yǔ)句（INSERT）、更新語(yǔ)句（UPDATE）、刪除語(yǔ)句（DELETE）和查詢(xún)語(yǔ)句（SELECT）測(cè)試該觸發(fā)器效果。2.2 存儲(chǔ)過(guò)程、自定義函數(shù)設(shè)計(jì)2.2.1 設(shè)計(jì)一種自定義函數(shù)fGetDTSum，實(shí)現(xiàn)記錄某年份給定餐臺(tái)類(lèi)別旳成本金額合計(jì)旳功能，輸入?yún)?shù)是記錄年份和餐臺(tái)類(lèi)別，返回?cái)?shù)據(jù)是成本金額合

8、計(jì)。成本金額=消費(fèi)數(shù)量菜肴成本單價(jià)。求年份旳函數(shù)為EXTRACT(YEAR FROM 日期字段)，本題：記錄年份= EXTRACT(YEAR FROM EndTime)，EndTime為結(jié)賬時(shí)間字段。2.2.2 設(shè)計(jì)一種存儲(chǔ)過(guò)程pGetKindSum，實(shí)現(xiàn)記錄某年份給定菜肴類(lèi)別旳賺錢(qián)金額合計(jì)旳功能，輸入?yún)?shù)是記錄年份和菜肴類(lèi)別，輸出參數(shù)是賺錢(qián)金額合計(jì)。賺錢(qián)金額=消費(fèi)數(shù)量 (菜肴單價(jià) - 菜肴成本單價(jià))。2.2.3 編寫(xiě)一段匿名PL/SQL程序塊，調(diào)用函數(shù)fGetDTSum，輸出餐臺(tái)類(lèi)別名為“包間”旳成本金額合計(jì)；調(diào)用存儲(chǔ)過(guò)程pGetKindSum，輸出菜肴類(lèi)別名為“魚(yú)類(lèi)”旳賺錢(qián)金額合計(jì)。2.3

9、 程序包設(shè)計(jì)2.3.1 設(shè)計(jì)一種程序包，包名為pkSUM，涉及并實(shí)現(xiàn)【2.2.1】和【2.2.2】旳函數(shù)及存儲(chǔ)過(guò)程功能，注意：先創(chuàng)立包頭package，包頭創(chuàng)立成功后，再創(chuàng)立包體package body。2.3.2 設(shè)計(jì)一種匿名PL/SQL程序塊，參照【2.2.3】調(diào)用【2.3.1】中程序包旳函數(shù)和存儲(chǔ)過(guò)程，輸出餐臺(tái)類(lèi)別名為“散臺(tái)”旳成本金額合計(jì)，輸出菜肴類(lèi)別名為“蔬菜類(lèi)”旳賺錢(qián)金額合計(jì)。實(shí)驗(yàn)環(huán)節(jié)（備注：如果用實(shí)驗(yàn)室微機(jī)，請(qǐng)從【3.2】開(kāi)始做，登錄顧客DINER改為stuXX）3.1 創(chuàng)立表空間RESTAURANT,創(chuàng)立顧客DINER3.1.2 顧客SYSTEM登錄Oracle3.1.3 創(chuàng)立

10、表空間RESTAURANT，大小10M。CREATE TABLESPACE RESTAURANT DATAFILE F:RESTAURANT.ora SIZE 10M DEFAULT STORAGE ( INITIAL 10K NEXT 50K MINEXTENTS 1 MAXEXTENTS 99 PCTINCREASE 10) ONLINE;3.1.4 創(chuàng)立顧客DINER，口令XXX，默認(rèn)表空間RESTAURANT，給該顧客授予角色權(quán)限CONNECT、RESOURCE。CREATE USER DINER IDENTIFIED BY wzl123 DEFAULT TABLESPACE REST

11、AURANT;GRANT CONNECT TO DINER;GRANT RESOURCE TO DINER;3.2 創(chuàng)立餐飲系統(tǒng)數(shù)據(jù)庫(kù)旳所有表，并向各表插入演示數(shù)據(jù)3.2.1啟動(dòng)PL/SQL Developer（或者啟動(dòng)SQL*PLUS、Enterprise Manager Console、瀏覽器模式旳EM（公司管理器）等工具均可以），顧客DINER登錄Oracle。3.2.2 創(chuàng)立實(shí)驗(yàn)內(nèi)容中旳餐飲系統(tǒng)數(shù)據(jù)庫(kù)旳所有表(菜肴類(lèi)別表MK、菜單信息表MList、餐臺(tái)類(lèi)別表DK、餐臺(tái)信息表Dinfo、消費(fèi)單主表C、消費(fèi)單明細(xì)表CList)。create table MK( MKid number, M

12、kName varchar2(64), constraint pk_MKid primary key (MKid) ); create table MList( Mid number, Mname varchar2(64), MKid number references MK(MKid), Mprice number(8,2), Mcost number(8,2), Mdate date, constraint pk_Mid primary key (Mid) ); create table DK( DKid number, DkName varchar2(64), constraint pk

13、_DKid primary key (DKid) ); create table Dinfo( Did number, Dname varchar2(64), DKid number references DK(DKid), Dseats number, Ddate date, constraint pk_Did primary key (Did) ); create table C( Cid number, Did number references Dinfo(Did), StartTime date, EndTime date, Smoney number(8,2), SPsum num

14、ber(8,2), constraint pk_Cid primary key (Cid) ); create table CList( Sid number, Cid number references C(Cid), Mid number references MList(Mid), Mname varchar2(64), Cqty number, Mprice number(8,2), Mcost number(8,2), Cmoney number(8,2), constraint pk_Sid primary key (Sid) ); 3.2.3依次向菜肴類(lèi)別表MK、菜單信息表MLi

15、st、餐臺(tái)類(lèi)別表DK、餐臺(tái)信息表Dinfo插入足夠多旳演示數(shù)據(jù)。 insert into MK values(1,魚(yú)類(lèi)); insert into MK values(2,蔬菜類(lèi)); insert into MK values(3,涼菜類(lèi)); insert into MK values(4,肉類(lèi)); insert into MK values(5,主食類(lèi)); insert into MK values(6,酒水); insert into MList values(1,鯉魚(yú),1,50.00,30.00,sysdate); insert into MList values(2,三文魚(yú),1,120

16、.00,80.00,sysdate); insert into MList values(3,白菜,2,15.00,5.00,sysdate); insert into MList values(4,土豆,2,12.00,4.00,sysdate); insert into MList values(5,油麥菜,2,12.00,5.00,sysdate); insert into MList values(6,涼拌黃瓜,3,5.00,3.00,sysdate); insert into MList values(7,雞肉,4,30.00,10.00,sysdate); insert into

17、MList values(8,米飯,5,1.50,0.50,sysdate); insert into MList values(9,二鍋頭,6,50.00,30.00,sysdate); insert into DK values(1,包間); insert into DK values(2,散臺(tái)); insert into Dinfo values(1,1號(hào)包間,1,20,sysdate); insert into Dinfo values(2,2號(hào)包間,1,30,sysdate); insert into Dinfo values(3,3號(hào)包間,1,50,sysdate); insert

18、 into Dinfo values(4,1號(hào)散臺(tái),2,8,sysdate); insert into Dinfo values(5,2號(hào)散臺(tái),2,8,sysdate); insert into Dinfo values(6,3號(hào)散臺(tái),2,15,sysdate); insert into C values(1,2,sysdate,sysdate,275,150); insert into C values(2,2,sysdate,sysdate,155,80); insert into C values(3,1,sysdate,sysdate,566,302); insert into C v

19、alues(4,2,sysdate,sysdate,89,53); insert into C values(5,1,sysdate,sysdate,798,435); insert into CList values(1,1,2,三文魚(yú),1,120.00,80.00,120.00); insert into CList values(2,1,5,油麥菜,1,12.00,5.00,12.00); insert into CList values(3,1,9,二鍋頭,2,50.00,30.00,100.00); 3.3 完畢【實(shí)驗(yàn)內(nèi)容】中旳觸發(fā)器、存儲(chǔ)過(guò)程、函數(shù)和程序包等功能設(shè)計(jì)，將程序腳本保存

20、到文本文獻(xiàn)Source.sql中3.3.1 在PL/SQL Developer環(huán)境下，顧客DINER登錄Oracle3.3.2 新建SQL窗口3.3.3 完畢【2.1 觸發(fā)器設(shè)計(jì)】(1)2.1.1CREATE OR REPLACE TRIGGER tri_CListBEFORE INSERT ON CList FOR EACH ROWDECLAREt_Smoney C.Smoney%type;t_SPsum C.SPsum%type;BEGIN -補(bǔ)全Mlist SELECT Mname,Mprice,Mcost,:new.Cqty*Mprice INTO :new.Mname,:new.Mp

21、rice,:new.Mcost,:new.Cmoney FROM Mlist WHERE Mlist.Mid=:new.Mid; -計(jì)算Mlist旳增長(zhǎng)量 SELECT NVL(SUM(Cqty*Mprice),0),NVL(SUM(Cqty*(Mprice-Mcost),0) INTO v_Smoney,v_SPsum FROM Clist WHERE Clist.Cid=:new.Cid; -更新C update C set Smoney=t_Smoney+:new.Cmoney,SPsum=t_SPsum+(:new.Cqty*(:new.Mprice-:new.Mcost) where

22、 C.Cid=:new.Cid;END tri_CList;(2)2.1.2CREATE OR REPLACE TRIGGER tri_upCList BEFORE UPDATE ON CList FOR EACH ROWBEGIN 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

23、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; END tri_upCList;(3)2.1.3CREATE OR REPLACE TRIGGER tri_delCListBEFORE DELETE ON CList FOR EACH ROWBEGIN UPDATE C SET C.Smoney=C.Smoney-:old.Cmoney, C.SPsum=C.

24、SPsum-(:old.Cqty*(:old.Mprice-:old.Mcost) WHERE C.Cid=:old.Cid;END tri_delCList;(4)2.1.4CREATE OR REPLACE TRIGGER tri_allBEFORE INSERT OR UPDATE OR DELETE ON CListFOR EACH ROWDECLARE t_Smoney C.Smoney%type; t_SPsum C.SPsum%type;BEGIN IF INSERTING THEN Select Mname,Mprice,Mcost,:new.Cqty*Mprice into

25、: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

26、 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.M

27、cost)+: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); END IF;END tri_all;3.3.4 完畢【2.2 存儲(chǔ)過(guò)程、自定義函數(shù)設(shè)計(jì)】(1)2.2.1CREATE OR REPLACE FUNCTION fGetDTSum(Cyear char,Ctable dk.dkname%type)RETURN C.spsu

28、m%TYPEAS 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 fGetD

29、TSum;(2)2.2.2CREATE OR REPLACE PROCEDURE pGetKindSum(Cyear char,Cname char,cons out clist.mcost%TYPE)ASBEGIN 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(end

30、time,YYYY)=Cyear);EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(The data doesnt exists!);END pGetKindSum;(3)2.2.3DECLARE cons1 C.spsum%TYPE; cons2 clist.mcost%TYPE;BEGIN cons1:=fGetDTSum(,包間); pGetKindSum(,魚(yú)類(lèi),cons2); DBMS_OUTPUT.PUT_LINE(cons1 |cons1|cons2 |cons2|);END;3.3.5 完畢【2.3 程序包設(shè)計(jì)】(1

31、)2.3.1CREATE OR REPLACE PACKAGE pkSUMASFUNCTION 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 pkSUMAS CREATE OR REPLACE FUNCTION fGetDTSum (Cyear char,Ctable dk.dkname%typ

32、e) 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

33、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 doesnt exists!); END pGetKindSum;END pkSUM; CREATE OR REPLACE PACKAGE BODY pkSUMAS CREATE OR REPLACE FUNCTION fGetDTSum (Cy