中南大學(xué)大型數(shù)據(jù)庫報告(2026210502)

1、中南大學(xué)大型數(shù)據(jù)庫報告學(xué)生計科120x?大型數(shù)據(jù)庫技術(shù)?實驗一1創(chuàng)立一個本地位圖管理表空間CAP_ts，表空間對應(yīng)一個數(shù)據(jù)文件CAP_ts.dbf，該數(shù)據(jù)文件初始大小為20M，可以自動擴展。CREATE TABLESPACE CAP_ts datafile 'CAP_ts.dbf size 20M reuse exte nt ma nageme nt local autoallocate;ALTER DATABASE DEFAULT TABLESPACE CAP_ts;2.在表空間 CAP_ts中創(chuàng)立表 Customers、Products和Agents，其中列cid、pid、aid分

2、別為 這3張表的主鍵。向表中添加如下數(shù)據(jù) 可首先將表中數(shù)據(jù)放入 EXCEL表，然后在 SQL Developer中導(dǎo)入數(shù)據(jù)庫。Customerscidcn amecitydiscntC001TipTopDuluth10.00C002BasicsDallas12.00C003AlliedDallas8.00C004ACMEDuluth8.00C005Orie ntalKyoto6.00C006ACMEKyoto0.00Productspidpn amecityqua ntitypriceP01combDallas1114000.50P02brushNewark2030000.50P03razor

3、Duluth1506001.00P04PenDuluth1253001.00P05pencilDallas2214001.00P06folderDallas1231002.00P07caseNewark1005001.00Age ntsaidan amecityperce ntA01smithNew York6A02JonesNewark6A03Brow nTokyo7A04GrayNew York6A05OtasiDuluth5A06SmithDallas5CREATE TABLE Customers(cid VARCHAR(5) PRIMARY KEY NOT VARCHAR(10),ci

4、ty V ARCHAR(10),discount REAL);CREATETABLEProducts(pidVARCHAR(5)PRIMARYKEYNOTVARCHAR(10),ity V ARCHAR(10),quantity NUMBER(10),price REAL);CREATETABLEAge nts(aidVARCHAR(5)PRIMARYKEYNOTVARCHAR(10),city VARCHAR(10),perce nt NUMBER(3);NULL,c naemNULL,p naemNULL,a naemin sert into Customers values('C

5、001','TipTop','Duluth',10.00);in sert into Customers values('C002','Basics','Dallas',12.00);in sert into Customers values('C003','Allied','Dallas',8.00);in sert into Customers values('C004','ACME','Duluth',8.

6、00);in sert into Customers values('C005','Orie ntal','Kyoto',6.00);in sert into Customers values('C006','ACME','Kyoto',0.00);in sert into Products values('P01','comb','Dallas',111400,0.50);in sert into Products values('P02&#

7、39;,'brush','Newark',203000,0.50);in sert into Products values('P03','razor','Duluth',150600,1.00);in sert into Products values('P04','Pe n','Duluth',125300,1.00);in sert into Products values('P05','pe ncil','Dallas&

8、#39;,221400,1.00);in sert into Products values('P06','folde','Dallas',123100,2.00);in sert into Products values('P07','case','Newark',100500,1.00);in sert into Age nts values('A01','smith','New York',6); in sert into Age nts val

9、ues('A02','Jo nes','Newark',6);in sert into Age nts values('A03','Brow n'，Tokyo',7);in sert into Age nts values('A04','Gray','New York',6); in sert into Age nts values('A05','Otasi','Duluth',5);in sert into A

10、ge nts values('A06','smith','Dallas',5)出CITe|Ifi CITI |J卩斑HI1caoiTipTftpDulutKID2caoeEasi.csasIE3CO03AlliedDallasS4004Duluth85COOSOri eatKyoto&BcaoeACMEKyotci0j PIB 問 PME |囲 CITY| HJAJimY H pbke1 P(J1coabDall sc1114000.52 FOEbrnskNewu-k£03000GE3 P03raiorDulqth15DG0QL

11、4 P04FmDuluth12530015 PUSpencilDUXm22140016 POGfoldfeDallas1231002T P(JTN»wiirk1005001因AID| A1TANE目 CITY |PEMEHT1 AD1sni thMew iork52磁JonssNewark&3 JUD3BrownT okyo74GrayIkw Yirk&5 JUD501 as iDuluth5smi tlDall 3.553.通過數(shù)據(jù)字典視圖查看是否已創(chuàng)立表Customers、Products和Agents，以及每個表的存儲參數(shù)設(shè)置。SELECT table_ nam

12、e,tablespace_ name, in itial_exte nt, next_exte ntFROM user_tables$7CU5T0IMEEScp_rs055381O48S76FftDBUCTSCA?_TS655361049576AGEHT5ckr_rs65536104SS764.在表空間CAP_ts中創(chuàng)立分區(qū)表 orders,該表以列ordno為主鍵，列cid、aid、pid為外鍵。 列mo nth作為分區(qū)關(guān)鍵字，數(shù)據(jù)按照季度分區(qū)，即將一個季度的訂單數(shù)據(jù)放到一個分區(qū)中。例如一月份、二月份、三月份為第一季度，這三個月的訂單記錄放在一個分區(qū)中。向表orders中添加如下數(shù)據(jù)：Ord

13、ersordnomon thcidaidpidqtydollars1011JanC001A01P011000450.001012JanC001A01P011000450.001019FebC001A02P02400180.001017FebC001A06P03600540.001018FebC001A03P04600540.001023MarC001A04P05500450.001022MarC001A05P06400720.001025AprC001A05P07800720.001013JanC002A03P031000880.001026MayC002A05P03800704.001015

14、JanC003A03P0512001104.001014JanC003A03P0512001104.001021FebC004A06P011000460.001016JanC004A01P011000500.001020FebC005A03P07600600.001024MarC006A06P01800400.00create table orders(order no nu mber(4) primary key not n ull,month varchar(3),cidvarchar(5),aidvarchar(5),pidvarchar(5),qtynu mber(4),dollars

15、 real,con stra int cid_fk foreig n key (cid) references Customers(cid),con stra int aid_fk foreig n key (aid) references Age nts(aid),con stra int pid_fk foreig n key (pid) references Products(pid)partiti on by list(m on th)(PARTITION seas on1 VALUES ('Ja n', 'Feb', 'Mar'),PA

16、RTITION seaso n2 VALUES ('Apr', 'May');in sert into orders values(1011,'Ja n','C001','a01','P01',1000,450.00);in sert into orders values(1012,'Ja n','C001','a01','P01',1000,450.00);in sert into orders values(1019,'Fe

17、b','C001','a02','P02',400,180.00);in sert into orders values(1017,'Feb','C001','a06','P03',600,540.00);in sert into orders values(1018,'Feb','C001','a03','P04',600,540.00);in sert into orders values(1023,'

18、;Mar','C001','a04','P05',500,450.00);in sert into orders values(1022,'Mar','C001','a05','P06',400,720.00);in sert into orders values(1025,'Apr','C001','a05','P07',800,720.00);in sert into orders values(1013,&

19、#39;Ja n','C002','a03','P03',1000,880.00);in sert into orders values(1026,'May','C002','a05','P03',800,704.00);in sert into orders values(1015,'Ja n','C003','a03','P05',1200,1104.00);in sert into orders value

20、s(1014,'Ja n','C003','a03','P05',1200,1104.00); in sert into orders values(1021,'Feb','C004','a06','P01',1000,460.00); in sert into orders values(1016,'Ja n','C004','a01','P01',1000,500.00);in sert into o

21、rders values(1020,'Feb','C005','a03','P07',600,600.00);in sert into orders values(1024,'Mar','C006','a06','P01',800,400.00);select * from orders partiti on( seas on 1);9ORDEW囲 MOWTH19 CIDH AID pidH qty|(£ DOLLARSi1011C001aOlJ011000

22、45U21012JemC0014J01P011O0D4505101FetCOOTOE100ISO41017FelC001JOS600£40呂ioieFetC001迦rot迦540&10S3MarC001Mras500450T1022 MarC001aJ05?&100720a1013J831C002a03T03100091015JanC003aD3T0512001104W1014JanC003a03F051200non111021FelC004aJ05T011D00460121016JuiC0Q4J01100050013102CFetCOOSaJ03TOT6006001

23、41024MarCOOBJ01800400select * from orders partiti on( seas on 2);r aU CIDul AID2 FIB11 QTTIl DOLLARL1025 AprC001a05PU73007202C002>05P033007045 在一季度分區(qū)中查詢所有訂單金額高于400的訂單記錄。select * from orders partiti on( seas on1) where dollars>400 order by order no;0RTEW S|M01TTH j. CIDII AID：S FID f|QTY£

24、T0LLABS11011 TanC001aJOl?ai100045021012COOJdlT01100045031013C002aJ03T031000eao41014C003如TO51200110451015 TanC003a03?061200Ll(M&1016 JuCDMaJOl?011M&50071017 FabC001迦TD35108lOlGFelC001m500540g1020 Ftlcoos403jarr臥EDO10iozi rtiC004JOI100046011.10Z2HarC001?&m720u10S5 MmG(J01«D4TW5U04506.

25、將二季度所有的訂單記錄復(fù)制到表Orders_2中。create table orders_2 as select * from orders partiti on( seas on 2); select * from orders_2;111 ORDISHO111 MOUTH目CID| fID仙 D&ELAK11025 AprCOOl05P078007202102fiHsyC002s05F03aooTO47.為Orders表創(chuàng)立公有同義詞，并通過該同義詞訪問該表。create synonym orde2 for orders;select * from orde2;ORTEWMOHTH

26、 I CIDIAID£ FIT£| QTYg TOLLUS11011C001aJOl?aiiaoo45021012 JuC001MlJ01100045031019 FelC0014oaISO斗1017C001T0360054051016 FetCOQIIW510610Z3 a«rC001Z055004507COO遊TOGquo720a1013 JanC002aJ03T03iaooeaog1015 JuC003汕3JOS12001104101014C003a03ros12001104it1021 F Jcao4並T01iooa460u1016 JtnCQ04T011

27、000500131020C005aJ03?0T600GOO14104 MarC006迦T01SOD4 DO15102S JkprMOITOT6007201&10Z6 flayC002J03現(xiàn)704&從數(shù)據(jù)字典中查詢當(dāng)前用戶創(chuàng)立的所有的同義詞。select * from user_s ynonyms;11) SYBQNrOWl£IS T 心 LEJDHMERHI TABLE 帥艇li DI UHE1 SVSCATWOGSTSZ CATAW5STSSYSCATALOWJAWG(ruII)血詛L)3 TABSTSTKE(iLull)4 COLSTSGOL(null)5 TA

28、BQUUTASSTSTOQUOTAS(null)a SVSflLESSTS7 TUBUCSTUSTSSYSflLESmiZCSTH(null)(null)3 TRDDBCT_USEl_FROfILE 5TSTEH9 DEIIEESTSTEMSCLPLJrS_FJinDUCT_FRJOFILEDKDER2(ruII)9. 基于表customers和表orders創(chuàng)立一個視圖 customer_orders，視圖中的列包括每筆訂單的 編號、訂購的產(chǎn)品編號、訂購的數(shù)量、顧客的編號及顧客的?；谝晥Dcustomer_orders查詢顧客c002下的所有訂單。通過數(shù)據(jù)字典表user_updatable_

29、colu mns ,查看視圖 customer_orders中哪些列是可更新的列。create view customer_ordersasselect o.order no, o.pid,o.qty,o.cid,customers aemfrom orders o,customerswhere o.cid=customers.cid;select * from customer_orders where cid='C002：select * from user_updatable_colu mns where table_ name='customers：| ORDER1IO

30、3 FIDHl QTT 色 CID11 CMAEM11013 FO：1000 C00221025 PO3aoc coo2BASICS10. 利用內(nèi)聯(lián)視圖，查詢每個顧客的編號、名稱、所在城市，折扣以及所下訂單的數(shù)量。 select c.cid,c aem,c.city,c.disco un t,order1.qtyfrom customers c,(select cid,qty from orders) orderlwhere c.cid=order1.cid;£ CIDH CUAEM CITYrrs com iQTV1 CQ01TipTcpDuluth1010002 C001Ti p

31、TopDuluth1ft10003 0001TiplopDuluth104004 C0&1Ti plopDxil xith10600s coaiTipTftfDalnthID800s C001TipTcpDuluthW5007 C001TipTopDili litkID400a CODEBasicsDallasIEWOOg coosAllielDallas8120010 C003AlliedD 411658120011 CU04/OEDuluth3100012 C004ACMEDnlti+h8100013 CU05Ori ent口& eoo14 C006ACMEKyo 20eo

32、o15 coaiTipTcpDuluth10600iscsD alias1左eao11. 創(chuàng)立一個物化視圖mv_product_orders，視圖中包含每種商品的編號、名稱和訂貨的總量。數(shù)據(jù)刷新的時間為ON COMMIT，即當(dāng)主表有數(shù)據(jù)提交時，立即刷新物化視圖中的數(shù)據(jù)，創(chuàng)立方式為BUILD IMMEDIATE 。1創(chuàng)立視圖后，執(zhí)行查詢select * from mv_product_orders;2向表 Orders 中新增一行 insert into orders values(1027,'May','C006','A05','P05&

33、#39;,100,50)，然 后執(zhí)行1中的查詢，查看 mv_product_orders是否有變化；3執(zhí)行Commit命令，然后執(zhí)行1中的查詢，查看 mv_product_orders是否有變化。1create materialized view log on products;create materialized view mv_product_orderrefresh fast on commitasselect pid,p naem,qua ntity from Products;select * from mv_product_order;目.0 .S QUA .1 F01 comb

34、1114002 P血 brusk3 F03 r Mflr203000150MJD4 FQ4 pen1253005 POE puicil221400E FOG folder1231007 P07 caza1005002insert into orders values(1027,'May','C006','a05','P05',100,50);IQUA-.1 101111400brush203000S T03r az 打 r1506004 J04pu1253OU5 ?05pencil221400& roafol d<r

35、LSS10AT ?071005003) commit;12. 利用以下語句創(chuàng)立表my_table。create table my_table NOLOGGING as select * from all_objects;1查詢表 my_table的行數(shù)。2執(zhí)行查詢 select * from my_table where object_ID=3，查看執(zhí)行方案和 SQL優(yōu)化指導(dǎo)。3在表my_table的列object_ID上創(chuàng)立索引，再次執(zhí)行2中的查詢，并查看執(zhí)行方案和SQL優(yōu)化指導(dǎo)。4查看索引樹的高度、刪除標記的比率以及索引頁塊使用率。1弟禾£)COUNTS)171&3323

36、4結(jié)果:町 HEIGJfT| D£L_Lf_RDV5/LF_RDWS gFCT lEin)|12099?大型數(shù)據(jù)庫技術(shù)?實驗二1執(zhí)行下面的語句，了解ROWID的編碼方式。SELECT rowid FROM age nts;1 WlASaSAAB AAAlfgp AAA2 AAASsSAABAAAm3 AAASsSAABUAlTspAAC4 AAAS a5 AAB AAA 7 SpAAD5 AAAS出貼BAAAIT融AXE6 AAAS e5 AAB AAAV gp AAF2. 創(chuàng)立一個序列 orderno_sequenee,起始值為1000,步長為1。查看該序列是否被創(chuàng)立。create

37、 seque nee order no _seque neestart with 1000in creme nt by 1;secjuence CRDERMO 5EQIIEMCE 已創(chuàng)崖。3. (1)在表orders上定義一個觸發(fā)器，當(dāng)向表中參加一條新的訂單記錄時，自動使用序列 orderno_seque nee生成一個訂單編號，并自動計算商品總價dollars。計算公式如下：商品總價dollars=商品數(shù)量qty*商品單價price * (1-顧客折扣discnt/100) 首先將表orders中的數(shù)據(jù)全部刪除，然后向表中添加如下數(shù)據(jù)驗證觸發(fā)器的正確性。Ordersordnomon thci

38、daidpidqtydollarsJanC001A01P011000JanC001A01P011000FebC001A02P02400FebC001A06P03600FebC001A03P04600MarC001A04P05500MarC001A05P06400AprC001A05P07800JanC002A03P031000MayC002A05P03800JanC003A03P051200JanC003A03P051200FebC004A06P011000JanC004A01P011000FebC005A03P07600MarC006A06P01800create or replace tr

39、igger in sert_orders_triggerbefore in sert on ordersfor each rowdeclarev_order no orders.order no%type;v_price products.price%type;v_disco unt customers.disco un t%type;beginselect order no _seque nce.n extval into v_order no from dual;select price into v_price from products where products.pid=:n ew

40、.pid;select disco unt into v_disco unt from customers where customers.cid=:n ew.cid;:n ew.order no :=v_order no;:n ew.dollars :=(:n ew.qty*v_price*(1-v_disco un t/100);en d;(2) trun cate table orders;in sert into orders(m on th,cid,aid,pid,qty)values ('Ja n','C001','a01','

41、;P01',1000);insert into orders(month,cid,aid,pid,qty)values ('Ja n','C001','a01','P01',1000);insert into orders(month,cid,aid,pid,qty)values ('Feb','C001','a02','P02',400);insert into orders(month,cid,aid,pid,qty)values ('Feb

42、9;,'C001','a06','P03',600);insert into orders(month,cid,aid,pid,qty)values ('Feb','C001','a03','P04',600);insert into orders(month,cid,aid,pid,qty)values ('Mar','C001','a04','P05',500);insert into orders(month,ci

43、d,aid,pid,qty)values ('Mar','C001','a05','P06',400);insert into orders(month,cid,aid,pid,qty)values ('Apr','C001','a05','P07',800);insert into orders(month,cid,aid,pid,qty)values ('Ja n','C002','a03','P03'

44、;,1000);insert into orders(month,cid,aid,pid,qty)values ('May','C002','a05','P03',800); insert into orders(month,cid,aid,pid,qty) values ('Ja n'，C003','a03','P05',1200);insert into orders(month,cid,aid,pid,qty) values ('Ja n'，C003&#

45、39;,'a03','P05',1200);insert into orders(month,cid,aid,pid,qty) values ('Feb','C004','a06','P01',1000);insert into orders(month,cid,aid,pid,qty) values ('Ja n','C004','a01','P01',1000);insert into orders(month,cid,aid,pi

46、d,qty) values ('Feb','C005','a03','P07',600);insert into orders(month,cid,aid,pid,qty) values ('Mar','C006','a06','P01',800);結(jié)累.S OR.lS CIDill MUIII FIDtil un:?l DOLLARS11010 Janco tnaDIPOL100045021017 JanCOOlaDlFOL100045031013FebCOOJ業(yè)P

47、0240018041019001P0360054051020COOlu03P04600540E1021 IffCOOlP05£004507COQI砧mmTEQa1024 丁皿COOSP031000S809lOZb JanCO03F0512001104101027 JanCO 03P0512001104111028 Fel004POL10004B0121QE JaGV申rot1QQC犠Q131030 FefcCOOEPOTGOO5S4141031 MarCO06血FOL6004Q0151023 AprCOOlP07eoo72016lOZSNayCO02P03600T044. 通過偽列

48、CURRVAL，查詢序列 orderno_sequenee的當(dāng)前值。結(jié)果11.0315. 編寫一個PL/SQL塊，查詢編號在1000到1020之間所有訂單的月份、 訂購的商品id號、 訂購的數(shù)量和商品的總價，并利用 DBMS_OUTPUT.PUT_LINE 顯示查詢的結(jié)果。要求定義 一個表類型存儲檢索出來的數(shù)據(jù)，表中的元素是一條記錄。declaretype order_table_type is table of varchar(50)in dex by binary_in teger;order_table order_table_type;cursor order_cursor issel

49、ect mon th,pid,qty,dollars from orders ;v_mon th orders.mo nth%type;v_pid orders.pid%type;v_qty orders.qty%type;v_dollars orders.dollars%type;v_loop nu mber(3) :=1;v_data varchar(50);beginope n order_cursor;fetch order_cursor into v_mon th,v_pid,v_qty,v_dollars;while order_cursor%fo und loopv_data :

50、=v_month '|v_pid '|v_qty '|v_dollars ;order_table(v_loop) := v_data;DBMS_OUTPUT.PUT_LINE(order_table(v_loop);fetch order_cursor into v_mon th,v_pid,v_qty,v_dollars;v_loop :=v_loop+1;end loop;close order_cursor;en d;6. 編寫一個PL/SQL塊，將編號為p08的產(chǎn)品的庫存數(shù)量修改為200370,如果沒有查找到相應(yīng)的記錄，那么在表中插入該條記錄。要求：使用隱式游

51、標)。declareBEGINUPDATE productsSET qua ntity = 200370WHERE pid = 'P08'IF SQL%NOTFOUND THENINSERT INTO products (pid, qua ntity)VALUES ('P08',200370);END IF;END;select * from products;3 PCS (null) (null)2D037D (rmll)7根據(jù)用戶輸入的 city 值，查詢該城市中每個顧客下的訂單的總額即 dollars 的總數(shù)。 要求定義一個存儲過程， 以 city 值為參

52、數(shù)。 過程中定義以 city 為參數(shù)的游標， 逐個計算該城 市中每個顧客的訂單金額的總額。要求以以下格式顯示查詢的結(jié)果：城市名稱 =xxxxx顧客編號 =xxxx 訂單總額 =xxxxxxcreate or replace procedure order_precedure(city in customers.city%type) iscursor order_cursor (citys in customers.city%type) is select o.cid,sum(o.dollars) from orders o,customers where o.cid=customers.cid

53、 and customers.city=citys group by o.cid;v_cid orders.cid%type; v_dollars orders.dollars%type;beginopen order_cursor (city ;fetch order_cursor into v_cid,v_dollars;while order_cursor%found loop DBMS_OUTPUT.PUT_LINE(' 城市名稱 ='|city); DBMS_OUTPUT.PUT_LINE(' 顧客編號 ='|v_cid|' '|

54、9;訂單總額 ='|v_dollars); fetch order_cursor into v_cid,v_dollars;end loop;close order_cursor;end;execute order_precedure('Duluth'); 8創(chuàng)立一個存儲過程，根據(jù)用戶輸入顯示所有的顧客或所有代理商的級別。要求將表名 customers 或 agents 作為過程的參數(shù)，采用游標變量根據(jù)參數(shù)綁定不同的查詢語句。如果某 顧客的折扣 discnt 低于 10.00，那么顯示該顧客的級別為“普通 ，否那么顯示為“ VIP ；如果某 代理商的傭金百分比低于 6，

55、那么顯示該代理商的級別為“普通 ，否那么顯示為“ VIP 。調(diào)用 過程時給定的參數(shù)錯誤時，顯示用戶自定義的錯誤信息： “ Input must be ''customers'' or ''agents''。create or replace procedure order_precedure(city in customers.city%type) iscursor order_cursor (citys in customers.city%type) is select o.cid,sum(o.dollars) from ord

56、ers o,customers where o.cid=customers.cid and customers.city=citys group by o.cid;v_cid orders.cid%type;v_dollars orders.dollars%type;beginopen order_cursor (city ;fetch order_cursor into v_cid,v_dollars;while order_cursor%found loopDBMS_OUTPUT.PUT_LINE(' 城市名稱 ='|city);DBMS_OUTPUT.PUT_LINE(' 顧客編號 ='|v_cid|' '|'訂單總額 ='|v_dollars); fetch order_cursor into v_cid,v_dollars;end loop;close order_cursor (city);end;CREATE OR REPLACE PROCEDURE ShowCustomersLevel(p_Table IN V ARCHAR2) AS- 定義游標變量類型 t_ClassesRoom