MSSQL网页编程之sql2k增添的Function的sqlbook的匡助
Cluster/NDB高冗余的存储引擎，用多台数据机器联合提供服务以提高整体性能和安全性。适合数据量大，安全和性能要求高的应用CREATEFUNCTION创立用户界说函数，它是前往值的已保留的Transact-SQL例程。用户界说函数不克不及用于实行一组修正全局数据库形态的操纵。与体系函数一样，用户界说函数能够从查询中叫醒挪用。也能够像存储历程一样，经由过程EXECUTE语句实行。
用户界说函数用ALTERFUNCTION修正，用DROPFUNCTION撤除。
语法
标量函数
CREATEFUNCTIONfunction_name
([{@parameter_namescalar_parameter_data_type[=default]}[,...n]])
RETURNSscalar_return_data_type
...n]]
BEGIN
function_body
RETURNscalar_expression
END
内嵌表值函数
CREATEFUNCTIONfunction_name
([{@parameter_namescalar_parameter_data_type[=default]}[,...n]])
RETURNSTABLE
...n]]
RETURN[(]select-stmt[)]
多语句表值函数
CREATEFUNCTIONfunction_name
([{@parameter_namescalar_parameter_data_type[=default]}[,...n]])
RETURNS@return_variableTABLE<table_type_definition>
...n]]
BEGIN
function_body
RETURN
END
<function_option>::=
{ENCRYPTION|SCHEMABINDING}
<table_type_definition>::=
({column_definition|table_constraint}[,...n])
参数
owner_name
具有该用户界说函数的用户ID的称号。owner_name必需是现有的用户ID。
function_name
用户界说函数的称号。函数称号必需切合标识符的划定规矩，对其一切者来讲，该称号在数据库中必需是独一的。
@parameter_name
用户界说函数的参数。CREATEFUNCTION语句中能够声明一个或多个参数。函数最多能够有1,024个参数。函数实行时每一个已声明参数的值必需由用户指定，除非该参数的默许值已界说。假如函数的参数有默许值，在挪用该函数时必需指定"default"关头字才干取得默许值。这类举动分歧于存储过程当中有默许值的参数，在存储过程当中省略参数也意味着利用默许值。
利用@标记作为第一个字符来指定参数称号。参数称号必需切合标识符的划定规矩。每一个函数的参数仅用于该函数自己；不异的参数称号能够用在别的函数中。参数只能取代常量；而不克不及用于取代表名、列名或别的数据库工具的称号。
scalar_parameter_data_type
参数的数据范例。一切标量数据范例（包含bigint和sql_variant）都可用感化户界说函数的参数。不撑持timestamp数据范例和用户界说数据范例。不克不及指定非标量范例（比方cursor和table）。
scalar_return_data_type
是标量用户界说函数的前往值。scalar_return_data_type能够是SQLServer撑持的任何标量数据范例（text、ntext、image和timestamp除外）。
scalar_expression
指定标量函数前往的标量值。
TABLE
指定表值函数的前往值为表。
在内嵌表值函数中，经由过程单个SELECT语句界说TABLE前往值。内嵌函数没有相干联的前往变量。
在多语句表值函数中，@return_variable是TABLE变量，用于存储和积累应作为函数值前往的行。
function_body
指定一系列Transact-SQL语句界说函数的值，这些语句合在一同不会发生反作用。function_body只用于标量函数和多语句表值函数。
在标量函数中，function_body是一系列合起来求得标量值的Transact-SQL语句。
在多语句表值函数中，function_body是一系列添补表前往变量的Transact-SQL语句。
select-stmt
是界说内嵌表值函数前往值的单个SELECT语句。
ENCRYPTION
指出SQLServer加密包括CREATEFUNCTION语句文本的体系表列。利用ENCRYPTION能够制止将函数作为SQLServer复制的一部分公布。
SCHEMABINDING
指定将函数绑定到它所援用的数据库工具。假如函数是用SCHEMABINDING选项创立的，则不克不及变动（利用ALTER语句）或撤除（利用DROP语句）该函数援用的数据库工具。
函数与其所援用工具的绑定干系只要在产生以下两种情形之一时才被排除：
撤除了函数。
在未指定SCHEMABINDING选项的情形下变动了函数（利用ALTER语句）。
只要在满意以下前提时，函数才干绑定到架构：
该函数所援用的用户界说函数和视图也已绑定到架构。
该函数所援用的工具不是用两部分称号援用的。
该函数及其援用的工具属于统一数据库。
实行CREATEFUNCTION语句的用户对一切该函数所援用的数据库工具都具有REFERENCES权限。
假如不切合以上前提，则指定了SCHEMABINDING选项的CREATEFUNCTION语句将失利。
正文
用户界说函数为标量值函数或表值函数。假如RETURNS子句指定一种标量数据范例，则函数为标量值函数。可使用多条Transact-SQL语句界说标量值函数。
假如RETURNS子句指定TABLE，则函数为表值函数。依据函数主体的界说体例，表值函数可分为行内函数或多语句函数。
假如RETURNS子句指定的TABLE不附带列的列表，则该函数为行内函数。行内函数是利用单个SELECT语句界说的表值函数，该语句构成了函数的主体。该函数前往的表的列（包含数据范例）来自界说该函数的SELECT语句的SELECT列
表。
假如RETURNS子句指定的TABLE范例带有列及其数据范例，则该函数是多语句表值函数。
多语句函数的主体中同意利用以下语句。未鄙人面的列表中列出的语句不克不及用在函数主体中。
赋值语句。
把持流语句。
DECLARE语句，该语句界说函数部分的数据变量和游标。
SELECT语句，该语句包括带有表达式的选择列表，个中的表达式将值付与函数的部分变量。
游标操纵，该操纵援用在函数中声明、翻开、封闭和开释的部分游标。只同意利用以INTO子句向部分变量赋值的FETCH语句；不同意利用将数据前往到客户真个FETCH语句。
INSERT、UPDATE和DELETE语句，这些语句修正函数的部分table变量。
EXECUTE语句挪用扩大存储历程。
函数切实其实定性和反作用
函数能够是断定的或不断定的。假如任什么时候候用一组特定的输出值挪用函数时前往的了局老是不异的，则这些函数为断定的。假如每次挪用函数时即便用的是不异的一组特定输出值，前往的了局老是分歧的，则这些函数为不断定的。
不断定的函数会发生反作用。反作用是变动数据库的某些全局形态，好比更新数据库表或某些内部资本，如文件或收集等（比方，修正文件或发送电子邮件动静）。
不同意在用户界说函数主体中内置不断定函数；这些不断定函数以下：
@@CONNECTIONS@@TOTAL_ERRORS
@@CPU_BUSY@@TOTAL_READ
@@IDLE@@TOTAL_WRITE
@@IO_BUSYGETDATE
@@MAX_CONNECTIONSGETUTCDATE
@@PACK_RECEIVEDNEWID
@@PACK_SENTRAND
@@PACKET_ERRORSTEXTPTR
@@TIMETICKS
只管在用户界说函数主体中不同意有不断定函数，这些用户界说函数在挪用扩大存储历程时仍会发生反作用。
因为扩大存储历程会对数据库发生反作用，因而挪用扩大存储历程的函数是不断定的。当用户界说函数挪用会对数据库发生反作用的扩大存储历程时，不要期望了局集坚持分歧或实行函数。
从函数中挪用扩大存储历程
从函数外部挪用时扩大存储历程没法向客户端前往了局集。任何向客户端前往了局集的ODSAPI都将前往FAIL。扩大存储历程能够毗连回Microsoft?SQLServer?；可是，它不该实验连接与叫醒挪用扩大存储历程的函数不异的事件。
与从批处置或存储过程当中叫醒挪用类似，扩大存储历程在运转SQLServer的Windows?平安帐户的高低文中实行。存储历程的一切者在授与用户EXECUTE特权时招考虑这一点。
函数挪用
在可以使用标量表达式的地位可叫醒挪用标量值函数，包含盘算列和CHECK束缚界说。当叫醒挪用标量值函数时，最少应利用函数的两部分称号。
owner_name.function_name([,...])
假如用户界说函数用于界说盘算列，则该函数切实其实定性一样决意了是不是可在该盘算列上创立索引。只要当函数具有断定性时，才能够在利用该函数的盘算列上创立索引。假如在输出不异的情形下函数一直前往不异的值，则该函数具有断定性。
可使用由一部分构成的称号叫醒挪用表值函数。
function_name([,...])
关于Microsoft?SQLServer?2000中包括的体系表函数，叫醒挪用时需在函数名的后面加上前缀"::"。
SELECT*
FROM::fn_helpcollations()
关于招致语句中断实行然后从存储过程当中的下一语句持续实行的Transact-SQL毛病，在函数中的处置体例分歧。在函数中，这类毛病会招致函数中断实行。这反过去使叫醒挪用该函数的语句中断实行。
权限
用户应具有实行CREATEFUNCTION语句的CREATEFUNCTION权限。
CREATEFUNCTION的权限默许地授与sysadmin流动服务器脚色和db_owner和db_ddladmin流动数据库脚色的成员。
sysadmin和db_owner的成员可用GRANT语句将CREATEFUNCTION权限授与别的登录。
函数的一切者对其函数具有EXECUTE权限。其他用户不具有EXECUTE权限，除非给他们授与了特定函数上的EXECUTE权限。
若要创立或变动在CONSTRAINT、DEFAULT子句或盘算列界说中援用了用户界说函数的表，用户还必需对这些函数有REFERENCES权限。
示例
A.盘算ISO周的标量值用户界说函数
下例中，用户界说函数ISOweek取日期参数并盘算ISO周数。为了准确盘算该函数，必需在挪用该函数前叫醒挪用SETDATEFIRST1。
CREATEFUNCTIONISOweek(@DATEdatetime)
RETURNSint
AS
BEGIN
DECLARE@ISOweekint
SET@ISOweek=DATEPART(wk,@DATE)+1
-DATEPART(wk,CAST(DATEPART(yy,@DATE)asCHAR(4))+0104)
--Specialcases:Jan1-3maybelongtothepreviousyear
IF(@ISOweek=0)
SET@ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1
ASCHAR(4))+12+CAST(24+DATEPART(DAY,@DATE)ASCHAR(2)))+1
--Specialcase:Dec29-31maybelongtothenextyear
IF((DATEPART(mm,@DATE)=12)AND
((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>=28))
SET@ISOweek=1
RETURN(@ISOweek)
END
上面是函数挪用。注重DATEFIRST设置为1。
SETDATEFIRST1
SELECTmaster.dbo.ISOweek(12/26/1999)ASISOWeek
上面是了局集。
ISOWeek
----------------
52
B.内嵌表值函数
下例前往内嵌表值函数。
USEpubs
GO
CREATEFUNCTIONSalesByStore(@storeidvarchar(30))
RETURNSTABLE
AS
RETURN(SELECTtitle,qty
FROMsaless,titlest
WHEREs.stor_id=@storeidand
t.title_id=s.title_id)
C.多语句表值函数
假定有一个表代表以下的条理干系：
CREATETABLEemployees(empidnchar(5)PRIMARYKEY,
empnamenvarchar(50),
mgridnchar(5)REFERENCESemployees(empid),
titlenvarchar(30)
)
表值函数fn_FindReports(InEmpID)有一个给定的人员ID，它前往与一切间接或直接向给定人员呈报的人员绝对应的表。
该逻辑没法在单个查询中体现出来，不外能够完成为用户界说函数。
CREATEFUNCTIONfn_FindReports(@InEmpIdnchar(5))
RETURNS@retFindReportsTABLE(empidnchar(5)primarykey,
empnamenvarchar(50)NOTNULL,
mgridnchar(5),
titlenvarchar(30))
/*Returnsaresultsetthatlistsalltheemployeeswhoreporttogiven
employeedirectlyorindirectly.*/
AS
BEGIN
DECLARE@RowsAddedint
--tablevariabletoholdaccumulatedresults
DECLARE@reportsTABLE(empidnchar(5)primarykey,
empnamenvarchar(50)NOTNULL,
mgridnchar(5),
titlenvarchar(30),
processedtinyintdefault0)
--initialize@Reportswithdirectreportsofthegivenemployee
INSERT@reports
SELECTempid,empname,mgrid,title,0
FROMemployees
WHEREempid=@InEmpId
SET@RowsAdded=@@rowcount
--Whilenewemployeeswereaddedinthepreviousiteration
WHILE@RowsAdded>0
BEGIN
/*Markallemployeerecordswhosedirectreportsaregoingtobe
foundinthisiterationwithprocessed=1.*/
UPDATE@reports
SETprocessed=1
WHEREprocessed=0
--Insertemployeeswhoreporttoemployeesmarked1.
INSERT@reports
SELECTe.empid,e.empname,e.mgrid,e.title,0
FROMemployeese,@reportsr
WHEREe.mgrid=r.empidande.mgride.empidandr.processed=1
SET@RowsAdded=@@rowcount
/*Markallemployeerecordswhosedirectreportshavebeenfound
inthisiteration.*/
UPDATE@reports
SETprocessed=2
WHEREprocessed=1
END
--copytotheresultofthefunctiontherequiredcolumns
INSERT@retFindReports
SELECTempid,empname,mgrid,title
FROM@reports
RETURN
END
GO
--Exampleinvocation
SELECT*
FROMfn_FindReports(11234)
GO
你看出了作者的深度？深处半米！当初是冲那么多的大牛给他写序才买的，后来才发现无啥内容，作者也只是才用几年的新手，百花了几十两银子，再次感叹当今社会的虚伪与浮躁 having子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having条件显示特定的组，也可以使用多个分组标准进行分组。 而SQLServer如果能像Oracle一样可以为登陆分配如：5%的cpu，10%的内存。就可以解决这个漏洞。 从底层原理到表层引用，书籍多的很。个人认为没有什么那本书好？这样的说法。主要看和个人的学习方法是否适合。 如果处理少量数据,比如几百条记录的数据,我不知道这两种情况哪个效率更高,如果处理大量数据呢?比如有表中有20万条记录. 而SQLServer如果能像Oracle一样可以为登陆分配如：5%的cpu，10%的内存。就可以解决这个漏洞。 这一点很好的加强了profiler的功能。但是提到profiler提醒大家注意一点。windows2003要安装sp1补丁才能启动profiler。否则点击没有反应。 连做梦都在想页面结构是怎么样的，绝非虚言 多加的系统视图和实时系统信息这些东西对DBA挑优非常有帮助，但是感觉粒度还是不太细。
页:
[1]