SQL Server基础（五）

chenqb

　　1、用户自定义函数
　　优点：
　　允许模块化程序设计
　　执行速度更快
　　减少网络流量
　　分类：
　　标量函数：返回一个标量（单值）结果
　　表值函数：返回 table 数据类型
　　内置函数：系统提供，返回标量数据类型或 table 数据类型
　　函数的限制：用户定义函数体中不允许使用内置非确定性函数，如：@@ERROR、 @@IDENTITY等
　　2、唯一、合法的用户定义函数名
　　CREATE FUNCTION 数据库名.架构名.函数名 （参数…）
　　语句指定输入参数及它们的数据类型、处理指令，以及返回的值及其数据类型
　　标量用户定义函数可在任何需要它们返回值的数据类型值的地方使用
　　表值用户定义函数只能用在 SELECT 语句的 FROM 子句中
　　3、架构绑定的函数
　　使用架构绑定：函数创建时使用 SCHEMABINDING 选项，创建后，所引用的数据库对象不能被更改
　　创建时指定SCHEMABINDING 选项
　　该函数引用的所有视图和用户定义函数都绑定到架构
　　函数引用的对象的引用格式：“架构者名.对象名”
　　位于同一数据库
　　用户权限：REFERENCE 权限
　　4、为用户定义函数设置权限
　　创建、更改或删除用户定义函数，必须具有 CREATE FUNCTION 权限
　　其他用户使用函数，则必须具有 EXECUTE 权限
　　若函数是架构绑定的，则创建者必须具有函数所引用的表、视图和函数上的 REFERENCE 权限

　　若在 CREATE TABLE 或>　　5、更改和删除用户定义函数
　　（1）更改函数：使用新的函数定义代替原来的函数定义，保留所有已分配的权限

　　>　　（2）删除一个用户定义函数：
　　DROP FUNCTION ……
　　6、标量函数
　　调用方式：架构名.函数名([参数列表])
　　使用：
　　可以使用 EXECUTE 语句调用：EXECUTE @返回变量 = 函数名 参数列表
　　当使用命名参数的时候，参数的次序不必按照在函数中声明的次序，但必须包括所有参数，不能省略任何参数
　　EXECUTE @返回变量 = 函数名 @变量=值 [,@变量 = 值]
　　标量函数返回 RETURNS 子句中定义的数据类型的单个数据值
　　在 BEGIN … END 块之间定义了函数体，包含返回值的一系列 Transact-SQL 语句
　　返回值可以是除了 text、ntext、image、cursor 、 timestamp 之外的任何数据类型
　　创建：
　　CREATE FUNCTION fn_DateFormat (@indate datetime, @separator char(1))RETURNS Nchar(20) AS BEGIN  RETURN    CONVERT(Nvarchar(20),datepart(mm,@indate))+ @separator + CONVERT(Nvarchar(20),datepart(dd,@indate)) + @separator + CONVERT(Nvarchar(20),datepart(yy,@indate)) END
　　调用：
　　SELECT dbo.fn_DateFormat(GETDATE(), ':')
　　7、多语句表值函数
　　调用方式：不需要指定“拥有者名”，但必须包括所有定义的参数
　　函数名 [参数列表]
　　函数体内允许有：赋值语句、流控制语句，DECLARE 语句，SELECT 语句，局部游标操作
　　使用：BEGIN 和 END 分隔了函数体
　　RETURNS 子句指定 table 作为返回的数据类型
　　RETURNS 子句定义了返回表的名字和格式
　　返回变量名的使用域限定于函数局部
　　注意：不允许出现返回非函数返回值的语句以及产生副作用的语句，不允许对数据库表的更新、全局游标语句、创建对象的语句、事务控制语句
　　创建：
　　CREATE FUNCTION fn_Employees (@length varchar(9))
　　RETURNS @fn_Employees table
　　(EmployeeID int PRIMARY KEY NOT NULL,
　　[Employee Name] nvarchar(61) NOT NULL)
　　AS
　　BEGIN
　　IF @length = 'ShortName'
　　INSERT @fn_Employees SELECT EmployeeID,LastName FROM Employees
　　ELSE IF @length = 'LongName'
　　INSERT @fn_Employees SELECT EmployeeID,(FirstName + ' ' + LastName) FROM Employees
　　RETURN
　　END
　　调用：SELECT * FROM dbo.fn_Employees('LongName')
　　8、内联表值函数：内联表值函数返回表，以在 FROM 子句中被引用，就像视图一样
　　使用：RETURN 子句在括号中包含单个 SELECT 语句，SELECT 语句的结果集构成函数所返回的表
　　函数体不由 BEGIN 和 END 分隔
　　RETURNS 指定 table 作为返回的数据类型
　　不必定义返回变量的格式，因为它由 RETURN 子句中的 SELECT 语句的结果集的格式设置
　　创建：
　　CREATE FUNCTION fn_CustomerNamesInRegion
　　( @RegionParameter nvarchar(30) )
　　RETURNS table
　　AS
　　RETURN
　　(
　　SELECT CustomerID, CompanyName
　　FROM Northwind.dbo.Customers
　　WHERE Region = @RegionParameter
　　)
　　调用：SELECT * FROM fn_CustomerNamesInRegion(N'WA')
　　9、触发器：是一种特殊的存储过程，它与表紧密相连，可以看作是表定义的一部分。当用户修改指定表或视图中的数据时，触发器将会自动运行。
　　格式：Create trigger 触发器名字
　　On {表名或视图名}
　　With encryption
　　{for|after|instead of}{delete|insert|update}
　　As
　　操作SQL语句
　　说明：1、for和after的作用相同，都是在触发器都在指定的事件之后。
　　Instead of是用触发器的操作替代原有的操作。
　　2、delete ，insert，update可以只有一个，也可以有多个
　　触发器不能执行的操作：
　　对数据库的操作
　　不允许对基表结构执行修改，删除等操作
　　对索引的操作
　　10、INSERT 触发器
　　工作过程：
　　（1）在定义了 INSERT 触发器的表上执行 INSERT 语句
　　（2）INSERT 语句插入的行被记录下来
　　（3）触发器动作被执行
　　（4）触发 INSERT 触发器时，新行被同时增加到触发器表和 inserted 表中
　　（5）inserted 表是保存了插入行的副本的逻辑表，它并不实际存在于数据库中
　　（6）inserted 表允许用户引用 INSERT 语句所插入的数据，这样触发器可以根据具体数据决定是否执行以及如何执行特定语句
　　创建INSERT 触发器：建一个日志记录表，用来存放操作的日志
　　use mydb
　　go
　　create table Roc
　　(

　　>　　op_name varchar(20),
　　op_des varchar(40)
　　)
　　create trigger stu_insert_trig
　　on stu
　　after insert
　　as
　　begin
　　insert into roc values('insert','插入操作')
　　end
　　测试INSERT 触发器：
　　insert into stu values('张三',20,1)
　　select * from roc
　　11、UPDATE 触发器
　　工作过程：
　　（1）UPDATE 语句可以考虑为两个步骤：DELETE 步骤捕获数据的前像，INSERT 步骤捕获数据的后像
　　（2）当在定义了触发器的表上执行 UPDATE 语句的时候，原行（前像）被移到 deleted 表中，而更新的行（后像）则插入 inserted 表中
　　（3）触发器可以检索 deleted 和 inserted 表以及被更新的表，来确定是否更新了多行以及如何执行触发器动作
　　（4）监视对特定列的更新：IF UPDATE ()
　　允许触发器监测特定列，以对特定列的更新作出反应
　　创建update触发器：
　　create trigger stu_update_trig
　　on stu
　　after update
　　as
　　begin
　　insert into roc values('update','更新操作')
　　end
　　--测试语句

　　update stu set stuname='李四' where>　　select * from roc
　　12、DELETE 触发器
　　工作过程：
　　（1）在定义了 DELETE 触发器的表上执行 DELETE 语句
　　（2）DELETE 语句删除的行被记录下来
　　（3）触发器动作被执行
　　（4）触发 DELETE 触发器时，被删除的行放入 deleted 表中
　　（5）当行添加到 deleted 表后，将不再存在于数据库表中
　　（6）从内存中分配空间创建 deleted 表
　　（7）DELETE 触发器不会被 TRUNCATE TABLE 语句触发，因为 TRUNCATE TABLE 语句不记录在日志中
　　创建delete触发器：
　　create trigger stu_delete_trig
　　on stu
　　after delete
　　as
　　Begin
　　insert into roc values('delete','删除操作')
　　end
　　--测试语句

　　delete  stu  where>　　select * from roc
　　创建instead of触发器：
　　create trigger stu_instead_trig
　　on stu
　　instead of insert
　　as
　　begin
　　insert into stu values('无名',0,0)
　　end
　　--测试语句
　　insert into stu values('张三',19,1)
　　select * from  stu
　　13、嵌套触发器
　　工作过程：
　　（1）一个触发器在执行操作时引发了另一个触发器
　　（2）触发器可嵌套至32层
　　（3）嵌套触发器配置选项默认是打开的
　　（4）一个嵌套触发器在同一触发器事务中不会被触发两次
　　（5）由于触发器在事务中执行，如果在一系列嵌套触发器的任意层中发生错误，则整个事务都将取消，且所有的数据修改都将回滚
　　修改，删除，禁用，解禁触发器：
　　--修改触发器

　　>　　on stu
　　instead of insert
　　as
　　begin
　　insert into stu values(‘真的无名',0,0)
　　End
　　--删除触发器
　　drop trigger stu_instead_trig
　　--禁用触发器

　　>　　disable trigger stu_insert_trig
　　--解禁触发器

　　>　　enable trigger stu_insert_trig
　　14、游标
　　例：use northwind
　　go
　　--定义游标
　　declare order_cursor cursor
　　local scroll static
　　for
　　select top 10 orderid from orders
　　--打开游标
　　open order_cursor
　　--print 'order count：'+cast(@@cursor_rows as varchar)
　　--检索游标
　　declare @tempid int
　　fetch next from order_cursor
　　into @tempid
　　select orderid,count(*) as sl from [order details] where orderid=@tempid group by orderid
　　while @@fetch_status=0
　　begin
　　fetch next from order_cursor
　　into @tempid
　　select orderid,count(*) as sl from [order details] where orderid=@tempid group by orderid
　　end
　　--关闭游标
　　close order_cursor
　　--释放游标
　　deallocate order_cursor