数据库的存储过程

查询指定系别的所有读者信息

-- 查询指定系别的所有读者信息
CREATE PROCEDURE get_readers_by_department(IN deptName VARCHAR(50))
BEGIN
    SELECT * FROM Readers WHERE department = deptName;
END;

向Reader表中插入一条数据，并处理主键冲突 

--向Reader表中插入一条数据，并处理主键冲突
CREATE PROCEDURE insert_reader(IN readerId INT, IN readerName VARCHAR(50), IN tel VARCHAR(11), IN dept VARCHAR(50))
BEGIN
    DECLARE EXIT HANDLER FOR 1062
    BEGIN
        INSERT INTO ErrorLog (LogDate, Operation, ErrorMessage)
        VALUES (CURDATE(), 'INS', 'Duplicate entry for ReaderId');
    END;
    INSERT INTO Readers (reader_id, reader_name, tel, department)
    VALUES (readerId, readerName, tel, dept);
END;

使用游标返回图书表中所有出版社为“清华大学出版社”的书籍编号、书籍名称和书籍类型，并在游标中查找并显示书名为“C++程序设计”的书籍编号、书籍名称和书籍类型

--使用游标返回图书表中所有出版社为“清华大学出版社”的书籍编号、书籍名称和书籍类型，并在游标中查找并显示书名为“C++程序设计”的书籍编号、书籍名称和书籍类型
CREATE PROCEDURE get_books_by_publisher()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE bookId INT;
    DECLARE bookName VARCHAR(50);
DECLARE bookType VARCHAR(50);

    DECLARE bookCursor CURSOR FOR
        SELECT Book_id, Book_name, Book_type
        FROM BookInfo
        WHERE publisher = '清华大学出版社';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    OPEN bookCursor;
    read_loop: LOOP
        FETCH bookCursor INTO bookId, bookName, bookType;
        IF done THEN
            LEAVE read_loop;
        END IF;
        IF bookName = 'C++程序设计' THEN
            SELECT bookId;
            SELECT bookName;
            SELECT bookType;
        END IF;
    END LOOP;
    CLOSE bookCursor;
END

完整SQL代码：

create DATABASE LittleLibrary2;
use LittleLibrary2;

-- 创建读者表
CREATE TABLE Readers (
  reader_id INT PRIMARY KEY,
  reader_name VARCHAR(50),
  gender VARCHAR(10),
  department VARCHAR(50),
  email VARCHAR(100),
	tel VARCHAR(11)
);

-- 插入读者数据
INSERT INTO Readers (reader_id, reader_name, gender, department, email,tel)
VALUES 
  (1, '张三', '男', '计算机科学与技术', 'zhangsan@example.com','13594427396'),
  (2, '李四', '女', '信息管理与信息系统', 'lisi@example.com','15552369452'),
  (3, '王五', '男', '软件工程', 'wangwu@example.com','12345678998'),
  (4, '赵六', '女', '电子工程', 'zhaoliu@example.com','98765432112'),
  (5, '钱七', '男', '网络工程', 'qianqi@example.com','14725836912');

-- 创建图书表
CREATE TABLE BookInfo (
  Book_id INT PRIMARY KEY,
  ISBN VARCHAR(20),
  Book_name VARCHAR(100),
  author VARCHAR(50),
  publisher VARCHAR(50),
  Book_type VARCHAR(50),
  price DECIMAL(8, 2),
  order_date DATE,
  Book_status VARCHAR(10)
);

-- 插入图书数据
INSERT INTO BookInfo (Book_id, ISBN, Book_name, author, publisher, Book_type, price, order_date, Book_status)
VALUES 
  (1, '978-7-302-33007-0', '数据库系统原理及应用', '袁丽娜', '人民邮电出版社', '专业基础', 59.00, '2021-01-01', '在库'),
  (2, '978-7-115-41235-5', '网站设计与Web前端应用开发技术', '张锦祥', '清华大学出版社', '编程语言', 69.00, '2021-01-01', '借出'),
  (3, '978-7-302-33007-1', '计算机科学导论', '罗琼', '北京邮电大学出版社', '专业基础', 89.00, '2021-01-02', '在库'),
  (4, '978-7-03-032711-6', '大数据技术实战教程', '袁丽娜', '大连理工大学出版社', '实践类', 79.00, '2021-01-02', '借出'),
  (5, '978-7-04-047815-2', '数据结构', '严蔚敏', '人民邮电出版社', '编程语言', 99.00, '2021-01-03', '在库'),
	(6, '978-7-04-047915-2', '数据仓库与数据挖掘实践', '李春葆', '电子工业出版社', '实践类', 99.00, '2021-01-03', '在库'),
  (7, '978-7-110-41235-5', 'Java程序设计', '李晶晶', '北京理工大学出版社', '编程语言', 99.00, '2021-01-01', '借出');

-- 创建借阅记录表
CREATE TABLE Borrow_Records (
  Record_id INT PRIMARY KEY,
  Reader_id INT,
  Book_id INT,
  Borrow_date DATE,
  return_date DATE,
  FOREIGN KEY (Reader_id) REFERENCES Readers(Reader_id),
  FOREIGN KEY (Book_id) REFERENCES BookInfo(Book_id)
);

-- 插入借阅记录数据
INSERT INTO borrow_records (record_id, reader_id, book_id, borrow_date, return_date)
VALUES 
  (1, 1, 2, '2021-01-05', '2021-01-10'),
  (2, 1, 3, '2021-02-15', '2021-02-20'),
  (3, 2, 1, '2021-03-10', '2021-03-15'),
  (4, 3, 4, '2021-04-20', NULL),
  (5, 4, 2, '2021-05-01', '2021-05-05'),
  (6, 5, 3, '2021-06-10', NULL),
  (7, 1, 4, '2021-07-15', '2021-07-20'),
  (8, 3, 2, '2021-08-01', NULL),
  (9, 4, 3, '2021-09-10', NULL),
  (10, 5, 1, '2021-10-05', NULL);
-- 查询指定系别的所有读者信息
CREATE PROCEDURE get_readers_by_department(IN deptName VARCHAR(50))
BEGIN
    SELECT * FROM Readers WHERE department = deptName;
END;
--向Reader表中插入一条数据，并处理主键冲突
CREATE PROCEDURE insert_reader(IN readerId INT, IN readerName VARCHAR(50), IN tel VARCHAR(11), IN dept VARCHAR(50))
BEGIN
    DECLARE EXIT HANDLER FOR 1062
    BEGIN
        INSERT INTO ErrorLog (LogDate, Operation, ErrorMessage)
        VALUES (CURDATE(), 'INS', 'Duplicate entry for ReaderId');
    END;
    INSERT INTO Readers (reader_id, reader_name, tel, department)
    VALUES (readerId, readerName, tel, dept);
END;
--使用游标返回图书表中所有出版社为“清华大学出版社”的书籍编号、书籍名称和书籍类型，并在游标中查找并显示书名为“C++程序设计”的书籍编号、书籍名称和书籍类型
CREATE PROCEDURE get_books_by_publisher()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE bookId INT;
    DECLARE bookName VARCHAR(50);
DECLARE bookType VARCHAR(50);

    DECLARE bookCursor CURSOR FOR
        SELECT Book_id, Book_name, Book_type
        FROM BookInfo
        WHERE publisher = '清华大学出版社';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    OPEN bookCursor;
    read_loop: LOOP
        FETCH bookCursor INTO bookId, bookName, bookType;
        IF done THEN
            LEAVE read_loop;
        END IF;
        IF bookName = 'C++程序设计' THEN
            SELECT bookId;
            SELECT bookName;
            SELECT bookType;
        END IF;
    END LOOP;
    CLOSE bookCursor;
END