数据库系统原理与设计PPT课件

1、1ECUST-CS第第 7 7 章章 SQL SQL数据定义、更新及数据库编程数据定义、更新及数据库编程第第7 7章章 SQLSQL数据定义、更新数据定义、更新 及数据库编程及数据库编程树立健康的人生观、世界观u胸怀正面的世界观，维持自己的人格u不靠别人，要靠自己，为自己的未来念书u不搞自我中心，具有团队精神，待人和善有礼u要看到客观场景（Context）: 要全面，不片面 不光看当前，也看过去与未来2ECUST-CS第第 7 7 章章 SQL SQL数据定义、更新及数据库编程数据定义、更新及数据库编程第第7 7章章 SQLSQL数据定义、更新数据定义、更新 及数据库编程及数据库编程3ECUS

2、T-CS第第 7 7 章章 SQL SQL数据定义、更新及数据库编程数据定义、更新及数据库编程目目 录录SQL数据定义语言数据定义语言7.17.2SQL数据更新语言数据更新语言视视 图图T-SQL语言简介语言简介游游 标标存储过程存储过程触触 发发 器器7.47.37.67.57.74ECUST-CS第第 7 7 章章 SQL SQL数据定义、更新及数据库编程数据定义、更新及数据库编程7.4 T-SQL语言简介语言简介n 变量变量l局部变量局部变量：变量名前加变量名前加1个个符号符号l全局变量全局变量：变量名前加变量名前加2个个符号符号。如。如: ERROR：当：当事务事务成功时为成功时为0，

3、否则为最近一次的错误号，否则为最近一次的错误号 ROWCOUNT：返回受上一语句：返回受上一语句影响的行数影响的行数 FETCH_STATUS：返回最近：返回最近的的FETCH语句语句执行后的执行后的游标状态游标状态n 变量变量的的声明声明与与赋值赋值l声明声明变量变量的语法的语法： DECLARE , l单个单个变量变量赋值赋值的语法的语法：SET = l变量变量列表列表赋值赋值(或或显示显示表达式表达式的值的值)的语法的语法： SELECT = , = 5ECUST-CS第第 7 7 章章 SQL SQL数据定义、更新及数据库编程数据定义、更新及数据库编程7.4 T-SQL语言简介语言简介

4、n 运算符运算符l算术运算符：算术运算符：+，-，*，/，%(取余取余)l比较运算符：比较运算符：，=，=，=，!=l逻辑运算符：逻辑运算符：AND，OR，NOTl位运算符：位运算符：&(按位与按位与)，|(按位或按位或)，(按位非按位非)，(按位异或按位异或)l字符串连接运算符字符串连接运算符：+l赋值语句：赋值语句：SELECT：一次可一次可赋值赋值多个多个变量变量，或或显示显示多个多个表达式表达式的值的值SET：一次仅能给一次仅能给一个一个变量变量赋值赋值n 函数函数：数学函数、字符串函数、日期和时间函数、聚合函数和系统函数等数学函数、字符串函数、日期和时间函数、聚合函数和系统函

5、数等l数学函数数学函数：绝对值函数绝对值函数abs、随机数函数、随机数函数rand、四舍五入、四舍五入函数函数round、上取整函数、上取整函数ceiling、下取整函数、下取整函数floor、指数、指数函数函数exp、平方根函数、平方根函数sqrt等等6ECUST-CS第第 7 7 章章 SQL SQL数据定义、更新及数据库编程数据定义、更新及数据库编程7.4 T-SQL语言简介语言简介n 函数函数：数学函数、字符串函数、日期和时间函数、聚合函数和系统函数等数学函数、字符串函数、日期和时间函数、聚合函数和系统函数等l字符串函数字符串函数：7ECUST-CS第第 7 7 章章 SQL SQL数

6、据定义、更新及数据库编程数据定义、更新及数据库编程7.4 T-SQL语言简介语言简介n 函数函数：数学函数、字符串函数、日期和时间函数、聚合函数和系统函数等数学函数、字符串函数、日期和时间函数、聚合函数和系统函数等l日期和时间函数日期和时间函数：8ECUST-CS第第 7 7 章章 SQL SQL数据定义、更新及数据库编程数据定义、更新及数据库编程7.4 T-SQL语言简介语言简介n 函数函数：数学函数、字符串函数、日期和时间函数、聚合函数和系统函数等数学函数、字符串函数、日期和时间函数、聚合函数和系统函数等l系统系统函数函数：9ECUST-CS第第 7 7 章章 SQL SQL数据定义、更新

7、及数据库编程数据定义、更新及数据库编程7.4 T-SQL语言简介语言简介n 函数函数：数学函数、字符串函数、日期和时间函数、聚合函数和系统函数等数学函数、字符串函数、日期和时间函数、聚合函数和系统函数等l系统系统函数函数： convert(data_type (length), expr , style) data_type：系统所提供的数据类型：系统所提供的数据类型 length：字符数据类型字符数据类型的可选参数的可选参数，用于控制字符串的长度，用于控制字符串的长度 expr：任何有效的：任何有效的SQL Serve表达式表达式 style：日期格式样式：日期格式样式(详见表详见表7-4)

8、。u例如，经常例如，经常将将datetime数据数据或或数值数值数据数据表达式表达式expr转换为转换为字符数据类型字符数据类型data_type，然后可用于字符串的，然后可用于字符串的连接连接输出输出10ECUST-CS第第 7 7 章章 SQL SQL数据定义、更新及数据库编程数据定义、更新及数据库编程7.4 T-SQL语言简介语言简介n 流程控制语句流程控制语句：11ECUST-CS第第 7 7 章章 SQL SQL数据定义、更新及数据库编程数据定义、更新及数据库编程7.4 T-SQL语言简介语言简介n 程序实例：程序实例：l 例例7.38 在在ScoreDB数据库中，查询数据库中，查询

9、Score表中的表中的最高成绩最高成绩，如果如果最高成绩最高成绩大于大于95分分，则显示，则显示“very good!”。use ScoreDB GODECLARE score numericSELECT score = (select MAX(score) from Score)if score 80PRINT good12ECUST-CS第第 7 7 章章 SQL SQL数据定义、更新及数据库编程数据定义、更新及数据库编程7.4 T-SQL语言简介语言简介n 程序实例：程序实例：l 例例7.38 在在ScoreDB数据库中，查询数据库中，查询Score表中的表中的最高成绩最高成绩，如果如果

10、最高成绩最高成绩大于大于95分分，则显示，则显示“very good!”。l 例例7.39 声明两个局部变量声明两个局部变量sno和和score，用于接受，用于接受SELECT语句查询返回的结果语句查询返回的结果(005号刘方晨号刘方晨)，并显示其结果，并显示其结果。13ECUST-CS第第 7 7 章章 SQL SQL数据定义、更新及数据库编程数据定义、更新及数据库编程7.4 T-SQL语言简介语言简介n 程序实例：程序实例：l 例例7.45 在学生表在学生表Student中，中，如果有如果有蒙古族蒙古族学生学生，则显示，则显示：存在蒙古族的学生存在蒙古族的学生。14ECUST-CS第第 7

11、 7 章章 SQL SQL数据定义、更新及数据库编程数据定义、更新及数据库编程7.4 T-SQL语言简介语言简介n 程序实例：程序实例：l 例例7.45 在学生表在学生表Student中，中，如果有如果有蒙古族蒙古族学生学生，则显示，则显示：存在蒙古族的学生存在蒙古族的学生。15ECUST-CS第第 7 7 章章 SQL SQL数据定义、更新及数据库编程数据定义、更新及数据库编程7.4 T-SQL语言简介语言简介n 程序实例：程序实例：l 例例7.45 在学生表在学生表Student中，中，如果有如果有蒙古族蒙古族学生学生，则显示，则显示：存在蒙古族的学生存在蒙古族的学生。l 例例7.46 列

12、示成绩表列示成绩表Score中的所有选课记录，要求根据中的所有选课记录，要求根据学期学期号号termNo的不同取值分别显示的不同取值分别显示开课时间开课时间为为xx年下半年年下半年、xx年年上半年上半年、xx年年暑暑期小学期期小学期，根据，根据成绩成绩score的不同取值分别显的不同取值分别显示示等级等级为为优良优良(80分及以上分及以上)、合格合格和和不及格不及格(小于小于60分分)。如。如152显示为显示为“16年上半年年上半年”。16ECUST-CS第第 7 7 章章 SQL SQL数据定义、更新及数据库编程数据定义、更新及数据库编程目目 录录SQL数据定义语言数据定义语言7.17.2S

13、QL数据更新语言数据更新语言视视 图图T-SQL语言简介语言简介游游 标标存储过程存储过程触触 发发 器器7.47.67.57.37.717ECUST-CS第第 7 7 章章 SQL SQL数据定义、更新及数据库编程数据定义、更新及数据库编程7.5游标游标n 对对SELECT语句的结果集语句的结果集进行进行逐行处理逐行处理，需使用，需使用游标游标。n 游标游标(cursor)是是系统系统为用户为用户开设开设的一个的一个数据缓冲区数据缓冲区，用于，用于存放存放SQL语句的执行结果语句的执行结果(元组集合元组集合)。每个。每个游标游标都有一都有一个名字，用户可以用个名字，用户可以用SQL提供的提供

14、的语句从语句从游标游标中中逐一获取逐一获取元组元组(记录记录)，并赋给，并赋给主变量主变量，交由，交由主语言主语言进一步进一步处理处理。n 可对可对游标游标的的当前位置当前位置进行进行更新更新、查询查询和和删除删除，使用，使用游标游标需要经历需要经历5个步骤：个步骤：l定义定义游标游标：DECLAREl打开打开游标游标：OPENl逐行提取逐行提取游标集游标集中的行：中的行：FETCHl关闭关闭游标游标：CLOSEl释放释放游标游标：DEALLOCATE7.5.1 游标游标的的定义定义与与使用使用7.5.2 当前当前游标集游标集的的修改修改与与删除删除18ECUST-CS第第 7 7 章章 SQ

15、L SQL数据定义、更新及数据库编程数据定义、更新及数据库编程7.5.1 游标的定义与使用游标的定义与使用n 定义定义游标游标l语法为：语法为：DECLARE CURSOR FOR FOR READ ONLY | UPDATE OF l在在使用使用游标游标之前，必须先之前，必须先定义定义游标游标。其中：。其中： ：所：所定义定义游标游标的的名称名称； ：游标游标要实现的功能程序要实现的功能程序，即，即SQL子查询子查询； ：属性列名属性列名列表；列表； FOR READ ONLY | UPDATE OF ： READ ONLY表示当前表示当前游标集游标集中的中的元组元组仅可仅可查询查询，不能，

16、不能修改修改; UPDATE OF 表示可对当前表示可对当前游标集游标集中的中的元组元组进行进行更新操作更新操作。 如果有如果有OF ，表示仅可以对，表示仅可以对游标集游标集中指定的中指定的属属性列性列进行进行修改操作修改操作；缺省为；缺省为UPDATE19ECUST-CS第第 7 7 章章 SQL SQL数据定义、更新及数据库编程数据定义、更新及数据库编程7.5.1 游标的定义与使用游标的定义与使用n 打开打开游标游标l语法为：语法为： OPEN l游标游标定义定义后，如果要后，如果要使用使用游标游标，必须要先，必须要先打开打开游标游标。 打开打开游标游标操作表示：操作表示： 系统按照系统按

17、照游标游标的定义的定义从从数据库数据库中将数据检索出来中将数据检索出来，放在内存的放在内存的游标集游标集中中(如果内存不够，会放在临时数如果内存不够，会放在临时数据库中据库中) 为为游标集游标集指定一个指定一个游标游标(相当于一个相当于一个指针指针)，该该游标游标指指向向游标集游标集中的中的第第1个元组个元组20ECUST-CS第第 7 7 章章 SQL SQL数据定义、更新及数据库编程数据定义、更新及数据库编程7.5.1 游标的定义与使用游标的定义与使用n 获取获取当前游标当前游标值值：即即获取获取当前游标当前游标所指向所指向元组元组的值的值，语法是，语法是FETCH INTO 执行一次该执

18、行一次该SQL语句，系统将语句，系统将当前游标当前游标所指向的元组所指向的元组属性属性值值放到放到变量变量中，然后中，然后游标游标自动下移自动下移一个元组一个元组。 当前游标当前游标所指向元组所指向元组的的每个属性每个属性值值必须必须分别用分别用一个一个变量变量来接收，来接收，即即变量变量个数个数、数据类型数据类型必须与必须与定义定义游标游标中的中的SELECT子句所定义子句所定义的的属性属性(或或表达式表达式)个数个数、数据类型数据类型相一致相一致。 当当游标游标移至尾部移至尾部，不可再不可再读取读取游标游标，必须，必须关闭关闭游标游标，然后，然后重新重新打打开开游标游标。 通过检查通过检查

19、全局变量全局变量FETCH_STATUS来判断来判断是否已读完是否已读完游标游标集集中中所有行所有行(元组元组)。 FETCH_STATUS的值有：的值有：0：FETCH 语句成功语句成功，表示已经从，表示已经从游标集游标集中获取了中获取了元组元组值值-1：FETCH 语句失败或此行不在结果集中语句失败或此行不在结果集中-2：被提取的行不存在：被提取的行不存在21ECUST-CS第第 7 7 章章 SQL SQL数据定义、更新及数据库编程数据定义、更新及数据库编程7.5.1 游标的定义与使用游标的定义与使用n 关闭关闭游标游标：游标游标不不使用使用了，必须了，必须关闭关闭，其语法为：，其语法为

20、： CLOSE n 释放释放游标游标(集集)所占用的空间：所占用的空间：关闭关闭游标游标并没有释放并没有释放游标游标所占所占用的内存和外存空间用的内存和外存空间，必须，必须释放释放游标游标，其语法为：，其语法为： DEALLOCATE 22ECUST-CS第第 7 7 章章 SQL SQL数据定义、更新及数据库编程数据定义、更新及数据库编程7.5.1 游标的定义与使用游标的定义与使用n 例例7.48 创建一个创建一个游标游标，逐行显示，逐行显示选修了选修了计算机原理计算机原理课程课程的的学生学生姓名姓名、相应成绩相应成绩和和该课程的平均分该课程的平均分。n 分析：分析： 选修选修计算机原理计算

21、机原理课程的课程的同学可能不止一个，需要使用同学可能不止一个，需要使用游标游标查询选修该门课程的查询选修该门课程的学生姓名学生姓名和和相应的选课成绩相应的选课成绩。 定义定义游标游标为：为：DECLARE myCur CURSOR FOR SELECT studentName, score, termNo FROM Student a, Course b, Score c WHERE a.studentNo=c.studentNo AND b.courseNo=c.courseNo AND courseName=计算机原理计算机原理 ORDER BY studentName23ECUST-CS

22、第第 7 7 章章 SQL SQL数据定义、更新及数据库编程数据定义、更新及数据库编程7.5.1 游标的定义与使用游标的定义与使用 要获得该课程的要获得该课程的平均分平均分，必须首先计算，必须首先计算选课人数选课人数和和总分总分 声明声明计数器和累加器变量计数器和累加器变量countScore、sumScore，赋初值赋初值为为0 DECLARE countScore smallint, sumScore int SET countScore=0 SET sumScore=0 声明声明3个变量个变量sName、score和和termNo，用于接收，用于接收游标游标集集中中当前游标当前游标中的中

23、的学生姓名学生姓名、选课成绩选课成绩和和选课学期选课学期 DECLARE sName varchar(20), score tinyint, termNo char(3) 由于由于FETCH命令命令每次仅从每次仅从游标集游标集中中提取提取一条一条记录记录，必须通，必须通过一个循环来过一个循环来重复重复提取提取，直到，直到游标集游标集中的中的全部全部记录记录被被提取提取全局变量全局变量 FETCH_STATUS用于判断用于判断是否正确地从是否正确地从游标集游标集中中提取提取到了到了记录记录；FETCH_STATUS=0表示已经表示已经正确正确提取提取到了到了游标游标记录记录；循环语句为：循环语句

24、为： WHILE ( FETCH_STATUS = 0 )24ECUST-CS第第 7 7 章章 SQL SQL数据定义、更新及数据库编程数据定义、更新及数据库编程7.5.1 游标的定义与使用游标的定义与使用 在循环体内：在循环体内： 首先，显示提取到的首先，显示提取到的学生姓名学生姓名、选课成绩选课成绩和和选课学期选课学期，使用语句：，使用语句：PRINT convert(char(10), sName) + convert(char(10), score) + convert(char(10), termNo) 其次，计数器其次，计数器countScore进行计数，并将提取到的进行计数，并

25、将提取到的成绩成绩累加到变量累加到变量sumScore中。语句为：中。语句为：SET sumScore = sumScore + score - 计算总分计算总分SET countScore = countScore + 1 - 计算选课人数计算选课人数 提取提取当前游标当前游标所指向所指向元组元组，并下移并下移(即即使其指向使其指向游标集游标集中下一中下一元组元组)： FETCH myCur INTO sName, score, termNo 重复，重复，直到全部直到全部游标游标记录记录处理完毕处理完毕，退出循环。，退出循环。 处理完全部处理完全部游标游标记录记录后：后： 关闭关闭和和释放释

26、放游标游标 对计数器对计数器countScore进行判断：如果为进行判断：如果为0，表示没有同学选修，其，表示没有同学选修，其平均分为平均分为0；否则，平均分等于总分除以选课人数。；否则，平均分等于总分除以选课人数。25ECUST-CS第第 7 7 章章 SQL SQL数据定义、更新及数据库编程数据定义、更新及数据库编程7.5.1 游标的定义与使用游标的定义与使用 程序如下：程序如下：/* 声明声明变量变量及及赋赋初值初值 */DECLARE sName varchar(20), score tinyint, termNo char(3)DECLARE countScore smallint,

27、 sumScore intSET countScore=0SET sumScore=0- 定义定义游标游标DECLARE myCur CURSOR FOR SELECT studentName, score, termNo FROM Student a, Course b, Score c WHERE a.studentNo=c.studentNo AND b.courseNo=c.courseNo AND courseName=计算机原理计算机原理 ORDER BY studentNameOPEN myCur - 打开打开游标游标，游标游标指向指向游标集游标集(查询结果集查询结果集)的的第第

28、1个个元组元组PRINT convert(char(10), 学生姓名学生姓名)+convert(char(10), 课程成绩课程成绩)+convert(char(10), 选课学期选课学期)PRINT replicate(-, 30) - 输出表头信息输出表头信息26ECUST-CS第第 7 7 章章 SQL SQL数据定义、更新及数据库编程数据定义、更新及数据库编程7.5.1 游标的定义与使用游标的定义与使用-获取获取当前游标当前游标的值的值(即即第第1个个元组元组值值)放到变量放到变量sName、score和和termNo中中FETCH myCur INTO sName, score,

29、termNo -获取获取第第1个个元组元组值值, 游标游标下移下移WHILE ( FETCH_STATUS = 0 ) - 循环循环处理处理游标游标集集中的每一个中的每一个元组元组BEGIN- 显示变量显示变量sName、score和和termNo中的值中的值PRINT convert(char(10), sName) + convert(char(10), score) + convert(char(10), termNo)SET sumScore = sumScore + score - 计算总分计算总分SET countScore = countScore + 1 - 计算选课人数计算选

30、课人数FETCH myCur INTO sName, score, termNo -获取获取当前游标当前游标所指向所指向元组元组值值, 游标游标下移下移ENDPRINT replicate(-, 30) - 输出表格底线输出表格底线PRINT 课程平均分课程平均分 - 输出输出选修选修计算机原理计算机原理课程的课程的所有学生的所有学生的平均分平均分IF countScore0 PRINT sumScore/countScoreELSE - 选修人数为选修人数为0，即没有学生即没有学生选修选修计算机原理计算机原理课程课程 PRINT 0.00CLOSE myCur - 关闭关闭游标游标DEALL

31、OCATE myCur - 释放释放游标游标27ECUST-CS第第 7 7 章章 SQL SQL数据定义、更新及数据库编程数据定义、更新及数据库编程7.5.2 当前游标集的修改与删除当前游标集的修改与删除 n 游标游标可以放在可以放在触发器触发器和和存储过程存储过程中使用中使用n 可以对可以对游标集游标集中的中的当前当前元组元组执行执行删除删除和和修改修改操作操作n 删除删除游标集游标集中的中的当前当前元组元组(即即游标游标所指向的所指向的元组元组) DELETE FROM WHERE CURRENT OF l从从游标集游标集中中删除删除当前当前元组元组后，后，游标游标定位于定位于被删除被删

32、除元组元组的的下一行下一行，但还需要用，但还需要用FETCH语句提取该行语句提取该行的值的值。n 修改修改游标集游标集中的中的当前当前元组元组(即即游标游标所指向的所指向的元组元组) UPDATE SET = , = . WHERE CURRENT OF 28ECUST-CS第第 7 7 章章 SQL SQL数据定义、更新及数据库编程数据定义、更新及数据库编程n 例例7.49 将将选修了选修了高等数学高等数学课程课程且且成绩不及格成绩不及格的的学生选课学生选课记录记录显示出来，并从数据库中显示出来，并从数据库中删除删除该选课记录该选课记录。/* 声明声明变量变量及及赋赋初值初值 */DECLA

33、RE sName varchar(20), score tinyint- 定义定义游标游标DECLARE myCur CURSOR FOR SELECT studentName, score FROM Student a, Course b, Score c WHERE a.studentNo=c.studentNo AND b.courseNo=c.courseNo AND courseName=高等数学高等数学 AND score60OPEN myCur - 打开打开游标游标7.5.2 当前游标集的修改与删除当前游标集的修改与删除 29ECUST-CS第第 7 7 章章 SQL SQL数据

34、定义、更新及数据库编程数据定义、更新及数据库编程7.5.2 当前游标集的修改当前游标集的修改 - 获取获取当前游标当前游标(此时指向此时指向游标集游标集中第中第1个个元组元组)的值的值放入变量放入变量sName和和scoreFETCH myCur INTO sName, score - 获取获取第第1个个元组元组值值，游标游标下移下移WHILE ( FETCH_STATUS = 0 ) - 循环处理循环处理BEGIN - 显示显示变量变量sName和和score中的值中的值 SELECT sName 学生姓名学生姓名, score 课程成绩课程成绩 - 删除删除当前游标当前游标获取获取值值的的

35、元组元组，并且，并且游标游标指向下一指向下一元组元组 DELETE FROM Score WHERE CURRENT OF myCur FETCH myCur INTO sName, score -获取获取当前游标当前游标所指向所指向元组元组值值, 游标游标下移下移ENDCLOSE myCur -关闭关闭游标游标DEALLOCATE myCur -释放释放游标游标n 注意注意：对：对游标游标当前位置当前位置的的记录记录进行进行修改修改和和删除删除，最终都将，最终都将 转化为转化为对对基本表基本表的的更新更新。30ECUST-CS第第 7 7 章章 SQL SQL数据定义、更新及数据库编程数据定

36、义、更新及数据库编程目目 录录SQL数据定义语言数据定义语言7.17.2SQL数据更新语言数据更新语言视视 图图T-SQL语言简介语言简介游游 标标存储过程存储过程触触 发发 器器7.47.67.57.77.331ECUST-CS第第 7 7 章章 SQL SQL数据定义、更新及数据库编程数据定义、更新及数据库编程7.6 存储过程存储过程n 存储过程存储过程是为了完成特定功能汇集而成的是为了完成特定功能汇集而成的一组命名了的一组命名了的SQL语语句集合句集合l 该集合该集合编译后存放在编译后存放在数据库数据库中中，可根据实际情况重新编译；，可根据实际情况重新编译；l 存储过程存储过程可直接在可

37、直接在服务器端服务器端运行运行，也可在，也可在客户端客户端远程调用运行远程调用运行，远程调用时远程调用时存储过程存储过程还是在还是在服务器端服务器端运行运行。n 使用使用存储过程存储过程具有如下优点：具有如下优点：l 将业务操作封装将业务操作封装可为可为复杂的业务操作复杂的业务操作编写编写存储过程存储过程，放在，放在数据库数据库中；中；用户用户可调用可调用存储过程存储过程执行执行，而，而业务操作对用户是不可见的业务操作对用户是不可见的；若若存储过程存储过程仅修改了执行体仅修改了执行体，没有修改接口没有修改接口(即即调用参数调用参数)，则用户，则用户程序不需要修改，达到程序不需要修改，达到业务封

38、装的效果业务封装的效果。l 便于事务管理便于事务管理事务控制可以用在事务控制可以用在存储过程存储过程中；中；用户可依据业务的性质定义事务，并对事务进行相应级别的操作。用户可依据业务的性质定义事务，并对事务进行相应级别的操作。32ECUST-CS第第 7 7 章章 SQL SQL数据定义、更新及数据库编程数据定义、更新及数据库编程7.6 存储过程存储过程 l 实现一定程度的安全性保护实现一定程度的安全性保护存储过程存储过程存放在存放在数据库数据库中，且中，且在在服务器端服务器端运行运行；对于不允许用户直接操作的对于不允许用户直接操作的基本表基本表或或视图视图，可通过调用，可通过调用存储过程存储过

39、程来来间接地访问这些间接地访问这些基本表基本表或或视图视图，达到一定程度的，达到一定程度的安全性安全性；这种安全性缘于用户这种安全性缘于用户对对存储过程存储过程只有执行权限，没有查看权限；只有执行权限，没有查看权限；拥有拥有存储过程存储过程的执行权限，自动获取了的执行权限，自动获取了存储过程存储过程中对相应中对相应基本表基本表或或视图视图的操作权限；的操作权限；这些操作权限仅能通过执行这些操作权限仅能通过执行存储过程存储过程来实现，一旦脱离来实现，一旦脱离存储过程存储过程，也就失去了相应操作权限。也就失去了相应操作权限。l 注意注意：对：对存储过程存储过程只需授予只需授予执行权限执行权限，不需

40、授予，不需授予基本表基本表或或视图视图的的操作权限操作权限。l 特别适合统计和查询操作特别适合统计和查询操作一般统计和查询，尤其是期末统计，往往涉及数据量大、表多，若一般统计和查询，尤其是期末统计，往往涉及数据量大、表多，若在在客户端客户端实现，实现，数据流量和网络通信量较大数据流量和网络通信量较大；很多情况下，管理信息系统的设计者，将复杂的查询和统计用很多情况下，管理信息系统的设计者，将复杂的查询和统计用存储存储过程过程来实现，来实现，免去免去客户端客户端的大量编程的大量编程。33ECUST-CS第第 7 7 章章 SQL SQL数据定义、更新及数据库编程数据定义、更新及数据库编程7.6 存

41、储过程存储过程 l 减少网络通信量减少网络通信量存储过程存储过程仅在仅在服务器端服务器端执行，执行，客户端客户端只接收结果只接收结果；由于由于存储过程存储过程与数据一般在同一个与数据一般在同一个服务器服务器中，可减少大量的网中，可减少大量的网络通信量。络通信量。l 使用使用存储过程存储过程前，首先要前，首先要创建创建存储过程存储过程。可对。可对存储过程存储过程进行进行修改修改和和删除删除。l 创建创建存储过程存储过程后，必须对后，必须对存储过程存储过程授予执行授予执行EXECUTE的权的权限限，否则该，否则该存储过程存储过程仅可以供创建者执行仅可以供创建者执行。n 7.6.1 创建创建存储过程

42、存储过程n 7.6.2 执行执行存储过程存储过程n 7.6.3 修改修改和和删除删除存储过程存储过程34ECUST-CS第第 7 7 章章 SQL SQL数据定义、更新及数据库编程数据定义、更新及数据库编程7.6.1 创建存储过程创建存储过程n 语法：语法：CREATE PROCEDURE ( = OUTPUT , = OUTPUT ) AS l其中：其中：存储过程存储过程的的名称名称，必须符合标识符规则，必须符合标识符规则，且在同一个且在同一个数据库数据库中唯一；中唯一；：参数名参数名，存储过程存储过程可不带可不带参数参数，形式形式参参数数是是变量变量，但，但实际实际参数参数可以是可以是变量

43、变量、常量和表达式、常量和表达式；OUTPUT：说明该参数是：说明该参数是输出输出参数参数，被调用者获取使用。，被调用者获取使用。缺缺省时表示是省时表示是输入输入参数参数。35ECUST-CS第第 7 7 章章 SQL SQL数据定义、更新及数据库编程数据定义、更新及数据库编程7.6.1 创建存储过程创建存储过程n 如果如果存储过程存储过程的的输出输出参数参数取取集合值集合值，则该，则该输出输出参数参数不在不在存储过程存储过程的的参数参数中定义中定义，而是，而是在在存储过程存储过程中定义一个中定义一个临临时表时表来存储该来存储该集合值集合值。l 临时表临时表的表名前加一个的表名前加一个#符号，

44、如符号，如#myTempl 在在存储过程存储过程尾部，使用语句：尾部，使用语句： SELECT * FROM #myTemp 将将结果集合结果集合返回给调用者返回给调用者。l 存储过程存储过程结束后，结束后，临时表临时表自动被删除自动被删除。n 注意注意：l 用户定义的用户定义的存储过程存储过程只能在当前只能在当前数据库数据库中中创建创建；l 一个一个存储过程存储过程最大不能超过最大不能超过128MB。若超过。若超过128MB，可将超出，可将超出的部分编写为另一个的部分编写为另一个存储过程存储过程，然后在，然后在存储过程存储过程中中调用调用。36ECUST-CS第第 7 7 章章 SQL SQ

45、L数据定义、更新及数据库编程数据定义、更新及数据库编程7.6.1 创建存储过程创建存储过程n 例例7.50 输入某个同学的学号输入某个同学的学号，统计该同学的，统计该同学的平均分平均分。CREATE PROCEDURE proStudentByNo1(sNo char(7)AS SELECT a.studentNo, studentName, avg(score) FROM Student a, Score b WHERE a.studentNo=b.studentNo AND a.studentNo=sNo GROUP BY a.studentNo, studentNameEXECUTE p

46、roStudentByNo1 150000337ECUST-CS第第 7 7 章章 SQL SQL数据定义、更新及数据库编程数据定义、更新及数据库编程存储过程创建，存储过程创建，openGauss下建立下建立n 通过窗体和命令行均可以建立。通过窗体和命令行均可以建立。n 其基本格式为：其基本格式为：38ECUST-CS第第 7 7 章章 SQL SQL数据定义、更新及数据库编程数据定义、更新及数据库编程7.6.1 创建存储过程创建存储过程(openGauss）n 例例7 输入某个同学的学号输入某个同学的学号，插入该学生信息，插入该学生信息create or replace procedure

47、insert_data(IN sno char(7)isbeginINSERT INTO student (studentno,studentname,sex,birthday,native,nation,classno) VALUES (sno,李志强李志强,男男 ,1999-12-21 00:00:00,北京北京,汉族汉族,CP1602);end;/CALL insert_data ( 1500003)39ECUST-CS第第 7 7 章章 SQL SQL数据定义、更新及数据库编程数据定义、更新及数据库编程7.6.1 创建存储过程创建存储过程(openGauss）n 例例7.50 输入某个

48、同学的学号输入某个同学的学号，统计该同学的，统计该同学的平均分平均分。CREATE OR REPLACE PROCEDURE proStudentByNo1( IN sNo CHAR(7)ASDECLARE temp1 char(7)BEGIN SELECT a.studentNo, studentName, avg(score) into temp1,temp2,temp3 FROM Student a, Score b WHERE a.studentNo=b.studentNo AND a.studentNo= sNo GROUP BY a.studentNo, studentName;E

49、ND;CALL proStudentByNo1( 1500003)40ECUST-CS第第 7 7 章章 SQL SQL数据定义、更新及数据库编程数据定义、更新及数据库编程7.6.1 创建存储过程创建存储过程n SQL Server数据库还可以返回一个数据库还可以返回一个数据集合数据集合l 该该数据集合数据集合在在客户端客户端的程序中可以被的程序中可以被网格类网格类的对象接收；的对象接收；l 可以对其进行可以对其进行逐行逐行处理处理；l 游标游标中可以中可以嵌套嵌套游标游标。 例例7.51 输入某同学的学号输入某同学的学号，使用，使用游标游标统计该同学的统计该同学的平均分平均分，并，并返回返回

50、平均分平均分，同时，同时逐行逐行显示显示该同学的该同学的姓名姓名、选课名称选课名称和和选课成绩选课成绩。CREATE PROCEDURE proStudentByNo2(sNo char(7), avg numeric(6, 2) OUTPUT)ASBEGIN DECLARE sName varchar(20), cName varchar(20) DECLARE score tinyint, sum int, count tinyint SELECT sum = 0, count = 041ECUST-CS第第 7 7 章章 SQL SQL数据定义、更新及数据库编程数据定义、更新及数据库编程

51、7.6.1 创建存储过程创建存储过程 - 定义定义、打开打开、获取获取游标游标 DECLARE curScore CURSOR FOR SELECT studentName, courseName, score FROM Score a, Student b, Course c WHERE b.studentNo=sNo AND a.studentNo=b.studentNo AND a.courseNo=c.courseNo OPEN curScore FETCH curScore INTO sName, cName, score WHILE (FETCH_STATUS = 0) BEGIN

52、 - 业务处理业务处理 SELECT sName, cName, score -逐行显示该同学的选课逐行显示该同学的选课信息信息 SET sum=sum+score SET count=count+1 FETCH curScore INTO sName, cName, score END42ECUST-CS第第 7 7 章章 SQL SQL数据定义、更新及数据库编程数据定义、更新及数据库编程7.6.1 创建存储过程创建存储过程 CLOSE curScore DEALLOCATE curScore IF count = 0 SELECT avg = 0 ELSE SELECT avg=sum/c

53、ountENDn 本例使用了本例使用了SELECT语句语句来显示来显示变量变量的值，即的值，即 SELECT sName, cName, scoren 由于由于存储过程存储过程仅在仅在服务器端服务器端执行，其执行，其显示的内容显示的内容只在只在服务器端服务器端出现，并不返回给出现，并不返回给客户端客户端，这样的，这样的输出结果输出结果是是没有价值的没有价值的。l 显示内容显示内容在在调试调试存储过程存储过程时有作用，一旦时有作用，一旦存储过程存储过程调试正确调试正确，使，使用用存储过程存储过程的修改命令的修改命令将将存储过程存储过程中的中的显示命令显示命令删除删除。43ECUST-CS第第 7

54、 7 章章 SQL SQL数据定义、更新及数据库编程数据定义、更新及数据库编程7.6.1 创建存储过程创建存储过程例例7.52 输入某学院名称输入某学院名称，统计，统计该学院该学院每个班级每个班级同学的同学的选课信息选课信息, 返回返回班级编号班级编号、班级名称班级名称、课程名称课程名称、课程选课人数课程选课人数、课程平均分课程平均分。n 本例使用本例使用嵌套嵌套游标游标，读者通过该例掌握，读者通过该例掌握嵌套嵌套游标游标的使用方法。的使用方法。n 分析：分析：l本例涉及两个本例涉及两个参数参数一个是一个是输入输入参数参数：学院名称学院名称，设为，设为institute；一个是一个是输出输出参

55、数参数，它为一个，它为一个集合值集合值，包含了，包含了该学院该学院所有班级所有班级的的班级编号班级编号、班级名称班级名称、课程名称课程名称、课程选课人数课程选课人数、课程平课程平均分均分；对于对于集合值集合值输出输出参数参数，在，在存储过程存储过程中定义一个中定义一个临时表临时表来存储来存储该集合该集合，设，设临时表临时表为为#myTemp在在存储过程存储过程尾部使用语句尾部使用语句“SELECT * FROM #myTemp”将将该集合该集合返回给调用者。返回给调用者。44ECUST-CS第第 7 7 章章 SQL SQL数据定义、更新及数据库编程数据定义、更新及数据库编程7.6.1 创建存

56、储过程创建存储过程l声明声明5个临时个临时变量变量，分别保存查询出来的，分别保存查询出来的班级编号班级编号classNo、班级名称班级名称className、课程名称课程名称courseName、选课人数选课人数count、选课平均分选课平均分avg。l由于由于一个一个学院学院有多个有多个班级班级，定义定义一个一个游标游标curClass，根据，根据输入输入的学院名称的学院名称，查询，查询该学院该学院所有的所有的班级编号班级编号和和班级名称班级名称。将查询出的将查询出的班级编号班级编号和和班级名称班级名称放入变量放入变量classNo、className中。中。定义定义游标游标语句为：语句为：

57、 DECLARE curClass CURSOR FOR SELECT classNo, className FROM Class WHERE institute=institute45ECUST-CS第第 7 7 章章 SQL SQL数据定义、更新及数据库编程数据定义、更新及数据库编程7.6.1 创建存储过程创建存储过程l由于由于一个一个班级班级选修了多门选修了多门课程课程，需依据查询出来的，需依据查询出来的班级号班级号classNo，按选课的，按选课的课程名课程名进行进行分组计算分组计算，统计，统计该班级该班级每每门课程门课程的的选课人数选课人数和和选课平均分选课平均分。需要需要使用第二个

58、使用第二个游标游标，将查询出来的，将查询出来的该班级该班级的的选课人数和平选课人数和平均分均分放入变量放入变量count和和avg中。中。定义定义游标游标语句为：语句为：DECLARE curCourse CURSOR FOR SELECT courseName, count(*), avg(score) FROM Student a, Score b, Course c WHERE a.studentNo=b.studentNo AND b.courseNo=c.courseNo AND classNo=classNo GROUP BY courseName注意注意：classNo变量的值是

59、从变量的值是从外外游标游标中获取的中获取的班级编号班级编号。l将查询出来的将查询出来的班级编号班级编号、班级名称班级名称、课程名称课程名称、课程选课人课程选课人数数、课程平均分课程平均分插入到插入到临时表临时表#myTemp中。中。46ECUST-CS第第 7 7 章章 SQL SQL数据定义、更新及数据库编程数据定义、更新及数据库编程7.6.1 创建存储过程创建存储过程l存储过程为：存储过程为：CREATE PROCEDURE proInstitute( institute varchar(30) )ASBEGIN DECLARE className varchar(30), courseN

60、ame varchar(30) DECLARE classNo char(6), count tinyint, avg numeric(5, 1) /* 创建创建一个一个临时表临时表，存放，存放每个班级每个班级的的班级编号班级编号、班级名称班级名称、 课程名称课程名称、课程选课人数课程选课人数、课程平均分课程平均分 */ CREATE TABLE #myTemp ( classNo char(6), className varchar(30), courseName varchar(30), classCount tinyint, classAvg numeric(5, 1) )47ECUST-CS第