数据库每日一题 (4)存储过程

题目

在这里插入图片描述

解答

SUM(t1.销售数量*t1.销售单价) --SUM(销售数量*销售单价) 也正确
t1.商品号 = t2.商品号
year(t1.销售时间) --year(销售时间) 也正确
t2.类别 = @lb

解析

建表

if not exists(SELECT name FROM SYS.DATABASES WHERE name = 'SALES')
CREATE DATABASE SALES

USE SALES

if exists(SELECT name FROM sys.objects WHERE name = 'SELL')
DROP TABLE SELL

if exists(SELECT name FROM sys.objects WHERE name = 'COMMODITY')
DROP TABLE COMMODITY

CREATE TABLE COMMODITY
(
NO INT PRIMARY KEY,
NAME CHAR(10) NOT NULL  UNIQUE,
CATEGORY CHAR(10) NOT NULL,
COST FLOAT NOT NULL
)
CREATE TABLE SELL
(
NO INT FOREIGN KEY REFERENCES COMMODITY(NO),
TIME DATETIME NOT NULL,
QUANTITY INT NOT NULL,
PRICE FLOAT NOT NULL,
PROFIT FLOAT 
)
插入值
INSERT INTO COMMODITY
VALUES 
	(1,'冰红茶','饮料', 1.5),
	(2,'方便面','食品',0.5),
	(3,'矿泉水','饮料',0.8)

在这里插入图片描述

创建触发器

练习上一次所学的触发器知识,在题目表格之外,新增一列此次交易利润。通过创建触发器,提供购买的商品号与数量,计算此次交易的利润并填入表格。

注意:此表与题目表格区别在此

并且,通过阅读文章SQL Server 触发器批量数据行处理,做到了批量处理交易数据。

计算商品利润触发器
USE SALES
IF EXISTS(SELECT * FROM DBO.SYSOBJECTS WHERE ID = OBJECT_ID(N'[DBO].[COUNTPROFIT]') AND OBJECTPROPERTY(ID, N'ISTRIGGER') = 1)
DROP TRIGGER COUNTPROFIT
GO

--批量插入触发
CREATE TRIGGER COUNTPROFIT 
	ON SELL
	INSTEAD OF INSERT
AS
BEGIN 
	INSERT INTO SELL
		SELECT NO,TIME,QUANTITY,PRICE,(QUANTITY*(SELECT COST 
			FROM COMMODITY C
			WHERE C.NO = I.NO)) AS PROFIT
		FROM inserted I
END;
插入数据测试
INSERT INTO SELL
VALUES
	--商品号,日期,数量,价格,获利(由触发器计算)
	(1,GETDATE(),2,3, null ),
	(2,GETDATE(),2,1, null ),
	(1,GETDATE(),2,3, null ),
	(3,GETDATE(),2,1, null )

测试成功

存储过程(完整答案)

USE SALES

IF EXISTS(SELECT name FROM SYS.objects WHERE NAME ='p_TotalProfit')
DROP PROC p_TotalProfit
GO
CREATE PROC p_TotalProfit
@lb char(10)
AS
SELECT NAME AS 商品名, (SELECT SUM(QUANTITY*PRICE)
				FROM SELL t1
				WHERE t1.NO=t2.NO and year(t1.time)=year(GETDATE())) AS 销售总金额
FROM COMMODITY t2
WHERE t2.CATEGORY = @lb
GO 
测试结果

测试结果
测试结果

源码地址

GitHub仓库

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值