Oracle第16章基于Oracle数据库的应用开发.pptx

第16 基于Oracle数据库的应用开发;本章内容;本章要求;16.1图书管理系统数据库设计与开发;16.1.1 图书管理系统需求分析;16.1.2 图书管理系统数据库对象设计;表设计;;BOOK表结构及其约束;;;;;;;SEQ_READERS：产生读者编号，起始值为10 000。 SEQ_BOOKS：产生图书编号，起始值为100。;视图设计;PL/SQL功能模块设计;16.1.3 图书管理系统数据库对象创建;;;创建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,publish 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)); ;;;;;;;计算借阅超期天数的存储过程 CREATE OR REPLACE PROCEDURE p_days_FROM_due( v_readerid NUMBER,v_bookid NUMBER,v_days out NUMBER) AS BEGIN SELECT due –sysdate 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 VARCHAR2 AS v_booktype NUMBER; v_readertype NUMBER; v_date VARCHAR2(10); BEGIN SELECT booktype INTO v_booktype 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_money( v_readerid NUMBER,v_bookid NUMBER ,v_money OUT NUMBER) AS v_days NUMBER; v_readertype NUMBER; v_booktype NUMBER; BEGIN p_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; 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) AS v_right NUMBER; v_borrowed_num NUMBER; v_rule_num NUMBER; BEGI