【Oracle 实验实验4 Oracle数据库模式对象管理】

文档中心

1 实验目的

掌握表的创建与管理。
掌握索引的创建与管理。
掌握视图的创建与管理。
掌握序列的创建与应用。

2 实验要求

为图书销售系统创建表。
在图书销售系统适当表的适当列上创建适当类型的索引。
为图书销售系统创建视图。
为图书销售系统创建序列。

3 实验步骤

(1) 打开SQL Plus或SQL Developer，以sys用户登录BOOKSALES数据库。

(2) 按下列方式创建一个用户bs，并给该用户授权。

CREATE USER C##bs026 IDENTIFIED BY bs DEFAULT TABLESPACE USERS;(12c版 本之后需要在用户名之前加c##前缀)

GRANT RESOURCE,CONNECT,CREATE VIEW TO c##bs026;

(3) 使用bs用户登录数据库，并进行下面的相关操作。

(4) 根据图书销售系统关系模式设计，在表空间BOOKTBS1中创建表A-1至表A-6（课本P315-316）。查询数据字典视图检查表的基本属性。

Create table CUSTOMERS(

customer_id number(4) primary key,

name char(20) not null,

phone varcher2(50) not null,

email varcher2(50)

address varcher2(200),

code varcher2(10)

)

Tablespace BOOKTBS1;

Create table PUBLISHERS(

publisher_id number(2) primary key,

name varcher2(50),

contact char(10),

phone varcher2(50)

)

Tablespace BOOKTBS1;

Create table BOOKS(

ISBN varcher2(50)primary key,

title varcher2(50) ,

author varcher2(50),

pubdate Date,

publisher_id number(2) references PUBLISHERS(publisher_id),

cost number(6,2),

retril number(6,2),

category varcher2(50)

)

Tablespace BOOKTBS1;

Create table ORDERS(

order_id number(4) primary key,

customer_id number(4) references CUSTOMERS(customer_id)

orderdate date not null,

shipdate date,

shipaddress vachar2(200),

shipcode varchar2(10)

)

Tablespace BOOKTBS1;

Create table ORDERITEM(

order_id number(4) references ORDERS(order_id) primary key,

item_id number(4) primary key,

ISBN vachar2(50) not null,

quantity number(4)

)

Tablespace BOOKTBS1;

Create table PROMOTION(

gift_id number(2) ,

name char(20) primary key,

minretail number(5,2),

maxretail number(5,2)

)

Tablespace BOOKTBS1;

(5) BOOKS表按出版日期进行分区，1990-01-01日期前的放在BOOKTBS1表空间，日期再1990-01-01到2000-01-01之间的放到BOOKTBS2表空间，2000-01-01之后的放到BOOKTBS3表空间。（若表空间不存在，请自行创建）。

CREATE TABLESPACE BOOKTBS1 DATAFILE

'E:\ORACLE1\oradata\BOOKSALES026\DATAFILE\BOOKTBS1026_1.DBF' SIZE 50M;

CREATE TABLESPACE BOOKTBS2 DATAFILE

'E:\ORACLE1\oradata\BOOKSALES026\DATAFILE\BOOKTBS2026_1.DBF' SIZE 50M;

CREATE TABLESPACE BOOKTBS3 DATAFILE

'E:\ORACLE1\oradata\BOOKSALES026\DATAFILE\BOOKTBS3026_1.DBF' SIZE 50M;

分区：

Create table BOOKS(

ISBN varcher2(50)primary key,

title varcher2(50) ,

author varcher2(50),

pubdate Date,

publisher_id number(2) references PUBLISHERS(publisher_id),

cost number(6,2),

retril number(6,2),

category varcher2(50)

)

Partition by range(pubdate)

(

Partition p1 value less than(To_Date(‘1990-01-01’,‘YYYY-MM-DD’))

Tablespace BOOKTBS1,

Partition p2 value less than(To_Date(‘2000-01-01’,‘YYYY-MM-DD’))

Tablespace BOOKTBS2,

Partition p1 value less than(maxvalue)

Tablespace BOOKTBS3,

)

Tablespace BOOKTBS1;

(6)在表BOOKS插入几行测试数据，并设置表空间的可用性，检验对分区表BOOKS进行测试。

Insert into BOOKS(ISBN,title,author,pubdate) values('1001','三国演义','罗贯中 ',to_date(TO_CHAR('1989-01-01'),'YYYY-MM-DD'));

Insert into BOOKS(ISBN,title,author,pubdate) values('1002','水浒传','施耐庵 ',to_date(TO_CHAR('1992-01-01'),'YYYY-MM-DD'));

Insert into BOOKS(ISBN,title,author,pubdate) values('1003','红楼梦','曹雪芹 ',to_date(TO_CHAR('2001-01-01'),'YYYY-MM-DD'));

授予DBA权限后：

alter tablespace BOOKTBS1 offline;

把 BOOKTBS1 offline之后查询不到

alter tablespace BOOKTBS1 online;

打开之后

(7) 在CUSTOMERS表的name列上创建一个B-树索引，要求索引值为大写字母。

create index CUSTOMERS_name_index on CUSTOMERS(upper(name)) tablespace users;

(8) 在BOOKS表的title列上创建一个非唯一索引，按照图书类型建分区位图索引。

create index BOOKS_title_index on books(title) tablespace users;

(9) 在ORDERSITEM表的ISBN列上创建一个唯一索引。

create unique index ORDERITEM_isbn_index on orderitem(isbn) tablespace users;

(10) 创建一个视图customers_book，描述客户与订单的详细信息，包括客户编号、客户名单、订购图书的ISBN、图书名称、图书数量、订货日期、发货日期等。

drop view customers_book;

create view customers_book(customer_id, name,isbn,title, quantity, orderdate, shipdate)

select customers.customer_id, customers.name, orderitem.isbn, books.title, orderitem.quantity, orders.orderdate, orders.shipdate

from customers, orderitem, books, orders

where customers.customer_id=orders.customer_id and orders.order_id=orderitem.order_id and orderitem.ISBN=books.ISBN;

(11) 创建一个视图customers_gift，描述客户获得礼品的信息，包括客户名称、图书总价、礼品名称。

create view customers_gift(customer_name,book_price,promotion_name)

select c.name,(case when oi.quantity<=10 then oi.quantity*b.retail else oi.quantity*b.cost end),p.name

from customers c,orderitem oi,books b,promotion p,orders o

where c.customer_id=o.customer_id and o.order_id=oi.order_id and b.ISBN=oi.ISBN and

case when oi.quantity<=10 then oi.quantity*b.retail else oi.quantity*b.cost end between minretail and maxretail;

(12) 定义序列seq_customers，产生客户编号，序列起始值为1，步长为1，不缓存，不循环。并插入数据进行测试。

create sequence seq_customers start with 1 increment by 1 nocache nocycle;

Insert into customers

values(seq_customers.nextval,'梁恩硕 ','1718962386','1620172874@qq.com','山东建筑大学','les');

Insert into customers values(seq_customers.nextval,'les1','1718962386','1620172874@qq.com','山东建筑大学 ','les');

(13) 定义序列seq_orders，产生订单编号，序列起始值为1000，步长为1，不缓存，不循环。并插入数据进行测试。

create sequence seq_orders start with 1000 increment by 1 nocache nocycle;

Insert into orders values(seq_orders.nextval,1,to_date(TO_CHAR('2022-01-01'),'YYYY-MM-DD'),to_date(TO _CHAR('2022-01-12'),'YYYY-MM-DD'),'山东建筑大学','253300');

Insert into orders values(seq_orders.nextval,2,to_date(TO_CHAR('2022-01-01'),'YYYY-MM-DD'),to_date(TO _CHAR('2022-01-12'),'YYYY-MM-DD'),'港沟街道凤鸣路1000号','253300');

(14) 定义序列seq_promotion，产生礼品编号，序列起始值为1，步长为1，不缓存，不循环。并插入数据进行测试。

create sequence seq_promotion start with 1 increment by 1 nocache nocycle;

Insert into promotion values(seq_promotion.nextval,'水杯',100.25,300.50);

Insert into promotion values(seq_promotion.nextval,'玩偶',300.25,500.50);

(15) 完成课本P107 “2 实训题”中的（1）-（10）。

1.创建HUMAN_RESOURCE数据库

2.创建human用户

CREATE USER C##human026 IDENTIFIED BY bs DEFAULT TABLESPACE USERS;

GRANT RESOURCE,CONNECT,CREATE VIEW TO c##human026;

Create table exer_class(

CNO number(2) primary key,

CNAME VARCHAR2(20) ,

NUM number(2)

)

Tablespace HRTBS1;

Create table exer_student(

SNO number(4) primary key,

SNAME VARCHAR2(10) ,

SAGE number,

SEX CHAR(2),

CNO number(2)references exer_class(CNO)

)

Tablespace HRTBS1;

3.添加检查约束

alter table exer_student add constraint P_CK CHECK(sage between 0 and 100);

4.sex检查约束

alter table exer_student add constraint P_SEX CHECK(SEX='F' or SEX='M')modify SEX default 'M';

5.CNAME唯一性索引

create unique index ind_cname on exer_class(cname);

6.创建视图包含学生及其班级

create view student_info(sno,sname,sex,cno,cname)

select SNO,SNAME,SEX,exer_class.CNO,CNAME

from exer_student,exer_class

where exer_student.cno=exer_class.cno;

7.创建序列作为学号

create sequence seq_sno start with 100000001 increment by 1 nocache nocycle;

create table exer_student_range(sno number(4)primary key,sname varchar2(10),sage number,sex char(2),cno number(2))

partition by range(sage)(partition part1 values less than(20) tablespace HRTBS1,partition part2 values less than(30) tablespace HRTBS2,partition part3 values less than(maxvalue) tablespace HRTBS3)

9.创建exer_student_list表（按性别）

create table student_list(sno number(4) primary key,sname varchar2(10),sage number,sex char(2),cno number(2))

partition by list(sex)(

partition man values('M') tablespace HRTBS1,

partition woman values('F') tablespace HRTBS2)

10.创建本地分区索引exer_student_range

create index exer_student_range_local on exer_student_range(sname) local;

4. 实验总结

遇到的问题：创建用户创建不了

解决的问题：在用户名前面加上C##或c##成功创建

实验总结：通过这次实验，学会了创建用户并授权，创建表格，创建视图，创建索引，创建序列等操作。更加熟练了这些操作！

注:本文章仅用于参考学习，如有错误，请大家指正。

【Oracle 实验实验4 Oracle数据库模式对象管理】

公告

标签

【Oracle 实验 实验4 Oracle数据库模式对象管理】

相关问题

公告

标签

【Oracle 实验实验4 Oracle数据库模式对象管理】