--视图
--创建视图,用于查看三表联合
create view allshop
as
select a.id,a.namel,a.price,a.spec,b.sale,b.quantity,c.ywid,c.name2,c.sex,c.age,c.address from shop3_jb a,shop5_xs b,shop3_yw c where a.id=b.id and b.ywid=c.ywid

--使用视图
select * from allshop

--用视图方法,查看二号业务员商品销售金额,并显示商品名称,销售金额,业务员名
create view twoywid
as
select a.namel,(b.sale*b.quantity) as 销售金额,c.name2 from shop_jb a join shop_xs b on a.id=b.id
join shop_yw c on b.ywid=c.ywid where c.ywid=2
select * from twoywid

--查看视图内容
exec sp_helptext twoywid

--用视图方法,查看三号业务员商品销售金额,并显示商品名称,销售金额,业务员名
--修改视图内容
alter view twoywid
as
select a.namel,(b.sale*b.quantity) as 销售金额,c.name2 from shop3_jb a join shop5_xs b on a.id=b.id
join shop3_yw c on b.ywid=c.ywid where c.ywid=3

select * from twoywid

--重命名视图
exec sp_rename 'twoywid','bb'
select * from bb

--删除视图
drop view bb

select * from shop_jb1

--存储过程
--创建存储过程
create proc aaa
as
insert into shop_jb1(namel,spec,stock,price,datel) values('电视','合格',100,2000,'2010-8-2')
insert into shop_jb1(namel,spec,stock,price,datel) values('电脑','sp-A8',50,2000,'2010-7-5')
insert into shop_jb1(namel,spec,stock,price,datel) values('洗衣机','sp-400',100,900,'2010-7-2')
insert into shop_jb1(namel,spec,stock,price,datel) values('风扇','合格',1000,110,'2010-5-2')
insert into shop_jb1(namel,spec,stock,price,datel) values('饮水机','合格',100,160,'2010-8-2')
insert into shop_jb1(namel,spec,stock,price,datel) values('空调','sp-3',400,3000,'2010-8-2')
insert into shop_jb1(namel,spec,stock,price,datel) values('电脑桌','f-100',5000,200,'2010-7-16')
insert into shop_jb1(namel,spec,stock,price,datel) values('电灯','合格',100,20,'2010-8-2')
insert into shop_jb1(namel,spec,stock,price,datel) values('面包','合格',1000,2,'2010-7-2')
insert into shop_jb1(namel,spec,stock,price,datel) values('汽水','合格',10000,2,'2010-6-2')

--执行存储过程
exec aaa

--用存储过程方法,查看二号业务员商品销售金额,并显示商品名称,销售金额,业务员名
create proc twoywid
as
select a.namel,(b.sale*b.quantity) as 销售金额,c.name2 from shop_jb a join shop_xs b on a.id=b.id
join shop_yw c on b.ywid=c.ywid where c.ywid=2

exec twoywid

--利用存储过程完成以下需求,如果销售表的某种商品卖出时,则要求该种商品的库存量相对减少,这两个操作要么都成功执行,要么都不执行.
--如果任何一个环节发生了错误,操作都将被取消.
--创建存储过程
create proc bbb
as
declare @id int,@quan int,@sale float,@ywid int
--赋值变量
set @id=9
set @quan=3
set @sale=5
set @ywid=4
--事务开始
begin tran
insert into shop_xs values(@id,@sale,@quan,GETDATE(),@ywid)
if(@@error!=0)
begin
--事务回滚
rollback tran
print '插入失败'
return
end
else
begin
update shop_jb set stock=stock-@quan where id=@id
if(@@rowcount=0)
begin
rollback tran
print '更新失败'
return
end
else
begin
--提交事务
commit tran
print '操作成功'
end
end

--修改存储过程
alter proc bbb
as
declare @id int,@quan int,@sale float,@ywid int
--赋值变量
set @id=9
set @quan=3
set @sale=5
set @ywid=4
--事务开始
begin tran
insert into shop_xs values(@id,@sale,@quan,GETDATE(),@ywid)
if(@@error!=0)
begin
--事务回滚
rollback tran
print '销售商品失败'
return
end
else
begin
update shop_jb set stock=stock-@quan where id=@id
if(@@rowcount=0)
begin
rollback tran
print '库存量更新失败'
return
end
else
begin
--提交事务
commit tran
print '用户操作成功'
end
end

--删除存储过程
drop proc bbb

exec bbb