数据库原理与应用课程实验指导书
2、用户自定义数据类型定义、使用和删除
用SQL命令定义一名为Customer_id的用户自定义数据类型，要求char(6)，NOT NULL， 并把该自定义数据类型用来定义XSS表中的客户编号，然后删除该自定义数据类型，请叙述该过程，并写出相关语句。
sp_addtype 'Customer_id','char(6)','not null' sp_droptype 'Customer_id'
先用创建语句自定义数据类型，然后打开“数据库-CPXSB-表”，右击XSS,选择“设计表”，将客户编号的数据类型 改为Customer_id,保存即可
将客户编号的数据类型改回char(6),然后用第二个语句删除该用户自定义数据类型
3、T-SQL语言编程
（1）用T-SQL语言编程输出3～300之间能被7整除的数。 declare @i int,@j int set @i=3 set @j=7
while @i<=300 begin
if(@i%@j)=0 print @i
set @i=@i+1 end
（2）用T-SQL语言编程输出3～100之内的素数。 declare @i int,@j int,@t int set @i=3
while @i<100 begin
set @j=2 while @j<@i begin
set @t=@i%@j if @t=0 break set @j=@j+1
45
if(@j>=@i) print @i end
set @i=@i+1 end
实验十
1、函数的定义
对于CPXS数据库，定义完成如下功能的函数： （1）据产品名称，查询该产品的相关信息；（函数名为FU_CP） create function FU_CP(@产品名称 char(30)) returns table as return
select * from CP where 产品名称=@产品名称
（2）按某年某季度统计给定产品名称的销售数量及销售金额；分别用名为FU1_CPXS内嵌表值函数和名为FU2_CPXS的多语句表值函数。
create function FU1_CPXS(@year int,@quarter int,@产品名称 char(30)) returns table as return
select 产品名称,sum(数量) as 销售数量,sum(销售额) as 销售金额 from CP,CPXSB
where CP.产品编号=CPXSB.产品编号 and datepart(yy,销售日期)=@year and datepart(qq,销售日期)=@quarter and 产品名称=@产品名称 group by 产品名称
create function FU2_CPXS(@year int,@quarter int,@产品名称 char(30)) returns @f2 table (
产品名称 char(30), 销售数量 int, 金额 real ) as begin
insert into @f2
select 产品名称,sum(数量) as 销售数量,sum(销售额) as 金额 from CP,CPXSB
where CP.产品编号=CPXSB.产品编号 and datepart(yy,销售日期)=@year
46
数据库原理与应用课程实验指导书
and datepart(qq,销售日期)=@quarter and 产品名称=@产品名称 group by 产品名称 return end
（3）根据销售商名称，统计其在某年某季度内销售商品名称、数量及金额。（函数名为FU3_CPXS）
create function FU3_CPXS(@客户名称 char(30),@year int,@quarter int) returns table as return
select 客户名称,产品名称 as 销售商品名称,数量,销售额 as 金额 from CP,XSS,CPXSB
where CPXSB.产品编号=CP.产品编号 and CPXSB.客户编号=XSS.客户编号
and 客户名称=@客户名称 and datepart(yy,销售日期)=@year and datepart(qq,销售日期)=@quarter
2、函数的调用
（1）对函数FU_CP，查询产品名称为“mp3”的产品情况； select * from FU_CP('MP3')
（2）对函数FU1_CPXS，查询2004年第3季度彩色电视机的销售数量和销售金额； select * from FU1_CPXS(2004,3,'彩色电视机')
（3）对函数FU2_CPXS，查询2004年第1季度洗衣机的销售数量和销售金额；
select * from FU2_CPXS(2004,1,'洗衣机')
（4）对函数FU3_CPXS，查询广电公司2004年第1季度销售的产品名称、销售数量和销售金额。
select * from FU3_CPXS('广电公司',2004,1)
47
实验十一 对于CPXS数据库，完成如下操作： 1、索引的创建和删除
（1）对CP表，在产品名称上定义一个唯一非聚簇的索引ind_cp。 create unique nonclustered index on CP(产品名称)
（2）先创建各客户购买产品的情况VIEW1视图，包括客户编号、客户名称、产品编号、产品名称、
价格，购买日期、购买数量，然后在客户编号+产品编号+购买日期定义一个唯一聚簇索引ind_view1。（请注意7个SET选项设置）。 create view view1 with schemabinding as
select XSS.客户编号,客户名称,CP.产品编号,产品名称,价格,销售日期 as 购买日期,数量 as 购买数量
from dbo.CP,dbo.XSS,dbo.CPXSB
where XSS.客户编号=CPXSB.客户编号 and CP.产品编号=CPXSB.产品编号
SET ANSI_NULLS ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF
create unique clustered index ind_view1 on dbo.view1(客户编号,产品编号,购买日期)
2、索引的删除
删除ind_view1索引。； drop index view1.ind_view1
48