RETURNS VARCHAR(20) BEGIN
RETURN CAST(DATEPART(YY,@date) AS VARCHAR(4))+'年' +CAST(DATEPART(MM,@date) AS VARCHAR(4))+'月' +CAST(DATEPART(DD,@date) AS VARCHAR(4))+'日' END GO
SELECT dbo.formatdate(GETDATE())
7．编写一个函数，功能是查询学生及所借书籍的详细信息。 参考答案：
USE TSG GO
CREATE FUNCTION Selectdetails () RETURNS TABLE AS RETURN
SELECT Patron.Name,Patron.Gender,Patron.BirthDate,Patron.Type, Patron.Department,Book.*,Lend.LendTime,Lend.ReturnTime FROM Book, Lend,Patron
WHERE Book.CallNo=Lend.CallNo GO
SELECT * FROM Selectdetails()
第6章 存储过程、触发器及游标
1．简述什么是存储过程，存储过程有哪些优点?
参考答案：存储过程是一组为了完成特定功能的SQL语句的集合、它经编译后存储在数据库中，用户通过指定的调用方法执行之。存储过程具有名称，参数及返回值，并且可以嵌套调用，其优点如下：
（1）快速执行。存储过程已在服务器注册，在创建时就进行了分析和优化，当存储过程第一次执行后，就驻留在内存中，省去了重新分析优化工作，比T-SQL批代码执行快得多。
（2）安全性好。存储过程具有安全特性（例如权限）和所有权链接，用户可以被授予权限来执行存储过程而不必直接对存储过程中引用的对象具有权限，从而增强系统的安全性。另外，参数化存储过程有助于保护应用程序不受 SQL注入攻击。
（3）访问统一。存储过程允许模块化程序设计，存储过程一旦创建，以后即可在程序中调用任意多次。这可以改进应用程序的可维护性，并允许应用程序统一访问数据库，可以在C/S及B/S模式中进行统一调用。
（4）存储过程是命名代码，允许延迟绑定。
（5）减少网络通信流量。如一个需要数百行T-SQL代码的操作可以通过一条执行过程代码的语句来执行，而不需要在网络中发送数百行代码，降低网络通信开销。
2．SQL Server中如何执行存储过程?
参考答案：使用T-SQL的 EXECUTE 语句执行存储过程。如果存储过程是批处理中的第一条语句，可以省略EXECUTE 关键字。
3．SQL Server存储过程的输入、输出参数如何表示？如何使用？
参考答案：存储过程输入参数在创建时声明，使用CREATE PROCEDURE 存储过程名 后面跟随存储过程参数名称、类型及默认值，参数的个数可以0个或多个。如果是输出参数，还要用OUTPUT关键字声明。参数的传递可以用名字传递，也可以按位置传递。
4．简述存储过程与函数的区别。
参考答案：存储过程和函数都属于可编程的数据库对象，它们都是具有一定功能的SQL 语句的集合，且都可以带参数，但二者还是有很大区别，主要体现在：
（1）存储过程是预编译的，执行效率比函数高。
（2）存储过程可以不返回任何值，也可以返回多个输出变量，但函数有且必须有一个返回值。
（3）存储过程必须单独执行，而函数可以嵌入到表达式中，使用更灵活。 （4）存储过程主要是对逻辑处理的应用或解决，函数主要是一种功能应用。 5．什么是触发器？触发器分几类，DML触发器有什么优点？
参考答案：触发器实际上是一种特殊的存储过程，分为DML，DDL及登录触发器三类，在某种事件发生时触发，可以类似事件处理的功能。DML触发器的主要作用就是能够实现由主键和外键所不能实现的复杂的参照完整性和数据的一致性，也就是说，主要用于表间的完整性约束，除此之外，还可以用于解决高级形式的业务规则，复杂行为限制以及
实现定制记录，数据同步等。其优点有：（1）强化了约束的功能。（2）可以跟踪数据变化。（3）支持级联运行。（4）可以调用存储过程。
6．简述SQL Server的DML触发器分为哪几类，如何使用？
参考答案：（1）INSERT触发器：在将数据插入到表或视图时执行的特殊存储过程，当数据表的INSERT触发器执行时，新插入的数据行，将同时插入到该数据表和Inserted表。Inserted表是保存了已经插入的数据行复本的虚拟表，该表允许用户引用该表的数据，通过表对比插入数据的变化。
（2）DELETE触发器：是将数据从数据表或视图删除时，执行的特殊存储过程。当数据表的DELETE触发器执行时，从数据表被删除的数据首先被放在一个特殊的名字为Deleted的表中，该表是保存了已经被删除的数据的虚拟表，允许用户引用该表的数据，Deleted表和触发器表通常没有相同的行。
（3）UPDATE触发器：是在数据表或视图中修改数据时，执行的触发器，UPDATE触发器的处理过程与前两者不同，UPDATE触发器处理分为两个步骤，当该触发器执行时，原始数据被移到Deleted表，修改后的数据被插入到Inserted表，触发器将检查这两个表同时更数据表。
按照触发执行的方式，触发器分为AFTER触发器和INSTEAD OF触发器两种，其中AFTER触发器是在INSERT、UPDATE、DELETE命令执行完之后执行，只能在表上定义，而INSTEAD OF 触发器是当INSERT、UPDATE、DELETE语句执行时替代原有操作，其执行早于约束处理，晚于创建Inserted和Deleted表，可以在表上和视图上定义。
7．什么叫游标，游标有哪些优点？
游标是一种处理数据的方法，它可以对结果集进行逐行处理，也可以指向结果集的任意位置然后对该位置的结果集处理。游标的优点如下：
（1）在结果及对特定行进行定位。 （2）从结果集的当前位置检索数据行。
（3）支持对结果集中当前位置进行数据修改操作。
（4）为由其他用户对显示在结果集中的数据库数据所做的更改提供不同级别的可见性支持。
（5）支持在脚本、存储过程以及触发器中访问结果集中的数据。 8．简述使用游标处理数据的一般步骤。
参考答案：使用游标处理数据的典型过程包括声明、打开游标、通过FETCH逐行读取数据并进行处理，使用完之后，用CLOSE语句关闭游标，再通过DEALLOCATE语句释放游标的存储空间。
9．创建一存储过程，该存储过程创建一个包含100个随机数的临时表，这个临时表只有一个字段，类型为数值型，然后查询该临时表的数据，作为一个CURSOR返回给调用者。
参考代码如下：
CREATE PROCEDURE usp_GenRandom @tmpCursor CURSOR VARYING OUTPUT AS BEGIN
SET NOCOUNT ON
CREATE TABLE #tmpTable ( num FLOAT) DECLARE @i INT DECLARE @num FLOAT SELECT @i = 0 WHILE @i <100 BEGIN
SELECT @i = @i + 1 SELECT @num = RAND()
INSERT INTO #tmpTable( num) VALUES (@num) -- PRINT @I END
SET @tmpCursor = CURSOR SCROLL FOR SELECT * FROM #tmpTable OPEN @tmpCursor END
调用代码如下：
DECLARE @MyCursor CURSOR
EXEC usp_GenRandom @MyCursor OUTPUT FETCH NEXT FROM @MyCursor WHILE (@@FETCH_STATUS = 0) BEGIN
FETCH NEXT FROM @MyCursor END
CLOSE @MyCursor
DEALLOCATE @MyCursor
10．根据教材提供的TSG数据库，编写借书逻辑的存储过程，输入参数为读者证号以及图书的索书号，返回值定义如下：
返回值 0 1 2 3 其他
参考代码如下：
CREATE PROCEDURE usp_CheckOut @PatronID VARCHAR(20) = NULL, @CallNo VARCHAR(20) = NULL AS
含义 借书成功 读者证号不存在 索书号不存在 可借册数为0 借书失败
BEGIN
SET NOCOUNT ON;
IF (@PatronID IS NULL
OR (SELECT COUNT (*) FROM Patron WHERE PatronID=@PatronID)=0) RETURN(1) IF (@CallNo IS NULL
OR (SELECT COUNT (*) FROM Book WHERE CallNo=@CallNo)=0) RETURN(2) IF (SELECT AvailableNumber FROM Book WHERE CallNo=@CallNo)=0 RETURN(3)INSERT INTO Lend (CallNo,PatronID,LendTime ) VALUES (@CallNo ,@PatronID ,GETDATE()) IF @@ERROR <> 0
RETURN(4)
ELSE
RETURN(0)
END
调用代码如下
DECLARE @ret int
EXEC @ret=usp_CheckOut 'T0101','C52/J181B:7' SELECT @ret
11．在TSG数据库中,对Patron表编写删除触发器，如果该读者有外借图书，则不允许执行删除操作。
参考代码如下：
CREATE TRIGGER [dbo].[tri_Patron_D] ON [dbo].[Patron] AFTER DELETE AS BEGIN
SET NOCOUNT ON; IF Exists(
select * from Lend where PatronID in ( select PatronID from Deleted ) and ReturnTime is Null) BEGIN
PRINT '该读者有图书未还，不能删除!' ROLLBACK TRANSACTION END ELSE
DELETE FROM Patron where PatronID in (select PatronID from Deleted) END
12．编写程序，定义一个游标cur_Patron，通过读取cur_Patron数据行计算Patron表中男女读者的数量。
参考代码如下：