大型数据库技术实验报告.doc

大型数据库技术实验报告 专业班级： 学 号： 姓 名： 时 间：2014年11月17日 大型数据库技术实验一1创建一个本地位图管理表空间cap_ts，表空间对应一个数据文件cap_ts.dbf，该数据文件初始大小为20m，可以自动扩展。解：create tablespace cap_ts datafile d:cap_ts.dbf size 20m autoextend on;2在表空间cap_ts中创建表customers、products和agents，其中列cid、pid、aid分别为这3张表的主键。向表中添加如下数据(可首先将表中数据放入excel表，然后在sql developer中导入数据库)。customerscidcnamecitydiscntc001tiptopduluth10.00c002basicsdallas12.00c003allieddallas8.00c004acmeduluth8.00c005orientalkyoto6.00c006acmekyoto0.00 productspidpnamecityquantitypricep01combdallas1114000.50p02brushnewark2030000.50p03razorduluth1506001.00p04penduluth1253001.00p05pencildallas2214001.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 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,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), quantity 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,150600,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 values(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,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,smith,dallas,5);3 通过数据字典视图查看是否已创建表customers、products和agents，以及每个表的存储参数设置。解：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中创建分区表orders，该表以列ordno为主键，列cid、aid、pid为外键。列month作为分区关键字，数据按照季度分区，即将一个季度的订单数据放到一个分区中。例如一月份、二月份、三月份为第一季度，这三个月的订单记录放在一个分区中。向表orders中添加如下数据：ordersordnomonthcidaidpidqtydollars1011janc001a01p011000450.001012janc001a01p011000450.001019febc001a02p02400180.001017febc001a06p03600540.001018febc001a03p04600540.001023marc001a04p05500450.001022marc001a05p06400720.001025aprc001a05p07800720.001013janc002a03p031000880.001026mayc002a05p03800704.001015janc003a03p0512001104.001014janc003a03p0512001104.001021febc004a06p011000460.001016janc004a01p011000500.001020febc005a03p07600600.001024marc006a06p01800400.00解：create table orders( 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 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中插入数据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);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);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.00);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,1104.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,600.00);insert into orders values(1024,mar,c006,a06,p01,800,400.00);commit;select * from orders;5 在一季度分区中查询所有订单金额高于400的订单记录。解：select * from orders partition(part_spring) where dollars400 ;6 将二季度所有的订单记录复制到表orders_2中。解：create table orders_2 asselect * from orders partition(part_summer);select * from orders_27 为orders表创建公有同义词，并通过该同义词访问该表。解：create public synonym ord for orders;select * from ord;8 从数据字典中查询当前用户创建的所有的同义词。解：select * from dba_synonyms where table_owner=xiao9 基于表customers和表orders创建一个视图customer_orders，视图中的列包括每笔订单的编号、订购的产品编号、订购的数量、顾客的编号及顾客的姓名。基于视图customer_orders查询顾客c002下的所有订单。通过数据字典表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;select * from user_updatable_columns where table_name=customer_orders;10. 利用内联视图，查询每个顾客的编号、名称、所在城市，折扣以及所下订单的数量。解：select * from (select customers.cid,cname,city,discnt,qty from customers, orders where customers.cid=orders.cid);11. 创建一个物化视图mv_product_orders，视图中包含每种商品的编号、名称和订货的总量。数据刷新的时间为on commit，即当主表有数据提交时，立即刷新物化视图中的数据，创建方式为build immediate。 1）创建视图后，执行查询select * from mv_product_orders; 2）向表orders中新增一行insert into orders values(1027,may,c006,a05,p05,100,50)，然后执行1）中的查询，查看mv_product_orders是否有变化； 3）执行commit命令，然后执行1）中的查询，查看mv_product_orders是否有变化。解：create materialized view mv_product_orders refresh on commit as select products.pid ,pname,qty from products, orders where products.pid=orders.pid;- 1）创建视图后，执行查询select * from mv_product_orders;select * from mv_product_orders-2）向表orders中新增一行insert into orders values(1027,may,c006,a05,p05,100,50)，然后执行1）中的查询-，查看mv_product_orders是否有变化；insert into orders values(1027,may,c006,a05,p05,100,50)- 3）执行commit命令，然后执行1）中的查询，查看mv_product_orders是否有变化。commit12. 利用下列语句创建表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）查询表my_table的行数。 select count(*) from my_table- 2）执行查询 select * from my_table where object_id=3，查看执行计划和sql优化指导。 select * from my_table where object_id=3-3）在表my_table的列object_id 上创建索引，再次执行2）中的查询，并查看执行计划和sql优化指导。 create index myindex on my_table(object_id) commit- 4）查看索引树的高度、删除标记的比率以及索引页块使用率。select * from dba_indexes where index_name=myindex-需要先分析索引的结构analyze index myindex validate structure;select height,del_lf_rows/lf_rows,pct_used from index_stats;附：cap数据库cap数据库系统主要由customers、agents和products三张表和一张订货信息表orders组成。批发商用cap数据库记录他们的顾客、商品和接受顾客订单的代理商的信息。这里的顾客是指从批发商那里批发大量商品然后转销的零售商，每个顾客有唯一的标识符。顾客向代理商要求购买商品，代理商和商品都有唯一的标识。每次订货都会在orders中间增加一条订单记录，用orderno唯一标识该记录。customers 存放顾客信息的表 cid 顾客的唯一标识 cname 顾客的名称 city 顾客所在的城市 discnt 顾客享有的折扣agents 存放代理商信息的表 aid 代理商的唯一标识 aname 代理商的名称 city 代理商所在的城市 percent 每笔交易代理所能获得的佣金百分比products 存放商品的信息的表 pid 商品的唯一标识 pname 商品的名称 city 商品库存所在的城市 quantity 目前可销售的商品库存数量 price 每单位商品的批发价orders 存放订单信息的表 orderno 订单的唯一标识 month 订单月份 cid 购买商品的顾客 aid 经由该代理商订货 pid 所订购的商品 qty 订购的商品数量 dollars 商品的总价cap数据库中所有的表和列的定义如下： 大型数据库技术实验二1执行下面的语句，了解rowid的编码方式。解：select rowid from agents; 2创建一个序列orderno_sequence，起始值为1000，步长为1。查看该序列是否被创建。解：create sequence orderno_sequence increment by 1start with 1000;commit;select * from dba_sequences where sequence_owner =wujiyang3(1) 在表orders上定义一个触发器，当向表中加入一条新的订单记录时，自动使用序列orderno_sequence生成一个订单编号，并自动计算商品总价dollars。计算公式如下：商品总价dollars= 商品数量qty* 商品单价price * (1-顾客折扣discnt/100) (2) 首先将表orders中的数据全部删除，然后向表中添加如下数据验证触发器的正确性。ordersordnomonthcidaidpidqtydollarsjanc001a01p011000janc001a01p011000febc001a02p02400febc001a06p03600febc001a03p04600marc001a04p05500marc001a05p06400aprc001a05p07800janc002a03p031000mayc002a05p03800janc003a03p051200janc003a03p051200febc004a06p011000janc004a01p011000febc005a03p07600marc006a06p01800解：create or replace trigger calu_orders before insert on orders for each rowdeclare t_price products.price%type t_discnt customers.discnt%typebegin select 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);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的当前值。解：select * from user_sequences where sequence_name=orderno_sequence select orderno_sequence.currval from dual;select orderno_sequence.nextval from dual;5 编写一个pl/sql块，查询编号在1000到1020之间所有订单的月份、订购的商品id号、订购的数量和商品的总价，并利用dbms_output.put_line显示查询的结果。要求定义一个表类型存储检索出来的数据，表中的元素是一条记录。解：set serveroutput on;declare type ordersearch is table of orders%rowtype index by binary_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.put_line(s_orders(i).month |,| s_orders(i).pid |,| s_orders(i).qty | , | s_orders(i).dollars);end loop ;end;6编写一个pl/sql块，将编号为p08的产品的库存数量修改为200370，如果没有查找到相应的记录，则在表中插入该条记录。（要求：使用隐式游标)。 解：begin update products set quantity = 200370 where pid = p08; if sql%notfound then insert into products (pid, quantity) values (p08,200370); end if;end;7根据用户输入的city值，查询该城市中每个顾客下的订单的总额（即dollars的总数）。要求定义一个存储过程，以city值为参数。过程中定义以city为参数的游标，逐个计算该城市中每个顾客的订单金额的总额。要求以下列格式显示查询的结果：城市名称=xxxxx顾客编号=xxxx 订单总额=xxxxxx解：set serveroutput on;create or replace procedure proc_search(p_city 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); dbms_output.put_line(顾客编号=|temp1.cid| |订单总额=|temp1.dollars); end loop ;end proc_search;drop procedure proc_search;begin proc_search(kyoto);end;8创建一个存储过程，根据用户输入显示所有的顾客或所有代理商的级别。要求将表名customers或agents作为过程的参数，采用游标变量根据参数绑定不同的查询语句。如果某顾客的折扣discnt低于10.00，则显示该顾客的级别为“普通”，否则显示为“vip”；如果某代理商的佣金百分比低于6，则显示该代理商的级别为“普通”，否则显示为“vip”。调用过程时给定的参数错误时，显示用户自定义的错误信息：“input must be customers or agents”。解：set serveroutput on;create or replace procedure proc_2(p_table in varchar2)as-定义游标变量类型并创建该类游标type t_cursor is ref cursor;p_cursor t_cursor;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_discnt10.00 then dbms_output.put_line(顾客|p_cid|的等级为：|普通); else dbms_output.put_line(顾客| p_cid|的等级为：|vip); end if; else fetch p_cursor into p_