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

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