什么是Transact-SQL？
标准SQL不支持过程化控制, 不能完成复杂的功能.
T-SQL是过程化SQL语言, 是SQL的扩展
增加了过程化语句 (变量，赋值，分支，循环...)
是数据库服务器端的编程，不同于客户端的应用程序
不是标准语言 (ORACLE中称为PL/SQL)
作用：
编写批处理、存储过程，函数，触发器
一 SQL-SERVER批处理
下面的示例创建两个批处理。
USE STUDB --这是使用STUDB数据库
GO DECLARE @RS int /* 定义变量@RS */
SET @RS = (SELECT COUNT(*) /* 变量赋值 */
FROM STUDENT
WHERE SDEPT='CS' )
PRINT '计科学院学生人数 ' + CAST(@RS AS char (3))
GO
用GO 命令结束一个批处理
两种类型的注释方式
一般保存到批处理文件中，以后打开执行，文件名后缀为.SQL
控制语句
BEGIN...END
BEGIN...END：一组要执行的 T-SQL 语句可以包含在 BEGIN...END 中。相当于C语言的复合语句{ }
语法：
BEGIN
{
statement
| statement_block
}
END
其中，statement 为语句，statement_block 为语句块。
IF...ELSE
IF...ELSE：可以根据指定的条件来执行不同的SQL 语句。
语法：
IF Boolean_expression
{ sql_statement|statement_block }
ELSE
{ sql_statement|statement_block }
IF 语句示例：
IF（SELECT COUNT(*) FROM SC WHERE CNO=1 )>20--单值子查询可以当做表达式
BEGIN
……
END
ELSE
BEGIN
……
END
WHILE 循环
WHILE：可以根据某些条件来执行一条 SQL 语句或一组语句。只要指定的条件为真，则重复执行该语句。
语法：
WHILE Boolean_expression
{
statement | statement_block
}
BREAK
{
statement | statement_block
}
CONTINUE
例题：
给1号课加分，如果平均分少于 75，每人加1分，如果最高分已经达到95，则停止加分 。
USE STUDB
GO
WHILE (SELECT AVG(GRADE) FROM SC WHERE CNO=1) < 75 --单值子查询可以当做表达式
BEGIN --循环体用BEGIN END
IF (SELECT MAX(grade) FROM SC WHERE CNO=1) >= 95
BREAK
UPDATE SC
SET GRADE=GRADE+1 WHERE CNO=1
END PRINT '调整完成!'
GO
变量
两种类型的变量：
全局变量
全局变量是SQL Server系统内部使用的变量，任何程序均可调用。
局部变量
自定义变量, 作用于局部
全局变量：
SQL Server 中的全局变量都用'@@' 标记作为前缀。
可以使用PRINT 输出全局变量的值。
输出SQL SERVER版本
PRINT @@VERSION
输出上一个SQL语句影响的行数
SELECT * FROM SC WHERE CNO=1
PRINT @@ROWCOUNT
局部变量：
局部变量名必须以 '@' 为前缀， 先定义，再使用。用DECLARE定义, 类型同表的列类型。
作用域：声明局部变量的批处理、存储过程或语句块。
DECLARE @姓名 CHAR(20)
DECLARE @年龄 INT
DECLARE @工资 FLOAT
DECLARE @地址 VARCHAR(40)
DECLARE @SNO NUMERIC(5), @SNAME CHAR(6), @SAGE INT
注意：变量的初值为空值null ！
两种方法为变量赋值
SET 语句或 SELECT 语句用于给局部变量赋值。
DECLARE @姓名 CHAR(10), @年龄 INT
SET @姓名 ='刘晨' --SET一次只能赋值一个变量
--赋值单值子查询结果
SET @姓名 = (SELECT SNAME FROM STUDENT WHERE SNO=95001)
或：
DECLARE @姓名 CHAR(10), @年龄 INT
SELECT @姓名 ='刘晨',@年龄=20 --SELECT可多 赋值
--赋值单值查询结果
SELECT @姓名 = SNAME, @年龄=SAGE
FROM STUDENT
WHERE SNO=95001
注意这里给赋值单值子查询SET SELECT的区别，SELECT已经有了一个SELECT所以可以省去不写，但SET必须写，否则会报错！！
SET @CCOURSE =( SELECT COUNT(*) FROM SC WHERE SNO=@SNO )--SET必须写SELECT
SELECT @AVG =(SELECT AVG(GRADE) FROM SC WHERE SNO=@SNO)
SELECT @AVG = AVG(GRADE)
FROM SC
WHERE SNO=@SNO --SELECT可以省去不写
局部变量的使用实例：
DECLARE @姓名 CHAR(10)
SET @姓名 ='刘晨'
SELECT * FROM STUDENT
WHERE SNAME=@姓名--变量可以用在SQL语句中
GO
变量值的输出
格式一：单表达式输出
功能：一次只能输出一个局部变量值或一个表达式的值。
例句：
print @x+4
print @y
格式二： 多表达式输出
功能：一次可输出若干个局部变量值或表达式的值。按查询表的格式输出，一般不用。
例句：
select @x, @y+100
T-SQL语法总结
变量定义: DECLARE @变量名 类型
变量赋值: SET @变量=表达式
SET @变量=( 单值子查询 )
SELECT @变量1=表达式1,@变量2=表达式2
SELECT @变量1=列1,@变量2=列2 FROM 表 WHERE 条件
输出: PRINT ‘输出结果是’+CAST( @变量名 AS CHAR(3))
条件: IF 条件表达式
语句 /*多条语句要用BEGIN END*/
ELSE
语句 /*多条语句要用BEGIN END*/
循环: WHILE 条件表达式
BEGIN
语句
END
二 SQL-SERVER 存储过程
什么是存储过程？
存储过程（procedure）类似于C语言中的函数
存储过程可以带参数。
void sum(int a,int b)
{
int s;
s =a+b;
printf("sum=%d\n", s) ;
}
存储过程的分类
系统存储过程
由系统定义，存放在master数据库中，类似C语言中的系统函数，系统存储过程的名称都以“sp_”开头
用户自定义存储过程
由用户在自己的数据库中创建的存储过程，经编译和优化后存储在数据库服务器中，使用时只要调用即可。类似C语言中的用户自定义函数。
常用的系统存储过程：
如何创建存储过程？
创建存储过程的语法
CREATE PROCEDURE 存储过程名 [参数]
AS
SQL语句
GO
执行存储过程的语法
EXEC 存储过程名 [参数]
建立存储过程， 显示计科学院的学生人数：
CREATE PROCEDURE CSCOUNT
AS
DECLARE @RS int /* 定义变量@RS */
SET @RS = (SELECT COUNT(*) /* 变量赋值 */
FROM STUDENT
WHERE SDEPT='CS' )
PRINT '计科学院学生人数 ' + CAST(@RS AS char (3))
GO
执行存储过程语句：
EXEC CSCOUNT
创建不带参数的存储过程
问题：计算机等级考试分为笔试成绩和机试成绩，
请创建存储过程，查看本次考试平均分以及未通过考试的学员名单
需要定义什么变量?
CREATE PROCEDURE proc_stu --proc_stu为自定义的存储过程名
AS
DECLARE @writtenAvg float,@labAvg float
SELECT @writtenAvg=AVG(writtenExam),
@labAvg=AVG(labExam) FROM Marks
print '笔试平均分：'+CAST(@writtenAvg AS CHAR(3))
print '机试平均分：'+CAST(@labAvg AS CHAR(3))
IF (@writtenAvg>70 AND @labAvg>70) --显示考试成绩的等级
print '本班考试成绩：优秀'
ELSE
print '本班考试成绩：较差'
print '--------------------------------------------------'
print ' 参加本次考试没有通过的学员：'
SELECT stuName,stu.stuNo,writtenExam,labExam--显示未通过的学员
FROM stu, Marks
WHERE stu.stuNo=Marks.stuNo AND (writtenExam<60 OR labExam<60)
GO
调用存储过程
EXEC PROC_STU
带输入参数的存储过程
CREATE PROCEDURE DEPTCOUNT
@DEPT CHAR(2) /* 参数变量 注意位置 无关键字DECLARE*/
AS
DECLARE @RS int /* 定义变量@RS */
SET @RS = (SELECT COUNT(*) /* 变量赋值 */
FROM STUDENT
WHERE SDEPT=@DEPT )
PRINT @DEPT+'学院学生人数 ' + CAST(@RS AS char (3))
GO
执行存储过程语句：
EXEC DEPTCOUNT 'CS'
练习: 建立过程 PSTU, 输出某学生的姓名和总学分.
过程参数? 变量? 代码?
DROP PROCEDURE PSTU
GO
CREATE PROCEDURE PSTU
@SNO INT
AS
DECLARE @NAME CHAR(20)
DECLARE @TOTAL INT
SELECT @NAME=SNAME FROM STUDENT WHERE SNO=@SNO
SELECT @TOTAL=SUM(CCREDIT) FROM COURSE,SC
WHERE SC.SNO=@SNO AND SC.CNO=COURSE.CNO
PRINT'Name='+@NAME+'Total credit='+CAST(@TOTAL AS CHAR(3))
GO
如何执行?
EXEC PSTU 95001
练习:
建立过程 PSTU2, 输出某学生的姓名, 选课门数, 平均分, 最高分,最低分, 总学分, 如果总学分<9,则显示“此学生学分不足！”，否则显示“此学生学分已足！”，
过程参数? 变量? 代码?
DROP PROCEDURE PSTU2
GO CREATE PROCEDURE PSTU2
@SNO INT
AS
DECLARE @NAME CHAR(20),@CCOURSE INT,@TOTAL INT,@AVG INT
SELECT @NAME=SNAME FROM STUDENT WHERE SNO=@SNO
SELECT @CCOURSE =COUNT(*) FROM SC WHERE SNO=@SNO
SELECT @TOTAL= SUM(CCREDIT) FROM COURSE,SC WHERE SC.SNO=@SNO AND SC.CNO=COURSE.CNO
SELECT @AVG = AVG(GRADE) FROM SC WHERE SNO=@SNO
PRINT'Name='+@NAME+ '选课门数'+CAST(@CCOURSE AS CHAR(5))+'平均分'
+CAST(@AVG AS CHAR(5))+'Total credit='+CAST(@TOTAL AS CHAR(3))
IF(@TOTAL<9)
PRINT '此学生学分不足！'
ELSE
PRINT '此学生学分已足！'
GO
如何执行?
EXEC PSTU2 95001
总结
存储过程是一组预编译的SQL语句，它可以包含数据操纵语句、变量、逻辑控制语句等
定义存储过程的语法:
CREATE PROCEDURE 存储过程名
@参数1 数据类型,
…… ,
@参数n 数据类型
AS
SQL语句
GO
EXECUTE（执行,调用)存储过程的语法:
EXEC 过程名 [参数]
存储过程的优点
经编译和优化后存储在数据库服务器中，运行效率高
客户端应用程序可以调用
降低客户机和服务器之间的通信量
有利于集中控制，方便维护
三 SQL-SERVER 游标
什么是游标？
先看一个例子：
设计一个存储过程，参数是课号，显示某门课的优、良、中、及格、差的人数。
输出某门课优良差人数 实现方法1
CREATE PROCEDURE P1
@CNO INT --参数为课号
AS
DECLARE @优 int,@良 int,@差 int
SET @优=( SELECT COUNT(*) FROM SC WHERE CNO=@CNO AND GRADE>=90 )
SET @良=( SELECT COUNT(*) FROM SC WHERE CNO=@CNO AND GRADE>=80 AND GRADE<90 )
SET @差=( SELECT COUNT(*) FROM SC WHERE CNO=@CNO AND GRADE<80 )
PRINT '优人数'+CAST(@优 AS CHAR(2))+' 良人数'+CAST(@良 AS CHAR(2)) +' 差人数'+CAST(@差 AS CHAR(2))
GO
多遍扫描SC， 效率低
游标（cursor）
SQL查询结果是记录的集合
可将SQL语句的查询结果定义为游标，游标代表一个查询结果集合
可以逐一从游标中获取记录，进行处理。
游标操作分为四步：
1、声明游标：
DECLARE 游标名 CURSOR FOR
SELECT查询
2、打开游标
3、循环从游标中提取数据
4、关闭游标
输出某门课优良差人数 实现方法2，使用游标：
将选某门课的成绩（这是一个查询）定义为一个游标，对游标中的成绩循环判断，统计
DROP PROCEDURE P1
GO CREATE PROCEDURE P1
@CNO INT
AS
DECLARE @成绩 int,@优 int,@良 int,@差 int
DECLARE C1 CURSOR FOR /*定义游标*/
SELECT GRADE FROM SC WHERE CNO=@CNO
SELECT @优=0,@良=0,@差=0 /*计数变量清零（原初值为null) */
OPEN C1 /*打开游标—执行游标中的SELECT*/
WHILE 1=1 --建立循环
BEGIN --循环体开始
FETCH NEXT FROM C1 INTO @成绩 /*读游标*/ IF @@fetch_status<>0 /*判断提取结束*/
BREAK
IF @成绩 IS NOT NULL
IF @成绩 >=90
SET @优=@优+1
ELSE IF @成绩 >=80
SET @良=@良+1
ELSE
SET @差=@差+1
END --循环体结束
CLOSE C1 /*关闭游标*/
DEALLOCATE C1 /*释放游标*/
PRINT '优人数'+CAST(@优 AS CHAR(2))+' 良人数'+CAST(@良 AS CHAR(2)) +' 差人数'+CAST(@差 AS CHAR(2))
GO
游标语法框架：
/*定义有关变量*/
DECLARE 游标名 CURSOR FOR /*（1）定义游标*/
SELECT子查询
OPEN 游标名 /*（2）打开游标—执行游标中的SELECT*/
WHILE 1=1 --建立循环
BEGIN --循环体开始
/*（3）读游标的一行到变量， 变量的个数类型要与游标中子查询一致 */
FETCH NEXT FROM 游标名 INTO @变量1， @变量2
IF @@fetch_status<>0 /*判断提取结束*/
BREAK
处理
END --循环体结束
CLOSE 游标名 /*(4)关闭游标*/
DEALLOCATE 游标名 /*释放游标*/
练习：
使用游标, 统计输出男生人数，女生人数，年龄>20的人数，年龄<=20的人数。
CREATE PROCEDURE P2
AS
--定义变量存放男生人数，女生人数，年龄>20的人数，年龄<=20的人数
--定义变量存放性别，年龄
DECLARE C1 CURSOR FOR /*定义游标*/
SELECT SSEX,SAGE FROM STUDENT
......
DROP PROCEDURE P2
GO CREATE PROCEDURE P2
AS
DECLARE @MC INT,@WC INT,@UP INT,@DOWN INT
DECLARE @SSEX CHAR(2),@AGE INT
SELECT @MC=0,@WC=0,@UP=0,@DOWN=0
DECLARE C1 CURSOR FOR
SELECT SSEX,SAGE FROM STUDENT
OPEN C1
WHILE 1=1
BEGIN
FETCH NEXT FROM C1 INTO @SSEX,@AGE IF @@fetch_status<>0
BREAK
IF @SSEX IS NOT NULL
IF @SSEX ='男'
SET @MC=@MC+1
ELSE IF @SSEX='女'
SET @WC=@WC+1
IF @AGE IS NOT NULL
IF @AGE >20
SET @UP=@UP+1
ELSE IF @AGE<=20
SET @DOWN=@DOWN+1
END
CLOSE C1 /*关闭游标*/
DEALLOCATE C1 /*释放游标*/
PRINT '男生人数'+CAST(@MC AS CHAR(2))+'女生人数'+CAST(@WC AS CHAR(2))
+ '年龄>20的人数'+ CAST(@UP AS CHAR(2))+ '年龄<=20的人数'+CAST(@DOWN AS CHAR(2))
GO
思考题：
使用游标, 统计每门课的课名，成绩前三名的学生名。
定义几个游标？什么游标？
定义什么变量？
如何处理？
DROP PROCEDURE P3
GO CREATE PROCEDURE P3
@CNO INT
AS
DECLARE @SNO INT,@SNAME CHAR(20),@SDEPT CHAR(5),@GRADE INT
DECLARE @COUNT INT
DECLARE C1 CURSOR FOR
SELECT STUDENT.SNO,SNAME,SDEPT,GRADE
FROM STUDENT,SC
WHERE STUDENT.SNO=SC.SNO AND @CNO=SC.CNO
ORDER BY GRADE DESC
SET @COUNT=0
OPEN C1
WHILE 1=1
BEGIN
FETCH NEXT FROM C1 INTO @SNO,@SNAME,@SDEPT,@GRADE
IF @@fetch_status<>0
BREAK
IF @COUNT=3
BREAK
SET @COUNT=@COUNT+1
PRINT '学号'+CAST(@SNO AS CHAR(10))+'姓名'+@SNAME+ '系名'
+ @SDEPT+ '成绩'+CAST(@GRADE AS CHAR(5))
END
CLOSE C1
DEALLOCATE C1
GO
四 SQL-SERVER 触发器
WHAT IS TRIGGER?
触发器是一种特殊的存储过程，它不能被显式地调用，
而是在往表中插入记录、更改记录或者删除记录时，才被自动地调用。
SQL-SERVER触发器的类型
对每个表, 可以建立
DELETE 触发器
INSERT 触发器
UPDATE 触发器
创建触发器的语法：
CREATE TRIGGER 触发器名
ON 表名
FOR [DELETE, INSERT, UPDATE]
AS
T-SQL语句
GO
DELETE, INSERT, UPDATE指定触发器的类型
两个临时表： INSERTED 和DELETED
触发器触发时，系统自动在内存中创建两个临时表： INSERTED和DELETED
INSERTED 表临时保存了新记录行（插入或更新后的记录行）
DELETED 表临时保存了旧记录行（删除或更新前的记录行）
结构与触发器对应表相同
触发器执行完成后，自动删除这两个表
只读，用于检查操作是否满足业务需求
查看DELETED, INSERTED内容
编写触发器， 当选课表修改数据时, 显示修改前后的数据
编写触发器：
哪个表？
哪种触发语句？
修改前的行，修改后的行存放在哪里？
CREATE TRIGGER trig_SC
ON SC
FOR UPDATE AS --SC表的UPDATE触发器
SELECT * FROM deleted --deleted存旧数据
SELECT * FROM inserted -- inserted存新数据
GO
UPDATE触发器
编写触发器， 使学生的年龄修改只能增1
编写触发器：
哪个表？
哪种触发语句？
修改前的行，修改后的行存放在哪里？
修改前的年龄？ 修改后的年龄？
-----使学生的年龄修改只能增1----
DROP TRIGGER trig_update_sage --为防止重建时报错, 先删一下
GO
CREATE TRIGGER trig_update_sage
ON STUDENT
FOR UPDATE AS --STUDENT表的UPDATE触发器
DECLARE @beforeSage int,@afterSage int --定义变量
SET @beforeSage=(SELECT TOP 1 SAGE FROM deleted) --旧数据
SET @afterSage=(SELECT TOP 1 SAGE FROM inserted) -- 新数据
IF @afterSage-@beforeSage<>1 AND @afterSage-@beforeSage<>0
BEGIN
PRINT ‘年龄修改只能增1’ --报错
ROLLBACK TRANSACTION --回滚,撤销引起触发器的UPDATE语句
END
GO
DELETE触发器
编写触发器， 在deletedsc中记录SC表删除的行 (deletedsc表已建立, 结构同SC)
编写触发器：
哪个表？
哪种触发语句？
删除的行存放在哪里？
-------关键代码------
CREATE TRIGGER trig_delete_SC
ON SC
FOR DELETE
AS
INSERT INTO DELETEDSC SELECT * FROM deleted GO
INSERT触发器---实时统计
实时统计：在学生表中增加选课门数列CCOUNT和总成绩列GTOTAL
CREATE TABLE STUDENT
(SNO NUMERIC(5) CONSTRAINT P_STUDENT PRIMARY KEY,
。。。。。。
CCOUNT NUMERIC(3), --选课门数
GTOTAL NUMERIC(5) --总成绩
);
编写SC的插入、删除和修改触发器, 自动更新学生表中选课门数和总成绩.
-------关键代码------
--当插入SC数据时,自动更新学生表中选课门数列和总成绩列.
CREATE TRIGGER trig_insert_sc
ON SC
FOR insert AS
DECLARE @GRADE INT, @SNO INT
/* 从INSERTED中取插入的学号和成绩 */
SELECT @SNO=SNO,@GRADE=GRADE FROM INSERTED
IF @GRADE IS NOT NULL
UPDATE STUDENT
SET CCOUNT=CCOUNT+1, GTOTAL=GTOTAL+@GRADE
WHERE SNO=@SNO
GO
DELETE触发器 ---实时统计
DELETE可能删除多行
要对删除的多行逐行进行处理
如何进行？
CREATE TRIGGER trig_delete_sc ON SC
FOR DELETE AS --SC表DELETE触发器
DECLARE @sno int ,@grade int --定义变量
DECLARE C1 CURSOR FOR --定义游标，存放被删的数据
SELECT SNO,GRADE FROM DELETED
OPEN C1 --打开游标
WHILE 1=1 --循环
BEGIN
FETCH NEXT FROM C1 INTO @SNO, @GRADE --读游标
IF @@fetch_status<>0 --如果读完了
BREAK
IF @GRADE IS NOT NULL
UPDATE STUDENT
SET CCOUNT=CCOUNT-1,GTOTAL=GTOTAL-@GRADE
WHERE SNO=@sno
END
CLOSE C1
DEALLOCATE C1
GO
当对SC表UPDATE时，如何自动更新学生表中选课门数列和总成绩列?
对SC表建立UPDATE触发器
UPDATE可能修改多行
修改前的数据，修改后的数据？
要对修改的多行进行处理，如何进行？
/*建立两个游标C1，C2，分别对应 deleted inserted*/
/*循环处理游标，发现修改，同步更新STUDENT*/
DROP TRIGGER trig_update_sc
GO
CREATE TRIGGER trig_update_sc ON SC
FOR UPDATE AS
DECLARE @sno1 int,@grade1 int
DECLARE @sno2 int,@grade2 int
DECLARE C1 CURSOR FOR
SELECT SNO,GRADE FROM deleted
DECLARE C2 CURSOR FOR
SELECT SNO,GRADE FROM inserted
OPEN C1
OPEN C2
WHILE 1=1
BEGIN
FETCH NEXT FROM C1 INTO @SNO1,@GRADE1
FETCH NEXT FROM C2 INTO @SNO2,@GRADE2
IF @@fetch_status<>0
BREAK IF @SNO1=@SNO2/*学号未变*/
BEGIN
IF @GRADE1 IS NOT NULL AND @GRADE2 IS NOT NULL AND @GRADE1<>@GRADE2/*更新前后均不为空*/
UPDATE STUDENT
SET GTOTAL=GTOTAL-@GRADE1+@GRADE2
WHERE SNO=@sno1
ELSE IF @GRADE1 IS NULL AND @GRADE2 IS NOT NULL/*更新前为空，更新后不为空*/
UPDATE STUDENT
SET CCOUNT=CCOUNT+1,
GTOTAL=GTOTAL+@GRADE2
WHERE SNO=@sno1
ELSE IF @GRADE1 IS NOT NULL AND @GRADE2 IS NULL/*更新前不为空，更新后为空*/
UPDATE STUDENT
SET CCOUNT=CCOUNT-1,
GTOTAL=GTOTAL-@GRADE1
WHERE SNO=@sno1
END ELSE/*学号变了*/
BEGIN
IF @GRADE1 IS NOT NULL/*对旧学号相当于删选课*/
UPDATE STUDENT
SET CCOUNT=CCOUNT-1,GTOTAL=GTOTAL-@GRADE1
WHERE SNO=@sno1
IF @GRADE2 IS NOT NULL/*对新学号相当于插入选课*/
UPDATE STUDENT
SET CCOUNT=CCOUNT+1,GTOTAL=GTOTAL+@GRADE2
WHERE SNO=@sno2
END
END/*where*/
CLOSE C1
CLOSE C2
DEALLOCATE C1
DEALLOCATE C2
GO
查看触发器信息
触发器作为一种特殊的存储过程，在用户创建以后，其名字存放在系统表sysobjects中，其创建源代码存放在syscomments系统表中。
查看指定表中所定义的触发器及它们的类型:
EXEC sp_helptrigger 表名
查看指定触发器的定义文本:
EXEC sp_helptext 触发器名
删除触发器:
DROP TRIGGER 触发器名
总结
触发器是在对表进行插入、更新或删除操作时自动执行的存储过程
触发器作用： 增强数据完整性和安全性，实时统计
触发器一般都需要使用临时表：deleted表和inserted表，它们存放了被删除或插入的记录行副本
创建触发器的语法：
CREATE TRIGGER 触发器名
ON 表名
FOR [DELETE, INSERT, UPDATE]
AS
T-SQL语句
GO