图书管理系统

1.项目简介

1.1 背景

​ 图书管理系统以方便、快捷、费用低的优点正慢慢地进入人们的生活，将传统的图书管理方式彻底地解脱出来，提高效率，减轻工作人员以往繁忙的工作，减小出错的概率，使读者可以花更多的时间在选择书和看书上。从而使人们有更多时间来获取信息、了解信息和掌握信息。图书馆作为提供学习的场所，不仅要求便于管理，而且要求对读者和借阅者提供方便快速的查找，借阅和登记手续。一个好的图书馆不仅仅看它的藏书的种类和数量是否齐全，还要看它的管理机制是否健全。这不仅仅是人的因素，还有操作手段和途径，比如一个好的图书管理系统。 本系统根据图书馆的日常业务运作而设计，能够更好的管理借书，还书，统计图书类别等业务，系统操作流程与图书馆的业务流程相一致。界面友好、操作容易、维护简单；系统数据库稳定、安全，有图书管理业务操作知识的人皆能方便操作。 本系统实用于各种图书馆。

1.2 项目工具

• IDEA 2020.1.1
• JDK 8.0
• Tomcat 8.0
• Oracle 11g
• Navicat 12
• PL/SQL Developer

1.3 数据库码数

• 表 ：10
• 索引 ：8
• 视图 ：7
• 序列 ： 10
• 函数：15
• 功能：41
• 触发器 ：15
• 存储过程 ：13
• 数据库数据量 : 897

2. 需求分许

2.1 需求概述

图书管理系统主要是用oracle数据库进行逻辑处理，实现对图书信息的增删改查，以及出库入库的管理。

2.2 概念设计结构

​ 借阅图书

​ 归还图书

​ 图书管理系统

​ 管理员子系统

​ 普通借阅者子系统

​ 借阅信息查看，续借

​ 过期罚款功能

3. 数据库分析

3.1 数据库总体设计

​ 数据库设计是图书管理系统的重要组成部分。建立良好的数据库结构和文件组织形式，能够使系统快速、准确的获得所需信息。这里采用oracle 数据库。该数据库对进行增、删、改、查、显示都极为方便。能为系统提供良好的数据支持。根据系统的详细分析和和总体的需求分析，将为各个不同模块详细设计数据结构。

3.2 数据库表设计

Student 学生表

字段名	字段	数据类型	主键	索引	非空	外键	说明
stuNo	学生学号	NUMBER	√	unique	√		序列自增
stuName	学生姓名	VARCHAR2			√
sex	性别	VARCHAR2					触发器 限制为男 或 女
age	年龄	NUMBER					触发器 限制为 0~100岁之间
stuTel	电话号码	VARCHAR2		unique	√		触发器 限制为11位
classNo	班级编号	NUMBER			√	CLASS.CLASSNO
idCard	身份证	VARCHAR2		unique	√		触发器 限制为18位
stuPassword	学生密码	VARCHAR2			√
stuPasswordQuestion	秘钥问题	VARCHAR2			√
stupasswordAnswer	秘钥答案	VARCHAR2			√

Class 班级表

字段名	字段	数据类型	主键	索引	非空	外键	说明
classNo	班级编号	NUMBER	√	unique	√		序列自增
className	班级名	VARCHAR2		unique	√

AdminInf 管理员表

字段名	字段	数据类型	主键	索引	非空	外键	说明
admNo	管理员编号	NUMBER	√	unique	√		序列自增
admName	管理员姓名	VARCHAR2			√
admTel	管理员电话	VARCHAR2		unique	√		触发器 限制为11位
admPassword	管理员密码	VARCHAR2			√
admPasswordQuestion	秘钥问题	VARCHAR2			√
admpasswordAnswer	秘钥答案	VARCHAR2			√

Book 图书表

字段名	字段	数据类型	主键	索引	非空	外键	说明
bookNo	图书编号	NUMBER	√	unique	√		序列自增
bookName	图书名	VARCHAR2			√
ISBN	存放位置:楼_架_号	VARCHAR2		unique	√
autNo	作者编号	NUMBER			√	Author.autNo
preNo	出版社号	NUMBER			√	Press.preNo
price	价格	NUMBER			√
amount	数量	NUMBER			√
publictionTime	出版时间	VARCHAR2			√
typeNo	类型编号	NUMBER			√	BookType.typeNo

Author 作者表

字段名	字段	数据类型	主键	索引	非空	外键	说明
autNo	作者编号	NUMBER	√	unique	√		序列自增
autName	作者姓名	VARCHAR2		unique	√
country	作者国籍	VARCHAR2

BookType 图书类别表

字段名	字段	数据类型	主键	索引	非空	外键	说明
typeNo	类别编号	NUMBER	√	unique	√		序列自增
typeName	类别名	VARCHAR2		unique	√

Press 出版社表

字段名	字段	数据类型	主键	索引	非空	外键	说明
preNo	出版社编号	NUMBER	√	unique	√		序列自增
preName	出版社名	VARCHAR2		unique	√

BorrowRecord 借书记录表

字段名	字段	数据类型	主键	索引	非空	外键	说明
borrNo	借书记录编号	NUMBER	√	unique	√		序列自增
stuNo	学生学号	NUMBER			√	Student.stuNo
bookNo	图书编号	NUMBER			√	Book.bookNo
adminNo	管理员编号	NUMBER			√	AdminInf.admNo
isReturn	是否归还：0：没有 1：归还	NUMBER			√
borrowTime	借书的时间	DATE			√
borrowDay	借书的天数：默认为3天	NUMBER			√

ReturnRecord 还书记录表

字段名	字段	数据类型	主键	索引	非空	外键	说明
retNo	还书记录编号	NUMBER	√	unique	√		序列自增
stuNo	学生编号	NUMBER			√	Student.stuNo
bookNo	图书编号	NUMBER			√	Book.bookNo
adminNo	管理员编号	NUMBER			√	Admininf.admNo
returnTime	还书的时间	DATE
isOverDue	是否过期：0：没有 1：过期	NUMBER			√

Ticket 罚款记录

字段名	字段	数据类型	主键	索引	非空	外键	说明
ticNo	罚款编号	NUMBER	√	unique	√		序列自增
stuNo	学生学号	NUMBER			√	Student.stuNo
bookNo	图书编号	NUMBER			√	Book.bookNo
ticDate	罚款日期	DATE			√
returnNo	归书记录编号	NUMBER			√	ReturnRecord.retNo
borrowNo	还书记录编号	NUMBER			√	BorrowRecord.borNo
money	罚款金额	NUMBER			√

3.3 数据库表关系

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-B7SjdP2g-1609664133611)(C:\Users\CJ\AppData\Roaming\Typora\typora-user-images\1609592591817.png)]

3.4 数据库视图作用

1, 视图不仅可以简化用户对数据的理解，也可以简化他们的操作。

2， 用户只能查询和修改他们所能见到的数据。但不能授权到数据库特定行和特定的列上。

3， 视图可帮助用户屏蔽真实表结构变化带来的影响。

4 ， 简化表连接的复杂度。

3.5 数据库视图设计

student_info 学生信息视图

字段名	字段	说明
stuNo	学生学号	Student.stuNo
stuName	学生姓名	Student.stuName
sex	性别	Student.sex
age	年龄	Student.age
className	班级名	Class.className
idCard	身份证	Student.idCard
stuTel	学生电话	Student.stuTel

admin_info 管理员信息视图

字段名	字段	说明
admNo	管理员账号	AdminInf.admNo
admName	管理员姓名	Admininf.admName

book_info 图书信息视图

字段名	字段	说明
bookNo	图书编号	Book.bookNo
bookName	图书名	Book.bookName
ISBN	图书位置	Book.ISBN
preName	出版社名	Press.preName
autName	作者名	Author.autName
typeName	类型	BookType.typeName
publictionTime	出版时间	Book.publictionTime
amount	数量	Book.amount

press_info 出版社信息视图

字段名	字段	说明
preNo	出版社编号	Press.preNo
preName	出版社名	Press.preName
bookName	出版社出的书名	Book.bookName

author_info 作者信息视图

字段名	字段	说明
autNo	作者编号	Author.autNo
autName	作者姓名	Author.autName
country	作者国籍	Author.country
bookName	作者出的书	Book.bookName

borrow_info 借书记录视图

字段名	字段	说明
borrNo	借书记录编号	BorrowRecord.borrNo
stuName	学生姓名	Student.stuName
admName	管理员姓名	AdminInf.admName
bookName	图书姓名	Book.bookName
isReturn	是否归还	BorrowRecord.isReturn
borrowTime	借书时间	BorrowRecord.borrowTime
borrowDay	借书天数	BorrowRecord.borrowDay

return_info 还书记录视图

字段名	字段	说明
retNo	还书记录编号	ReturnRecord.retNo
stuName	学生姓名	Student.stuName
bookName	图书名	Book.bookName
admName	管理员名字	AdminInf.admName
isOverDue	是否过期	ReturnRecord.isOverDue
returnTime	还书时间	ReturnRecord.returnTime

3.6 数据库索引设计

• 学生
  • 身份证 唯一索引
  • 电话号码 唯一索引
• 管理员
  • 电话号码 唯一索引
• 图书
  • 图书位置 唯一索引
• 图书类型
  • 类型名 唯一索引
• 班级
  • 班级名 唯一索引
• 出版社
  • 出版社 唯一索引
• 作者
  • 作者名 唯一索引

3.7 数据库触发器设计

• 学生
  • idCard 插入和修改时，满足 长度为18位
  • age 插入和修改时，满足大于 0 且 小于 100
  • sex 插入和修改时，满足 只能为 男 或 女
  • stuTel 插入和修改时，满足 长度为11位
• 管理员
  • stuTel 插入和修改时，满足 长度为11位

3.7 数据库操作列表

• 作者
  • 查询指定作者信息
  • 查询所有的作者的姓名
  • 查询指定作者的书的数量
  • 查询指定作者的所有的图书
  • 通过作者的姓名查询作者编号
  • 向作者表中插入新的作者信息
• 图书
  • 模糊查询图书信息
  • 指定图书的数量减一
  • 指定图书的数量加一
  • 查询指定图书的数量
  • 查询所有图书的信息
  • 查询指定类型的图书
  • 计算整个图书馆的库存量
  • 查询没有图书馆中还有库存的图书
  • 查询没有图书馆中没有库存的图书
• 学生
  • 登录操作
  • 学生注销操作
  • 找回密码操作
  • 修改密码操作
  • 修改密钥操作
  • 查询指定学生的信息
  • 修改指定学生信息操作
• 出版社
  • 模糊查询出版社信息
  • 向出版社表中插入新的数据
  • 查询指定出版社出的所有的图书
  • 计算指定出版社出的所有图书的数量
  • 通过出版社名字来查询出版社编号
• 管理员
  • 注销管理员
  • 管理员注册
  • 查询指定管理员信息
  • 查找指定管理员密码
  • 修改指定管理员密钥
  • 修改指定管理员资料
  • 修改指定管理员密码
• 借还书记录
  • 查询所有的借书的记录
  • 查询所有的还书的记录
  • 查询指定学生的借书记录
  • 查询指定学生的还书记录
  • 向借书记录表中插入数据操作
  • 向还书记录表中插入数据操作

4. 主要代码

1. 创建表空间

-- 创建表空间
CREATE TABLESPACE libTablespace DATAFILE 'D:\app\CJ\oradata\orcl\libTablespace.dbf' SIZE 200M REUSE ;

2. 创建用户并授权

-- 创建用户
-- 这个项目中的所有的操作都在这一个用户下完成，不会影响到其他用户下的信息
CREATE USER library IDENTIFIED BY library DEFAULT TABLESPACE libTablespace;

-- 给用户授权
GRANT create session TO library;
GRANT create table TO library;
GRANT unlimited tablespace TO library;

3. 创建表

3.1 建表语句

-- ----------------------------
-- Table structure for ADMININF
-- ----------------------------
DROP TABLE "LIBRARY"."ADMININF";
CREATE TABLE "LIBRARY"."ADMININF" (
  "ADMNO" NUMBER(10) NOT NULL ,
  "ADMNAME" VARCHAR2(20 BYTE) NOT NULL ,
  "ADMPASSWORD" VARCHAR2(20 BYTE) NOT NULL ,
  "ADMPASSWORDQUESTION" VARCHAR2(50 BYTE) NOT NULL ,
  "ADMPASSWORDANSWER" VARCHAR2(50 BYTE) NOT NULL ,
  "ADMTEL" VARCHAR2(11 BYTE) NOT NULL 
)
TABLESPACE "USERS"
LOGGING
NOCOMPRESS
PCTFREE 10
INITRANS 1
STORAGE (
  INITIAL 65536 
  NEXT 1048576 
  MINEXTENTS 1
  MAXEXTENTS 2147483645
  BUFFER_POOL DEFAULT
)
PARALLEL 1
NOCACHE
DISABLE ROW MOVEMENT
;
COMMENT ON COLUMN "LIBRARY"."ADMININF"."ADMNO" IS '管理员账号';
COMMENT ON COLUMN "LIBRARY"."ADMININF"."ADMNAME" IS '管理员姓名';
COMMENT ON COLUMN "LIBRARY"."ADMININF"."ADMPASSWORD" IS '管理员密码';
COMMENT ON COLUMN "LIBRARY"."ADMININF"."ADMPASSWORDQUESTION" IS '密码问题';
COMMENT ON COLUMN "LIBRARY"."ADMININF"."ADMPASSWORDANSWER" IS '密码答案';
COMMENT ON COLUMN "LIBRARY"."ADMININF"."ADMTEL" IS '电话号码';

-- ----------------------------
-- Table structure for AUTHOR
-- ----------------------------
DROP TABLE "LIBRARY"."AUTHOR";
CREATE TABLE "LIBRARY"."AUTHOR" (
  "AUTNO" NUMBER(10) NOT NULL ,
  "AUTNAME" VARCHAR2(30 BYTE) NOT NULL ,
  "COUNTRY" VARCHAR2(20 BYTE) 
)
TABLESPACE "USERS"
LOGGING
NOCOMPRESS
PCTFREE 10
INITRANS 1
STORAGE (
  INITIAL 65536 
  NEXT 1048576 
  MINEXTENTS 1
  MAXEXTENTS 2147483645
  BUFFER_POOL DEFAULT
)
PARALLEL 1
NOCACHE
DISABLE ROW MOVEMENT
;
COMMENT ON COLUMN "LIBRARY"."AUTHOR"."AUTNO" IS '作者的编号';
COMMENT ON COLUMN "LIBRARY"."AUTHOR"."AUTNAME" IS '作者的姓名';
COMMENT ON COLUMN "LIBRARY"."AUTHOR"."COUNTRY" IS '作者的国籍';

-- ----------------------------
-- Table structure for BOOK
-- ----------------------------
DROP TABLE "LIBRARY"."BOOK";
CREATE TABLE "LIBRARY"."BOOK" (
  "BOOKNO" NUMBER(10) NOT NULL ,
  "AUTNO" NUMBER(10) ,
  "PRENO" NUMBER(10) ,
  "PRICE" NUMBER(5) ,
  "AMOUNT" NUMBER(5) ,
  "PUBLICTIONTIME" VARCHAR2(7 BYTE) ,
  "TYPENO" NUMBER(10) ,
  "BOOKNAME" VARCHAR2(30 BYTE) ,
  "ISBN" VARCHAR2(20 BYTE) 
)
TABLESPACE "USERS"
LOGGING
NOCOMPRESS
PCTFREE 10
INITRANS 1
STORAGE (
  INITIAL 65536 
  NEXT 1048576 
  MINEXTENTS 1
  MAXEXTENTS 2147483645
  BUFFER_POOL DEFAULT
)
PARALLEL 1
NOCACHE
DISABLE ROW MOVEMENT
;
COMMENT ON COLUMN "LIBRARY"."BOOK"."BOOKNO" IS '书的编号';
COMMENT ON COLUMN "LIBRARY"."BOOK"."AUTNO" IS '作者';
COMMENT ON COLUMN "LIBRARY"."BOOK"."PRENO" IS '出版社';
COMMENT ON COLUMN "LIBRARY"."BOOK"."PRICE" IS '价格';
COMMENT ON COLUMN "LIBRARY"."BOOK"."AMOUNT" IS '图书馆剩余的数量';
COMMENT ON COLUMN "LIBRARY"."BOOK"."PUBLICTIONTIME" IS '出版时间';
COMMENT ON COLUMN "LIBRARY"."BOOK"."TYPENO" IS '书的类型';
COMMENT ON COLUMN "LIBRARY"."BOOK"."BOOKNAME" IS '书名';
COMMENT ON COLUMN "LIBRARY"."BOOK"."ISBN" IS '书存放的位置: 楼_架_号';

-- ----------------------------
-- Table structure for BOOKTYPE
-- ----------------------------
DROP TABLE "LIBRARY"."BOOKTYPE";
CREATE TABLE "LIBRARY"."BOOKTYPE" (
  "TYPENO" NUMBER(10) NOT NULL ,
  "TYPENAME" VARCHAR2(10 BYTE) NOT NULL 
)
TABLESPACE "USERS"
LOGGING
NOCOMPRESS
PCTFREE 10
INITRANS 1
STORAGE (
  INITIAL 65536 
  NEXT 1048576 
  MINEXTENTS 1
  MAXEXTENTS 2147483645
  BUFFER_POOL DEFAULT
)
PARALLEL 1
NOCACHE
DISABLE ROW MOVEMENT
;
COMMENT ON COLUMN "LIBRARY"."BOOKTYPE"."TYPENO" IS '类型编号';
COMMENT ON COLUMN "LIBRARY"."BOOKTYPE"."TYPENAME" IS '类型名';

-- ----------------------------
-- Table structure for BORROWRECORD
-- ----------------------------
DROP TABLE "LIBRARY"."BORROWRECORD";
CREATE TABLE "LIBRARY"."BORROWRECORD" (
  "STUNO" NUMBER(10) NOT NULL ,
  "BOOKNO" NUMBER(10) NOT NULL ,
  "ADMINNO" NUMBER(10) NOT NULL ,
  "ISRETURN" NUMBER(1) ,
  "BORROWTIME" DATE ,
  "BORROWDAY" NUMBER(5) ,
  "BORRNO" NUMBER(11) NOT NULL 
)
TABLESPACE "USERS"
LOGGING
NOCOMPRESS
PCTFREE 10
INITRANS 1
STORAGE (
  INITIAL 65536 
  NEXT 1048576 
  MINEXTENTS 1
  MAXEXTENTS 2147483645
  BUFFER_POOL DEFAULT
)
PARALLEL 1
NOCACHE
DISABLE ROW MOVEMENT
;
COMMENT ON COLUMN "LIBRARY"."BORROWRECORD"."STUNO" IS '学号';
COMMENT ON COLUMN "LIBRARY"."BORROWRECORD"."BOOKNO" IS '书的编号';
COMMENT ON COLUMN "LIBRARY"."BORROWRECORD"."ADMINNO" IS '操作的管理员';
COMMENT ON COLUMN "LIBRARY"."BORROWRECORD"."ISRETURN" IS '是否归还';
COMMENT ON COLUMN "LIBRARY"."BORROWRECORD"."BORROWTIME" IS '借阅时间';
COMMENT ON COLUMN "LIBRARY"."BORROWRECORD"."BORROWDAY" IS '借阅天数';
COMMENT ON COLUMN "LIBRARY"."BORROWRECORD"."BORRNO" IS '借书记录编号';

-- ----------------------------
-- Table structure for CLASS
-- ----------------------------
DROP TABLE "LIBRARY"."CLASS";
CREATE TABLE "LIBRARY"."CLASS" (
  "CLASSNO" NUMBER(11) NOT NULL ,
  "CLASSNAME" VARCHAR2(20 BYTE) NOT NULL 
)
TABLESPACE "USERS"
LOGGING
NOCOMPRESS
PCTFREE 10
INITRANS 1
STORAGE (
  INITIAL 65536 
  NEXT 1048576 
  MINEXTENTS 1
  MAXEXTENTS 2147483645
  BUFFER_POOL DEFAULT
)
PARALLEL 1
NOCACHE
DISABLE ROW MOVEMENT
;
COMMENT ON COLUMN "LIBRARY"."CLASS"."CLASSNO" IS '班级编号';
COMMENT ON COLUMN "LIBRARY"."CLASS"."CLASSNAME" IS '班级全名';

-- ----------------------------
-- Table structure for PRESS
-- ----------------------------
DROP TABLE "LIBRARY"."PRESS";
CREATE TABLE "LIBRARY"."PRESS" (
  "PRENO" NUMBER(10) NOT NULL ,
  "PRENAME" VARCHAR2(30 BYTE) NOT NULL 
)
TABLESPACE "USERS"
LOGGING
NOCOMPRESS
PCTFREE 10
INITRANS 1
STORAGE (
  INITIAL 65536 
  NEXT 1048576 
  MINEXTENTS 1
  MAXEXTENTS 2147483645
  BUFFER_POOL DEFAULT
)
PARALLEL 1
NOCACHE
DISABLE ROW MOVEMENT
;

-- ----------------------------
-- Table structure for RETURNRECORD
-- ----------------------------
DROP TABLE "LIBRARY"."RETURNRECORD";
CREATE TABLE "LIBRARY"."RETURNRECORD" (
  "STUNO" NUMBER(10) NOT NULL ,
  "BOOKNO" NUMBER(10) NOT NULL ,
  "ADMINNO" NUMBER(10) NOT NULL ,
  "ISOVERDUE" NUMBER(1) NOT NULL ,
  "RETURNTIME" DATE ,
  "RETNO" NUMBER(11) NOT NULL 
)
TABLESPACE "USERS"
LOGGING
NOCOMPRESS
PCTFREE 10
INITRANS 1
STORAGE (
  INITIAL 65536 
  NEXT 1048576 
  MINEXTENTS 1
  MAXEXTENTS 2147483645
  BUFFER_POOL DEFAULT
)
PARALLEL 1
NOCACHE
DISABLE ROW MOVEMENT
;
COMMENT ON COLUMN "LIBRARY"."RETURNRECORD"."STUNO" IS '学号';
COMMENT ON COLUMN "LIBRARY"."RETURNRECORD"."BOOKNO" IS '书的编号';
COMMENT ON COLUMN "LIBRARY"."RETURNRECORD"."ADMINNO" IS '操作的管理员';
COMMENT ON COLUMN "LIBRARY"."RETURNRECORD"."ISOVERDUE" IS '是否过期';
COMMENT ON COLUMN "LIBRARY"."RETURNRECORD"."RETURNTIME" IS '归还日期';
COMMENT ON COLUMN "LIBRARY"."RETURNRECORD"."RETNO" IS '归书记录编号';

-- ----------------------------
-- Table structure for STUDENT
-- ----------------------------
DROP TABLE "LIBRARY"."STUDENT";
CREATE TABLE "LIBRARY"."STUDENT" (
  "STUNO" NUMBER(10) NOT NULL ,
  "STUNAME" VARCHAR2(20 BYTE) NOT NULL ,
  "SEX" VARCHAR2(3 BYTE) ,
  "AGE" NUMBER(4) ,
  "CLASSNO" NUMBER(20) NOT NULL ,
  "IDCARD" VARCHAR2(18 BYTE) NOT NULL ,
  "STUPASSWORD" VARCHAR2(20 BYTE) NOT NULL ,
  "STUPASSWORDQUESTION" VARCHAR2(50 BYTE) NOT NULL ,
  "STUPASSWORDANSWER" VARCHAR2(50 BYTE) NOT NULL ,
  "STUTEL" VARCHAR2(11 BYTE) 
)
TABLESPACE "USERS"
LOGGING
NOCOMPRESS
PCTFREE 10
INITRANS 1
STORAGE (
  INITIAL 65536 
  NEXT 1048576 
  MINEXTENTS 1
  MAXEXTENTS 2147483645
  BUFFER_POOL DEFAULT
)
PARALLEL 1
NOCACHE
DISABLE ROW MOVEMENT
;
COMMENT ON COLUMN "LIBRARY"."STUDENT"."STUNO" IS '学生学号';
COMMENT ON COLUMN "LIBRARY"."STUDENT"."STUNAME" IS '学生姓名';
COMMENT ON COLUMN "LIBRARY"."STUDENT"."SEX" IS '性别';
COMMENT ON COLUMN "LIBRARY"."STUDENT"."AGE" IS '年龄';
COMMENT ON COLUMN "LIBRARY"."STUDENT"."CLASSNO" IS '班级编号';
COMMENT ON COLUMN "LIBRARY"."STUDENT"."IDCARD" IS '身份证号码';
COMMENT ON COLUMN "LIBRARY"."STUDENT"."STUPASSWORD" IS '学生用户的密码';
COMMENT ON COLUMN "LIBRARY"."STUDENT"."STUPASSWORDQUESTION" IS '密码问题';
COMMENT ON COLUMN "LIBRARY"."STUDENT"."STUPASSWORDANSWER" IS '密码答案';
COMMENT ON COLUMN "LIBRARY"."STUDENT"."STUTEL" IS '电话号码';

-- ----------------------------
-- Table structure for TICKET
-- ----------------------------
DROP TABLE "LIBRARY"."TICKET";
CREATE TABLE "LIBRARY"."TICKET" (
  "STUNO" NUMBER(10) NOT NULL ,
  "BOOKNO" NUMBER(10) NOT NULL ,
  "MONEY" NUMBER(10) NOT NULL ,
  "TICNO" NUMBER(10) NOT NULL ,
  "TICDATE" DATE NOT NULL ,
  "RETURNNO" NUMBER(10) ,
  "BORROWNO" NUMBER(10) 
)
TABLESPACE "USERS"
LOGGING
NOCOMPRESS
PCTFREE 10
INITRANS 1
STORAGE (
  BUFFER_POOL DEFAULT
)
PARALLEL 1
NOCACHE
DISABLE ROW MOVEMENT
;
COMMENT ON COLUMN "LIBRARY"."TICKET"."STUNO" IS '被罚款的学生的学号';
COMMENT ON COLUMN "LIBRARY"."TICKET"."BOOKNO" IS '书的编号';
COMMENT ON COLUMN "LIBRARY"."TICKET"."MONEY" IS '罚款金额';
COMMENT ON COLUMN "LIBRARY"."TICKET"."TICNO" IS '罚款单号';
COMMENT ON COLUMN "LIBRARY"."TICKET"."TICDATE" IS '罚款日期';
COMMENT ON COLUMN "LIBRARY"."TICKET"."RETURNNO" IS '借书记录';
COMMENT ON COLUMN "LIBRARY"."TICKET"."BORROWNO" IS '归书记录';

4. 创建视图

4.1创建视图语句

-- ----------------------------
-- View structure for ADMIN_INFO
-- 管理员的信息视图
-- ----------------------------
CREATE OR REPLACE VIEW "LIBRARY"."ADMIN_INFO" AS SELECT
LIBRARY.ADMININF.ADMNO,
LIBRARY.ADMININF.ADMNAME
FROM
LIBRARY.ADMININF;

-- ----------------------------
-- View structure for AUTHOR_INFO
-- 作者的信息视图
-- ----------------------------
CREATE OR REPLACE VIEW "LIBRARY"."AUTHOR_INFO" AS SELECT
"a".AUTNO,
"a".AUTNAME,
"a".COUNTRY,
"b".BOOKNAME
FROM
LIBRARY.BOOK "b"
INNER JOIN LIBRARY.AUTHOR "a" ON "b".AUTNO = "a".AUTNO;

-- ----------------------------
-- View structure for BOOK_INFO
-- 图书的信息视图
-- ----------------------------
CREATE OR REPLACE VIEW "LIBRARY"."BOOK_INFO" AS SELECT
"b".BOOKNO,
"b".ISBN,
"b".BOOKNAME,
"bt".TYPENAME,
"p".PRENAME,
"b".PUBLICTIONTIME,
"a".AUTNAME,
"b".AMOUNT
FROM
LIBRARY.BOOK "b"
INNER JOIN LIBRARY.BOOKTYPE "bt" ON "b".TYPENO = "bt".TYPENO
INNER JOIN LIBRARY.PRESS "p" ON "b".PRENO = "p".PRENO
INNER JOIN LIBRARY.AUTHOR "a" ON "b".AUTNO = "a".AUTNO;

-- ----------------------------
-- View structure for BORROW_INFO
-- 借书记录的信息表
-- ----------------------------
CREATE OR REPLACE VIEW "LIBRARY"."BORROW_INFO" AS SELECT
LIBRARY.BORROWRECORD.BORRNO,
LIBRARY.STUDENT.STUNAME,
LIBRARY.ADMININF.ADMNAME,
LIBRARY.BOOK.BOOKNAME,
LIBRARY.BORROWRECORD.ISRETURN,
LIBRARY.BORROWRECORD.BORROWTIME,
LIBRARY.BORROWRECORD.BORROWDAY
FROM
LIBRARY.BORROWRECORD
INNER JOIN LIBRARY.BOOK ON LIBRARY.BORROWRECORD.BOOKNO = LIBRARY.BOOK.BOOKNO
INNER JOIN LIBRARY.ADMININF ON LIBRARY.BORROWRECORD.ADMINNO = LIBRARY.ADMININF.ADMNO
INNER JOIN LIBRARY.STUDENT ON LIBRARY.BORROWRECORD.STUNO = LIBRARY.STUDENT.STUNO;

-- ----------------------------
-- View structure for PRESS_INFO
-- 出版社的信息表
-- ----------------------------
CREATE OR REPLACE VIEW "LIBRARY"."PRESS_INFO" AS SELECT
LIBRARY.PRESS.PRENO,
LIBRARY.PRESS.PRENAME,
LIBRARY.BOOK.BOOKNAME
FROM
LIBRARY.BOOK
INNER JOIN LIBRARY.PRESS ON LIBRARY.BOOK.PRENO = LIBRARY.PRESS.PRENO;

-- ----------------------------
-- View structure for RETURN_INFO
-- 归书记录的信息表
-- ----------------------------
CREATE OR REPLACE VIEW "LIBRARY"."RETURN_INFO" AS SELECT
LIBRARY.RETURNRECORD.RETNO,
LIBRARY.STUDENT.STUNAME,
LIBRARY.BOOK.BOOKNAME,
LIBRARY.ADMININF.ADMNAME,
LIBRARY.RETURNRECORD.ISOVERDUE,
LIBRARY.RETURNRECORD.RETURNTIME
FROM
LIBRARY.RETURNRECORD
INNER JOIN LIBRARY.BOOK ON LIBRARY.RETURNRECORD.BOOKNO = LIBRARY.BOOK.BOOKNO
INNER JOIN LIBRARY.ADMININF ON LIBRARY.RETURNRECORD.ADMINNO = LIBRARY.ADMININF.ADMNO
INNER JOIN LIBRARY.STUDENT ON LIBRARY.RETURNRECORD.STUNO = LIBRARY.STUDENT.STUNO;

-- ----------------------------
-- View structure for STUDENT_INFO
-- 学生信息表
-- ----------------------------
CREATE OR REPLACE VIEW "LIBRARY"."STUDENT_INFO" AS SELECT
LIBRARY.STUDENT.STUNO,
LIBRARY.STUDENT.STUNAME,
LIBRARY.STUDENT.SEX,
LIBRARY.STUDENT.AGE,
LIBRARY.CLASS.CLASSNAME,
LIBRARY.STUDENT.IDCARD,
LIBRARY.STUDENT.STUTEL
FROM
LIBRARY.STUDENT
INNER JOIN LIBRARY.CLASS ON LIBRARY.STUDENT.CLASSNO = LIBRARY.CLASS.CLASSNO;

5. 设置主键序列自增

5.1 作用：

设置主键序列自增，在插入表中数据时，主键能自动生成，类似MySQL 中的 auto

5.2 语句：

-- 学生主键自增序列
create sequence seq_student_types_auto
  minvalue 29040301
  maxvalue 30000000
  start with 29040301
  increment by 1
  cache 50;
  
  create or replace trigger trig_student_types_auto
  before insert on student
  referencing OLD as old NEW as new for each row
declare
begin
    select seq_student_types_auto.nextval into :new.stuno from dual;
end trig_student_types_auto;

-- 管理员编号
create sequence seq_admin_types_auto
  minvalue 19040601
  maxvalue 20000000
  start with 19040601
  increment by 1
  cache 50;
  
create or replace trigger trig_admin_types_auto
  before insert on admininf
  referencing OLD as old NEW as new for each row
declare
begin
    select seq_admin_types_auto.nextval into :new.admno from dual;
end trig_admin_types_auto;

-- 图书编号
create sequence seq_book_types_auto
  minvalue 1
  maxvalue 10000
  start with 1
  increment by 1
  cache 50;
  
create or replace trigger trig_book_types_auto
  before insert on book
  referencing OLD as old NEW as new for each row
declare
begin
    select seq_book_types_auto.nextval into :new.bookno from dual;
end trig_book_types_auto;

-- 作者编号
create sequence seq_author_types_auto
  minvalue 1
  maxvalue 10000
  start with 1
  increment by 1
  cache 50;
  
create or replace trigger trig_author_types_auto
  before insert on author
  referencing OLD as old NEW as new for each row
declare
begin
    select seq_author_types_auto.nextval into :new.autno from dual;
end trig_author_types_auto;

-- 书的编号
create sequence seq_booktype_types_auto
  minvalue 1
  maxvalue 10000
  start with 1
  increment by 1
  cache 50;
  
create or replace trigger trig_booktype_types_auto
  before insert on booktype
  referencing OLD as old NEW as new for each row
declare
begin
    select seq_booktype_types_auto.nextval into :new.typeno from dual;
end trig_booktype_types_auto;

-- 借书记录的编号
create sequence seq_borrowrecord_types_auto
  minvalue 1
  maxvalue 10000
  start with 1
  increment by 1
  cache 50;
  
create or replace trigger trig_borrowrecord_types_auto
  before insert on borrowrecord
  referencing OLD as old NEW as new for each row
declare
begin
    select seq_borrowrecord_types_auto.nextval into :new.BORRNO from dual;
end trig_borrowrecord_types_auto;


-- 班级的班号
create sequence seq_class_types_auto
  minvalue 1
  maxvalue 10000
  start with 1
  increment by 1
  cache 50;
  
create or replace trigger trig_class_types_auto
  before insert on class
  referencing OLD as old NEW as new for each row
declare
begin
    select seq_class_types_auto.nextval into :new.classno from dual;
end trig_class_types_auto;


-- 出版社的出版社号
create sequence seq_press_types_auto
  minvalue 1
  maxvalue 10000
  start with 1
  increment by 1
  cache 50;
  
create or replace trigger trig_press_types_auto
  before insert on press
  referencing OLD as old NEW as new for each row
declare
begin
    select seq_press_types_auto.nextval into :new.preno from dual;
end trig_press_types_auto;

-- 出版社的出版社号
create sequence seq_press_types_auto
  minvalue 1
  maxvalue 10000
  start with 1
  increment by 1
  cache 50;
  
create or replace trigger trig_press_types_auto
  before insert on press
  referencing OLD as old NEW as new for each row
declare
begin
    select seq_press_types_auto.nextval into :new.preno from dual;
end trig_press_types_auto;

-- 归书的归书的记录号
create sequence seq_returnrecord_types_auto
  minvalue 1
  maxvalue 10000
  start with 1
  increment by 1
  cache 50;
  
create or replace trigger trig_returnrecord_types_auto
  before insert on returnrecord
  referencing OLD as old NEW as new for each row
declare
begin
    select seq_returnrecord_types_auto.nextval into :new.retno from dual;
end trig_returnrecord_types_auto;


-- 罚款记录
create sequence seq_ticket_types_auto
  minvalue 1
  maxvalue 10000
  start with 1
  increment by 1
  cache 50;
  
create or replace trigger trig_ticket_types_auto
  before insert on ticket
  referencing OLD as old NEW as new for each row
declare
begin
    select seq_ticket_types_auto.nextval into :new.ticno from dual;
end trig_ticket_types_auto;

6. 创建索引

CREATE [UNIQUE|CLUSTERED] INDEX INDEX_NAME ON TABLE_NAME(PROPERTY_NAME);

-- 创建学生.身份证唯一索引
CREATE UNIQUE INDEX unique_index_student_idcard ON student(idcard);

-- 创建学生.号码唯一索引
CREATE UNIQUE INDEX unique_index_student_stutel ON student(stutel);

-- 创建管理员.号码唯一索引
CREATE UNIQUE INDEX unique_index_admin_admtel ON ADMININF(ADMTEL);

-- 创建图书.位置的唯一索引
CREATE UNIQUE INDEX unique_index_book_isbn ON book(isbn);

-- 创建图书类型.类型名称的唯一索引
CREATE UNIQUE INDEX unique_index_booktype_typename ON booktype(typename);

-- 创建班级.班级名的唯一索引
CREATE UNIQUE INDEX unique_index_class_classname ON class(classname);

-- 创建出版社.出版社名的唯一索引
CREATE UNIQUE INDEX unique_index_press_prename ON press(prename);

-- 创建作者.作者名的唯一索引
CREATE UNIQUE INDEX unique_index_author_autname ON author(autname);

7. 创建触发器

-- 触发器
-- 限制身份证的位数为18位
CREATE OR REPLACE TRIGGER stu_idCard_tri
BEFORE INSERT OR UPDATE ON student
REFERENCING OLD AS old NEW AS new
FOR EACH ROW
BEGIN
  IF(length(:NEW.IDCARD)<>18)THEN
     RAISE_APPLICATION_ERROR(-20004,'身份证的位数为18位。');
  END IF;
END stu_idCard_tri;

-- 限制学生的电话号码为11位
CREATE OR REPLACE TRIGGER stu_stutel_tri
BEFORE INSERT OR UPDATE ON student
REFERENCING OLD AS old NEW AS new
FOR EACH ROW
BEGIN
  IF(length(:NEW.stutel)<>11)THEN
     RAISE_APPLICATION_ERROR(-20007,'电话号码的位数为11位');
  END IF;
END stu_stutel_tri;

-- 限制管理员的电话号码为11位
CREATE OR REPLACE TRIGGER adm_admtel_tri
BEFORE INSERT OR UPDATE ON ADMININF
REFERENCING OLD AS old NEW AS new
FOR EACH ROW
BEGIN
  IF(length(:NEW.admtel)<>11)THEN
     RAISE_APPLICATION_ERROR(-20008,'电话号码的位数为11位');
  END IF;
END adm_admtel_tri;

-- 限制学生的性别输入
CREATE OR REPLACE TRIGGER stu_sex_tri
BEFORE INSERT OR UPDATE ON student
REFERENCING OLD AS old NEW AS new
FOR EACH ROW
BEGIN
  IF(:NEW.sex not in['男','女'])THEN
     RAISE_APPLICATION_ERROR(-20008,'性别输入不合法');
  END IF;
END stu_sex_tri;

-- 限制学生的年龄输入
CREATE OR REPLACE TRIGGER stu_age_tri
BEFORE INSERT OR UPDATE ON student
REFERENCING OLD AS old NEW AS new
FOR EACH ROW
BEGIN
  IF(:NEW.age<0 or :NEW >110)THEN
     RAISE_APPLICATION_ERROR(-20010,'年龄输入不合法');
  END IF;
END stu_age_tri;

8. 查询语句

8.1 作者的查询语句

	-- 1.查询指定作者的信息
	SELECT AUTNO,AUTNAME,COUNTRY FROM AUTHOR
	WHERE AUTNAME =  '思妥耶夫斯基';
	
	-- 2.查看指定作者一共写了多少本书
	SELECT COUNT(*)
	FROM BOOK
	WHERE AUTNO = (SELECT AUTNO FROM AUTHOR WHERE AUTNAME = '史蒂芬·霍金');
	
	-- 3.查看一个作者写了哪些书
	SELECT BOOKNAME 
	FROM BOOK+
	WHERE AUTNO = (SELECT AUTNO FROM AUTHOR WHERE AUTNAME = '史蒂芬·霍金');
	
	-- 4.查看有哪些作者
	SELECT DISTINCT AUTNAME 
	FROM AUTHOR;
	
	-- 5.查看作者的编号
    SELECT AUTNO 
    FROM AUTHOR 
    WHERE AUTNAME = '思妥耶夫斯基';

8.2 学生的查询语句

-- 学生操作

-- 1.查询指定学生的信息
SELECT STUNO,STUNAME,SEX,AGE,CLASSNAME,STUTEL
FROM STUDENT
WHERE STUNO = 200420172;


-- 2.登录操作
-- 根据账号查询用户的密码
SELECT STUPASSWORD
FROM STUDENT
WHERE STUNO = 200420172;


-- 3..注册操作
-- ①.向表中插入输入数据
INSERT INTO STUDENT VALUES(200430132,'邓紫棋','女','26','人工智能2001','32089220701086677X','ysyhl9t','我最喜欢看的电影？','敢死队','1308129553');
-- ②.验证
SELECT * 
FROM STUDENT
WHERE STUNO = 200430132;


-- 4.修改密码
-- ①.根据学生的账号 和 密码问题找答案
SELECT STUPASSWORDANSWER
FROM STUDENT
WHERE STUNO = 200420172 AND STUPASSWORDQUESTION = '我最喜欢看的电影？';
--②.修改密码操作
UPDATE STUDENT SET STUPASSWORD = 'cqmyg'
WHERE STUNO = 200420172;
-- ③.验证
SELECT * 
FROM STUDENT
WHERE STUNO = 200420172;


-- 5.修改密码问题答案
-- ①.根据账号和问题来修改答案
UPDATE STUDENT SET STUPASSWORDANSWER = '周延 G.A.Y'
WHERE STUNO = 200420172 AND STUPASSWORDQUESTION = '我最喜欢看的电影？';
-- ②.验证
SELECT * 
FROM STUDENT
WHERE STUNO = 200420172;


-- 6.修改学生的资料
-- ①根据学号去修改学生的信息
UPDATE STUDENT SET STUNAME = '陶喆' , SEX = '男', AGE = '21' ,CLASSNAME = '人工智能2003', STUPASSWORDANSWER = '周延 G.A.Y',STUTEL = '13071258802'
WHERE STUNO = 200420172 ;
-- ②验证
SELECT * 
FROM STUDENT
WHERE STUNO = 200420172;


-- 7.注销学生账号操作
-- ①.查询是否有这个学生
SELECT STUNO 
FROM STUDENT
WHERE STUNO = 200420173;
-- ②.删除指定的用户账号
DELETE FROM STUDENT WHERE STUNO = 200420173;
-- ③.验证
SELECT STUNO 
FROM STUDENT
WHERE STUNO = 200420173;

8.3 图书的查询语句

-- 1.查询所有图书的信息
SELECT
"b".BOOKNO AS "编号",
"b".ISBN AS "储存位置",
"b".BOOKNAME AS "书名",
"bt".TYPENAME AS "类型",
"p".PRENAME AS "出版社",
"b".PUBLICTIONTIME AS "出版时间",
"a".AUTNAME AS "作者",
"b".AMOUNT AS "剩余数量"
FROM LIBRARY.BOOK "b"
INNER JOIN LIBRARY.BOOKTYPE "bt" ON "b".TYPENO = "bt".TYPENO
INNER JOIN LIBRARY.PRESS "p" ON "b".PRENO = "p".PRENO
INNER JOIN LIBRARY.AUTHOR "a" ON "b".AUTNO = "a".AUTNO;

-- 2.模糊查询图书的信息
SELECT * 
FROM BOOK_INFO
WHERE BOOKNAME LIKE '%'||'人生'||'%';

-- 3.查询图书馆的库存量
SELECT SUM(AMOUNT)
FROM BOOK;

-- 4.查看指定类型的书
SELECT * 
FROM BOOK_INFO
WHERE TYPENAME = '哲学类';

-- 5.查看还有库存的书
SELECT BOOKNAME,ISBN,TYPENAME,PRENAME,AUTNAME,AMOUNT
FROM BOOK_INFO
WHERE AMOUNT > 0
ORDER BY AMOUNT;

-- 6.查看图书馆借完的书
SELECT BOOKNAME,TYPENAME,PRENAME,AUTNAME,AMOUNT
FROM BOOK_INFO
WHERE AMOUNT = 0
ORDER BY AMOUNT;

-- 7.借书操作 数量-1
UPDATE BOOK SET AMOUNT = (SELECT AMOUNT FROM BOOK WHERE BOOKNO = 45) - 1
WHERE BOOKNO = 45;
-- 验证
SELECT * FROM BOOK WHERE BOOKNO = 45;


-- 8.还书操作 数量+1
UPDATE BOOK SET AMOUNT = (SELECT AMOUNT FROM BOOK WHERE BOOKNO = 45) + 1
WHERE BOOKNO = 45;
-- 验证
SELECT * FROM BOOK WHERE BOOKNO = 45;

-- 9.向图书馆中增加新书
-- ①.先查看数据库中有没有此类的书
SELECT * 
FROM BOOK
WHERE BOOKNAME = '罪与罚';
-- ②.如果没有此类作者 则向AUTHOR表中插入作者信息
INSERT INTO AUTHOR VALUES((SELECT COUNT(*) FROM AUTHOR) + 1,'思妥耶夫斯基','俄国');
-- ③.如果没有此类出版社 则向PRESS表中插入出版社信息
INSERT INTO PRESS VALUES((SELECT COUNT(*) FROM PRESS) + 1,'新华书社');
-- ④.增加新的图书
INSERT INTO BOOK VALUES(
(SELECT COUNT(*) FROM BOOK) + 1, -- 书的编号
(SELECT AUTNO FROM AUTHOR WHERE AUTNAME = '思妥耶夫斯基'), -- 作者编号
(SELECT PRENO FROM PRESS WHERE PRENAME =  '人民邮电出版社' ),-- 出版社编号
6,-- 数量
2004,-- 出版日期
);

8.4 出版社的查询语句

-- 1.模糊查询出版社信息
SELECT * FROM PRESS WHERE PRENAME LIKE '%邮电%';

-- 2.查看一个出版社出的图书数量
SELECT Count(*)
FROM LIBRARY.BOOK_INFO
WHERE LIBRARY.BOOK_INFO.PRENAME = '人民邮电出版社';

-- 3.查看一个出版设出的所有图书
SELECT BOOKNO,BOOKNAME,ISBN,AUTNAME,PRENAME,PUBLICTIONTIME
FROM LIBRARY.BOOK_INFO
WHERE LIBRARY.BOOK_INFO.PRENAME = '人民邮电出版社';

8.5 管理员的查询语句

/* 管理员操作 */

-- 1.查询指定的管理员信息
SELECT *
FROM ADMININF
WHERE ADMNO = 190430132;


-- 2.管理员的登
-- 根据用户名查询密码
SELECT ADMPASSWORD 
FROM ADMININF
WHERE ADMNO = 190430132;


-- 3.注册操作
-- ①.查看是否有这个管理员
SELECT * 
FROM ADMININF
WHERE ADMNO = 190330132;
-- ①.注册管理员
INSERT INTO ADMININF VALUES (190330132,'周杰伦','ysyhl9t','我最喜欢的歌手是？','李荣浩');
-- ②.验证
SELECT * 
FROM ADMININF
WHERE ADMNO = 190330132;


-- 4.修改密码
-- ①.根据管理员的账号 和 密码问题找答案
SELECT ADMNO,ADMPASSWORDQUESTION, ADMPASSWORDANSWER
FROM ADMININF
WHERE ADMNO = 190430132 AND ADMPASSWORDQUESTION = '我最喜欢的歌手是？';
-- ②.修改密码操作
UPDATE ADMININF SET ADMPASSWORD = 'cqmyg'
WHERE ADMNO = 190430132;
-- ③.验证
SELECT ADMNO,ADMPASSWORD
FROM ADMININF
WHERE ADMNO = 190430132;


-- 5.修改管理员的密码答案
-- ①.根据管理员的账号和密码问题来来修改密码答案
UPDATE ADMININF SET ADMPASSWORDANSWER = '邓紫棋'
WHERE ADMNO = 190430132 AND ADMPASSWORDQUESTION = '我最喜欢的歌手是？';
-- ②.验证信息
SELECT ADMNO,ADMPASSWORDQUESTION,ADMPASSWORDANSWER
FROM ADMININF
WHERE ADMNO = 190430132;


-- 6.修改管理员的资料
-- ①.根据账号去修改管理员的信息
UPDATE ADMININF SET ADMNAME = '李白'
WHERE ADMNO = 190330132;
-- ②.验证
SELECT * 
FROM ADMININF
WHERE ADMNO = 190330132;


-- 7.查询特定班的所有学生信息
-- ①.查询一共哪几个班
SELECT DISTINCT  CLASSNAME 
FROM STUDENT;
-- ②.查询
SELECT * 
FROM STUDENT
WHERE CLASSNAME = '大数据2001';


-- 8.查询共有多少个班
SELECT COUNT(*) AS 
FROM (SELECT DISTINCT  CLASSNAME 
FROM STUDENT);


-- 9.注销管理员
-- ①.查看管理员表中是否有这个管理员
SELECT ADMNO
FROM ADMININF
WHERE ADMNO = 190430132;
-- ②.删除管理员
DELETE FROM ADMININF WHERE ADMNO = 190430132;
-- ③.验证
SELECT ADMNO
FROM ADMININF
WHERE ADMNO = 190430132;

9. 创建存储过程和函数

9.1 图书的存储过程和函数

-- 借书操作：指定图书的数量-1
CREATE OR REPLACE CREATE OR REPLACE FUNCTION BORROW_BOOK (V_BOOKNO IN NUMBER)
RETURN VARCHAR2
  IS
    P_COUNT NUMBER;
    P_RESTEL VARCHAR2(30);
  BEGIN
    SELECT AMOUNT
    INTO P_COUNT
    FROM BOOK
    WHERE BOOKNO = V_BOOKNO;
    
    IF P_COUNT <= 0 THEN
      P_RESTEL := '此数已经被借光，请下次再来！';
    ELSE 
      UPDATE BOOK SET AMOUNT = (SELECT AMOUNT FROM BOOK WHERE BOOKNO = V_BOOKNO) - 1 
      WHERE BOOKNO = V_BOOKNO;
      COMMIT;
      P_RESTEL := '操作成功，请及时归还！';
    END IF;
    RETURN P_RESTEL;
  END BORROW_BOOK;
  
  -- 计算图书馆中的图书的库存量
  CREATE OR REPLACE FUNCTION COUNT_BOOK_NUMBER RETURN VARCHAR2
  IS
    P_COUNT NUMBER;
    P_RESTEL VARCHAR2(50);
  BEGIN
    SELECT SUM(AMOUNT)
    INTO P_COUNT
    FROM BOOK;
    
    IF P_COUNT = 0 THEN
      P_RESTEL := '这个图书馆倒闭了';
    ELSE 
      P_RESTEL := P_COUNT;
    END IF;
    RETURN P_RESTEL;
  END COUNT_BOOK_NUMBER;
  
  -- 归书操作：被还书的数量+1
  CREATE OR REPLACE FUNCTION RETURN_BOOK (V_BOOKNO IN NUMBER)RETURN VARCHAR2
  IS
  BEGIN
    UPDATE BOOK SET AMOUNT = (SELECT AMOUNT FROM BOOK WHERE BOOKNO = V_BOOKNO) + 1
    WHERE BOOKNO = V_BOOKNO;
    RETURN '操作成功,欢迎光临！';
  END RETURN_BOOK;

9.2 学生的存储过程和函数

  -- 1.找回密码操作
  CREATE OR REPLACE FUNCTION FIND_PASSWORD_BY_STUNO (P_NO IN NUMBER,P_ANSWER IN VARCHAR2)RETURN STUDENT.STUPASSWORD%TYPE
  IS
    V_PASSWORD STUDENT.STUPASSWORD%TYPE;
  BEGIN
    SELECT STUPASSWORD
    INTO V_PASSWORD
    FROM STUDENT
    WHERE STUNO = P_NO AND P_ANSWER = (
                                            SELECT STUPASSWORDANSWER 
                                            FROM STUDENT
                                            WHERE STUNO = P_NO
                                          );
    RETURN V_PASSWORD;
  EXCEPTION 
    WHEN NO_DATA_FOUND THEN
     RETURN '查无此人';
  END FIND_PASSWORD_BY_STUNO;
  
  -- 2.注销学生账号
  CREATE OR REPLACE PROCEDURE DELETE_STU
  (
    V_NO IN STUDENT.STUNO%TYPE,
    V_RESTEL OUT VARCHAR2
  )
  IS
    P_COUNT NUMBER;
  BEGIN
     SELECT COUNT(*)
     INTO P_COUNT
     FROM STUDENT
     WHERE STUNO = V_NO;
     
     IF P_COUNT <> 0 THEN
      DELETE FROM STUDENT WHERE STUNO = V_NO;
      V_RESTEL := '删除用户成功';
    ELSE
      V_RESTEL := '未找到该用户';
    END IF;
  END DELETE_STU;
  
  -- 3.查找用户信息
CREATE OR REPLACE PROCEDURE FIND_STU_INFO(
  P_STUNO IN STUDENT.STUNO%TYPE, 

  P_STUNAME out STUDENT.STUNAME%TYPE,
  P_SEX out STUDENT.SEX%TYPE,
  P_AGE out STUDENT.AGE%TYPE,
  P_CLASSNAME out CLASS.CLASSNAME%TYPE,
  P_STUTEL out STUDENT.STUTEL%TYPE
) 
IS
begin
  SELECT
  LIBRARY.STUDENT.STUNAME,
  LIBRARY.STUDENT.SEX,
  LIBRARY.STUDENT.AGE,
  LIBRARY.CLASS.CLASSNAME,
  LIBRARY.STUDENT.STUTEL
  INTO P_STUNAME,P_SEX,P_AGE,P_CLASSNAME,P_STUTEL
  FROM
  LIBRARY.CLASS
  INNER JOIN LIBRARY.STUDENT ON LIBRARY.STUDENT.CLASSNO = LIBRARY.CLASS.CLASSNO
  WHERE STUDENT.STUNO = P_STUNO;

exception
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('查无此人');
    
end FIND_STU_INFO;
  
-- 4.注册操作
CREATE OR REPLACE PROCEDURE INSERT_INTO_STUDENT
(
  V_NAME IN STUDENT.STUNAME%TYPE,
  V_SEX IN STUDENT.SEX%TYPE,
  V_AGE IN STUDENT.AGE%TYPE,
  V_CLASS IN CLASS.CLASSNAME%TYPE,
  V_IdCard IN STUDENT.IDCARD%TYPE,
  V_PASS IN STUDENT.STUPASSWORD%TYPE,
  V_ANSWER IN STUDENT.STUPASSWORDANSWER%TYPE,
  V_TEL IN STUDENT.STUTEL%TYPE,
  V_RESTLE OUT VARCHAR 
)
IS
  P_COUNT NUMBER;-- 判断数据库中是否有这个学生
BEGIN
SELECT COUNT(*)
INTO P_COUNT
FROM STUDENT
WHERE IDCARD = V_IdCard;

IF P_COUNT = 0 THEN
  INSERT INTO STUDENT(
    STUNAME,
    SEX,
    AGE,
    CLASSNO,
    IDCARD,
    STUPASSWORD,
    STUPASSWORDQUESTION,
    STUPASSWORDANSWER,
    STUTEL
  ) 
  VALUES(
    V_NAME ,
    V_SEX ,
    V_AGE ,
    (SELECT CLASSNO FROM CLASS WHERE CLASSNAME = V_CLASS),
    V_IdCard,
    V_PASS,
    '我最喜欢看的电影？',
    V_ANSWER,
    V_TEL
  );
  V_RESTLE := '注册成功';
ELSE
  V_RESTLE := '用户已经存在';
END IF;
END INSERT_INTO_STUDENT;

  -- 5.更改密码
  CREATE OR REPLACE PROCEDURE UPDATE_STU_PASSWORD
(
  V_NO IN STUDENT.STUNO%TYPE, -- 用户的账号
  V_ANSWER IN STUDENT.STUPASSWORDANSWER%TYPE, -- 验证用户:密钥
  V_OLD_PASSWORD IN STUDENT.STUPASSWORD%TYPE, -- 验证用户:密码
  V_NEW_PASSWORD IN STUDENT.STUPASSWORD%TYPE,
  
  V_RESTLE OUT VARCHAR2 -- 用户提示信息
)
IS
  P_COUNT NUMBER; -- 查询用户是否存在
BEGIN
  SELECT COUNT(*)
  INTO P_COUNT
  FROM STUDENT
  WHERE STUNO = V_NO 
    AND V_ANSWER = (SELECT STUPASSWORDANSWER 
                    FROM STUDENT
                    WHERE STUNO = V_NO)
    AND V_OLD_PASSWORD = (SELECT STUPASSWORD  
                          FROM STUDENT
                          WHERE STUNO = V_NO);
IF P_COUNT <> 0 THEN
  UPDATE STUDENT SET STUPASSWORD = V_NEW_PASSWORD
  WHERE STUNO = V_NO;
    
  V_RESTLE := '更新密码成功';
ELSE
  V_RESTLE := '更新密码失败';
END IF;
END UPDATE_STU_PASSWORD;

  -- 6.更改学生的资料
  CREATE OR REPLACE PROCEDURE UPDATE_STU_DATA
(
    V_NO IN STUDENT.STUNO%TYPE,
    V_NAME IN STUDENT.STUNAME%TYPE,
    V_SEX IN STUDENT.SEX%TYPE,
    V_AGE IN STUDENT.AGE%TYPE,
    V_CLASS IN CLASS.CLASSNAME%TYPE,
    V_TEL IN STUDENT.STUTEL%TYPE,
    
    V_RESTLE OUT VARCHAR
)
IS
  P_COUNT NUMBER;
BEGIN
  SELECT COUNT(*)
  INTO P_COUNT
  FROM STUDENT
  WHERE STUNO = V_NO;
  
  IF P_COUNT = 0 THEN
    V_RESTLE := '未找到该用户';
  ELSE 
    UPDATE STUDENT SET STUNAME = V_NAME , SEX = V_SEX, AGE = V_AGE ,CLASSNO = ((SELECT CLASSNO FROM CLASS WHERE CLASSNAME = V_CLASS)),STUTEL = V_TEL
    WHERE STUNO = V_NO;
    V_RESTLE := '修改信息成功';
  END IF;
END UPDATE_STU_DATA;
  
  -- 7.更新学生的密钥
   CREATE OR REPLACE PROCEDURE UPDATE_STU_ANSWER
(
  V_NO IN STUDENT.STUNO%TYPE, -- 用户的账号
  V_PASSWORD IN STUDENT.STUPASSWORD%TYPE, -- 用户输入的密码 
  V_ANSWER IN STUDENT.STUPASSWORDANSWER%TYPE, --用户要修改的密钥
  V_DISTEL OUT VARCHAR2
)
IS
  P_COUNT NUMBER;
BEGIN
  SELECT COUNT(*)
  INTO P_COUNT
  FROM STUDENT
  WHERE STUNO = V_NO AND  V_PASSWORD = (
                                          SELECT STUPASSWORD 
                                          FROM STUDENT
                                          WHERE STUNO = V_NO
                                        );
  IF P_COUNT = 0 THEN
    V_DISTEL:= '未找到此用户';
  ELSE
    UPDATE STUDENT SET STUPASSWORDANSWER = V_ANSWER WHERE STUNO = V_NO;
    V_DISTEL:= '更新密钥成功';
  END IF;
END UPDATE_STU_ANSWER;

9.3 出版社的存储过程和函数

-- 计算每个出版社写的图书数量
CREATE OR REPLACE FUNCTION COUNT_PRESS_BOOK_NUMBER(V_PRENO IN OUT NUMBER)
RETURN VARCHAR2
IS 
  P_COUNT NUMBER;
BEGIN
  SELECT COUNT(*)
  INTO P_COUNT
  FROM BOOK
  WHERE PRENO = V_PRENO;
	
	RETURN P_COUNT;
END COUNT_PRESS_BOOK_NUMBER;

-- 通过作者的姓名找到作者的编号
CREATE OR REPLACE FUNCTION FIND_PRENO_BY_PRENAME (V_NAME IN VARCHAR2)
RETURN NUMBER

IS
	P_NO NUMBER;
BEGIN
	SELECT PRENO
	INTO P_NO
	FROM PRESS WHERE PRENAME = V_NAME;
	
	RETURN P_NO;
EXCEPTION
	WHEN NO_DATA_FOUND THEN
		RETURN -1;
END;

-- 向数据中插入出版社的信息，并返回出版社的编号
CREATE OR REPLACE PROCEDURE INSERT_INTO_PRESS
(
  V_NAME IN VARCHAR2,
  V_NO OUT NUMBER
)
IS
  P_COUNT NUMBER;
BEGIN
  -- 查看数据库中是否有这个出版社
  SELECT COUNT(*)
  INTO P_COUNT
  FROM PRESS
  WHERE PRENAME = V_NAME;
  
  -- 如果没有，则插入，并返回出版社编号
  IF P_COUNT = 0 THEN
    INSERT INTO PRESS(PRENAME) VALUES(V_NAME);
    COMMIT;
    -- 返回出版社编号
    SELECT FIND_PRENO_BY_PRENAME(V_NAME) 
    INTO V_NO 
    FROM dual;
   -- 有这个出版社则直接返回出版社编号
   ELSE 
      SELECT FIND_PRENO_BY_PRENAME(V_NAME) 
      INTO V_NO 
      FROM dual;
  END IF;
END INSERT_INTO_PRESS;

9.4 作者的存储过程和函数

-- 计算每个作者写的图书的数量
CREATE OR REPLACE FUNCTION COUNT_AUTHOR_BOOK_NUMBER (V_NAME IN OUT VARCHAR2)
RETURN VARCHAR2
IS
  P_COUNT NUMBER;
  P_RESTEL VARCHAR2(50);
BEGIN
  SELECT COUNT(*) 
  INTO P_COUNT
  FROM BOOK_INFO 
  WHERE AUTNAME = V_NAME;
  
  IF P_COUNT = 0 THEN
    P_RESTEL := '这个作者没有写过书';
  ELSE
    P_RESTEL := V_NAME||'写了 '||TO_CHAR(P_COUNT)||' 本书';
  END IF;
  RETURN P_RESTEL;
END COUNT_AUTHOR_BOOK_NUMBER;

-- 通过作者名查询作者的信息
CREATE OR REPLACE PROCEDURE FIND_AUTHOR_INFO (V_NAME IN OUT VARCHAR2, V_COUNTRY OUT VARCHAR2)
IS
BEGIN
	SELECT COUNTRY 
	INTO V_COUNTRY
	FROM AUTHOR
	WHERE AUTNAME = V_NAME;
EXCEPTION
	WHEN NO_DATA_FOUND THEN
		V_COUNTRY := '查无此人';
END FIND_AUTHOR_INFO;

-- 通过作者名找作者的编号
CREATE OR REPLACE FUNCTION FIND_AUTNO_BY_AUTNAME(V_NAME IN VARCHAR2) 
RETURN NUMBER
IS
	P_AUTNO NUMBER;
BEGIN
	SELECT AUTNO 
	INTO P_AUTNO
	FROM AUTHOR
	WHERE AUTNAME = V_NAME;
	RETURN P_AUTNO;
EXCEPTION
	WHEN NO_DATA_FOUND THEN
		RETURN -1;
END FIND_AUTNO_BY_AUTNAME;

-- 向作者表中插入作者编号
CREATE OR REPLACE PROCEDURE INSERT_INTO_AUTHOR (V_NAME IN VARCHAR2, V_COUNTRY IN VARCHAR2,V_NO OUT NUMBER)
IS
  P_COUNT NUMBER;
BEGIN
  SELECT COUNT(*)
  INTO P_COUNT
  FROM AUTHOR
  WHERE AUTNAME = V_NAME;
  
  IF P_COUNT = 0 THEN
    INSERT INTO AUTHOR(AUTNAME,COUNTRY) VALUES(V_NAME,V_COUNTRY);

    SELECT FIND_AUTNO_BY_AUTNAME(V_NAME)
    INTO V_NO
      FROM dual;
  ELSE
        SELECT FIND_AUTNO_BY_AUTNAME(V_NAME)
        INTO V_NO
        FROM dual;
  END IF;
END INSERT_INTO_AUTHOR;

9.5 管理员的存储过程和函数

-- 注销管理员
CREATE OR REPLACE FUNCTION DELETE_ADMIN
(
  V_NO IN NUMBER
)
RETURN  VARCHAR2
IS
  P_COUNT NUMBER;
  P_RESTEL VARCHAR2(20);
BEGIN
  SELECT COUNT(*)
  INTO P_COUNT
  FROM ADMININF 
  WHERE ADMNO = V_NO;
  
  IF P_COUNT = 0 THEN
    P_RESTEL := '查无此人';
  ELSE 
    DELETE ADMININF WHERE ADMNO = V_NO;
    P_RESTEL := '注销成功';
  END IF;
  RETURN P_RESTEL;
END DELETE_ADMIN;

-- 查找管理员信息
CREATE OR REPLACE PROCEDURE FIND_ADMIN_INFO
(
P_VO IN OUT ADMININF.ADMNO%TYPE,
P_NAME OUT ADMININF.ADMNAME%TYPE,
P_TEL OUT ADMININF.ADMTEL%TYPE
) 
IS
BEGIN
  SELECT ADMNAME,ADMTEL
  INTO P_NAME,P_TEL
  FROM ADMININF
  WHERE ADMNO = P_VO;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    P_NAME := '查无此人';
		
		P_TEL := '查无此人';
  WHEN OTHERS THEN
    P_NAME := '查无此人';
		P_TEL := '查无此人';
END FIND_ADMIN_INFO;

-- 查询密码操作
CREATE OR REPLACE FUNCTION FIND_PASSWORD_BY_ADMNO (V_NO IN VARCHAR2)
RETURN VARCHAR2
IS
  V_PASSWORD VARCHAR2(20);
BEGIN
  SELECT ADMPASSWORD
  INTO V_PASSWORD
  FROM ADMININF
  WHERE ADMNO = V_NO;
  RETURN V_PASSWORD;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN '查无此人';
  WHEN OTHERS THEN
    RETURN '其他错误';
END FIND_PASSWORD_BY_ADMNO;

-- 注册操作
CREATE OR REPLACE FUNCTION INSERT_INTO_ADMIN 
(
  V_NO IN NUMBER, 
  V_NAME IN VARCHAR2,
  V_PASSWORD IN VARCHAR2,
  V_ANSWER IN VARCHAR2,
  V_TEL IN VARCHAR2
)
RETURN VARCHAR2
IS
  P_COUNT NUMBER;
  P_RESTEL VARCHAR2(20);
BEGIN
  SELECT COUNT(*) 
  INTO P_COUNT
  FROM ADMININF 
  WHERE ADMNO = V_NO;
  
  IF P_COUNT = 0 THEN
    INSERT INTO ADMININF 
    VALUES(
      V_NO,
      V_NAME,
      V_PASSWORD,
      '我最喜欢的歌手是？',
      V_ANSWER,
      V_TEL
    );
    COMMIT;
    P_RESTEL:= '注册成功！';
  ELSE 
    P_RESTEL:= '用户已经存在！';
  END IF;
  RETURN P_RESTEL;
END INSERT_INTO_ADMIN;

-- 更新密钥
CREATE OR REPLACE FUNCTION UPDATE_ADMIN_ANSWER
(
  V_NO IN NUMBER,
  V_PASSWORD IN VARCHAR2,
  V_NEW_ANSWER IN VARCHAR2
)
RETURN VARCHAR2
IS 
P_COUNT NUMBER;
P_RESTEL VARCHAR2(20);
BEGIN
  SELECT COUNT(*)
  INTO P_COUNT
  FROM ADMININF
  WHERE ADMNO = V_NO AND ADMPASSWORD = V_PASSWORD;
  
  IF P_COUNT = 0 THEN
    P_RESTEL := '查无此人';
  ELSE
    UPDATE ADMININF 
    SET ADMPASSWORDANSWER = V_NEW_ANSWER 
    WHERE ADMNO = V_NO;
    P_RESTEL := '修改密钥成功';
  END IF;
  RETURN P_RESTEL;
END UPDATE_ADMIN_ANSWER;

-- 更新资料
CREATE OR REPLACE FUNCTION UPDATE_ADMIN_DATA
(
  V_NO IN NUMBER, 
  V_NAME IN VARCHAR2,
  V_TEL IN VARCHAR2
)
RETURN VARCHAR2
IS
 P_COUNT NUMBER;
 P_RESTEL VARCHAR2(20);
BEGIN
  SELECT COUNT(*)
  INTO P_COUNT
  FROM ADMININF
  WHERE ADMNO = V_NO;
  
  IF P_COUNT = 0 THEN
    P_RESTEL := '查无此人';
  ELSE 
    UPDATE ADMININF 
    SET ADMNAME = V_NAME,ADMTEL = V_TEL
    WHERE ADMNO = V_NO;
    P_RESTEL := '更新资料成功';
  END IF;
  RETURN P_RESTEL;
END UPDATE_ADMIN_DATA;

-- 更新密码
CREATE OR REPLACE FUNCTION UPDATE_ADMIN_PASSWORD
(
V_NO IN NUMBER,
V_ANSWER IN VARCHAR2,
V_NEW_PASSWORD VARCHAR2
)
RETURN VARCHAR2
IS 
 P_COUNT NUMBER;
 P_RESTEL VARCHAR2(20); 
BEGIN
  SELECT COUNT(*)
  INTO P_COUNT
  FROM ADMININF
  WHERE ADMNO = V_NO AND ADMPASSWORDANSWER = V_ANSWER;
  
  IF P_COUNT = 0 THEN
    P_RESTEL := '查无此人';
  ELSE 
    UPDATE ADMININF 
    SET ADMPASSWORD = V_NEW_PASSWORD
    WHERE ADMNO = V_NO;
    
    P_RESTEL := '修改密码成功';
  END IF;
  RETURN P_RESTEL;
END UPDATE_ADMIN_PASSWORD;

9.6 班级的存储过程和函数

-- 统计班级的总数
CREATE OR REPLACE FUNCTION FIND_CLASS_COUNT
RETURN NUMBER
IS 
	P_COUNT NUMBER;
BEGIN
	SELECT COUNT(*)
	INTO P_COUNT
	FROM (SELECT DISTINCT CLASSNAME FROM CLASS);
	RETURN P_COUNT;
END FIND_CLASS_COUNT;

9.7 借书 / 还书 记录的存储过程和函数

-- 向借书记录表中插入数据
CREATE OR REPLACE PROCEDURE INSERT_INTO_BORROW
(
	V_STUNO IN NUMBER,
	V_BOOKNO IN NUMBER,
	V_ADMNO IN NUMBER,
	V_BORROWTIME IN BORROWRECORD.BORROWTIME%TYPE
)
IS
BEGIN
	INSERT INTO BORROWRECORD
	(
	STUNO,
	BOOKNO,
	ADMINNO,
	ISRETURN,
	BORROWTIME,
	BORROWDAY
	)
	VALUES(V_STUNO,V_BOOKNO,V_ADMNO,0,V_BORROWTIME,4);
END INSERT_INTO_BORROW;

-- 向还书记录表中插入数据
CREATE OR REPLACE PROCEDURE INSERT_INTO_RETURN 
(
	V_STUNO IN NUMBER,
	V_BOOKNO IN NUMBER,
	V_ADMNO IN NUMBER,
	V_ISOVERDUE IN NUMBER,
	V_RETURNTIME IN RETURNRECORD.RETURNTIME%TYPE
)
IS
BEGIN
	INSERT INTO RETURNRECORD
	(
		STUNO,
		BOOKNO,
		ADMINNO,
		ISOVERDUE,
		RETURNTIME
	)
	VALUES(V_STUNO,V_BOOKNO,V_ADMNO,V_ISOVERDUE,V_RETURNTIME);
END INSERT_INTO_RETURN;

存储过程返回多行多列:例子

-- 创建管理员信息类型
CREATE OR REPLACE TYPE ADMIN_INFO_TYPE AS object(
  ADMNO NUMBER,
  ADMNAME VARCHAR2(20)
);

-- 创建管理员的表的类型
CREATE OR REPLACE type ADMIN_INFO_TABLE AS TABLE OF ADMIN_INFO_TYPE;

CREATE OR REPLACE function FIND_ADMIN_DATA(V_ADMNO IN NUMBER)
RETURN ADMIN_INFO_TABLE PIPELINED
is
ADMIN_INFO_TYPE ADMIN_INFO_TABLE := ADMIN_INFO_TABLE();
-- 定义游标
cursor cur IS SELECT ADMNO,ADMNAME FROM ADMININF WHERE ADMNO = V_ADMNO;
BEGIN
FOR c IN cur LOOP
	pipe row(t_test(c.empno, c.ename, c.job, c.sal));
end loop;
return;
end;


create or replace type t_test as object(
  EMPNO    NUMBER(4),
  ENAME    VARCHAR2(10),
  JOB      VARCHAR2(9),
  SAL      NUMBER(7,2)
);

create or replace type t_test_table as table of t_test;

create or replace function f_test_pipe(v_deptno in number default null)
return t_test_table PIPELINED
is
v_test t_test_table := t_test_table();
cursor cur is select empno, ename, job, sal from emp where deptno = v_deptno;
begin
for c in cur loop
pipe row(t_test(c.empno, c.ename, c.job, c.sal));
end loop;
return;
end;

5. 系统分析

5.1 系统特色

1. 界面统一
2. 有不同的用户，区分角色
3. 借阅图书不能借阅同一本图书多次，不能借阅库存量为 0 的图书
4. 文档内容详细
5. 约束详细，结构清晰

5.2 系统不足

1. 没有实现增加新的图书的操作
2. 没有实现罚款的操作

项目地址

https://github.com/83start/Oracle-Library.git