declare 放在with 前面 sql server declare @sql

我们之前讲过，SQL是一种结构化（Structured）的语言（Language），所以它也必然具有以下要素：

变量（复习）

可以把变量看成一个存储数据的容器。使用变量必须首先声明（创建），然后再赋值（讲数据装入这个容器中） 。

DECLARE

也可以一次性声明多个变量：

DECLARE

还可以在声明变量的同时给他赋值

DECLARE

注意，SQL中的变量必须以@开头，以一个@开头的变量被称为局部变量。所谓“局部”，是指他的作用域（也就是能够产生作用、有限的区域）是局部的，而不是全局的。具体来说，局部变量的作用域位于一个批处理语句中。

SQL Server中还有一种变量，是使用@@开头的，被称之为全局变量。全局变量可以不受批处理的约束，可以在任何地方直接使用。但用户不能定义全局变量，全局变量只能由SQL SERVER定义。

但我就是要定义一个全局变量呢？比如：

DECLARE

SQL Server会将其作为一个名为@name（不是name）的局部变量使用。

演示：局部变量和全局变量的区别

常用的全局变量有：

PRINT

数据类型和运算符（复习）

分支和循环

分支使用的只有两个关键字：IF和ELSE（复习）

猜一猜这段代码的结果：

DECLARE

如果改成@age<18呢？=18呢？

为什么呢？因为@age声明过后没有被赋值，它的值为NULL；而NULL值和任何值进行任何比较，结果都为假。

通常我们在使用IF...ELSE...时，会配合使用BEGIN...END，以界定IF或ELSE的范围。

SQL只有一个循环关键字：WHILE

WHILE后面接条件，只要条件为真，就会一直循环执行WHILE引导（通常由BEGIN...END界定）的循环体语句。循环无法终止，就是“死循环”，这是一定要注意避免出现的。所以在WHILE的循环体中，通常都会设置语句，使得一定条件下WHILE条件为假，以结束循环。比如：

DECLARE

运行上述SQL语句，会输入1，2，3，4，5。

使用这些分支循环，就可以构建大量的、复杂的SQL语句，这些SQL语句需要被有效的管理，函数应运而生。（复习：人人都是程序猿）

SQL Server中函数的最大特点： 必须有一个返回值 。根据返回值类型，我们可以将其分为：

标量函数：返回一个简单类型的单一值。

其函数声明的SQL代码如下：

CREATE

注意：

1. 函数不属于任何一张表，而属于整个数据库，所以函数名在整个数据库中不能重复
2. 函数的命名规则见：，
3. 函数的参数和变量声明一样，必须用@开头，且指明类型
4. 标明函数返回类型时使用的关键字是RETURNS，带了一个S；函数体内标明返回值时，使用的是RETURN，没有S
5. BEGIN和END是不能省略的
6. 函数体中：

1. 不能对数据库中的数据进行修改，比如使用INSERT/UPDATE/DELETE等
2. RETURN语句只能在函数体的最后

声明函数之后，运行，就可以在SQL Server Object Explorer中看到这个函数了：

可以看到，YzAdd被添加到：数据库17bang -> Programmability -> Functions -> Scalare-valued Functions下面，而且被自动的添加了 dbo 的schema。

然后，我们就可以调用它了：

PRINT

注意：Scalar函数必须指定schema_name（这里是dbo）

函数还可以被 修改，这需要使用 ALTER

ALTER

运行上述SQL语句，函数就会被修改，在SQL Server Object Explorer中查看：

@b已经变成了Default（区别于@a的No default）。

设置了默认值的参数，可以用两种方式调用：

PRINT

此外，函数还可以被加密，以免被其他用户看到函数的具体实现。未加密的函数，在函数上右键点击 View Code，就能查看到函数的“源代码”

但是，如果创建（或修改）函数时添加了WITH ENCRYPTION：

ALTER

再去View Code，就会发现：无法查看到函数体（函数的定义，如名称、参数、返回值等还是可以看到的）

CREATE

SQL函数的另一个特殊性，就表现在它的返回值可以是“表”，这种函数被称之为：表值函数。注意这里所称的表，实际上是一种具有表结构的数据，它和使用CREATE Table生成的表不一样：数据库中并没有这样一张表，这张“表”是通过函数运算而“临时”获得的。

表值函数又可以被细分为：

单行表值函数（Inline Function）

其SQL语句如下：

CREATE

除了RETURNS后直接接TABLEG关键字，这种函数的特点是：函数体只能有一句SELECT语句，且不能由BEGIN...END包裹。其他（如DEFAULT参数和WITH ENCRYTION）都和标量函数一样。

调用YzInlineTable函数，会返回一个“表”，所以，我们也要像表一样使用它：

--不是：PRINT YzInlineTable(2)，而是：

注意：调用表值函数时可以不需要使用schema，¯_(ツ)_/¯

如果函数中需要封装更复杂的逻辑，我们就只有使用多行表值函数（Multi-Statement Function）了。它同样是返回一个表，但它和标量型函数一样有一个用BEGIN-END 语句括起来的函数体，返回值的数据是由函数体中的语句插入的。

SQL代码及语法代码如下所示：

CREATE

其实，随着软件工程的发展，让数据库承担“增删改查”的单一职能，越来越成为主流。利用T-SQL生成函数封装业务逻辑的情形越来越少。目前我们经常使用的是：

系统函数

即由SQL Serer系统内置的、已经定义好了函数。包括：

聚合函数：分组（GROUP BY）查询统计时使用 （复习）

数值函数： 对数值进行运算，以下用value表示需传入的、被运算数值参数

• 四舍五入：ROUND(value, n) ，把小数value舍入为n位小数位数。比如：ROUND(9.865, 2)，结果值为9.870
• 取整：

1. FLOOR(value)，向下取整 ，比如：FLOOR(3.8)，结果为3；
2. CEILING(value)，向上取整，比如：CEILING(3.8)，结果为4

• 随机：

3. RAND(value)，获得一个以value为“种子（seed）”的小于1的随机小数。（注意：value固定，生成的随机值也就是固定的）
4. RAND()，获得一个以系统当前时间为种子的随机小数；

文本函数： 对字符串进行运算，以下用value表示需传入的、被运算字符串参数

• 大小写：

1. LOWER(value) ：将value转变成小写，比如：LOWER('AbC')，结果为'abc'
2. UPPER(value)：将value转变成大写，比如：UPPER('AbC')，结果为'ABC'

• 修剪，删除空格

3. LTRIM(value)：删除value中左边开头的空格，比如：LTRIM(' AB C ')，结果为'AB C '
4. RTRIM(value) ：删除value中右边开头的空格，比如：RTRIM(' AB C ')，结果为' AB C'

• 取长度：LEN(value)，取value的长度，但注意value末尾的空格不会被计入长度，即：
  ' AB C '和' AB C'的长度是一样的，都是9（左边有5个空格，AB和C中间也有一个空格）
• 截取：

5. LEFT(value, n) ，从左向右截取value的n位字符，比如：LEFT(N'源栈欢迎您',2)，结果为'源栈'
6. RIGHT(value, n) ，从右向左截取value的n位字符，比如：RIGHT(N'源栈欢迎您',3)，结果为'欢迎您'
7. SUBSTRING(value, start, n) ，从start开始，从左向右截取value的n个字符，比如：SUBSTRING(N'源栈欢迎您',3,2)，结果为'欢迎'

• 查找：CHARINDEX(target, value, n) ，在value中从第n位字符开始查找target，

8. 找到返回target在string中的起始位置，比如：CHARINDEX(N'欢迎', N'源栈欢迎您', 1) ，结果为3
9. 找不到返回0，比如：CHARINDEX(N'欢迎', N'源栈欢迎您', 4)

• 替换：REPLACE(value, target, substitute) 在string找到target，用substitute替换它。比如： REPLACE(N'源栈欢迎您', N'您', N'英俊的你')，结果为'源栈欢迎英俊的你'
• 重复：REPLICATE(value, n) ，将value重复n遍，比如：REPLICATE(N'源栈欢迎您', 2)，结果为'源栈欢迎您源栈欢迎您'
• 空格填充：SPACE(n) ，生成n个空格，比如：SPACE(10)，结果为10个空格

日期函数： 对日期进行运算，以下用value表示需传入的、会被运算的日期参数

• 当前时间：GETDATE()，获取当前时间
• 年月日：获取日期的年/月/日

1. YEAR(value)：value上的年份，比如：YEAR('2019/10/21')，结果为：2019
2. MONTH(value)：value上的月份，比如：MONTH('2019/10/21')，结果为：10
3. DAY(value) ：value上的日期，比如：DAY('2019/10/21')，结果为：21

• 增加：DATEADD(datetype,n,value)：在value日期的基础上增加n个datetype单位的时间。datetype可以为：YEAR/MONTH/WEEK/DAY/HOUR/MINUTE/SECOND。比如：DATEADD(YEAR, 1, '2018/10/21')表示在2018年10月21日的基础上再增加1年，结果为'2019/10/21'；n值也可以为负数，表示减少，比如DATEADD(YEAR, -1, '2018/10/21')，结果为'2017/10/21'。
• 计算时间差：DATEDIFF(datetype, start, end)，比较start和end两个日期之间相差多少个datetype单位的时间。比如：DATEDIFF(DAY, '2018/10/21', '2018/12/2')表示计算'2018/12/2'比'2018/10/21'多了多少天，结果为42
• 截取：DATEPART(datetype,value)，截取value中的datetype，比如DATEPART(HOUR, '2018/12/2 23:11')就是截取'2018/12/2 23:11'中的小时数，结果为23

类型转换函数：

T-SQL是一种强类型语言，不兼容类型的数据之间不能进行运算；兼容的类型进行运算，也需要进行类型转换。转换分为两种，一种是隐式（implicit）转换，即SQL Server自动的为你进行的转换，比如：

DECLARE

隐式转换不需要我们使用任何类型转换函数，很方便，但规则都是T-SQL预定义的（参考：类型优先级）。比如：

PRINT

如果我们想将27.83转换成整数再运行呢？这就需要使用函数：CAST(value AS datatype) 或 CONVERT(datatype, value)进行显式（explicit）转换

PRINT

作业：

1. 利用循环，打印如下所示的等腰三角形：

1. 定义一个函数GetBigger(INT a, INT b)，可以取a和b之间的更大的一个值
2. 创建一个单行表值函数GetLatestPublish(INT n)，返回最近发布的n篇求助
3. 创建一个多行表值函数GetByReward(INT n, BIT asc)，如果asc为1，返回悬赏最少的n位同学；否则，返回悬赏最多的n位同学。
4. 在表TProblem中：

• 找出所有周末发布的求助（添加CreateTime列，如果还没有的话）
• 找出每个作者所有求助悬赏的平均值，精确到小数点后两位
• 有一些标题以test、[test]后者Test-开头的求助，找到他们并把这些前缀都换成大写

每日单词：

---

感谢童鞋们的阅读！^_^
我就是：黑律师/包工头/创业狗/老码农……现在还是教书匠的大飞哥。
再次重申这个系列的目标是：
1）通俗易懂。2）实战为主。3）面向就业。
系列内容的完善需要你的反馈！
欢迎点赞和评论，以及加入我们的QQ交流群：326801052。