Oracle数据库图书管理系统的课程设计

Oracle数据库图书管理系统的课程设计

在Oracle数据库最初的sys用户XE数据库下执行；

--删除表空间

drop tablespace tablespace_book including contents
cascade constraints;


--创建永久表空间
create tablespace tablespace_book
datafile 'f:\oracle\book' size 50m
autoextend on next 50m maxsize 100m;    --自动增长表空间从50M到100M；


--创建用户
create user book identified by book
default tablespace tablespace_book;


--用户授权

grant all privileges to book;

 

在创建的book用户下执行，我连接的数据库名与用户名相同；

--创建管理员角色
create role manager identified by manager;


--创建读者角色
create role reader identified by reader;


--创建读者表
create table reader(
rno number(10) not null primary key,
rname varchar2(20),
rid number(18) unique)tablespace tablespace_book
storage(initial 256k);


--创建图书表
create table book(
bno number(10) not null primary key,
bname varchar2(20) not null,
bprice varchar(4),
author varchar2(30) not null,
bnum int not null)tablespace tablespace_book
storage(initial 256k);


--创建借阅表
create table rb(
readno number(10) not null primary key,
rno number(10) not null,
rname varchar2(20),
bno number(18) not null,
bname varchar2(20),
regdate date default sysdate,
back char(2))tablespace tablespace_book
storage(initial 256k);


--创建管理员表
create table manager(
mno number(10) not null primary key,
mname varchar2(20)not null,
regdate date default sysdate
)tablespace tablespace_book
storage(initial 256k);


--为借阅表创建外键约束
alter table rb add constraint fk_rb foreign key
(rno) references
reader(rno);


--向读者表中插入数据
insert into reader(rno,rname,rid)
values(101,'叶凡一',123456789);
insert into reader(rno,rname,rid)
values(102,'叶凡二',223456789);
insert into reader(rno,rname,rid)
values(103,'叶凡三',323456789);
insert into reader(rno,rname,rid)
values(104,'叶凡四',423456789);
insert into reader(rno,rname,rid)
values(105,'叶凡五',523456789);
insert into reader(rno,rname,rid)
values(106,'叶凡六',623456789);
insert into reader(rno,rname,rid)
values(107,'叶凡七',723456789);
insert into reader(rno,rname,rid)
values(108,'叶凡八',823456789);
insert into reader(rno,rname,rid)
values(109,'叶凡九',923456789);
insert into reader(rno,rname,rid)
values(110,'叶凡十',023456789);


--向图书表中插入10条数据
insert into book(bno,bname,bprice,author,bnum)
values(1010,'遮天1',50,'辰东',1);
insert into book(bno,bname,bprice,author,bnum)
values(1011,'遮天2',50,'辰东',3);
insert into book(bno,bname,bprice,author,bnum)
values(1012,'遮天3',50,'辰东',5);
insert into book(bno,bname,bprice,author,bnum)
values(1013,'遮天4',50,'辰东',7);
insert into book(bno,bname,bprice,author,bnum)
values(1014,'遮天5',50,'辰东',8);
insert into book(bno,bname,bprice,author,bnum)
values(1015,'遮天6',50,'辰东',5);
insert into book(bno,bname,bprice,author,bnum)
values(1016,'遮天7',50,'辰东',4);
insert into book(bno,bname,bprice,author,bnum)
values(1017,'遮天8',50,'辰东',2);
insert into book(bno,bname,bprice,author,bnum)
values(1018,'遮天9',50,'辰东',10);
insert into book(bno,bname,bprice,author,bnum)
values(1019,'遮天10',50,'晨曦',10);


--向借阅表中插入数据
insert into rb(readno,rno,rname,bno,bname,BACK)
values(1,103,'叶凡三',1010,'遮天1','N');
insert into rb(readno,rno,rname,bno,bname,BACK)
values(2,103,'叶凡三',1011,'遮天2','N');
insert into rb(readno,rno,rname,bno,bname,BACK)
values(3,103,'叶凡三',1012,'遮天3','N');
insert into rb(readno,rno,rname,bno,bname,BACK)
values(4,104,'叶凡四',1013,'遮天4','N');
insert into rb(readno,rno,rname,bno,bname,BACK)
values(5,105,'叶凡五',1014,'遮天5','N');
insert into rb(readno,rno,rname,bno,bname,BACK)
values(6,105,'叶凡五',1015,'遮天6','N');
insert into rb(readno,rno,rname,bno,bname,BACK)
values(7,107,'叶凡七',1016,'遮天7','N');
insert into rb(readno,rno,rname,bno,bname,BACK)
values(8,109,'叶凡九',1017,'遮天8','N');
insert into rb(readno,rno,rname,bno,bname,BACK)
values(9,101,'叶凡一',1018,'遮天9','N');
insert into rb(readno,rno,rname,bno,bname,BACK)
values(10,101,'叶凡一',1019,'遮天10','N');


--向管理员表中插入数据
insert into manager(mno,mname) values(1,'图图');
insert into manager(mno,mname) values(2,'花花');


--创建主键索引
create unique index manager_index on manager (mno desc) 
tablespace tablespace_book 
storage 
( 
  buffer_pool default 
);


create unique index book_index on book (bno desc) 
tablespace tablespace_book 
storage 
( 
  buffer_pool default 
);


create unique index read_index on rb (readno desc) 
tablespace tablespace_book 
storage 
( 
  buffer_pool default 
);


create unique index reader_index on reader (rno desc) 
tablespace tablespace_book 
storage 
( 
  buffer_pool default 
);


--创建视图
create or replace view book_view_union as
select bno,bname,bprice,bnum from book;


create or replace view reader_view_union as
select rno,rname,rid from reader;


create or replace view rb_view_union as
select readno,rno,rname,bno,bname,regdate,back from rb;


--创建存储过程实现表中数据的修改
--还书数据的修改管理
CREATE OR REPLACE PROCEDURE RBack(B_ID VARCHAR2)
IS
BEGIN
UPDATE RB SET BACK = 'Y' WHERE readno = B_ID;
COMMIT;
END;
--执行
BEGIN
rback('1');
END;


--创建函数统计各种图书的库存量
CREATE OR REPLACE FUNCTION GETSCOUNT(au IN VARCHAR2) RETURN NUMBER
  AS
  BOOKNUM NUMBER;
  BEGIN
  SELECT bnum INTO BOOKNUM FROM BOOK WHERE author = au;
  RETURN(BOOKNUM);
  END;
  
--调用函数
DECLARE
COUNTNUM NUMBER:=0;
BEGIN
COUNTNUM:=GETSCOUNT('晨曦');
SYS.DBMS_OUTPUT.PUT('图书馆有“晨曦”所写书籍总数为: ');
SYS.DBMS_OUTPUT.PUT(COUNTNUM);
SYS.DBMS_OUTPUT.PUT_LINE('本');
END;


--触发器
CREATE OR REPLACE TRIGGER tri_rb
AFTER INSERT OR UPDATE ON RB
FOR EACH ROW
DECLARE
BEGIN
SYS.DBMS_OUTPUT.PUT_LINE('借阅者：'||:NEW.RNAME);
SYS.DBMS_OUTPUT.PUT_LINE('书籍编号：'||:NEW.BNO);
SYS.DBMS_OUTPUT.PUT_LINE('书籍名称：'||:NEW.BNAME);
SYS.DBMS_OUTPUT.PUT_LINE('借出日期：'||:NEW.REGDATE);
SYS.DBMS_OUTPUT.PUT_LINE('是否归还：'||:NEW.BACK);
SYS.DBMS_OUTPUT.PUT_LINE('触发器已被执行');
END;


--普通读者用户只能进行查询操作授权
grant select on book to reader;


--授权管理员角色可进行各种操作
grant connect,resource to manager;
grant create any sequence to manager;  
grant create any table to manager;  
grant delete any table to manager;  
grant insert any table to manager;  
grant select any table to manager;  
grant unlimited tablespace to manager;  
grant execute any procedure to manager;  
grant update any table to manager;  
grant create any view to manager;