第17章 SQL Server函数.ppt

1、第17章 SQL Server函数,本章内容,内置函数 用户自定义函数,内置函数,数学函数 字符串函数 日期函数 系统函数 其它常用函数,函数概述,编程语言中的函数是用于封装经常执行的逻辑。任何代码若需要执行函数所包含的逻辑，都可以调用该函数，而不必重复所有的函数逻辑。,内置函数与用户自定义函数,内置函数 按 Transact-SQL 参考中定义的方式运行且不能修改。只有使用 Transact-SQL 参考中所定义语法的 Transact-SQL 语句才能引用这类函数。 用户定义函数 使您得以用 CREATE FUNCTION 语句定义自己的 Transact-SQL 函数。,数学函数,SQL

2、 Server内置的数学函数有： ABS：求绝对值 ACOS：求反余弦值 ASIN：求反正弦值 PI：圆周率 SQRT：求平方根 SQUARE：求平方值 ,数学函数举例,下例显示了 ABS 函数对三个不同数字的效果。 SELECT ABS(-1.0), ABS(0.0), ABS(1.0) 下例返回半径为 1 米、高为 5 米的圆柱容积。 DECLARE h float, r float SET h = 5 SET r = 1 SELECT PI()* SQUARE(r)* h AS 容积,字符串函数,SQL Server内置的字符串函数有： LEN：返回给定字符串的长度 LEFT：返回字符串

3、最左边给定整数个字符串 RIGHT：返回字符串最右边给定整数个字符串 SUBSTRING：返回一个子字符串 UPPER：将字符串全部改为大写子母 LTRIM：返回删除给定字符串左段空格后的字符串 RTRIM：返回删除给定字符串右段空格后的字符串 ,字符串函数举例（一）,下面的示例选定字符个数和位于芬兰的公司的 CompanyName 数据。 USE Northwind GO SELECT LEN(CompanyName) AS Length, CompanyName FROM Customers WHERE Country = Finland,字符串函数举例（二）,下面的示例返回每个书名最左边

4、的 5 个字符。 USE pubs GO SELECT LEFT(title, 5) FROM titles ORDER BY title_id GO,字符串函数举例（三）,下例显示如何显示字符串常量 abcdef 中的第二个、第三个和第四个字符。 SELECT x = SUBSTRING(abcdef, 2, 3) 下面是结果集： x - bcd,日期函数,SQL Server内置的日期函数有： Getdate：返回当前系统时间 Datepart：以整数形式返回给定日期的指定部分 Datediff：返回开始日期和结束日期在给定部分上的差值 Day：返回指定日期day部分的数值 ,日期函数举例

5、（一）,下面的示例得出当前系统日期和时间： SELECT GETDATE() GO 返回当前月份 SELECT DATEPART(month, GETDATE() AS Month Number GO,日期函数举例（二）,显示指定日期（2004-4-12）到当前日期的天数。 SELECT DATEDIFF(day, 2004-4-12, getdate() AS no_of_days 显示当前日期的指定部分 select day(getdate() as day, month(getdate() as month, year(getdate() as year,系统函数,系统函数使您得以访问

6、SQL Server系统表中的信息，而不必直接访问系统表。 通过调用系统函数可以获得有关服务器、用户、数据库状态等系统信息。 这些信息对一般用户用处不大，但在管理和维护数据库服务器方面却很有价值。,其它常用函数,ISDATE ISNULL PRINT CAST CONVERT,ISDATE函数,用法：ISDATE（expression） 功能：判断给定的日期是否为合法的日期。 示例：检查 datestring 局部变量是否为有效的日期。 DECLARE datestring varchar(8) SET datestring = 12/21/98 SELECT ISDATE(datestrin

7、g),ISNULL函数,用法： ISNULL ( check_expression , replacement_value ) 功能：使用指定的替换值替换 NULL。 示例：显示所有书的价格，用值 $10.00 替换 titles 表的 price 列中的所有 NULL 条目。 USE pubs GO SELECT ISNULL(price, $10.00) FROM titles GO,CAST函数,用法：CAST ( expression AS data_type ) 功能：将某种数据类型的表达式显式转换为另一种数据类型。 示例：使用 CAST 数据类型转换函数来串联非字符、非二进制表达式

8、。 SELECT The price is + CAST(price AS varchar(12) FROM titles,CONVERT函数,用法：CONVERT (data_type(length), expression , style) 功能：将某种数据类型的表达式显式转换为另一种数据类型。 示例：使用 CAST 数据类型转换函数来串联非字符、非二进制表达式。 SELECT The price is + CONVERT(varchar(12),price) FROM titles,课堂练习,1. 求半径2米，高3米的圆柱体体积。 2. 求字符串abcdefg的长度。 3. 显示pubs

9、数据库中所有作家的au_lname的开头两个字母，大写显示。 4. 取字符串abcdefg的第2到第5个字母。 5. 计算2001年9月11日到今天已经多少天了。,用户自定义函数,为什么使用用户自定义函数？ 函数是由一个或多个 Transact-SQL 语句组成的子程序，可用于封装代码以便重新使用。 怎么使用用户自定义函数？ 可使用 CREATE FUNCTION 语句创建、使用 ALTER FUNCTION 语句修改、以及使用 DROP FUNCTION 语句除去用户定义函数。 用户定义函数采用零个或更多的输入参数并返回标量值或表。,用户自定义函数分类,标量函数 内嵌表值函数 多语句表值函数

10、,标量函数,与SQL Server内置函数类似； 标量函数返回在 RETURNS 子句中定义的类型的单个数据值。 可以使用所有标量数据类型，除了以下数据类型：timestamp 、用户定义数据类型、table、cursor。,创建标量函数语法,CREATE FUNCTION owner_name. function_name ( parameter_name AS scalar_parameter_data_type = default ,.n ) RETURNS scalar_return_data_type WITH , .n AS BEGIN function_body RETURN s

11、calar_expression END,创建标量函数举例,创建一个加法函数。 use pubs go CREATE FUNCTION addfun (a int,b int) RETURNS int AS BEGIN DECLARE result int set result = a + b RETURN result END,调用标量函数,调用（执行）标量函数有多种方式： 比如：执行前面创建的加法函数 Select dbo.addfun(3,4) Declare c int set c = dbo.addfun(3,4) select c,使用标量函数注意,调用标量函数时，必须提供拥有者和

12、函数名两部分：dbo.fun_name() 当函数的参数有默认值时，调用该函数时必须指定默认 DEFAULT 关键字才能获取默认值。 用户定义函数中不允许使用会对每个调用返回不同数据的内置函数。比如：GETDATE，RAND，CPU_BUSY等，不过这些不确定函数可以作为参数传递给函数。,标量函数举例,修改addfun函数，使其参数具有默认值 ALTER FUNCTION addfun (a int = 0,b int = 0) RETURNS int AS BEGIN DECLARE result int set result = a + b RETURN result END,调用具有默认

13、值的标量函数,当函数的参数有默认值时，调用该函数时必须指定默认 DEFAULT 关键字才能获取默认值。 select dbo.addfun(default,default),课堂练习,6. 创建一个计算圆柱体体积的函数。（两个参数：r，h） 7. 在查询分析器中执行这个函数，并查看结果。,表值函数,表值函数：内嵌表值函数、多语句表值函数 返回 table 的用户定义函数可以是替代视图的强大方式。 返回 table 的用户定义函数可用在 T-SQL 查询中允许表或视图表达式的地方。 视图限制于单个 SELECT 语句，而用户定义函数可包含允许比视图中具有的逻辑功能更强大的附加语句。,内嵌表值函数

14、,内嵌函数可用于实现参数化视图的功能。 内嵌表值函数，没有函数主体（即没有BEGIN END块）。 内嵌表值函数返回单个 SELECT 语句的结果集。,内嵌表值函数语法,CREATE FUNCTION owner_name. function_name ( parameter_name AS scalar_parameter_data_type = default ,.n ) RETURNS TABLE WITH , .n AS RETURN ( select-satement ) ,创建内嵌表值函数举例（一）,USE pubs GO CREATE FUNCTION type_book (ty

15、pe varchar(10) RETURNS TABLE AS RETURN (SELECT title_id,title,type,price FROM titles WHERE type = type),创建内嵌表值函数举例（二）,上面的函数返回的是一个结果集，我们可以把这个函数“看成”是一个带参数的视图。（注意：视图是不允许带参数的） 我们可以在任何可以使用视图的地方使用这个函数。,使用内嵌表值函数,选择所有商业类的图书信息 select * from type_book(business) 选择所有价格小于15美元的商业类图书 Select * from type_book(busin

16、ess) Where price 15,课堂练习,8. 创建一个内嵌表值函数，要求这个函数能够以州名为参数，包含某个指定州的所有作家信息。并调用这个函数。,多语句表值函数,对于多语句表值函数，在 BEGIN.END 块中定义的函数主体包含 TRANSACT-SQL 语句，这些语句可生成行，并将行插入将返回的表中。 相当于在函数体中创建一个中间表，同时向这个表中插入数据，最后返回这个表。,多语句表值函数语法,CREATE FUNCTION owner_name. function_name ( parameter_name AS scalar_parameter_data_type = defa

17、ult ,.n ) RETURNS return_variable TABLE WITH , .n AS BEGIN function_body RETURN END,CREATE FUNCTION LargeOrderShippers ( FreightParm money ) RETURNS OrderShipperTab TABLE ( ShipperID int, ShipperName nvarchar(80), OrderID int, ShippedDate datetime, Freight money ) AS BEGIN INSERT OrderShipperTab SELECT S.ShipperID, S.CompanyName, O.OrderID, O.ShippedDate, O.Freight FROM Shippers AS S INNER JOIN Orders AS O ON S.ShipperID = O.ShipVia WHERE O.Freight FreightParm RETURN END,创建多语句表值函数举例,CREATE FUNCTION type_book2 (type varchar(10) RETURNS ret TABLE (