实验八 数据库编程实践

一、实验目的
1、理解存储过程、流程控制、触发器、事件等相关概念。

2、掌握存储过程、流程控制、触发器等创建以及管理的相关指令的使用。

3、掌握游标的定义、使用方法。
二、实验内容

1、存储过程实验1

1）创建一个存储过程，名为page_proc，传入参数两个，分别是当前的页码数和每页显示的最大记录数。

2）获取分页的总记录数，并利用传递的参数计算总页数，拼接分页的查询语句。

3）根据传递的参数查询指定分页的记录，如第2页的记录。

2、存储过程实验2

   在shop数据库中创建一个过程，以订单编号（order_id）为参数，利用游标，依次将表sh_order_goods中该订单的商品价格高于100的商品信息输出。

3、触发器实验

   在shop.sh_order_goods表上创建一个触发器，当添加订单-商品信息时，修改sh_goods表中对应商品的库存量，即将库存量为0的商品，更新为1000。

• 实验步骤和过程记录

1、存储过程实验1

创建一个存储过程，名为page_proc，传入参数两个，分别是当前的页码数和每页显示的最大记录数。

1. mysql> DELIMITER $$
2. mysql>  CREATE PROCEDURE page_proc(IN curp INT,IN per_page INT)
3.     -> BEGIN
4.     -> DECLARE total_records,total_page,start INT;
5.     -> SELECT COUNT(*) INTO total_records FROM sh_goods;
6.     -> IF per_page<=1
7.     -> THEN SET per_page=3;
8.     -> END IF;
9.     -> SET total_page=CEIL(total_records/per_page);
10.     -> IF curp<1
11.     -> THEN SET curp=1;
12.     -> ELSEIF curp >total_page THEN SET curp=total_page;
13.     -> END IF;
14.     -> SET start =(curp-1)*per_page;
15.     -> SET @sql_stmt=CONCAT('SELECT * FROM sh_goods LIMIT ',start,'\,',per_page);
16.     -> PREPARE paging FROM @sql_stmt;
17.     ->  EXECUTE paging;
18.     -> DEALLOCATE PREPARE paging;
19.     -> END;
20.     -> $$

创建完存储过程之后，还需要将结束符改回“；”。

根据传递的参数查询指定分页的记录，我们可以查询第3页的3条记录：

2、存储过程实验2

   在shop数据库中创建一个过程，以订单编号（order_id）为参数，利用游标，依次将表sh_order_goods中该订单的商品价格高于100的商品信息输出。

1. mysql> DELIMITER $$
2. CREATE  PROCEDURE cur_pro (IN p_order_id INT)
3. BEGIN
4. DECLARE p_goods_id,p_goods_num,p_goods_price INT;
5. DECLARE p_goods_name VARCHAR(50);
6. DECLARE mark INT DEFAULT 0;
7. DECLARE cur CURSOR FOR
8. SELECT goods_id,goods_name,goods_num,goods_price FROM sh_order_goods WHERE order_id=p_order_id AND goods_price>100;
9. DECLARE EXIT HANDLER FOR SQLSTATE '02000' SET mark=1;
10. OPEN cur;
11. REPEAT
12. FETCH cur INTO p_goods_id,p_goods_name,p_goods_num,p_goods_price ;
13. SELECT p_goods_id,p_goods_name,p_goods_num,p_goods_price ;
14. UNTIL mark END REPEAT;
15. CLOSE cur;
16. END
17.     -> $$
18. Query OK, 0 rows affected (0.01 sec)
20. mysql> DELIMITER ;

查看sh_order_goods表内容如下，根据题意，要查询good_price>100的数据，结果理论上只会返回前两条。

执行存储过程结果如下，与实现预测的结果符合。

3、触发器实验

在shop.sh_order_goods表上创建一个触发器，当添加订单-商品信息时，修改sh_goods表中对应商品的库存量，即将库存量为0的商品，更新为1000。

1. DELIMITER $$
2. CREATE TRIGGER insert_tr BEFORE INSERT
3. ON sh_order_goods FOR EACH ROW 
4. BEGIN
5. DECLARE stocks  INT DEFAULT 0;
6. SELECT stock INTO stocks FROM sh_goods where id=new.goods_id ; 
7. IF stocks=0
8. THEN SET new.goods_num :=stocks;
9. UPDATE sh_goods SET stock =1000 WHERE id=new.goods_id ;
10. END IF;
11. END;
12.     -> $$
13. Query OK, 0 rows affected (0.01 sec)
15. mysql> 
16. mysql> 
17. mysql> DELIMITER ;

此时查看sh_order_goods表和sh_goods表中的第五项，可以看到sh_goods表中第五项的stock为0，而sh_order_goods表中暂无数据。

    在sh_order_goods中插入数据如下：

这时再次查看sh_order_goods表和sh_goods表中的第五项，可以看到sh_goods表中第五项的stock已经成功改为1000，而sh_order_goods表中的goods_num为0，这说明触发器的触发过程是一个无痕操作，触发器会隐式地将sh_goods中id为5的商品的stock值设置为1000。

• 问题思考与总结

1、什么时候适合通过创建存储过程来实现？

当一个事务涉及到多个SQL语句时或者涉及到对多个表的操作时就要考虑用存储过程。存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般 SQL 语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。当对数据库进行复杂操作时，可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。这些操作，如果用程序来完成，就变成了一条条的 SQL 语句，可能要多次连接数据库。而换成存储，只需要连接一次数据库即可。

2、功能相同的存储过程和存储函数的不同点有哪些？

①存储过程用户在数据库中完成特定操作，存储函数用于返回特定的数据。

②存储过程声明用PROCEDURE，函数用FUNCTION。

③存储过程不需要返回类型，函数必须要返回类型。

④存储过程只能通过out和in/out来返回值，函数除了可以使用out，in/out以外，还可以使用return返回值。

⑤sql语句中不可用调用存储过程，而在存储函数可以。

3、使用游标对于数据检索的好处有哪些？

 在数据库中，游标提供了一种对从表中检索出的数据进行操作的灵活手段，就本质而言，游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。游标总是与一条SQL 选择语句相关联因为游标由结果集以及结果集中指向特定记录的游标位置组成。当决定对结果集进行处理时，必须声明一个指向该结果集的游标。

4、能否在当前数据库中为其他数据库创建触发器？

    触发器是由事件来触发某个操作。触发器可以引用当前数据库以外的对象，但是只能在当前数据库中创建触发器。

5、触发器何时被激发？

指定触发器的触发时间。如果指定为BEFORE，则表示在执行DML（INSERT、DELETE、UPDATE）操作之前触发，以防止某些错误操作发生；如果指定为AFTER，则表示在执行DML操作之后触发，以便记录该操作或做某些操作处理。