学生宿舍管理系统oracle

用户表:编号，用户名，密码，邮箱，角色，用户头像；

影片表：编号，影片名称，国外名称，演职人员，导演，电影详情，电影时长，电影类型，电影评分，票房，电影参评人数，上映时间，制片地区，电影海报地址，电影状态；

影院表：编号，影院名称，影院地址；

放映厅表：编号，放映厅名称，放映厅容量，所属影院编号；

订单表：编号，所属用户编号，所属场次编号，电影票座位信息，订单状态，订单价格，订单支付时间，所属用户对象；

订单详情表：编号，所属放映厅，放映的电影编号，电影放映时间，售价，剩余座位数，场次状态，所属放映厅对象，放映的电影；

评论表：编号，所属用户编号，评论内容，所属电影编号，评论时间，所属用户，评分；

影院票务管理系统

姓名	主要完成的工作	备注

1. 需求分析

1.1. 用户管理模块

在整体设计中，我们将宿舍管理系统分为五个大的功能：系统管理功能、公寓管理功能、信息查询功能、出入登记功能、信息修改功能。下面将具体进行介绍每个功能。

(1)系统管理功能

系统管理功能包括：用户登录、用户密码修改、用户管理、退出系统五个部分。

(2)公寓管理功能

公寓管理功能包括：楼房管理、宿舍管理、员工管理三个大的部分。

(3)信息查询功能

信息查询功能基本上包括：按学号进行查询、按姓名进行查询、按班级查询、按寝室号查询、每栋楼房住宿情况查询五个部分。

(4)出入登记功能

出入登记功能包括：进楼物品登记、出楼物品登记、人员来访登记、结束访问登记四个功能部分。

(5)信息修改功能

信息修改功能包括：学生基本信息修改、员工基本信息修改等。除了以上项目外，整个系统还包括登陆管理，即根据不同人员的身份合法性认证，使其进行相应的操作。人员分普通用户、管理员。

2. 系统设计

2.1. 系统结构功能图

根据需求分析，确定系统结构功能如图2-1所示：

图2-1 系统功能结构图

2.2. 功能流程图

系统最主要的功能就是管理员登录之后对各种功能的使用。管理员功能如图4-2所示。

图2-2 管理员登录管理流程

2.3. 数据库设计

整体功能的E-R图如图2-4所示。

图2-4 系统E-R图

本系统包含的数据表如下：

1**书架类型表** (shelf_types)

列名	数据类型	字段类型	长度	是否为空	默认值	备注
shelf_type_id	NUMBER	主键	-	否	-	书架类型ID
shelf_type_name	VARCHAR2	非空	50	否	-	书架类型名称

2**读者类型表** (reader_types)

列名	数据类型	字段类型	长度	是否为空	默认值	备注
reader_type_id	NUMBER	主键	-	否	-	读者类型ID
reader_type_name	VARCHAR2	非空	50	否	-	读者类型名称

3**书架表** (shelves)

列名	数据类型	字段类型	长度	是否为空	默认值	备注
shelf_id	NUMBER	主键	-	否	-	书架ID
shelf_number	VARCHAR2	非空	20	否	-	书架编号
shelf_type_id	NUMBER	外键且非空	-	否	-	书架类型ID

4**工作人员表** (staff)

列名	数据类型	字段类型	长度	是否为空	默认值	备注
staff_id	NUMBER	主键	-	否	-	工作人员ID
staff_name	VARCHAR2	非空	100	否	-	工作人员姓名
staff_position	VARCHAR2	非空	50	否	-	工作人员职位

5**图书表** (books)

列名	数据类型	字段类型	长度	是否为空	默认值	备注
book_id	NUMBER	主键	-	否	-	图书ID
title	VARCHAR2	非空	200	否	-	图书标题
author	VARCHAR2	非空	100	否	-	图书作者
isbn	VARCHAR2	唯一且非空	20	否	-	图书国际标准书号
publication_year	NUMBER	可空	-	是	-	出版年份
shelf_id	NUMBER	外键且非空	-	否	-	书架ID

6**读者表** (readers)

列名	数据类型	字段类型	长度	是否为空	默认值	备注
reader_id	NUMBER	主键	-	否	-	读者ID
reader_name	VARCHAR2	非空	100	否	-	读者姓名
reader_type_id	NUMBER	外键且非空	-	否	-	读者类型ID
contact_phone	VARCHAR2	可空	15	是	-	联系电话

7**正借阅表** (loans)

列名	数据类型	字段类型	长度	是否为空	默认值	备注
loan_id	NUMBER	主键	-	否	-	借阅记录ID
book_id	NUMBER	外键且非空	-	否	-	图书ID
reader_id	NUMBER	外键且非空	-	否	-	读者ID
loan_date	DATE	非空	-	否	-	借书日期
due_date	DATE	非空	-	否	-	应还日期

8**已还书籍表** (returns)

列名	数据类型	字段类型	长度	是否为空	默认值	备注
return_id	NUMBER	主键	-	否	-	还书记录ID
loan_id	NUMBER	外键	-	是	-	借阅记录ID
return_date	DATE	非空	-	否	-	还书日期

9**通知记录表（overdue_notifications）**

列名	数据类型	字段类型	长度	是否为空	默认值	备注
notification_id	NUMBER	主键	-	否	-	通知记录
loan_id	NUMBER	外键	-	否	-	借阅记录 ID
reader_id	NUMBER	外键	-	否	-	读者 ID
notification_date	DATE	日期	-	是	SYSDATE	通知日期
message	VARCHAR2	字符串	500	是	-	通知内容

10**历史记录表（book_location_history）**

列名	数据类型	字段类型	长度	是否为空	默认值	备注
history_id	NUMBER	主键	-	否	-	历史记录
book_id	NUMBER	外键	-	否	-	图书 ID
old_shelf_id	NUMBER	外键	-	是	-	旧书架 ID
new_shelf_id	NUMBER	外键	-	否	-	新书架 ID
change_date	DATE	日期	-	是	SYSDATE	变更日期
changed_by	NUMBER	外键	-	是	-	操作人 ID

3. SQL实现

3.1. 建表

-- 1. 用户表（系统管理）
CREATE TABLE system_users (
    user_id VARCHAR2(20) PRIMARY KEY,
    password VARCHAR2(100) NOT NULL,
    role VARCHAR2(10) NOT NULL CHECK (role IN ('管理员', '普通用户')),
    last_login DATE
);

-- 2. 公寓楼表（公寓管理）
CREATE TABLE buildings (
    building_id VARCHAR2(10) PRIMARY KEY,
    building_name VARCHAR2(50) NOT NULL,
    manager_id VARCHAR2(20),
    total_rooms NUMBER DEFAULT 0
);

-- 3. 宿舍房间表（公寓管理）
CREATE TABLE dorm_rooms (
    room_id VARCHAR2(15) PRIMARY KEY,
    building_id VARCHAR2(10) NOT NULL REFERENCES buildings(building_id),
    room_type VARCHAR2(20),
    capacity NUMBER DEFAULT 4,
    current_count NUMBER DEFAULT 0
);

-- 4. 员工表（公寓管理）
CREATE TABLE staff (
    staff_id VARCHAR2(20) PRIMARY KEY,
    name VARCHAR2(50) NOT NULL,
    position VARCHAR2(30),
    contact VARCHAR2(30),
    building_id VARCHAR2(10) REFERENCES buildings(building_id)
);

-- 5. 学生表（信息查询/修改）
CREATE TABLE students (
    student_id VARCHAR2(20) PRIMARY KEY,
    name VARCHAR2(50) NOT NULL,
    gender CHAR(1) CHECK (gender IN ('M', 'F')),
    class_name VARCHAR2(50),
    room_id VARCHAR2(15) REFERENCES dorm_rooms(room_id)
);

-- 6. 物品登记表（出入登记）
CREATE TABLE item_registrations (
    reg_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    student_id VARCHAR2(20) REFERENCES students(student_id),
    item_name VARCHAR2(100) NOT NULL,
    quantity NUMBER DEFAULT 1,
    direction VARCHAR2(10) CHECK (direction IN ('进楼', '出楼')),
    reg_time TIMESTAMP DEFAULT SYSTIMESTAMP,
    staff_id VARCHAR2(20) REFERENCES staff(staff_id)
);

-- 7. 访客登记表（出入登记）
CREATE TABLE visitor_registrations (
    visit_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    visitor_name VARCHAR2(50) NOT NULL,
    student_id VARCHAR2(20) REFERENCES students(student_id),
    building_id VARCHAR2(10) REFERENCES buildings(building_id),
    enter_time TIMESTAMP DEFAULT SYSTIMESTAMP,
    leave_time TIMESTAMP,
    staff_id VARCHAR2(20) REFERENCES staff(staff_id)
);

-- 8. 操作日志表（系统管理）
CREATE TABLE system_log (
    log_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id VARCHAR2(20) REFERENCES system_users(user_id),
    operation VARCHAR2(100) NOT NULL,
    op_time TIMESTAMP DEFAULT SYSTIMESTAMP
);

3.2. 索引

-- 1. 用户表索引
CREATE INDEX idx_user_role ON system_users(role);

-- 2. 公寓楼表索引
CREATE INDEX idx_building_manager ON buildings(manager_id);

-- 3. 宿舍房间表索引
CREATE INDEX idx_dorm_room_building ON dorm_rooms(building_id);
CREATE INDEX idx_dorm_room_capacity ON dorm_rooms(capacity);

-- 4. 员工表索引
CREATE INDEX idx_staff_building ON staff(building_id);

-- 5. 学生表索引
CREATE INDEX idx_student_gender ON students(gender);
CREATE INDEX idx_student_class ON students(class_name);
CREATE INDEX idx_student_room ON students(room_id);

-- 6. 物品登记表索引
CREATE INDEX idx_item_registration_student ON item_registrations(student_id);
CREATE INDEX idx_item_registration_direction ON item_registrations(direction);

-- 7. 访客登记表索引
CREATE INDEX idx_visitor_registration_student ON visitor_registrations(student_id);
CREATE INDEX idx_visitor_registration_building ON visitor_registrations(building_id);
CREATE INDEX idx_visitor_registration_enter_time ON visitor_registrations(enter_time);

-- 8. 操作日志表索引
CREATE INDEX idx_system_log_user ON system_log(user_id);
CREATE INDEX idx_system_log_time ON system_log(op_time);

3.3. 视图

-- 1. 学生住宿详情视图
CREATE VIEW vw_student_dorm_details AS
SELECT 
    s.student_id, 
    s.name AS student_name,
    s.gender,
    s.class_name,
    d.room_id,
    b.building_name,
    d.room_type,
    d.capacity,
    d.current_count,
    (d.capacity - d.current_count) AS available_beds
FROM students s
JOIN dorm_rooms d ON s.room_id = d.room_id
JOIN buildings b ON d.building_id = b.building_id;

-- 2. 楼栋住宿统计视图
CREATE VIEW vw_building_occupancy AS
SELECT 
    b.building_id,
    b.building_name,
    COUNT(DISTINCT d.room_id) AS total_rooms,
    SUM(d.capacity) AS total_capacity,
    SUM(d.current_count) AS current_occupancy,
    ROUND(SUM(d.current_count) / SUM(d.capacity) * 100, 2) AS occupancy_rate,
    st.name AS manager_name
FROM buildings b
JOIN dorm_rooms d ON b.building_id = d.building_id
LEFT JOIN staff st ON b.manager_id = st.staff_id
GROUP BY b.building_id, b.building_name, st.name;

— 3. 访客登记详情视图
CREATE OR REPLACE VIEW vw_visitor_records AS
SELECT
v.visit_id,
v.visitor_name,
s.student_id,
s.name AS student_name,
b.building_name,
v.enter_time,
v.leave_time,
st.name AS reception_staff,
— 使用SYSDATE简化计算
ROUND(
(
COALESCE(CAST(v.leave_time AS DATE), SYSDATE)

        - 
        CAST(v.enter_time AS DATE)
    ) * 24 * 60
) AS duration_minutes,
CASE 
    WHEN v.leave_time IS NULL THEN '访问中'
    ELSE '已离开'
END AS visit_status

FROM visitor_registrations v
JOIN students s ON v.student_id = s.student_id
JOIN buildings b ON v.building_id = b.building_id
JOIN staff st ON v.staff_id = st.staff_id;

— 4. 物品进出记录视图
CREATE VIEW system_log_info AS
SELECT
sl.log_id,
u.user_id,
u.role,
sl.operation,
sl.op_time
FROM
system_log sl
JOIN
system_users u ON sl.user_id = u.user_id;

— 5. 宿舍空位视图
CREATE VIEW vw_available_beds AS
SELECT
d.room_id,
b.building_name,
d.room_type,
d.capacity,
d.current_count,
(d.capacity - d.current_count) AS available_beds
FROM dorm_rooms d
JOIN buildings b ON d.building_id = b.building_id
WHERE d.current_count < d.capacity;

— 6. 员工负责楼栋视图
CREATE VIEW vw_staff_responsibilities AS
SELECT
s.staff_id,
s.name AS staff_name,
s.position,
s.contact,
b.building_id,
b.building_name,
COUNT(d.room_id) AS rooms_managed
FROM staff s
LEFT JOIN buildings b ON s.building_id = b.building_id
LEFT JOIN dorm_rooms d ON b.building_id = d.building_id
GROUP BY s.staff_id, s.name, s.position, s.contact, b.building_id, b.building_name;

## 存储过程、函数
```sql
-- 1. 用户管理相关

--# 1.1 用户登录验证函数

CREATE OR REPLACE FUNCTION fn_user_login(
    p_user_id IN VARCHAR2,
    p_password IN VARCHAR2
) RETURN VARCHAR2
IS
    v_role VARCHAR2(10);
BEGIN
    SELECT role INTO v_role
    FROM system_users
    WHERE user_id = p_user_id AND password = p_password;
    
    RETURN v_role;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN 'FAILURE';
END;
/


--# 1.2 修改密码存储过程

CREATE OR REPLACE PROCEDURE sp_change_password(
    p_user_id IN VARCHAR2,
    p_old_password IN VARCHAR2,
    p_new_password IN VARCHAR2
)
IS
BEGIN
    UPDATE system_users
    SET password = p_new_password
    WHERE user_id = p_user_id AND password = p_old_password;
    
    IF SQL%ROWCOUNT = 0 THEN
        RAISE_APPLICATION_ERROR(-20001, '原密码错误或用户不存在');
    END IF;
END;
/


-- 2. 公寓管理相关

--# 2.1 添加新公寓楼存储过程

CREATE OR REPLACE PROCEDURE sp_add_building(
    p_building_id IN VARCHAR2,
    p_building_name IN VARCHAR2
)
IS
BEGIN
    INSERT INTO buildings (building_id, building_name)
    VALUES (p_building_id, p_building_name);
    
    -- 记录操作日志
    sp_log_operation('system', '添加新公寓楼: ' || p_building_name);
END;
/


--# 2.2 分配楼管员存储过程

CREATE OR REPLACE PROCEDURE sp_assign_building_manager(
    p_building_id IN VARCHAR2,
    p_staff_id IN VARCHAR2
)
IS
    v_manager_exists NUMBER;
BEGIN
    -- 检查员工是否存在
    SELECT COUNT(*) INTO v_manager_exists
    FROM staff
    WHERE staff_id = p_staff_id;
    
    IF v_manager_exists = 0 THEN
        RAISE_APPLICATION_ERROR(-20002, '员工不存在');
    END IF;
    
    -- 更新楼栋管理员
    UPDATE buildings
    SET manager_id = p_staff_id
    WHERE building_id = p_building_id;
    
    IF SQL%ROWCOUNT = 0 THEN
        RAISE_APPLICATION_ERROR(-20003, '楼栋不存在');
    END IF;
END;
/


-- 3. 宿舍管理相关

--# 3.1 添加宿舍房间存储过程

CREATE OR REPLACE PROCEDURE sp_add_dorm_room(
    p_room_id IN VARCHAR2,
    p_building_id IN VARCHAR2,
    p_capacity IN NUMBER DEFAULT 4,
    p_room_type IN VARCHAR2 DEFAULT '标准间'
)
IS
BEGIN
    INSERT INTO dorm_rooms (room_id, building_id, room_type, capacity)
    VALUES (p_room_id, p_building_id, p_room_type, p_capacity);
    
    -- 更新楼栋房间总数
    UPDATE buildings
    SET total_rooms = total_rooms + 1
    WHERE building_id = p_building_id;
END;
/


--# 3.2 分配学生到宿舍存储过程

CREATE OR REPLACE PROCEDURE sp_assign_student_to_room(
    p_student_id IN VARCHAR2,
    p_room_id IN VARCHAR2
)
IS
    v_available_beds NUMBER;
BEGIN
    -- 检查宿舍是否有空位
    SELECT available_beds INTO v_available_beds
    FROM vw_available_beds
    WHERE room_id = p_room_id;
    
    IF v_available_beds <= 0 THEN
        RAISE_APPLICATION_ERROR(-20004, '该宿舍已满员');
    END IF;
    
    -- 分配宿舍
    UPDATE students
    SET room_id = p_room_id
    WHERE student_id = p_student_id;
    
    -- 更新宿舍当前人数
    UPDATE dorm_rooms
    SET current_count = current_count + 1
    WHERE room_id = p_room_id;
END;
/


-- 4. 出入登记相关

--# 4.1 访客登记存储过程

CREATE OR REPLACE PROCEDURE sp_register_visitor(
    p_visitor_name IN VARCHAR2,
    p_student_id IN VARCHAR2,
    p_building_id IN VARCHAR2,
    p_staff_id IN VARCHAR2
)
IS
BEGIN
    INSERT INTO visitor_registrations (visitor_name, student_id, building_id, staff_id)
    VALUES (p_visitor_name, p_student_id, p_building_id, p_staff_id);
END;
/


--# 4.2 结束访问登记存储过程

CREATE OR REPLACE PROCEDURE sp_end_visit(
    p_visit_id IN NUMBER
)
IS
BEGIN
    UPDATE visitor_registrations
    SET leave_time = SYSTIMESTAMP
    WHERE visit_id = p_visit_id;
    
    IF SQL%ROWCOUNT = 0 THEN
        RAISE_APPLICATION_ERROR(-20005, '访问记录不存在');
    END IF;
END;
/


--# 4.3 物品进出登记存储过程

CREATE OR REPLACE PROCEDURE sp_register_item(
    p_student_id IN VARCHAR2,
    p_item_name IN VARCHAR2,
    p_direction IN VARCHAR2,
    p_staff_id IN VARCHAR2,
    p_quantity IN NUMBER DEFAULT 1
)
IS
BEGIN
    INSERT INTO item_registrations (student_id, item_name, direction, staff_id, quantity)
    VALUES (p_student_id, p_item_name, p_direction, p_staff_id, p_quantity);
END;
/


-- 5. 信息查询相关函数

--# 5.1 查询学生住宿信息函数

CREATE OR REPLACE FUNCTION fn_get_student_info(
    p_search_key IN VARCHAR2,
    p_search_type IN VARCHAR2 -- 'ID', 'NAME', 'CLASS', 'ROOM'
) RETURN SYS_REFCURSOR
IS
    v_cursor SYS_REFCURSOR;
BEGIN
    CASE p_search_type
        WHEN 'ID' THEN
            OPEN v_cursor FOR
                SELECT * FROM vw_student_dorm_details
                WHERE student_id = p_search_key;
                
        WHEN 'NAME' THEN
            OPEN v_cursor FOR
                SELECT * FROM vw_student_dorm_details
                WHERE student_name LIKE '%' || p_search_key || '%';
                
        WHEN 'CLASS' THEN
            OPEN v_cursor FOR
                SELECT * FROM vw_student_dorm_details
                WHERE class_name = p_search_key;
                
        WHEN 'ROOM' THEN
            OPEN v_cursor FOR
                SELECT * FROM vw_student_dorm_details
                WHERE room_id = p_search_key;
                
        ELSE
            RAISE_APPLICATION_ERROR(-20006, '无效的查询类型');
    END CASE;
    
    RETURN v_cursor;
END;
/


--# 5.2 获取楼栋入住率函数

CREATE OR REPLACE FUNCTION fn_get_building_occupancy(
    p_building_id IN VARCHAR2
) RETURN NUMBER
IS
    v_occupancy_rate NUMBER;
BEGIN
    SELECT occupancy_rate INTO v_occupancy_rate
    FROM vw_building_occupancy
    WHERE building_id = p_building_id;
    
    RETURN v_occupancy_rate;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN 0;
END;
/


-- 6. 信息修改相关

--# 6.1 修改学生基本信息存储过程

CREATE OR REPLACE PROCEDURE sp_update_student_info(
    p_student_id IN VARCHAR2,
    p_name IN VARCHAR2 DEFAULT NULL,
    p_gender IN CHAR DEFAULT NULL,
    p_class_name IN VARCHAR2 DEFAULT NULL,
    p_room_id IN VARCHAR2 DEFAULT NULL
)
IS
BEGIN
    UPDATE students
    SET 
        name = COALESCE(p_name, name),
        gender = COALESCE(p_gender, gender),
        class_name = COALESCE(p_class_name, class_name),
        room_id = COALESCE(p_room_id, room_id)
    WHERE student_id = p_student_id;
    
    IF SQL%ROWCOUNT = 0 THEN
        RAISE_APPLICATION_ERROR(-20007, '学生不存在');
    END IF;
END;
/


--# 6.2 修改员工信息存储过程

CREATE OR REPLACE PROCEDURE sp_update_staff_info(
    p_staff_id IN VARCHAR2,
    p_name IN VARCHAR2 DEFAULT NULL,
    p_position IN VARCHAR2 DEFAULT NULL,
    p_contact IN VARCHAR2 DEFAULT NULL,
    p_building_id IN VARCHAR2 DEFAULT NULL
)
IS
BEGIN
    UPDATE staff
    SET 
        name = COALESCE(p_name, name),
        position = COALESCE(p_position, position),
        contact = COALESCE(p_contact, contact),
        building_id = COALESCE(p_building_id, building_id)
    WHERE staff_id = p_staff_id;
    
    IF SQL%ROWCOUNT = 0 THEN
        RAISE_APPLICATION_ERROR(-20008, '员工不存在');
    END IF;
END;
/


-- 7. 系统管理相关

--# 7.1 记录操作日志存储过程

CREATE OR REPLACE PROCEDURE sp_log_operation(
    p_user_id IN VARCHAR2,
    p_operation IN VARCHAR2
)
IS
BEGIN
    INSERT INTO system_log (user_id, operation)
    VALUES (p_user_id, p_operation);
END;
/


--# 7.2 获取用户最后登录时间函数

CREATE OR REPLACE FUNCTION fn_get_last_login(
    p_user_id IN VARCHAR2
) RETURN DATE
IS
    v_last_login DATE;
BEGIN
    SELECT last_login INTO v_last_login
    FROM system_users
    WHERE user_id = p_user_id;
    
    RETURN v_last_login;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN NULL;
END;
/

3.4. 触发器

-- 1. 宿舍人数自动维护触发器

-- 当学生分配到宿舍时，自动增加宿舍当前人数
CREATE OR REPLACE TRIGGER trg_student_room_assign
AFTER INSERT OR UPDATE OF room_id ON students
FOR EACH ROW
DECLARE
    v_old_room_id VARCHAR2(15);
BEGIN
    -- 处理房间变更
    IF :NEW.room_id IS NOT NULL THEN
        -- 更新新宿舍人数
        UPDATE dorm_rooms
        SET current_count = current_count + 1
        WHERE room_id = :NEW.room_id;
    END IF;
    
    -- 处理旧房间释放
    IF :OLD.room_id IS NOT NULL AND (:OLD.room_id != :NEW.room_id OR :NEW.room_id IS NULL) THEN
        -- 更新旧宿舍人数
        UPDATE dorm_rooms
        SET current_count = current_count - 1
        WHERE room_id = :OLD.room_id;
    END IF;
END;
/


-- 2. 楼栋房间数自动统计触发器

-- 当宿舍房间增删时，自动更新楼栋总房间数
CREATE OR REPLACE TRIGGER trg_building_room_count
AFTER INSERT OR DELETE ON dorm_rooms
FOR EACH ROW
BEGIN
    IF INSERTING THEN
        -- 新增房间
        UPDATE buildings
        SET total_rooms = total_rooms + 1
        WHERE building_id = :NEW.building_id;
    ELSIF DELETING THEN
        -- 删除房间
        UPDATE buildings
        SET total_rooms = total_rooms - 1
        WHERE building_id = :OLD.building_id;
    END IF;
END;
/


-- 3. 访客离开时间验证触发器

-- 确保离开时间不早于进入时间
CREATE OR REPLACE TRIGGER trg_visit_time_check
BEFORE UPDATE OF leave_time ON visitor_registrations
FOR EACH ROW
BEGIN
    IF :NEW.leave_time < :OLD.enter_time THEN
        RAISE_APPLICATION_ERROR(-20010, '离开时间不能早于进入时间');
    END IF;
END;
/


-- 4. 密码修改审计触发器

-- 记录密码修改操作
CREATE OR REPLACE TRIGGER trg_password_change_audit
AFTER UPDATE OF password ON system_users
FOR EACH ROW
BEGIN
    INSERT INTO system_log (user_id, operation)
    VALUES (:OLD.user_id, '修改密码');
END;
/


-- 5. 学生住宿容量检查触发器

-- 防止宿舍超员分配
CREATE OR REPLACE TRIGGER trg_room_capacity_check
BEFORE INSERT OR UPDATE OF room_id ON students
FOR EACH ROW
DECLARE
    v_current_count NUMBER;
    v_capacity NUMBER;
BEGIN
    IF :NEW.room_id IS NOT NULL THEN
        SELECT current_count, capacity 
        INTO v_current_count, v_capacity
        FROM dorm_rooms
        WHERE room_id = :NEW.room_id;
        
        IF v_current_count >= v_capacity THEN
            RAISE_APPLICATION_ERROR(-20011, '宿舍' || :NEW.room_id || '已满员');
        END IF;
    END IF;
END;
/


-- 6. 员工楼栋分配验证触发器

-- 确保员工分配的楼栋存在
CREATE OR REPLACE TRIGGER trg_staff_building_check
BEFORE INSERT OR UPDATE OF building_id ON staff
FOR EACH ROW
DECLARE
    v_building_exists NUMBER;
BEGIN
    IF :NEW.building_id IS NOT NULL THEN
        SELECT COUNT(*)
        INTO v_building_exists
        FROM buildings
        WHERE building_id = :NEW.building_id;
        
        IF v_building_exists = 0 THEN
            RAISE_APPLICATION_ERROR(-20012, '楼栋' || :NEW.building_id || '不存在');
        END IF;
    END IF;
END;
/


-- 7. 自动记录用户登录时间触发器

-- 登录时更新最后登录时间
CREATE OR REPLACE TRIGGER trg_update_login_time
AFTER UPDATE OF last_login ON system_users
FOR EACH ROW
BEGIN
    -- 记录登录操作
    INSERT INTO system_log (user_id, operation)
    VALUES (:OLD.user_id, '用户登录');
END;
/


-- 8. 物品进出登记验证触发器

-- 确保出楼物品有对应的进楼记录
CREATE OR REPLACE TRIGGER trg_item_direction_check
BEFORE INSERT ON item_registrations
FOR EACH ROW
DECLARE
    v_in_count NUMBER;
BEGIN
    IF :NEW.direction = '出楼' THEN
        SELECT COUNT(*)
        INTO v_in_count
        FROM item_registrations
        WHERE student_id = :NEW.student_id
          AND item_name = :NEW.item_name
          AND direction = '进楼'
          AND reg_time < :NEW.reg_time;
        
        IF v_in_count = 0 THEN
            RAISE_APPLICATION_ERROR(-20013, '物品' || :NEW.item_name || '没有进楼记录');
        END IF;
    END IF;
END;
/


-- 9. 楼栋管理员变更审计触发器

-- 记录楼栋管理员变更
CREATE OR REPLACE TRIGGER trg_manager_change_audit
AFTER UPDATE OF manager_id ON buildings
FOR EACH ROW
BEGIN
    IF :OLD.manager_id IS NOT NULL OR :NEW.manager_id IS NOT NULL THEN
        INSERT INTO system_log (user_id, operation)
        VALUES (USER, '变更楼栋管理员: ' || :OLD.building_id || 
                      ' 旧:' || COALESCE(:OLD.manager_id, '无') || 
                      ' 新:' || COALESCE(:NEW.manager_id, '无'));
    END IF;
END;
/


-- 10. 学生信息变更历史跟踪触发器

-- 创建学生历史表
CREATE TABLE student_history (
    history_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    student_id VARCHAR2(20) NOT NULL,
    changed_column VARCHAR2(30) NOT NULL,
    old_value VARCHAR2(100),
    new_value VARCHAR2(100),
    change_time TIMESTAMP DEFAULT SYSTIMESTAMP,
    changed_by VARCHAR2(20)
);

-- 学生信息变更跟踪
CREATE OR REPLACE TRIGGER trg_student_change_history
AFTER UPDATE ON students
FOR EACH ROW
BEGIN
    -- 跟踪姓名变更
    IF :OLD.name != :NEW.name THEN
        INSERT INTO student_history (student_id, changed_column, old_value, new_value, changed_by)
        VALUES (:OLD.student_id, '姓名', :OLD.name, :NEW.name, USER);
    END IF;
    
    -- 跟踪班级变更
    IF :OLD.class_name != :NEW.class_name THEN
        INSERT INTO student_history (student_id, changed_column, old_value, new_value, changed_by)
        VALUES (:OLD.student_id, '班级', :OLD.class_name, :NEW.class_name, USER);
    END IF;
    
    -- 跟踪宿舍变更
    IF :OLD.room_id != :NEW.room_id THEN
        INSERT INTO student_history (student_id, changed_column, old_value, new_value, changed_by)
        VALUES (:OLD.student_id, '宿舍', :OLD.room_id, :NEW.room_id, USER);
    END IF;
END;
/

3.5. 数据操纵

-- 1. 基本数据插入
-- 添加新公寓楼
INSERT INTO buildings (building_id, building_name) 
VALUES ('B03', '宁静楼');

-- 添加宿舍房间
INSERT INTO dorm_rooms (room_id, building_id, capacity) 
VALUES ('B03-201', 'B03', 4);

-- 添加学生
INSERT INTO students (student_id, name, class_name, room_id) 
VALUES ('20230010', '王明', '电子2023级1班', 'B03-201');

-- 添加员工
INSERT INTO staff (staff_id, name, position) 
VALUES ('S1005', '李楼管', '管理员');

-- 2. 基本数据更新
-- 修改学生班级
UPDATE students 
SET class_name = '电子2023级2班' 
WHERE student_id = '20230010';

-- 更新员工联系方式
UPDATE staff 
SET contact = '13900139000' 
WHERE staff_id = 'S1005';

-- 3. 基本数据删除
-- 删除已毕业学生
DELETE FROM students 
WHERE student_id = '20200001';

-- 删除离职员工
DELETE FROM staff 
WHERE staff_id = 'S1003';

-- 4.出入登记操作
-- 访客登记（简化版）
INSERT INTO visitor_registrations (visitor_name, student_id, building_id, staff_id)
VALUES ('张家长', '20230010', 'B03', 'S1005');

-- 物品进楼登记
INSERT INTO item_registrations (student_id, item_name, direction, staff_id)
VALUES ('20230010', '台式电脑', '进楼', 'S1005');


-- 5.简单统计查询
-- 统计各楼栋学生人数
SELECT b.building_name, COUNT(s.student_id) AS student_count
FROM buildings b
LEFT JOIN dorm_rooms d ON b.building_id = d.building_id
LEFT JOIN students s ON d.room_id = s.room_id
GROUP BY b.building_name;

-- 统计物品进出数量
SELECT item_name, 
       SUM(CASE WHEN direction = '进楼' THEN 1 ELSE 0 END) AS in_count,
       SUM(CASE WHEN direction = '出楼' THEN 1 ELSE 0 END) AS out_count
FROM item_registrations
GROUP BY item_name;

学生宿舍管理系统.zip