数据库系统实训——实验五——存储过程

第一部分：样例库的应用
1）Creating Stored Procedures（创建）

CREATE PROCEDURE productpricing( ) 
BEGIN
     SELECT Avg(prod_price) AS priceaverage
     FROM products;
END;
（求出产品的平均价格）

2）Executing Stored Procedures（调用）

CALL productpricing();

3）Dropping Stored Procedures（删除）

DROP PROCEDURE productpricing;

4）Working with Parameters（带参的存储过程）
1）带输出参数：

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 @pricehigh, @pricelow, @priceaverage;
（功能：分别返回产品的最高、最低、平均价格，并存入三个变量中，所有MYSQL变量在使用时都必须以@开头）

2）带输入输出参数：

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;
CALL ordertotal(20009, @total);
SELECT @total;
（功能：求出某特定订单号的总金额）

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

-- Name: ordertotal
-- Parameters: onumber = order number
--             taxable = 0 if not taxable, 1 if taxable
--             ototal  = order total variable

CREATE PROCEDURE ordertotal(
   IN onumber INT,
   IN taxable BOOLEAN,
   OUT ototal DECIMAL(8,2)
) COMMENT ‘Obtain order total, optionally adding tax’
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;

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

CALL ordertotal(20005, 1, @total);
SELECT @total;
（求是否含税的给定订单总金额）

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

SHOW CREATE PROCEDURE ordertotal;
（功能：显示用于创建一个存储过程ordertotal的CREATE语句，即定义语句的本身）

2）

SHOW PROCEDURE STATUS LIKE 'ordertotal' ;
（功能：获得当前数据库中包括何时、何人创建等详细信息的存储过程列表，可以限制或指定一个过滤模式，如LIKE关键字的使用。）

第二部分：所选课题数据库的应用
1）Creating Stored Procedures（创建）

CREATE PROCEDURE moviepricing( ) 
BEGIN
     SELECT Avg(length) AS lengthaverage
     FROM movies;
END;
（求出电影的平均时长）

2）Executing Stored Procedures（调用）

CALL moviepricing();

3）Dropping Stored Procedures（删除）

DROP PROCEDURE moviepricing;

4）Working with Parameters（带参的存储过程）
1）带输出参数：

CREATE PROCEDURE moviepricing(
   OUT pl DECIMAL(8,2),
   OUT ph DECIMAL(8,2),
   OUT pa DECIMAL(8,2)
)
BEGIN
   SELECT Min(length)
   INTO pl
   FROM movies;
   SELECT Max(length)
   INTO ph
   FROM movies;
   SELECT Avg(length)
   INTO pa
   FROM movies;
END;
CALL moviepricing(@movielow,@moviehigh,@movieaverage);
SELECT @moviehigh, @movielow, @movieaverage;
语句：（功能：分别返回产品的最高、最低、平均时长，并存入三个变量中，所有MYSQL变量在使用时都必须以@开头）

2）带输入输出参数：

CREATE PROCEDURE ordertotal(
   IN onumber INT,
   OUT ototal DECIMAL(8,2)
)
BEGIN
   SELECT Sum(producerC)
   FROM movies
   WHERE length = onumber
   INTO ototal;
END;

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

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

-- Name: ordertotal
-- Parameters: onumber = length
--             taxable = 0 if not taxable, 1 if taxable
--             ototal  = order total variable

CREATE PROCEDURE ordertotal(
   IN onumber INT,
   IN taxable BOOLEAN,
   OUT ototal DECIMAL(8,2)
) COMMENT 'Obtain order total, optionally adding tax'
BEGIN
   -- Declare variable for total
   DECLARE total DECIMAL(8,2);
   -- Declare tax percentage
   DECLARE taxrate INT DEFAULT 6;

   -- Get the order total
   SELECT Sum(producerC)
   FROM movies
   WHERE length = 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;

CALL ordertotal(116, 0, @total);
SELECT @total;

6）Inspecting Stored Procedures（检查存储过程）
1、
SHOW CREATE PROCEDURE ordertotal;
（功能：显示用于创建一个存储过程ordertotal的CREATE语句，即定义语句的本身）

2）

SHOW PROCEDURE STATUS LIKE 'ordertotal' ;
（功能：获得当前数据库中包括何时、何人创建等详细信息的存储过程列表，可以限制或指定一个过滤模式，如LIKE关键字的使用。）