第6章 Transact-SQL简介、存储过程和触发器

例6、将例1定义的存储过程修改，使之能查询任 何指定系的学生的修课情况。
例4、带有多个输入参数并均指定默认值的存储过程： 查询指定系，指定性别的学生中年龄大于等于指 定年龄的学生的情况。系的默认值为‘计算机’， 默认的性别为‘男’，默认的年龄为20。
CREATE PROC S_GRADE3 @dept char(20)= ‘计算机’ ， @sex char(2)= ‘男’，@age int=20 AS SELECT * FROM Student WHERE Sdept = @dept and Ssex = @sex and Sage>=@age
例5、带有输出参数的存储过程：计算两个数得到积， 将结果作为输出参数返回给调用者。 CREATE PROCEDURE PROC1 @var1 int, @var2 int, @var3 int output AS SET @var3 = @var1 * @var2 执行此存储过程的示例： DECLARE @res int EXEC PROC1 5,7, @res output PRINT @res 结果为：35
1 常量
Transact-SQL的常量主要有以下几种。

字符串常量 数值常量 日期常量
2 全局变量 全局变量是SQL Server系统提供并赋值 的变量。用户不能定义全局变量，也不 能用SET语句来修改全局变量。通常是将 全局变量的值赋给局部变量，以便保存 和处理。事实上，在SQL Server中，全局 变量是一组特定的函数，它们的名称是 以@@开头，而且不需要任何参数，在 调用时无需在函数名后面加上一对圆括 号，这些函数也称为无参数函数。
4、保证系统的安全性。
6.2.1存储过程的创建
1．使用T-SQL语句创建存储过程
创建存储过程使用CREATE PROC [EDURE]语 句。 语法格式如下：
CREATE PROCEDURE 存储过程名 [{@参数名 数据类型}[=default][OUTPUT] ][,…n] AS SQL语句
– –注释文本
2)块注释：使用‚/* */”作为注释符 块注释的语法格式为：
/*注释文本*/
或：
/* 注释文本 */
6.1.2常量和变量

常量和变量是程序设计中不可缺少的元 素。变量又分为局部变量和全局变量，局 部变量是一个能够保存特定数据类型实例 的对象，是程序中各种类型数据的临时存 储单元，用在批处理内SQL语句之间传递数 据。全局变量是系统给定的特殊变量。

1 批处理 建立批处理如同编写SQL语句，区别在于 它是多条语句同时执行的，用GO语句作为 一个批处理的结束。 2 脚本 脚本是批处理的存在方式，将一个或多 个批处理组织到一起就是一个脚本 。 脚本可以在查询分析器中执行，查询分 析器是编辑、调试和使用脚本的最好环境。
3注释 1)单行注释： 使用两个连在一起的减号‚– –”作为注释符 语法格式为：
源自文库
@Sname char(20)，@Cname char(20)= ‘数据库’ AS SELECT Sname,Cname,Grade FROM Student ,Scourse,Course WHERE Sname= @Sname and Cname= @Cname and Student.Sno=Scourse.Sno And Course.Cno=Scourse.Cno
声明变量的语句格式： DECLARE @局部变量名 数据类型 注：不能把局部变量指定为text或image类型， 使用DECLARE声明一个局部变量后，这个 变量的值将被初始化为null。 变量的赋值语句格式为： SET @局部变量名=值 | 表达式 注：表达式可以是任意的SQL SERVER表达式。
第六章 Transact-SQL简介、 存储过程和触发器
6.1Transact-SQL简介
6.2存储过程
6.3触发器
6.1 Transact-SQL简介
6.1.1 批处理、脚本和注释 6.1.2 常 量 和 变 量 6.1.3 流 程 控 制 语 句

6.1.1 批处理、脚本和注释

批处理就是一个或多个Transact-SQL语句的 集合，用户或应用程序一次将它发送给SQL Server，由SQL Server编译成一个执行单元， 此单元称为执行计划，执行计划中的语句每次 执行一条。 批处理的结束标记是：GO。
Default：表示参数的默认值。 Output：表明参数是输出参数。 执行存储过程的SQL语句是EXECUTE，其语 法格式为： [EXECUTE]存储过程名 [实参[，OUTPUT][，…n]] 存储过程可以嵌套，SQL Server 2000 最多 可以允许嵌套32层存储过程。

例1：带有复杂查询的存储过程：查询计算机系学生 的考试情况，列出学生的姓名、课程名和考试成绩。 CREATE PROCEDURE S_grade1 AS SELECT sname,cname,grade FROM Student s JOIN sc ON s.sno=sc.sno JOIN course c ON c.cno=sc.cno WHERE Sdept=‘计算机系’ 执行此存储过程：EXEC S_grade1
6.2存储过程
存储过程（stored procedure）是 一组事先编译好的Transact-SQL代码。 存储过程作为一个独立的数据库对象， 可以作为一个单元被用户的应用程序 调用。由于存储过程是已经编译好的 代码，所以执行的时候不必再次进行 编译，从而提高了程序的运行效率。

使用存储过程的好处： 1、执行速度快。 2、模块化的程序设计。 3、减少网络通信量。
句的语法元素，在批处理、存储过程、脚
本和特定的检索中使用。它们包括条件控
制语句、无条件转移语句和循环语句等。
主要的流程控制语句： BEGIN…END：定义语句块 IF…ELSE：若指定条件为真，执行一个分支， 否则执行另一个分支 WHILE：当指定条件为真时重复一些语句 CASE：允许表达式按照条件返回不同的值 BREAK：退出最内层的WHILE循环 RETURN：重新开始WHILE循环 WAITFOR：为语句的执行设置延迟
例：若希望条件为假时，在ELSE语句中执行其后续 的两条语句，则必须使用BEGIN…END将这两条 语句包括起来，使其成为一个语句块。 DECLARE @x int,@y int,@z int SET @x=40 SET @y=30 IF(@x > @y) SET @z= @x - @y ELSE BEGIN SET @z= @y - @x SET @x=0 END
执行带多个参数的存储过程时，参数的传递方 式有两种： 1、按参数位置传递：执行存储过程的EXEC语 句中的实参的排列顺序必须与定义存储过程时 定义的参数的顺序一致 EXEC S_GRADE2 ’张三’， ’VB’ 2、按参数名传递：执行存储过程的EXEC语句 中要指明定义存储过程时定义的参数的名字以 及此参数的值，而不关心参数的定义顺序 EXEC S_GRADE2 @Sname =’张三’， @Cname =’VB’
1 BEGIN…END语句块 BEGIN和END用来定义语句块，必须成 对出现。它将多个SQL语句括起来，相 当于一个单一语句，其语法格式如下。
BEGIN 语句1或语句块1 语句2或语句块2 … END
2 IF...ELSE语句 IF…ELSE语句用来实现选择结构，其 语法格式如下。 IF 布尔表达式 {语句1或语句块1 } [ELSE {语句2或语句块2} ]



注：当存储过程有输入参数并且没有为输 入参数指定默认值时，在调用此存储过程 时，必须要为此输入参数指定一个常量值。 执行例2的存储过程，查询信息系学生的 修课情况： EXEC student_grade2 ‘信息系’
例3、带有多个输入参数并有默认值的存储过程：查询某个 学生某门课程的考试成绩，若没有指定课程，则默认为 ‘数据库’。 CREATE PROC S_GRADE2
6.2.3修改存储过程
SQL Server提供了在不改变存储过程使用许可和 名字的情况下，对存储过程进行修改的语句。 语法格式为：
ALTER PROC[EDURE] 存储过程名 [{@参数名 数据类型}[=default][OUTPUT] ][,…n] AS SQL语句
注：也可以使用企业管理器进行系统存储过程的修改。
6.2.2查看存储过程信息

1．使用T-SQL语句查看存储过程 （1）可以使用sp_helpText 命令查看创 建存储过程的文本信息。例如： use master go sp_helptext sp_who go
系统返回信息是：
（2）可以使用sp_help查看存储过程的一 般信息。例如：
use master go sp_help proc1 go






例2：带有输入参数的存储过程：查询某个指定系学 生的考试情况，列出学生的姓名、所在系、课程名和 考试成绩。 CREATE PROC student_grade2 @dept char(20) AS SELECT Sname,sdept,cname,grade FROM student s JOIN sc ON s.sno=sc.sno JOIN course c ON c.cno=sc.cno WHERE sdept=@dept
PRINT @x PRINT @y PRINT @z
执行结果为 40 30 10
例：计算1+2+3+…+100的和
DECLARE @i int，@sum int SET @i=1 SET @sum=0 WHILE @i<=100 BEGIN SET @sum= @sum+ @i SET @i= @i +1 END PRINT @sum
3 局部变量 局部变量是用户在程序中定义的变量， 一次只能保存一个值，它仅在定义的批 处理范围内有效。局部变量可以临时存 储数值。局部变量名总是以@符号开始， 最长为128个字符。 使用DECLARE语句声明局部变量，定 义局部变量的名字、数据类型，有些还 需要确定变量的长度。
4 变量的声明与赋值
例：计算两个变量的值的和，然后输出 其结果。 DECLARE @x int DECLARE @y int DECLARE @z int SET @x =10 SET @y =10 SET @z = @x + @y Print @z
6.1.3 流程控制语句

流程控制语句是组织较复杂Transact-SQL语
3 WHILE语句 WHILE语句用来实现循环结构，其语法 格式如下：
WHILE 逻辑表达式 语句块
当逻辑表达式为真时，执行循环体，直 到逻辑表达式为假。 BREAK语句退出WHILE循环， CONTINUE语句跳过语句块中的所有其 他语句，开始下一次循环。
例：若IF条件为真或为假时要执行的语句只有一条 （默认时，一条语句就是一个语句块），则可以 不使用BEGIN…END。 DECLARE @x int,@y int,@z int SET @x=40 SET @y=30 IF(@x > @y) SET @z= @x - @y ELSE 执行结果为 SET @z= @y - @x 0 SET @x=0 30 PRINT @x PRINT @y 10 PRINT @z



例6、创建一个存储过程，向学生表中插入数据。 CREATE PROC insert_student @sno char (10),@sname char(10),@ssex char (4),@sage int,@sdept char(20) as insert into student ,@sage,@sdept) 执行此存储过程 exec insert_student ‘1001’,’李明’,’男’,’20’,’计算机 系’