存储过程

第一部分：样例库的应用

1） Creating Stored Procedures（创建存储过程）SQL语句描述：

create procedure productpricing( )
begin
    select avg(prod_price) as priceaverage
    from products;
end;

功能描述：将查询产品平均价格存储为过程productpricing

执行结果截图：

2）Executing Stored Procedures（调用过程）

SQL语句描述：

CALL productpricing();

功能描述：调用过程，即查询产品平均价格

执行结果截图：

3）Dropping Stored Procedures（删除过程）

SQL语句描述：

DROP PROCEDURE productpricing;

功能描述：删除过程

执行结果截图：

4）Working with Parameters（带参的存储过程）

4.1）带输出参数：

SQL语句描述：

create procedure productpricing(
    out pl decimal(8,2),
    out ph decimal(8,2),
    out pa decimal(8,2)
)
begin
    select min(prod_price)
    into pl
    from products;
    select max(prod_price)
    into ph
    from products;
    select avg(prod_price)
    into pa
    from products;
end ;

功能描述：分别返回产品的最高、最低、平均价格，并存入三个变量中

执行结果截图：

创建成功后，查询结果如下

CALL productpricing(@pricelow,@pricehigh,@priceaverage) ;

SELECT @pricelow,@pricehigh,@priceaverage;

4.2）带输入输出参数：

SQL语句描述：

create procedure ordertotal(
   in onumber int,
   out ototal decimal(8,2)
)
begin
   select sum(item_price*quantity)
   from orderitems
   where order_num = onumber
   into ototal;
end ;

功能描述：求出某特定订单号的总金额

执行结果截图：

创建成功后，查询结果如下

CALL ordertotal(20005, @total);

SELECT @total;

5）Building Intelligent Stored Procedures（创建智能存储过程）

SQL语句描述：

create procedure ordertotal(
   in onumber int,
   in taxable boolean,
   out ototal decimal(8,2)
)
begin
   -- declare variable for total
   declare total decimal(8,2);
   -- declare tax percentage
   declare taxrate int default 6;
   -- get the order total
   select sum(item_price*quantity)
   from orderitems
   where order_num = onumber
   into total;
   -- is this taxable?
   if taxable then
      -- yes, so add taxrate to the total
      select total+(total/100*taxrate) into total;
  end if;
   -- and finally, save to out variable
   select total into ototal;
end;

功能描述：求是否含税的给定订单总金额

执行结果截图：

下图查询订单为20005号，且不含税的订单总金额。

下图查询订单为20005号，且含税的订单总金额。

6） Inspecting Stored Procedures（检查存储过程）

6.1）

SQL语句描述：

SHOW CREATE PROCEDURE ordertotal;

功能描述：显示用于创建一个存储过程ordertotal的CREATE语句，即定义语句的本身

执行结果截图：

6.2）

SQL语句描述：

SHOW PROCEDURE STATUS LIKE 'ordertotal' ;

功能描述：获得当前数据库中包括何时、何人创建等详细信息的存储过程列表，使用like关键字限制或指定一个过滤模式。

执行结果截图：

第二部分：所选课题数据库的应用

1） Creating Stored Procedures（创建存储过程）

SQL语句描述：

create procedure goodsnum()
begin
    select count(*) as num from goods;
end;

功能描述：查询商品个数

执行结果截图：

2）Executing Stored Procedures（调用过程）

SQL语句描述：

CALL goodsnum ();

功能描述：调用过程，即查询商品个数

执行结果截图：

3）Dropping Stored Procedures（删除过程）

SQL语句描述：

DROP PROCEDURE goodsnum;

功能描述：删除过程

执行结果截图：

4）Working with Parameters（带参的存储过程）

SQL语句描述：

create procedure goodsnum(
  in gid int,
  out dnum int(11)
)
begin
  select goods_stock
  from goods
  where id = gid
  into dnum;
end;

功能描述：查询某商品数量

执行结果截图：

创建成功后，查询结果如下

5） Inspecting Stored Procedures（检查存储过程）

SQL语句描述：

SHOW CREATE PROCEDURE goodsnum;

功能描述：显示用于创建一个存储过程goodsnum的CREATE语句，即定义语句的本身

执行结果截图：

参考文章

这是我大三第一学期《数据库基础》这门课的实验报告，总共15个实验，我挑了几个比较重要的放在博客上，方便查看和复习，尤其是最近SSM后端开发，希望对大家，以及将来的我有所帮助！

本文是存储过程，通过书本和网上的参考代码和讲解，大致把存储过程理解为一个自定义函数，在多次重复使用某一个功能的时候，可以把这个功能写成一个自定义函数(存储过程)，这样使代码可读性增加，逼格也更高......

实验要求

在实验5数据库设计的基础上补充设计存储过程，并基于实验5建立的表和实验6插入的数据完成实验；

1. 按要求设计完成如下功能的存储过程；

        ①查询平均分数在x到y范围内的学生信息。说明：该存储过程有两个参数；要求查询的学生信息包括学号、姓名、院系名称和平均分数。

         ②更新操作，以学号、课程编号和考试成绩作参数更新指定学生和课程的考试成绩，并返回该学生的平均成绩。

2. 自行再分别设计一个完成查询和完成操作功能的存储过程（在实验报告中要准确描述功能需求）；

3. 在客户端以存储过程和输入ＳＱＬ语句的方式分别执行相同的查询或操作，比较使用和不使用存储过程的区别；

全部总结链接

SQL基础   （数据库、表、数据的增删改查、视图相关，以及所有实验报告源代码）

游标 （类似C++ 的 指针）

存储过程（类似 C++ 的自定义函数）

触发器 （类似 自定义的陷阱，或者说是监听器，满足某个条件了执行某个方法）

用户权限及权限管理 （类似Windows的多用户管理）

并发控制 （了解多个用户同时对数据造成错误的情况 和 解决方法）

数据恢复（当数据库数据丢失，相应的解决方法）

实验过程

第一大题

1.1查询平均分数在x到y范围内的学生信息

令x = 80 ， y = 88

代码：

create procedure aaa         //创建存储过程 名字为aaa

@s smallint, @e smallint     //传入两个参数@s(最低),@e(最高)

As

    select 学生.学号,学生.姓名,院系.名称 as 院系名称,avg(选课.成绩) as 平均成绩 from 学生,选课,院系

        where 学生.学号 = 选课.学号 and 学生.院系 = 院系.编号   //以上查询每个学生的学号、姓名、院系名称、平均成绩

        group by 学生.学号,学生.姓名,院系.名称      //对学号进行分组 

        having avg(选课.成绩) between @s and @e;     //筛选平均分条件

        go   //暂停作用


exec aaa 80,88; //执行


drop procedure aaa  // 删除

注：存储过程可以理解为C++中的自定义函数，先定义，然后通过主函数调用

图 1 查询平均分数在80到88范围内的学生信息

1.2更新操作，以学号、课程编号和考试成绩作参数更新指定学生和课程的考试成绩，并返回该学生的平均成绩。

代码：

Create  procedure  new_data    //创建存储过程 名字为new_data

@sno char(8), @cno char(8), @grade smallint  //传入三个参数 学号、课程号、成绩

as

    if(@sno is not null and @cno is not null)  //如果学号和课程号不为空

    begin

        update 选课 set 成绩 = @grade   //更新这个学生这门课的成绩

        where 选课.学号 = @sno and 选课.课程编号 = @cno;


        select 选课.学号,avg(选课.成绩) as 平均成绩

        from 选课        //然后在查询这个学生的平均分

        where 选课.学号 = @sno

        group by 选课.学号;

    end

    go

exec new_data '2000278','1156',99;  //执行

drop procedure new_data  //删除

注：为了不给后续实验造成干扰，所以我手动插入了这一个数据

图 2   第1.2题更新前 成绩为80分

然后执行代码，因为这个学生只有这一门课，所以平均分就是99分

图 3  更新成绩 输出平均分

图 4  第1.2题更新后  成绩为99分

第二大题

自行再分别设计一个完成查询和完成操作功能的存储过程

2.1完成查询功能

功能：输入一个学生的学号，输出其学号、姓名、性别、生源、院系名称和状态

代码：

create  procedure  new_find //创建存储过程 名字为new_find

@sno char(8)    //参数

as

    if(@sno is not null)    //如果主键不为空

    begin

        select 学生.学号,学生.姓名,学生.性别,学生.生源,院系.名称,学生.状态

        from 学生,院系

        where 学生.院系 = 院系.编号 and 学生.学号 = @sno

    end

go

exec new_find '2000012'    //执行

drop procedure new_find    //删除

图 5  查询学号为2000012的学生信息

2.2完成操作功能

功能：新增一门课程，插入到课程表

代码：

create  procedure  new_class   //创建名为new_class的存储过程

@cno char(8), @cname char(20), @ctea char(8), @time smallint, @xz char(10)  //参数

as

    if(@cno is not null)   //如果主键不为空

    begin

        insert 课程 values('1000','不知名的高级课程','100001',2,'专业基础');  //插入

    end

go

exec new_class '1000','不知名的高级课程','100001',2,'专业基础'   //执行

drop procedure new_class   //删除

图 6 更新前的课程

图 7 运行截图

图 8 更新后 多了一门插入的课

第三大题

注：把2.2题的代码拿来用，发现存储过程的代码就像是C++中的自定义函数，可以接受若干个参数，来实现需要的功能。那种多次执行某一个功能的时候，收益最大。

图 9 存储过程 代码

图 10  普通查询代码