--表
create table SP
(
ID int primary key identity,
Name varchar(50), --商品名称
Price int, --价格
GZ varchar(50) --关注度
)
--创建存储过程
Go
create proc V_Add
as
begin
--创建游标
declare Adds cursor scroll
for
select * from SP
--打开游标
open Adds
--查询语句
declare @Price int
declare @IDs int
fetch next from V_O into @Price,@IDs
if(@Price>100)
update SP set Price=price+price*0.1 where ID = @ID
else if(@Price>50)
update SP set Price=price+price*0.05 where ID = @ID
while (@@FETCH_STATUS = 0)
begin
fetch next from V_O into @Price,@IDs
if(@Price>100)
update SP set Price=price+(Price*0.1) where ID = @ID
else if(@Price>50)
update SP set Price=price+(Price*0.05) where ID = @ID
end
--关闭游标
close V_O
--释放游标
deallocate V_o
end
--执行游标
exec V_Add
--删除
go
create proc Deletes
(
@Id int
)
as
begin
--创建游标
declare V_delete cursor scroll
for
select ID from UNT1
--打开游标
open V_delete
--查看数据
declare @SID int
fetch next from V_delete into @SID
if(@SID = @id)
delete from UNT1 where ID=@SID
while (@@FETCH_STATUS = 0)
begin
fetch next from V_delete into @SID
if(@SID = @id)
delete from UNT1 where ID=@SID
end
--关闭游标
close V_delete
--释放游标
deallocate V_delete
end
--修改
go
alter proc V_change
(
@CName varchar(50),
@China int,
@Math int,
@English int,
@Rema varchar(50),
@ID int
)
as
begin
--创建游标
declare Edi cursor scroll
for
select ID,SNAME,CHINA,MATH,ENGLISH,REMA from UNT1
--打开游标
open Edi
--查询数据
declare @CID int
declare @Name varchar(50),@Ch int,@sx int,@ey int ,@re varchar(50)
fetch next from Edi into @CID,@Name,@Ch,@sx,@ey,@re
if(@CID = @ID)
update UNT1 set SNAME=@CName,CHINA=@Ch,MATH=@sx,ENGLISH=@ey,REMA=@re where Id=@CID
while(@@FETCH_STATUS = 0)
BEGIN
fetch next from Edi into @CID,@Name,@Ch,@sx,@ey,@re
if(@CID = @ID)
update UNT1 set SNAME=@CName,CHINA=@Ch,MATH=@sx,ENGLISH=@ey,REMA=@re where Id=@CID
END
--关闭游标
close Edi
--释放游标
deallocate Edi
end