oracle實(shí)驗(yàn)4(實(shí)驗(yàn)報(bào)告)-pl-sql程序設(shè)計(jì)

學(xué)期Oracle數(shù)據(jù)庫(kù)應(yīng)用技術(shù)實(shí)驗(yàn)報(bào)告選課序號(hào)：班 級(jí)：學(xué) 號(hào)：姓 名：指導(dǎo)教師： 史金余成 績(jī)：2017年月 日目 錄1.實(shí)驗(yàn)?zāi)康?.............................. 錯(cuò)誤!未定義書(shū)簽。2.實(shí)驗(yàn)內(nèi)容............................... 錯(cuò)誤!未定義書(shū)簽。觸發(fā)器設(shè)計(jì)........................... 錯(cuò)誤!未定義書(shū)簽。存儲(chǔ)過(guò)程、自定義函數(shù)設(shè)計(jì) ............. 錯(cuò)誤!未定義書(shū)簽。程序包設(shè)計(jì)........................... 錯(cuò)誤!未定義書(shū)簽。3.實(shí)驗(yàn)步驟............................... 錯(cuò)誤!未定義書(shū)簽。創(chuàng)建表空間RESTAURANT,創(chuàng)建用戶DINER..錯(cuò)誤!未定義書(shū)簽。創(chuàng)建餐飲系統(tǒng)數(shù)據(jù)庫(kù)的所有表，并向各表插入演示數(shù)據(jù)錯(cuò)誤!未定義書(shū)簽。完成【實(shí)驗(yàn)內(nèi)容】中的觸發(fā)器、存儲(chǔ)過(guò)程、函數(shù)和程序包等功能設(shè)計(jì)，將程序腳本保存到文本文件中 ...... 錯(cuò)誤!未定義書(shū)簽。4.實(shí)驗(yàn)總結(jié)............................... 錯(cuò)誤!未定義書(shū)簽。PL/SQL程序設(shè)計(jì)實(shí)驗(yàn)?zāi)康恼莆誔L/SQL程序設(shè)計(jì)基本技巧，包括基本數(shù)據(jù)類型、表類型、數(shù)組類型、匿名程序塊、控制語(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/SQLDeveloper或Oracle的其它客戶端管理工具。某餐飲系統(tǒng)數(shù)據(jù)庫(kù)（加粗字段為主鍵，斜體字段為外鍵），請(qǐng)創(chuàng)建如下各數(shù)據(jù)表，并實(shí)現(xiàn)如下存儲(chǔ)過(guò)程、函數(shù)、包和觸發(fā)器等功能設(shè)計(jì)，將程序腳本保存到文本文件中：1）菜肴類別表MK（菜肴類別編號(hào)MKid，菜肴類別名稱MkName）,菜肴類別名稱：魚(yú)類、蔬菜類、涼菜類、肉類、主食類和酒水類等。2）菜單信息表MList（菜肴編號(hào)Mid，菜肴名稱Mname，菜肴類別MKid，菜肴單價(jià)Mprice，菜肴成本單價(jià)Mcost，更新日期Mdate)。3）餐臺(tái)類別表DK（餐臺(tái)類別編號(hào)DKid，餐臺(tái)類別名稱DkName），餐臺(tái)類別：包間和散臺(tái)等。4）餐臺(tái)信息表Dinfo(餐臺(tái)編號(hào)Did，餐臺(tái)名稱Dname，餐臺(tái)類別DKid，座位數(shù)Dseats，更新日期Ddate)。5）消費(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，盈利金額合計(jì)SPsum)，其中，消費(fèi)金額合計(jì)=消費(fèi)單明細(xì)表CList中該消費(fèi)單號(hào)的所有消費(fèi)記錄的消費(fèi)金額的合計(jì)，即SUM（消費(fèi)金額）或SUM（菜肴單價(jià)×消費(fèi)數(shù)量），盈利金額合計(jì)=消費(fèi)單明細(xì)表CList中該消費(fèi)單號(hào)的所有消費(fèi)記錄的盈利合計(jì)，即SUM（(菜肴單價(jià)-菜肴成本單價(jià))×消費(fèi)數(shù)量）。6）消費(fèi)單明細(xì)表CList(消費(fèi)單號(hào)Cid，序號(hào)Sid，菜肴編號(hào)Mid，菜肴名稱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)菜。觸發(fā)器設(shè)計(jì)為消費(fèi)單明細(xì)表CList定義一個(gè)觸發(fā)器，每插入（INSERT）一條消費(fèi)單明細(xì)記錄（消費(fèi)單號(hào)，序號(hào)，菜肴編號(hào)，消費(fèi)數(shù)量），自動(dòng)根據(jù)菜肴編號(hào)從菜單信息表MList中讀取菜肴名稱Mname、菜肴單價(jià)Mprice、菜肴成本單價(jià)Mcost，然后計(jì)算其消費(fèi)金額（=消費(fèi)數(shù)量×菜肴單價(jià)）、以及消費(fèi)單主表C的消費(fèi)金額合計(jì)、盈利金額合計(jì)。編寫相應(yīng)的插入語(yǔ)句（INSERT）和查詢語(yǔ)句（SELECT）測(cè)試該觸發(fā)器效果。 為消費(fèi)單明細(xì)表 CList定義一個(gè)觸發(fā)器，每更新 UPDATE一條消費(fèi)單明細(xì)表記錄，自動(dòng)修改其消費(fèi)金額、以及消費(fèi)單主表 C的消費(fèi)金額合計(jì)、盈利金額合計(jì)。編寫相應(yīng)的更新語(yǔ)句（ UPDATE）和查詢語(yǔ)句（SELECT）測(cè)試該觸發(fā)器效果。 為消費(fèi)單明細(xì)表 CList定義一個(gè)觸發(fā)器，每刪除 DELETE一條消費(fèi)單明細(xì)表記錄自動(dòng)修改其消費(fèi)單主表 C的消費(fèi)金額合計(jì)、盈利金額合計(jì)。編寫相應(yīng)的刪除語(yǔ)句（DELETE）和查詢語(yǔ)句（SELECT）測(cè)試該觸發(fā)器效果。 將【】、【】、【】三個(gè)觸發(fā)器禁用 disable，重新編寫一個(gè)觸發(fā)器實(shí)現(xiàn)這三個(gè)觸發(fā)器的全部功能。編寫相應(yīng)的插入語(yǔ)句（ INSERT）、更新語(yǔ)句（UPDATE）、刪除語(yǔ)句（DELETE）和查詢語(yǔ)句（SELECT）測(cè)試該觸發(fā)器效果。存儲(chǔ)過(guò)程、自定義函數(shù)設(shè)計(jì) 設(shè)計(jì)一個(gè)自定義函數(shù) fGetDTSum，實(shí)現(xiàn)統(tǒng)計(jì)某年份給定餐臺(tái)類別的成本金額合計(jì)的功能，輸入?yún)?shù)是統(tǒng)計(jì)年份和餐臺(tái)類別， 返回?cái)?shù)據(jù)是成本金額合計(jì)。成本金額=消費(fèi)數(shù)量×菜肴成本單價(jià)。求年份的函數(shù)為 EXTRACT(YEARFROM日期字段)，本題：統(tǒng)計(jì)年份=EXTRACT(YEARFROMEndTime)，EndTime為結(jié)賬時(shí)間字段。 設(shè)計(jì)一個(gè)存儲(chǔ)過(guò)程 pGetKindSum，實(shí)現(xiàn)統(tǒng)計(jì)某年份給定菜肴類別的盈利金額合計(jì)的功能，輸入?yún)?shù)是統(tǒng)計(jì)年份和菜肴類別， 輸出參數(shù)是盈利金額合計(jì)。盈利金額=消費(fèi)數(shù)量 ×(菜肴單價(jià)- 菜肴成本單價(jià))。 編寫一段匿名PL/SQL程序塊，調(diào)用函數(shù)fGetDTSum，輸出2013年餐臺(tái)類別名為“包間”的成本金額合計(jì)；調(diào)用存儲(chǔ)過(guò)程 pGetKindSum，輸出2013年菜肴類別名為“魚(yú)類”的盈利金額合計(jì)。程序包設(shè)計(jì) 設(shè)計(jì)一個(gè)程序包，包名為 pkSUM，包括并實(shí)現(xiàn)【】和【】的函數(shù)及存儲(chǔ)過(guò)程功能，注意：先創(chuàng)建包頭 package，包頭創(chuàng)建成功后，再創(chuàng)建包體 packagebody。 設(shè)計(jì)一個(gè)匿名PL/SQL程序塊，參照【】調(diào)用【】中程序包的函數(shù)和存儲(chǔ)過(guò)程，輸出2013年餐臺(tái)類別名為“散臺(tái)”的成本金額合計(jì)，輸出 2013年菜肴類別名為“蔬菜類”的盈利金額合計(jì)。3. 實(shí)驗(yàn)步驟（備注：如果用實(shí)驗(yàn)室微機(jī)，請(qǐng)從【】開(kāi)始做，登錄用戶 DINER改為stuXX）創(chuàng)建表空間RESTAURANT,創(chuàng)建用戶DINER用戶SYSTEM登錄Oracle 創(chuàng)建表空間RESTAURANT，大小10M。CREATETABLESPACERESTAURANTDATAFILE'F:\' SIZE10MDEFAULTSTORAGE(INITIAL 10KNEXT50KMINEXTENTS1MAXEXTENTS99PCTINCREASE10)ONLINE;創(chuàng)建用戶DINER，口令XXX，默認(rèn)表空間 RESTAURANT，給該用戶授予角色權(quán)限CONNECT、RESOURCE。CREATEUSERDINERIDENTIFIEDBY"wzl123" DEFAULTTABLESPACERESTAURANT;GRANTCONNECTODINER;GRANTRESOURCETODINER;創(chuàng)建餐飲系統(tǒng)數(shù)據(jù)庫(kù)的所有表，并向各表插入演示數(shù)據(jù)啟動(dòng)PL/SQLDeveloper（或者啟動(dòng)SQL*PLUS、EnterpriseManagerConsole、瀏覽器模式的EM（企業(yè)管理器）等工具均可以），用戶DINER登錄Oracle。創(chuàng)建實(shí)驗(yàn)內(nèi)容中的餐飲系統(tǒng)數(shù)據(jù)庫(kù)的所有表(菜肴類別表MK、菜單信息表MList、餐臺(tái)類別表DK、餐臺(tái)信息表Dinfo、消費(fèi)單主表C、消費(fèi)單明細(xì)表CList)。create table MK(MKid number,MkName varchar2(64),constraint pk_MKid primary key(MKid));create table MList(Mid number,Mname varchar2(64),MKid numberreferences 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_DKid primary key(DKid));create table Dinfo (Did number,Dname varchar2(64),DKid numberreferences DK(DKid),Dseats number,Ddate date,constraint pk_Did primary key(Did));create table C(Cid number,Did numberreferences Dinfo (Did),StartTime date,EndTime date,Smoney number(8,2),SPsum number(8,2),constraint pk_Cid primary key(Cid));createtableCList(Sidnumber,CidnumberreferencesC(Cid),MidnumberreferencesMList(Mid),Mnamevarchar2(64),Cqtynumber,Mpricenumber(8,2),Mcostnumber(8,2),Cmoneynumber(8,2),constraintpk_Sidprimarykey(Sid)); 依次向菜肴類別表 MK、菜單信息表 MList、餐臺(tái)類別表 DK、餐臺(tái)信息表Dinfo插入足夠多的演示數(shù)據(jù)。insert into MKvalues(1,'魚(yú)類');insert into MKvalues(2,'蔬菜類');insert into MKvalues(3,'涼菜類');insertintoMKvalues(4,'肉類');insertintoMKvalues(5,'主食類');insertintoMKvalues(6,'酒水');insertintoMListvalues(1,'鯉魚(yú)',1,,,sysdate);insertintoMListvalues(2,'三文魚(yú)',1,,,sysdate);insertintoMListvalues(3,'白菜',2,,,sysdate);insertintoMListvalues(4,'土豆',2,,,sysdate);insertintoMListvalues(5,'油麥菜',2,,,sysdate);insertintoMListvalues(6,'涼拌黃瓜',3,,,sysdate);insertintoMListvalues(7,'雞肉',4,,,sysdate);insertintoMListvalues(8,'米飯',5,,,sysdate);insertintoMListvalues(9,'二鍋頭',6,,,sysdate);insert into DKvalues(1,'包間');insert into DKvalues(2,'散臺(tái)');insert into Dinfo values(1,'1號(hào)包間',1,20,sysdate);insertintoDinfovalues(2,'2號(hào)包間',1,30,sysdate);insertintoDinfovalues(3,'3號(hào)包間',1,50,sysdate);insertintoDinfovalues(4,'1號(hào)散臺(tái)',2,8,sysdate);insertintoDinfovalues(5,'2號(hào)散臺(tái)',2,8,sysdate);insertintoDinfovalues(6,'3號(hào)散臺(tái)',2,15,sysdate);insertintoCvalues(1,2,sysdate,sysdate,275,150);insertintoCvalues(2,2,sysdate,sysdate,155,80);insertintoCvalues(3,1,sysdate,sysdate,566,302);insertintoCvalues(4,2,sysdate,sysdate,89,53);insertintoCvalues(5,1,sysdate,sysdate,798,435);insertintoCListvalues(1,1,2,'三文魚(yú)',1,,,;insertintoCListvalues(2,1,5,'油麥菜',1,,,;insertintoCListvalues(3,1,9,'二鍋頭',2,,,;完成【實(shí)驗(yàn)內(nèi)容】中的觸發(fā)器、存儲(chǔ)過(guò)程、函數(shù)和程序包等功能設(shè)計(jì)，將程序腳本保存到文本文件中 在PL/SQLDeveloper環(huán)境下，用戶DINER登錄Oracle 新建SQL窗口 完成【 觸發(fā)器設(shè)計(jì)】(1) ORREPLACETRIGGERtri_CListBEFOREINSERTONCList FOREACHROWDECLAREt_Smoney%type;t_SPsum%type;BEGIN--補(bǔ)全MlistSELECTMname,Mprice,Mcost,:*Mprice INTO:,:,:,:FROMMlist WHERE=:;--計(jì)算Mlist

的增加量SELECTNVL(SUM(Cqty*Mprice),0),NVL(SUM(Cqty*(Mprice-Mcost)),0)INTOv_Smoney,v_SPsumFROMClist WHERE=:;--更新CupdateCsetSmoney=t_Smoney+:,SPsum=t_SPsum+(:*(::)where=:;ENDtri_CList ;(2) ORREPLACETRIGGERtri_upCList BEFOREUPDATEONCList FOREACHROWBEGINSELECTMname,Mprice,McostINTO:,:,:FROMMlist WHERE=:;::=NVL(:,:* NVL(:,:;UPDATECSETSmoney=Smoney-:+:,SPsum =SPsum-:*(::+:*(::WHERE=:;ENDtri_upCList ;(3) ORREPLACETRIGGERtri_delCListBEFOREDELETEONCList FOREACHROWBEGINUPDATECSET=:,=(:*(::)WHERE=:;ENDtri_delCList ;(4) ORREPLACETRIGGERtri_allBEFOREINSERTORUPDATEORDELETEONCListFOREACHROWDECLAREt_Smoney %type;t_SPsum %type;BEGINIFINSERTINGTHENSelect Mname,Mprice,Mcost,:*Mpriceinto :,:,:,:fromMlistwhere=:;Select nvl(sum(Cqty*Mprice),0),nvl(sum(Cqty*(Mprice-Mcost)),into t_Smoney,t_SPsumfromClistwhere=:;

0)updateCsetSmoney=t_Smoney+:,SPsum=t_SPsum+(:*(::)where=:;ELSIFUPDATINGTHENselect Mname,Mprice,Mcostinto :,:,:fromMlistwhere=:;::=nvl(:,:* nvl(:,:;UpdateCsetSmoney=Smoney-:+:,SPsum =SPsum-:*(::+:*(::where=:;ELSEUPDATECSET=:,=(:*(::);ENDIF;ENDtri_all

; 完成【 存儲(chǔ)過(guò)程、自定義函數(shù)設(shè)計(jì)】(1)

ORREPLACEFUNCTIONfGetDTSum(Cyearchar,Ctable %type)RETURN%TYPEAScons %TYPE;BEGINselect nvl(sum(spsum),0)into cons fromCwheredid in(select did fromdinfo wheredkid in (select dkid fromdk wheredkname=Ctable))and(to_char(endtime,'YYYY')=Cyear);RETURNcons;EXCEPTIONWHENO_DATA_FOUNDTHEN('Thedataisinvalid!'ENDfGetDTSum;

);(2) ORREPLACEPROCEDUREpGetKindSum(Cyearchar,Cnamechar,consout %TYPE)ASBEGINselect nvl(sum(mprice-mcost),0)into cons fromclistwheremid in(select mid frommlist wheremkid in(select mkid frommkwheremkname=Cname))andcid in(select cid fromCwhereto_char (endtime,'YYYY')=Cyear);EXCEPTIONWHENO_DATA_FOUNDTHEN('Thedatadoesn

’’texists!'

);ENDpGetKindSum;(3) cons1

%TYPE;cons2 %TYPE;BEGINcons1:=fGetDTSum('2013','包間');pGetKindSum('2013' ,'魚(yú)類',cons2);('cons1' ||cons1||'cons2' ||cons2||'');END; 完成【 程序包設(shè)計(jì)】(1) ORREPLACEPACKAGEpkSUMASFUNCTIONfGetDTSum(Cyearchar,Ctable %type)RETURN%TYPE;PROCEDUREpGetKindSum(Cyearchar,Cnamechar,consout %TYPE);ENDpkSUM;CREATEORREPLACEPACKAGEBODYpkSUMASCREATEORREPLACEFUNCTIONfGetDTSum(Cyearchar,Ctable %type)RETURN%TYPEAScons %TYPE;BEGINselect nvl(sum(spsum),0)into cons fromCwheredid in(select did fromdinfo wheredkid in (select dkid fromdk wheredkname=Ctable))and(to_char(endtime,'YYYY')=Cyear);RETURNcons;EXCEPTIONWHENO_DATA_FOUNDTHEN('Thedataisinvalid!' );ENDfGetDTSum;CREATEORREPLACEPROCEDUREpGetKindSum(Cyear char,Cnamechar,cons out %TYPE)ASBEGINselect nvl(sum(mprice-mcost),0) into cons fromclistwheremid in(select mid frommlist wheremkid in(select mkid frommkwheremkname=Cname))andcid in(select cid fromCwhereto_char (endtime,'YYYY')=Cyear);EXCEPTIONWHENO_DATA_FOUNDTHEN('Thedatadoesn ’’texists!' );ENDpGetKindSum;ENDpkSUM;CREATEORREPLACEPACKAGEBODYpkSUMASCREATEORREPLACEFUNCTIONfGetDTSum(Cyearchar,Ctable %type)