SQLServer技术(21)

来源：互联网发布：游戏攻略网站知乎编辑：程序博客网时间：2024/06/16 01:43

8、系统函数
系统函数用于获取有关计算机系统、用户、数据库和数据库对象的信息。系统函数可以让用户在得到信息后，使用条件语句，根据返回的信息进行不同的操作。与其它函数一样，可以在SELECT语句的SELECT和WHERE子句以及表达式中使用系统函数。

·APP_NAME（）
APP_NAME（）函数语法如下：
APP_NAME（）
APP_NAME（）函数返回当前执行的应用程序的名称。其返回值类型为nvarchar（128）。

例4-77：测试当前应用程序是否为QueryAnalyzer

·COALESCE（）
COALESCE（）函数语法如下：
COALESCE（[，...n]）
COALESCE（）函数返回众多表达式中第一个非NULL表达式的值。如果所有的表达式均为NULL，则COALESCE（）函数返回NULL值。COALESCE（）函数等效于下面的CASE语句：

CASE
WHEN（expression1ISNOTNULL）THENexpression1
…
WHEN（expressionNISNOTNULL）THENexpression
ELSENULL

例4-78：

·COL_LENGTH（）
COL_LENGTH（）函数语法如下：
COL_LENGTH（<'table_name'>，<'column_name'>）
COL_LENGTH（）函数返回表中指定字段的长度值。其返回值为INT类型。

例4-79：

·COL_NAME（）
COL_NAME（）函数语法如下：
COL_NAME（，）
COL_NAME（）函数返回表中指定字段的名称，即列名。其返回值为SYSNAME类型。其中table_id和column_id都是INT类型的数据，函数用table_id和column_id参数来生成列名字符串。关于如何获得table_id和column_id，请参见OBJECT_ID（）函数。

例4-80：

·DATALENGTH（）
DATALENGTH（）函数语法如下：
DATALENGTH（）
DATALENGTH（）函数返回数据表达式的数据的实际长度。其返回值类型为INT。DATALENGTH（）函数对VARCHAR、VARBINARY、TEXT、IMAGE、NVARCHAR和NTEX等能存储变动长度数据的数据类型特别实用。NULL的长度为NULL。

例4-81：

·DB_ID（）
DB_ID（）函数语法如下：
DB_ID（['database_name']）
DB_ID（）函数返回数据库的编号。其返回值为SMALLINT类型。如果没有指定database_name，则返回当前数据库的编号。

例4-82：

·DB_NAME（）
DB_NAME()函数语法如下：
DB_NAME(database_id)
DB_NAME()函数返回数据库的名称.其返回值类型为NVARCHAR(128).database_id是SMALLINT类型的数据.如果没有指定database_id,则返回当前数据库的名称.

例4-83:

·FORMATMESSAGE()
FORMATMESSAGE()函数语法如下:
FORMATMESSAGE(,[,...n])
FORMATMESSAGE()函数用于从系统信息表sysmessages中挑选一条现存的信息,
再将其格式转换为用户需要的形式.其功能类似于RAISERROR命令,不同的是RAISERROR命令立即打印出信息,而FORMATMESSAGE()函数返回编辑过的信息给调用它的程序.其返回值类型为NVARCHAR.
其中message_number是存储在sysmessages系统表中的信息ID号.param_value是在信息中使用的参数.最多可使用20个参数.
FORMATMESSAGE()函数查找使用当前语言平台语言的信息,如果没有本地语言的信息,则使用U.S.English版本语言的信息.

  例4-84:假定有一个编号为10001的信息存储在sysmessages表中,此信息的内容为"TheLengthofrows%column_namein%table_nameis%column_num.".FORMATMESSAGE()函数用值myColumn、myTable和20分别替代上述参数值,将结果存储在变量@test1中.程序如下:
declare@test1varchar(100)
select@test1=formatmessage(10001,'myColumn','myTable',20)

·GETANSINULL()
GETANSINULL()函数语法如下:
GETANSINULL()(['database_name'])
GETANSINULL()函数返回当前数据库默认的NULL值.其返回值类型为INT.GETANSINULL()函数对ANSI空值NULL返回1;如果没有定义ANSI空值,则返回0.

  例4-85:
selectgetansinull('pangu')
运行结果如下:
------
1

·HOST_ID()
HOST_ID()函数语法如下:
HOST_ID()
HOST_ID()函数返回服务器端计算机的名称.其返回值类型为NCHAR.

  例4-86:
declare@hostIDchar(8)
select@hostID=host_id()
print@hostID
运行结果如下:
-606771

·HOST_NAME()
HOST_NAME()函数语法如下:
HOST_NAME()
HOST_NAME()函数返回服务器端计算机的名称.其返回值类型为CHAR(8).

例4-87:
declare@hostNAMEnchar(20)
select@hostNAME=host_name()
print@hostNAME

运行结果如下:
XUJIN

·IDENTITY()
IDENTITY()函数语法如下:
IDENTITY([,seed,increment])[AScolumn_name])
IDENTITY()函数只在SELECT...INTO语句中使用,用于插入一个identitycolumn列到新表中.其返回值类型与data_type的类型相同.

其中:
data_type指定数据类型.datatype是INTEGER或DECIMAL类的数据类型.Seed指定identitycolumn的初值,即第一个值.Increment指定增加一个记录时,记录的identitycolumn值应在前记录的基础上增加的值.seed和increment的缺省值为1.column_name是所插入的identitycolumn的列名.虽然AScolumn_name为可选项,但由于函数在表中插入了一个新列,所以必须指定列名.如果不使用AScolumn_name选项,可以使用以下两种方式来指定列名:
(1)selectidentity(int,1,1)ascolumn_name
intonewtable
fromoldtable
(2)selectcolumn_name=identity(int,1,1)
intonewtable
fromoldtable
有关identitycolumn的信息,请参见"管理数据库表"中的"列属性"章节.

·IDENT_INCR()
IDENT_INCR()函数语法如下:
IDENT_INCR('table_or_view')
IDENT_INCR()函数返回表中标识性字段identitycolumn的增量.若无此字段,则返回NULL值.其返回值类型为NUMERIC.

例4-88:

·IDENT_SEED()
IDENT_SEED()函数语法如下:
IDENT_SEED(<'table_or_view'>)
IDENT_SEED()函数返回表中标识性字段identitycolumn的初值.若无此字段,则返回NULL值.其返回值类型为NUMERIC.

例4-89:

·INDEX_COL()
INDEX_COL()函数语法如下:
INDEX_COL(<'table_name'>,,)
INDEX_COL()函数返回表内索引识别码为index_id的索引的名称,并找出组成该索引的列组合中第key_id个列名.其返回值类型为NCHAR.

例4-90:

·ISDATE()
ISDATE()函数语法如下:
ISDATE()
ISDATE()函数判断所给定的表达式是否为合理日期,如果是则返回1,不是则返回0.

例4-91：

·ISNULL()
ISNULL()函数语法如下:
ISNULL(,)
ISNULL()函数将表达式中的NULL值用指定值替换.如果check_expresssion不是NULL,则返回其原来的值,否则,返回replacement_value的值.

提醒：reaplacement_value的数据类型应与chech_expresssion一致.

例4-92:

·ISNUMERIC()
ISNUMERIC()函数语法如下:
ISNUMERIC()
ISNUMERIC()函数判断所给定的表达式是否为合理的数值(INTEGER、FLOATINGPOINTNUMBER、MONEY或DECIMAL类型),如果是则返回1,不是则返回0.

提醒：检查MONEY型数据时，应先用CONVERT()函数将其转换为数值型或字符型。

例4-93：

·NEWID
NEWID()函数语法如下:
NEWID()
NEWID()函数返回一个UNIQUEIDENTIFIER类型的数值.此函数使用计算机的网卡的Ethernet地址加上经由CPUClock产生的数字而得到其返回值.由于网卡的Ethernet地址是全球惟一的(在未来近一百年内不会生产出相同Ethernet地址的网卡),因而函数所产生的数字也是惟一的.

例4-94:

·NULLIF()
NULLIF()函数语法如下:
NULLIF(,)
NULLIF()函数在expression1与expression2相等时返回NULL值,若不相等时则返回expression1的值.其返回值类型与expression1相同.expression1与expression2应为同类的数据类型.

例4-95:

·OBJECT_ID()
OBJECT_ID()函数语法如下:
OBJECT_ID(<'object_name'>)
OBJECT_ID()函数返回数据库对象的编号.其返回值类型为INT.

例4-96:

提醒：当指定一个临时表的表名时,其表名的前面必须加上临时数据库名"tempdb"，如：selectobject_id("tempdb..#mytemptable").

·OBJECT_NAME()
OBJECT_NAME()函数语法如下:
OBJECT_NAME()
OBJECT_NAME()函数返回数据库对象的名称.其返回值类型为NCHAR.

例4-97:

·PARSENAME()
PARSENAME()函数语法如下:
PARSENAME(<'object_name'>,)
PARSENAME()函数返回一个数据库对象完整名称中的特定部分(对象名称或数据库拥有者名称或数据库名称或服务器名称).其返回值类型为NCHAR.
其中object_piece为INT类型,其取值如下:
  对象名称Objectname;
  数据库拥有者名称Ownername;
  数据库名称Databasename;
  服务器名称Servername.
如果object_name或object_piece为NULL,则返回NULL值.

例4-98:

·PERMISSIONS()
PERMISSIONS()函数语法如下:
PERMISSIONS([object_id[,'column_name']])
PERMISSIONS()函数用于获取当前用户对某一对象的存取权限或对某一命令的执行权限.其返回值为一个32位的bitmap值,其中低16位表示目前用户对象的存取权限设定值.高16位表示用户是否可以开放此对象的权限给其它人.
如果不指定object_id,则函数返回目前用户的命令执行权限的bitmap值.使用column_name选项可以得到表的列权限.各权限值见表4-10至4-12.

有关PERMISSIONS()函数使用方法,我们将在以后的章节分别讲述.

·STATS_DATE()
STATS_DATE()函数语法如下:
STATS_DATE(,)
STATS_DATE()函数返回最新的索引统计日期.其返回值类型为DATETIME.

例4-99:

·SUSER_SID()
SUSER_SID()函数语法如下:
SUSER_SID(['login_name'])
SUSER_SID()函数根据用户登录名返回用户的SID(SecurityIdentificationNumber,帐户名)号.其返回值类型为INT.如果不指定login_name,则返回当前用户的SID号.

例4-100:

·SUSER_SNAME()
SUSER_SNAME()函数语法如下:
SUSER_SNAME([server_user_sid])
SUSER_SNAME()函数根据SID号返回用户的登录名.如果没有指定server_user_sid,则返回当前用户的登录名.其返回值类型为NCHAR.

例4-101:

·USER_ID()
USER_ID()函数语法如下:
USER_ID(['user_name'])
USER_ID()函数根据用户数据库的用户名返回用户的数据库ID号.其返回值类型为INT.如果没有指定user_name,则返回当前用户的数据库ID号.

例4-102:

·USER_NAME()
USER_NAME()函数语法如下:
USER_NAME([user_id])
USER_NAME()函数根据用户的数据库ID号返回用户的数据库用户名.其返回值类型为NCHAR.如果没有指定user_id,则返回当前数据库的用户名.

例4-103:

9、TEXT和IMAGE函数
·TEXTPTR()
TEXTPTR()函数语法如下:
TEXTPTR()
TEXTPTR()函数返回一个指向存储文本的第一个数据库页的指针.其返回值是一个VARBINARY(16)类型的二进制字符串.如果数据类型为TEXT、NTEXT或IMAGE的列没有赋予初值,则TEXTPTR()函数返回一个NULL指针.

例4-104:

·TEXTVALID()
TEXTVALID()函数语法如下:
TEXTVALID(<'table.column'>,)
TEXTVALID()函数用于检查指定的文本指针是否有效.如果有效,则返回1;无效,则返回0.如果列未赋予初值,则返回NULL值.

例4-105:

10、NILADIC函数
NILADIC函数返回一个用户或时间戳值.这类函数均不带参数.NILADIC函数经常被定义为CREATE或ALERTTABLE语句中DEFAULT约束的一部分,可参见"管理数据库表"章节.主要的NILADIC函数如下:

·CURRENT_TIMESTAMP
CURRENT_TIMESTAMP函数语法如下:
CURRENT_TIMESTAMP
CURRENT_TIMESTAMP函数返回当前的日期和时间,等同于GETDATE()函数.返回值类型为DATETIME.

例4-106:

·CURRENT_USER
CURRENT_USER函数语法如下:
CURRENT_USER
CURRENT_USER函数与USER_NAME()函数功能相同,返回当前用户的数据库用户名.返回值类型为SYSNAME.

例4-107:

·SESSION_USER
SESSION_USER函数语法如下:
SESSION_USER
SESSION_USER函数等同于USER_NAME()函数,返回当前用户的数据库用户名.返回值类型为NCHAR.

·SYSTEM_USER
SYSTEM_USER函数语法如下:
SYSTEM_USER
SYSTEM_USER返回当前系统的用户名.如果用户是以WindowsNT方式登录SQLServer的,则会返回WindowsNT的登录名;如果用户是以SQLServer认证方式登录的,则返回SQLServer中的用户名.

例4-108:

·USER
USER函数语法如下:
USER
USER与系统函数USER_NAME()的功能相同,返回当前用户的数据库用户名.返回值类型为CHAR.

例4-109:

11、用户自定义函数
从SQLServer2000开始,用户可以自定义函数了.在SQLServer2000中用户自定义函数是作为一个数据库对象来管理的,可以使用企业管理器(EnterpriseManager)或Transact-SQL命令来创建、修改、删除.其具体方法请参见第13章"游标、视图和自定义函数".

本章介绍了Transact-SQL语言的基本概念,及其使用方法.Transact-SQL语言需要大量的实践,才能熟练运用.本章及以后的SQL语法基本上都是标准的ANSISQL兼容语法,在其它数据库中,如ORACLE、SYBASE、INFORMIX、FOXPRO等,大部分语句均可套用.