数据库实验三

根据给定的信息，本次数据库实验主要分为两个部分：第一部分涉及存储过程与触发器的创建与使用；第二部分包括视图、事务与游标的概念及其应用。以下将对每个实验内容进行详细的解释与分析。 ### 实验内容（一）：存储过程与触发器 #### 存储过程 存储过程是一种在数据库服务器上预先编译好的SQL过程，可以接受参数并返回结果。通过存储过程可以提高应用程序的执行效率，并增强安全性。 **1. 创建一个存储过程用于查询订单的装运费用与包装费用** ```sql CREATE PROCEDURE prcCharges @cOrderNo CHAR(6), @mShippingCharges MONEY OUTPUT, @mGiftWrapCharges MONEY OUTPUT AS BEGIN SELECT @mShippingCharges = Orders.mShippingCharges, @mGiftWrapCharges = Orders.mGiftWrapCharges FROM Orders WHERE cOrderNo = @cOrderNo; END GO ``` 此存储过程接受订单号作为输入参数，并返回对应的装运费用和包装费用。`OUTPUT`关键字表示这些变量将在调用过程中被更新。 **示例调用代码：** ```sql DECLARE @mShippingCharges MONEY; DECLARE @mGiftWrapCharges MONEY; EXEC prcCharges '000001', @mShippingCharges OUTPUT, @mGiftWrapCharges OUTPUT; PRINT '装运费用:' + CONVERT(CHAR(10), @mShippingCharges); PRINT '包装费用:' + CONVERT(CHAR(10), @mGiftWrapCharges); ``` **2. 创建一个存储过程计算订单的总处理成本** ```sql CREATE PROCEDURE prcHandlingCharges @cOrderNo CHAR(6), @runcost MONEY OUTPUT AS BEGIN DECLARE @mShippingCharges MONEY; DECLARE @mGiftWrapCharges MONEY; EXEC prcCharges @cOrderNo, @mShippingCharges OUTPUT, @mGiftWrapCharges OUTPUT; SET @runcost = @mShippingCharges + @mGiftWrapCharges; END GO ``` 该存储过程同样接受订单号作为输入参数，但返回值是总处理成本，即装运费用加上包装费用。 **示例调用代码：** ```sql DECLARE @runcost MONEY; EXEC prcHandlingCharges '000002', @runcost OUTPUT; PRINT '000002的经营费用为：' + CONVERT(CHAR(10), @runcost); ``` **3. 创建一个存储过程用于生成每个月的销售冠军** ```sql CREATE PROCEDURE prcGenPickofMonth @year INT, @month SMALLINT AS BEGIN DECLARE @sold INT; DECLARE @ID CHAR(6); SELECT @ID = cToyId, @sold = iTotalSold FROM PickOfMonth WHERE iYear = @year AND siMonth = @month; PRINT CONVERT(VARCHAR(5), @year) + '年' + CONVERT(VARCHAR(5), @month) + '月的'; PRINT '玩具编号:' + CONVERT(VARCHAR(6), @ID); PRINT '销售额:' + CONVERT(VARCHAR(10), @sold); END GO ``` 该存储过程接收年份和月份作为输入参数，查询出当月销售最好的玩具的信息。 **示例调用代码：** ```sql EXEC prcGenPickofMonth 2001, 1; ``` #### 触发器 触发器是在特定事件发生时自动执行的一段SQL代码，用于维护数据完整性或执行复杂的业务逻辑。 **4. 创建一个触发器来更新库存量** ```sql CREATE TRIGGER TrOrderDetail ON OrderDetail FOR INSERT AS BEGIN DECLARE @ToyId CHAR(6); DECLARE @ToyQoh SMALLINT; DECLARE @Qty SMALLINT; SELECT @ToyId = cToyId, @Qty = siQty FROM inserted; SELECT @ToyQoh = siToyQoh FROM Toys WHERE cToyId = @ToyId; UPDATE Toys SET siToyQoh = @ToyQoh - @Qty WHERE cToyId = @ToyId; END ``` 该触发器在向`OrderDetail`表中插入新记录时被触发，用于更新玩具的库存数量。 **示例调用代码：** ```sql -- 插入前 SELECT * FROM Toys; -- 插入 INSERT INTO Orders VALUES ('000013', '05/12/2001', '000002', '000002', '01', 7, 1.2500, 'Y', 82.2200, '05/23/2001'); INSERT INTO OrderDetail VALUES ('000013', '000008', 2, 'N', NULL, NULL, 39.5000); -- 插入后 SELECT * FROM Toys; ``` **5. 创建一个触发器来更新订单明细中的玩具成本** ```sql CREATE TRIGGER OrderDetail_tyg ON OrderDetail FOR UPDATE AS BEGIN IF UPDATE(siQty) BEGIN UPDATE OrderDetail SET mToyCost = siQty * mToyRate FROM OrderDetail, Toys WHERE OrderDetail.cToyId = Toys.cToyId; END END ``` 该触发器在更新`OrderDetail`表中的`siQty`字段时被触发，用于同步更新玩具的成本。 ### 实验内容（二）：视图、事务与游标 这部分实验内容虽然没有给出具体的代码实现，但是我们可以简单介绍其概念与应用场景： **视图**：视图是从一个或多个表中导出的虚拟表，它并不实际存储数据，而是存储了一个SQL查询语句。视图可以简化复杂查询，提供安全机制等。 **事务**：事务是一系列SQL操作的集合，这些操作要么全部成功，要么全部失败，保证了数据的一致性和完整性。事务管理包括提交（COMMIT）、回滚（ROLLBACK）和保存点（SAVEPOINT）等操作。 **游标**：游标允许用户逐行处理查询结果集，通常用于循环读取数据。游标可以用于处理那些不适合一次性加载到内存中的大量数据。 通过上述实验内容的学习与实践，可以深入理解存储过程、触发器、视图、事务与游标在数据库中的作用及其实现方式，这对于进一步提升数据库设计与开发能力具有重要意义。