SQL Server 自定义函数(Function)——参数默认值

本文涉及的产品
云数据库 RDS SQL Server,基础系列 2核4GB
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
简介: 原文:SQL Server 自定义函数(Function)——参数默认值sql server 自定义函数分为三种类型:标量函数(Scalar Function)、内嵌表值函数(Inline Function)、多声明表值函数(Multi-Statement Function) 标量函数:标量函数是对单一值操作,返回单一值。
原文: SQL Server 自定义函数(Function)——参数默认值

sql server 自定义函数分为三种类型:标量函数(Scalar Function)、内嵌表值函数(Inline Function)、多声明表值函数(Multi-Statement Function

标量函数:标量函数是对单一值操作,返回单一值。

内嵌表值函数:内嵌表值函数的功能相当于一个参数化的视图。它返回的是一个型函数没有由BEGIN-END 句括起来的函数体。

多声明表值函数它的返回是一个表,但它和量型函数一有一个用BEGIN-END 句括起来的函数体,返回的表中的数据是由函数体中的句插入的。

它可以行多次查询行多次筛选与合并,弥了内型函数的不足。

 

创建标量函数的语法:

1 create function [函数的所有者].函数名(标量参数 [as] 标量参数类型 [=默认值])
2 returns 标量返回值类型
3 [with {Encryption | Schemabinding }]
4 [as]
5 begin
6     函数体(即 Transact-SQL 语句)
7     return 变量/标量表达式
8 end

下面自定义一个根据传入参数(即学生性别)来计算学生平均身高的 Function,示例如下:

 1 use Test
 2 go
 3 
 4 create function dbo.AvgHeight(@Sex varchar(10)='')    --在这里是给参数默认值,即可以不传参数调用这个函数
 5 returns decimal
 6 as
 7 begin
 8     declare @AvgHeight decimal(10,2)
 9     select @AvgHeight=AVG(Convert(decimal(10,2),S_Height)) from Student where S_Sex=@Sex
10     return @AvgHeight
11 end
12 go

执行用户自定义标量函数:

1 select [函数的所有者].函数名 as 字段别名
2 
3 select dbo.AvgHeight(default) as AvgHeight;    --返回 179
4 select dbo.AvgHeight('');     --返回 179
5 select dbo.AvgHeight('');     --返回 176

PS:创建函数时指定了函数所有者,那么调用的时候也必须指定函数的所有者。(一般都为 dbo)

调用自定义函数时如果想不传入参数而使用默认值,那么必须使用 default 关键字。如果自定义函数的参数没有默认值,那么会返回 null。

 

创建内嵌表值函数语法:

1 create function [函数的所有者].函数名(标量参数 [as] 标量参数类型 [=默认值])
2 returns table
3 [with {Encryption | Schemabinding }]
4 [as]
5 return(单个 SELECT 语句,确定返回的表的数据。)

下面自定义一个根据传入参数(即学生学号)来查询学生信息的 Function,示例如下:

 1 use Test
 2 go
 3 
 4 create function dbo.SearchStuInfo(@Stu_No varchar(50)='001')
 5 returns table
 6 as
 7 return(
 8 select S_StuNo,S_Name,S_Sex,S_Height,S_BirthDate 
 9 from Student where S_StuNo=@Stu_No)
10 go

执行用户自定义内嵌表值函数:

1 select * from dbo.SearchStuInfo(default);    --返回学号为 001 的学生信息
2 
3 select * from dbo.SearchStuInfo('008');        --返回学号为 008 的学生信息

 

创建多声明表值函数的语法:

1 create function [函数的所有者].函数名(标量参数 [as] 标量参数类型 [=默认值])
2 returns @表变量 table 表的定义(即列的定义和约束)
3 [with {Encryption | Schemabinding }]
4 [as]
5 begin
6     函数体(即 Transact-SQL 语句)
7     return
8 end

下面自定义一个根据传入参数(即学生学号)来查询学生信息和课程,并计算出该学生的身高是否大于所有学生的平均身高的 Function,示例如下:

 1 use Test
 2 go
 3 
 4 create function dbo.SearchStuCou(@StuN varchar(10)='001')
 5 returns @StuInfo table (
 6     StuNo varchar(50) not null primary key,
 7     StuName varchar(10) not null,
 8     StuSex  varchar(10) not null,
 9     StuHeight varchar(10) null,
10     StuAvgHeight decimal(10,2) null,
11     StuConAvgHeight varchar(30) null,
12     StuCou varchar(10) null
13 )
14 as
15 begin
16     declare @Height decimal(10,2)
17     declare @AvgHeight decimal(10,2)
18     declare @ConAvgHeight varchar(30)
19 
20     select @AvgHeight=AVG(Convert(decimal(10,2),S_Height))        --查询所有同学的平均身高
21     from Student 
22 
23     select @Height=Convert(decimal(10,2),S_Height)        --查询指定学号的学生身高
24     from Student 
25     where S_StuNo=@StuN
26 
27     if((@Height-@AvgHeight) > Convert(decimal(10,2),0))
28     begin
29         set @ConAvgHeight='大于平均身高'
30     end
31     else if((@Height-@AvgHeight) = Convert(decimal(10,2),0))
32     begin
33         set @ConAvgHeight='等于平均身高'
34     end
35     else
36     begin
37         set @ConAvgHeight='小于平均身高'
38     end
39 
40     insert into @StuInfo(StuNo,StuName,StuSex,StuHeight,StuAvgHeight,StuConAvgHeight,StuCou)
41     select s.S_StuNo,s.S_Name,s.S_Sex,s.S_Height,@AvgHeight,@ConAvgHeight,c.C_Name from Student s 
42     left join Course c on s.C_S_Id=c.C_Id
43     where S_StuNo=@StuN 
44 
45     return
46 end
47 go

执行用户自定义多声明表值函数:

1 select * from dbo.SearchStuCou(default);
2 
3 select * from dbo.SearchStuCou('015');
4 
5 select * from dbo.SearchStuCou('009');

适用范围

1.      只查询,不修改数据库的状态(修改、删除表中记录等)

2.      结果集需要通过递归等方法得到时,可以使用函数,函数比较灵活

3.      结果集需要直接被引用时,可以使用函数。需要对结果集进行再加工(指放在select语句中等),可以使用函数,函数可以嵌在select等sql语句中。

注意事项:

用户自定义函数不能用于执行一系列改变数据库状态的操作

在编写自定义函数时需要注意的:

对于标量函数:

1.      所有的入参前都必须加@

2.      create后的返回,单词是returns,而不是return

3.      returns后面的跟的不是变量,而是返回值的类型,如:int,char等。

4.      在begin/end语句块中,是return。

内嵌表值函数:

1.      只能返回table,所以returns后面一定是TABLE

2.      AS后没有begin/end,只有一个return语句来返回特定的记录。

多语句表值函数:

1.      returns后面直接定义返回的表类型,首先是定义表名,表明前面要加@,然后是关键字TABLE,最后是表的结构。

2.      在begin/end语句块中,直接将需要返回的结果insert到returns定义的表中就可以了,在最后return时,会将结果返回。

3.      最后只需要return,return后面不跟任何变量。

参考:

http://www.cnblogs.com/xueyuangudiao/archive/2011/08/03/2126580.html

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
目录
相关文章
|
30天前
|
SQL 存储 关系型数据库
MySQL进阶突击系列(01)一条简单SQL搞懂MySQL架构原理 | 含实用命令参数集
本文从MySQL的架构原理出发,详细介绍其SQL查询的全过程,涵盖客户端发起SQL查询、服务端SQL接口、解析器、优化器、存储引擎及日志数据等内容。同时提供了MySQL常用的管理命令参数集,帮助读者深入了解MySQL的技术细节和优化方法。
|
3月前
|
SQL Java 数据库连接
mybatis使用四:dao接口参数与mapper 接口中SQL的对应和对应方式的总结,MyBatis的parameterType传入参数类型
这篇文章是关于MyBatis中DAO接口参数与Mapper接口中SQL的对应关系,以及如何使用parameterType传入参数类型的详细总结。
61 10
|
4月前
|
存储 SQL 关系型数据库
【MySQL调优】如何进行MySQL调优?从参数、数据建模、索引、SQL语句等方向,三万字详细解读MySQL的性能优化方案(2024版)
MySQL调优主要分为三个步骤:监控报警、排查慢SQL、MySQL调优。 排查慢SQL:开启慢查询日志 、找出最慢的几条SQL、分析查询计划 。 MySQL调优: 基础优化:缓存优化、硬件优化、参数优化、定期清理垃圾、使用合适的存储引擎、读写分离、分库分表; 表设计优化:数据类型优化、冷热数据分表等。 索引优化:考虑索引失效的11个场景、遵循索引设计原则、连接查询优化、排序优化、深分页查询优化、覆盖索引、索引下推、用普通索引等。 SQL优化。
680 15
【MySQL调优】如何进行MySQL调优?从参数、数据建模、索引、SQL语句等方向,三万字详细解读MySQL的性能优化方案(2024版)
|
4月前
|
关系型数据库 MySQL 网络安全
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
|
6月前
|
SQL 存储 监控
SQL Server的并行实施如何优化?
【7月更文挑战第23天】SQL Server的并行实施如何优化?
139 13
|
6月前
|
SQL
解锁 SQL Server 2022的时间序列数据功能
【7月更文挑战第14天】要解锁SQL Server 2022的时间序列数据功能,可使用`generate_series`函数生成整数序列,例如:`SELECT value FROM generate_series(1, 10)。此外,`date_bucket`函数能按指定间隔(如周)对日期时间值分组,这些工具结合窗口函数和其他时间日期函数,能高效处理和分析时间序列数据。更多信息请参考官方文档和技术资料。
|
6月前
|
SQL 存储 网络安全
关系数据库SQLserver 安装 SQL Server
【7月更文挑战第26天】
76 6
|
6月前
|
存储 SQL C++
对比 SQL Server中的VARCHAR(max) 与VARCHAR(n) 数据类型
【7月更文挑战7天】SQL Server 中的 VARCHAR(max) vs VARCHAR(n): - VARCHAR(n) 存储最多 n 个字符(1-8000),适合短文本。 - VARCHAR(max) 可存储约 21 亿个字符,适合大量文本。 - VARCHAR(n) 在处理小数据时性能更好,空间固定。 - VARCHAR(max) 对于大文本更合适,但可能影响性能。 - 选择取决于数据长度预期和业务需求。
481 1
|
5月前
|
SQL 安全 Java
驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接。错误:“The server selected protocol version TLS10 is not accepted by client
驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接。错误:“The server selected protocol version TLS10 is not accepted by client
554 0
|
6月前
|
SQL Cloud Native 关系型数据库
云原生数据仓库使用问题之控制JDBC方式请求的SQL大小限制的参数是什么
阿里云AnalyticDB提供了全面的数据导入、查询分析、数据管理、运维监控等功能,并通过扩展功能支持与AI平台集成、跨地域复制与联邦查询等高级应用场景,为企业构建实时、高效、可扩展的数据仓库解决方案。以下是对AnalyticDB产品使用合集的概述,包括数据导入、查询分析、数据管理、运维监控、扩展功能等方面。

热门文章

最新文章