Oracle图书管理系统.docx

1、Oracle图书管理系统图书管理系统参与人员：组长：冀佳伟 12111100220组员：梁恒宇12111100219赵一兆12111100217刘鼎 12111100218胡明池12111100232项目分工：冀佳伟负责总体设计与表的创建梁恒宇负责绘图赵一兆负责视图创建刘鼎负责触发器创建胡明池负责序列创建总体设计表设计BOOK_CLASS表结构及其约束字段名数据类型长度约束说明classidNUMBER11主码学科类型编号classnameVARCHAR220NOT NULL学科类型名称demoVARCHAR2100说明BOOK_TYPE表结构及其约束字段名数据类型长度约束说明typeidNU

2、MBER11主码图书类型编号typenameVARCHAR220NOT NULL图书类型名称demoVARCHAR2100说明BOOK表结构及其约束字段名数据类型长度约束说明bookidNUMBER11主码书籍编号booknameVARCHAR220NOT NULL书籍名称author1VARCHAR220NOT NULL书籍作者author2VARCHAR220书籍作者author3VARCHAR220书籍作者pubdateDATE出版日期publishVARCHAR230出版社photoVARCHAR2100图片地址abstractVARCHAR24000内容简介priceNUMBER7,

3、2NOT NULL价格ISBNVARCHAR217NOT NULL书籍ISBN码bookclassNUMBER11外码学科类型booktypeNUMBER11外码藏书类型READER_TYPE表结构及其约束字段名数据类型长度约束说明typeidNUMBER11主码图书类型编号typenameVARCHAR220NOT NULL图书类型名称demoVARCHAR2100说明READER表结构及其约束字段名数据类型长度约束说明readeridNUMBER11主码读者编号nameVARCHAR210NOT NULL读者姓名telephoneVARCHAR215联系电话emailVARCHAR230邮

4、箱地址deptVARCHAR220所在院系rightNUMBER1取值为0或1借阅权限readertypeNUMBER11外码读者类型demoVARCHAR21000说明BORROW表结构及其约束字段名数据类型长度约束说明readeridNUMBER11外码联合主码读者编号bookidNUMBER11外码图书编号borrowdateDATE出借日期dueDATE应还日期last_dueDATE实际归还日期RULE表结构及其约束字段名数据类型长度约束说明booktypeNUMBER11外码联合主码藏书类型编号readertypeNUMBER11外码读者类型编号daysNUMBER5NOT NUL

5、L期限（天）numNUMBER5NOT NULL册数（本）renewNUMBER5NOT NULL续借次数（次）overtimeNUMBER5,2NOT NULL逾期处罚（元/册/天）ADMIN表结构及其约束字段名数据类型长度约束说明IdNUMBER11主码管理员编号usernameVARCHAR210NOT NULL管理员账号passwordVARCHAR211NOT NULL账号密码PRECONCERT（预约表）表结构及其约束字段名数据类型长度约束说明readeridNUMBER11主码联合主码读者编号bookidNUMBER11主码图书编号predateDATE预约日期创建admin表C

6、REATE TABLE admin(id NUMBER(11) PRIMARY KEY, username VARCHAR2(10) NOT NULL,pASsword VARCHAR2(11) NOT NULL);创建reader_type表CREATE TABLE reader_type(typeid NUMBER(11) PRIMARY KEY, typename VARCHAR2(20) NOT NULL,demo VARCHAR2(100);创建reader表CREATE TABLE reader(readerid NUMBER(11) PRIMARY KEY, name VARCH

7、AR2(10) NOT NULL,telephone VARCHAR2(15),email VARCHAR2(30),dept VARCHAR2(20),right NUMBER(1) CHECK(right=0 or right=1),readertype NUMBER(11) REFERENCES reader_type(typeid),demo VARCHAR2(1000);创建book_type表CREATE TABLE book_type(typeid NUMBER(11) PRIMARY KEY,typename VARCHAR2(20) NOT NULL,demo VARCHAR

8、2(100);创建book_class表CREATE TABLE book_class(clASsid NUMBER(11) PRIMARY KEY,clASsname VARCHAR2(20) NOT NULL,demo VARCHAR2(100);创建book表CREATE TABLE book(bookid NUMBER(11) PRIMARY KEY,bookname VARCHAR2(20) NOT NULL,author1 VARCHAR2(20) NOT NULL,author2 VARCHAR2(20),author3 VARCHAR2(20),pubDATE DATE,pub

9、lish VARCHAR2(30),photo VARCHAR2(100),abstract VARCHAR2(4000),price NUMBER(7,2) NOT NULL,isbn VARCHAR2(17) NOT NULL,bookclASs NUMBER(11) REFERENCES book_clASs(clASsid),booktype NUMBER(11) REFERENCES book_type(typeid);创建borrow表CREATE TABLE borrow(readerid NUMBER(11) REFERENCES reader(readerid),bookid

10、 NUMBER(11) REFERENCES book(bookid),borrowdate DATE,due DATE,last_due DATE,PRIMARY KEY(readerid,bookid);创建preconcert表CREATE TABLE preconcert(readerid NUMBER(11) REFERENCES reader(readerid),bookid NUMBER(11) REFERENCES book(bookid),predate DATE,PRIMARY KEY(readerid,bookid);创建rule表CREATE TABLE rule(bo

11、oktype NUMBER(11) REFERENCES book_type(typeid),readertype NUMBER(11) REFERENCES reader_type(typeid),days NUMBER(5) NOT NULL,num NUMBER(5) NOT NULL,renew NUMBER(5) NOT NULL,overtime NUMBER(5,2) NOT NULL,PRIMARY KEY(booktype,readertype);序列的创建CREATE SEQUENCE seq_reader START WITH 1 INCREMENT BY 10000;C

12、REATE SEQUENCE seq_book START WITH 1 INCREMENT BY 100;创建视图reader_book_viewCREATE OR REPLACE VIEW reader_book_viewASSELECT name,bookname,borrowdate,due,last_due FROM reader,book,borrow WHWER reader.readid=borrow.readid AND borrow.bookid=book.bookid;创建视图book_type_stat_viewCREATE OR REPLACE VIEW book_t

13、ype_stat_viewASSELECT booktype,COUNT(booktype) FROM (SELECT readerid,borrow.booktype,Booktype,borrowdate,due,last_due FROM borrow,book WHERE book.bookid=borrow.bookid)计算借阅超期天数的存储过程CREATE OR REPLACE PROCEDURE p_days_FROM_due(v_readerid NUMBER,v_bookid NUMBER,v_days out NUMBER)ASBEGINSELECT due sysdat

14、e INTO v_days FROM borrow WHERE readerid=v_readerid AND bookid=v_bookid;IF v_days=0 THEN v_day:=0;END IF;END;计算图书应归还日期的函数CREATE OR REPLACE FUNCTION f_date_is_due(v_readerid NUMBER,v_bookid NUMBER)RETURN VARCHAR2ASv_booktype NUMBER;v_readertype NUMBER;v_date VARCHAR2(10);BEGINSELECT booktype INTO v_b

15、ooktype FROM book WHERE bookid=v_bookid;SELECT readertype INTO v_readertype FROM reader WHERE readerid=v_readerid;SELECT to_char(sysdate+days, yyyy-mm-dd) INTO v_date FROM rule WHERE readertype=v_readertype AND booktype=v_booktype;RETURN v_date;END;计算超期罚款的存储过程CREATE OR REPLACE PROCEDURE p_timeover_m

16、oney(v_readerid NUMBER,v_bookid NUMBER ,v_money OUT NUMBER)ASv_days NUMBER;v_readertype NUMBER;v_booktype NUMBER;BEGINp_days_from_due(v_readerid,v_bookid,v_days);SELECT booktype INTO v_booktype FROM book WHERE bookid=v_bookid;SELECT readertype INTO v_readertype FROM reader WHERE readerid=v_readerid;

17、SELECT overtime*floor(abs(v_days) INTO v_money FROM rule WHERE readertype=v_readertype AND booktype=v_booktype;END;判断读者可否进行借阅的存储过程CREATE OR REPLACE PROCEDURE p_can_borrow(v_readerid NUMBER,v_bookid NUMBER,v_num OUT NUMBER)ASv_right NUMBER;v_borrowed_num NUMBER;v_rule_num NUMBER;BEGINSELECT right INT

18、O v_right FROM reader WHERE readerid=v_readerid;v_borrowed_num:=f_borrowed_num(v_readerid,v_bookid);v_rule_num:=f_rule_num(v_readerid,v_bookid);IF v_right=1 THEN v_num:=0;ELSEv_num:=v_rule_num-v_borrowed_num;END IF;EXCEPTION WHEN OTHERS THEN v_num:=0;END;为BOOK表创建一个触发器，当插入新书籍时显示新书籍的书籍编号、出版社和价格；当更新书籍价

19、格时，显示修改前后书籍价格；当删除书籍时，显示被删除的书籍编号、出版社和价格。 CREATE OR REPLACE TRIGGER trg_dml_rowBEFORE INSERT OR UPDATE OR DELETE ON BOOKFOR EACH ROWBEGIN IF INSERTING THEN DBMS_OUTPUT.PUT_LINE(:new.bookid| | :new.publish | |:new.price); ELSIF UPDATING THEN DBMS_OUTPUT.PUT_LINE(:old.price| |:new.price); ELSE DBMS_OUTP

20、UT.PUT_LINE(:old.bookid|:old.publish|:old.price);END IF;END trg_dml_row;为BOOK表创建一个触发器，当修改书籍价格时，保证修改后的价格高于修改前的价格。CREATE OR REPLACE TRIGGER trg_update_rowBEFORE UPDATE OF price ON BOOKFOR EACH ROWWHEN(new.price=old.price)BEGIN RAISE_APPLICATION_ERROR(-20001,The price is lower! );END trg_update_row;包体创

21、建CREATE OR REPLACE PACKAGE BODY pkg_enoASPROCEDURE update_sal(p_no NUMBER, p_sal NUMBER)ASBEGINSELECT min(price), max(price) INTO minsal,maxsal FROM BOOK;IF p_sal BETWEEN minsal AND maxsal THENUPDATE BOOK SET price=p_sal WHERE bookid=p_no;IF SQL%NOTFOUND THENRAISE_APPLICATION_ERROR(-20000,The BOOK d

22、oesnt exist);END IF;ELSERAISE e_beyondbound;END IF;EXCEPTIONWHEN e_beyondbound THENDBMS_OUTPUT.PUT_LINE(The price is beyond bound! );END update_sal; PROCEDURE add_book(p_no NUMBER,p_sal NUMBER)ASBEGINSELECT min(price), max(price) INTO minsal,maxsal FROM BOOK;IF p_sal BETWEEN minsal AND maxsal THENINSERT INTO BOOK (bookid, bookname,author1, pubdate, publish,price)VALUES(p_no,JAVA教程,king,2009-10-03, 电子出版社,p_sal);ELSERAISE e_beyondbound;END IF;EXCEPTIONWHEN e_beyondbound THENDBMS_OUTPUT.PUT_LINE(The price is beyond bound! );END add_book; END pkg_eno;