《SQLServer函数》PPT课件.ppt

,第十章 SQL Server函数,10.1 常用系统函数,10.2 创建自定义函数,10.3 用户自定义函数类型,10.4 修改和删除自定义函数,10.5 案例中的自定义函数,练 习 题,目录,10.1 常用系统函数,10.1.1 标量函数 10.1.2 聚合函数,10.1.1 标量函数,标量函数进行单一值操作，返回单一值。只要表达式有效 即可使用标量函数。 1.日期和时间函数 对日期和时间输入值执行操作，并返回一个字符串、数字值或日期和时间值。,1）DATENAME(datepart ,date)： 返回某日期指定部分的字符 串。 参数描述 datepart： 指定应返回的日期部分； date： 指定的日期。,表10.1 SQL Server识别的datepart参数,2）GETDATE( )： 返回当前系统日期和时间。 例：select getdate() 结果：2005-06-20 13:55:37 3）DAY( date ) ： 返回代表指定日期的天的日期部分的整数。 参数描述 date：指定的日期 例：select day(03/12/1998) 结果：12,4）MONTH(date)： 返回代表指定日期月份的整数。 参数描述 date：指定的日期 例：select month(03/12/1998) 结果：3 5）YEAR(date)： 返回表示指定日期中的年份的整数。 参数描述 date：指定的日期 例：select month(03/12/1998) 结果：1998,2.数学函数 对作为参数提供的输入值执行计算，并返回一个数字 值。常用的数学函数有： 1）ABS(x)： 返回给定数字表达式的绝对值。 例：select abs(-12) 结果：12 2）ACOS(x)： 返回以弧度表示的角度值。,参数描述 x： 是 float 或 real 类型的表达式，其取值范围从 -1 到 1。 例：select acos（-1） 结果：3.1415926535897931 3）ASIN(x)： 返回以弧度表示的角度值。 参数描述 x： 是 float 或 real 类型的表达式，其取值范围从 -1 到 1。 4） ATAN(x)： 返回以弧度表示的角度值。 参数描述 x： 是 float类型的表达式。,5）CEILING(x)： 返回大于或等于所给数字表达式的最小整数。 例：select ceiling(56.3), ceiling (-56.3) 结果：57,-56 6）COS(x)： 返回给定表达式中给定角度的三角余弦值。 参数描述 x： 是 float类型的表达式。 7）DEGREES(x)： 返回以弧度表示的角度值。 例： select degrees(pi() 结果：180 8）EXP(x)： 返回给定表达式的指数值。,9）FLOOR(x)： 返回小于或等于所给数字表达式的最大整数。 例：select floor(56.3), floor(-56.3) 结果：56,-57 10）LOG(x)： 返回给定表达式的自然对数。 11）PI( )： 返回 PI 的常量值，结果3.14159265358979。 12）POWER(x,y)： 返回x的y次方。 例：select power（2，3） 结果：8 13）RAND( )：返回01之间的随机数。,14）ROUND(x,y)： 返回以y指定的精度进行四舍五入后的数值。 参数描述 y：指定的精度。当 y为正数时，x四舍五入为 y 所指定的小数位数。当 y为负数时，x则按y所指定的在小数点的左边四舍五入。 例：select round(56.34,1),round(56.34,-1) 结果：56.30，60 15）SIN(x)： 返回给定表达式中给定角度的三角正弦值。 参数描述 x： 是 float类型的表达式。,16）SQUARE(x)： 返回给定表达式的平方。 例：select square(5) 结果：25 17）SQRT(x)： 返回给定表达式的平方根。 例：select sqrt(16) 结果：4 3.元数据函数 返回有关数据库和数据库对象的信息。,1）COL_LENGTH(table , column)： 返回列的长度，且以字节为单位。 参数描述 table：表名。column：列名。 例：use student select col_length(专业,专业名称) 结果：20 2）COL_NAME( table_id , column_id )：返回数据库列的名称。 参数描述 table_id , column_id：表标识号，列标识号。,例：use student select col_name(object_id(专业),2) 结果：专业名称 注：此例返回“student”数据库，“专业”表中第二列的名称。 3）DB_ID(db_name)：返回数据库标识号。 参数描述db_name：用来返回相应数据库 ID 的数据库名 4）DB_NAME(db_id)： 返回数据库名。 参数描述db_id：是应返回数据库的标识号。,4.字符串函数 对字符串输入值执行操作，返回字符串或数字值。 1）ASCII(str)： 返回字符表达式最左端字符的 ASCII 代码值。 参数描述 str： char 或 varchar的表达式,2）CHAR(x)：将 ASCII 代码转换为字符的字符串函数。 参数描述 x： 介于 0 和255 之间的整数。,表10.2 字符串中常用的控制字符,3）LEFT(str,x)：返回字符串中从左边开始指定个数的字符。 参数描述 str：指定字符串；x：指定返回字符的个数。 例：select left(“command”,4) 结果：comm 4）LEN(str)： 返回字符串的字符个数，不包含尾随空格。 参数描述 str：将进行长度计算的字符串。 5）LOWER(str)： 将大写字符数据转换为小写字符数据，返回类型varchar。,参数描述 str： 是字符或二进制数据表达式。 6）LTRIM(str)：删除起始空格，返回类型varchar。 参数描述 str：字符或二进制数据表达式。 7）REPLACE(str1,str2,str3)：用第三个表达式替换第一个字符串表达式中出现的所有第二个给定字符串表达式。 参数描述 str1：包含待替换字符串的表达式；str2： 待替换字符串表达式；str3：替换用的字符串表达式。,8）REPLICATE(str,x)： 以指定的次数重复字符表达式。 参数描述 str： 可以是常量或变量，也可以是字符列或二进制数据列。x：指定重复次数。 9）REVERSE(str)：将指定字符串逆序排列。 参数描述 str：待排列的字符串。 10）RIGHT(str,x)：返回字符串中从右边开始指定个数的字符。 参数描述 str：指定字符串；x：指定返回字符的个数。 例：select right(“command”,4) 结果：mand,11）RTRIM(str)： 删除尾随空格，返回类型varchar。 参数描述 str：字符或二进制数据表达式。 12）SPACE(x)： 产生指定个数的空格。 参数描述 x：空格的个数。 13）SUBSTRING(str,start,len)：截取指定的部分字符串。 参数描述 str：待截取的表达式；strat：截取部分的起始位置；len：截取的长度。,14）UPPER(str)： 将小写字符数据转换为大写字符数据，返回类型varchar。 参数描述 str： 是字符或二进制数据表达式。 5.系统函数 对 SQL Server 中的值、对象和设置进行操作并返回有关信息。,1）APP_NAME( )： 返回当前会话的应用程序名称。 2）CAST(expression AS data_type)：将某种数据类型的表达式转换为另一种数据类型。 参数描述 expression：待转换的表达式；data_type：表达式新的数据类型。 例：selcet cast(123 as char(1) 结果：将数字数据123 转换成长度为1个字节的字符型数据。,3）CONVERT (data_type(length), expression , style)：同cast函数。 参数描述 data_type：表达式新的数据类型； length：表达式长度。 expression：待转换的表达式。 style：日期或字符串格式样式。,4）CURRENT_USER( )：返回当前的用户。此函数等价于 USER_NAME()。 5）HOST_ID( )：返回工作站标识号。 6）HOST_NAME( )：返回工作站名称。 7）USER_NAME(id)： 返回给定标识号的用户数据库用户名。 参数描述 id：用户名标识号。id省略时user_name( )返回当前用户。,10.1.2 聚合函数,对一组值执行计算并返回单一的值。Transact-SQL 编程语 言提供下列聚合函数： 1.AVG( ALL | DISTINCT expression)：返回组中值的平均值。 参数描述 ALL：对所有的值进行聚合函数运算。 DISTINCT：指定 COUNT 返回唯一非空值的数量。 expression：待求平均值的表达式。 例：统计学生平均成绩。,use student select 课程注册.学号, 学生.姓名, avg(成绩) as 平均成绩 from 学生,课程注册 where 学生.学号=课程注册.学号 group by 课程注册.学号,学生.姓名 结果：,图10.3 avg函数返回结果,2.COUNT( ALL | DISTINCT expression | * )：返回组中项 目的数量。 参数描述 ALL：对所有的值进行聚合函数运算。 DISTINCT：指定 COUNT 返回唯一非空值的数量， 去除重复值。 expression：待计数的表达式。,*：指定应该计算所有行以返回表中行的总数。 例：统计学生所选课程总数。 use student select count(distinct 课程号) from 课程注册 结果：4,3.MAX( ALL | DISTINCT expression)：返回表达式的最大 值。 参数描述 ALL：对所有的值进行 聚合函数运算。 DISTINCT：指定 COUNT返回唯 一非空值的数量，去除重复值。 expression：待求最大值的表达式。,例：统计每门课程学生成绩的最大值。 use student select 课程名, max(成绩) from 课程注册,课程 where 课程.课程号=课程注册.课程号 group by 课程注册.课程号,课程.课程名 结果：,图10.4 max函数返回结果,4.MIN( ALL | DISTINCT expression)：返回表达式的最大 值。 参数描述 ALL：对所有的值进行聚合函数运算。 DISTINCT：指定 COUNT 返回唯一非空值的数量， 去除重复值。 expression：待求最小值的表达式。,5.SUM( ALL | DISTINCT expression)：返回表达式中所有值 的和，或只返回 DISTINCT 值。SUM 只能用于数字列。 参数描述 ALL：对所有的值进行聚合函数运算。 DISTINCT：指定 SUM 返回唯一值的和，即若有相 同值则只相加一次。 expression：待求最小值的表达式。,10.2 创建自定义函数,1.用户定义函数 函数是由一个或多个 Transact-SQL 语句组成的子程序，可用于封装代码以便重新使用。Microsoft SQL Server 2000 并不将用户限制在定义为Transact-SQL 语言一部分的内置函数上，而是允许用户创建自己的用户定义函数。我们可使用 CREATE FUNCTION 语句创建、使用 ALTER FUNCTION语句修改、以及使用 DROP FUNCTION 语句除去用户定义函数。用户定义的函数名必须唯一且符合命名规则。 函数名的命名规则：,1）有效字符：SQL 函数名必须以一个字母（a-z以及带可区别标记的字母以及非拉丁字母），(at符), #(数字符)或下划线开头 （_）开头，跟在首字符后面的字符可以是字母( A - Z、a - z以及其他语言的任何字母符号)、数字( 0 - 9以及其他语言的任何其他数字符号)、 ( a t符)、$ (美元符)、# (数字符)或_ (下划线)。并不是所有以符号开头的对象就意味着它是一个局部变量，SQL Server有许多以开头的函数,例如：函数ERROR能返回最后执行的 Transact-SQL 语句的错误代码。,2）有效长度：函数名的有效长度为 128个字符。 3）SQL Server保留的关键字不能用作函数名。 4）嵌入的空格或其他特殊字符不能在函数名中使用。,2.调用用户定义函数 1）当调用标量用户定义函数时，必须提供至少由两部分组成的名称： SELECT *, MyUser.MyScalarFunction() FROM MyTable 2）可以使用一个部分构成的名称调用表值函数： SELECT * FROM MyTableFunction(),10.3 用户自定义函数类型,10.3.1 标量函数 10.3.2 内嵌表值函数 10.3.3 多语句表值函数,10.3.1 标量函数,标量函数返回在 RETURNS 子句中定义的 类型的单个数据值。可以使用所有标量 数据类型，包括 bigint 和 sql_variant。 1.标量函数的创建 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 scalar_expression END,参数描述 owner_name：拥有该用户定义函数的用户 ID 的名称。 function_name：用户定义函数的名称。该名称在数据库中必须是唯一的。 parameter_name：用户定义函数的参数。使用 符号作为第一个字符来指定参数名称。参数名称必须符合标识符的规则。每个函数的参数仅用于该函数本身；相同的参数名称可以用在其它函数中。,scalar_parameter_data_type：参数的数据类型。 scalar_return_data_type：是标量用户定义函数的返回值。 function_body ：是一系列合 Transact-SQL 语句。 scalar_expression：指定标量函数返回的标量值。,10.3.2 内嵌表值函数,内嵌用户定义函数是返回 table 的用户定义函数的子集。内嵌函数可用于实现参数化视图的功能。 1. 内嵌表值函数的创建 CREATE FUNCTION owner_name. function_name ( parameter_name AS scalar_parameter_data_type = default ,.n ),RETURNS TABLE WITH , .n AS RETURN ( ) select-stmt 参数描述 select-stmt：是定义内嵌表值函数返回值的单个 SELECT 语句。 其他同创建标量函数参数描述。,10.3.3 多语句表值函数,对于多语句表值函数，在 BEGIN.END 块中定义的函数 主体包含 TRANSACT-SQL 语句，这些语句可生成行并将行插 入将返回的表中。 1. 多语句表值函数的创建,CREATE FUNCTION owner_name. function_name ( parameter_name AS scalar_parameter_data_type = default ,.n ) RETURNS return_variable TABLE WITH , .n AS BEGIN,function_body RETURN END ：= ENCRYPTION | SCHEMABINDING ：= ( column_definition | table_constraint ,.n ),参数描述 return_variable ： table 变量，用于存储和累积应 作为函数值返回的行。function_body：一系列填充表返回变量的 Transact- SQL 语句。 ENCRYPTION：指出 SQL Server 加密包含 CREATE FUNCTION 语句文本的系统表列。 SCHEMABINDING：指定将函数绑定到它所引用的 数据库对象。 其他同创建标量函数参数描述。,10.4 修改和删除自定义函数,10.4.1 修改用户自定义函数 10.4.2 删除用户自定义函数,10.4.1 修改自定义函数,1. 使用ALTER FUNCTION命令修改自定义函数 更改先前由CREATE FUNCTION 语句创建的现有用户定义 函数，但不会更改权限，也不影响相关的函数、存储过程或触 发器。 1）标量函数,ALTER FUNCTION owner_name. function_name ( parameter_name scalar_parameter_data_type = default ,.n ) RETURNS scalar_return_data_type WITH ,.n AS BEGIN,function_body RETURN scalar_expression END 参数描述 owner_name：拥有待更改的用户定义函数的用户 ID 名。 function_name是要更改的用户定义函数。,parameter_name：用户定义函数的参数。 scalar_parameter_data_type：参数的数据类型。 scalar_return_data_type：是标量用户定义函数的返回值。 function_body ：是一系列合起来求得标量值的Transact-SQL 语句。 scalar_expression：指定标量函数返回的标量值。 2）内嵌表值函数,ALTER FUNCTION owner_name. function_name ( parameter_name scalar_parameter_data_type = default ,.n ) RETURNS TABLE WITH ,.n AS ,RETURN ( ) select-stmt 参数描述 select-stmt：是定义内嵌表值函数返回值的单个 SELECT 语句。 其他同更改标量函数参数描述。 3）多语句表值函数,ALTER FUNCTION owner_name. function_name ( parameter_name scalar_parameter_data_type = default ,.n ) RETURNS return_variable TABLE WITH ,.n AS ,BEGIN function_body RETURN END := ENCRYPTION | SCHEMABINDING := ( column_definition | table_constraint ,.n ),参数描述 return_var