数据库原理与应用课程实验指导书

select CP.产品编号,产品名称,客户编号,销售日期,数量,销售额 from CP,CPXSB

where CP.产品编号=CPXSB.产品编号 and 产品名称=@产品名称

exec xsqk '彩色电视机'

4、带有OUTPUT参数的存储过程

编写一存储过程，查询指定客户在指定时间段内购买指定产品的数量，存储过程中使用了输入和输出参数。

并调用该存储过程查询名称为“家电市场”的客户在2004年购买“洗衣机”的数量。 create procedure gmqk2 @客户名称 char(30),@year char(4),@产品名称 char(30),@数量 int output

as select @数量=sum(数量) from CP,XSS,CPXSB

where CP.产品编号=CPXSB.产品编号 and XSS.客户编号=CPXSB.客户编号

and 客户名称=@客户名称 and datepart(yy,销售日期)=@year and 产品名称 = @产品名称

declare @数量 int

exec gmqk2 '家电市场','2004','洗衣机',@数量 output select @数量 as 购买数量

5、带有OUTPUT游标参数的存储过程

编写一带有OUTPUT游标参数的存储过程，游标结果集为客户信息，并通过调用该存储过程，实现依次读取游标CUR2中各行数据。

create procedure kh_cursor @kh_cursor cursor varying output as

set @kh_cursor=cursor for select * from XSS

open @kh_cursor

declare @kh cursor

exec kh_cursor @kh_cursor=@kh output fetch next from @kh

while @@fetch_status=0 fetch next from @kh close @kh deallocate @kh

53

实验十四

对于CPXS数据库，完成如下各项：

1、编写存储过程，对产品销售表进行插入操作，并通过触发器保证插入时，产品编号与CP表中的对应字段一致，

销售商编号与销售商表中对应字段一致。其中触发器分别用后触发器和替代触发器实现。

create trigger insert_exists1 on CPXSB for insert as

if not exists (select * from CP where 产品编号=(select 产品编号 from inserted)) or

not exists (select * from XSS where 客户编号=(select 客户编号 from inserted) ) begin

raiserror('数据不一致',16,1) rollback transaction end

create trigger insert_exists2 on CPXSB instead of insert as begin

if not exists (select * from CP where 产品编号=(select 产品编号 from inserted)) or

not exists (select * from XSS where 客户编号=(select 客户编号 from inserted) ) begin

raiserror('数据不一致',16,1) rollback transaction end

else insert CPXSB select * from inserted end

create procedure insert_cpxsb @产品编号 char(6),@客户编号 char(6),@销售日期 datetime,@数量 int,@销售额 real as

insert into CPXSB values(@产品编号,@客户编号,@销售日期,@数量,@销售额) exec insert_cpxsb '','','2006-5-18',3,5600

alter table CPXSB

add constraint CPXSB_PK primary key (产品编号,客户编号,销售日期)

54

数据库原理与应用课程实验指导书

2、在CPXSB上创建一后触发器，若对产品编号列和客户编号列修改，则给出提示信息，并取消修改操作，用两种方法实现。

create trigger update_cpxsb1 on cpxsb for update as

if update(产品编号) or update(客户编号) begin

raiserror('不允许修改',16,1) rollback transaction end

create trigger update_cpxsb2 on cpxsb instead of update as

if(columns_updated()&3)>0 begin

raiserror('不允许修改',16,1) rollback transaction end

drop trigger insert_exists2

update cpxsb set 产品编号='' where 产品编号=''

实验十五

1、创建Windows NT登录帐户

在SQL Server中添加一个帐户名为“WANG”的Windows NT登录帐户。 sp_grantlogin \

sp_revokelogin \ 2、创建SQL Server登录帐户

在SQL Server中添加一个帐户名为“LIU”，密码为“”的SQL Server登录帐户。 sp_addlogin \

sp_droplogin \ 3、添加服务器角色成员

将“WANG”这个Windows NT登录帐户添加到系统管理员服务器角色中。 sp_addsrvrolemember \

sp_dropsrvrolemember \

55

4、添加一个登录帐户为某个数据库的用户 将“LIU” 这个SQL Server登录帐户添加为CPXS数据库中一个用户，名称也为“LIU”。 sp_grantdbaccess \

5、添加数据库角色

在CPXS数据库中添加一个名为“ROLE”的角色。 sp_addrole \ 6、添加数据库角色成员

将CPXS数据库中名为“LIU”这个用户添加为“ROLE”角色成员。 sp_addrolemember \

7、用户、角色的授权

授予“LIU”用户和“ROLE”角色对CPXSB表的查询权限和数量列的修改权限。 grant select ,update(数量) on CPXSB to LIU with Grant option grant select,update(数量) on CPXSB to role with Grant option 8、收回用户、角色的

收回“LIU”用户的所有权限。并查看是否能打开CPXSB表。 revoke all from LIU 9、拒绝用户、角色的权限

重新授予“LIU”用户对CPXSB表的查询权限和数量列的修改权限。再拒绝该用户的所有权限。并查看是否能打开CPXSB表，

并通过此实例阐述收回权限与拒绝权限的区别。 deny all to LIU

56