中南大学大型数据库报告.doc

中南大学大型数据库报告学生姓名 学 院 信息科学与工程学院 专业班级 计科120x 大型数据库技术实验一1创建一个本地位图管理表空间CAP_ts，表空间对应一个数据文件CAP_ts.dbf，该数据文件初始大小为20M，可以自动扩展。CREATE TABLESPACE CAP_ts datafile CAP_ts.dbf size 20M reuse extent management local autoallocate; ALTER DATABASE DEFAULT TABLESPACE CAP_ts;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 York6A05OtasiDuluth5A06SmithDallas5CREATE TABLE Customers(cid VARCHAR(5) PRIMARY KEY NOT NULL,cnaem VARCHAR(10),city VARCHAR(10),discount REAL);CREATE TABLE Products(pid VARCHAR(5) PRIMARY KEY NOT NULL,pnaem VARCHAR(10),ity VARCHAR(10),quantity NUMBER(10),price REAL);CREATE TABLE Agents(aid VARCHAR(5) PRIMARY KEY NOT NULL,anaem VARCHAR(10),city VARCHAR(10),percent NUMBER(3);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);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,folde,Dallas,123100,2.00);insert into Products values(P07,case,Newark,100500,1.00);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, initial_extent,next_extent FROM user_tables4在表空间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( orderno number(4) primary key not null, month varchar(3), cid varchar(5), aid varchar(5), pid varchar(5), qty number(4), dollars real, constraint cid_fk foreign key (cid) references Customers(cid), constraint aid_fk foreign key (aid) references Agents(aid), constraint pid_fk foreign key (pid) references Products(pid) ) partition by list(month)( PARTITION season1 VALUES (Jan, Feb, Mar), PARTITION season2 VALUES (Apr, May) ); 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);select * from orders partition(season1);select * from orders partition(season2);5在一季度分区中查询所有订单金额高于400的订单记录。select * from orders partition(season1) where dollars400 order by orderno;6将二季度所有的订单记录复制到表Orders_2中。create table orders_2 as select * from orders partition(season2);select * from orders_2;7为Orders表创建公有同义词，并通过该同义词访问该表。create synonym orde2 for orders;select * from orde2;8从数据字典中查询当前用户创建的所有的同义词。select * from user_synonyms;9基于表customers和表orders创建一个视图customer_orders，视图中的列包括每笔订单的编号、订购的产品编号、订购的数量、顾客的编号及顾客的姓名。基于视图customer_orders查询顾客c002下的所有订单。通过数据字典表user_updatable_columns，查看视图customer_orders中哪些列是可更新的列。 create view customer_orders as select o.orderno,o.pid,o.qty,o.cid,aem from orders o,customers where o.cid=customers.cid;select * from customer_orders where cid=C002;select * from user_updatable_columns where table_name=customers;10. 利用内联视图，查询每个顾客的编号、名称、所在城市，折扣以及所下订单的数量。 select c.cid,aem,c.city,c.discount,order1.qty from customers c, (select cid,qty from orders) order1 where c.cid=order1.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是否有变化。1）create materialized view log on products; create materialized view mv_product_order refresh fast on commit as select pid,pnaem,quantity from Products; select * from mv_product_order;2）insert into orders values(1027,May,C006,a05,P05,100,50);3) 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） 2）3）4)大型数据库技术实验二1执行下面的语句，了解ROWID的编码方式。SELECT rowid FROM agents; 2创建一个序列orderno_sequence，起始值为1000，步长为1。查看该序列是否被创建。 create sequence orderno_sequence start with 1000 increment by 1;3(1) 在表orders上定义一个触发器，当向表中加入一条新的订单记录时，自动使用序列orderno_sequence生成一个订单编号，并自动计算商品总价dollars。计算公式如下：商品总价dollars= 商品数量qty* 商品单价price * (1-顾客折扣discnt/100) (2) 首先将表orders中的数据全部删除，然后向表中添加如下数据验证触发器的正确性。OrdersordnomonthcidaidpidqtydollarsJanC001A01P011000JanC001A01P011000FebC001A02P02400FebC001A06P03600FebC001A03P04600MarC001A04P05500MarC001A05P06400AprC001A05P07800JanC002A03P031000MayC002A05P03800JanC003A03P051200JanC003A03P051200FebC004A06P011000JanC004A01P011000FebC005A03P07600MarC006A06P01800(1)create or replace trigger insert_orders_trigger before insert on orders for each row declare v_orderno orders.orderno%type; v_price products.price%type; v_discount customers.discount%type; begin select orderno_sequence.nextval into v_orderno from dual; select price into v_price from products where products.pid=:new.pid; select discount into v_discount from customers where customers.cid=:new.cid; :new.orderno :=v_orderno; :new.dollars :=(:new.qty*v_price*(1-v_discount/100); end;(2) truncate table orders;insert into orders(month,cid,aid,pid,qty) values (Jan,C001,a01,P01,1000); insert into orders(month,cid,aid,pid,qty) values (Jan,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,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,cid,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 (Jan,C002,a03,P03,1000); insert into orders(month,cid,aid,pid,qty) values (May,C002,a05,P03,800); insert into orders(month,cid,aid,pid,qty) values (Jan,C003,a03,P05,1200); insert into orders(month,cid,aid,pid,qty) values (Jan,C003,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 (Jan,C004,a01,P01,1000); insert into orders(month,cid,aid,pid,qty) values (Feb,C005,a03,P07,600); insert into orders(month,cid,aid,pid,qty) values (Mar,C006,a06,P01,800);4通过伪列CURRVAL，查询序列orderno_sequence的当前值。5编写一个PL/SQL块，查询编号在1000到1020之间所有订单的月份、订购的商品id号、订购的数量和商品的总价，并利用DBMS_OUTPUT.PUT_LINE显示查询的结果。要求定义一个表类型存储检索出来的数据，表中的元素是一条记录。declare type order_table_type is table of varchar(50) index by binary_integer; order_table order_table_type; cursor order_cursor is select month,pid,qty,dollars from orders ; v_month orders.month%type; v_pid orders.pid%type; v_qty orders.qty%type; v_dollars orders.dollars%type; v_loop number(3) :=1; v_data varchar(50); begin open order_cursor; fetch order_cursor into v_month,v_pid,v_qty,v_dollars; while order_cursor%found loop v_data :=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_month,v_pid,v_qty,v_dollars; v_loop :=v_loop+1; end loop; close order_cursor; end;6编写一个PL/SQL块，将编号为p08的产品的库存数量修改为200370，如果没有查找到相应的记录，则在表中插入该条记录。（要求：使用隐式游标)。 declare BEGIN UPDATE products SET quantity = 200370 WHERE pid = P08; IF SQL%NOTFOUND THEN INSERT INTO products (pid, quantity) VALUES (P08,200370); END IF;END;select * from products;7根据用户输入的city值，查询该城市中每个顾客下的订单的总额（即dollars的总数）。要求定义一个存储过程，以city值为参数。过程中定义以city为参数的游标，逐个计算该城市中每个顾客的订单金额的总额。要求以下列格式显示查询的结果：城市名称=xxxxx顾客编号=xxxx 订单总额=xxxxxx create or replace procedure order_precedure(city in customers.city%type) is cursor order_cursor (citys in customers.city%type) is select o.cid,sum(o.dollars) from orders 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; begin open 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| |订单总额=|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，则显示该代理商的级别为“普通”，否则显示为“VIP”。调用过程时给定的参数错误时，显示用户自定义的错误信息：“Input must be customers or agents”。create or replace procedure order_precedure(city in customers.city%type) is cursor order_cursor (citys in customers.city%type) is select o.cid,sum(o.dollars) from orders 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; begin open 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| |订单总额=|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 VARCHAR2) AS -定义游标变量类型t_ClassesRooms TYPE order_cursor IS REF CURSOR; -创建游标变量v_CursorVar v_CursorVar order_cursor; v_discount customers.disount%TYPE; v_percent agents.percent%TYPE; BEGIN - 根据输入的参数对游标变量绑定不同的查询语句 IF p_Table = customers THEN OPEN v_CursorVar FOR SELECT discount FROM customers; ELSIF p_table = agents THEN OPEN v_CursorVar FOR SELECT percent FROM agents; ELSE -错误输入时，抛出错误 RAISE_APPLICATION_ERROR(-20000, Input must be customers or agents); END IF;LOOP IF p_Table = customers THEN FETCH v_CursorVar INTO v_discount; EXIT WHEN v_CursorVar%NOTFOUND; if(v_discount10) then DBMS_OUTPUT.PUT_LINE(普通); else DBMS_OUTPUT.PUT_LINE(VIP); ELSE FETCH v_CursorVar INTO v_percent; EXIT WHEN v_CursorVAR%NOTFOUND; if(v_percent6) then DBMS_OUTPUT.PUT_LINE(普通); else DBMS_OUTPUT.PUT_LINE(VIP); END IF;END LOOP;CLOSE v_CursorVar;COMMIT;END ShowCustomersLevel;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 or replace procedure order_precedure(city in cu