《数据库管理与应用教材的电子课件和所用数据库 06-Chap06》由会员分享，可在线阅读，更多相关《数据库管理与应用教材的电子课件和所用数据库 06-Chap06（49页珍藏版）》请在金锄头文库上搜索。
1、数据库管理与应用实例教程,彭勇 主编,第6章 存储过程和触发器,本章导航,本章的主要内容及其在SQL Server2005数据库管理系统中的位置如图示。,学习要点,（1）T-SQL语言的基本元素 （2）流程控制语句 （3）存储过程操作 （4）触发器操作,T-SQL编程基础,一、批和脚本,1批处理 批处理是指包含一个或多个T-SQL语句的组，从应用程序一次性地发送到SQL Server 2005进行执行。SQL Server将批处理的语句编译为一个可执行单元，称为执行计划。执行计划中的语句每次执行一条，如果在一个批处理中存在一个语法错误，那么所有的语句都无法编译通过。 2脚本 脚本是批处理的存在
2、方式，将一个或多个批处理组织到一起就是一个脚本，例如，我们执行命令的各个实例都可以称为一个脚本。我们将脚本保存到磁盘文件上就称为脚本文件。使用脚本文件对重复操作或在几台计算机之间交换SQL语句是非常有用的。 3注释 （1）行内注释 （2）块注释 4标识符 （1）常规标识符 （2）分隔标识符,5输出语句 （1）PRINT语句 PRINT语句把用户定义的消息返回客户端，其基本语法格式如下所示： PRINT （2）RAISERROR语句 返回用户定义的错误信息，其基本语句格式如下所示： RAISERROR(msg_id|msg_str,severity,state) 例如，输出“Hello,你好！”
3、字符串和返回用户定义的错误信息。 PRINT Hello,你好！ RAISERROR(发生错误,16,1),二、变量,1局部变量 （1）局部变量的定义 局部变量必须用DECLARE命令定义后才可以使用，定义局部变量的语法格式如下： DECLARE 变量名 数据类型，N （2）局部变量的赋值方法 其语法格式如下： SET 变量名=表达式 或 SELECT 变量名=表达式,N 局部变量的定义与赋值 DECLARE var1 VARCHAR(5),var2 VARCHAR(8) SELECT var1=Hello,var2= 你好! PRINT var1+var2 SELECT var1+var2
4、PRINT结果显示在“消息”框中，SELECT结果显示在“结果”框中，运行结果如图所示。, 查询Book表，将返回的记录数据赋值给局部变量NUM。 USE books GO DECLARE SUM INT SET SUM=(SELECT COUNT(*) FROM Book) -上面的语句也可以写成 SELECT SUM=COUNT(*) FROM Book SELECT SUM AS 书总量 GO,2全局变量,例如，利用全局变量查看SQL Server的版本和当前所使用的语言。 SELECT VERSION AS 版本 SELECT LANGUAGE AS 语言 GO,课堂实践1,【任务1】
5、 在一个批处理中，定义一个字符型局部变量cXM和可变长字符型局部变量vAddress，并分别赋值为“张三”和“株洲田心区”，最后输出变量的值，并要求通过注释对批处理中语句的功能进行说明。 【任务2】 编写一个批处理，通过全局变量返回当前SQL Server服务器的版本和处理器类型。,三、函数,1聚合函数,例如，求图书的平均价格。 SELECT 平均值=AVG(mprice) FROM Book GO 程序执行结果如图所示。,2字符串函数,例如，字符串函数的使用。 PRINT CHARINDEX(人民,中华人民共和国) PRINT UPPER(Hello china!) PRINT LOWER(
6、Hello china!) PRINT STUFF(ABCDEF,2,4,mnh) PRINT REPLICATE(*,5)+SPACE(2)+REPLICATE($,2),3数学函数,例如，数学函数的使用。 SELECT EXP(1) AS e的一次幂,ROUND(EXP(1),2,3) AS 约等于,4日期时间函数,例如，日期函数的使用。 DECLARE VAR1 DATETIME SET VAR1=1986/1/1 SELECT 当前日期=GETDATE(), 30天后的日期=DATEADD(DAYOFYEAR,30,GETDATE(), 距离现在年数=DATEDIFF(YY,VAR1,
7、GETDATE(), 距离现在月数=DATEDIFF(MM,VAR1,GETDATE(), 距离现在天数=DATEDIFF(DD,VAR1,GETDATE(),5系统函数,例如，改写上例中的程序。以消息方式输出结果。 DECLARE VAR1 DATETIME SET VAR1=1986/1/1 PRINT 当前日期是：+CAST(GETDATE() AS CHAR(21)+CHAR(13)+30天后的日期:+CAST(DATEADD(DAYOFYEAR,30,GETDATE() AS CHAR(21)+CHAR(13)+ 距离现在年数:+ CAST(DATEDIFF(YY,VAR1,GETD
8、ATE() AS CHAR(2)+年+ 距离现在月数:+ CAST(DATEDIFF(MM,VAR1,GETDATE() AS CHAR(3)+月+ 距离现在天数:+ CAST(DATEDIFF(DD,VAR1,GETDATE() AS CHAR(4)+天,6游标函数,7配置函数,三、流程控制语句,1顺序控制语句,其语法格式如下： BEGIN SQL语句|语句块 END 2分支控制语句 IFELSE语句用来实现选择结构。其语法格式如下： IF SQL语句1|语句块1 ELSE SQL语句2|语句块2,例如，在图书信息表Book中，如果存在编号为“P0006”的出版社，则输出该出版社出版图书的平
9、均价格，否则，显示信息“不存在此出版社编号P0006”。 IF EXISTS(SELECT cPublisherID FROM Book WHERE cPublisherID=P0006) BEGIN DECLARE AVG FLOAT SET AVG=(SELECT AVG(mprice) FROM Book WHERE cPublisherID=P0006) SELECT 存在此出版社编号P0006,AVG AS 平均价格 END ELSE PRINT 不存在此出版社编号P0006 程序执行结果：“不存在此出版社编号P0006”。,3循环控制语句,WHILE语句的基本语法格式如下所示： W
10、HILE SQL语句|语句块 BREAK SQL语句|语句块 CONTINUE SQL语句|语句块,例如，用WHILE语句计算2的10次方。 DECLARE MY_VAR INT,MY_RESULT INT SET MY_VAR=10 SET MY_RESULT=1 WHILE MY_VAR0 BEGIN SET MY_RESULT=MY_RESULT*2 SET MY_VAR=MY_VAR-1 END PRINT MY_RESULT 程序执行结果：1024,4错误处理语句 T-SQL语句组可以包含在TRY块中。如果TRY块内部发生错误，则会将控制传递给CATCH块中包含的另一个语句组。 BE
11、GIN TRY END TRY BEGIN CATCH END CATCH,例如，使用TRY-CATCH捕捉SQL语句执行过程中的异常。 BEGIN TRY SELECT 1/0 END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS 错误号, ERROR_SEVERITY() AS 错误等级, ERROR_STATE() AS 错误状态, ERROR_PROCEDURE() AS 错误过程, ERROR_LINE() AS 错误行, ERROR_MESSAGE() AS 错误信息 END CATCH GO,5其他流程控制语句 （1）RETURN语句,RETU
12、RN 整型表达式 （2）无条件转移语句GOTO GOTO 标号 （3）WAITFOR语句 WAITFORDELAY time|TIME time 例如，等待10秒，再显示Book表的内容。 WAITFOR DELAY 00:00:10 SELECT * FROM Book 以下是上午10：00显示Book表的内容。 WAITFOR TIME 00:00:10 SELECT * FROM Book,6CASE表达式,（1）简单表达式,CASE 测试表达式 WHEN 测试值1 THEN 结果表达式1 WHEN 测试值2 THEN 结果表达式2 ELSE 结果表达式N END,例如，显示Book表中
13、的数据，将出版社编号替换为出版社名称。 SELECT vBookName, 出版社名称=CASE cPublisherID WHEN P0001 THEN 高等教育出版社 WHEN P0002 THEN 安徽教育出版社 WHEN P0003 THEN 安徽科学技术出版社 WHEN P0004 THEN 安徽美术出版社 WHEN P0222 THEN 机械工业出版社 END FROM Book,(2) 搜索表达式,CASE WHEN 逻辑表达式1 THEN 结果表达式1 WHEN 逻辑表达式2 THEN 结果表达式2 ELSE 结果表达式N END,例如，在Book表中，根据书的价格来确定书的等
14、级。单价在50元以上（含50元）的为“高价书”，3050元之间为“中价书”，低于30元（含30元）为“低价书”。 SELECT vBookName, 单价=CASE WHEN mprice=50 THEN 高价书 WHEN mprice30 and mprice50 THEN 中价书 WHEN mprice=30 THEN 低价书 END FROM Book,课堂实践2,【任务1】 参考部门表Department中的数据，显示Reader表中的信息，将部门编号（cDepartmentID列）替换为部门名称。 【任务2】 利用WHILE循环求1100的偶数和。 【任务3】 分析RAND(5)，R
15、OUND(-121.66666,2)和ROUND(-121.66666,-2)的值。,存储过程,一、存储过程的基本知识,1存储过程的优点 （1）允许模块化程序设计 （2）允许更快执行 （3）减少网络流量 （4）保证系统的安全性 2系统存储过程 3存储过程与视图的比较,二、课堂案例1使用SSMS管理存储过程,【案例学习目标】 学会用管理平台来管理books数据库中的存储过程。 【案例知识要点】 存储过程的创建、修改、查看与删除。 【案例完成步骤】 （1）打开SQL Server Management Studio的“对象资源管理器”，依次展开各节点到数据库books中的【可编程性】节点，在【存储过程】节点上右击，在弹出的快捷菜单上选择【新建存储过程】，如图所示。,（2）执行【新建存储过程】命令，右边窗格打开存储过程编写模板,（3）右边窗格中显示了存储过程的名称，用户可以根据模板输入存储过程所包含的文本，如图所示。创建一个存储过程selbook，用来查询图书信息表中所有信息。,（4）单击工具栏的按钮，成功创建后，在“对象资源管理器”中便可显示新创建的存储过程。,（5）右键单击新建的存储过程【selbook】，选择【执行存储过程】
