大型數(shù)據(jù)庫(kù)技術(shù)實(shí)驗(yàn)報(bào)告

1、大型數(shù)據(jù)庫(kù)技術(shù)實(shí)驗(yàn)報(bào)告 專業(yè)班級(jí)： 學(xué) 號(hào)： 姓 名： 時(shí) 間：2014年11月17日 大型數(shù)據(jù)庫(kù)技術(shù)實(shí)驗(yàn)一1創(chuàng)建一個(gè)本地位圖管理表空間cap_ts，表空間對(duì)應(yīng)一個(gè)數(shù)據(jù)文件cap_ts.dbf，該數(shù)據(jù)文件初始大小為20m，可以自動(dòng)擴(kuò)展。解：create tablespace cap_ts datafile 'd:cap_ts.dbf' size 20m autoextend on;2在表空間cap_ts中創(chuàng)建表customers、products和agents，其中列cid、pid、aid分別為這3張表的主鍵。向表中添加如下數(shù)據(jù)(可首先將表中數(shù)據(jù)放入excel表，然后在sql

2、developer中導(dǎo)入數(shù)據(jù)庫(kù))。customerscidcnamecitydiscntc001tiptopduluth10.00c002basicsdallas12.00c003allieddallas8.00c004acmeduluth8.00c005orientalkyoto6.00c006acmekyoto0.00 productspidpnamecityquantitypricep01combdallas1114000.50p02brushnewark2030000.50p03razorduluth1506001.00p04penduluth1253001.00p05pencilda

3、llas2214001.00p06folderdallas1231002.00p07casenewark1005001.00 agentsaidanamecitypercenta01smithnew york6a02jonesnewark6a03browntokyo7a04graynew york6a05otasiduluth5a06smithdallas5解：create table customers( cid char(6) primary key, cname varchar2(20), city varchar2(20), discnt number(10,2) tablespace

4、 cap_ts;insert into customers values('c001','tiptop','duluth',10.00);insert into customers values('c002','basics','dallas',12.00);insert into customers values('c003','allied','dallas',8.00);insert into customers values('c004

5、','acme','duluth',8.00);insert into customers values('c005','oriental','kyoto',6.00);insert into customers values('c006','acme','kyoto',0.00);create table products( pid char(5) primary key, pname varchar2(20), city varchar2(20), qua

6、ntity int, price number(10,2) tablespace cap_ts;insert into products values('p01','comb','dallas',111400,0.50);insert into products values('p02','brush','newark',203000,0.50);insert into products values('p03','razor','duluth',15

7、0600,1.00);insert into products values('p04','pen','duluth',125300,1.00);insert into products values('p05','pencil','dallas',221400,1.00);insert into products values('p06','floder','dallas',123100,2.00);insert into products valu

8、es('p07','case','newark',100500,1.00);create table agents( aid char(6) primary key, aname varchar2(20), city varchar2(20), percent int) tablespace cap_ts;insert into agents values('a01','smith','new york',6);insert into agents values('a02','

9、;jones','newark',6);insert into agents values('a03','brown','tokyo',7);insert into agents values('a04','gray','new york',6);insert into agents values('a05','otasi','duluth',5);insert into agents values('a06',

10、'smith','dallas',5);3 通過數(shù)據(jù)字典視圖查看是否已創(chuàng)建表customers、products和agents，以及每個(gè)表的存儲(chǔ)參數(shù)設(shè)置。解：select table_name, tablespace_name, status, ini_trans, max_trans, initial_extent, next_extent, min_extents, max_extents from user_tables where tablespace_name='cap_ts'4在表空間cap_ts中創(chuàng)建分區(qū)表orders，該表以列or

11、dno為主鍵，列cid、aid、pid為外鍵。列month作為分區(qū)關(guān)鍵字，數(shù)據(jù)按照季度分區(qū)，即將一個(gè)季度的訂單數(shù)據(jù)放到一個(gè)分區(qū)中。例如一月份、二月份、三月份為第一季度，這三個(gè)月的訂單記錄放在一個(gè)分區(qū)中。向表orders中添加如下數(shù)據(jù)：ordersordnomonthcidaidpidqtydollars1011janc001a01p011000450.001012janc001a01p011000450.001019febc001a02p02400180.001017febc001a06p03600540.001018febc001a03p04600540.001023marc001a04p0

12、5500450.001022marc001a05p06400720.001025aprc001a05p07800720.001013janc002a03p031000880.001026mayc002a05p03800704.001015janc003a03p0512001104.001014janc003a03p0512001104.001021febc004a06p011000460.001016janc004a01p011000500.001020febc005a03p07600600.001024marc006a06p01800400.00解：create table orders(

13、ordno int not null, cid char(6) , aid char(6) , pid char(5) , month char(5), qty int, dollars number(8,2), primary key(ordno), foreign key(cid) references customers(cid), foreign key(aid) references agents(aid), foreign key(pid) references products(pid)partition by list(month)( partition part_spring

14、 values ('jan','feb','mar'), partition part_summer values ('apr','may','jun'), partition part_autumn values ('jul','aug','sep'), partition part_winter values ('oct','nov','dec');desc orders;-向表orders中插入數(shù)據(jù)

15、insert into orders values(1011,'jan','c001','a01','p01',1000,450.00);insert into orders values(1012,'jan','c001','a01','p01',1000,450.00);insert into orders values(1019,'feb','c001','a02','p02',400,180.00

16、);insert into orders values(1017,'feb','c001','a06','p03',600,540.00);insert into orders values(1018,'feb','c001','a03','p04',600,540.00);insert into orders values(1023,'mar','c001','a04','p05',500,450.00

17、);insert into orders values(1022,'mar','c001','a05','p06',400,720.00);insert into orders values(1025,'apr','c001','a05','p07',800,720.00);insert into orders values(1013,'jan','c002','a03','p03',1000,880.0

18、0);insert into orders values(1026,'may','c002','a05','p03',800,704.00);insert into orders values(1015,'jan','c003','a03','p05',1200,1104.00);insert into orders values(1014,'jan','c003','a03','p05',1200,11

19、04.00);insert into orders values(1021,'feb','c004','a06','p01',1000,460.00);insert into orders values(1016,'jan','c004','a01','p01',1000,500.00);insert into orders values(1020,'feb','c005','a03','p07',600

20、,600.00);insert into orders values(1024,'mar','c006','a06','p01',800,400.00);commit;select * from orders;5 在一季度分區(qū)中查詢所有訂單金額高于400的訂單記錄。解：select * from orders partition(part_spring) where dollars>400 ;6 將二季度所有的訂單記錄復(fù)制到表orders_2中。解：create table orders_2 asselect * from

21、orders partition(part_summer);select * from orders_27 為orders表創(chuàng)建公有同義詞，并通過該同義詞訪問該表。解：create public synonym ord for orders;select * from ord;8 從數(shù)據(jù)字典中查詢當(dāng)前用戶創(chuàng)建的所有的同義詞。解：select * from dba_synonyms where table_owner='xiao'9 基于表customers和表orders創(chuàng)建一個(gè)視圖customer_orders，視圖中的列包括每筆訂單的編號(hào)、訂購(gòu)的產(chǎn)品編號(hào)、訂購(gòu)的數(shù)量、顧客的

22、編號(hào)及顧客的姓名?；谝晥Dcustomer_orders查詢顧客c002下的所有訂單。通過數(shù)據(jù)字典表user_updatable_columns，查看視圖customer_orders中哪些列是可更新的列。解：create or replace view customer_orders as select ordno,pid,qty,customers.cid,cname from customers, orders where customers.cid=orders.cid;select * from customer_orders where cid='c002'sele

23、ct * from user_updatable_columns where table_name='customer_orders'10. 利用內(nèi)聯(lián)視圖，查詢每個(gè)顧客的編號(hào)、名稱、所在城市，折扣以及所下訂單的數(shù)量。解：select * from (select customers.cid,cname,city,discnt,qty from customers, orders where customers.cid=orders.cid);11. 創(chuàng)建一個(gè)物化視圖mv_product_orders，視圖中包含每種商品的編號(hào)、名稱和訂貨的總量。數(shù)據(jù)刷新的時(shí)間為on commit

24、，即當(dāng)主表有數(shù)據(jù)提交時(shí)，立即刷新物化視圖中的數(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',100,50)，然后執(zhí)行1）中的查詢，查看mv_product_orders是否有變化； 3）執(zhí)行commit命令，然后執(zhí)行1）中的查詢，查看mv_product_orders是否有變化。解：create ma

25、terialized view mv_product_orders refresh on commit as select products.pid ,pname,qty from products, orders where products.pid=orders.pid;- 1）創(chuàng)建視圖后，執(zhí)行查詢select * from mv_product_orders;select * from mv_product_orders-2）向表orders中新增一行insert into orders values(1027,'may','c006','a05&

26、#39;,'p05',100,50)，然后執(zhí)行1）中的查詢-，查看mv_product_orders是否有變化；insert into orders values(1027,'may','c006','a05','p05',100,50)- 3）執(zhí)行commit命令，然后執(zhí)行1）中的查詢，查看mv_product_orders是否有變化。commit12. 利用下列語(yǔ)句創(chuàng)建表my_table。 create table my_table nologging as select * from all_objects;

27、 1）查詢表my_table的行數(shù)。 2）執(zhí)行查詢 select * from my_table where object_id=3，查看執(zhí)行計(jì)劃和sql優(yōu)化指導(dǎo)。3）在表my_table的列object_id 上創(chuàng)建索引，再次執(zhí)行2）中的查詢，并查看執(zhí)行計(jì)劃和sql優(yōu)化指導(dǎo)。 4）查看索引樹的高度、刪除標(biāo)記的比率以及索引頁(yè)塊使用率。解：- 1）查詢表my_table的行數(shù)。 select count(*) from my_table- 2）執(zhí)行查詢 select * from my_table where object_id=3，查看執(zhí)行計(jì)劃和sql優(yōu)化指導(dǎo)。 select * from m

28、y_table where object_id=3-3）在表my_table的列object_id 上創(chuàng)建索引，再次執(zhí)行2）中的查詢，并查看執(zhí)行計(jì)劃和sql優(yōu)化指導(dǎo)。 create index myindex on my_table(object_id) commit- 4）查看索引樹的高度、刪除標(biāo)記的比率以及索引頁(yè)塊使用率。select * from dba_indexes where index_name='myindex'-需要先分析索引的結(jié)構(gòu)analyze index myindex validate structure;select height,del_lf_row

29、s/lf_rows,pct_used from index_stats;附：cap數(shù)據(jù)庫(kù)cap數(shù)據(jù)庫(kù)系統(tǒng)主要由customers、agents和products三張表和一張訂貨信息表orders組成。批發(fā)商用cap數(shù)據(jù)庫(kù)記錄他們的顧客、商品和接受顧客訂單的代理商的信息。這里的顧客是指從批發(fā)商那里批發(fā)大量商品然后轉(zhuǎn)銷的零售商，每個(gè)顧客有唯一的標(biāo)識(shí)符。顧客向代理商要求購(gòu)買商品，代理商和商品都有唯一的標(biāo)識(shí)。每次訂貨都會(huì)在orders中間增加一條訂單記錄，用orderno唯一標(biāo)識(shí)該記錄。customers 存放顧客信息的表 cid 顧客的唯一標(biāo)識(shí) cname 顧客的名稱 city 顧客所在的城市 di

30、scnt 顧客享有的折扣agents 存放代理商信息的表 aid 代理商的唯一標(biāo)識(shí) aname 代理商的名稱 city 代理商所在的城市 percent 每筆交易代理所能獲得的傭金百分比products 存放商品的信息的表 pid 商品的唯一標(biāo)識(shí) pname 商品的名稱 city 商品庫(kù)存所在的城市 quantity 目前可銷售的商品庫(kù)存數(shù)量 price 每單位商品的批發(fā)價(jià)orders 存放訂單信息的表 orderno 訂單的唯一標(biāo)識(shí) month 訂單月份 cid 購(gòu)買商品的顧客 aid 經(jīng)由該代理商訂貨 pid 所訂購(gòu)的商品 qty 訂購(gòu)的商品數(shù)量 dollars 商品的總價(jià)cap數(shù)據(jù)庫(kù)中所

31、有的表和列的定義如下： 大型數(shù)據(jù)庫(kù)技術(shù)實(shí)驗(yàn)二1執(zhí)行下面的語(yǔ)句，了解rowid的編碼方式。解：select rowid from agents; 2創(chuàng)建一個(gè)序列orderno_sequence，起始值為1000，步長(zhǎng)為1。查看該序列是否被創(chuàng)建。解：create sequence orderno_sequence increment by 1start with 1000;commit;select * from dba_sequences where sequence_owner ='wujiyang'3(1) 在表orders上定義一個(gè)觸發(fā)器，當(dāng)向表中加入一條新的訂單記錄時(shí)，自

32、動(dòng)使用序列orderno_sequence生成一個(gè)訂單編號(hào)，并自動(dòng)計(jì)算商品總價(jià)dollars。計(jì)算公式如下：商品總價(jià)dollars= 商品數(shù)量qty* 商品單價(jià)price * (1-顧客折扣discnt/100) (2) 首先將表orders中的數(shù)據(jù)全部刪除，然后向表中添加如下數(shù)據(jù)驗(yàn)證觸發(fā)器的正確性。ordersordnomonthcidaidpidqtydollarsjanc001a01p011000janc001a01p011000febc001a02p02400febc001a06p03600febc001a03p04600marc001a04p05500marc001a05p06400

33、aprc001a05p07800janc002a03p031000mayc002a05p03800janc003a03p051200janc003a03p051200febc004a06p011000janc004a01p011000febc005a03p07600marc006a06p01800解：create or replace trigger calu_orders before insert on orders for each rowdeclare t_price products.price%type t_discnt customers.discnt%typebegin sel

34、ect orderno_sequence .nextval into :new.orderno from dual; select price into t_price from products where pid= :new.pid; select discnt into t_discnt from customers where cid= :new.cid; insert into orders values(:new.orderno,:new.month,:new.cid,:new.aid,:new.pid,:new.qty,:new.qty*t_price*(1-t_discnt);

35、end calu_orders drop trigger calu_orders select * from orders delete from orders insert into orders(month,cid,aid,pid,qty)values('jan','c001','a01','p01',1000);4通過偽列currval，查詢序列orderno_sequence的當(dāng)前值。解：select * from user_sequences where sequence_name='orderno_sequen

36、ce' select orderno_sequence.currval from dual;select orderno_sequence.nextval from dual;5 編寫一個(gè)pl/sql塊，查詢編號(hào)在1000到1020之間所有訂單的月份、訂購(gòu)的商品id號(hào)、訂購(gòu)的數(shù)量和商品的總價(jià)，并利用dbms_output.put_line顯示查詢的結(jié)果。要求定義一個(gè)表類型存儲(chǔ)檢索出來(lái)的數(shù)據(jù)，表中的元素是一條記錄。解：set serveroutput on;declare type ordersearch is table of orders%rowtype index by binar

37、y_integer; s_orders ordersearch; s_num number:=0; cursor cur_ord is select * from orders where ordno >=1000 and ordno<=1020; begin for temp in cur_ord loop s_num :=s_num+1; select * into s_orders(s_num) from orders where ordno=temp.ordno; end loop ; for i in 1.s_orders.count loop dbms_output.p

38、ut_line(s_orders(i).month |','| s_orders(i).pid |','| s_orders(i).qty | ',' | s_orders(i).dollars);end loop ;end;6編寫一個(gè)pl/sql塊，將編號(hào)為p08的產(chǎn)品的庫(kù)存數(shù)量修改為200370，如果沒有查找到相應(yīng)的記錄，則在表中插入該條記錄。（要求：使用隱式游標(biāo))。 解：begin update products set quantity = 200370 where pid = 'p08' if sql%notfound

39、then insert into products (pid, quantity) values ('p08',200370); end if;end;7根據(jù)用戶輸入的city值，查詢?cè)摮鞘兄忻總€(gè)顧客下的訂單的總額（即dollars的總數(shù)）。要求定義一個(gè)存儲(chǔ)過程，以city值為參數(shù)。過程中定義以city為參數(shù)的游標(biāo)，逐個(gè)計(jì)算該城市中每個(gè)顧客的訂單金額的總額。要求以下列格式顯示查詢的結(jié)果：城市名稱=xxxxx顧客編號(hào)=xxxx 訂單總額=xxxxxx解：set serveroutput on;create or replace procedure proc_search(p_ci

40、ty customers.city%type)as temp_city customers.city%type:= p_city; cursor tempcursor is select customers.city,customers.cid,dollars from customers, orders where customers.city= temp_city and customers.cid=orders.cid ;begin for temp1 in tempcursor loop dbms_output.put_line('城市名稱='|temp1.city);

41、 dbms_output.put_line('顧客編號(hào)='|temp1.cid|' '|'訂單總額='|temp1.dollars); end loop ;end proc_search;drop procedure proc_search;begin proc_search('kyoto');end;8創(chuàng)建一個(gè)存儲(chǔ)過程，根據(jù)用戶輸入顯示所有的顧客或所有代理商的級(jí)別。要求將表名customers或agents作為過程的參數(shù)，采用游標(biāo)變量根據(jù)參數(shù)綁定不同的查詢語(yǔ)句。如果某顧客的折扣discnt低于10.00，則顯示該顧客的級(jí)別為“普

42、通”，否則顯示為“vip”；如果某代理商的傭金百分比低于6，則顯示該代理商的級(jí)別為“普通”，否則顯示為“vip”。調(diào)用過程時(shí)給定的參數(shù)錯(cuò)誤時(shí)，顯示用戶自定義的錯(cuò)誤信息：“input must be ''customers'' or ''agents''”。解：set serveroutput on;create or replace procedure proc_2(p_table in varchar2)as-定義游標(biāo)變量類型并創(chuàng)建該類游標(biāo)type t_cursor is ref cursor;p_cursor t_cursor

43、;p_aid char(20);p_percent number(5,2);p_cid char(20);p_discnt number(5,2);begin if p_table='customers' then open p_cursor for select cid,discnt from customers; elsif p_table='agents' then open p_cursor for select aid,percent from agents; else raise_application_error(-20000,'input must be customers or agents'); end if; loop if p_table='customers' then fetch p_cursor into p_cid, p_discnt; exit when p_cursor%notfound; if p_discnt<10.00 then dbms_output.put_line('顧客'|p_cid|'的等級(jí)為：'|'普通'); else dbms_output.put_line('顧客'