大型数据库课堂测验答案

课堂测验

一、程序设计题

1.创建一个自定义函数Date_Now来显示当前日期和时间，可使用GETDATE()

和DATENAME()函数。例如当前时间是：2010-11-20 23:45:40，函数返回显示：“今天是2010年11月20日23时45分40秒星期六”。

create function Date_Now()

returns table

as

return(SELECT '今天是'+DATENAME(yy,getDate())+'年'

+ DATENAME(mm,getDate())+'月'

+ DATENAME(dd,getDate())+'日'

+ DATENAME(hh,getDate())+'时'

+ DATENAME(mi,getDate())+'分'

+ DATENAME(ss,getDate())+'秒'

+ DATENAME(dw,getDate()) as 返回

)

select * from Date_Now()

2.下面是一段程序代码，请回答问题。

CREATE FUNCTION mydefine_fun (@var1 int,@var2 int,@var3 int)

RETURNS int

AS

BEGIN

DECLARE @result int

SET @result=(@var1+@var2)*@var3

RETURN @result

END

试分析程序的功能是什么？并解释参数和返回值的作用？调用程序代码如下select dbo.mydefine_fun(1,2,3) ，返回显示结果为9。

二、触发器设计题

有一数据库，包含两张表：学生（学号，姓名，专业名称，性别，课程号，成绩）课程（课程号，课程名称，课程类型，学分）表中数据如下：

1.为学生表创建触发器S_insert，当向学生表中插入数据时，要求学号必须以

“10”开头，且课程号必须在课程表中，否则取消插入操作。

当向学生表中插入数据时：

INSERT INTO 学生 VALUES('00702','秦永','机械工程','男',2,78)

INSERT INTO 学生 VALUES('10702','秦永','机械工程','男',12,78)

它们的运行结果分别是什么？

CREATE TRIGGER S_insert

ON 学生

FOR INSERT AS

DECLARE @S_no VARCHAR(4), @S_cno INT

SELECT @S_no= 学号, @S_cno=课程号

FROM INSERTED

IF (LEFT(@S_no,2)!='10')

BEGIN

ROLLBACK TRANSACTION

RAISERROR('输入的学号：%s不是10级的学生，请确认后重新录入！',16,1, @S_no)

END

IF(@S_cno NOT IN (SELECT CNO FROM COURSE))

BEGIN

ROLLBACK TRANSACTION

RAISERROR('输入的课程号：%d在课程表中不存在，请确认后重新录入！

',16,1, @S_cno)

END

INSERT INTO 学生 VALUES('00702','秦永','机械工程','男',2,78)

由于插入数据的学号为“00702”，并不是以“10”开头，所以执行S_insert 触发器时，将执行“ROLLBACK TRANSACTION”语句，取消完成的工作，并执行RAISERROR语句给出错误信息。运行结果如下。

输入的学号：00702不是10级的学生，请确认后重新录入！

INSERT INTO 学生 VALUES('10702','秦永','机械工程','男',12,78)

由于课程号12在课程表中不存在，所以执行S_insert触发器时，将执行“ROLLBACK TRANSACTION”语句，取消工作，并执行RAISERROR语句给出错误信息。运行结果：

输入的课程号：12在课程表中不存在，请确认后重新录入！

2.为学生表创建DELETE触发器S_delete，当一次删除的记录大于一行或者删

除记录的课程为必修课时，取消删除操作。部分代码如下：

CREATE TRIGGER S_delete

ON 学生

FOR DELETE AS

DECLARE @rowcount int

SELECT @rowcount=@@ROWCOUNT --@@ROWCOUNT返回受上一语句影响的行数

IF @rowcount>1

BEGIN

Rollback transaction

RAISERROR('当前要删除的记录数为%d,一次只允许删除一行记录！',16,1,

@rowcount)

END

DECLARE @S_cno INT

SELECT @S_cno=课程号

FROM DELETED

IF( @S_cno in (select 课程号 from 课程 where 课程类型=’必修’) ) BEGIN

ROLLBACK TRANSACTION

RAISERROR('删除记录的课程为必修课，不允许删除！',16,1)

END

当通过如下语句从学生表中删除数据时：

DELETE 学生 WHERE 学号=’10201’

DELETE 学生 WHERE 学号=’10201’ AND 课程号=1

它们的运行结果是分别是什么？

DELETE 学生 WHERE 学号=’10201’，结果如下：

当前要删除的记录数为2,一次只允许删除一行记录！

DELETE 学生 WHERE 学号=’10201’ AND 课程号=1 ，结果如下

删除记录的课程为必修课，不允许删除！

说明在SQL Server中执行TRUNCATE TABLE语句，从表中删除所有的行时，不会触发DELETE触发器。

3.为课程表创建UPDATE触发器C_update，当课程表的课程号和课程名称列（第

1、2列）被更新时，触发器给出提示信息，该两列不能被更新，并回滚事务。

当其余的列（第3、4列）被更新时，触发器将更新前后的数据写入C_UpInfo 表中。实例代码如下。

（1）．创建跟踪信息表C_UpInfo

CREATE TABLE C_UpInfo

(

Oper_Time smalldatetime,--更新时间

Date_Type CHAR(3),--更新类型：值只能为old或new

CNO int ,--课程号

CNAME char(30),--课程名称

CTYPE char(4),--课程类型

SCOUNT int,--学分

)

（2）．创建UPDATE触发器C_update

CREATE TRIGGER C_update

ON 课程

FOR UPDATE AS

IF（COLUMNS_UPDATED()&3）>0 --第1、2列被更新时

BEGIN

ROLLBACK TRANSACTION

RAISERROR('课程表的课程号和课程名中的数据不允许被更新！',16,1)

END

IF（COLUMNS_UPDATED()&12）>0 --第3、4列被更新时

BEGIN

--将更新前的数据插入到C_UpInfo表中（deleted表）

Insert into C_UpInfo(Oper_Time,Date_Type,CNO,CNAME,CTYPe,SCOUNT_) Select getdate(),’old’,d.课程号,d.课程名称,d.课程类型,d.学分

From Deleted as d

--将更新后的数据插入到C_UpInfo表中（inserted表）

Insert into C_UpInfo(Oper_Time,Date_Type,CNO,CNAME,CTYPe,SCOUNT_) Select getdate(),’new’,i.课程号,i.课程名称,i.课程类型,i.学分

From Inserted as i

END

当通过下面的代码更新课程表的课程名称列的值：

UPDATE 课程

SET 课程名 = '线性代数'

WHERE 课程号 = 2

运行后的结果是什么？

课程表的课程号和课程名中的数据不允许被更新！

当更新课程号和课程名称以外的值：

UPDATE 课程

SET 课程类型=选修,学分=1

WHERE 课程号 = 2

三、存储过程设计题

产品销售数据库中包含如下几张表：product(产品表)、employee(员工表)、Customer(顾客表)、categories(产品类别表)、orders(订单表)、orderDetail(订单详表)等。其中，product表结构为：产品编号、产品名、类别号、单价、库存量、产品描述。Employee表结构为：员工编号、姓名、性别、入厂时间、电话号码、地址。Customer表结构为：顾客编号、姓名、性别、联系电话、地址。Categories 表结构为：类别号、类别名称、详细描述。Orders表结构为：订单号、顾客编号、员工编号、订单日期、发货日期、订单描述。orderDetail表结构为：订单号、产品编号、单价、数量、折扣、备注。

1.试分析orders表和orderDetail表之间的关系。(一对多)

2.创建一个存储购物车信息的表ShoppingCart，然后创建一个存储过程

ShoppingCartAddItem，将参数指定的产品ID及要选购的产品数量添加到参数指定的购物车中。

创建购物车的代码如下：

Create table ShoppingCart(

cartID varchar(20) not null,--购物车编号

quantity int default(1) not null,--选购数量

productID int not null foreign key references product(产品编号), dateCreated datetime default(getdate()) not null,--选购日期

constraint PK_ShoppingCart primary key (cartID,productID)—主键

)

创建存储过程的部分代码如下：

Create procedure ShoppingCartAddItem(

@CartID varchar(20),

@ProductID int,

@Quantity int=1)

As

If Exists( Select productID from ShoppingCart where productID=@ProductID and cartID=@CartID )

/*购物车中有指定的产品，则更新该产品的数量*/

Begin

Declare @countItems int

Select @countItems =ShoppingCart.quantity

From ShoppingCart

Where productID=@ProductID and cartID=@CartID

Update shoppingCart set quantity=(@Quantity+@countItems)

Where productID=@ProductID and cartID=@CartID

End

Else

/*购物车中没有指定的产品，则向购物车添加一条该产品的记录*/ Insert into ShoppingCart( cartID,Quantity,productID)

Values(@CartID,@Quantity,@ProductID)

Go

3.创建存储过程ShoppingCartEmpty来清空购物车。

Create procedure ShoppingCartEmpty(

@CartID varchar(20)

)

As

Detele from ShoppingCart where cartID=@CartID

Go

4.创建存储过程ShoppingCartTotal返回购物车中产品的总价格。

Create procedure ShoppingCartTotal(

@CartID varchar(20),

@TotalCost money OUTPUT

)

As

Select @TotalCost=sum(product.单价*ShoppingCart.quantity)

From product,ShoppingCart

Where product.产品编号=ShoppingCart.productID and

ShoppingCartID=@CartID

Go

5.创建存储过程ShoppingCartRemoveItem删除购物车中指定的产品。Create procedure ShoppingCartRemoveItem(

@CartID varchar(20),

@ProductID int

)

As

Delete from ShoppingCart

Where cartID=@CartID and productID=@ProductID

6.创建存储过程OrdersList获取指定用户的订单列表。

Create procedure OrdersList

(

@CustomerID int

)

As

Select orders.订单号,订单日期，发货日期，orderDetail.产品编号，数量From orders,orderDetail

Where orders.订单号=orderDetail.订单号 and

Orders.顾客编号=@CustomerID