中南大学大型数据库报告(2026210502)

1、中南大学大型数据库报告学生计科120x?大型数据库技术?实验一1创立一个本地位图管理表空间CAP_ts，表空间对应一个数据文件CAP_ts.dbf，该数据文件初始大小为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中创立表 Customers、Products和Agents，其中列cid、pid、aid分

2、别为 这3张表的主键。向表中添加如下数据 可首先将表中数据放入 EXCEL表，然后在 SQL Developer中导入数据库。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.通过数据字典视图查看是否已创立表Customers、Products和Agents，以及每个表的存储参数设置。SELECT table_ nam

12、e,tablespace_ name, in itial_exte nt, next_exte ntFROM user_tables$7CU5T0IMEEScp_rs055381O48S76FftDBUCTSCA?_TS655361049576AGEHT5ckr_rs65536104SS764.在表空间CAP_ts中创立分区表 orders,该表以列ordno为主键，列cid、aid、pid为外键。 列mo nth作为分区关键字，数据按照季度分区，即将一个季度的订单数据放到一个分区中。例如一月份、二月份、三月份为第一季度，这三个月的订单记录放在一个分区中。向表orders中添加如下数据：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 在一季度分区中查询所有订单金额高于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、将二季度所有的订单记录复制到表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表创立公有同义词，并通过该同义词访问该表。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现704&从数据字典中查询当前用户创立的所有的同义词。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创立一个视图 customer_orders，视图中的列包括每笔订单的 编号、订购的产品编号、订购的数量、顾客的编号及顾客的。基于视图customer_orders查询顾客c002下的所有订单。通过数据字典表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. 利用内联视图，查询每个顾客的编号、名称、所在城市，折扣以及所下订单的数量。 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. 创立一个物化视图mv_product_orders，视图中包含每种商品的编号、名称和订货的总量。数据刷新的时间为ON COMMIT，即当主表有数据提交时，立即刷新物化视图中的数据，创立方式为BUILD IMMEDIATE 。1创立视图后，执行查询select * from mv_product_orders;2向表 Orders 中新增一行 insert into orders values(1027,'May','C006','A05','P05&

33、#39;,100,50)，然 后执行1中的查询，查看 mv_product_orders是否有变化；3执行Commit命令，然后执行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. 利用以下语句创立表my_table。create table my_table NOLOGGING as select * from all_objects;1查询表 my_table的行数。2执行查询 select * from my_table where object_ID=3，查看执行方案和 SQL优化指导。3在表my_table的列object_ID上创立索引，再次执行2中的查询，并查看执行方案和SQL优化指导。4查看索引树的高度、删除标记的比率以及索引页块使用率。1弟禾£)COUNTS)171&3323

36、4结果:町 HEIGJfT| D£L_Lf_RDV5/LF_RDWS gFCT lEin)|12099?大型数据库技术?实验二1执行下面的语句，了解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. 创立一个序列 orderno_sequenee,起始值为1000,步长为1。查看该序列是否被创立。create

37、 seque nee order no _seque neestart with 1000in creme nt by 1;secjuence CRDERMO 5EQIIEMCE 已创崖。3. (1)在表orders上定义一个触发器，当向表中参加一条新的订单记录时，自动使用序列 orderno_seque nee生成一个订单编号，并自动计算商品总价dollars。计算公式如下：商品总价dollars=商品数量qty*商品单价price * (1-顾客折扣discnt/100) 首先将表orders中的数据全部删除，然后向表中添加如下数据验证触发器的正确性。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);结累.S OR.lS CIDill MUIII FIDtil un:?l DOLLARS11010 Janco tnaDIPOL100045021017 JanCOOlaDlFOL100045031013FebCOOJ业P

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

48、CURRVAL，查询序列 orderno_sequenee的当前值。结果11.0315. 编写一个PL/SQL块，查询编号在1000到1020之间所有订单的月份、 订购的商品id号、 订购的数量和商品的总价，并利用 DBMS_OUTPUT.PUT_LINE 显示查询的结果。要求定义 一个表类型存储检索出来的数据，表中的元素是一条记录。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的产品的库存数量修改为200370,如果没有查找到相应的记录，那么在表中插入该条记录。要求：使用隐式游

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根据用户输入的 city 值，查询该城市中每个顾客下的订单的总额即 dollars 的总数。 要求定义一个存储过程， 以 city 值为参

52、数。 过程中定义以 city 为参数的游标， 逐个计算该城 市中每个顾客的订单金额的总额。要求以以下格式显示查询的结果：城市名称 =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创立一个存储过程，根据用户输入显示所有的顾客或所有代理商的级别。要求将表名 customers 或 agents 作为过程的参数，采用游标变量根据参数绑定不同的查询语句。如果某 顾客的折扣 discnt 低于 10.00，那么显示该顾客的级别为“普通 ，否那么显示为“ VIP ；如果某 代理商的佣金百分比低于 6，

55、那么显示该代理商的级别为“普通 ，否那么显示为“ VIP 。调用 过程时给定的参数错误时，显示用户自定义的错误信息： “ 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