10 sql server
Download
1 / 54

第 10 章 SQL Server 程序设计 - PowerPoint PPT Presentation


  • 110 Views
  • Uploaded on

第 10 章 SQL Server 程序设计. 本章要点 领会批处理、脚本、事务 学会变量的使用方法 掌握程序控制语句的特点及使用方法 学会编写简单的 SQL 程序. 10.1 程序中的批处理、脚本、注释. SQL srever 的编程语言就是 T-SQL 语言 ,是一种非过程化的语言。不论是普通 的客户 / 服务器应用程序,还是 web 应用程 序,都必须通过服务器发送 T-SQL 语言才 能实现与 SQL srever 的通信。. 一、批处理.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' 第 10 章 SQL Server 程序设计' - fritz-moran


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
10 sql server
10章 SQL Server程序设计

本章要点

  • 领会批处理、脚本、事务

  • 学会变量的使用方法

  • 掌握程序控制语句的特点及使用方法

  • 学会编写简单的SQL程序


10.1 程序中的批处理、脚本、注释

SQL srever的编程语言就是T-SQL

语言,是一种非过程化的语言。不论是普通

的客户/服务器应用程序,还是web应用程

序,都必须通过服务器发送T-SQL语言才

能实现与SQL srever的通信。


一、批处理

批处理就是一个或多个T_SQL语句,两个GO之间的SQL语句作为一个批处理。在一个批处理中可以包含一条或多条Transact-SQL语句,成为一个语句组。这样的语句组从应用程序一次性地发送到SQL Server服务器进行执行。SQL Server服务器将批处理编译成一个可执行单元,称为执行计划。


批处理的特点

1、一次发给数据库服务器的所有的SQL语句,由GO作为结束标志。

2、编译成一个可执行单元——执行计划。

3、执行时,每次执行计划中的一条语句。

注意:如果在一个批处理中,某条语句存在语法错误,SQL Server将不执行批处理中的任何语句。


二、 脚本

脚本是存储在文件中的一系列SQL 语句,是一系列按顺序提交的批处理。使用脚本可以将创建和维护数据库的操作步骤保存为磁盘文件,其扩展名为.sql。

查询分析器是建立、编辑和使用脚本的一个最好的环境。


三、 注释

在SQL Server中,有两种类型的注释字符,

用来说明程序内容的语句,不能执行且不参与程

序的编译。

单行注释(行内):

使用两个连在一起的减号“- -”作为注释符;

多行注释(块):

使用“/* */”作为注释符,用来标志多行。


例:利用查询分析器执行两个批处理,用来显示学生表中的信息及学生记录个数。例:利用查询分析器执行两个批处理,用来显示学生表中的信息及学生记录个数。

use student

go

print ‘学生表包含信息如下:’

select * from 学生

print ‘学生表记录个数为:’

SELECT COUNT(*) FROM 学生

go

--PRINT语句用于显示char、varchar类型

/*或可自动转换为字符串类型的数据*/


10 2 sql server
10.2 SQL Server例:利用查询分析器执行两个批处理,用来显示学生表中的信息及学生记录个数。变量

变量是SQL Server用来在语句之间传递数据的方式之一,是一种语言中必不可少的组成部分。Transact-SQL语言中有两种形式的变量,一种是用户自己定义的局部变量,以@字符开始;另外一种是系统提供的全局变量,以@@字符开始。


一、例:利用查询分析器执行两个批处理,用来显示学生表中的信息及学生记录个数。 全局变量

全局变量是SQL Server系统提供并赋值的变量,是SQL Server系统内部使用的变量。全局变量其作用范围并不仅仅局限于某一程序,而是任何程序均可以随时调用。全局变量通常存储一些SQL Server的配置设定值和统计数据。用户可以在程序中用全局变量来测试系统的设定值或者是Transact-SQL命令执行后的状态值。


SQL Server例:利用查询分析器执行两个批处理,用来显示学生表中的信息及学生记录个数。提供的全局变量共有33个,在使用全局变量(是一组特殊的函数)时应该注意以下几点:

  • 全局变量是在服务器级定义的。

  • 用户只能使用预先定义的全局变量。

  • 引用全局变量时,必须以标记符“@@”开头。

  • 全局变量对用户来说是只读的。

  • 局部变量的名称不能与全局变量的名称相同。


例:利用全局变量查看例:利用查询分析器执行两个批处理,用来显示学生表中的信息及学生记录个数。sql server的版本、当前所使

用的sql server服务器的名称以及所使用的服务器的

服务名称等信息。

代码如下:

print ‘目前所用sql server的版本信息’

print @@version 版本

print ’目前所用sql server服务器的名称:‘+

@@servername 服务器的名称

print ’目前所用服务器的服务名称:‘+

@@servicename 服务名称

go


二、 局部变量例:利用查询分析器执行两个批处理,用来显示学生表中的信息及学生记录个数。

变量:在程序运行过程中其值是变化的量。

局部变量是一个能够拥有特定数据类型的对象,它

的作用范围仅限制在程序内部。局部变量可以作为计

数器来计算循环执行的次数,或是控制循环执行的次

数。另外,利用局部变量还可以保存数据值,以供控

制流语句测试以及保存由存储过程返回的数据值等。

局部变量被引用时要在其名称前加上标志“@”,而且

必须先由用户自己定义和赋值后才可以使用。


局部变量的定义例:利用查询分析器执行两个批处理,用来显示学生表中的信息及学生记录个数。:

DECLARE @局部变量名 数据类型 […n]

局部变量的赋值方法 :

SET @局部变量 =表达式

或者

SELECT @局部变量=表达式 [ ,...n ]


局部变量例:利用查询分析器执行两个批处理,用来显示学生表中的信息及学生记录个数。(以@开头;使用DECLARE声明)

例:

DECLARE @var1 int,@var2 money

SELECT @var1=50,@var2=$29.95

SELECT @var1,@var2

全局变量(也称配置函数,是系统定义的变量,以@@开头;用户只能使用)

例如:@@version

@@rowcount


例:声明两个局部变量,并对它们进行赋值,例:利用查询分析器执行两个批处理,用来显示学生表中的信息及学生记录个数。

然后将变量的值显示出来。

代码如下:

declare @nowdate char(6),@disp varchar(30)

set @nowdate=getdate()

set @disp=‘现在的日期为’:

print @disp+@nowdate


10.3 例:利用查询分析器执行两个批处理,用来显示学生表中的信息及学生记录个数。程序流程控制语言

流程控制语句是指那些用来控制程序执行和流程分支的命令,在SQL Server 中,流程控制语句主要用来控制SQL语句、语句块或者存储过程的执行流程。


Begin end
一、 例:利用查询分析器执行两个批处理,用来显示学生表中的信息及学生记录个数。BEGIN…END语句

其语法形式为:

BEGIN

语句1

语句2

END

语句序列


说明:例:利用查询分析器执行两个批处理,用来显示学生表中的信息及学生记录个数。

BEGIN…END语句能够将多个Transact-SQL语句组合成一个语句块,并将它们视为一个单元处理。在条件语句和循环等控制流程语句中,当符合特定条件便要执行两个或者多个语句时,就需要使用BEGIN…END语句,将多个T-sql语句组合成一个语句块。


If else
二、 例:利用查询分析器执行两个批处理,用来显示学生表中的信息及学生记录个数。IF…ELSE语句

IF 条件表达式

语句1

ELSE

语句2


说明:例:利用查询分析器执行两个批处理,用来显示学生表中的信息及学生记录个数。

IF…ELSE语句是条件判断语句,其中,ELSE

子句是可选的,最简单的IF语句没有ELSE子句部分。

IF…ELSE语句用来判断当某一条件成立时执行某段

程序,条件不成立时执行另一段程序。SQL Server

允许嵌套使用IF…ELSE语句,而且嵌套层数没有限

制。


例:例:利用查询分析器执行两个批处理,用来显示学生表中的信息及学生记录个数。Use market

if (select 库存量 from goods

where 货品名称=‘彩电’)<30

Begin

Declare @number1 int

Set @number1=(select 库存量 from goods

where 货品名称=‘彩电’) -- 将彩电的库存量赋给内存变量

Print ‘彩电的库存量为:’+cast(@number1 as char(3))

Print ‘该商品库存量已不足,提醒进货!’

end /*cast函数用于将某种数据类型的表达式转换为另

一种数据类型。*/


三、例:利用查询分析器执行两个批处理,用来显示学生表中的信息及学生记录个数。CASE表达式

CASE表达式能够实现多重选择的计算,并将其中一个符合条件的结果表达式返回。CASE表达式按照使用形式的不同,可以分为简单CASE表达式和搜索CASE表达式。


1.例:利用查询分析器执行两个批处理,用来显示学生表中的信息及学生记录个数。简单 CASE语句

语法形式为: CASE 测试表达式

WHEN 测试值1 THEN 结果表达式1

WHEN 测试值2 THEN 结果表达式2

[ ELSE 结果表达式n+1]

END

2.搜索CASE语句

语法形式为:CASE

WHEN 表达式1 THEN 结果表达式1

WHEN 表达式2 THEN 结果表达式2

[ ELSE 结果表达式n+1]

END


使用简单例:利用查询分析器执行两个批处理,用来显示学生表中的信息及学生记录个数。CASE表达式示例

USE MARKET

Select 货品名称,供应商,库存量,状态=

CASE 状态

when 0 then ’库存无’

when 1 then ’库存有’

END

FROM goods


使用搜索例:利用查询分析器执行两个批处理,用来显示学生表中的信息及学生记录个数。CASE表达式示例:

根据商品价格判别商品档次。代码如下:

USE MARKET

Select 货品名称,供应商,价格评价=

CASE

when(价格>=5000)then’高档商品’

when(价格>=1500)then’中档商品’

when(价格>=900)then’中档商品’

when(价格>=100)then’低档商品’

ElSE’小商品’

END

FROM goods


Waitfor
四、 例:利用查询分析器执行两个批处理,用来显示学生表中的信息及学生记录个数。WAITFOR语句

WAITFOR语句用于暂时停止执行SQL语句、

语句块或者存储过程等,直到所设定的时间已过

或者所设定的时间已到才继续执行。

WAITFOR语句的语法形式为:

WAITFOR DELAY 'time' | TIME 'time'

其中:DELAY用于指定时间间隔,TIME用于指

定某一时刻,其数据类型为datetime,在datetime

数据中不允许有日期部分,即采用‘hh:mm:ss’格式。


Waitfor1
本例演示了例:利用查询分析器执行两个批处理,用来显示学生表中的信息及学生记录个数。WAITFOR语句的语法

SELECT’执行waitfor之前,秒数为:’DATEPART(SECOND,GETDATE()),’执行waitfor之前,时间为:’= GETDATE()

go

Waitfor delay’00:00:05’延时5秒

Select ’执行waifor之后,秒数为:‘=DATEPART(SEXOND,GETDATE()),’执行waitfor之后,时间为:‘=GETDATE()


五、例:利用查询分析器执行两个批处理,用来显示学生表中的信息及学生记录个数。PRINT语句

PRINT语句的作用是向用户端返回用户定义的消息。

语法格式:

PRINT ‘字符串’|局部变量|全局变量


While
六、例:利用查询分析器执行两个批处理,用来显示学生表中的信息及学生记录个数。WHILE语句

在程序中当需要多次重复处理某项工作时,就需使用WHILE循环语句。WHILE语句通过布尔表达式来设置一个循环条件,当条件为真时,重复执行一个SQL语句或语句块,否则退出循环,继续执行后面语句。While语句的语法格式为:


语法格式如下:例:利用查询分析器执行两个批处理,用来显示学生表中的信息及学生记录个数。

WHILE 布尔表达式

Begin

语句序列1

[ BREAK ]

语句序列2

[ CONTINUE ]

语句序列3

End


DECLARE @i INT,@e INT例:利用查询分析器执行两个批处理,用来显示学生表中的信息及学生记录个数。

SET @i=1

SET @e=0

WHILE @i<=100 -- 循环条件恒为真

BEGIN

IF @i%7=0 /*如果@I能够被7整除*/

begin

SET @e= @e+1

SET @i= @i+1

CONTINUE --使WHILE循环重新执行

END

IF @ i>=100 - -当i>=100时结束整个循环

BREAK

ELSE

SET @ i=@ i+1

END

PRINT’1~100之间能被7整除的数共有‘+

CONVERT(VARCHAR, @ e)+’ 个‘


Return
七、例:利用查询分析器执行两个批处理,用来显示学生表中的信息及学生记录个数。RETURN语句

RETURN语句用于无条件地终止一个查询、

存储过程或者批处理,此时位于RETURN语句之

后的程序将不会被执行。

RETURN语句的语法形式为:

RETURN 表达式


10 4 2
10.4.2例:利用查询分析器执行两个批处理,用来显示学生表中的信息及学生记录个数。用户自定义函数

在SQL Server中,用户不仅可以使用标准的

内置函数,也可以使用自己定义的函数来实现一些

特殊的功能。

在创建时需要注意:函数名在数据库中必须唯

一,其可以有参数,也可以没有参数,其参数只能

是输入参数,最多可以有1024参数。

  • 标量函数:返回单个数据值。

  • 表值函数:返回值是一个记录集合——表。在

    此函数中,return语句包含一条单独的select语句。


10 4 21
10.4.2例:利用查询分析器执行两个批处理,用来显示学生表中的信息及学生记录个数。用户自定义函数

1、使用CREATE FUNCTION语句创建用户自定义函数

语法格式如下:

CREATE FUNCTION 函数名

( [ { @变量 数据类型} [ ,...n ] ] )

RETURNS 返回值的数据类型

[ AS ]

BEGIN

语句

RETURN 返回值表达式

END


例:在例:利用查询分析器执行两个批处理,用来显示学生表中的信息及学生记录个数。studenet库中创建一个用户自定义标量值函数xuefen,该函数通过输入成绩来判断是否取得学分,当成绩大于等于60时,返回取得学分,否则,返回未取得学分。

CREATE FUNCTION xuefen(@cj int)

RETURNS nvarchar(10)

BEGIN

declare @str nvarchar(10)

if @cj >=60

set @str='取得学分'

else

set @str='未取得学分'

return @str

END

GO


使用刚才定义的例:利用查询分析器执行两个批处理,用来显示学生表中的信息及学生记录个数。xuefen函数来查看课程号为“0004”

的课程,学生获得学分的情况。

在查询编辑器中输入如下代码:

USE studenet

GO

SELECT 学号,成绩,dbo.xuefen(成绩) AS 学分情况

FROM 课程注册

WHERE 课程号='0004'

GO


2.例:利用查询分析器执行两个批处理,用来显示学生表中的信息及学生记录个数。表值函数

表值函数遵循的原则:

  • RETURNS子句仅包含关键字table。不必定义返回变量的格式,因为它由RETURN 子句中的 SELECT 语句的结果集的格式设置。

  • 语句体不由BEGIN和END分隔。

  • RETURN子句在括号中包含单个SELECT语句。SELECT语句的结果集构成函数所返回的表。


例:在例:利用查询分析器执行两个批处理,用来显示学生表中的信息及学生记录个数。STUDENT库中创建一个内嵌表值函数XSH,该函数可以根据输入的系部代码返回该系学生的基本信息。其代码如下:

CREATE FUNCTION XSH (@zymc char(4)) RETURNS table

AS

RETURN

( SELECT 学号, 姓名, 入学时间 FROM 学生 WHERE 专业=@zymc)

GO


建立好该内嵌表值函数后,就可以象使用表或视图一样来使用它:建立好该内嵌表值函数后,就可以象使用表或视图一样来使用它:

SELECT * FROM DBO.XSH (‘计算机网络')

GO


10 4 3
10.4.3建立好该内嵌表值函数后,就可以象使用表或视图一样来使用它:查看、修改和删除自定义函数

1.查看用户自定义函数的属性

语法格式为:

sp_helptext 用户自定义函数名

使用sp_help查看用户自定义函数的一般信息,其语法格式为:

sp_help 用户自定义函数名


10 4 31
10.4.3建立好该内嵌表值函数后,就可以象使用表或视图一样来使用它:查看、修改和删除自定义函数

例:使用有关系统过程查看STUDENT数据库中名为XUEFEN的用户自定义函数的文本信息。其程序代码如下:

USE STUDENT

GO

SP_HELPTEXT XUEFEN

GO


10 4 32
10.4.3 建立好该内嵌表值函数后,就可以象使用表或视图一样来使用它:查看、修改和删除自定义函数

使用T-SQL命令删除用户自定义函数

使用DROP命令可以一次删除多个用户自定义函数,其语法格式为:

DROP FUNCTION 函数名称[,…n]


10.5 建立好该内嵌表值函数后,就可以象使用表或视图一样来使用它:程序中的事务

1. 概述

1)事务

事务是最小的工作单元。这个工作单元要么成功完成所有操作,要么就是失败,并将所做的一切复原。

2) 事务特性

原子性(Atomic)

一致性(ConDemoltent)

独立性(Isolated)

持久性(Durable)


10.5 建立好该内嵌表值函数后,就可以象使用表或视图一样来使用它:程序中的事务

3)事务类型

※显式事务:显示事务是手工配置的事务。用保留字标识显式事务的开始和结束。

开始显式事务,首先输入关键词BEGIN TRAN。

结束显示事务,使用COMMIT TRAN。

取消事务,使用ROLL BACK TRAN命令。


10.6 建立好该内嵌表值函数后,就可以象使用表或视图一样来使用它:游 标

游标是处理数据的一种方法,可以看作

是一个表中的记录的指针,作用于SELECT 语

句生成的记录集,能够实现在记录集中逐行向

前或者向后访问数据。使用游标,可以在记录

集中的任意位置显示、修改和删除当前记录的

数据。

1.游标的基本操作包含5部分内容:声明游标、打开游标、提取数据、关闭游标和释放游标。


1建立好该内嵌表值函数后,就可以象使用表或视图一样来使用它:)声明游标

游标在使用之前需要声明,以建立游标。

声明游标的语法格式为:

DECLARE 游标名 CURSOR

[ LOCAL | GLOBAL ] [ FORWARD_ONLY | SCROLL ]

[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] FOR select语句

[ FOR UPDATE [ OF 列名 [ ,...n ] ] ]


2建立好该内嵌表值函数后,就可以象使用表或视图一样来使用它:)打开游标

创建游标之后,使用之前需要打开游标,才能从游标中提取数据。打开游标的语法格式为:

OPEN 游标名

游标在打开状态下,不能再被打开,也就是OPEN

命令只能打开已声明但尚未打开的游标。打开一个

游标以后,可以使用全局变量@@ERROR判断打开

操作是否成功,如果返回值为0,表示游标打开成

功,否则表示打开失败。当游标被成功打开时,游

标位置指向记录集的第一行之前。游标打开成功

后,可以使用全局变量@@CURSOR_ROWS返回

游标中的记录数。


3建立好该内嵌表值函数后,就可以象使用表或视图一样来使用它:)提取数据

游标被成功打开后,就可以使用FETCH命令

从中检索特定的数据。

提取游标中数据的语法格式为:

FETCH[[NEXT|PRIOR|FIRST|LAST|ABSOLUTE{n}|RELATIVE{n}]FROM] 游标名 [ INTO @Variable_name [ ,...n ] ]


4建立好该内嵌表值函数后,就可以象使用表或视图一样来使用它:)关闭游标

游标使用完毕后,应该关闭游标,释放当前

结果集,以便释放游标所占用的系统资源。

关闭游标语法格式为:

CLOSE 游标名

关闭游标后,系统删除了游标中所有的数据,

所以不能再从游标中提取数据。但是,可以再

使用OPEN命令重新打开游标使用。

在一个批处理中,可以多次打开和关闭游标。


5建立好该内嵌表值函数后,就可以象使用表或视图一样来使用它:)释放游标

如果一个游标确定不再使用时,可以将其删

除,彻底释放游标所占系统资源。释放游标

的语法格式为:

DEALLOCATE 游标名

释放游标即将其删除,如果想重新使用游标

就必须重新声明一个新的游标。


案例:在建立好该内嵌表值函数后,就可以象使用表或视图一样来使用它:student数据库,使用游标逐行显示“教师”表中的教师信息。代码如下:

--定义游标

DECLARE c_jsxx CURSOR KEYSET FOR SELECT 姓名,性别,学历 FROM 教师

OPEN c_jsxx --打开游标

DECLARE @xm nvarchar(8),@xb char(2),@xl nvarchar(10)

IF @@ERROR=0 --判断游标打开是否成功

BEGIN

IF @@CURSOR_ROWS>0

BEGIN

PRINT '共有教师'+RTRIM(CAST(@@CURSOR_ROWS AS CHAR(3)))+'名,分别是:'

PRINT ''


--建立好该内嵌表值函数后,就可以象使用表或视图一样来使用它:提取游标中第一条记录,将其字段内容分别存入变量中

FETCH NEXT FROM c_jsxx INTO @xm,@xb,@xl

--检测全局变量@@FETCH_STATUS,如果有记录,继续循环

WHILE (@@FETCH_STATUS=0)

BEGIN

PRINT @xm+','+@xb+','+@xl

--提取游标中下一条记录,将其字段内容分别放入变量中

FETCH NEXT FROM c_jsxx INTO @xm,@xb,@xl

END

END

END

ELSE

PRINT '游标存在问题!'

CLOSE c_jsxx --关闭游标

DEALLOCATE c_jsxx --删除游标

GO


案例:在建立好该内嵌表值函数后,就可以象使用表或视图一样来使用它:student数据库,生成一张备份课程表,使

用游标删除一门课程,修改一门课程。代码如下:

USE student

GO

SELECT * INTO 课程备份 from 课程

SELECT * FROM 课程备份

GO

--定义游标

DECLARE c_xiugai CURSOR SCROLL DYNAMIC FOR SELECT * FROM 课程备份

OPEN c_xiugai --打开游标

--提取第一条记录的内容

FETCH FIRST FROM c_xiugai

--删除第一条记录


DELETE FROM 建立好该内嵌表值函数后,就可以象使用表或视图一样来使用它:课程备份

WHERE CURRENT OF c_xiugai

--提取最后一条记录的内容

FETCH LAST FROM c_xiugai

--修改最后一条记录

UPDATE 课程备份 SET 课程名称='大学语文'

WHERE CURRENT OF c_xiugai

CLOSE c_xiugai --关闭游标

DEALLOCATE c_xiugai --删除游标

GO

SELECT * FROM 课程备份

GO


ad