SQL Server Sql Server学习笔记整理.docx
- 文档编号:11913427
- 上传时间:2023-06-03
- 格式:DOCX
- 页数:12
- 大小:20.21KB
SQL Server Sql Server学习笔记整理.docx
《SQL Server Sql Server学习笔记整理.docx》由会员分享,可在线阅读,更多相关《SQL Server Sql Server学习笔记整理.docx(12页珍藏版)》请在冰点文库上搜索。
SQLServerSqlServer学习笔记整理
一、SQLServer为什么要与CLR集成
1、SQLServer提供的存储过程、函数等十分有限,经常需要外部的代码来执行一些繁重的移植;
2、与CLR集成可将原本需要独立的程序来实现的功能迁移到SQLServer内部进行数据操作;
3、T-SQL数据查询语言在返回数据集方面很好,但是除此之外表现不佳。
与CLR的集成可解决这一问题;
4、.NET的操作代码和执行的速度比T-SQL快的很多。
.NET程序是已经编译好的二进制代码,而不是作为存储过程来构建,不再编译就直接可运行。
二、SQLServer中的程序集(编译、添加、修改、删除)
只有在添加了程序集后才能在该程序集的基础上建立CLR存储过程、CLR函数等。
1、CLR代码(编译)→DLL文件(注册)→SQLServer(作为数据库对象)→执行数据库操作过程如下:
(1)将托管程序编写为一组类定义。
编写好代码后编译成一个DLL文件;
存储过程、用户自定义函数、触发器的编写为类的静态方法;
用户自定义类型、聚合函数编写为一个结构体。
(2)DLL文件上传SQLServer磁盘上,并使用createassembly将DLL程序集存储到系统目录;
(3)创建SQL对象(函数、存储过程、触发器等)并将其绑定到程序集的入口点;
存储过程:
createprocedure
用户自定义函数:
createfunction
触发器:
createtrigger
用户自定义类型:
createtype
聚合函数:
createaggregate
(4)像使用T-SQL例程一样使用。
2、SQLServer中的程序集(创建程序集并上载到SQLServer实例然后创建数据库对象)
(1)SQLServer2008默认情况下禁用了CLR集成的功能,必需先启用CLR集成后才能在SQLServer访问.NET对象。
启用CLR集成
execsp_configure'showadvancedoptions','1';
go
reconfigure;
go
execsp_configure'clrenabled','1';//开启CLR集成
go
reconfigure;
go
解释
(2)将DLL程序集添加到SQLServer中。
在SQLServer中添加程序集使用createassembly命令。
createassemblyassembly_name(程序集名)
[authorizationowner_name]
from{
[withpermission_set={safe|external_access|unsafe}]
其中,
表示程序集所在的本地位置或网络位置以及与程序集对应的清单文件名。
表示组成程序集和依赖程序集的二进制值的列表。
permission_set={safe|external_access|unsafe:
表示指定SQLServer访问程序集时相程序集授予的一组访问权限,默认值为safe。
(3)修改程序集
alterassemblyassembly_name
[from
[with
[dropfile{file_name[,....n]|all}]
[addfilefromclient_file_specifier[asfile_name]|file_bitsasfile_name}[,....n]][;]
其中,
:
=permission_set=[{safe|external_access|unsafe}|visibility={on|off}|uncheckeddata],其中visibility={on|off}:
指示在创建CLR函数、存储过程、触发器、用户定义的类型以及用户自定义聚合函数时,该程序集是否可见。
如果设置为OFF则程序集只能由其他程序集调用。
uncheckeddata:
默认情况下,如果alterassembly必须验证各个表行的一致性,则他将失败。
该选项使得用户可以通过使用DBCCCHECKTABLE将检查推迟到以后的某个时间进行。
A、为程序集添加文件:
alterassemblyassembly_name
addfilefromclient_file_specifier[asfile_name]|file_bitsasfile_name}[,....n]][;]
B、更新程序集:
usedatabase_name
go
alterassemblyassembly_name
dropfileall
go
alterassemblyassembly_name
from
addfilefromclient_file_specifier[asfile_name]|file_bitsasfile_name}[,....n]][;]
(4)删除程序集
删除程序集是,将从数据库中删除程序集和它的所有关联文件,如,源代码和调试文件等。
但如果该程序集被其他对象引用则返回错误。
dropassemblyassembly_name[,....n]
[withnodependents]
其中,withnodependents:
表示只删除assembly_name而不删除该程序集引用的相关程序集。
如果不指定它,则dropassembly将删除assembly_name和所有相关程序集。
三、创建CLR函数(Function)
要创建被SQLServer引用的CLR程序则需要引用Microsoft.SqlServer.Server命名空间,创建CLR函数还需要使用该命名空间下的SqlFunctionAttribute特性类即将[Microsoft.SqlServer.ServerSqlFunction.]放置CLR函数的头部。
1、创建CLR标量值函数
(1)使用C#编写CLR标量值函数在VS2010中创建CLR函数后,编译成DLL文件,并将该文件添加到数据库中。
(2)在SQLServer中使用CLR标量值函数使用createfunction创建引用注册程序集的函数。
createfunction--[schema_name.]function_name//[schema_name.]如:
[dbo.]
(
{@parameter_name [as][type_schema_name.]parameter_data_type[=default]}[,....n]
)
return{return_date_type}
[with
[as]externalnameassembly_name.class_name.method_name
其中externalnameassembly_name.class_name.method_name:
指定将程序集与函数绑定的方法。
:
={[returnsnullonnullinput|callednonullinput]|[execute_as_clause]}其中returnsnullonnullinput|callednonullinput]|[execute_as_clause]:
指定标量值函数的onNULLCall属性。
如果未指定,则默认值为calledonnullinput。
这意味着即使传递的参数为null,也将执行函数体。
如果在CLR函数中指定了returnsnullonnullinput,它指示当SQLServer接收到的任何一个参数为null时,它可以返回null,而无须实际调用函数体。
优先采用createfunction语句指示的属性。
不能为表值函数指定Onnullcall属性。
2、创建CLR表值函数
(1)使用C#编写CLR表值函数
CLR表值函数只返回一个表,在.NET中中创建对应的函数,返回的结果是一个IEnumerable接口,用于表示一个集合。
集合中是对象的实例并不是SQLServer中所识别的表,因此需要在函数的属性中指定FillRowMethodName,这个参数的值是用于将.NET中的对象转换为表列的函数名。
即将特性[Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName="FillSplitTable")]放置与于表值函数的头部,以指定该特性下的函数为CLR表值函数。
其中,FillSplitTable是将.NET中的对象转换为表列的函数名。
还有用于将.NET中的对象转换为表列的方法必须为静态方法。
第一个参数必须为System.Object类型,接下来的参数的个数就是列的个数。
同时接下来的参数都必须声明为ref参数。
SQLServer中返回的列的数据类型和顺序必须与该函数中ref参数的数据类型和顺序相同。
编写完后编译成DLL文件并添加到数据库中。
(2)在SQLServer中使用CLR表值函数
A、更新程序集
要在SQLServer中使用C#编写的CLR表值函数,必须先更新程序集。
如:
alterassemblyassembly_name
from'程序集地址'
withpermission_set=safe
B、创建CLR表值函数
createfunction[schema_name.]function_name
(
{@parameter_name[as][type.schema_name.]
parameter_data_type[=default]}[,...n]
)
returntable
[with
[order(
[as]externalnameassembly_name.class_name.method_name[;]
其中,
:
=({column_namedata_type}[,...n])定义CLR函数的表数据类型。
表声明仅包含列名称和数据类型。
表始终放在主文件组中。
order(
3、在T-SQL中使用CLR函数
四、创建CLR存储过程(Procedure)
1、使用C#编写CLR存储过程所需的函数:
在C#中编写可用于CLR存储过程引用的函数必须使用SqlProcedure属性标识。
存储过程不需要返回值,所以在C#中建立void函数即可。
存储过程一般用于查询并生成一个查询的表,在c#中需要使用SqlPipe对象将表格结果与信息传回给客户端。
一般,通过SqlContext类的Pipe属性获得SqlPipe对象,后调用Pipe对象的Send()方法将表格结果或信息传送给客户端,或者使用SqlPipe对象的ExecuteAndSend()方法将查询结果传送给客户端。
ExecuteAndSend()方法提供了一种高效率的方式将查询结果传送给客户端。
使用特性[Microsoft.SqlServer.Server.SqlProcedure]放置在存储过程调用的函数的头部,用以标示该函数是作为CLR存储过程被调用的,CLR存储过程对应的函数。
将C#编写的代码编译成DLL文件,并添加到数据库中。
2、在SQLServer中使用CLR存储过程
create{proc|procedure}[schema_name.]procedure_name[;number]
[
{@parameter[type_schema_name.]data_type}
[varying][=default][out|output][readonly]
][,...n]
[with
[forreplication]
asexternalnameassembly_name.class_name.method_name[;]
其中,externalnameassembly_name.class_name.method_name指定.netframework程序集的方法,以便程序集引用。
class_name必须存在与该程序集中,而且指定的方法必须为该类的静态方法。
:
=[encryption][recompile]
3、创建有output参数的CLR存储过程
存储过程中也可以使用output参数,带有output的参数的值在存储过程内部被修改后也会将修改应用到存储过程外部相当于指针和ref参数。
output参数对应于C#中的ref参数。
4、在T-SQL中使用CLR存储过程
五、创建CLR触发器(Trigger)
触发器是数据库服务器中发生时间事自动执行的特殊存储过程。
DML触发器:
如果用户通过DML事件数据,则执行DML触发器。
DML事件是针对表或视图的insert、update、或delete语句。
DDL触发器:
用于响应各种DDL事件,主要是create、alter、drop语句。
1、使用C#编写CLR触发器
为了能够在C#中处理触发器触发时的情况,Microsoft.SqlServer.Server命名空间提供了SqlTriggerContext类。
SqlTriggerContext类提供所激发的触发器的上下文信息,通过SqlContext.TriggerContext来获得。
通过TriggerAction来获得触发的类型,SqlTriggerContext.TriggerAction属性指示激发触发器的操作。
在使用C#编写CLR触发器是有可能用到触发器中的俩张特殊的表:
insert和deleted的时候需要使用SqlCommand.如:
SqlConnectionconnection=newSqlConnection("contextconnection=true");
connection.Open();//打开链接
SqlCommandsqlcom=newSqlCommand();
sqlcom.CommandText="Select*from"+"inserted";//使用到inserted表
reader=sqlcom.ExecuteReader();//执行SQL语句
reader.Read();//读取数据
for(intcolumnNumber=0;columnNumber {//将每一列的列名通过pipe.Send方法发送到客户端 Pipe.Send("UpdateColumn"+reader.GetName(columnNumber)+"? " +triggerContext.IsUpdateColumn(columnNumber).Tostring()); } reader.Close();//关闭链接将C#编写的代码编译成DLL文件后添加到数据库并更新SQLServer中的程序集。 2、在SQLServer中使用CLR触发器 将程序集中的触发器函数添加到SQLServer中,需要用到createtrigger命令。 createtrigger[schema_name.]trigger_name on{table|view} [with {for|after|insteadof} {[insert][,][update][,][delete]} [withappend] [notforreplication] asexternalnameassembly_name.class_name.method_name 其中,externalnameassembly_name.class_name.method_name用于指定程序集与触发器绑定的方法。 该方法不带任何参数而且必需返回空值。 3、在T-SQL中使用CLR触发器 六、创建用户定义聚合函数(Aggregate) 在SQLServer中,经常需要对数据按组进行自定义的聚合操作,默认的聚合函数只有SUM(),MAX(),MIN(),AVG()等,因此就需要定义用户自定义聚合函数。 1、使用C#编写聚合函数 创建用户自定义聚合函数必须使用特性[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.Native)]放置聚合函数的头部,以标识该函数是用户自定义聚合函数。 此外创建的聚合函数还必须是可序列化的,使用特性[Serializable]标识。 聚合函数实际上是一个结构类型或者说聚合函数对应的是一个struct类型而不是一个方法,在其中必须实现4个方法: (1)Init()初始化函数: 为要处理的每组行调用Init()方法。 在这个方法中,为要计算的每组行进行初始化; (2)Accumulate()定义具体聚合操作的函数: 为所有组中的每个值调用这个方法。 这个方法的参数必须是正确的累加类型,还可以上用户定义的类型。 该函数定义聚合函数的具体聚合操作; (3)Merge()合并函数: 聚合的结果必须和另一个聚合结果合并起来,调用Merge()方法。 (4)Terminate()结束函数: 在处理每一组的最后一行后,调用该方法。 这里,聚合的结果必须用正确的数据类型返回。 编写好聚合函数后重新编译整个项目将DLL文件添加的数据库中。 后使用alterassembly命令将聚合到SQLServer的程序集中。 2、在SQLServer中创建用户自定义聚合函数 在SQLServer中创建用户自定义聚合函数以引用CLR中的聚合函数。 创建用户自定义聚合函数使用createaggregate命令。 如下: createaggregate[schema_name.]aggregate_name ( @param_name ) returns externalnameassembly_name[.class_name] : = system_scalar_type|{[udt_schema_name.]udt_type_name} : = system_scalar_type|{[udt_schema_name.]udt_type_name} 其中,system_scalar_type: 表示要存放输入参数值或返回值的任意一个SQLServer系统标量数据类型。 除了text、ntext和image之外的所有标量数据类型,都可以用作自定义聚合函数的参数。 不能指定非标量类型(如cursor和table)。 udt_schema_name: 表示CLR用户定义类型所属的架构的名称。 如果未指定则数据库按以下顺序引用udt_schema_name: 本机SQL类型命名空间、当前数据库中当前用户的默认架构、当前数据库中的dbo架构。 udt_type_name: 表示当前数据库中以创建的CLR用户自定义类型的名称。 如果未指定udt_schema_name,则SQLServer假定该类型属于当前用户的架构。 assembly_name[.class_name]: 表示指定与用户定义的聚合函数绑定在一起的程序集以及(可选)该程序集所属的架构名称和该程序集中实现该用户定义聚合函数的类名称。 3、在T-SQL中使用用户自定义聚合函数 createaggregateCountVowels ( @inputnvarchar(4000) ) returnsint externalnameTestAssembly.CountVowels go selectCity,COUNT(City)asPersonCount,dbo.CountVowels(City)asCityVowelsCount fromPerson.Address groupbyCity 七、创建CLR用户定义类型(UDT) 创建CLR用户自定义类型来扩展SQL的类型系统,UDT可用于定义表中的列的类型或T-SQL中的变量或例程(存储过程、触发器等)参数的类型。 用户定义类型实例可以是表中的列,比处理、函数或存储过程中的变量,或者函数或者存储过程的参数。 1、使用C#定义类型 用户定义类型必须实现接口INullable,申明IsNull属性表示该类型是否为空值,而且用户定义类型在C#中用一个可序列化的结构体表示,这点和CLR用户自定义聚合函数相同。 编写好C#代码后进行编译生成DLL文件并更新到数据库中。 2、在SQLServer中使用CLR用户定义类型 要创建CLR用户定义类型需使用createtype命令,不仅可以创建基于SQL数据类型的用户自定义类型,也可以创建基于CLR的用户自定义类型。 createtype[schema_name]type_name externalnameassembly_name.[class_name] 3、使用CLR用户自定义类型 createtypemyFirstType externalnamemyTypeAssembly.myFirstType go selecttabletestMyFirstType ( TmyFirstType; ) go insertintotestMyFirstType values(‘1,7’); insertintotestMyFirstType values(‘6,0’); go selectT fromtestMyFirstType
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQL Server Server学习笔记整理 学习 笔记 整理