8 数据库编程

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,Principles of Database Systems,*,数据库系统原理,Principles of Database Systems,第八章 数据库编程,上一章主要内容,数据库的设计过程,需求分析,概念结构设计,逻辑结构设计,物理设计,实施,运行维护,设计过程中往往还会有,许多反复,。,2,Principles of Database Systems,上一章主要内容,(,续,),数据库各级模式的形成,数据库的各级模式是在设计过程中逐步形成的,需求分析阶段综合各用户的应用需求（现实世界的需求）,概念设计阶段形成独立于机器特点、独立于各个,DBMS,产品的,概念模式,（信息世界模型），用,E-R,图来描述。,在逻辑设计阶段将,E-R,图转换成具体的数据库产品支持的数据模型如关系模型，形成数据库,逻辑模式,。然后根据用户处理的要求，安全性的考虑，在基本表的基础上再建立必要的视图（,VIEW,）形成数据的,外模式,。,在物理设计阶段根据,DBMS,特点和处理的需要，进行物理存储安排，设计索引，形成数据库,内模式,。,3,Principles of Database Systems,SQL,和,Transact-SQL,SQL,语言是关系型数据库的标准语言，而本章所讲的,Transact SQL,语言则是一种编程语言，与,SQL,查询语言相比，它多了许多编程的成分，比如常量和变量，系统函数和用户自定义函数，流程控制语句，,While,、,For,、,Case,语句等。,T-SQL,语言的基本成分是,语句,，由一个或多个语句可以构成一个,批处理,，由一个或多个批处理可以构成一个,查询脚本,（以,sql,作为文件扩展名）并保存到磁盘文件中，供以后需要时使用。,4,Principles of Database Systems,Transact-SQL,语言,Transact-SQL,语言是微软公司在,Microsoft SQL Server,系统中使用的语言，是对,SQL,语言的一种扩展形式。,Transact-SQL,语言是一种交互式查询语言，具有功能强大、简单易学的特点。该语言既允许用户直接查询存储在数据库中的数据，也可以把语句嵌入到某种高级程序设计语言中来使用,Transact-SQL,语言有,4,个特点：,一是一体化的特点，集数据定义语言、数据操纵语言、数据控制语言、事务管理语言和附加语言元素为一体。,二是有两种使用方式，即交互使用方式和嵌入到高级语言中的使用方式。,三是非过程化语言，只需要提出“干什么”，不需要指出“如何干”，语句的操作过程由系统自动完成。,四是，类似于人的思维习惯，容易理解和掌握。,5,Principles of Database Systems,Transact-SQL,语言的特点和执行方式,在,Microsoft SQL Server 2005,系统中，根据,Transact-SQL,语言的功能特点，可以把,Transact-SQL,语言分为,5,种类型，即数据定义语言、数据操纵语言、数据控制语言、事务管理语言和附加的语言元素。,在,Microsoft SQL Server 2005,系统中，主要使用,SQL Server Management Studio,工具来执行,Transact-SQL,语言编写的查询语句。除此之外，还可以使用,sqlcmd,实用工具来执行,Transact-SQL,语句。,6,Principles of Database Systems,第八章 数据库编程,8.1,批处理、脚本和注释,8.2 常量和变量,8.3 系统函数,8.4 流程控制语句,8.5 用户自定义函数,8.6 游标及其应用,8.7,存储过程,8.8,小结,7,Principles of Database Systems,8.1,批处理、脚本和注释,8.1.1,批处理,8.1.2,脚本,8.1.3,注释,8,Principles of Database Systems,8.1.1,批处理,批处理就是一个或多个,Transact-SQL,语句的集合，用户或应用程序一次将它发送给,SQL Server,，,由,SQL Server,编译成一个执行单元，此单元称为执行计划，执行计划中的语句每次执行一条。,9,Principles of Database Systems,8.1.1,批处理,(,续,),一些,SQL,语句不能放在同一个批处理中执行，它们需要遵循下述规则,:,1.,大多数,CREATE,命令要在单个批处理中执行，但,CREATE DATABASE,、,CREATE TABLE,、,CREATE INDEX,除外。,2.,调用存储过程时，如果它不是批处理中第一个语句，则在它前面必须加上,EXECUTE,。,3.,不能把规则和默认值绑定到用户定义的数据类型上后，在同一个批处理中使用它们。,4.,不能在给表字段定义了一个,CHECK,约束后，在同一个批处理中使用该约束。,5.,不能在修改表的字段名后，在同一个批处理中引用该新字段名。,10,Principles of Database Systems,8.1.1,批处理,(,续,),建立批处理如同编写,SQL,语句，区别在于它是多条语句同时执行的，用,GO,语句作为一个批处理的结束。,例,8-1,：利用查询分析器，查询客户购买商品的信息，新建,客户订单视图,。,11,Principles of Database Systems,8.1.1,批处理,(,续,),create view,客户订单视图,as,select a.,编号,a.,姓名,a.,电话,b.,货品编码,b.,订货日期,from,客户信息,a inner join,订单信息,b,on a.,编号,=b.,客户编号,go,select * from,客户订单视图,Go,由于,CREATE VIEW,建立视图语句不能和其他使用语句放在同一个批处理中，所以需要,GO,命令将,CREATE VIEW,语句与其下的语句,SELECT,分成两个批处理。否则,SQL Server,将报错。,12,Principles of Database Systems,8.1.1,批处理,(,续,),客户信息,订单信息,13,Principles of Database Systems,客户订单视图,8.1.1,批处理,(,续,),14,Principles of Database Systems,8.1.2,脚本,数据库应用过程中，经常需要把编写好的,SQL,语句（例如创建数据库对象、调试通过的,SQL,语句集合）保存起来，以便下一次执行同样（或类似）操作时，调用这些语句集合。这样可以省去重新 编写调试,SQL,语句的麻烦，提高工作效率。这些用于执行某项操作的,T-SQL,语句集合称为脚本。,T-SQL,脚本存储为文件，带有,sql,扩展名。,使用脚本文件对重复操作或几台计算机之间交换,SQL,语句是非常有用的。,15,Principles of Database Systems,8.1.2,脚本,(,续,),脚本是批处理的存在方式，将一个或多个批处理组织到一起就是一个脚本 。例如我们在查询分析器中执行的各个实例都可以称为一个脚本。,生成脚本有两种方法：,1.,在查询分析器中保存脚本；,2.,在企业管理器中创建数据库对象脚本。,脚本可以在查询分析器中执行，也可以在,ISQL,或,OSQL,实用程序中执行。,查询分析器是编辑、调试和使用脚本的最好环境。,16,Principles of Database Systems,8.1.3,注释,脚本文件除了含有,T-SQL,语句外，还包含有对,SQL,语句进行说明的注释。,注释是不能执行的文字字符串，或暂时禁用的部分语句。,为程序加注释不仅能使程序易懂，更有助于日后的管理和维护。,注释通常用于记录程序名、作者姓名和主要的程序更改日期，也用于描述复杂的计算或解释编程方法等。,SQL Server,支持两种形式的注释语句：,行内注释与块注释。,17,Principles of Database Systems,8.1.3,注释,(,续,),2,块注释,块注释的语法格式为：,/*,注释文本*,/,或：,/*,注释文本,*,/,1,行内注释,行内注释的语法格式为：, ,注释文本,18,Principles of Database Systems,第八章 数据库编程,8.1,批处理、脚本和注释,8.2 常量和变量,8.3 系统函数,8.4 流程控制语句,8.5 用户自定义函数,8.6 游标及其应用,8.7,存储过程,8.8,小结,19,Principles of Database Systems,8.2,常量和变量,常量和变量是程序设计中不可缺少的元素。变量又分为,局部变量,和,全局变量，,局部变量是一个能够保存特定数据类型实例的对象，是程序中各种类型数据的临时存储单元，用在批处理内,SQL,语句之间传递数据。局部变量的,作用域,，只在声明它的批处理内，一旦批处理结束，局部变量自动消失。,全局变量,是系统给定的特殊变量。,8.2.1,常量,8.2.2,局部变量,8.2.3,全局变量,20,Principles of Database Systems,8.2.1,常量,Transact-SQL,的常量主要有以下几种。,1.,字符串常量,2.,数值常量,3.,日期常量,21,Principles of Database Systems,1.,字符串常量,字符串常量包含在,单引号,之内，由字母数字（如,a-z,A-Z,0-9),及特殊符号,(!,#),组成。例如：,SQL SERVER 2000.,如果字符串常量中包含有一个单引号，可以用两个单引号表示这个字符串常量内的单引号，如 ,Toms birthday,，即可以表示为,Toms,birthday,22,Principles of Database Systems,2.,数值常量,Bit,常量,：,用,0,或,1,表示，如果是一个大于,1,的数，它将被转化为,1,。,Integer,常量,：,整数常量，不包含小数点。如,1968,。,Decimal,常量,：,可以包含小数点的数值常量。例如：,123.456,。,Float,常量,和,real,常量,：,使用科学计数法表示：,101.5E6,、,54.8E-11,等。,Money,常量,：,货币类型，可以包含小数点。,正数前加“,+”,或不加，负数前加“,-”,。例如,-123.45,$-32.5,等。,23,Principles of Database Systems,3,日期常量,使用特定格的字符日期表示，并用单引号括起来。如：,2007/11/27 18:49:07.,24,Principles of Database Systems,8.2.2,局部变量,局部变量是用户在程序中定义的变量，一次只能保存一个值，它仅在定义的批处理范围内有效。局部变量可以临时存储数值。,局部变量名总是以,符号开始，最长为,128,个字符。,使用,DECLARE,语句,声明局部变量，定义局部变量的名字、数据类型，有些还需要确定变量的长度。,25,Principles of Database Systems,8.2.2,局部变量,(,续,),局部变量的初值为,NULL,，可以使用,SELECT,或,SET,语句,对局部变量进行赋值。,SET,语句一次只能给一个局部变量赋值，而,SELECT,语句可以同时给一个或多个变量赋值。,例,8-2,：定义两个局部变量，用他们来显示当前的日期。,本例中给出了两种显示方式：,PRINT,显示在“消息”框，而,SELECT,显示在“网格”框。,declare ,todayDate,char(10),dispStr varchar(20),set ,todayDate,=,getdate,(),set ,dispStr,=,今天的日期为,:,print ,dispstr+todaydate,select ,dispstr+todaydate,26,Principles of Database Systems,8.2.2,局部变量,(,续,),例题：通过,SELECT,语句来给多个变量赋值。,declare name varchar(50),lianxiren varchar(10),address varchar(50),declare ,msgstr,varchar(80),-,变量赋值,select name=,名称,lianxiren,=,联系人,address=,地址,from,供应商信息,set ,msgstr,=,供应商名,:+name+,联系人,:+,lianxiren,+,地址,:+address,-,显示信息,select ,msgstr,Go,27,Principles of Database Systems,8.2.2,局部变量,(,续,),当返回的行数大于,1,时，仅最后一行的数据赋给变量。,Select,名称,联系人,地址,from,供应商信息 返回,6,条记录，仅最后一行赋给了变量值。如果要每一行一行地进行处理，则需要用到,游标,或,循环,的概念。,28,Principles of Database Systems,8.2.2,局部变量,(,续,),例,8-3,：,利用例,8-1,给出的“客户订单视图”和“货品视图”，将客户编号为,2,的客户订货信息显示一条消息，该消息给出客户姓名、电话、订货日期、货品名称、供应商。,declare,uName,varchar(10),uTel varchar(10),uOrder varchar(10),declare,goodN,varchar(30),company varchar(30),declare,MsgStr,varchar(80),select,uName,=a.,姓名,uTel,=a.,电话,uOrder,=a.,订货日期,goodN,=b.,货品名称,Company=b.,供应商名称,from,客户订单视图,a inner join,货品视图,b on a.,货品编码,=b.,编码,and a.,编号,=2,set,MsgStr,=,客户,+,uName,+,联系电话,+,uTel,+,于,+,uOrder,+,订购了,+company+,的,+,goodN,+.,print,MsgStr,go,29,Principles of Database Systems,客户订单视图,货品视图,30,Principles of Database Systems,两视图连接的结果：,最终显示的信息：,31,Principles of Database Systems,8.2.2,局部变量,(,续,),局部变量的作用域，只能在声明它的批处理内部。一旦批处理消失，局部变量也将自动消失。,例,8-4,：局部变量引用出错的演示。,Declare,dispstr,varchar(20),Set,dispstr,=,这是一个局部变量引用出错的演示,Go,-,批处理在这里结束，局部变量被清除。,Print,dispstr,Go,32,Principles of Database Systems,8.2.3,全局变量,全局变量是,SQL Server,系统提供并赋值的变量。,用户不能定义全局变量，也不能用,SET,语句来修改全局变量。,通常是将全局变量的值赋给局部变量，以便保存和处理。事实上，,在,SQL Server,中，全局变量是一组特定的函数，它们的名称是以,开头，,而且不需要任何参数，在调用时无需在函数名后面加上一对圆括号，这些函数也称为,无参数函数。,大部分的全局变量记录了,SQL Server,服务器的当前状态信息。,33,Principles of Database Systems,8.2.3,全局变量,(,续,),例,8-5,：利用全局变量查看,SQL Server,的版本、当前使用的语言、服务器及服务器名称。,print ,所用,SQL sever,的版本信息,print version,print ,print ,服务器名称为：,+,servername,print ,所用的语言为：,+language,print ,所用的服务为,: +,servicename,go,34,Principles of Database Systems,第八章 数据库编程,8.1,批处理、脚本和注释,8.2 常量和变量,8.3 系统函数,8.4 流程控制语句,8.5 用户自定义函数,8.6 游标及其应用,8.7,存储过程,8.8,小结,35,Principles of Database Systems,8.3,系统函数,函数对于任何程序设计语言都是非常关键的组成部分。,SQL Server,提供的函数分为以下几类：,集合函数、配置函数、游标函数、日期函数、数学函数、元数据函数、行集函数、安全函数、字符串函数、系统函数、文本与图像函数。,一些函数提供了取得信息的快捷方法。函数有值返回，值的类型取决于所使用的函数。一般来说，允许使用变量、字段或表达式的地方都可以使用函数。,有些函数以前介绍过，例如集合函数,:,（,SUM,（）、,AVG,（）、,COUNT,（）、,MAX,（）和,MIN,（）。,36,Principles of Database Systems,8.3,系统函数,(,续,),8.3.1,字符串函数,8.3.2,日期函数,8.3.3,系统综合函数,8.3.4,数学函数,8.3.6,配置函数,37,Principles of Database Systems,8.3.1,字符串函数,Space(,整型表达式,),：返回,N,个空格组成的字符串，,N,为整型表达式。,Ltrim,(,字符表达式,),：去掉字符表达式的前导空格。,Charindex,(,字符表达式,1,，字符表达式,2,，,开始位置,),：返回字符表达式,1,在字符表达式,2,的开始位置，可以从所给出的,“,开始位置,”,进行查找，如果没指定开始位置，或者指定为负数和零，则默认从字符表达式,2,的开始位置进行查找。,Replicate(,字符表达式，整型表达式,),：将字符表达式重复多次，整数表达式给出重复的次数。,38,Principles of Database Systems,8.3.1,字符串函数,(,续,),例,8-6,：给出“计算机”在“深圳现代计算机股份有限公司”中的位置。,select,charindex,(,计算机,深圳现代计算机公司,),开始位置,declare ,StrTarget,varchar(30),set ,StrTarget,=,深圳现代计算机公司,select,CHARINDEX,(,计算机, ,StrTarget,),开始,1,位置,CHARINDEX,(,计算机,深圳现代计算机公司,),开始,2,位置,go,39,Principles of Database Systems,8.3.1,字符串函数,(,续,),例,8-7,：,REPLICATE,和,SPACE,函数的练习。,SELECT,REPLICATE,(*,10),SPACE(10),REPLICATE,(,大家好！,2),space,(10),REPLICATE,(*,10),PRINT,REPLICATE,(*,10)+SPACE(10)+,REPLICATE,(,大家好！,2)+,SPACE,(10)+,REPLICATE,(*,10),GO,40,Principles of Database Systems,8.3.2,日期函数,日期函数用来显示日期和时间的信息,。它们处理,datetime,和,smalldatetime,的值，并对其进行数学运算。,日期函数,功能,Getdate,(),返回服务器当前日期和时间,Datename,(,日期元素，日期,),返回指定日期的名字，,字符串型,Datepart,(,日期元素，日期,),返回指定日期的一部分，,整数型,Datediff,(,日期元素，日期,1,，日期,2),返回两个日期间的差值并转换为指定日期元素的形式,Dateadd,(,日期元素，数值，日期,),将日期元素加上日期产生新的日期,Year(,日期,),返回年份,Month(,日期,),返回月份,day(,日期,),返回某月几号的整数值,Getutcdate,(),返回当前,UTC,时间,41,Principles of Database Systems,8.3.2,日期函数,(,续,),日期元素的缩写和取值范围,日期元素,缩写,取值,year,yy,1753-9999,month,Mm,1-12,day,Dd,1-31,hour,hh,0-23,42,Principles of Database Systems,8.3.2,日期函数,(,续,),例,8-8,：给出服务器当前的系统日期和时间，给出系统当前的月份和月份名字。,select,getdate,(),当前日期和时间,datepart,(year,getdate,(),年,datename,(year,getdate,(),年名,datepart,(month,getdate,(),月份,datename,(month,getdate,(),月份名,datepart,(day,getdate,(),日,print ,当前日期,+,datename,(year,getdate,()+,年,+,datename,(month,getdate,()+,月,+,datename,(day,getdate,()+,日,go,43,Principles of Database Systems,8.3.2,日期函数,(,续,),例,8-9,：,Mary,的生日为,1980/8/13,，请使用日期函数计算,Mary,的年龄和天数。,select,年龄,=datediff(year,1980/8/13,getdate(),天,=datediff(day,1980/8/13,getdate(),go,44,Principles of Database Systems,8.3.3,系统综合函数,在这里重点介绍两个数据类型转换函数,CAST,和,CONVERT,，在,SQL SERVER,中，有些数据类型之间会自动进行转换，有些类型之间必须显式地进行转换，而有些类型是不允许转换的。,45,Principles of Database Systems,1,CAST,函数,语法格式为：,CAST(,表达式,AS,数据类型,),：将表达式显式转化为另一种数据类型。,例,8-10,：,Mary,的生日为,1980/8/13,，请使用日期函数计算,Mary,的年龄和天数，并以消息的形式输出。,由,datediff,得到的年龄和天数均为,整数,，显示时需要进行类型转换。整数可以出现在,SELECT,语句中，但不能出现在,PRINT,语句中，后者只输出字符串。,Print,Mary,的年龄是,+cast(,datediff(year,1980/8/13,getdate(),),as char(2)+,岁，核,+cast(,datediff(day,1980/8/13,getdate(),),as char(5)+,天,Go,select,Mary,的年龄是,=datediff(year,1980/8/13,getdate(),datediff(day,1980/8/13,getdate(),Go,46,Principles of Database Systems,2,CONVERT,函数,如果希望指定类型转换后数据的样式，则应使用,CONVERT,函数进行数据类型转换。,语法格式为：,CONVERT,(,数据类型,(,长度,),表达式, style),其中的表达式是任何有效的,SQL Server,表达式，数据类型只能是,系统数据类型,，不能是用户自定义的数据类型。,47,Principles of Database Systems,2,CONVERT,函数,(,续,),STYLE,参数的典型取值,日期,Style,取值,返回字符串的日期时间格式,2,位年份,4,位年份,2 102,yy.mm.dd,返回年月日,8 108,Hh:mm:ss,只返回时间,11 111,Yy/mm/dd,120,Yy-mm-dd,hh:mm:ss,返回年月日和时间,48,Principles of Database Systems,2,CONVERT,函数,(,续,),设置日期输入格式用,SET DATEFORMAT,。它用于设置输入,DATETIME,或,SMALLDATATIME,数据的日期部分的顺序。,格式为：,SET FORMAT,格式,|,格式变量,格式的有效参数包括,mdy,（月日年）、,dmy,（日月年）、,ymd,（年月日）、,ydm,（年日月）、,myd,（月年日,),、,dym,（日年月）。美国英语默认值是,mdy,。,49,Principles of Database Systems,2,CONVERT,函数,(,续,),例,8-11,：,演示,SET DATEFORMAT,和,CONVERT,函数的使用方法。,set,dateformat,mdy,declare ,dt,datetime,set ,dt,=03.03.03 03:03:03 PM,select,默认格式,=,dt,仅有日期,=convert(varchar(30),dt,102),仅有时间,=convert(varchar(30),dt,108),仅有日期,=convert(varchar(30),dt,111),日期和时间,=convert(varchar(30),dt,120),50,Principles of Database Systems,2,CONVERT,函数,(,续,),declare ,rl,REAL,set ,rl,=2568,select,实数,6,位,=convert(varchar(40),rl,0),实数,8,位,=convert(varchar(40),rl,1),实数,16,位,=convert(varchar(40),rl,2),Declare my money,set my=9635225.36,select,货币默认,=convert(varchar(25),my,0),货币,1=convert(varchar(25),my,1),货币,2=convert(varchar(25),my,2),51,Principles of Database Systems,8.3.4,数学函数,常用的有：,CEILING,（数值表达式）：返回大于或等于数值表达式的最小整数。,FLOOR,（数值表达式）：返回小于或等于数值表达式的最大整数。是,CEILING,的反函数。,RADIANS,（数值表达式）：将度数转化为弧度。,RAND,(,整形表达式,),：返回一个,1,到,0,之间的随机十进制数。,52,Principles of Database Systems,8.3.4,数学函数,(,续,),例,8-12,：将,180,度转化为弧度，并用,CEILING,和,FLOOR,函数给出大于它的最小整数和小于它的最大整数。,select,弧度=radians(180),大于的最小整数=ceiling(radians(180),小于的最大整数=floor(radians(180),53,Principles of Database Systems,8.3.5,配置函数,配置函数给出系统当前的参数，它是全局变量的一部分。,language,:,返回当前使用语言的名称,max_connections,:,返回允许用户同时连接的最大数。,servername,:,返回运行,SQL Server,数据库服务器的名称。,version,:,返回,SQL Server,当前安装的日期、版本和处理其类型。,例,8-16,：给出,SQL Server,的最大连接数。,Select ,max_connections,Go,54,Principles of Database Systems,第八章 数据库编程,8.1,批处理、脚本和注释,8.2 常量和变量,8.3 系统函数,8.4 流程控制语句,8.5 用户自定义函数,8.6 游标及其应用,8.7,存储过程,8.8,小结,55,Principles of Database Systems,8.4,流程控制语句,流程控制语句是组织较复杂,Transact-SQL,语句的语法元素，在批处理、存储过程、脚本和特定的检索中使用。它们,包括条件控制语句、无条件转移语句和循环语句等。,56,Principles of Database Systems,8.4.1 BEGINEND,语句块,BEGIN,和,END,用来定义语句块，必须成对出现。它将多个,SQL,语句括起来，相当于一个单一语句，其语法格式如下。,BEGIN,语句,1,或语句块,1,语句,1,或语句块,1,END,57,Principles of Database Systems,8.4.2 IF.ELSE,语句,IFELSE,语句用来实现选择结构，其语法格式如下。,IF,逻辑表达式,语句,1,或语句块,1 ,ELSE,语句,2,或语句块,2 ,58,Principles of Database Systems,8.4.2 IF.ELSE,语句,(,续,),例,8-17,：由“订单信息”和“销售人员表”，给出每个销售人员的订单个数的统计。,首先建立,order,表和销售人员订单视图。,select,销售工号,订单数,=count(,销售工号,) into,ordernum,from,订单信息,group by,销售工号,go,create view,销售人员订单视图,as,select a.,工号,a.,姓名,b.,订单数,from,销售人员,a left join,ordernum,b,on a.,工号,=b.,销售工号,go,59,Principles of Database Systems,8.4.2 IF.ELSE,语句,(,续,),订单信息表,#,Ordernum,表,60,Principles of Database Systems,8.4.2 IF.ELSE,语句,(,续,),销售人员表,销售人员订订单视图,61,Principles of Database Systems,8.4.2 IF.ELSE,语句,(,续,),销售人员订订单视图,62,Principles of Database Systems,8.4.2 IF.ELSE,语句,(,续,),由“订单信息”表和“销售人员”表，给出每个销售人员的订单个数的统计。（局部变量赋值并输出，输出的对象是一张二维表，可以用,游标,简单的实现，在没有讲游标的情况下，用,WHILE,循环来实现）,63,Principles of Database Systems,8.4.2 IF.ELSE,语句,(,续,),declare ,wkno,int,set ,wkno,=1,while ,wkno,=100,begin,declare sales varchar(10),num,int,if ,wkno,in(select,工号,from,销售人员,),begin,select sales=,姓名,num=,订单数,from,销售人员订单视图,where,工号,=,wkno,if num is null,print sales+,无订单,else,print sales+,订单数,:+,cast(num,as,varchar(4),end,set ,wkno,=wkno+1,end,go,64,Principles of Database Systems,8.4.3 CASE,表达式,CASE,表达式用于简化,SQL,表达式，它可以用在任何允许使用表达式的地方。注意：,CASE,表达式不是语句，它不能单独执行，而只能作为语句的一部分来使用。,65,Principles of Database Systems,1,简单表达式,简单,CASE,表达式将一个测试表达式与一组简单表达式进行比较，如果某个简单表达式与测试表达式的值相等，则返回相应结果表达式的值。其语法格式如下。,CASE,测试表达式,WHEN,测试值,1 THEN,结果表达式,1,WHEN,测试值,2 THEN,结果表达式,2,.,ELSE,结果表达式,n,END,66,Principles of Database Systems,1,简单表达式,(,续,),例,8-18,：通过例,8-17,得到的每个销售人员的销售人员订单视图 ，给出各销售人员的业绩等级，这里只以每两个订单进行分级。,select,工号,姓名,业绩等级,=,case,订单数,/2,when 0 then ,初级,when 1 then ,中级,when 2 then ,高级,end,订单数,from,销售人员订单视图,67,Principles of Database Systems,2,搜索表达式,与简单表达式不同的是，搜索表达式中，,CASE,关键字后面不跟任何表达式，在各,WHEN,关键字后面跟的都是逻辑表达式，其语法格式如下。,CASE,WHEN,逻辑表达式,1 THEN,结果表达式,1,WHEN,逻辑表达式,2 THEN,结果表达式,2,ELSE,结果表达式,n,END,68,Principles of Database Systems,2,搜索表达式,(,续,),例,8-19,：使用简单的,CASE,语句编写。查询客户信息，通过地址中的城市名确定其所属城市。,select,姓名,城市,=,case,when,地址,like %,深圳,%,then ,深圳人,when,地址,like %,北京,%,then ,北京人,when,地址,like %,上海,%,then ,上海人,else ,其它城市人,end,电话,from,客户信息,go,69,Principles of Database Systems,8.4.4 WAITFOR,语句,可以使用,WAITFOR,语句延迟或暂停程序的执行。,语法格式如下。,WAITFOR DELAY time | TIME time ,其中，,DELAY,是指等待指定的时间间隔，最长可达,24,小时。,TIME,值等待到所指定的时间。,Waitfor,delay 00:00:10,Select * from,客户信息,Waitfor,time 20:00:00,Select * from,客户信息,70,Principles of Database Systems,8.4.5 WHILE,语句,WHILE,语句用来实现循环结构，其语法格式如下。,WHILE,逻辑表达式,语句块,当逻辑表达式为真时，执行循环体，直到逻辑表达式为假。,BREAK,语句退出,WHILE,循环，,CONTINUE,语句跳过语句块中的所有其他语句，开始下一次循环。,71,Principles of Database Systems,8.4.6,其他语句,1,RETURN,语句,RETURN,语句实现无条件退出执行的批处理命令、存储过程或触发器。,2,GOTO,语句,GOTO,语句是无条件转移语句，语法格式为：,GOTO,标号,3,RAISERROR,RAISERROR,语句通常用在错误处理中，它既可在屏幕上显示用户的信息，又可将错误号保存在,ERROR,全局变量中，以备错误处理时使用。其语法为：,RAISERROR ( ,消息标识,|,消息串, ,错误等级,状态, ,参数, ,.n ), WITH,选项, ,.n ,72,Principles of Database Systems,第八章 数据库编程,8.1,批处理、脚本和注释,8.2 常量和变量,8.3 系统函数,8.4 流程控制语句,8.5,用户自定义函数,8.6 游标及其应用,8.7,存储过程,8.8,小结,73,Principles of Database Systems,8.5,用户自定义函数,SQL Server,不仅提供了系统内置函数，还允许用户创建自己的函数。,用户定义的函数由一个或多个,T-SQL,语句组成，一般是为了方便重用而建立的。,74,Principles of Database Systems,8.5,用户自定义函数,(,续,),8.5.1,基本概念,8.5.2,创建用户自定义函数,8.5.3,修改和删除用户自定义函数,75,Principles of Database Systems,8.5.1,基本概念,用户定义函数可以有输入参数并返回值，但没有输出参数。当函数的参数有默认值时，调用该函数时必须明确指定,DEFAULT,关键字才能获取默认值。,可使用,CREATE FUNCTION,语句创建，使用,ALTER FUNCTION,语句修改，使用,DROP FUNCTION,语句删除用户定义函数。,SQL Server,支持,3,种类型的用户自定义函数：,标量函数,、,内嵌表值函数,、,多语句表值函数,。,标量函数返回在,RETURNS,子句中定义的单个函数值,内嵌表值函数和多语句表函数返回的是一个表,内嵌表值函数没有函数主体，是通过,Select,语句的结果集作为返回的表。,多语句表值函数则是通过,BEGINEND,块中定义的函数主体，有,SQL,语句生成一个临时表返回。,76,Principles of Database Systems,8.5,用户自定义函数,(,续,),8.5.1,基本概念,8.5.2,创建用户自定义函数,8.5.3,修改和删除用户自定义函数,77,Principles of Database Systems,8.5.2,创建用户自定义函数,1,建立标量函数,创建标量函数的语法格式如下。,CREATE FUNCTION,所有者名称,.,函数名称,（,参数名称,AS,标量数据类型,=,默认值,n,）,RETURNS,标量数据类型,AS,BEGIN,函数体,RETURN,标量表达式,END,78,Principles of Database Systems,8.5.2,创建用户自定义函数,(,续,),例,8-20,：,在,Marketing,数据库中，创建一个用户下订单天数的函数，该函数接受输入的订单号，通过查询“订单信息”表返回已经下订单的天数。,（等于当前日期减去订货日期）,通过查询系统对象表，如果存在这样的参数先删除,if exists (select name from,sysobjects,where name=,orderDays, and type=FN),drop function,dbo.orderDays,go,79,Principles of Database Systems,8.5.2,创建用户自定义函数,(,续,),create function,dbo.orderDays(orderno,as,int,currentdate,datetime,),returns,int,as,begin,declare ,ordDate,datetime,select ,ordDate,=,订货日期,from,订单信息,where,订单号,=,orderno,return,datediff(dd,ordDate,CurrentDate,),end,go,80,Principles of Database Systems,8.5.2,创建用户自定义函数,(,续,),select,已订天数,=dbo.orderDays(2,getdate(),Go,注意：,在用户定义的函数中不能调用不确定函数。,例如，概率函数,RAND(),或,GETDATE,（）,函数等。,81,Principles of Database Systems,8.5.2,创建用户自定义函数,(,续,),2,建立内嵌表值函数,创建内嵌表值函数的语法格式如下。,CREATE FUNCTION,所有者名称,.,函数名称,（,参数名称,AS,标量数据类型,=,默认值,n,）,RETURNS TABLE,AS,RETURN (SELECT,语句,),82,Principles of Database Systems,8.5.2,创建用户自定义函数,(,续,),例,8-21,：在,Marketing,数据库中，创建内嵌表值函数，该函数给出指定销售信息，即销售的工号作为输入参数，（,输出销售人员的信息,）。,if,exists(select,name from,sysobjects,where name=,ordercount, and type=if),drop function,dbo.ordercount,Go,83,Principles of Database Systems,8.5.2,创建用户自定义函数,(,续,),create function,dbo.ordercount(wkno,as,int,),returns table,as,return,(,select,工号,姓名,地址,电话,from,销售人员,where,工号,=,wkno,),Go,select,*,from dbo.ordercount(3),go,84,Principles of Database Systems,8.5.2,创建用户自定义函数,(,续,),3,建立多语句表值函数,创建多语句表值函数的语法格式如下。,CREATE FUNCTION,所有者名称,.,函数名称,（,参数名称,AS,标量数据类型,=,默认值,n,）,RETURNS ,表名变量,TABLE,表的定义,AS,BEGIN,函数体,RETURN,END,85,Principles of Database Systems,8.5.2,创建用户自定义函数,(,续,),例,8-22,：在,Marketing,数据库中，创建多语句表值函数，它可以查询指定部门每个销售人员的订单数，该函数接收输入的部门号，通过查询“订单信息”表返回销售订单数。,如果存在，则删除。,if,exists(select,name from,sysobjects,where name=,selesCt, and type=TF),drop function,dbo,.,selesCt,Go,86,Principles of Database Systems,8.5.2,创建用户自定义函数,(,续,),create function,dbo.salesCt(departNo,as,int,),returns ,salesDepart,TABLE(,销售工号,INT PRIMARY KEY,销售,VARCHAR,（,10,）,订单数,INT,）,as,BEGIN,DECLARE ,OrderNum,TABLE(,销售工号,INT,订单数,INT),INSERT ,OrderNum,SELECT,销售工号,订单数,=COUNT(,销售工号,),FROM,订单信息,GROUP BY,销售工号,INSERT ,salesDepart,SELECT A.,工号,A.,姓名,B.,订单数,FROM,销售人员,A JOIN ,OrderNum,B ON A.,工号,=B.,销售工号,WHERE,部门号,=,departNo,RETURN,END,Go,87,Principles of Database Systems,8.5.2,创建用户自定义函数,(,续,),使用该多语句表值函数查询部门,3,的每个销售人员的订单数：,select,部门号,=3,*,from salesCt(3),go,88,Principles of Database Systems,8.5,用户自定义函数,(,续,),8.5.1,基本概念,8.5.2,创建用户自定义函数,8.5.3,修改和删除用户自定义函数,89,Principles of Database Systems,8.5.3,修改和删除用户自定义函数,使用,ALTER FUNCTION,语句可以修改用户自定义函数，但是不能更改函数的类型，即标量值函数不能更改为表值函数，反之亦然。同样地，也不能将内嵌表值函数更改为多语句表值函数，反之亦然。,使用,DROP FUNCTION,语句删除一个或多个用户定义函数。,90,Principles of Database Sys