《数据库系统实训》实验报告——存储过程

柔情只为你懂 2023-01-13 04:18 128阅读 0赞

# **存储过程** #

**第一部分：样例库的应用**

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

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

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

**执行结果截图：**

![20210419232530299.png][]

**2****）****Executing Stored Procedures****（调用过程）**

**SQL****语句描述：**

CALL productpricing();

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

**执行结果截图：**

![20210419232530264.png][]

**3****）****Dropping Stored Procedures****（删除过程）**

**SQL****语句描述：**

DROP PROCEDURE productpricing;

**功能描述：**删除过程

**执行结果截图：**

![20210419232530341.png][]

**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 ;

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

**执行结果截图：**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzI3Mjc4MQ_size_16_color_FFFFFF_t_70][]

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

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzI3Mjc4MQ_size_16_color_FFFFFF_t_70 1][]

**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 ;

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

**执行结果截图：**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzI3Mjc4MQ_size_16_color_FFFFFF_t_70 2][]

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

CALL ordertotal(20005, @total);
    
    SELECT @total;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzI3Mjc4MQ_size_16_color_FFFFFF_t_70 3][]

**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;

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

**执行结果截图：**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzI3Mjc4MQ_size_16_color_FFFFFF_t_70 4][]

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzI3Mjc4MQ_size_16_color_FFFFFF_t_70 5][]

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzI3Mjc4MQ_size_16_color_FFFFFF_t_70 6][]

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

**6.1****）**

**SQL****语句描述：**

SHOW CREATE PROCEDURE ordertotal;

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

**执行结果截图：**

![20210419232530379.png][]

**6.2****）**

**SQL****语句描述：**

SHOW PROCEDURE STATUS LIKE 'ordertotal' ;

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

**执行结果截图：**

![20210419232530383.png][]

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

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

**SQL****语句描述：**

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

**功能描述：**查询商品个数

**执行结果截图：**

![20210419232530343.png][]

**2****）****Executing Stored Procedures****（调用过程）**

**SQL****语句描述：**

CALL goodsnum ();

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

**执行结果截图：**

![20210419232530345.png][]

**3****）****Dropping Stored Procedures****（删除过程）**

**SQL****语句描述：**

DROP PROCEDURE goodsnum;

**功能描述：**删除过程

**执行结果截图：**

![20210419232530259.png][]

**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;

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

**执行结果截图：**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzI3Mjc4MQ_size_16_color_FFFFFF_t_70 6][]

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzI3Mjc4MQ_size_16_color_FFFFFF_t_70 1][]

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

**SQL****语句描述：**

SHOW CREATE PROCEDURE goodsnum;

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

**执行结果截图：**

![20210419232530343.png][]

# 参考文章 #

[20210419232530299.png]: /images/20221022/c0a1bbf450a44122a466fa648730ba5e.png
[20210419232530264.png]: /images/20221022/d1d1a8e9efa94437a2d51e6c4e90cf29.png
[20210419232530341.png]: /images/20221022/be4006b226024c79884a40ee86fa03f8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzI3Mjc4MQ_size_16_color_FFFFFF_t_70]: /images/20221022/2a11a2028f6a4efe9efca5dee467ad85.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzI3Mjc4MQ_size_16_color_FFFFFF_t_70 1]: /images/20221022/be4006b226024c79884a40ee86fa03f8.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzI3Mjc4MQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzI3Mjc4MQ_size_16_color_FFFFFF_t_70 2]: /images/20221022/1f26810128f64cea9ef0d71edca12a68.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzI3Mjc4MQ_size_16_color_FFFFFF_t_70 3]: /images/20221022/65dfe8afd5684a65b3c43b23ac203906.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzI3Mjc4MQ_size_16_color_FFFFFF_t_70 4]: /images/20221022/90c3c959bf17423e90953a11438def91.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzI3Mjc4MQ_size_16_color_FFFFFF_t_70 5]: /images/20221022/5eb7c1b027304453b595bd0a72dfdeed.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzI3Mjc4MQ_size_16_color_FFFFFF_t_70 6]: /images/20221022/971947a453924b1381f48c15fcdd1a8a.png
[20210419232530379.png]: /images/20221022/998118b1b2dc49249c6830741dc663c9.png
[20210419232530383.png]: /images/20221022/58dd8232f6bb44319efe1e68aab06b73.png
[20210419232530343.png]: /images/20221022/d4b97ab844af48f384be65281cc43141.png
[20210419232530345.png]: /images/20221022/a78c57ec4d93458c979278098846e2dd.png
[20210419232530259.png]: /images/20221022/0bc575358faa4d8bbf4b2e0c37755df3.png