[理学]附加第8章 T-SQL 编程.ppt

第8章,T-SQL编程,目标,掌握如何定义变量并赋值 掌握如何输出显示数据 掌握IF、WHILE、CASE逻辑控制语句 理解SQL中批处理的概念,使用变量,变量分为： 局部变量： 局部变量必须以标记作为前缀 ，如age 局部变量的使用也是先声明，再赋值 全局变量： 全局变量必须以标记 作为前缀，如version 全局变量由系统定义和维护，我们只能读取，不能修改全局变量的值,局部变量,例如： DECLARE name varchar(8) DECLARE seat int,声明局部变量 DECLARE 变量名 数据类型,赋值 SET 变量名 =值 SELECT 变量名 = 值,例如： SET name=张三 SELECT name=stuName FROM stuInfo WHERE stuNo=s25302,必须确保筛选出的记录只有1条,或,全局变量,全局变量都使用两个标志作为前缀,SQL Server中的函数,相当于Java语言中的内置包,字符串函数 日期函数 数学函数 系统函数,字符串函数,日期函数,数学函数,系统函数,案例分析1要求,某公司印了一批充值卡，卡的密码是随机生成的，现在出现这个问题： 卡里面的“O和0”（哦和零）“i和1”（哎和一），用户反映说看不清楚，公司决定，把存储在数据库中的密码中所有的“哦”都改成“零”，把所有的“i”都改成“1”； 请编写SQL语句实现以上要求； 数据库表名：Card；密码字段名：PassWord；,案例分析1分析,这是更新语句，需要使用UPDATE语句； 因为牵涉到字符串的替换，需要使用到SQL Server中的函数Replace；,案例分析2要求,在数据库表中有以下字符数据，如： 13-1、13-2、13-3、13-10、13-100、13-108、13-18、13-11、13-15、14-1、14-2 现在希望通过SQL语句进行排序，并且首先要按照前半部分的数字进行排序，然后再按照后半部分的数字进行排需，输出要排成这样： 13-1、13-2、13-3、13-10、13-11、13-15、13-18、13-100、13-108、14-1、14-2 数据库表名：SellRecord；字段名：ListNumber；,案例分析2分析,这是查询语句，需要使用SELECT语句 需要使用到ORDER BY进行排序，并且在ORDER BY的排序列中，也需要重新计算出排序的数字来 前半部分的数字，可以从先找到“-”符号的位置，然后，取其左半部分，最后再使用Convert函数将其转换为数字： Convert(int, Left(ListNumber, CharIndex(-, ListNumber)-1) 后半部分的数字，可以先找到“-”符号的位置，然后把从第一个位置到该位置的全部字符替换为空格，最后再使用Convert函数将其转换为数字： Convert(int, Stuff(ListNumber,1, Charindex(-, ListNumber), ),用户自定义函数,标量函数：返回单个数据值。 表值函数：返回值是一个记录集合表。在此函数中，return语句包含一条单独的select语句。 多语句表值函数：返回值是由选择的结果构成的记录集。,在SQL Server中，用户不仅可以使用标准的内置函数，也可以使用自己定义的函数来实现一些特殊的功能。 用户自定义函数可以在对象资源管理器中创建，也可以使用CREATE FUNCTION 语句创建。 在创建时需要注意：函数名在数据库中必须唯一，其可以有参数，也可以没有参数，其参数只能是输入参数，最多可以有1024参数。,1. 使用CREATE FUNCTION语句创建用户自定义函数 使用CREATE FUNCTION创建用户自定义函数，其语法格式如下： CREATE FUNCTION owner_name. function_name ( parameter_name AS scalar_parameter_data_type = default ,.n ) RETURNS scalar_return_data_type AS BEGIN function_body RETURN scalar_expression END,function_name：指用户自定义函数的名称。其名称必须符合标识符的命名规则，并且对其所有者来说，该名称在数据库中必须唯一。 parameter_name：用户自定义函数的参数，其可以是一个或多个。每个函数的参数仅用于该函数本身；相同的参数名称可以用在其它函数中。参数只能代替常量；而不能用于代替表名、列名或其它数据库对象的名称。函数执行时每个已声明参数的值必须由用户指定，除非该参数的默认值已经定义。如果函数的参数有默认值，在调用该函数时必须指定“default“关键字才能获得默认值。 scalar_parameter_data_type：参数的数据类型。 scalar_return_data_type：是用户定义函数的返回值。可以是 SQL Server 支持的任何标量数据类型（text、ntext、image 和 timestamp 除外）。 function_body：位于begin和end之间的一系列 Transact-SQL 语句，其只用于标量函数和多语句表值函数。 scalar_expression：用户自定义函数中返回值的表达式。,2.表值函数,表值函数遵循的原则： RETURNS子句仅包含关键字table。不必定义返回变量的格式，因为它由RETURN 子句中的 SELECT 语句的结果集决定。 function_body 不由BEGIN和END分隔。 RETURN子句在括号中包含单个SELECT语句。SELECT语句的结果集构成函数所返回的表。,例：在stuDB库中创建一个内嵌表值函数XUESHENG，该函数可以根据输入的系部代码返回该系学生的基本信息。其代码如下： CREATE FUNCTION XUESHENG(inputdep nvarchar(4) RETURNS table AS RETURN ( SELECT 学号, 姓名 FROM 学生 WHERE 所属院系=inputdep) GO,建立好该内嵌表值函数后，就可以象使用表或视图一样来使用它： SELECT * FROM DBO.XUESHENG(100) GO,3.多语句表值函数,多语句函数的主体中允许使用以下语句： 赋值语句 DECLARE 语句，该语句定义函数局部的数据变量和游标。 SELECT 语句，该语句包含带有表达式的选择列表，其中的表达式将值赋予函数的局部变量。 游标操作，该操作引用在函数中声明、打开、关闭和释放的局部游标。只允许使用以INTO子句向局部变量赋值的FETCH语句；不允许使用将数据返回到客户端的FETCH语句。 INSERT、UPDATE和DELETE语句，这些语句修改函数的局部table变量。 EXECUTE语句调用扩展存储过程。,3.多语句表值函数,案例：在stuDB库中创建一个多语句表值函数chengji，该函数可以根据输入的课程名称返回选修该课程的学生姓名和成绩。代码如下：,USE stuDB GO CREATE FUNCTION chengji( inputkm as char(20) ) /*为chengji 函数定义的表结构，名称变量为cj */ RETURNS cji TABLE (科目编号 varchar(10), 姓名 varchar(10), 成绩 int ) AS BEGIN INSERT cj /*该变量是上面定义的表名称变量*/ select b.科目编号,a.学生姓名,(b.笔试成绩+b.上机成绩)/2 from 学生信息表 as a inner join 成绩表 as b on a.学号=b.学号 where b.科目编号=inputkm RETURN END GO,定义返回表的结构,在查询编辑器中输入以下查询命令： SELECT * FROM dbo.chengji(9001),查看、修改和删除自定义函数,1.查看用户自定义函数的属性 在SQL Server中，根据不同需要，可以使用sp_helptext、sp_help等系统存储过程来查看用户自定义函数的不同信息。每个系统存储过程的具体作用和语法如下： 使用sp_helptext查看用户定义函数的文本信息，其语法格式为： sp_helptext 用户自定义函数名 使用sp_help查看用户自定义函数的一般信息，其语法格式为： sp_help 用户自定义函数名 例：使用有关系统过程查看STUDENT数据库中名为XUEFEN的用户自定义函数的文本信息。其程序代码如下： SP_HELPTEXT XUEFEN GO,查看、修改和删除自定义函数,2.修改用户自定义函数的属性 使用SQL命令修改用户自定义函数，使用ALTER FUNCTION 命令可以修改用户自定义函数。修改由CREATE FUNCTION 语句创建的现有用户定义函数，不会更改权限，也不影响相关的函数、存储过程或触发器。其语法格式如下： ALTER FUNCTION owner_name. function_name ( parameter_name AS scalar_parameter_data_type = default ,.n ) RETURNS scalar_return_data_type AS BEGIN function_body RETURN scalar_expression END 其中的参数与建立用户自定义函数中的参数意义相同。,查看、修改和删除自定义函数,3.使用T-SQL命令删除用户自定义函数 使用DROP命令可以一次删除多个用户自定义函数，其语法格式为： DROP FUNCTION 所有者名称.函数名称,n 案例：删除在student库上建立的xuefen函数。代码如下： DROP FUNCTION dbo.xuefen GO,逻辑控制语句,IFELSE语句,SQL中的IF-ELSE语句 IF （条件） BEGIN 语句1 语句2 END ELSE BEGIN 语句1； 语句2； END,ELSE是可选部分 如果有多条语句，才需要BEGIN-END语句块,IF-ELSE示例,问题： 统计并显示本班笔试平均分，如果平均分在70以上，显示“成绩优秀“，并显示前三名学员的考试信息；如果在70以下，显示“本班成绩较差“，并显示后三名学员的考试信息 。,学员成绩表,分析： 第一步，统计平均成绩存入临时变量； 第二步，用IFELSE判断；,IF-ELSE示例,设置输出结果的格式,为了文本消息和输出结果显示在同一窗口，需要设置输出结果的格式,逻辑控制语句,WHILE循环语句,SQL中的WHILE语句 WHILE （条件） BEGIN 语句1 语句2 BREAK END,BREAK表示退出循环 如果有多条语句，才需要BEGIN-END语句块,WHILE示例,问题： 本次考试成绩较差，假定要提分，确保每人笔试都通过。提分规则很简单，先每人都加2分，看是否都通过，如果没有全部通过，每人再加2分，再看是否都通过，如此反复提分，直到所有人都通过为止 。,学员成绩表,分析： 第一步，统计没通过的人数 ； 第二步，如果有人没通过，加分； 第三步，循环判断。,DECLARE n int WHILE(1=1) -条件永远成立 BEGIN SELECT n=COUNT(*) FROM 成绩表 WHERE writtenExam0) UPDATE 成绩表 -每人加2分 SET writtenExam=writtenExam+2 ELSE BREAK -退出循环 END print 加分后的成绩如下： SELECT * FROM 成绩表,WHILE示例,参考语句,逻辑控制语句,CASEEND多分支语句,CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 ELSE 其他结果 END,CASE-END示例,问题： 采用美国的ABCDE五级打分制来显示笔试成绩。 A级: 90分以上 B级： 8089分 C级: 7079分 D级： 6069分 E级： 60分以下,学员成绩表,print ABCDE五级显示成绩如下： SELECT 学号, 成绩= CASE WHEN writtenExam60 THEN E WHEN writtenExam BETWEEN 60 AND 69 THEN D WHEN writtenExam BETWEEN 70 AND 79 THEN C WHEN writtenExam BETWEEN 80 AND 89 THEN B ElSE A END FROM 成绩表,CASEEND示例,参考语句,CASE-END练习,练习： 请根据平均分和下面的评分规则，编写T-SQL语句查询学员的成 绩，如上图所示。 优 ：90分以上 良 ：8089分 中 ：7079分 差 ：6069分 不及格 ：60分以下,学员成绩分析,CASE-END练习答案,USE stuDB GO select a.学生姓名,a.学号,b.笔试成绩,b.上机成绩,总成绩=(b.笔试成绩+b.上机成绩)/2,等级= case when (b.笔试成绩+b.上机成绩)/260 then 不及格 when (b.笔试成绩+b.上机成绩)/2 between 60 and 69 then 及格 when (b.笔试成绩+b.上机成绩)/2 between 70 and 79 then 中 when (b.笔试成绩+b.上机成绩)/2 between 80 and 89 then 良 else 优 end from 学生信息表 as a,成绩表 as b where a.学号=b.学号,批处理语句,批处理是包含一个或多个 SQL 语句的组，从应用程序一次性地发送到SQL Server执行 SQL Server 将批处理语句编译成一个可执行单元，此单元称为执行计划。执行计划中的语句每次执行一条,客户端应用程序,SQL Server服务器,批处理语句： 语句1 语句2 GO,批处理语句示例,SELECT * FROM stuInfo SELECT * FROM stuMarks UPDATE stuMarks SET writtenExam=w