实现公司管理数据系统的编程课件.ppt

1、项目五 实现公司管理数据系统的编程 终极目标：会利用变量、函数和流程控制语句编写Transact-SQL程序一、促成目标：1能正确理解和使用SQL Server变量 2能正确理解和使用SQL Server 函数 3会使用流程控制语句编写顺序结构、选择结构和循环结构的程序二、工作任务 通过使用Transact-SQL语句编程，实现公司管理数据库系统的操作。模块1 编程获取某雇员的工作业绩信息一、教学目标 1能正确理解Transact-SQL批处理 2能正确理解和使用SQL Server变量 3能正确理解和使用SQL Server 函数 4会用Transact-SQL语句编程获取对用户有用的信息二

2、、工作任务 通过Transact-SQL编程，获取名为王孔若的姓名、性别、出生年月及雇佣日期等信息；然后输出王孔若所做订单的明细表、所做的订单数目及订单总金额。（一）一）SQL Server 2000SQL Server 2000语法规则语法规则 服务器端的程序使用SQL语句来编写。一般而言，一个服务器端的程序是由以下一些成分组成：批、注释、变量、流程控制语句、错误和消息处理。1Transact-SQL批量 批量是一条或多条SQL语句构成的，它被应用程序同时发送给SQL Server执行。批结束的符号是“GO”。SQL Server从批中读取所有语句，并把它们编译成可执行的单元（执行计划），然

3、后，SQL Server就一次执行计划中的所有语句，所以可以节省系统开销。2GO 命令 为了在Transact-SQL脚本中结束批量，SQL Server用到了关键字GO。GO关键字出现在一系列语句之后，使它们能作为一个单独的批量运行。3续行 Transact-SQL语句很多情况下都写的很长。如果全部放在一行写，则不利于阅读与修改。可以将一条语句在多行中进行编写，Transact-SQL会忽略空格和行尾的换行符号。4注释 注释语句由“-”（双减号）或“/*”和“*/”表示。“-”（双减号）：用于注释单行文本。“/*”和“*/”：用于注释多行文本，“/*”和“*/”一定要配套使用，否则将会出现错

4、误。5、SELECTSELECT语句无源查询语句无源查询 SELECT语句无源查询就是最简单的语句。SELECT 常量|变量|函数|表达式 as 别名,n 所谓无源查询就是使用SELECT语句来查询不在表中的数据。无源查询实质上就是在客户机屏幕上显示出常量、变量或表达式的值。【例1】使用SELECT语句查看常量。select 我是中国人6、PRINT功能：将用户定义的消息返回客户端。语法：PRINT 字符串|变量|字符串的表达式注意：只允许使用常量、表达式或变量。不允许使用列名。【例2】使用 PRINT 语句有条件地返回消息。IF EXISTS(SELECT 产品ID FROM P_order

5、 WHERE 产品ID=4)PRINT 产品ID为4有订单！（二）SQL Server 2000变量分类：v局部变量：由用户自己创建并赋值的，也只有 创建它的用户可以使用。v全局变量：是有那些由服务器声明的、通常由服务器赋值的变量。1、局部变量局部变量局部变量局部变量用用DECLAREDECLARE语句声明，只能用在声明该变量的过语句声明，只能用在声明该变量的过程实体中，即使用范围是定义它的批、存储过程和触程实体中，即使用范围是定义它的批、存储过程和触发器等。由一个发器等。由一个 符号开始。符号开始。定义定义：DECLARE DECLARE 局部变量名局部变量名 数据类型数据类型,n,n赋值赋

6、值：SET SET 局部变量名局部变量名=表达式表达式,n,n select select 局部变量名局部变量名=表达式表达式,n,n【例例3】本模块声明了一个不超过本模块声明了一个不超过8 8个字符的字符型变量个字符的字符型变量namename的语句为：的语句为：DECLARE name varchar(8)DECLARE name varchar(8)【例4】将王孔若赋给变量name的赋值语句。DECLARE name VARCHAR(10)SET name=王孔若 PRINT 选民 +name【例5】将张立赋给变量name的赋值语句。DECLARE name VARCHAR(10)SEL

7、ECT name=张立 PRINT 选民 +name2、全局变量全局变量：由SQL Server系统提供并赋值的变量，名字由符号开始。用户不能建立全局变量，也不可能使用SET语句去修改全局变量的值。通常应该将全局变量的值赋给在同一个批中的局部变量，以便保存和处理。全局变量分为两类：与连接有关的全局变量，如：rowcount 影响的行数；与 系 统 内 部 信 息 有 关 的 全 局 变 量。如version SQL的版本号。SQL常用的全局变量表名称说明connections返回当前到本服务器的连接的数目。rowcount返回上一条T-SQL语句影响的数据行数。error返回上一条T-SQL语

8、句执行后的错误号。procid返回当前存储过程的ID号remserver返回登录记录中远程服务器的名字。spid返回当前服务器进程的ID标识。version返回当前SQL Server服务器的版本和处理器类型。language返回当前SQL Server服务器的语言。【例6】使用SELECT语句查询局部变量。declare a int,s varchar(9)set a=40set s=欢迎您select a,s【例7】使用SELECT语句查询全局变量。select version as 版本select language as 语言（三）SQL Server 2000函数 在T-SQL 编程

9、语言中函数可分系统定义函数和用户定义函数。本节介绍的是系统定义函数中最常用的数学函数、字符串函数、日期时间函数、聚合函数、系统函数、系统统计函数的最常用的部分。1日期和时间函数 对日期和时间输入值执行操作，返回一个字符串、数字或日期和时间值。名称函数现在日期时间 GETDATE()年YEAR(日期型表达式)月MONTH(日期型表达式)日DAY(日期型表达式)日期 部分DATEPART(格式串,日期型表达式)日期 加DATEADD(格式串,数值,日期)日期 差DATEDIFF(格式串,日期1,日期2)(1)GETDATE函数功能：返回当前系统日期和服务器的时间。语法：GETDATE()【例8】输

10、出当前日期：PRINT GETDATE()其输出结果如下：07 12 2006 3:06AM(2)DAY函数功能：返回某日期的日部分所代表的整数值，该函数等价于DATEPART(dd,date)。语法：DAY(date)【例9】输出指定日期的日期。PRINT DAY(08/30/1998)其输出结果如下：30(3)MONTH函数功能：返回某日期的月部分所代表的整数值。该函数等价于DATEPART(mm,date)。语法：MONTH(date)【例10】输出指定日期的月份。PRINT MONTH(08/30/2006)其输出结果如下：8(4)YEAR函数功能：返回某日期的年部分所代表的整数值。该

11、函数等价于DATEPART(yy,date)。语法：YEAR(date)【例11】输出指定日期的年份。PRINT YEAR(08/30/2006)其输出结果如下：2006（5）DATEADD函数功能：在某日期上加上一个时间间隔，从而获得一个新日期。语法：DATEADD(datepart,number,date)参数:Datepart：是指定应返回的日期部分的参数P123 日期组成部分表日期组成部分表日期部分日期部分缩写缩写Yearyy,yyyyMonthmm,mDayofyeardy,yDaydd,dWeekwk,wwWeekdaydwHourhhMinutemi,n【例12】获得指定时间后的

12、新日期 PRINT DATEADD(DY,35,11/30/2007)输出结果如下：01 4 2008 12:00AM注意：日期函数完成所有的字符串转换操作，并返回SQL Server标准日期格式的输出结果。所有这些日期函数都会自动执行该转换。(6)DATEDIFF函数功能：返回datepart指定的日期的差。语法：DATEDIFF(datepart,date1,date2)【例13】计算两个日期之间相差的天数。PRINT DATEDIFF(DY,11/30/2007,1/04/2008)结果如下：35(7)DATENAME函数功能：返回日期的字符串表示。语法：DATENAME(datepar

13、t,date)【例14】指定日期是星期几：PRINT DATENAME(DW,11/29/2007)输出结果如下：星期四 即2007年11月29日是星期四(8)DATEPART函数功能：指定datepart的整数值(注意DATEPART函数与datepart参数不同)。语法：DATEPART(datepart,date)【例15】从日期中得到月份的数值表示：PRINT DATEPART(MM,6/11/2006)输出结果：62.字符串函数 字符串函数对字符串输入值执行操作，返回一个字符串或数字值。参考：P127 常用的字符串函数名称函数ASCII码ASCII(字符表达式)字符CHAR(数字表达

14、式)字符CHAR(数字表达式)小写LOWER(字符表达式)大写UPPER(字符表达式)长度LEN(字符表达式)空格SPACE(数字表达式)名称函数截取左左字串LEFT(字符表达式,整数)截取右右字串RIGHT(字符表达式,整数)截取中间中间字串SUBSTRING(字符表达式,起始点,n)求子串位置CHARINDEX(字符表达式1,字符表达式2,开始位置)剪去左左空格LTRIM(字符表达式)剪去右右空格RTRIM(字符表达式)重复字串REPLICATE(字符表达式,n)倒置字串REVERSE(字符表达式)数值转字串STR(数字表达式)（1）LOWER函数功能：将所有字母表中存在的字符转换为小写形

15、式。所有在字母表中不存在的字符保持不变。语法：LOWER(字符串)【例16】PRINT LOWER(ABCDEF)其输出结果如下：abcdef（2）LTRIM函数 功能：删除字符串中所有的前导空格。语法：LTRIM(字符串)（3）RTRIM函数 功能：删除指定字符串末尾的所有后缀空格。语法：RTRIM(字符串)（4）REVERSE函数功能：将字符串中的所有字符逆转。语法：REVERSE(字符串)【例17】PRINT REVERSE(ABCDEF)其输出结果如下：FEDCBA （5）STR函数功能：将数值型数据转换为字符串型数据。语法：STR(数值,总长度,小数位数)“数值”是将要转换成字符串型

16、数据的数值型数据。“总长度”可以用来指定字符串的总长度。“小数位数”参数也是可选项，用来指定数值型数据小数部分的位数(默认值为10)。3.数据类型转换函数 将数据从一种数据类型转换为另一种数据类型的工作通常由SQL Server自动完成。如果要进行显示转换数据类型，需要使用两种函数：CAST()或者CONVERT()函数。（1）CAST功能：将某种数据类型的表达式显式转换为另一种数据类型。语法 CAST(表达式 AS 数据类型)【例18】将 int 列（“库存量”列）转换为varchar(20)列，以便使用 LIKE 子句。SELECT 产品名,单价,库存量FROM productWHERE

17、CAST(库存量 AS varchar(20)LIKE 1_（2）CONVERT功能：将某种数据类型的表达式显式转换为另一种数据类型。语法：CONVERT(目的数据类型目的数据类型,要转化的表达式，要转化的表达式，日期样式日期样式)【例19】print convert(char(2),month(getdate()工作任务 通过Transact-SQL编程，获取名为“王孔若”的姓名、性别、出生年月及雇佣日期等信息；然后输出王孔若所做订单的明细表、所做的订单数目及订单总金额。程序代码模块2 编程实现订购订单一、教学目标 能正确理解Transact-SQL中的流程控制语句，包括判断语句、循环语句，

18、标签、RETURN语句以及GOTO语句。工作任务 雇员王孔若签订了一条供应给鹿城中学50个优盘的订单，编程实现将订单涉及到的相关信息写入到数据库中。流程控制语句流程控制语句 流程控制语句是指那些用来控制程序执行和流程分支的命令，在SQL Server 2000中，流程控制语句主要用来控制SQL语句、语句块或者存储过程的执行流程。（一）BEGIN.END 功能：对用来将多行SQL语句组织在一起，以达到一起执行的目的。语法：BEGIN SQL语句组 END 注意：必须包含在一个单独的批处理中。（二）IF.ELSE语句语法格式：IF ELSE 注意：使用select语句作为这个条件表达式时，必须用圆

19、括号将select语句括起来。【例20】编程判断在P_order订单表中是否存在“订货日期”在2007年5月7号以后的订单，分别打印相关信息。程序清单1：IF(SELECT count(*)FROM P_order WHERE 订货日期 2007-05-07)0 PRINT 存在2007年5月7号以后的订单ELSE PRINT 不存在2007年5月7号以后的订单 程序清单2：IF exists (SELECT*FROM P_order WHERE 订货日期 2007-05-07)PRINT 存在2007年5月7号以后的订单ELSE PRINT 不存在2007年5月7号以后的订单（三）WHILE

20、循环 WHILE语句用于创建一个循环。语法：1）WHILE 2）WHILE BEGIN END 【例21】计算并输出1+2+3+100表达式的和。程序清单：DECLARE sum intDECLARE i intSELECT sum=0SELECT i=1WHILE i=10 and i=30)BEGIN IF i%3=0 BEGIN PRINT i SET i=i+1 SET n=n+1 CONTINUE END IF i=22 BREAK SET i=i+1ENDPRINT 有+convert(char(2),n)+个数能被3整除【例22】程序功能：该程序用于输出能被3整除的整数。其中用了

21、CONTINUE语句和BREAK语句。当程序执行到i为22时，直接跳出循环。故其执行结果如下：12151821有4个数能被3整除（四）GOTO语句和RETURN语句 GOTO语句用于让SQL Server跳转到SQL代码中的指定标签处，标签设计成如下形式：语法：GOTO 标签名 标签名:【例23】分析以下语句的执行结果。DECLARE sum int,x int,y intSELECT x=9,y=10SELECT sum=x+yGOTO label1SET sum=sum+100label1:PRINT x+y的值为+convert(char(3),sum)【例24】使用循环计算所给的局部变

22、量的变化值 declare r int,s int,t intselect r=2,s=3 print rwhile r 5 begin select t=100*r+s print t select r=r+2 Select s=1end（五）WAITFOR语句 功能：指定触发语句块、存储过程或事务执行的时间、时间间隔或事件。语法 WAITFOR DELAY time|TIME time 参数：DELAY：指示 一直等到指定的时间过去，最长可达 24 小时。time：要等待的时间。可以按 datetime 数据可接受的格式指定 time，也可以用局部变量指定此参数。不能指定日期。TIME：指

23、示 SQL Server 等待到指定时间。使用 WAITFOR delay【例25】等待30秒后执行select语句 Use companyinfo go WAITFOR delay 00:00:30 Select *from product使用 WAITFOR time【例26】等到11点12分后才执行select 语句Use companyinfogo WAITFOR time 11:12:00 Slect*from product（六）RETURN语句 用于无条件地终止一个查询、存储过程或者批处理，此时位于RETURN语句之后的程序将不会被执行。语法：RETURN integer_exp

24、ression 参数integer_expression为返回的整型值。存储过程可以给调用过程或应用程序返回整型值。拓展知识拓展知识 系统内置函数系统内置函数 在Transact-SQL语言中，函数被用来执行一些特殊的运算以支持SQL Server的标准命令。Transact-SQL 编程语言提供了三种函数：标量函数：标量函数用于对传递给它的一个或者多个参数值进行处理和计算，并返回一个单一的值。聚合函数：聚合函数用于对一组值执行计算并返回一个单一的值。行集函数：行集函数可以在Transact-SQL语句中当作表引用。(一一)系统函数系统函数 系统函数用于返回有关SQL Server系统、用户、

25、数据库和数据库对象的信息。系统函数可以让用户在得到信息后，使用条件语句，根据返回的信息进行不同的操作。与其它函数一样，可以在SELECT语句的SELECT和 WHERE 子句以及表达式中使用系统函数。例：select DB_NAME()as Database（二）聚合函数（二）聚合函数 聚合函数可以返回整个或者几个列或者一个列的汇总数据，它常用来计算SELECT语句查询的统计值。聚合函数经常与 SELECT 语句的 GROUP BY 子句一同使用。创建用户定义函数，它是返回值的已保存的 Transact-SQL 例程。用户定义函数不能用于执行一组修改全局数据库状态的操作。与系统函数一样，用户定

26、义函数可以从查询中唤醒调用。也可以像存储过程一样，通过 EXECUTE 语句执行。（三）用户定义函数1、用户指定义函数分类返回单值的标量函数类似于视图的可更新内嵌表函数使用代码创建结果集的多语句表函数2、创建标量函数语法：CREATE FUNCTION function_name（inputparameters）RETURNS data_type AS BEGIN function_body RETURN expressionEND【例27】阅读分析以下程序：CREATE FUNCTION dbo.multiply(a int,b int)RETURNS int asBEGIN return a*bENDGoSelect dbo.multiply(3,4)Select dbo.multiply(7,3)3、调用用户定义函数 当调用标量用户定义函数时，必须提供至少由两部分组成的名称：Select dbo.multiply(3,4)4、获得有关函数的信息 对象报告有关用户定义函数的信息：sp_help 报告有关用户定义函数的信息。例:exec sp_help multiply sp_helptext 报告用户定义函数的来源。例:exec sp_helptext multiply