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

第16基于Oracle数据库的应用开发本章内容本章要求16.1图书管理系统数据库设计与开发16.1.1图书管理系统需求分析16.1.2图书管理系统数据库对象设计表设计BOOK表结构及其约束SEQ_READERS：产生读者编号，起始值为10000。
SEQ_BOOKS：产生图书编号，起始值为100。视图设计PL/SQL功能模块设计16.1.3图书管理系统数据库对象创建创建book表
CREATETABLEbook(
bookidNUMBER(11)PRIMARYKEY,
booknameVARCHAR2(20)NOTNULL,
author1VARCHAR2(20)NOTNULL,
author2VARCHAR2(20),
author3VARCHAR2(20),
pubDATEDATE,publishVARCHAR2(30),
photoVARCHAR2(100),
abstractVARCHAR2(4000),
priceNUMBER(7,2)NOTNULL,
isbnVARCHAR2(17)NOTNULL,
bookclASsNUMBER(11)REFERENCESbook_clASs(clASsid),
booktypeNUMBER(11)REFERENCESbook_type(typeid));
计算借阅超期天数的存储过程
CREATEORREPLACEPROCEDUREp_days_FROM_due(
v_readeridNUMBER,v_bookidNUMBER,v_daysoutNUMBER)
AS
BEGIN
SELECTdue–sysdateINTOv_daysFROMborrow
WHEREreaderid=v_readeridANDbookid=v_bookid;
IFv_days<=0THEN
v_day:=0;
ENDIF;
END;
计算图书应归还日期的函数
CREATEORREPLACEFUNCTIONf_date_is_due(
v_readeridNUMBER,v_bookidNUMBER)
RETURNVARCHAR2
AS
v_booktypeNUMBER;
v_readertypeNUMBER;
v_dateVARCHAR2(10);
BEGIN
SELECTbooktypeINTOv_booktypeFROMbookWHEREbookid=v_bookid;
SELECTreadertypeINTOv_readertypeFROMreaderWHEREreaderid=v_readerid;
SELECTto_char(sysdate+days,'yyyy-mm-dd')INTOv_dateFROMrule
WHEREreadertype=v_readertypeANDbooktype=v_booktype;
RETURNv_date;
END;
计算超期罚款的存储过程
CREATEORREPLACEPROCEDUREp_timeover_money(
v_readeridNUMBER,v_bookidNUMBER,v_moneyOUTNUMBER)
AS
v_daysNUMBER;
v_readertypeNUMBER;
v_booktypeNUMBER;
BEGIN
p_days_from_due(v_readerid,v_bookid,v_days);
SELECTbooktypeINTOv_booktypeFROMbookWHEREbookid=v_bookid;
SELECTreadertypeINTOv_readertypeFROMreaderWHEREreaderid=v_readerid;
SELECTovertime*floor(abs(v_days))INTOv_moneyFROMrule
WHEREreadertype=v_readertypeANDbooktype=v_booktype;
END;
判断读者可否进行借阅的存储过程
CREATEORREPLACEPROCEDUREp_can_borrow(
v_readeridNUMBER,v_bookidNUMBER,v_numOUTNUMBER)
AS
v_rightNUMBER;
v_borrowed_numNUMBER;
v_rule_numNUMBER;
BEGIN
SELECTrightINTOv_rightFROMreaderWHEREreaderid=v_readerid;
v_borrowed_num:=f_borrowed_num(v_readerid,v_bookid);
v_rule_num:=f_rule_num(v_readerid,v_bookid);
IFv_right=1THEN