第6章 SQL程序设计与开发-模板.ppt

1、数据库原理与设计,数据库原理与设计,第6章 SQL程序设计与开发,数据库原理与设计,第6章 SQL程序设计与开发,批处理与脚本 SQL程序设计基础 流程控制语句 游 标 SQL程序的调试与错误处理 SQL程序实例,SQL Server数据库应用中复杂的业务数据处理需要编写一些SQL程序来完成。 SQL程序是面向过程的语言与SQL的结合，可以进行复杂的数据处理。,数据库原理与设计,在数据库应用的客户端适当使用批处理具有以下优点： 减少数据库服务器与客户端之间的数据传输次数，消除过多的网络流量。 减少数据库服务器与客户端之间传输的数据量。 缩短完成逻辑任务或事务所需的时间。 较短的事务不会长期占有

2、数据库资源，能尽快释放锁，有效避免死锁。 增加逻辑任务处理的模块化，提高代码的可复用度，减少维护工作量。,批处理与脚本,批处理由一个或多个SQL语句组成，应用程序将这些语句作为一个整体单元提交给SQL Server，由SQL Server编译成一个执行单元，然后作为一个整体来执行。批处理的种类较多，如存储过程、触发器、函数内的所有语句都构成了一个批处理。,数据库原理与设计,批处理的执行,只要批处理中的语句没有任何语法错误，就可以经过编译建立执行计划。,（1）不能建立执行计划的批处理 在下面的示例中，批处理中存在语法错误，不能建立执行计划，其中Pubs是SQL Server自带的测试数据库。 U

3、SE pubs CREATE TABLE TestBatch (Cola INT PRIMARY KEY, Colb CHAR(3) INSERT INTO TestBatch VALUES (1, aaa) INSERT INTO TestBatch VALUES (2, bbb) INSERT INTO TestBatch VALUSE (3, ccc) /* 语法错误 ，VALUES 拼写错误*/ SELECT * FROM TestBatch GO,数据库原理与设计,批处理的执行,下面的示例没有语法错误，可以建立执行计划。在执行过程中，由于第3个INSERT语句产生主键重复的错误，因此

4、该INSERT语句与之后的SELECT语句不能被执行。由于前两个INSERT语句成功地执行并且提交，因此它们在发生运行时错误之后被保留下来。 USE pubs CREATE TABLE TestBatch (Cola INT PRIMARY KEY, Colb CHAR(3) INSERT INTO TestBatch VALUES (1, aaa) INSERT INTO TestBatch VALUES (2, bbb) INSERT INTO TestBatch VALUES (1, ccc) /* 主键重复*/ SELECT * FROM TestBatch /* 返回行1和2的记录*

5、/ GO,数据库原理与设计,编写批处理的规则, 不能在同一个批处理中更改表，然后引用新列。 不能在删除一个对象之后，立即在同一个批处理中引用该对象。 不能在定义一个CHECK约束后，立即在同一个批处理中使用该约束。 CREATE DEFAULT、CREATE PROCEDURE、CREATE RULE、CREATE TRIGGER和CREATE VIEW语句，在一个批处理中只能提交一个。 如果批处理中的第一句是执行某些存储过程的EXECUTE语句，则EXECUTE关键字可以省略不写。如果EXECUTE语句不是批处理中的第一条语句，则需要EXECUTE关键字。,数据库原理与设计,脚本,Trans

6、act-SQL语句的集合称为脚本。 Transact-SQL脚本存储为文件，带有sql扩展名。 把编写好的SQL语句（例如，创建数据库对象、调试通过的SQL 语句集合）保存起来，以便下一次执行同样（或类似）操作时，调用这些语句集合。这样可以省去重新编写调试SQL语句的麻烦，提高工作效率。 脚本文件可以调入查询分析器查看内容或再次被执行，也可以通过记事本等浏览器查看内容。,数据库原理与设计,SQL程序设计基础,1SQL程序基本成分 2SQL程序编写规范,数据库原理与设计,变量,Transact-SQL中的变量分为局部变量和全局变量。 局部变量的声明格式为： DECLARE local_varia

7、ble data_type , local_variable data_type. 如： DECLARE empidvar INT SET empidvar = 1234 SELECT * FROM Employees WHERE Employeeid = empidvar DECLARE pub_id CHAR(4), hire_date DATETIME SET pub_id = 0877 SET hire_date = 1/01/93 SELECT pub_id = 0877, hire_date = 1/01/93 /*使用SELECT赋值也可以*/ SELECT Fname, Lna

8、me FROM Employee WHERE Pub_id = pub_id AND Hire_date = hire_date,数据库原理与设计,运算符,SQL Server提供赋值运算符、算术运算、逻辑运算、位运算、比较运算、字符串连接运算符等。 赋值运算符“=”用于将表达式的值赋给某个变量。 算术运算符在两个表达式上执行数学运算，包括加法（+）、减法（）、乘法（*）、除法（/）、取模（%）等运算，加减运算也可用于datetime和smalldatetime日期类型。 位运算符可以在两个表达式之间执行位操作，包括按位与（ /* 定义变量*/ IF (CubeLength0) AND (Cu

9、beWidth 0) AND (CubeHeight0) SELECT Volume =CubeLength * CubeWidth * CubeHeight ELSE SELECT Volume =-1; /* 条件分支流程*/ RETURN (Volume ) END,数据库原理与设计,SQL程序编写规范,对变量和数据库对象等标识符采用有意义的命名 编写代码时养成合理的大小写习惯 对存储过程、游标等数据库对象命名时，采用适当的前缀和后缀 代码采用缩进方式 在程序中增加适当的注释,数据库原理与设计,流程控制语句,数据库原理与设计,语句块：BEGINEND,USE pubs IF (SELEC

10、T COUNT(*) FROM deleted, sales WHERE sales.title_id = deleted.title_id) 0 BEGIN ROLLBACK TRANSACTION PRINT You cant delete a title with sales. END,BEGINEND关键字之间封装了一系列的 SQL 语句，形成一个语句块，代表一组一起执行的SQL 语句。BEGINEND的语法结构如下： BEGIN SQL 语句1 SQL 语句2 END,数据库原理与设计,条件执行：IF.ELSE语句,IF.ELSE语句的语法结构如下： IF 布尔表达式 SQL 语句|

11、 SQL 语句块 ELSE SQL 语句| SQL 语句块 IF.ELSE语句允许嵌套，可以在其他IF之后或在ELSE下面，嵌套另一个IF语句，嵌套层数没有限制。,数据库原理与设计,条件执行：IF.ELSE语句（2）,USE pubs IF (SELECT AVG(price) FROM titles WHERE type = mod_cook) $15 BEGIN PRINT the following titles are excellent mod_cook books: PRINT SELECT SUBSTRING(title, 1, 35) AS Title FROM titles

12、WHERE type = mod_cook END ELSE PRINT Average title price is more than $15.,数据库原理与设计,条件执行：IF.ELSE语句（3）,USE pubs IF (SELECT AVG (price) FROM titles WHERE type = mod_cook) $15 BEGIN PRINT The following titles are expensive mod_cook books: PRINT SELECT SUBSTRING (title, 1, 35) AS Title FROM titles WHERE

13、 type = mod_cook END,数据库原理与设计,多分支CASE表达式,简单CASE函数将某个表达式与一组简单表达式进行比较以确定结果。 CASE搜索函数计算一组布尔表达式以确定结果。,简单CASE表达式的语法结构如下： CASE 表达式 WHEN表达式THEN表达式 . . ELSE 表达式 END CASE 搜索函数的语法结构如下： CASE WHEN 布尔表达式 THEN表达式 . . ELSE表达式 END,数据库原理与设计,多分支CASE表达式,USE pubs GO SELECT Category = CASE type WHEN popular_comp THEN Po

14、pular Computing WHEN mod_cook THEN Modern Cooking WHEN business THEN Business WHEN psychology THEN Psychology WHEN trad_cook THEN Traditional Cooking ELSE Not yet categorized END, CAST (title AS varchar(25) AS Shortened Title, price AS Price FROM titles WHERE price IS NOT NULL ORDER BY type, price C

15、OMPUTE AVG(price) BY type - CAST函数的功能是将某种数据类型的表达式显式转换为另一种数据类型,数据库原理与设计,循环：WHILE语句,WHILE语句的语法结构如下： WHILE布尔表达式 SQL 语句| SQL 语句块 BREAK SQL 语句| SQL 语句块 CONTINUE WHILE语句也允许嵌套。 BREAK语句使程序从最内层的WHILE循环中退出。 CONTINUE语句使WHILE循环重新开始执行，忽略CONTINUE关键字后的语句。,数据库原理与设计,循环：WHILE语句（2）,USE pubs GO WHILE (SELECT AVG(price)

16、 FROM titles) $50 BREAK ELSE CONTINUE END PRINT Too much for the market to bear,数据库原理与设计,循环：WHILE语句（3）,DECLARE counter smallint SET counter = 1 WHILE counter 5 BEGIN SELECT RAND(counter) Random_Number SET NOCOUNT ON SET counter = counter + 1 SET NOCOUNT OFF END GO,数据库原理与设计,非条件执行：GOTO 语句,GOTO语句的语法结构如

17、下： 标签 : -定义标签 GOTO 标签-改变执行,USE pubs GO DECLARE tablename sysname SET tablename = Nauthors table_loop: -定义标签 IF (FETCH_STATUS -2) BEGIN SELECT tablename = RTRIM(UPPER(tablename) EXEC (SELECT + tablename + = COUNT(*) FROM + tablename ) PRINT END FETCH NEXT FROM tnames_cursor INTO tablename IF (FETCH_S

18、TATUS -1) GOTO table_loop -改变执行 GO,数据库原理与设计,调度执行：WAITFOR,BEGIN WAITFOR TIME 22:20 EXECUTE update_all_stats END。,语法结构如下： WAITFOR DELAY 时间 | TIME 时间 其中DELAY指定等待的时间间隔，最长可达 24 小时；TIME指定等待到的时间点，即触发的具体时间；时间可以是datetime 数据类型，格式为hh:mm:ss，不指定日期。,在晚上 10:20 执行存储过程 update_all_stats。,数据库原理与设计,游标,SELECT语句返回所有满足条件的

19、完整记录集，在数据库应用程序中常常需要处理结果集的一行或多行。游标（CURSOR）是结果集的逻辑扩展，可以看作是指向结果集的一个指针，通过使用游标，应用程序可以逐行访问并处理结果集。 使用游标时，应先声明，然后打开，接着使用；使用完后关闭、释放资源。,数据库原理与设计,游标,声明游标：DECLARE CURSOR语句 打开游标：OPEN语句 读取数据：FETCH语句 关闭游标：CLOSE语句 释放游标：DEALLOCATE语句,数据库原理与设计,游标使用实例,USE MS BEGIN DECLARE Cno VARCHAR(5) -变量：课程编号 DECLARE Clname VARCHAR

20、(30) -变量：班级名称 DECLARE clno VARCHAR (6) -变量：班级编号 DECLARE avgscore NUMERIC(10,2) -变量：平均成绩 DECLARE Cterm INT -变量：学期 DECLARE class_cursor CURSOR FOR SELECT clname,clno,dbo.termConvert(2005-2006/2 ,clno) FROM class -声明班级游标 /*其中termConvert函数是自定义函数，可以将如“2006-2007/2”的学期表述的字符串方式转换为如1、2、3等表述的数字方式。如2005年入学的同学的

21、“2006-2007/2”学期是其在校的第4学期 */ OPEN class_cursor -打开班级游标 FETCH NEXT FROM class_cursor INTO CLname,clno, Cterm -读取游标数据 WHILE FETCH_STATUS = 0 -检测游标数据是否读取完，如果还有数据，继续循环 BEGIN SET avgscore=(SELECT ISNULL(avg(score) ,0) FROM sc a ,student b ,class c,course d WHERE a.sno=b.sno AND b.clno=c.clno AND b.clno=cl

22、no AND o=o AND d.Cterm=Cterm),数据库原理与设计,游标使用实例（2）,IF avgscore0 /* 根据班级平均成绩是否为0判断，该班的成绩是否登记，如果为0，表明没有登记该班的在2005-2006/2学期的成绩 */ BEGIN PRINT 2005-2006/2+学期 +CLname+ 各门课总平均成绩为+str(avgscore,5,1) -每个学生的平均成绩和获得的学分 PRINT 该班每个学生的平均成绩如下： SELECT e.sname ,d.avgscore ,totalCredit FROM (SELECT a.sno,AVG(score) avg

23、score,SUM(dbo.CreditConvert(score,CCredits) totalCredit FROM student a ,sc b,course c WHERE a.sno=b.sno AND o=o AND c.cterm=Cterm GROUP BY a.sno) d ,student e,class f WHERE e.sno=d.sno AND e.clno=f.clno AND f.clno=clno END ELSE PRINT 2005-2006/2+学期 +CLname+ 成绩没有登记 FETCH NEXT FROM class_cursor INTO C

24、Lname,clno, Cterm END CLOSE class_cursor DEALLOCATE class_cursor END,数据库原理与设计,SQL程序的错误类型,语法错误是指不符合Transact-SQL规范的错误，这类错误会导致SQL语句不能编译。不熟悉语法规范经常会导致这类错误的发生。 还有一些错误虽然没有语法错误，也不会使SQL语句执行失败，但确实不能实现预设的功能，这是一种逻辑错误。逻辑错误不仅会发生在程序开发阶段，也常常会出现在程序实际运行阶段。只有非常清楚程序需要实现的功能，才能避免此类错误。如果发生了逻辑错误，可以跟踪程序的执行流程，逐行、逐段地检查程序执行结果，

25、发现产生问题的原因。 运行时错误是指SQL程序在执行过程中出现的意想不到的错误，如由于锁表影响对数据表的更改、修改数据库对象时违反数据完整性与一致性的约束等。这类错误导致了一些SQL语句执行失败。采取必要的边界处理和错误处理，可以减少此类错误的发生次数。,数据库原理与设计,SQL程序的错误处理,1定位错误发生的位置 2判定错误原因 3简化程序以便调试,数据库原理与设计,学分转换函数, 功能要求：将学生考试成绩转换成学分的功能。如果考试通过获得该课程的学分，否则获得学分为0。 入口参数：成绩和课程学分， 返回：返回应得学分。 CREATE FUNCTION CreditConvert( scor

26、e NUMERIC(3,1),CCredits NUMERIC (3,1) -score：考试成绩 -CCredits：课程规定学分 RETURNS NUMERIC (5,2) -应得学分 AS BEGIN RETURN CASE SIGN(score-60) WHEN 1 THEN CCredits WHEN 0 then CCredits WHEN -1 then 0 END END,数据库原理与设计,学期转换函数, 入口参数：学年和入学年份 返回：数字表示的学期。 -termConvert 功能：学期转换 CREATE function termConvert( trem CHAR(11

27、),clno CHAR (6) -trem 学年，格式如：2006-2007/2 - clno 班级编号，格式如：020001，前2位代表入学年份 RETURNS INT - 在校第几学期 AS BEGIN RETURN CONVERT(NUMERIC,SUBSTRING(trem,1,4)-CONVERT(NUMERIC,20+ SUBSTRING(clno,1,2)*2+CONVERT(NUMERIC,SUBSTRING(trem,11,1) ) END,数据库原理与设计,统计平均成绩,CREATE PROCEDURE p_AverageScore term varchar(11)-入口参

28、数：学期 -学期的格式为：XXXX-XXXX/X。 -前9位标别学年，最后一位表示本学年的第几学期。如2005-2006/2表示2005-2006学年的第2学期。 AS BEGIN DECLARE Cno VARCHAR(5) -变量：课程编号 DECLARE Clname VARCHAR (30) -变量：班级名称 DECLARE clno VARCHAR (6) -变量：班级编号 DECLARE avgscore NUMERIC(10,2) -变量：平均成绩 DECLARE Cterm INT -变量：学期 DECLARE class_cursor CURSOR FOR SELECT CL

29、name,CLno,dbo.termConvert(term ,clno) FROM class -声明班级游标 /*其中termConvert函数是自定义函数，可以将如“2006-2007/2”的学期表述的字符串方式转换为如1、2、3等表述的数字方式。如2005年入学的同学的“2006-2007/2”学期是其在校的第4学期 */,数据库原理与设计,统计平均成绩,OPEN class_cursor -打开班级游标 FETCH NEXT FROM class_cursor INTO CLname,clno, Cterm -读取游标数据 WHILE FETCH_STATUS = 0 -检测游标数据

30、是否读取完，如果还有数据，继续循环 BEGIN SET avgscore=(SELECT ISNULL(avg(Score) ,0) FROM SC a ,Student b ,Class c,Course d WHERE a.SNo=b.SNo AND b.CLno=c.CLno AND b.CLno=clno AND a.CNo=d.CNo AND d.Cterm=Cterm) IF avgscore0 /* 根据班级平均成绩是否为0判断该班的成绩是否登记，如果为0，表明没有登记该班的在本学期的成绩 */ BEGIN PRINT term +学期 +CLname+ 各门课总平均成绩为+st

31、r(avgscore,5,1) -每个学生的平均成绩和获得的学分 PRINT 该班每个学生的平均成绩如下： SELECT e.SName ,d.avgscore ,totalCredit FROM (SELECT a.SNo,AVG(score) avgscore,SUM(dbo.CreditConvert(score,CCredits) totalCredit FROM Student a, SCb, Course c WHERE a.SNo=b.SNo AND b.CNo=c.CNo AND c.Cterm=Cterm GROUP BY a.SNo) d ,Student e,Class

32、f WHERE e.SNo=d.SNo AND e.CLno=f.CLno AND f.CLno=clno END ELSE PRINT term +学期 +CLname+ 成绩没有登记 FETCH NEXT FROM class_cursor INTO CLname,clno, Cterm END CLOSE class_cursor DEALLOCATE class_cursor END,数据库原理与设计,统计不同分数段的人数和平均成绩,CREATE PROCEDURE p_SatSore cno CHAR(5) -入口参数：班级编号 clno CHAR (6) -入口参数：课程编号 AS

33、 BEGIN DECLARE socre1 INT -待统计分数段上限 DECLARE socre2 INT -待统计分数段下限 DECLARE num INT -待统计分数段人数 DECLARE CLNAME VARCHAR(30) -班级名称 DECLARE CNAME VARCHAR(50) -课程名称 -查询课程名称和班级名称 SET CLNAME=(SELECT CLNAME FROM CLASS WHERE CLNO=clno) SET CNAME=(SELECT CNAME FROM COURSE WHERE CNO=cno) PRINT CLNAME+ + 考试成绩 按照分数段统计情况 -设置被统计分数段的初值 SET socre1=100 SET socre2=90 WHILE (socre1=60) BEGIN SET num=(SELECT count(*) FROM SC a, Class b,Student c WHERE b.CLno=c.CLno AND a.SNo=c.SNo AND b.CLno=clno AND a.Cno=cno AND score BETWEEN socre2 AND socre1