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-11)(C:\Users\CJ\AppData\Roaming\Typora\typora-user-images\17.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 MINEXTENTS 1 MAXEXTENTS 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 MINEXTENTS 1 MAXEXTENTS 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 MINEXTENTS 1 MAXEXTENTS 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 MINEXTENTS 1 MAXEXTENTS 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 MINEXTENTS 1 MAXEXTENTS 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 MINEXTENTS 1 MAXEXTENTS 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 MINEXTENTS 1 MAXEXTENTS 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 MINEXTENTS 1 MAXEXTENTS 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 MINEXTENTS 1 MAXEXTENTS 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 maxvalue start with 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 maxvalue start with 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 = ; -- 2.登录操作 -- 根据账号查询用户的密码 SELECT STUPASSWORD FROM STUDENT WHERE STUNO = ; -- 3..注册操作 -- ①.向表中插入输入数据 INSERT INTO STUDENT VALUES(,'邓紫棋','女','26','人工智能2001','086677X','ysyhl9t','我最喜欢看的电影？','敢死队',''); -- ②.验证 SELECT * FROM STUDENT WHERE STUNO = ; -- 4.修改密码 -- ①.根据学生的账号 和 密码问题找答案 SELECT STUPASSWORDANSWER FROM STUDENT WHERE STUNO = AND STUPASSWORDQUESTION = '我最喜欢看的电影？'; --②.修改密码操作 UPDATE STUDENT SET STUPASSWORD = 'cqmyg' WHERE STUNO = ; -- ③.验证 SELECT * FROM STUDENT WHERE STUNO = ; -- 5.修改密码问题答案 -- ①.根据账号和问题来修改答案 UPDATE STUDENT SET STUPASSWORDANSWER = '周延 G.A.Y' WHERE STUNO = AND STUPASSWORDQUESTION = '我最喜欢看的电影？'; -- ②.验证 SELECT * FROM STUDENT WHERE STUNO = ; -- 6.修改学生的资料 -- ①根据学号去修改学生的信息 UPDATE STUDENT SET STUNAME = '陶喆' , SEX = '男', AGE = '21' ,CLASSNAME = '人工智能2003', STUPASSWORDANSWER = '周延 G.A.Y',STUTEL = '' WHERE STUNO = ; -- ②验证 SELECT * FROM STUDENT WHERE STUNO = ; -- 7.注销学生账号操作 -- ①.查询是否有这个学生 SELECT STUNO FROM STUDENT WHERE STUNO = ; -- ②.删除指定的用户账号 DELETE FROM STUDENT WHERE STUNO = ; -- ③.验证 SELECT STUNO FROM STUDENT WHERE STUNO = ; 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 = ; -- 2.管理员的登 -- 根据用户名查询密码 SELECT ADMPASSWORD FROM ADMININF WHERE ADMNO = ; -- 3.注册操作 -- ①.查看是否有这个管理员 SELECT * FROM ADMININF WHERE ADMNO = ; -- ①.注册管理员 INSERT INTO ADMININF VALUES (,'周杰伦','ysyhl9t','我最喜欢的歌手是？','李荣浩'); -- ②.验证 SELECT * FROM ADMININF WHERE ADMNO = ; -- 4.修改密码 -- ①.根据管理员的账号 和 密码问题找答案 SELECT ADMNO,ADMPASSWORDQUESTION, ADMPASSWORDANSWER FROM ADMININF WHERE ADMNO = AND ADMPASSWORDQUESTION = '我最喜欢的歌手是？'; -- ②.修改密码操作 UPDATE ADMININF SET ADMPASSWORD = 'cqmyg' WHERE ADMNO = ; -- ③.验证 SELECT ADMNO,ADMPASSWORD FROM ADMININF WHERE ADMNO = ; -- 5.修改管理员的密码答案 -- ①.根据管理员的账号和密码问题来来修改密码答案 UPDATE ADMININF SET ADMPASSWORDANSWER = '邓紫棋' WHERE ADMNO = AND ADMPASSWORDQUESTION = '我最喜欢的歌手是？'; -- ②.验证信息 SELECT ADMNO,ADMPASSWORDQUESTION,ADMPASSWORDANSWER FROM ADMININF WHERE ADMNO = ; -- 6.修改管理员的资料 -- ①.根据账号去修改管理员的信息 UPDATE ADMININF SET ADMNAME = '李白' WHERE ADMNO = ; -- ②.验证 SELECT * FROM ADMININF WHERE ADMNO = ; -- 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 = ; -- ②.删除管理员 DELETE FROM ADMININF WHERE ADMNO = ; -- ③.验证 SELECT ADMNO FROM ADMININF WHERE ADMNO = ;
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 系统特色
- 界面统一
- 有不同的用户，区分角色
- 借阅图书不能借阅同一本图书多次，不能借阅库存量为 0 的图书
- 文档内容详细
- 约束详细，结构清晰
5.2 系统不足
- 没有实现增加新的图书的操作
- 没有实现罚款的操作
项目地址
https://github.com/83start/Oracle-Library.git
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请联系我们，一经查实，本站将立刻删除。
如需转载请保留出处：https://51itzy.com/kjqy/56565.html