标量函数调用Scalar Function Calls

标量函数为每行返回一个值。Scalar functions return a value for each row. 例如,绝对值标量函数使用数值列作为参数,并返回该列中每个值的绝对值。For example, the absolute value scalar function takes a numeric column as an argument and returns the absolute value of each value in the column. 用于调用标量函数的转义序列是The escape sequence for calling a scalar function is

{fn 标量函数 }{fn scalar-function }

其中, 标量函数附录 E:标量函数中列出的函数之一。where scalar-function is one of the functions listed in Appendix E: Scalar Functions. 有关标量函数转义序列的详细信息,请参阅附录 C: SQL 语法中的 标量函数转义序列For more information about the scalar function escape sequence, see Scalar Function Escape Sequence in Appendix C: SQL Grammar.

例如,以下 SQL 语句创建了一个大写的客户名称的结果集。For example, the following SQL statements create the same result set of uppercase customer names. 第一条语句使用转义序列语法。The first statement uses the escape-sequence syntax. 第二个语句使用适用于 OS/2 的 Ingres 的本机语法,并且不可互操作。The second statement uses the native syntax for Ingres for OS/2 and is not interoperable.

SELECT {fn UCASE(Name)} FROM Customers  
  
SELECT uppercase(Name) FROM Customers  

应用程序可将调用混合到使用本机语法和调用使用 ODBC 语法的标量函数的标量函数。An application can mix calls to scalar functions that use native syntax and calls to scalar functions that use ODBC syntax. 例如,假定 Employee 表中的名称存储为姓氏、逗号和名字。For example, assume that names in the Employee table are stored as a last name, a comma, and a first name. 下面的 SQL 语句创建 Employee 表中雇员姓氏的结果集。The following SQL statement creates a result set of last names of employees in the Employee table. 语句使用 ODBC 标量函数 SUBSTRING 和 SQL Server 标量函数 CHARINDEX 并仅在 SQL Server 上正确执行。The statement uses the ODBC scalar function SUBSTRING and the SQL Server scalar function CHARINDEX and will execute correctly only on SQL Server.

SELECT {fn SUBSTRING(Name, 1, CHARINDEX(',', Name) - 1)} FROM Customers  

为了实现最大的互操作性,应用程序应使用 CONVERT 标量函数来确保标量函数的输出是所需的类型。For maximum interoperability, applications should use the CONVERT scalar function to make sure that the output of a scalar function is the required type. CONVERT函数将数据从一种 sql 数据类型转换为指定的 sql 数据类型。The CONVERT function converts data from one SQL data type to the specified SQL data type. CONVERT函数的语法是The syntax of the CONVERT function is

转换 ( value_exp data_type **) **CONVERT( value_exp , data_type)

其中value_exp是列名称,另一个标量函数的结果或文本值, data_type是一个关键字,该关键字与附录 D:数据类型中定义的 SQL 数据类型标识符所使用的 #define名称匹配。where value_exp is a column name, the result of another scalar function, or a literal value, and data_type is a keyword that matches the #define name that is used by an SQL data type identifier as defined in Appendix D: Data Types. 例如,下面的 SQL 语句使用 CONVERT 函数来确保 CURDATE 函数的输出为日期,而不是时间戳或字符数据:For example, the following SQL statement uses the CONVERT function to make sure that the output of the CURDATE function is a date, instead of a timestamp or character data:

INSERT INTO Orders (OrderID, CustID, OpenDate, SalesPerson, Status)  
   VALUES (?, ?, {fn CONVERT({fn CURDATE()}, SQL_DATE)}, ?, ?)  

若要确定数据源支持哪些标量函数,应用程序需要使用 SQL_CONVERT_FUNCTIONS、SQL_NUMERIC_FUNCTIONS、SQL_STRING_FUNCTIONS、SQL_SYSTEM_FUNCTIONS 和 SQL_TIMEDATE_FUNCTIONS 选项来调用 SQLGetInfoTo determine which scalar functions are supported by a data source, an application calls SQLGetInfo with the SQL_CONVERT_FUNCTIONS, SQL_NUMERIC_FUNCTIONS, SQL_STRING_FUNCTIONS, SQL_SYSTEM_FUNCTIONS, and SQL_TIMEDATE_FUNCTIONS options. 若要确定 CONVERT 函数支持哪些转换操作,应用程序将使用以 SQL_CONVERT 开头的任何选项调用 SQLGetInfoTo determine which conversion operations are supported by the CONVERT function, an application calls SQLGetInfo with any of the options that start with SQL_CONVERT.