SQL Server用户自定义函数

前几篇博文介绍了不少SQLServer系统自带函数方面的使用方法，这次将学习SQLSever用户自定义函数的使用方法，算是对SQLServer函数的补充。希望与园里的朋友共同学习并取经。关于SQLServer用户自定义的函数，有标量函数、表值函数（内联表值函数、多语句表值函数）两种。题外话，可能有部分朋友不知道SQLServe用户自定义的函数应该是写在哪里，这里简单提示一下，在MicrosoftSQLServerManagermentStudio里面，展开具体需要创建SQLServer用户自定义函数的数据库（即每个用户自定义函数只针对具体的一个数据库有用），然后找到可编程性选项，再展开找到函数选项，在具体的函数选项里面可参照下图的方式鼠标右键选择来添加。

标量函数

所谓标量函数简单点来讲就是返回的结果只是一个标量，对于我来讲，返回的结果就是一种类型的一个值。写法如下：--=============================================--Author:<Author,,Name>--Createdate:<CreateDate,,>--Description:<Description,,>--=============================================CREATEFUNCTION<Scalar_Function_Name,sysname,FunctionName>(--Addtheparametersforthefunctionhere<@Param1,sysname,@p1><Data_Type_For_Param1,,int>)RETURNS<Function_Data_Type,,int>ASBEGIN--DeclarethereturnvariablehereDECLARE<@ResultVar,sysname,@Result><Function_Data_Type,,int>--AddtheT-SQLstatementstocomputethereturnvaluehereSELECT<@ResultVar,sysname,@Result>=<@Param1,sysname,@p1>--ReturntheresultofthefunctionRETURN<@ResultVar,sysname,@Result>END例子：--=============================================--Author:<Author,,Name>--Createdate:<CreateDate,,>--Description:<Description,,>--=============================================CREATEFUNCTIONGetSum

(@firstNumint,@secondNumint)RETURNSintASBEGIN--DeclarethereturnvariablehereDECLARE@resultint--AddtheT-SQLstatementstocomputethereturnvaluehereSELECT@result=@firstNum+@secondNum--ReturntheresultofthefunctionRETURN@resultENDGO

题外话：我们来看看上面的写法，对于SQLServer来讲，我们声明一个变量的方式是用@变量名，而且相对于编程来讲，SQLServer声明的方式跟我们开了个玩笑，是先变量后面才是类型。对于需要传参跟不需要传参的方式，其实跟我们编程的方式一样。有参数则是如下方式：CREATEFUNCTIONGetSum(@firstNumint,@secondNumint)如果没有参数，则只要保留括号即可。跟我们理解的函数写法一致。CREATEFUNCTIONGetSum()对于返回方式，这跟我们编程的方式又不大一样。SQLServer函数的返回类型并不放在函数名前面，而是函数名括号的后面。而且函数的返回类型需要用到返回关键字RETURNS，而不是RETURN。对于函数来讲，当然也会有所谓的函数体。标量函数也一样。它的函数体是包含在：ASBEGIN--函数体END对于需要在函数体里面声明变量的话，则需要使用到DECLARE关键字进行声明。函数体内的返回才是关键字RETURN。

好了，标量函数的例子也举完了，要存到数据库里面，还需要点击MicrosoftSQLServerManagementStudio工具里的执行操作。这样之后，就可以在查询窗口里面跟查询表数据一样来查询结果了。使用方式好懂吧，但是需要注意的是[dbo]这个对象名在不能省，[GetSum]函数后面可也别少了()。说来也奇怪，对于表值函数来说，对象名[dbo]倒是不写也可以正确执行。select[dbo].[GetSum]()

内联表值函数

相对于标量函数只返回一个标量值，内联表值函数返回的是表数据。当然罗，表数据就是Table类型。写法如下：--=============================================--Author:<Author,,Name>--Createdate:<CreateDate,,>--Description:<Description,,>--=============================================CREATEFUNCTION<Inline_Function_Name,sysname,FunctionName>(--Addtheparametersforthefunctionhere<@param1,sysname,@p1><Data_Type_For_Param1,,int>,<@param2,sysname,@p2><Data_Type_For_Param2,,char>)RETURNSTABLEASRETURN(--AddtheSELECTstatementwithparameterreferenceshereSELECT0)GO例子：--=============================================--Author:<Author,,Name>--Createdate:<CreateDate,,>--Description:<Description,,>--=============================================CREATEFUNCTION[GetMoreThanSalary](@salaryint)RETURNSTABLEASRETURN(SELECT[FName],[FCity],[FAge],[FSalary]FROM[Demo].[dbo].[T_Person]Where[FSalary]>@salary)GO题外话：标量函数上面提过的内容，这里就不重复了。内联表函数返回的表结构由函数体内的SELECT语句来决定。对于标量函数来讲，函数体是包含在如下结构中。ASBEGIN--函数体END但是对于内联表值函数来讲，函数体的结构则是如下的方式。内联表值函数只执行一条SQL语句后返回Table结果。ASRETURN--函数体END执行表值函数的方式如下：select[FName],[FCity],[FAge],[FSalary]from[dbo].[GetMoreThanSalary](8000)可以看得出，这种执行方式就跟普通表的执行方式一样了。表值函数其实相当于存储在内存空间里面的一张虚拟表。

多语句表值函数

多语句表值函数跟内联表值函数都是表值函数，它们返回的结果都是Table类型。多语句表值函数顾名思义，就是可以通过多条语句来创建Table类型的数据。这里不同于内联表值函数，内联表值函数的返回结果是由函数体内的SELECT语句来决定。而多语句表值函数，则是需要指定具体的Table类型的结构。也就是说返回的Table，已经定义好要哪些字段返回。所以它能够支持多条语句的执行来创建Table数据。写法如下：--=============================================--Author:<Author,,Name>--Createdate:<CreateDate,,>--Description:<Description,,>--=============================================CREATEFUNCTION<Table_Function_Name,sysname,FunctionName>(--Addtheparametersforthefunctionhere<@param1,sysname,@p1><data_type_for_param1,,int>,<@param2,sysname,@p2><data_type_for_param2,,char>)RETURNS<@Table_Variable_Name,sysname,@Table_Var>TABLE(--AddthecolumndefinitionsfortheTABLEvariablehere<Column_1,sysname,c1><Data_Type_For_Column1,,int>,<Column_2,sysname,c2><Data_Type_For_Column2,,int>)ASBEGIN--FillthetablevariablewiththerowsforyourresultsetRETURNENDGO例子：--=============================================--Author:<Author,,Name>--Createdate:<CreateDate,,>--Description:<Description,,>--=============================================ALTERFUNCTIONDemoFun()RETURNS@resultTABLE(namenvarchar(20),citynvarchar(20),ageint,salaryint)ASBEGIN--Fillthetablevariablewiththerowsforyourresultsetinsertinto@result(name,city,age,salary)selectFName,FCity,FAge,FSalaryfromdbo.T_PersonwhereFSalary>8000insertinto@result(name,city,age,salary)values('测试','China',1,0)RETURNENDGO题外话：可以看得出，多语句表值函数的返回结果是定义好表结构的虚拟表。这又跟标量函数一样了吧，只不过标量函数是返回一种类型的标量值而已。而且在多语句表值函数里面，你也会发现最后一句是RETURN。告诉执行程序，多语句表值函数已经执行完成。函数体结构跟标量函数的结构一样。对于类型放在变量后面这种方式确实需要好好转换一下观念。RETURNS<@Table_Variable_Name,sysname,@Table_Var>TABLE(--AddthecolumndefinitionsfortheTABLEvariablehere<Column_1,sysname,c1><Data_Type_For_Column1,,int>,<Column_2,sysname,c2><Data_Type_For_Column2,,int>)内容倒是不多，但是要熟练使用的话，还是需要在项目中多加使用才行。网上有一些网友总结出来的常用自定义函数大家可以收集积累，就像做项目一样，好的方法要形成所谓的开发库，帮助我们在下一个项目中复用。节省我们的开发时间，提高我们的工作效率。我眼中的单例模式说到单例模式，网上搜索出来的结果是多如牛毛，但这不影响我也来凑热闹的心情。任何事情都是要亲身去体会了，才能加深自己的理解。本着不断学习进取的精神，我很想可以站在牛人的肩膀上，哪怕是仰视牛人的情况下，我也想发挥自己的余热。记录下自己学习的足迹，权当自己未来细细回味也好。（不过说真的，自己试着去组织语言来介绍你的问题也好，你的产品也好，能在很大的程度上提高你的表达能力。大脑是越锻炼越活的东西，讲话、写作也一样，持之以恒，必有收获。总之，贵在坚持哦！）下面来介绍模式，单例模式就是保证一个类仅有一个实例，并提供一个访问它的全局访问点。

其实就是实现只有一个门可以进入，且每次只给一个人进入。这就像以前的一位博友所举的例子，很多人排队去厕所蹲坑一样，每一次只能让一个人去蹲坑。实现单例模式的原因，要么是资源共享，要么是控制资源等。所谓资源共享，就是因为单例模式保证了一个类仅有一个实例，所以大家访问的实例是一致的。而控制资源的话，主要是减少资源的申请与释放等。牛人就是牛人，一下给出了五种实现单例模式的例子。看得我茅塞顿开，大呼过瘾。

第一种：简单实现（惰性实例化）

namespaceSingleton{publicclassProgram{staticvoidMain(string[]args){Singletons1=Singleton.Instance;Singletons2=Singleton.Instance;if(s1==s2){Console.WriteLine("Objectsarethesameinstance");}Console.Read();}}publicsealedclassSingleton{privateSingleton(){}privatestaticSingletoninstance=null;publicstaticSingletonInstance{get{if(instance==null){instance=newSingleton();}returninstance;}}}}简单实现对于线程来说是不安全的，因为在多线程的情况下，有可能产生多个Singleton实例。多线程的情况下，如果多个线程都去判断（instance==null），而它们都还没有创建实例的情况下，就会产生多个Singleton实例。对于简单实现来讲，Singleton实例化并不是应用程序启动就创建，所以我们把它叫做“惰性实例化”，这能避免应用程序启动时实例化不必要的实例。

第二种：安全的线程

namespaceSingleton{publicclassProgram{staticvoidMain(string[]args){Singletons1=Singleton.Instance;Singletons2=Singleton.Instance;if(s1==s2){Console.WriteLine("Objectsarethesameinstance");}Console.Read();}}publicsealedclassSingleton{privateSingleton(){}privatestaticSingletoninstance=null;privatestaticreadonlyobjectpadLock=newobject();publicstaticSingletonInstance{get{lock(padLock){if(instance==null){instance=newSingleton();}returninstance;}}}}}安全的线程，这是对简单实例的补充。因为提供了加锁lock()的操作，这就能确保只有一个线程进入。但是加锁需要增加额外的开销，损失性能。

第三种：双重锁定检查

namespaceSingleton{publicclassProgram{staticvoidMain(string[]args){Singletons1=Singleton.Instance;Singletons2=Singleton.Instance;if(s1==s2){Console.WriteLine("Objectsarethesameinstance");}Console.Read();}}publicsealedclassSingleton{publicSingleton(){}privatestaticSingletoninstance=null;

privatestaticreadonlyobjectpadLock=newobject();publicstaticSingletonInstance{get{if(instance==null){lock(padLock){if(instance==null){instance=newSingleton();}}}returninstance;}}}}双重锁定检查在安全的线程上面又进行了改进，主要是考虑了每次加锁会增加额外的开销，影响性能。所以在加锁前再判断Singleton有没有被实例化。这样，它就能减少很多的额外开销且是线程安全的。实际上，应用程序很少需要上面方式的实现。这种方式仍然有很多缺点：无法实现延迟初始化。大多数情况下我们会使用静态初始化的方式。

第四种：静态初始化

namespaceSingleton{publicclassProgram{staticvoidMain(string[]args){Singletons1=Singleton.Instance;Singletons2=Singleton.Instance;if(s1==s2){Console.WriteLine("Objectsarethesameinstance");}Console.Read();}}publicsealedclassSingleton{staticreadonlySingletoninstance=newSingleton();privateSingleton(){}publicstaticSingletonInstance{get{returninstance;}}}}静态初始化，是在.NET中实现Singleton的首选方法。这段代码有点意思，我也解读一下。主要是讲解下关键字吧。sealed:修改类，意为这个类不可再被继承，防止子类被实例化而不能保证只有一个实例的问题。privateSingleton():用private修改构造函数，可以防止这个类在外部被实例。也就是在Singleton类外面想newSingleton()是会报编译错误。staticreadonly:表示只能在声明时赋值，或是在静态构造中赋值。

第五种：延迟初始化

namespaceSingleton{classProgram{staticvoidMain(string[]args){Singletons1=Singleton.Instance;Singletons2=Singleton.Instance;if(s1==s2){Console.WriteLine("Objectsarethesameinstance");}Console.Read();}}publicsealedclassSingleton{publicSingleton(){}publicstaticSingletonInstance{get{returnDelay.DelayInstance;