SQL_Server数据库技术及应用(第五讲)——编程基础和存.ppt

《SQL_Server数据库技术及应用(第五讲)——编程基础和存.ppt》由会员分享，可在线阅读，更多相关《SQL_Server数据库技术及应用(第五讲)——编程基础和存.ppt（79页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、LOGO 第第5 5讲讲 编程基础和存储过程编程基础和存储过程LOGO常量和变量常量和变量常量：常量：字符串常量字符串常量数值常量数值常量日期常量日期常量uniqueidentifier常量常量注：引用数值常量不用单引号，引用日期、字符串常注：引用数值常量不用单引号，引用日期、字符串常量时需要加单引号量时需要加单引号LOGO常量常量字符串常量字符串常量 字符串常量字符串常量包含在单引号内，由字母数字字符（包含在单引号内，由字母数字字符（az、AZ和和09）以及特殊字符（如！、）以及特殊字符（如！、和和#）组成。）组成。如果字符串常量中包含一个单引号，如：如果字符串常量中包含一个单引号，如：Im

2、 a studentLOGO常量常量数值常量数值常量 二进制常量：二进制常量：具有前缀具有前缀0X，并且是十六进制数字字符串。，并且是十六进制数字字符串。例如例如0X12EF、0XFF。bit常量：常量：使用使用0或或1表示。表示。integer常量：常量：整数常量。整数常量。decimal常量：常量：可以包含小数点的数值常量。可以包含小数点的数值常量。float常量和常量和real常量：常量：使用科学记数法表示，例如使用科学记数法表示，例如101.5E6。money常量：常量：货币常量，以货币常量，以$作为前缀，可以包含小数作为前缀，可以包含小数点。如点。如$12.53LOGO变量变量变量：

3、变量：在程序运行过程中其值是变化的量在程序运行过程中其值是变化的量局部变量局部变量（以（以开头；使用开头；使用DECLARE语句声明）语句声明）【问题问题】DECLARE var1 int,var2 moneySELECT var1=50,var2=$29.95SELECT var1,var2 全局变量全局变量（也称配置函数，是系统定义的变量，以（也称配置函数，是系统定义的变量，以开头；用户只能使用）开头；用户只能使用）例如：例如：version rowcountLOGO练习练习2问题：问题：编写程序计算两个整数之和编写程序计算两个整数之和declare i tinyint,j tinyint

4、,sum tinyint set i=50 set j=60 select sum=i+j print sum goLOGOLOGO例题例题打印打印Course表中有多少种类型的课程，要求声明局部变量，表中有多少种类型的课程，要求声明局部变量，进行赋值，然后打印变量内容。进行赋值，然后打印变量内容。use xk go declare user_msg varchar(50)select user_msg=在在Course表中有表中有+convert(varchar(3),(select count(distinct kind)from Course)+种类型的课程。种类型的课程。print u

5、ser_msg goLOGOLOGO练习练习打印打印“计算机应用工程系计算机应用工程系”下共有多少个班级，要求声下共有多少个班级，要求声明局部变量，进行赋值，然后打印变量内容。明局部变量，进行赋值，然后打印变量内容。LOGOLOGO运算符运算符算术运算符算术运算符（+、*、/、%）一元运算符一元运算符（+、）比较运算符比较运算符（=、=、=、!=、!）逻辑运算符逻辑运算符（ALL、AND、ANY、BETWEEN、EXISTS、IN、LIKE、NOT、OR、SOME）字符串连接运算符字符串连接运算符（）（）赋值运算符赋值运算符（=）位运算符位运算符（&、|、）LOGOLOGOLOGOLOGOLO

6、GOLOGOLOGOLOGOLOGO批处理批处理大多数大多数Create命令要在单个批命令中执行，但命令要在单个批命令中执行，但Create database、Create table、Create index例外。例外。LOGO流控语句流控语句【问题问题】如何用如何用SQL语句编程实现：求语句编程实现：求1+2+3+4+100=?分析：分析：WHILE、BEGINEND、IFELSE、GOTO的使用用法的使用用法 其他流控语句的使用用法其他流控语句的使用用法BRAEK：在循环语句中，用于退出本层循环：在循环语句中，用于退出本层循环CONTINUE：在循环语句中，结束本次循环，重新转：在循环语

7、句中，结束本次循环，重新转到下一次循环条件的判断到下一次循环条件的判断LOGO流控语句流控语句If 语句语句 if 逻辑表达式逻辑表达式 sql_statement|statement_block else sql_statement|statement_blockBeginend 用来定义语句块，必须成对出现。用来定义语句块，必须成对出现。While、break和和continue While 逻辑表达式逻辑表达式 语句块语句块 功能：当逻辑表达式为真时，执行循环体，直到逻辑表达式功能：当逻辑表达式为真时，执行循环体，直到逻辑表达式为假。为假。LOGO流控语句流控语句break语句语句退出退

8、出while循环，循环，coutinue语句语句跳过语句块跳过语句块的所有其他语句，开始下一次循环。的所有其他语句，开始下一次循环。while 逻辑表达式逻辑表达式1 begin 语句语句1 if 逻辑表达式逻辑表达式2 continue 语句语句2 endLOGO流控语句流控语句declare declare语句语句用来定义局部变量，定义后的变量值为用来定义局部变量，定义后的变量值为null。局部。局部变量必须以变量必须以开始，后跟一个标识符。开始，后跟一个标识符。定义局部变量的语法如下：定义局部变量的语法如下：declare variable_name datatype ,variable

9、_name datatype select语句或语句或set语句语句给局部变量进行赋值，给局部变量进行赋值，select语句一次性语句一次性可以给多个变量赋值，可以给多个变量赋值，set语句一次只能给一个变量赋值。语句一次只能给一个变量赋值。select local_variable=expression,nLOGO流控语句流控语句goto语句语句 goto语句是无条件转移语句，语法为：语句是无条件转移语句，语法为：goto 标号标号 goto 语句将程序无条件转去执行标号所在行的语句。标号语句将程序无条件转去执行标号所在行的语句。标号通常放在一个语句的前面。标号后面加冒号（：）通常放在一个语

10、句的前面。标号后面加冒号（：）Return语句语句 Return语句实现无条件退出批命令、存储过程或触发器。语句实现无条件退出批命令、存储过程或触发器。LOGO流控语句流控语句case表达式表达式 用于计算多个条件并为每个条件返回单个值，以简化用于计算多个条件并为每个条件返回单个值，以简化SQL表达式。语法如下：表达式。语法如下：case input_expression when when_expression then result_expression n else else_result_expression endLOGO流控语句流控语句对课程进行分类统计，要求显示课程类别、课程名称

11、、对课程进行分类统计，要求显示课程类别、课程名称、报名人数并计算各类平均报名人数，按照类别和报名人报名人数并计算各类平均报名人数，按照类别和报名人数升序排列，用数升序排列，用case语句实现。语句实现。LOGO流控语句流控语句 select 课程分类课程分类=case kind when 工科工科 then 工程类课程工程类课程 when 文科文科 then 文科类课程文科类课程 when 信息信息 then 信息类课程信息类课程 when 外语外语 then 外语类课程外语类课程 when 其他其他 then 其他类课程其他类课程 end,couname as 课程名称课程名称,willnu

12、m as 报名人数报名人数 from course where willnum15 order by kind,willnum compute avg(willnum)by kind goLOGOLOGOLOGOLOGO流控语句流控语句print print语句可在屏幕上显示用户的信息以及语句可在屏幕上显示用户的信息以及char、varchar数据类型变量的内容。其他数数据类型变量的内容。其他数据类型必须先进行类型转换。据类型必须先进行类型转换。LOGOLOGOLOGO练习练习3编写程序，计算编写程序，计算n!（n=10），并将结果显示出来），并将结果显示出来LOGOLOGO存储过程概述存储过

13、程概述问题问题 要把某完成功能的要把某完成功能的SQL做成类似做成类似C语言的函数，供需要时语言的函数，供需要时调用，如何做？调用，如何做？什么是存储过程？什么是存储过程？是一组被编译在一起的是一组被编译在一起的T-SQL语句的集合，它们被集合语句的集合，它们被集合在一起以完成一个特定的任务。在一起以完成一个特定的任务。LOGO概述概述存储过程的分类存储过程的分类系统存储过程系统存储过程扩展存储过程（提供从扩展存储过程（提供从SQL Server到外部程序的接到外部程序的接口，以便进行各种维护活动）口，以便进行各种维护活动）用户自定义的存储过程用户自定义的存储过程LOGO存储过程的作用存储过程

14、的作用从以下几个方面考虑：从以下几个方面考虑：n模块化编程：模块化编程：n快速执行：快速执行：n减少网络通信量减少网络通信量n安全机制安全机制LOGO存储过程和视图的比较存储过程和视图的比较视图视图存储过程存储过程语句语句只能是只能是SELECT语句语句可以包含程序流、可以包含程序流、逻辑以及逻辑以及SELECT语句语句输入、输入、返回结果返回结果不能接受参数，只能返不能接受参数，只能返回结果集回结果集可以有输入输出参可以有输入输出参数，也可以有返回数，也可以有返回值值典型应用典型应用多个表格的连接查询多个表格的连接查询完成某个特定的较完成某个特定的较复杂的任务复杂的任务LOGO组成部分组成部

15、分存储过程的三个组成部分：存储过程的三个组成部分：所有的输入参数以及传给调用者的输出参数。所有的输入参数以及传给调用者的输出参数。被被执执行行的的针针对对数数据据库库的的操操作作语语句句，包包括括调调用用其其它它存存储储过过程程的的语句。语句。返回给调用者的状态值，以指明调用是成功还是失败。返回给调用者的状态值，以指明调用是成功还是失败。LOGO创建存储过程创建存储过程 在在SQL Server中，可以使用两种方法创建存储过程中，可以使用两种方法创建存储过程：利用利用SQL Server 企业管理器创建存储过程。企业管理器创建存储过程。使用使用Transact-SQL语句中的语句中的CREAT

16、E PROCEDURE命令创建存储过程。命令创建存储过程。LOGO使用创建向导使用创建向导例：使用创建存储过程向导创建一存储过程例：使用创建存储过程向导创建一存储过程insert_class_1，用来向，用来向class表中插入数据。表中插入数据。LOGO执行存储过程执行存储过程执行此存储过程，向执行此存储过程，向class 表中插入一行记录。表中插入一行记录。use xk go exec insert_class_1 20010009,02,生物工程（生物工程（1）班）班 goLOGOLOGO使用企业管理器使用企业管理器 例：创建一个名为例：创建一个名为p_StuByClass的存储过程，返

17、回的存储过程，返回“00电子商务电子商务”班的所有学生的信息。班的所有学生的信息。在企业管理器里选择新建存储过程命令。在企业管理器里选择新建存储过程命令。LOGOLOGOLOGOLOGO创建存储过程创建存储过程LOGO LOGO使用使用SQL语句语句 create procedure p_stubyclass as select stuno,stuname,student.classno,classname from student,class where student.classno=class.classno and classname=00电子商务电子商务 goLOGO创建带输入参数的

18、存储过程创建带输入参数的存储过程|使用使用SQL语言语言 创建格式：创建格式：create procedure proc_namepara_name datatype=default assql_statements【例例】创建一个名为创建一个名为p_StudentPara的存储过程，该的存储过程，该存储过程根据给定的班级名称显示该班级所有学生存储过程根据给定的班级名称显示该班级所有学生的信息的信息LOGOSQL语句语句 use xk go create procedure p_studentpara classname char(20)=00电子商务电子商务 as select studen

19、t.*,classname from student,class where student.classno=class.classno and classname=classname go exec p_studentpara classname=00数据库数据库 goLOGOLOGO思考思考 若执行下列语句，其结果集为什么？若执行下列语句，其结果集为什么？exec p_studentpara goLOGOLOGO多个输入参数多个输入参数带有多个输入参数的存储过程带有多个输入参数的存储过程 创建一个名为创建一个名为p_StuByPara的存储过程，根据给定的的存储过程，根据给定的 班级编码和

20、学生学号，显示相应学生的信息班级编码和学生学号，显示相应学生的信息LOGOSQL语句语句 use xk go create procedure p_studentpara classname char(20),stuno char(8)as select student.*,classname from student,class where student.classno=class.classno and classname=classname and stuno=stuno goLOGO多个输入参数多个输入参数 分别执行下列两个语句，则结果集各为什么？分别执行下列两个语句，则结果集各为什

21、么？1.use xk go exec p_studentpara classname=00数据库数据库,stuno=00000021 go2.use xk go exec p_studentpara 00数据库数据库,00000021 goLOGOLOGO LOGO带输出参数的存储过程带输出参数的存储过程定义格式同上，同时指明定义格式同上，同时指明OUTPUT关键字关键字例：创建存储过程例：创建存储过程P_ClassNum，要求能根据用户给定，要求能根据用户给定的班级代码，统计该班的人数，并将人数通过输出变量的班级代码，统计该班的人数，并将人数通过输出变量返回给用户。返回给用户。如要查询班级代

22、码如要查询班级代码20000002的学生人数，如何调用的学生人数，如何调用存储过程存储过程 LOGOSQL语句语句 创建带有输出参数的存储过程创建带有输出参数的存储过程 use xk go create procedure p_classnum classno varchar(8),classnum smallint output as set classnum=(select count(*)from student where classno=classno)print classnumLOGOLOGO SQL语句语句执行含有输出参数的存储过程执行含有输出参数的存储过程 declare c

23、lassno varchar(8),classnum smallint set classno=20000001 exec p_classnum classno,classnumLOGOLOGO练习练习【练习一练习一】创建存储过程创建存储过程Cou_credit，要求能根据用户，要求能根据用户给定的学分值，统计满足该学分值的课程数目，并把它给定的学分值，统计满足该学分值的课程数目，并把它返回给调用程序。返回给调用程序。如：要统计如：要统计2个学分的课程门数，该如何调用上述存储过个学分的课程门数，该如何调用上述存储过程。程。【练习二练习二】创建名为创建名为mod_credit的存储过程，能修改用

24、的存储过程，能修改用户指定课程号的课程学分值，修改值也由用户指定。户指定课程号的课程学分值，修改值也由用户指定。如：将课程号为如：将课程号为001的课程的学分改为的课程的学分改为5。LOGO练习一练习一 use xk go create procedure cou_credit credit decimal(3,0),counum smallint output as set counum=(select count(*)from course where credit=credit)print counumLOGO练习一练习一 declare credit decimal(3,0),coun

25、um smallint set credit=2 exec cou_credit credit,counumLOGO练习二练习二 use xk go create procedure mod_credit couno char(8),credit decimal(3,0)as update course set credit=credit where couno=couno goLOGO练习二练习二 use xk go declare couno char(8),credit decimal(3,0)set couno=001 set credit=5 exec mod_credit coun

26、o,creditLOGO修改存储过程修改存储过程修改格式：修改格式：alter procedure proc_namepara_name datatype=default,with encryptionwith recompileassql_statementsLOGO修改存储过程修改存储过程例例 使用使用Transact-SQL语句修改存储过程语句修改存储过程p_studentpara1,使其能根据用户提供的班级名称，进行模使其能根据用户提供的班级名称，进行模糊查询。糊查询。LOGOSQL语句语句alter procedure p_studentpara5classname varchar(20)asselect classname,stuno,stuname,pwdfrom student,classwhere student.classno=class.classnoand classname like%+classname+%goLOGOLOGO重命名存储过程重命名存储过程重命名：重命名：使用企业管理器使用企业管理器注意：要修改两个地方注意：要修改两个地方在企业管理器中选择要重命名的存储过程，然后在企业管理器中选择要重命名的存储过程，然后右键选择右键选择“重命名重命名”，确认。，确认。双击此存储过程，进入属性对话框双击此存储过程，进入属性对话框LOGO重命名重命名