第7章 SQL Server 2000高级应用

7.2.2 创建存储过程
当创建存储过程时， 当创建存储过程时，需要确定存储过程的三个组成 部分： 部分：
（1）所有的输入参数以及传给调用者的输出参数。 ）所有的输入参数以及传给调用者的输出参数。 （2）被执行的针对数据库的操作语句，包括调用其他存储 ）被执行的针对数据库的操作语句， 过程的语句。 过程的语句。 （3）返回给调用者的状态值以指明调用是成功还是失败。 ）返回给调用者的状态值以指明调用是成功还是失败。
/*Transact-SQL程序的实例 程序的实例*/ 程序的实例 USE Teach /*将教学管理数据库 将教学管理数据库Teach置为当前数据库 将教学管理数据库 置为当前数据库 */ GO DECLARE @AvgScore DECIMAL /*定义变量 定义变量AvgScore */ 定义变量 SET @AvgScore=60 /*给变量 给变量AvgScore赋值初值 赋值初值*/ 给变量 赋值初值 IF(SELECT AVG(Score) FROM SC WHERE SNo='S1')>=@AvgScore --输出结果 输出结果 PRINT '课程平均成绩超过 课程平均成绩超过' 课程平均成绩超过 +CONVERT(VARCHAR(10),@AvgScore) ELSE --输出结果 输出结果 PRINT '课程平均成绩不超过 课程平均成绩不超过' 课程平均成绩不超过 +CONVERT(VARCHAR(10),@AvgScore) --执行批处理 执行批处理 GO
Transact-SQL程序的批处理 程序的批处理
程序内两个“ 在Transact-SQL程序内两个“GO”标记符之间的 程序内两个 ” 代码称为一个“ 代码称为一个“批”。 SQL Server对Transact-SQL程序的编译和执行是 对 程序的编译和执行是 按照“ 为单位来进行的，称为批处理 批处理。 按照“批”为单位来进行的，称为批处理。 一个Transact-SQL程序内可以包含多个“批”。 程序内可以包含多个“ 一个 程序内可以包含多个
[例7-5] 从学生表 中，选取 例 从学生表S中 选取SNo，Sex，如果 ， ，如果Sex 则输出“ ” 如果为“ 输出“ ” 为“男”则输出“M”，如果为“女”输出“F”。 SELECT SNo, Sex= CASE Sex WHEN '男' THEN 'M' 男 WHEN '女' THEN 'F' 女 END FROM S
[例7-4] 从S数据表中读取学号为 同学的数据记录，如果 例 数据表中读取学号为S1同学的数据记录 数据表中读取学号为 同学的数据记录， 存在，则输出“存在学号为S1的学生 的学生” 否则输出“ 存在，则输出“存在学号为 的学生”；否则输出“不存 在学号为S1的学生 的学生” 在学号为 的学生”。 USE Teach /*将教学管理数据库 将教学管理数据库Teach置为当前 将教学管理数据库 置为当前 数据库*/ 数据库 GO DECLARE @message VARCHAR(255) /*定义变量 定义变量 message */ IF EXISTS (SELECT * FROM S WHERE SNo='S1') SET @message='存在学号为 的学生 存在学号为S1的学生 存在学号为 的学生' ELSE SET @message='不存在学号为 的学生' 不存在学号为S1的学生 不存在学号为 的学生 PRINT @message GO
[例7-8] 等待 小时 分零 秒后才执行 例 等待1小时 分零3秒后才执行 小时2分零 SELECT语句。 语句。 语句 WAITFOR DELAY '01:02:03' SELECT * FROM S [例7-9] 指定在 指定在11:24:00时间点时开始执行 例 时间点时开始执行 SELECT语句。 语句。 语句 WAITFOR TIME '11:24:00' SELECT * FROM S
7.2 存储过程
7.2.1 存储过程的概念、优点及分类 存储过程的概念、
存储过程是一组为了完成特定功能的SQL语句集 。 语句集 存储过程是一组为了完成特定功能的 存储过程的优点 ：
模块化的程序设计 高效率的执行 减少网络流量 可以作为安全机制使用
存储过程的分类： 存储过程的分类：
系统存储过程 用户自定义存储过程 扩展存储过程 xp_cmdshell 'dir *.exe'
标准的注释符“ ” 用于单行注释； （1）ANSI标准的注释符“- -”：用于单行注释； ） 标准的注释符 语言相同的程序注释符， ……*/” （2）与C语言相同的程序注释符，即“/*…… ”，“/*”用 ） 语言相同的程序注释符 …… ” 于注释文字的开头， 于注释文字的开头，“*/”用于注释文字的结尾，可在程序 ”用于注释文字的结尾， 中标识多行文字为注释。 中标识多行文字为注释。
[例7-7] 以下程序计算 ～100之间所有能被 整除的数的个数及总和。 例 以下程序计算1～ 之间所有能被3整除的数的个数及总和 之间所有能被 整除的数的个数及总和。 DECLARE @s SMALLINT,@i SMALLINT,@nums SMALLINT SET @s=0 SET @i=1 SET @nums=0 WHILE (@i<=100) BEGIN IF (@i%3=0) BEGIN SET @s=@s+@i SET @nums=@nums+1 END SET @i=@i+1 END PRINT @s PRINT @nums
用CREATE PROCEDURE命令创建存储过程 命令创建存储过程
CREATE PROCEDURE procedure_name [ ; number ] [ { @parameter data_type } [ VARYING ] [ = default ] [ OUTPUT ] ] [ ,...n ] [ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ] [ FOR REPLICATION ] AS sql_statement [ ...n ]
DECLARE @id char(8) SELECT @id='10010001'
[例7-2] 查询学号为“S7”的学生的学号与 例 查询学号为“ ” 姓名，并存储到变量@sno和@sn中。 姓名，并存储到变量 和 中
DECLARE @sno varchar(10) DECLARE @sn varchar(10) SELECT @sno = SNo,@sn = SN FROM s WHERE SNo = 'S7'
[例7-6] 从SC表中查询所有同学选课的成绩情况，凡成绩为空的输出 例 表中查询所有同学选课的成绩情况， 表中查询所有同学选课的成绩情况 未考” 小于60分的输出 不及格” 分的输出“ 分至70分的输出 “未考”、小于 分的输出“不及格”、60分至 分的输出“及格”、 分至 分的输出“及格” 70分至 分的输出“良好”、大于或等于 分的输出“优秀”。 分至90分的输出 分的输出“ 分至 分的输出“良好” 大于或等于90分的输出 优秀” SELECT SNo,CNo, Score= CASE WHEN Score IS NULL THEN '未考 未考' 未考 WHEN Score<60 THEN '不及格 不及格' 不及格 WHEN Score>=60 AND Score<70 THEN '及格 及格' 及格 WHEN Score>=70 AND Score<90 THEN '良好 良好' 良好 WHEN Score>=90 THEN '优秀 优秀' 优秀 END FROM SC
一个Transact-SQL程序与一般的高级语言的语法要素是基本一致的， 程序与一般的高级语言的语法要素是基本一致的， 一个 程序与一般的高级语言的语法要素是基本一致的 主要含有注释 变量与常量、各种运算符、函数与表达式、 注释、 主要含有注释、变量与常量、各种运算符、函数与表达式、流程控制 语句、批处理等 语句、批处理等。
[例7-10] 求1+2+3+…+10的总和。 例 的总和。 … 的总和 DECLARE @s SMALLINT,@i SMALLINT SET @i=1 SET @s=0 BEG: IF (@i<=10) BEGIN SET @s=@s+@i SET @i=@i+1 GOTO BEG END PRINT @s
全局变量
全局变量由系统定义和维护的， 全局变量由系统定义和维护的，只能使用预先说明 及定义的全局变量。 及定义的全局变量。 全局变量对用户而言是只读的， 全局变量对用户而言是只读的，用户无法对它们进 例如： 行修改或管理 。例如：@@DBTS系统时间戳 系统时间戳
注释符
中可以使用两类注释符： 在Transact-SQL中可以使用两类注释符： 中可以使用两类注释符
BEGIN <命令行或程序块 命令行或程序块> 命令行或程序块 END
[例7-3] 从SC数据表中求出学号为 同学的 例 数据表中求出学号为S7同学的 数据表中求出学号为 平均成绩，如果此平均成绩大于或等于60分 平均成绩，如果此平均成绩大于或等于 分， 则输出“ 则输出“Pass!”信息。 ”信息。 IF (SELECT AVG(Score) FROM SC WHERE SNo='S7' GROUP BY SNo)>=60 BEGIN PRINT 'Pass!' END
7.1.3 流程控制命令