数据库原理及应用·存储过程和触发器

12.1 T-SQL中的变量

12.1.1 T-SQL概述

SQL（Structured Query Language）结构化查询语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。

Transact-SQL即事务SQL，也简称为T-SQL，它是微软公司对SQL语言的扩充，是SQL语言的超集，是应用程序与SQL-Server数据库引擎沟通的主要语言。T-SQL提供标准SQL的DDL和DML功能，加上延伸的函数、系统预存程序以及程序设计结构(例如 IF和WHILE)让程序设计更有弹性。

12.1.2 T-SQL中的变量

1.局部变量

局部变量是由用户自定义的变量，这些变量可以用来存储数值型、字符串型等数据，它的作用范围仅限制在程序内部。声明变量的语法代码为：

Declare {@local_variable [AS] datatype}[,...n]

Declare @name varchar(10) --声明局部变量
set @name='张三' --为变量赋值
print @name  --输出变量的值
注意：局部变量的作用范围是从声明该局部变量的地方开始到局部变量所在的批处理或者存储过程结束为止。在局部变量的作用范围以外引用该变量将引起语法错误。
Declare @name varchar(10) 
set @name='张三' 
go  --批处理结束
print @name  --@name变量的生命周期已结束

2.全局变量

全局变量是由系统提供的，用于存储一些系统信息，用户是不能定义全局变量的。全局变量的作用范围并不局限于某一程序，而是任何程序均可随时调用。

在使用全局变量时应注意以下几点：
①全局变量不能由用户定义，它们是在服务器中定义的。
②用户只能使用预先定义的全局变量。
③引用全局变量时，必须以标记符“@@”开始。
④局部变量不能与全局变量同名，否则会在程序中出现不可预测的结果。

update Course
	set Ccredit =3 where Cno='B002'
go
if @@ERROR>0      
	PRINT '发生错误！'

如果执行上述语句发生错误，则打印信息“发生错误！” 

12.2 T-SQL中的运算符

12.2.1 运算符种类

算术运算符  +、-、*、/、%
赋值运算符  =
比较运算符  =、>、<、>=、<=、<>、!=、!<、!>
逻辑运算符  AND、OR、NOT
字符串连接符  +

12.2.1 运算符的优先级

12.3 流程控制语句

12.3.1 选择结构

IF…ELSE语句

IF 条件表达式 
	SQL语句1
 [ ELSE
	SQL语句2
]

IF 条件表达式 
  BEGIN
     语句块1
  END
[ ELSE
    BEGIN
       语句块2
    END
]

张长弓是计算机学院的学生吗？

DECLARE @院系 CHAR (20)
SELECT @院系=Sdept FROM student WHERE sname=‘张长弓'

IF @院系=‘计算机学院’
   PRINT ‘张长弓是计算机学院的学生’
ELSE
   PRINT ‘张长弓不是计算机学院的学生’

CASE语句 

CASE 条件表达式
   WHEN 值1 THEN 语句1|语句块1
   [WHEN 值2 THEN 语句2|语句块2]
   […n]
   [ELSE 语句块n]
END

不同院系位于不同教学楼，查询每个学生上课所在教学楼

SELECT Sname,location=
  CASE Sdept
      WHEN ‘计算机学院’  THEN ‘明理楼’
      WHEN ‘石工院’  THEN ‘明辩楼’
      WHEN ‘机电院’  THEN ‘明志楼’
      WHEN ‘化工院’  THEN ‘明德楼’
      ELSE ‘思学楼’
  END
FROM student

12.3.2 循环结构

WHILE语句

WHILE 条件表达式
   BEGIN
      SQL语句|语句块1
      [BREAK]
      SQL语句|语句块2
      [CONTINUE]
      SQL语句|语句块3
   END

WHILE (select AVG(Ccredit) from course)<5
  BEGIN  --循环开始
    UPDATE Course set Ccredit=Ccredit+1
    IF (select MAX(Ccredit) from Course )>10
      BREAK
    ELSE
      CONTINUE
  END

12.4 存储过程

12.4.1 存储过程概述

存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数） 来执行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。

12.4.2 创建存储过程

使用CREATE PROCEDURE语句创建存储过程

语法：

CREATE PROCEDURE proc_name
AS
BEGIN
  sql_statement1
  [sql_statement2]
END

使用EXECUTE PROCEDURE语句创建存储过程

语法：

EXECUTE proc_name

12.4.3 修改/删除存储过程

修改存储过程

ALTER PROCEDURE proc_name
AS
  BEGIN
    sql_statement1
    [sql_statement2]
  END

删除存储过程

DROP PROCEDURE proc_name

12.5 带参数存储过程

被用于在运行时传递值到存储过程 这些值通过标准变量被传递。

传递值的参数被定义为输入参数。

CREATE PROCEDURE proc_name @参数 类型

 输出参数

CREATE PROCEDURE proc_name @参数 类型 OUTPUT

在新一轮的培养方案调整中，要求所有课程的平均学分不能低于5分，且单门课程的学分不能超过10分，应该如何调整每门课程的学分？

Create Procedure prcAVG @课程名 char(30), @平均成绩 FLOAT OUTPUT
AS
BEGIN
   Select @平均成绩 =AVG(Grade) From SC where Tno= (Select Cno From Course where Cname = @课程名)
END

DECLARE @avgGrade float
EXECUTE prcAVG ‘数据库’, @avgGrade OUTPUT 

12.6  触发器

触发器就是一种特殊的存储过程；

触发器可以自动执行，不依赖EXECUTE语句。

触发器与表的关系密切，用于保护表中的数据。

12.6.1 魔表

12.6.2 创建触发器

使用CREATE TRIGGER语句来创建触发器

语法：

CREATE TRIGGER trigger_name 
		ON { OBJECT NAME } 
		{ FOR | AFTER | INSTEAD OF } { event_type [
		,...n ] |      
	 DDL_DATABASE_LEVEL_EVENTS }   
   { AS 
   { sql_statement [ ...n ] }
   }

修改Course表时，自动将ModifiedDate设为当前时间

create trigger trgTime on Course
after update as
begin
  update Course
  set ModifiedDate=getdate()  from inserted
where Course.cno=inserted.cno
end