sql server 练习3（Transcat-SQL程序设计,触发器和存储过程）
sql server 的Transcat-SQL程序设计,触发器和存储过程
这篇博客主要简单讲一下sql server数据库的一些高级操作，那就是：Transcat-SQL语句,触发器和存储过程，由于边幅问题，这里这是简单地说一下概念，注意事项以及一些相关练习。
Transcat-SQL程序设计
我们平常在大多数数据库中用到的SQL语句，都是标准的SQL语句，是不支持流程控制。
Transcat-SQL就是在标准SQL的基础上进行扩充而推出的SQL server专用的结构化SQL，引入了程序设计的思想、增强了程序的流程控制语句等。
Transcat-SQL主要包含变量、运算符、批处理、流程控制语句、常用命令这五部分。这里说一下批处理和流程控制语句，因为我觉得这部分是之前我没有理解到的。
批处理
批处理是包含一个或多个的T-SQL语句的组，批处理的所有语句被整合成一个执行计划。一个批处理内的所有语句要么被放在一起通过解析，要么没有一句能够执行。
批处理是使用GO语句将多条SQL语句进行分割，其中每两个GO之间的SQL语句就是一个批处理处理单元。
如以下是一个批处理，会都执行，或都不执行：
use teach
go
select * from SC
select count(*) from s
go流程控制语句
T-SQL的流程控制语句主要有以下几种：
1.begin end
begin
<命令行或程序块>
end2.if…else
if <条件表达式>
<命令行或程序块>
[else
<命令行或程序块>]3.if[not] exists
if [not] exists (select 子查询)
<命令行或程序块>
[else
<命令行或程序块>]4.case语句
（格式一）如果比较两个表达式的值为相等，返回then后的，否则，测试下一条
case <表达式>
when <表达式> then <表达式>
。。。
when <表达式> then <表达式>
[else <表达式>]
end（格式二）如果表达式的值为真，返回then后的，否则，测试下一条
case
when <表达式> then <表达式>
。。。
when <表达式> then <表达式>
[else <表达式>]
end注意如果在case语句中没有else子句，则返回null
5.while…continue…break
while <条件表达式>
begin
<命令行或程序块>
[break]
[continue]
[命令行或程序块]
end6.waitfor
waitfor {delay <'时间'> | time <'时间'>
| errorexit | processexit | mirrorexit}7.goto
goto 标志符
8.return
return([整数值])存储过程
存储过程（stored procedure）是存储在SQL server数据库中的一种编译对象。它是一组为了完成特定功能的SQL语句集，这些SQL语句集经编译后存储在数据库中，可以被客户机管理工具、应用程序和其他存储过程代用，同时可以传递参数。简单来说，就是预编译好的SQL语句，能提高执行效率。
创建存储过程
create procedure procedure_name [ ; number ]
[{ @parameter data_type }
[ varying ] [ = default ] [ output ]
] [ ,...n ]
[with
{ recompile | encryption | recompile , encryption } ]
[for replication]
as sql_statement [ ,...n]好长，举个例子应该更能清楚理解
定义能够返回值的存储过程。在Teach数据库中，创建一个名称为QueryTeach的存储过程。该存储过程的功能是从数据库S中根据学号查询某一同学的姓名和系别，查询的结果由参数@sn和@dept返回。
use teach
go
create procedure QueryTeach
( @sno varchar(6),
@sn nvarchar(10) output,
@dept nvarchar(20) output
)
as
select @sn=SN,@dept=Dept from where sno[email protected]sno查看存储过程
-- 查看数据库Teach中存储过程MyProc的源代码
use teach
go
exec sp_heptext MyProc删除存储过程
use teach
go
if exists(select 1 from sysobjects where id=object_id('存储过程名') and xtype='P')
drop procedure MyNewProc执行存储过程
-- 执行数据库Teach中的带输出参数的存储过程QueryTeach，存储过程QueryTeach执行完毕后，所需结果保存在输出参数@sn和@dept中。
use teach
go
declare @sn nvarchar(10)
declare @dept varchar(20)
-- 这里注意输入的参数，不能@参数名=参数值，来输入，只能直接输入参数值
exec QueryTeach 'S10',@sn output,@dept output
select '姓名'=@sn, '系别'=@dept触发器
触发器是一种特殊的存储过程，其中包含一系列的T-SQL语句，但它的执行不是用execute命令显式调用，而是在满足一定条件下自动**而执行，如向表中插入记录，更新记录，或者删除记录时被系统自动地**并执行。
sql server2012提供了三种类型的触发器：DML触发器，DDL触发器和登录触发器。
创建触发器
这里主要说的DML触发器，因为这类触发器涉及到inserted和deleted表，比较复杂。
AFTER 触发器语法：
CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name
ON { table }
[ WITH <dml_trigger_option> [ ,...n ] ]
{ FOR | AFTER }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
AS { sql_statement [ ; ] [ ,...n ] }
<dml_trigger_option> ::=
[ NATIVE_COMPILATION ]
[ SCHEMABINDING ]
[ EXECUTE AS Clause ]INSTEAD OF 触发器语法：
CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name
ON { table | view }
[ WITH <dml_trigger_option> [ ,...n ] ]
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > }
<dml_trigger_option> ::=
[ ENCRYPTION ]
[ EXECUTE AS Clause ]
<method_specifier> ::=
assembly_name.class_name.method_name触发器的例子:
--Instead Of触发器：删除课程时,同时删除该课程的选课信息
IF OBJECT_ID (N'TRIGER_Courses_Instead_Delete', N'tr') IS NOT NULL
DROP TRIGGER TRIGER_Courses_Instead_Delete;
GO
CREATE TRIGGER TRIGER_Courses_Instead_Delete
ON Courses
Instead Of DELETE
AS
declare @courseId int
--获取要删除的课程ID
SELECT @courseId=ID FROM deleted
--删除选课信息
DELETE FROM SC WHERE CourseID = @courseId
--删除课程信息
DELETE FROM Courses WHERE [email protected] 练习
1、说明下面代码段的功能
SELECT tname as ‘姓名’,tSex as ‘性别’,
case trank
when ‘教授’ then ‘高级职称’
when ‘讲师’ then ‘中级职称’
else
‘初级职称’
end
from teacher2、完成下面代码的书写
计算student_course表的平时分数列的平均值。如果小于80，则分数增加其值的5%；如果分数的最高值超过95，则终止该操作。在以下代码划线处填入适当的内容以完成上述功能。
Update student_course set normalmark=60
go
WHILE (SELECT ________(normalmark) FROM student_course)<80
BEGIN
UPDATE student_course
SET normalmark = normalmark *1.05
if (SELECT MAX(normalmark) FROM student_course)>________
________
END3、根据要求完成下列题目
（1）编写一个触发器：在添加期末成绩信息时，利用平时成绩、其中成绩和期末成绩来计算成绩总评。如果没有期中成绩，则平时和期末成绩比重为2：8，如果有期中成绩，则平时、期中、期末三者比重为2：2：6。（假设表中有期中成绩列，且该列默认值为-1。）
（2）创建一个带输入参数和输出参数的存储过程，要求实现如下功能：输入学生学号，然后输出学生的选课门数、平均分以及所选学分。
（3）调用2小题中的存储过程，并分别获得输出相关信息。
4、思考题
（1）思考基于UPDATE操作的触发器的操作流程？
练习答案
use stuManage
go
-- 1.功能，从教师表查询出所有教师第姓名，性别和职称，并将名为教授的职称显示为“高级职称”，名为”讲师的职称显示为“中级职称，其他显示为初级职称。”
SELECT tname as '姓名',tSex as '性别',
case trank
when '教授' then '高级职称'
when '讲师' then '中级职称'
else
'初级职称'
end
from teacher;
select * from teacher;
select * from student_course;
-- 2、完整代码如下
Update student_course set normalmark=60
go
WHILE (SELECT avg(normalmark) FROM student_course)<80
BEGIN
UPDATE student_course
SET normalmark = normalmark *1.05
if (SELECT MAX(normalmark) FROM student_course)>95
break
END
/*
3、根据要求完成下列题目
（1）编写一个触发器：在添加期末成绩信息时，利用平时成绩、其中成绩和期末成绩来计算成绩总评。如果没有期中成绩，则平时和期末成绩比重为2：8，如果有期中成绩，则平时、期中、期末三者比重为2：2：6。（假设表中有期中成绩列，且该列默认值为-1。）
（2）创建一个带输入参数和输出参数的存储过程，要求实现如下功能：输入学生学号，然后输出学生的选课门数、平均分以及所选学分。
（3）调用2小题中的存储过程，并分别获得输出相关信息。
（1）编写一个触发器：在添加期末成绩信息时，利用平时成绩、其中成绩和期末成绩来计算成绩总评。
如果没有期中成绩，则平时和期末成绩比重为2：8，如果有期中成绩，则平时、期中、期末三者比重为2：2：6。
（假设表中有期中成绩列(mid_mark)，且该列默认值为-1。）
假设是要更新期末成绩列mark才有此需求
*/
use stuManage;
select * from student_course;
-- 增加期中成绩列
alter table student_course ADD mid_mark int;
-- 设置平时成绩和期末成绩，其中成绩
update student_course set examMark = 70;
update student_course set NormalMark = 80;
update student_course set mid_mark = -1;
-- alter table student_course drop column mid_mark;
select * from student_course;
select * from sys.server_triggers;
select * from sys.triggers;
-- 建立触发器
use stuManage
go
if(OBJECT_ID('trigger_update_student_course_mark') is not null)
drop trigger trigger_update_student_course_mark ;
go
Create trigger trigger_update_student_course_mark
on student_course
After update
As
declare @s_no int,@cc_no int
select @s_no=sNo,@cc_no=ccNo from deleted
print '要更新的学生id和学生课程号分别为：'+str(@s_no)+'，'+str(@cc_no);
begin
-- 只有更新mark（期末成绩列才会执行以下过程，更新其他列不会执行）
if (update(mark))
if(select mid_mark from student_course where [email protected]_no and [email protected]_No)>=0
begin
update student_course set mark=(NormalMark*2+mid_mark*2+examMark*6)/10
where [email protected]_no and [email protected]_no; ;
end
else
update student_course set mark=(NormalMark*2+examMark*8)/10
where [email protected]_no and [email protected]_no;
end
go
select * from student_course;
-- 测试
update student_course set Mark=200 where sNO=081220101 and ccNO=0312091006;
-- （2）创建一个带输入参数和输出参数的存储过程，要求实现如下功能：输入学生学号，然后输出学生的选课门数、平均分以及所选学分。
use stuManage
go
if Exists(select name from sysobjects where NAME = 'get_student_course_avgMark_credit_by_sno' and type='P')
drop procedure get_student_course_avgMark_credit_by_sno
go
create procedure get_student_course_avgMark_credit_by_sno
( @sno int
)
as
select @sno,sumCredit as '所选学分',sumCourse as '课程门数',avgMark as '平均分数' from
(select sNo,sum(Credit) as sumCredit,count(student_course.ccNo) as sumCourse ,avg(mark) as avgMark
from student_course,course_class
where student_course.ccNO=course_class.ccNo group by sNO ) tempCredit
where [email protected];
go
-- （3）调用2小题中的存储过程，并分别获得输出相关信息。
-- 调用方法一
exec get_student_course_avgMark_credit_by_sno @sno=081220102;
-- 调用方法二(不用参数)
exec get_student_course_avgMark_credit_by_sno 081220102;
4、思考题
（1）思考基于UPDATE操作的触发器的操作流程？
答：基于update操作的触发器的操作是sql语句在执行更新操作时，首先将要更新的行删除，临时存放在delete表中，然后再将新的行插入到原来的表中，并且将新的行临时存放在inserted表中。最后，再执行触发器里面的语句。
总结
经过这次练习，自己对于SQL server的触发器和存储过程有了更深一步的认识，知道了存储过程的应用于使用实际应用中的好处，即能模块化的程序设计，高效率的执行，减少网络流量，可以作为安全机制使用。此外，在这次练习中对于Transaction-SQL中流程语句也有了一定的了解，因为之前操作数据库时都没有怎么用过T-sql语句，在这次练习中用到了许多流程语句，对于自己自身的关于T-sql的流程控制语句知识有了更深刻的认识。另外，还有对于sql server 的触发器也加深了理解，还学到了如何只对某列执行更新的时候，才使触发器起作用。