1 / 32

第三讲 T-SQL 语言中的函数

第三讲 T-SQL 语言中的函数. 学习情境引入. 课前思考 （ 1 ）是否可以创建新的数据库对象实现高效和更广泛的数据库应用？ （ 2 ）使用 SQL 语言的编程功能创建自定义函数的目的和意义是什么？ 教学课题： （ 1 ）常用函数和系统函数的使用。（系统函数 返回服务器端计算机的名称 host name ，返回数据库的对象名 object_name ，返回数据库的 ID 号 db_id ） （ 2 ）自定义函数的创建、执行和应用。 教学目的： （ 1 ）具有实现企业级数据库更广泛更强大应用的能力； （ 2 ）培养逻辑编程思维能力，代码规范编写意识；.

lexiss
Download Presentation

第三讲 T-SQL 语言中的函数

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 第三讲 T-SQL语言中的函数

  2. 学习情境引入 课前思考 （1）是否可以创建新的数据库对象实现高效和更广泛的数据库应用？ （2）使用SQL语言的编程功能创建自定义函数的目的和意义是什么？ 教学课题： （1）常用函数和系统函数的使用。（系统函数 返回服务器端计算机的名称host name，返回数据库的对象名object_name，返回数据库的ID号db_id） （2）自定义函数的创建、执行和应用。 教学目的： （1）具有实现企业级数据库更广泛更强大应用的能力； （2）培养逻辑编程思维能力，代码规范编写意识；

  3. 学习情境引入 SQL（结构化查询语言）进行编程的时候，为了加快开发速度，提高效率，并对代码进行封装与重用，便于减轻应用程序的负担。 常常使用编程思想创建函数、包快内置函数和自定义函数的应用。SQL Server 2005数据库允许用户编写自定义函数，方便业务逻辑的实现业务逻辑的可重用性；同时SQL Server 2005数据库也提供了许多内置函数供用户使用。

  4. 学习情境引入 函数实现了模块化的程序设计，（模块化是指解决一个复杂问题时自顶向下逐层把系统划分成若干模块的过程，模块化用来分割，组织和打包软件。每个模块完成一个特定的子功能，所有的模块按某种方法组装起来，成为一个整体，完成整个系统所要求的功能。模块化是编程中良好的方法也是趋势）只需创建一次函数并将其存储在数据库中，用户便在需要的时候可以随时调用，用户定义函数可以独立于程序源代码进行修改。由于以上的原因，函数具有如下优点：执行速度更快。与存储过程相似，T-SQL用户定义函数通过缓存计划并在重复执行时重用它来降低 T-SQL代码的编译开销，这意味着每次使用用户定义函数时均无需重新编译，从而缩短了执行时间。函数还可以实现减少网络流量的功能，可以减少发送至客户端的数字或行数。

  5. 实施 • 函数是—组编译好的Transact-SQL语句，它们可以带一个或一组数值做参数，也可不带参数，它返回一个数值、数值集合，或执行一些操作。SQL Server 2005支持两种函数类型： • 内置函数:是一组预定义的函数，是Transact-SQL语言的一部分，按Transact-SQL参考中定义的方式运行且不能修改。 • (2) 用户定义函数:由用户定义的Transact-SQL函数。它将频繁执行的功能语句块封装到一个命名实体中，该实体可以由Transact-SQL语句调用。（分类）

  6. 1．字符串函数 字符串函数用来实现对字符型数据的转换、查找、分析等操作，通常用做字符串表达式的一部分。以下是几种常用字符串函数。 • 使用datalength和Len函数datalength函数主要用于判断可变长字符串的长度，对于定长字符串将返回该列的长度。要得到字符串的真实长度，通常需要使用rtrim函数截去字符串尾部的空格。Len函数可以获取字符串的字符个数，而不是字节数，也不包含尾随空格。

  7. 例从表department中读取manger列的各记录的实际长度。例从表department中读取manger列的各记录的实际长度。 SELECT Datalength(rtrim(manager)) AS 'DATALENGTH', Len(rtrim(manager)) AS 'LEN' FROM department 运行结果如下：

  8. （2）使用Charindex函数实现串内搜索charindex函数主要用于在串内找出与指定串匹配的串，如果找到的话，charindex函数返回第一个匹配的位置。格式：Charindex(expr1, expr2[, start_location]) expr1是待查找的字符串expr2是用来搜索expr1的字符表达式，start_location是在expr2中查找expr1的开始位置，如果此值省略、为负或为0，均从起始位置开始查找。

  9. 例如4-3-1 SELECT charindex(',', 'red,white,blue') 该查询确定了字符串‘red,white,blue’中第一个逗号的位置。运行结果如图

  10. (3) 使用Patindex函数 patindex函数返回在指定表达式中模式第一次出现的起始位置，如果模式没有则返回0。格式：Patindex('%pattern%', expression) pattern是字符串，%字符必须出现在模式的开头和结尾。expression通常是搜索指定子串的表达式或列。

  11. 格式：Patindex('%pattern%', expression) pattern是字符串，%字符必须出现在模式的开头和结尾。expression通常是搜索指定子串的表达式或列。 例如： SELECT patindex('%abc%','abc123'), patindex('123','abc123') 子串“abc”和“123”在字符串“abc123”中出现的起始位置分别为：1和0。因为子串“123”不是以%开头和结尾。

  12. Soundex函数 soundex函数将char_expr转换为4个字符的声音码，其中第一个码为原字符串的第一个字符，第2~4个字符为数字，是该字符串的声音字母所对应的数字，但忽略了除首字母外的串中的所有元音。

  13. 例如 SELECT soundex('1'),soundex('a'), soundex('计算机'), soundex('abc'), soundex ('abcd'), soundex('a12c'), soundex('a数字') 返回值为： 0000 A000 0000 A120 A120 A000 A000

  14. 2．数学函数 数学函数用来实现各种数学运算，如指数运算、对数运算、三角运算等，其操作数为数值型数据，如int、float、real、money等 例在同一表达式中使用sin、atan、rand、pi、sign函数。 SELECT sin(23.45), atan(1.234), rand(), pi(), sign(-2.34)运行结果如下：-0.99374071017265964 0.88976244895918932 0.19756617656167863.1415926535897931 -1.00

  15. 例用ceiling和floor函数返回大于或等于指定值的最小整数值和小于或等于指定值的最大整数值。例用ceiling和floor函数返回大于或等于指定值的最小整数值和小于或等于指定值的最大整数值。 SELECT ceiling(123),floor(321), ceiling(12.3), ceiling(-32.1),floor(-32.1) 运行结果如下： 123 321 13 -32 -33 例 round函数的使用。 SELECT round(12.34512,3), round(12.34567,3), round(12.345,-2), round(54.321,-1) 运行结果如下： 12.34500 12.34600 .000 50.000 Round(numeric_expr, int_expr) 的int_expr为负数时，将小数点左边第int_expr位四舍五入。

  16. 3．日期函数日期函数用来操作datetime和smalldatetime类型的数据，执行算术运算。与其他函数一样，可以在SELECT语句和WHERE子句以及表达式中使用日期函数。3．日期函数日期函数用来操作datetime和smalldatetime类型的数据，执行算术运算。与其他函数一样，可以在SELECT语句和WHERE子句以及表达式中使用日期函数。

  17. 例使用datediff函数来确定货物是否按时送给客户。例使用datediff函数来确定货物是否按时送给客户。 SELECT goods_id,datediff(dd,send_date,arrival_date) FROM purchase_orders 为了使用datename函数返回员工的出生日期的月份(mm)名称。 SELECT employee_name,datename(mm,birth_date) FROM employee 运行结果如下：

  18. 用户定义函数 根据函数返回值形式的不同将用户定义函数分为3种类型。 (1) 标量函数 标量函数返回一个确定类型的标量值，其函数值类型为SQL Server的系统数据类型（除text、ntext、image、cursor、timestamp、table类型外）。函数体语句定义在BEGIN…END语句内。(2) 内嵌表值函数 内嵌表值函数返回的函数值为一个表。内嵌表值函数的函数体不使用BEGIN…END语句，其返回的表是RETURN子句中的SELECT命令查询的结果集，其功能相当于一个参数化的视图。 (3) 多语句表值函数 多语句表值函数可以看作标量函数和内嵌表值函数的结合体。其函数值也是一个表，但函数体也用BEGIN…END语句定义，返回值的表中的数据由函数体中的语句插入。

  19. 实施 1.创建用户定义函数(1) 使用CREATE FUNCTION创建用户定义函数标量函数的语法格式： CREATE FUNCTION [owner_name.] function_name([{@parameter_name[AS] scalar_parameter_data_type[=default ] } [ ,...n ] ] ) RETURNS scalar_return_data_type[ WITH < function_option> [ [,] ...n] ] [ AS ]BEGIN function_body RETURN scalar_expression END

  20. 实施 内嵌表值函数的语法格式： CREATE FUNCTION [owner_name.] function_name([{@parameter_name[AS] scalar_parameter_data_type [=default ] } [ ,...n ] ] ) RETURNS TABLE [ WITH < function_option > [ [,] ...n ] ] [ AS ] RETURN [ ( ] select_stmt [ ) ] END

  21. 实施 多语句表值函数的语法格式： CREATE FUNCTION [owner_name.]function_name([ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] ) RETURNS @return_variable TABLE < table_type_definition > [ WITH < function_option > [ [,] ...n ] ] [ AS ] BEGIN function_body RETURN END

  22. 用户自定义标量函数 创建一个用户定义函数DatetoQuarter，将输入的日期数据转换为该日期对应的季度值。如输入'2006-8-5'，返回'3Q2006'，表示2006年3季度。 CREATE FUNCTION DatetoQuarter(@dqdate datetime) RETURNS char(6)AS BEGIN RETURN(datename(q,@dqdate)+'Q'+datename(yyyy,@dqdate)) END

  23. 例如，调用上例创建的用户定义函数DatetoQuarter例如，调用上例创建的用户定义函数DatetoQuarter SELECT dbo.DatetoQuarter ('2012-8-5') 运行结果为：

  24. 自定义内嵌表值函数 创建用户定义函数goodsq，返回输入商品编号的商品名称和库存量。 CREATE FUNCTION goodsq(@goods_id varchar(30)) RETURNS TABLE AS RETURN(SELECT goods_name,stock_quantity FROM goods WHERE goods_id =@goods_id)

  25. 调用上例创建的用户自定义函数goodsq，使用以下语句：调用上例创建的用户自定义函数goodsq，使用以下语句： Select * FROM dbo.goodsq('G002') 运行结果如下：

  26. 实施 根据输入的订单编号，返回该订单对应商品的编号、名称、类别编号、类别名称。 (1) 使用服务管理器创建用户定义函数在对象资源管理器管理器中选择要创建用户定义函数的数据库（Sales），在可编程菜单中选择“函数”，在函数菜单中选择“表值函数”从弹出的快捷菜单中选择“新建多语句表值函数”选项，出现如图所示的界面。

  27. 实施 （2）使用SQL语句创建多语句表值函数 CREATE FUNCTION good_info(@in_o_id varchar(10)) RETURNS @goodinfo TABLE (o_id char(6), g_id char(6), g_name varchar(50), c_id char(6), c_name varchar(20)) AS BEGIN DECLARE @g_id varchar(10),@g_name varchar(30) DECLARE @c_id varchar(10),@c_name varchar(30) SELECT @g_id=goods_id FROM sell_order WHERE order_id1=@in_o_id SELECT @g_name=goods_name,@c_id=classification_id FROM goods WHERE goods_id=@g_id SELECT @c_name=classification_name FROM goods_classification WHERE @c_id=classification_id INSERT @goodinfo VALUES(@in_o_id,@g_id,@g_name,@c_id,@c_name) RETURN END

  28. 实施 在服务管理器中使用查询方式执行以上SQL语句如图所示

  29. 实施 执行用户定义函数 使用函数需要指出函数所有者，即为函数加上所有者权限作为前缀。其语法格式如下： [database_name.]owner_name.function_name([argument_expr][, ...] )调用创建的用户定义函数good_info，使用以下语句： SELECT * FROM dbo.good_info('S001')运行结果为表的记录，如图所示。

  30. 修改和删除用户定义函数 用服务管理器修改用户定义函数，选择要修改函数，单击右键，从快捷菜单中选择“修改”选项，打开图4-3-4所示的“修改”命令。从快捷菜单中还可以选择“删除”选项，则可删除用户定义函数。 用ALTER FUNCTION命令也可以修改用户定义函数。此命令的语法与CREAT FUNCTION相同，使用ALTER FUNCTION命令相当于重建一个同名的函数。 使用DROP FUNCTION命令删除用户定义函数，其语法如下：DROP FUNCTION { [ owner_name .] function_name } [ ,...n ]其中，function_name是要删除的用户定义的函数名称。

  31. 实施 用户自定义函数可以像系统函数一样在查询或存储过程等的程序段中使用，也可以像存储过程一样通过调用来执行。用户自定义函数中存储了一个T-SQL 例程，可以返回一定的值。

  32. 本讲小结 函数是一组预编译好的Transact-SQL语句。SQL Server2005支持的函数分为内置函数和用户自定义函数两种类型。用户自定义函数可以通过集成管理工具sql server management studio和Transact-SQL语句来管理。CREATE FUNCTION、ALTER FUNCTION、DROP FUNCTION 分别创建、修改、删除用户自定义函数。用户定义函数的调用要在函数明前叫所有者作为前缀。

More Related