SQL Server数据库技术（21)

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

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

  例4-77： 测试当前应用程序是否为SQL Server Query Analyzer

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

CASE
WHEN （expression1 IS NOT NULL） THEN expression1
…
WHEN （expressionN IS NOT NULL） THEN expression
ELSE NULL

  例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 表中,此信息的内容为"The Length of rows %column_name in %table_name is %column_num.".FORMATMESSAGE() 函数用值myColumn、 myTable 和20 分别替代上述参数值,将结果存储在变量@test1 中.程序如下:
declare @test1 varchar (100)
select @test1 = formatmessage (10001, 'myColumn', 'myTable', 20)

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

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

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

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

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

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

运行结果如下:
XUJIN

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

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

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

  例4-88:


·IDENT_SEED()
IDENT_SEED() 函数语法如下:
IDENT_SEED (<'table_or_view'>)
IDENT_SEED() 函数返回表中标识性字段identity column 的初值.若无此字段,则返回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、 FLOATING POINT NUMBER、 MONEY 或DECIMAL 类型),如果是则返回1, 不是则返回0.

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

  例4-93：

·NEWID
NEWID() 函数语法如下:
 NEWID()
NEWID() 函数返回一个UNIQUEIDENTIFIER 类型的数值.此函数使用计算机的网卡的Ethernet 地址加上经由CPU Clock 产生的数字而得到其返回值.由于网卡的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"，如：select object_id("tempdb..#mytemptable").

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

  例4-97:

·PARSENAME()
PARSENAME() 函数语法如下:
 PARSENAME (<'object_name '>, )
PARSENAME() 函数返回一个数据库对象完整名称中的特定部分(对象名称或数据库拥有者名称或数据库名称或服务器名称).其返回值类型为NCHAR.
 其中object_piece 为INT 类型,其取值如下:
  对象名称Object name;
  数据库拥有者名称Owner name;
  数据库名称Database name;
  服务器名称Server name.
 如果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 (Security Identification Number,安全帐户名)号.其返回值类型为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 或ALERT TABLE 语句中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 返回当前系统的用户名.如果用户是以Windows NT 认证方式登录SQL Server 的,则会返回Windows NT 的登录名;如果用户是以SQL Server 认证方式登录的,则返回SQL Server 中的用户名.

  例4-108:

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

  例4-109:

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

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

原文转自：http://www.ltesting.net