大型数据库技术实验报告

大型数据库技术

实验报告

专业班级：

学号：

姓名：

时间：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中导入数据库)。

解：

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中添加如下数据：

解：

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 dollars>400 ;

6．将二季度所有的订单记录复制到表Orders_2中。

解：

create table Orders_2

AS

select * from orders partition(part_summer);

select * from orders_2

7．为Orders表创建公有同义词，并通过该同义词访问该表。

解：create public synonym ord for orders;

select * from ord;

8．从数据字典中查询当前用户创建的所有的同义词。

解：select * from dba_synonyms where table_owner='xiao'

9．基于表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是否有变化。

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）查询表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'

--需要先分析索引的结构

ANAL YZE 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唯一标识该记录。

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 ='WUJIYANG'

3．(1) 在表orders上定义一个触发器，当向表中加入一条新的订单记录时，自动使用序列orderno_sequence生成一个订单编号，并自动计算商品总价dollars。计算公式如下：

商品总价dollars= 商品数量qty* 商品单价price * (1-顾客折扣discnt/100)

(2) 首先将表orders中的数据全部删除，然后向表中添加如下数据验证触发器的正确性。

解：create or replace trigger calu_orders

before insert on orders

for each row

DECLARE

t_price products.price%type

t_discnt customers.discnt%type

BEGIN

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)

V ALUES ('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 V ARCHAR2)

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_discnt<10.00 then

dbms_output.put_line('顾客'||p_cid||'的等级为：'||'普通');

else

dbms_output.put_line('顾客'|| p_cid||'的等级为：'||'VIP');

end if;

else

fetch p_cursor into p_aid, p_percent;

exit when p_cursor%notfound;

if p_percent<6 then

dbms_output.put_line('代理商'||p_aid||'的等级为：'||'普通');

else

dbms_output.put_line('代理商'||p_aid||'的等级为：'||'VIP');

end if;

end if;

end loop;

close p_cursor;

commit;

end proc_2;

drop procedure proc_2;

begin

proc_2('agents');

end;

9．表discnt_audit用来记录对表Customers的列discnt的修改历史。在表Customers上创建触发器，记录在discnt列上所做的修改，将修改的人、修改的时间、顾客的ID号、修改之前的值和修改之后的值写入表discnt_audit中。如果是向Customers表中增加一条新记录，则修改之前的值为空值。

表discnt_audit的结构：

create table discnt_audit(change_by varchar2(8) not null,change_time DATE not null,cid char(4),

old_discnt number(4,2),new_discnt number(4,2));

解：create table discnt_audit(change_by varchar2(8) not null,change_time DA TE not null,cid char(4),

old_discnt number(4,2),new_discnt number(4,2));

10．从代理商那里接受订单时，要查询products表中的quantity列，quantity代表了在仓库中被订购商品的库存数量。写一个函数qty_check，以订单中的