❖掌握⬧ 变量、运算符、控制语句⬧ 游标、存储过程、自定义函数、触发器
❖了解⬧ 系统函数
❖重点⬧ 游标、存储过程、自定义函数、触发器
❖难点⬧ 自定义函数

---

❖第一节 T-SQL编程基础

❖T-SQL 即 Transact-SQL，是 SQL 在 Microsoft SQL Server 上的增强版

⬧ SQL SERVER专用标准结构化查询语言增强版
⬧ T-SQL 提供标准 SQL 的 DDL 和 DML 功能，加上延伸的函数、系统预存程序以及程序设计结构(例如 IF 和 WHILE)让程序设计更有弹性

标识符

❖标识符分类⬧ 常规标识符（严格遵守标识符格式规则）⬧ 界定标识符（引号”或方括号[]）
❖标识符格式规则⬧ 字母或_、@、＃开头的字母数字或_、@、$序列⬧ 不与保留字相同⬧ 长度小于128⬧ 不符合规则的标识符必须加以界定（双引号””或方括号[]）

注释语句

❖注释语句是对程序代码的说明或暂时禁用，是程序代码中不编译执行的语句

⬧ 单行注释 --
例： --求‘3-105’课程的平均分⬧ 多行注释 /**/
例： /* 作者：创建时间： */

表达式

❖数据类型
⬧ 整数数据、字符数据、货币数据、日期和时间数据、二进制字符串等❖变量
⬧ 局部变量
局部变量是用户定义，必须以@开头，在程序内声明，并只能在该程序内使用(1)局部变量的声明declare @<局部变量名> <数据类型>[,…n](2)局部变量的赋值set / select @<局部变量名>=<表达式>

declare @num1 int;	 定义
set @num1=10;     	 赋值
print @num1;      	 打印------------------------------------declare @time1 date,@string1 char(10)
set @time1=getdate()--获取系统时间开始格式转换，date-》char（10）
set @string1=convert(char(10),@time1,102)--目标数据类型，转换谁，风格
print @string1;

---

SET、SELECT区别

❖SET 、SELECT赋值的区别⬧ SET赋值语句一般用户赋给变量指定的数据变量⬧ SELECT赋值语句一般用于表中查询数据，然后查询的记录多于一条，将把最后一条记录的值赋给变量⬧ SET一次只能赋值一个变量        （重点）⬧ SELECT可以一次赋值多个变量➢SELECT @a=1,@b=2

例1：创建一个@myvar 变量，
然后将一个字符串值放在变量中，
最后输出 @myvar 变量的值DECLARE @myvar char(20)
select @myvar = 'This is a test'
SELECT @myvar例2 ：用SET语句和SELECT语句为
局部变量赋值DECLARE @var1 datetime,@var2 char(10)
SET @var1 = getdate()
SELECT @var2 =
convert(char(10),@var1,102)
Select @var2 --显示@var2

⬧ 全局变量

全局变量是SQL Server系统内部使用的变量，以@@开头➢全局变量不是由用户的程序定义的，它们是在服务器级定义的➢用户只能使用预先定义的全局变量T SQL全局变量只能归系统内部受用。主要表示系统基本状态用户不能自定义；

⬧ 例3 ：用全局变量查看SQL Server的版本、当前所使用的SQL Server服务器的名称以及所使用的服务名称等信息

print '目前所用SQL Server的版本信息如下:'
print @@VERSION
print '目前SQL Server服务器名称为：'+@@SERVERNAME
print '目前所用服务器为：'+@@SERVICENAME

❖运算符

⬧ SQL Server 2008的运算符和其他高级语言类似，用于指定要在一个或多个表
达式中执行的操作，将变量、常量和函数连接起来

流程控制语句

选择结构

❖IF ELSE

⬧ IF…ELSE语句用来判断当某一条件成立时执行某段程序，条件不成立时执行另一段程序。其中，ELSE子句是可选的， SQL Server允许嵌套使用IF…ELSE语句，而且嵌套层数没有限制

⬧ 语法格式
IF <布尔表达式><SQL语句>|<语句块>
[ELSE<SQL语句>|<语句块>]

语句块的界定符是 begin end

例：查找有没有学号为201215120的学生，有的话显示学生信息，
没有显式没找到IF EXISTS( SELECT * FROM STUDENT WHERE SNO = ‘201215120’)
BEGINSELECT *FROM STUDENTWHERE SNO = ‘201215120’
END
ELSEPRINT ‘没找到！’EXISTS 子查询 如果子查询结果非空返回真，否则返回假

CASE语句

❖CASE语句可以计算多个条件式，并将其中一个符合条件的结果表达式返回。

CASE语句按照使用形式的不同，可以分为简单CASE语句和搜索CASE语句

```sql
⬧ 简单CASE语句CASE语句CASE 表达式WHEN 表达式的值1 THEN 返回表达式1WHEN 表达式的值2 THEN 返回表达式2…ELSE 返回表达式nEND

❖例：从学生表STUDENT中，选取SNO,SSEX，如果SSEX为‘男’则输出‘M’，如果为‘女’，则输出‘F’SELECT SNO,SSEX=CASE SSEXWHEN ‘男’ THEN ‘M’WHEN ‘女’ THEN ‘F’END
FROM STUDENT

搜索式CASE语句

⬧搜索式CASECASEWHEN 逻辑表达式1 THEN 返回表达式1WHEN 逻辑表达式2 THEN 返回表达式2…ELSE 返回表达式n
END

例：从SC表中查询所有同学选课成绩情况，凡成绩为空者输出‘未考’、小于60输出‘不及格’ 、小于70输出‘及格’ 、小于90输出‘良好’ 、大于等于90输出‘优秀’

SELECT SNO,CNO,GRADE=CASEWHEN GRADE IS NULL THEN ‘未考’WHEN GRADE<60 THEN ‘不及格’WHEN GRADE>=60 AND GRADE<70 THEN ‘及格’WHEN GRADE>=70 AND GRADE<90 THEN ‘良好’WHEN GRADE>90 THEN ‘优秀’END
FROM SC

循环结构

设置重复执行 SQL 语句或语句块的条件。只要指定的条件为真，就重复执行语句。可以使用 BREAK 和 CONTINUE 关键字在循环内部控制 WHILE 循环中语句的执行

WHILE 逻辑表达式begin
BeginT-SQL语句组[break]/*终止整个语句的执行*/[continue]/*结束一次循环体的执行*/
END

❖例 求1 ~10的和

循环结构
DECLARE @X int, @sum int
SET @X=0
SET @sum = 0
WHILE @x<10BEGINSET @X=@X+1SET @sum = @sum + @XPRINT ‘sum='+convert(char(2),@sum) /*类型转换函数convert*/END；declare @num1 int,@num2 int
set @num1=0
set @num2=0while  @num1<10beginset @num1=@num1+1			--赋值前必须有setset @num2=@num2+@num1end
print @num2
print '和'+convert(char(2),@num1)    --不能字符串加号拼接，不支持

等待语句

等待语句挂起一个程序中语句的执行，直到指定的某一时间点到来或在一定的时间间断之后才继续执行
❖语法格式WAITFOR DELAY '<时间间隔>‘ | TIME '<时间>'
⬧ 其中，时间间隔以及时间均为datetime类型，格式为“hh:mm:ss”，分别说明等待的时间长度和时间点，在time内不能指定日期

❖例1：设置等待一小时后执行查询
BEGINWAITFOR DELAY '1:00:00'SELECT * FROM s
END-----------------
waitfor delay '0:00:05'   --后面是延迟时长
print 11;-------------❖例2：设置到十点整执行查询
BEGINWAITFOR TIME '10:00:00'SELECT * FROM s
END

---

返回语句

❖RETURN语句RETURN语句用于无条件地终止一个查询、存储过程或者批处理，此时位于RETURN语句之后的程序将不会被执行⬧ 语法格式RETURN [ integer_expression ]

---

系统函数

标量函数

日期和时间函数

❖例：使用日期时间函数计算自己现在的年龄SELECT ‘年龄’=DATEDIFF(YY,’1979-06-01’,GETDATE())//YY年mm月dd天❖例：返回指定日期中年/月/日的整数日期和时间函数	SELECT YEAR(‘2016-01-08’）

---

字符串函数

❖字符串转换函数

⬧ ASCII（字符串） 函数返回字符表达式最左端字符的ASCII 码值a-97  A-65
⬧ CHAR（整数表达式） 函数用于将ASCII 码转换为字符
⬧ LOWER（字符串） 函数把字符串全部转换为小写
⬧ UPPER（字符串） 函数把字符串全部转换为大写print ascii('asdf')       97

❖去空格函数

⬧ LTRIM（字符串） 函数把字符串头部的空格去掉
⬧ RTRIM（字符串） 函数把字符串尾部的空格去掉

❖取子串函数

⬧ LEFT(字符串,int num)函数返回的子串是从字符串最左边起到第num个字符的部分。若num为负值,则返回NULL值
⬧ RIGHT(字符串,int num)函数返回的子串是从字符串最右边起到第num个字符的部分。若num为负值,则返回NULL值⬧ SUBSTRING (字符串,int pos,int length）函数返回的子串是从字符串左边第starting_
position 个字符起length个字符的部分。SUBSTRING（）函数不能用于TEXT 和
IMAGE 数据类型print substring ('student',2,4)
参数1：目标字符串，参数2：截取的位置，参数3：截取长度

❖字符串比较函数

⬧ CHARINDEX (<要找的子串>，字符串)
函数返回字符串中某个指定的子串出现的开始位置，
如果没有发现子串，则返回0 值⬧ PATINDEX (<’%substring _expression%’>， <字符串>) 函数返回字符串中某个指定的
子串出现的开始位置，其中子串表达式前后必须有百分号“%”否则返回值为0

❖字符串操作函数

⬧ QUOTENAME（） 函数返回被特定字符括起来的字符串
⬧ REPLICATE（） 函数返回一个重复character_expression 指定次数的字符串
⬧ REVERSE（） 函数将指定的字符串的字符排列顺序颠倒
⬧ REPLACE（） 函数返回被替换了指定子串的字符串
⬧ SPACE（） 函数返回一个有指定长度的空白字符串
⬧ STUF（）函数用另一子串替换字符串指定位置、长度的子串

数据类型转换函数

❖转换函数有两个：CONVERT和CAST⬧ CAST函数允许把一个数据类型强制转换为另一种数据类型，其语法形式为：➢CAST( expression AS data_type )参数：转换谁，目的类型是什么⬧ CONVERT函数允许用户把表达式从一种数据类型转换成另一种数据类型，还允许把日期转换成不同的样式，其语法形式为：➢CONVERT (data_type[(length)],expression [,style])参数1：目标类型 ，参数2：转换目标，参数3：目的风格❖例：
查询学生基本信息表STUDENT中的学号、姓名、年龄，并且将这三个字段通过“+”
运算符连接显示在查询结果中SELECT SNO+SNAME+CAST(SAGE AS CHAR(2))FROM STUDENT

---

❖第二节 游标（重点）

	类似一指向一行的指针

❖游标是一种能从包括多条数据记录的结果集中每次提取一条记录的机制

❖游标的使用⬧ 声明游标⬧ 打开游标⬧ 读取游标中的数据⬧ 关闭游标⬧ 释放游标

1. 声明游标

❖语法格式：DECLARE <游标名> [INSENSITIVE] [SCROLL]CURSORFOR <SELECT语句>[FOR {READ ONLY | UPDATE [OF <列名> [,...n]]}]⬧ Insensitive 指定游标只对基本表的副本操作，游标的任何操作不对基本表产生影响
⬧ Scroll 指定游标推进方向（ FIRST、LAST、PRIOR、NEXT、RELATIVE、
ABSOLUTE）均可用，否则只有next可用

.

例：声明一个游标，统计没有选修课程的学生的人数declare num_cursor cursor	//声明 名字 游标关键字（确定类型）
for   //后面加查询内容select sno from student
for READ ONLY   //放到哪里

2. 打开游标

游标声明后，如果要从游标中读取数据，必须打开游标OPEN <游标名>
open num_cursor;

3. 读取游标中的数据

❖当游标被打开后，就可以从游标中逐行地读取数据FETCH[[NEXT|PRIOR|FIRST|LAST|ABSOLUTE {n|@nvar} //n，绝对定位|RELATIVE {n|@nvar}]   //相对定位FROM ] //游标的名字，表示来源{{[GLOBAL] <游标名>}|<@游标变量>}[INTO @<变量名>[,...n]]  //提取的数据放在哪，可以放在多个之中⬧ 默认情况下，指针指向第一条记录之前

❖执行FETCH语句后，可通过@@FETCH_STATUS全局变量返回游标
当前的状态。@@FETCH_STATUS 变量有三个不同的返回值
⬧ 0：FETCH 语句执行成功
⬧ -1：FETCH 语句执行失败或者行数据超出游标数据结果集的范围
⬧ -2：表示提取的数据不存在

declare num_cursor cursor --声明
for select sno from student
for READ ONLYOpen num_cursor; --打开
declare @sno varchar(10),@num int --声明变量
set @num = 0fetch next from num_cursor --取信息
into @sno
while @@fetch_status = 0 --检测状态
beginif not exists(select *from scwhere sno = @sno)set @num = @num + 1fetch next from num_cursorinto @sno
end
select @num 未选课人数
CLOSE num_cursor
DEALLOCATE num_cursor

4. 关闭游标

❖使用CLOSE命令关闭游标
⬧ 处理完游标中数据后，必须关闭游标来释放数据结果集和定位于数据记录上的锁
⬧ 语法格式:CLOSE [GLOBAL] <游标名>|@<游标变量> ⬧ CLOSE语句可以关闭游标，但不释放游标的数据结构➢如果要再次使用游标，可用OPEN命令重新打开
❖自动关闭游标

5. 释放游标

❖用CLOSE命令关闭游标并没有释放游标占用的数据结构。使用
DEALLOCATE命令将释放游标占用的数据结构，游标使用的任何资
源也随之释放
⬧ 语法格式DEALLOCATE [GLOBAL] <游标名>|@<游标变量>
⬧ 游标的关闭指释放游标的结果集所占用的资源，游标的释放指释放游标占用
的所有资源，当然也包括结果集占用的资源

--1. 声明游标
declare youbiao cursor	--//声明 名字 游标关键字（确定类型）
for   --//后面加查询内容select sno from student
for READ ONLY  -- //放到哪里
--2. 打开
open youbiao
--3. 操作
declare @sno char(10)		--临时存放
declare @num int	
set @num=0;		--人数fetch next from youbiao into @sno
while @@FETCH_STATUS=0 --判断是否提取成功beginif not exists(select * from sc where sno=@sno)set @num=@num+1fetch next from youbiao into @snoend
print @num
--4. 关闭
close youbiao
--5.释放
deallocate youbiao

例：根据学生成绩计算统计各个等级的人数：
[90-100]为A, [80-89]为B
[70-79]为C, [60-69]为D
[0-59]为E-- 定义局部变量
DECLARE @mygrade int,@mylevel char(1)
DECLARE @E int,@D int,@C int,@B int,@A int
select @E = 0,@D = 0,@C = 0,@B = 0,@A = 0-- 下面定义游标.
DECLARE level_cursor CURSOR FOR
SELECT grade
FROM students.sc-- 下面打开游标.
OPEN level_cursor-- 下面从游标中取出第一行，放到
--对应的变量中.
FETCH NEXT FROM level_cursor
INTO @mygrade--循环处理
WHILE @@FETCH_STATUS = 0
BEGIN-- 计算级别.if @mygrade is nullset @E = @E + 1elseif @mygrade<60set @E = @E+1elseif @mygrade<70set @D = @D +1elseif @mygrade<80set @C = @C + 1elseif @mygrade < 90set @B = @B + 1elseset @A = @A + 1
-- 从游标中取下一行.
FETCH NEXT FROM level_cursor
INTO @mygrade
END
-- 关闭游标.
CLOSE level_cursor
--释放资源
DEALLOCATE level_cursor
select @E,@D,@C,@B,@A

课堂练习

❖根据员工工资计算其个人所得税，3000元为起征点，超出3000元的部
分按照10%的比例征收个人所得税
例如：工资表如下

1号员工个人所得税为10元，2号员工个人所得税为50元，3号员工个人所得税为80元…，请使用游标编写一段Transact-SQL程序段，计算每个员工的个人所得税并更新员工工资表中的个人所得税

-- 定义局部变量
declare @salary int ,@cno int
--声明游标
declare salary_cursor cursor
forselect 工资，员工编号from 员工工资表
for read only
open salary_cursor ; -- 打开游标.
fetch next from salary_cursor
into @salary,@cno
--循环处理
while @@FETCH_STATUS = 0
beginif @salary>3000beginupdate 员工工资表 set 个人所得税=@salary*0.1 where 员工编号=@cno;//这行代码有错位，老师故意的。end
end
-- 关闭游标.
close salary_cursor
--释放资源
deallocate salary_cursor

---

❖第三节 存储过程

❖存储过程（Stored Procedure）是一组完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行存储过程

类似于C语言的函数

❖存储过程的优点

⬧ 存储过程已在服务器注册
⬧ 存储过程具有安全特性
⬧ 存储过程可以强制应用程序的安全性
⬧ 存储过程允许模块化程序设计
⬧ 存储过程可以减少网络通信流量重点是 安全 与 模块化

创建存储过程

❖语法：
1. 创建存储过程
CREATE { PROC | PROCEDURE } [schema_name.] procedure_name  //关键词，架构名称，存储的名字
[ { @parameter data_type }[ VARYING ] [ = default ] [ OUTPUT ] ] [ ,...n ]//形参（输入型，输出型）
[ WITH RECOMPILE | ENCRYPTION]
AS
{ <BEGIN>
<sql_statement> [;][ ...n ]
<END>
}❖例1：从SC表中查询不及格课程超过3门的学生信息
示例
create procedure myproc
as
beginselect * from student where sno in(select snofrom scwhere grade<60group by sno having count(*)>3) 
end-----------------------------------------------------
❖例2：将指定记录插入student表,带参数的
示例
create proc proc_insert_student@sno varchar(10),    //这是五个形式参数，与下面一一对应@sname varchar(20),	 //形参，后面第二部执行才是实参@ssex varchar(2) = "男",@sage smallint,@sdept varchar(50)
as
begininsert into student(sno,sname,ssex,sage,sdept)values(@sno,@sname,@ssex,@sage,@sdept)
end带输出参数的存储过程
❖例3 查询指定学号学生的平均成绩，并将平均成绩返回create proc proc_avergrade
@sno varchar(10),	--输入参数
@savg int out 		--输出参数，注意out
as
beginselect @savg = avg(grade) //边查询边赋值from scwhere sno = @sno
end❖请编写一个存储过程proc_sum，输入参数为学院，输出参数为人数，功
能为根据输入的学院，统计该学院的学生人数，并返回学生人数。学生表
的结构为（sno,sname,sex,department）各个字段含义为学号、姓名、性别
、学院
create proc proc_sum@department char(9),@p_num int out//输出型的
as
beginselect @p_num=count(*)from studentwhere department=@department
end

1.编写一个存储过程，在sc表统计每个学生的平均分
2.对练习1的存储过程进行改进，添加一个输入参数——学号，使存储
过程能根据输入的学号计算该学生的平均分
3.在练习2的基础上添加一个输出参数——平均分，计算指定学号的平
均分，然后将平均分输出-----练习1
create proc proc1
as
beginselect avg(grade)from scgroup by sno 
end--练习2
create proc proc2
@sno char(9)
as
beginselect avg(grade)from scwhere sno=@sno
end--练习3,最常见的
create proc proc3
@sno char(9),
@savg int out
as
beginselect @savg=avg(grade)from scwhere sno=@sno
end

执行存储过程

❖语法格式：
EXEC|EXECUTE [ @return_status = ] [schema_name.]procedure_name
[[@parameter =] {value | @variable [OUTPUT] | [ DEFAULT ]}][ ,...n ]❖例 执行例2 的存储过程
方法一：
exec proc_insert_student '200901031','张三','男',18,'软件学院'
方法二；
exec proc_insert_student @sname='200901031'这样也行❖例 执行例3的存储过程
declare @avg int
set @avg = 0
exec proc_avergrade '200215121',@avg out//结果返回放在局部变量里，用实参代替形参
select @avg

删除存储过程

❖删除存储过程可以使用DROP命令，DROP命令可以将一个或者多个
存储过程或者存储过程组从当前数据库中删除，其语法形式如下：
drop procedure {procedure_name}[,…n]

❖第四节 自定义函数

自定义函数那里也可以出现

❖SQL Server 2008支持3种类型的Transact-SQL用户自定义函数：标量函数、内嵌表值函数和多语句表值函数

要求掌握标量与内嵌表函数。
自定义函数比存储函数执行更快

⬧ 标量函数返回一个标量(单值)结果
⬧ 内嵌表值函数返回一个table数据类型
⬧ 多语句表值函数返回的数据必须存放于临时表中（性能不好）

❖在SQL Server中使用用户自定义函数有以下优点:

⬧ 允许模块化程序设计
⬧ 执行速度更快
⬧ 减少网络流量

.标量函数

❖创建语法：
CREATE FUNCTION [ schema_name.] function_name  //必须function
([{ @parameter_name [ AS ] data_type [ = default ] } [ ,...n ]])
RETURNS return_data_type    //returns 返回类型名字
[ WITH <ENCRYPTION>|<SCHEMABINDING> [ ,...n ] ]
[ AS ]BEGINfunction_bodyRETURN scalar_expression
END

❖定义一个函数返回不带时间的日期,标量值函数
CREATE FUNCTION dbo.DateOnly(@date datetime)  //输入型参数
RETURNS VARCHAR(12)   //返回的类型
ASBEGINRETURN CONVERT(VARCHAR(12),@DATE,101)//返回结果，格式转换end//执行
select dbo.DateOnly(getdate())

❖请写一个函数whichgeneration，根据输入的参数生日 (birthday datetime)
来判断学生是“80后”还是“90后”，如果是“80后”返回字符串
“80s”，如果是“90后”返回“90s”，其他返回“too old”CREATE FUNCTION whichgeneration(@birthday datetime)
RETURNS VARCHAR(12)
AS
BEGINif year(@birthday)<1980return 'too old';else if year(@birthday)<1990return '80s';elsereturn '90s';return 0  --SQL server不加无法运行，但是标准语法是没有的
ENDselect dbo.whichgeneration('1979-06-01')

.内嵌表值函数

❖创建语法：
CREATE FUNCTION [ schema_name. ] function_name
([{ @parameter_name data_type [ = default ] } [ ,...n ]])RETURNS TABLE  --后面是table[ WITH <function_option> [ ,...n ] ]
[ AS ]
RETURN ( select_stmt )  --返回表❖定义一个函数返回学生的学号和姓名
CREATE FUNCTION dbo.Fun1()  --这个里面无参数
RETURNS table
AS
RETURN
SELECT SNO,SNAME
FROM STUDENT --打印
select * from fun1() ❖查看计算机系学生的成绩
CREATE FUNCTION attendance(@sdept varchar(20) )
RETURNS TABLE
AS
RETURN
(SELECT student.sno,cno,gradefrom sc,studentwhere student.sno = sc.sno andsdept = @sdept
）create function fun2(@sdep char(10))
returns table
as
return select * from studentwhere sdept=@sdep 
---执行select * from fun2('cs');

.多语句表值函数（不要求）

❖创建语法：
CREATE FUNCTION [ schema_name. ] function_name
([{@parameter_name data_type [ = default ] } [ ,...n ]])
RETURNS @return_variable TABLE < table_type_definition >
[ WITH <function_option> [ ,...n ] ]
[ AS ]
BEGIN
function_body
RETURN
END❖现有一个员工表test （字段省），一个部门表bm （字段省）。查询
某部门的员工信息⬧ 创建内嵌表值函数
CREATE FUNCTION fn_bumen
(@bm char(2))
returns table
as
return (select * from test where 部门=@bm) ⬧ 创建多语句表值函数
多语句表值函数示例
CREATE FUNCTION fn_salary ( @bm char(2) )
RETURNS @salary table
(姓名 varchar(10),部门名称 varchar(10),工资 numeric(8,2))
as
begin
insert @salary
select a.姓名,b.部门名称,a.工资
from test a left join bm b on a.部门=b.部门 and a.部门=@bm
return
end

❖第五节 触发器