MySQL-存储过程(头歌数据库实验题)

MySQL存储过程实验与自主练习

原创已于 2024-12-22 21:02:55 修改 · 2.1k 阅读

22 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #mysql

于 2024-12-22 20:07:26 首次发布

MySql 专栏收录该内容

1 篇文章

订阅专栏

（学校数据库课程的头歌平台实验题，根据自己理解编写，希望对正在学的人有启发作用和借鉴帮助，不喜勿喷，有错请联系改正）

实验

存储过程：输入1

任务描述：

本关任务：编写一个存储过程，输入起始价格、价格分段长度、价格分段数，将Books表中处于某一区间段的价格数mSalePrice改为所在区间的起始数。比如输入0，5，10，则处理0-50的价格总区间，分段区间为[0,5),[5,9)....如果价格为7，则位于区间[5,9)，将价格设置为5。建议使用循环语句进行编写。

AC代码：

  USE purchase_book;

#请在此处添加实现代码
########## Begin ##########
delimiter $$

create procedure MYPROC(in init decimal, in len decimal, in number decimal)
begin
  declare i int default 0;
  declare interval_start decimal;
  declare interval_end decimal;

  -- 循环遍历每个区间
  while i < number do
    -- 计算当前区间的起始和结束价格
    set interval_start = init + len * i;
    set interval_end = init + len * (i + 1); -- 减去0.01以确保区间是开区间
    
    -- 更新Books表中的价格
    update books
    set mSalePrice = interval_start
    where mSalePrice >= interval_start and mSalePrice < interval_end;
    
    -- 增加循环计数
    set i = i + 1;
  end while;
end$$

delimiter ;

########## End ##########

存储过程：输入2

任务描述：

本关任务：编写一个存储过程。现有数据表books(cBookID char(10),mSalePrice decimal,cTypeID char(5))和booktype(cTypeID char(5),vTypeName varchar(100))。vTypeName包括（科技、经管、外语、文学、童书)五类书籍类别。输入一个书籍类别和一个标志符号，如果输入0，则把输入书籍类别价格乘以0.5，如果输入1，则将除了输入书籍类别的书籍的价格乘以0.5。存储过程声明如下：
create procedure MYPROC(in bookType varchar(100), in flag int)

AC代码：

  USE purchase_book;

#请在此处添加实现代码
########## Begin ##########
delimiter $$

create procedure MYPROC(in init decimal, in len decimal, in number decimal)
begin
  declare i int default 0;
  declare interval_start decimal;
  declare interval_end decimal;

  -- 循环遍历每个区间
  while i < number do
    -- 计算当前区间的起始和结束价格
    set interval_start = init + len * i;
    set interval_end = init + len * (i + 1); -- 减去0.01以确保区间是开区间
    
    -- 更新Books表中的价格
    update books
    set mSalePrice = interval_start
    where mSalePrice >= interval_start and mSalePrice < interval_end;
    
    -- 增加循环计数
    set i = i + 1;
  end while;
end$$

delimiter ;

########## End ##########

存储过程：输出1

任务描述：

本关任务：编写一个存储过程。现有数据表books(cBookID char(10),vBookName varchar(100))以及
evaluation(cEvaluationID char(36),cBookID char(10),comment varchar(2000)
comment属性包括（非常好，很好，好，一般，差）。现在需要分别输出“非常好”最多的书名和相应“非常好”评价的数量到输出变量中。存储过程声明如下：
create procedure MYPROC(out bookname varchar(100), out eval int)

AC代码：

  USE purchase_book;

#请在此处添加实现代码
########## Begin ##########
delimiter $$

create procedure MYPROC(out bookname varchar(100), out eval int)
begin
  select b.vBookName, count(*) as count into bookname, eval
  from evaluation e
  inner join books b on e.cBookID = b.cBookID
  where e.comment = '非常好'
  group by b.vBookName
  order by count desc
  limit 1;
end$$

delimiter ;
########## End ##########

存储过程：输入输出1

任务描述:

本关任务：编写一个存储过程。现有数据表books(cBookID char(10),vBookName varchar(100),mSalePrice decimal， siQuantity smallint)。现在需要输入一个bookname varchar 变量和number int变量，找到价格小于bookname的、从大到小排在第number位的书籍，注意limit下标从0开始。然后将对应的vBookName赋值给bookname、siQuantity赋值给number。如果书籍不存在则给bookname赋值“not existed”。存储过程声明如下：
create procedure MYPROC(inout bookname varchar(100), inout number int)

AC代码：

USE purchase_book;

DELIMITER //

CREATE PROCEDURE MYPROC(INOUT bookname VARCHAR(100), INOUT number INT)
BEGIN
    DECLARE found_book_name VARCHAR(100);
    DECLARE found_quantity SMALLINT;
    DECLARE book_price DECIMAL;
    DECLARE offset INT DEFAULT 0;
    
    -- 获取输入 bookname 对应的书籍价格
    SELECT mSalePrice INTO book_price
    FROM books
    WHERE vBookName = bookname;
    
    -- 检查是否找到了对应的书籍价格
    IF book_price IS NULL THEN
        SET bookname = 'not existed';
        SET number = 1;
    ELSE
        -- 计算LIMIT的起始值
        SET offset = number - 1;
        
        -- 查找价格小于输入 bookname 对应的书籍价格，从大到小排在第 number 位的书籍   
        SELECT vBookName, siQuantity INTO found_book_name, found_quantity
        FROM books
        WHERE mSalePrice < book_price
        ORDER BY mSalePrice DESC
        LIMIT offset, 1;

        IF found_book_name IS NULL THEN
            SET bookname = 'not existed';
            SET number = NULL;
        ELSE
            SET bookname = found_book_name;
            SET number = found_quantity;
        END IF;
    END IF;
END //

DELIMITER ;

因为limit语句第一个参数不能用表达式，所以用来一个offset变量来存储number-1，表示忽略前number-1个数据，返回后面的第一个数据。

存储过程：输入输出2

任务描述：

本关任务：编写一个存储过程。现有数据表books(cBookID char(10),vBookName varchar(100),mSalePrice decimal， siQuantity smallint)。现在需要输入一个价格变量price，找到离该价格最接近的价格和对应书籍，将找到的价格赋值给price。存储过程声明如下：
create procedure MYPROC(inout price decimal, out bookname varchar(100))

AC代码：

  USE purchase_book;

#请在此处添加实现代码
########## Begin ##########
DELIMITER $$

CREATE PROCEDURE MYPROC(INOUT price DECIMAL, OUT bookname VARCHAR(100))
BEGIN
    select mSaleprice, vBookName into price, bookname
    from books
    order by abs(price - mSaleprice)
    limit 1;
    
END $$

DELIMITER ;
########## End ##########

这题主要是要想到用绝对值来排序就很好做了。

自主练习

存储过程：输入

任务描述:

本关任务：编写一个存储过程，输入基本销量baseQty，将销量（siQuantity）小于基本baseQty的商品的售价mSalePrice乘以cut数值。存储过程声明如下：
create procedure MYPROC(in baseQty decimal, in cut decimal(10,2))

AC代码：

USE purchase_book;

#请在此处添加实现代码
########## Begin ##########
delimiter $$
 
create procedure MYPROC(in baseQty decimal, in cut decimal(10,2))
begin
  update books
  set mSalePrice = mSalePrice * cut
  where siQuantity < baseQty;
end$$
 
delimiter ;
########## End ##########

存储过程：输出

任务描述：

本关任务：编写一个存储过程，针对表books,如果siQuantity在0-99，则mSalePrice乘以0.9,如果siQuantity在100-499，则mSalePrice乘以1.1,否则乘以1.2。将书籍平均价格输出到price。存储过程声明如下：
create procedure MYPROC(out price decimal)

AC代码：

USE purchase_book;

#请在此处添加实现代码
########## Begin ##########

delimiter $$
 
create procedure MYPROC(out price decimal)
begin
  update books 
  set mSalePrice = mSalePrice*0.9
  where 0 <= siQuantity AND siQuantity <= 99;

  update books 
  set mSalePrice = mSalePrice*1.1
  where 100 <= siQuantity AND siQuantity <= 499;

  update books 
  set mSalePrice = mSalePrice*1.2
  where siQuantity<0 or siQuantity>=500;

  select avg(mSalePrice) into price
  from books;

end$$
 
delimiter ;
########## End ##########

注意不能用0 <= siQuantity <= 99这种形式，这在Sql语法中是错误的。

存储过程：输入输出

任务描述:

本关任务：编写一个存储过程。现有数据表books(cBookID char(10),vBookName varchar(100),mSalePrice decimal， siQuantity smallint)和orderdetail(cOrderNo char, cBookID char, mSalePrice decimal, siQty smallint,mBookCost decimal)，orders(cOrderNo,mTotalCost,vPayType varchar(20))。现在需要输入vPayType类型变量（包括：网银、支付宝、微信），将对应的订单的总销量价值和最大订单销量价值取出放入变量totalCost和maxCost中。存储过程声明如下：
create procedure MYPROC(in paytype varchar(20), out totalCost int, out maxCost int)

AC代码：

USE purchase_book;

#请在此处添加实现代码
########## Begin ##########
delimiter $$
 
create procedure MYPROC(in paytype  varchar(20), out totalCost int, out maxCost int)
begin
  select sum(mTotalCost), max(mTotalCost) into totalCost, maxCost
  from orders
  where vPayType = paytype;
end$$
 
delimiter ;
########## End ##########

（居然mTotalCost是销量）。。

(如果有帮助请点赞或评论支持，有问题请指正，其他问题请评论交流...)

~~都看到这里了真不点个赞吗~~