大二上学期文件-数据库王睿智chap05-1存储过程

数据库技术与应用第五章存储过程5.1

Transact-SQL语言基础5.2

存储过程(重点)5.3

事务5.4

触发器(重点)本节内容提要

Transact-SQL(简为T-SQL)语言用于管理微软SQLServer数据库引擎实例，创建和管理数据库对象，以及插入、检索、修改和删除数据等。Transact-SQL是对标准SQL语言的扩展。它在标准SQL语言的基础上添加了控制语句，是一种结构化的程序设计语言；既可在查询分析器中交互运行，也可嵌入到宿主语言中去运行。5.1

T-SQL参考文献：(v=sql.90).aspx

SQLServer中变量分两种：全局变量(@@)@@开始的标识符，以保存SQL

Server系统的某些参数值等。由系统定义和维护,用户只能引用而不能修改或定义。查看全局变量值可用SELECT语句。局部变量(@)以@开始的字母、数字、

_等字符串，不区分大小写字母。用于保存用临时数据或由存储过程返回的结果。由用户定义(DECLARE语句)和赋值(SET语句或SELECT语句)，使用范围只局限于某一个语句或过程体内；T-SQL变量声明语法：

DECLARE@variable1类型[,@variable2类型[,…]说明：声明变量后，所有变量均初始化为NULL。局部变量声明与赋值赋值语法1：SET@variable1=值|返回单值的子查询|NULL说明：不能用SET同时对多个变量进行赋值。赋值语法2：

SELECT@variable1=值1[,@variable2=值2,…][FROM…WHERE…]

说明：如果查询返回的结果包含多个值，将最后一个值赋给局部变量。SELECT语句可以直接为一个或多个局部变量赋值。例5.1

声明两个局部变量并赋值。DECLARE@mName

varchar(30),@mLenint

SET@mName='ForrestGump'--赋值

SET@mLen=142

--赋值赋值实例1或

DECLARE@mNamevarchar(30),@mLenint

SELECT@mName='ForrestGump',@mLen=142--赋值例5.2使用查询为局部变量赋值.DECLARE@rowsintSET@rows=(SELECT

COUNT(*)FROMmovies)赋值实例2或

DECLARE

@rowsint

SELECT@rows=COUNT(*)FROMmovies常用流程控制语句(一)

语句语法功能语句块BEGINSQL语句1;SQL语句2;…….END定义语句块。可嵌套IF语句IF

条件表达式

SQL语句1|语句块1ELSE

SQL语句2|语句块2定义条件以及当指定条件为TRUE或FALSE时执行的SQL语句。WHILE语句(可含BREAK、CONTINUE)WHILE

条件表达式

SQL语句1|

语句块1

[BREAK]|[CONTINUE]

SQL语句2|

语句块2

当条件为TRUE时重复语句。BREAK:退出最内层的WHILE循环。CONTINUE:重新开始WHILE循环,忽略CONTINUE关键字后面的任何语句。常用流程控制语句(二)

语句语法功能简单CASECASE

输入表达式

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

[…n]

[

ELSE结果表达式n+1]END将输入表达式的值与when中表达式值比较，若相等则计算THEN后的表达式；若与所有WHEN子句中的表达式值都不等，则计算ELSE后的表达式。最后将值返回。搜索型CASECASE

WHEN

条件表达式1THEN

结果表达式1

[…n

]

[

ELSE

结果表达式n+1]ENDRETURN语句RETURN

[整型表达式]无条件退出。CASE表达式应用例5.3实验四中的12小题,统计每门课程的选课人数及不及格人数。SELECTumAS课程号,cnameAS课程名,count(snum)AS选课人数,sum(CASEWHENscore<60THEN1ELSE0END)AS不及格人数FROMsc,sections,courseWHEREsc.secnum=sections.secnumANDum=umGROUPBYum,cname5.2存储过程

存储过程(StoredProcedure)是指封装了可重用代码的、存储在服务器端的数据库中的程序模块(modules)。

它类似传统语言(如C)中的过程。可接收0或多个输入参数；具有返回状态值(整型，默认为0)，表明被调用成功与否；并能以多输出参数的格式返回多个结果。一经创建，就可在SQL查询或宿主语言中被调用。优点：存储过程在第一次被执行时，便在服务器上被编译。以后的每次调用直接执行已编译好的二进制代码，不再重新编译。执行效率高、显著降低了网络通信量。它是数据库编程的首选模式。

分类：系统存储过程、用户存储过程、扩展存储过程。创建存储过程CREATEPROC<存储过程名>

[@parameter

类型[=默认值]

[OUTPUT]][,…n]AS

<存储过程体>说明：

存储过程参数：参数名-类型-模式@parameter：参数名；类型：除table之外的其他所有数据类型均可用作存储过程的参数类型。参数模式：OUTPUT(或OUT)表示该参数为输出参数；默认为输入参数。

<存储过程体>：表示包含在过程中的一个或多个T-SQL语句。存储过程参数例5.4一个无参数存储过程例5.4创建一个简单的存储过程，完成显示所有学生的平均分数，数据源见关系SC。CREATEPROCsp_sc_avgASSELECTsnum,AVG(score)as_avg

FROMsc

GROUPBYsnum一个存储过程可看作一个SQL语句，它可在如下3种环境中使用：在服务器端SSMS查询编辑窗口以EXECUTE为关键字调用。可在另一存储过程或函数中被调用。在宿主语言(如)中被当作嵌入式SQL被调用。(具体，见第8章课件：chap08数据库访问技术-2)存储过程的调用在服务器端SSMS中调用存储过程，其语法：说明：@return_status可选的整型变量，存储过程的返回状态。;number是可选整数，用于对同名的过程分组。注意：后续版本的MicrosoftSQLServer将删除该功能。@parameter

参数名，首字符必须为@。如果任何参数使用了@parameter=参数值格式，则后续的所有参数均必须使用该格式。@variable是用来存储参数或返回参数的变量。OUTPUT

指定存储过程返回一个参数。在服务器端调用EXEC

[@return_status=]存储过程名[;number][[@parameter=]参数值

|@variable[OUTPUT

]|[默认值]][,...n]在服务器端调用并执行例5.4中创建的存储过程。在SSMS的新建查询窗口中输入如下语句后,点击工具栏中的执行按钮“!”。DECLARE

@return_valueINTEXEC

@return_value=sp_sc_avgSELECT

'returnvalue'=@return_value服务器端执行例5.4结果集返回值执行结果例5.5

创建一个存储过程，实现如下功能：从University中查询选修指定课号的学生成绩，如果该课成绩中存在90分及其以上的学生记录，则将其姓名输出显示，并返回‘×××成绩为优的记录数为:××'消息；否则返回'×××成绩为优的记录数为零！'消息。例5.5

一个带输入参数的存储过程CREATEPROC

sp_topstudent@_cnumchar(4)AS

Declare@_iint

;

SET@_i=(Selectcount(*)FROMscjoinsectionsonsc.secnum=sections.secnumWHEREcnum=@_cnumand

score>=90);

IF@_i<>0ELSE

例5.5(续)BEGIN

RAISERROR('%s成绩为优的记录数为:%d',1,1,@_cnum,@_i);

SELECTsname

FROMstudent,sc,sections

WHEREstudent.snum=sc.snumand

sections.secnum=sc.secnumandcnum=@_cnumandscore>=90

ENDPRINT@_cnum+'成绩为优的记录数为零!

';服务器端执行例5.5在服务器端调用并执行例5.7中创建的存储过程。在SSMS的新建查询窗口中输入如下语句后,点击执行按钮“!”

DECLARE@cchar(4)SET@c='c120'EXECsp_topstudent@cEXECsp_topstudent@_cnum='c120'或参数名必须与存储过程定义中的参数名完全相同CREATEPROCsp_sc_5Levels@_secnumchar(5)ASSELECTsnumAS学号,

'五级制'

=

CASEscore/10WHEN6THEN

'及格'

WHEN7THEN

'中'WHEN8THEN

'良'WHEN9THEN'优'WHEN10THEN'优'

ELSE'不及格'ENDFROMscWHEREsecnum=@_secnum例5.6带输入参数的存储过程二例5.6

创建一个存储过程，将指定班号的学生成绩

(百分制)按优、良、中、及格和不及格五级制的形式输出，数据源为关系sc。服务器端执行例5.6在服务器端调用并执行例5.6中创建的存储过程。在SSMS的新建查询窗口中输入如下语句后,点击执行按钮“!”DECLARE@_secnumchar(5)SET@_secnum='11601'print

'班号为'+@_secnum+'的五分制成绩如下：'EXECsp_sc_5Levels@_secnum'例5.7一个带输入输出参数的存储过程例5.7创建带输出参数的存储过程，求指定课号的成绩方差，并保存在变量@_variance中，数据源为关系sc,sections。CREATEPROCsp_cnum_variance@_cnumchar(4),@_varianceREALOUTASDECLARE@_scoreint,@_iint,@_meanreal;SET@_i=0;SET@_variance=0;DECLAREscore_cursorCURSORFORSELECTscoreFROMscJOINsectionsONsc.secnum=sections.secnumWHEREcnum=@_cnum;OPENscore_cursor;FETCHNEXTFROMscore_cursorINTO@_score;WHILE(@@FETCH_STATUS=0)BEGINSET@_variance=@_variance+@_score*@_score;SET@_i=@_i+1;FETCHNEXTFROMscore_cursorINTO@_score;ENDSELECT@_mean=AVG(score)FROMscJOINsectionsONsc.secnum=sections.secnumwhereum=@_cnum;SET@_variance=@_variance/@_i-@_mean*@_meanCLOSEscore_cursorDEALLOCATEscore_cursor服务器端执行例5.7在服务器端调用并执行例5.9中创建的存储过程。在SSMS的新建查询窗口中输入如下语句后,点击执行按钮“!”DECLARE@_varianceREALEXECdbo.sp_cnum_variance'c120',@_varianceOUT;

SELECT

'cnum'='c120','variance'=@_variance存储过程的返回状态值

每个存储过程的执行，都将自动返回一个整型状态值，表明被调用存储过程的执行状态。返回状态值若为0，表示存储过程执行成功；-1~-99之间的数表示存储过程执行失败。

用户(即编程者)可在被调存储过程中，自定义返回状态值(取>0或<-99的整数)，以表示不同的执行状态。然后在调用程序中，根据返回状态值作相应的处理。自定义返回状态值例5.8

创建具有返回执行状态的存储过程。检查给定学号的学生有无不及格的记录，若无则返回0，并输出该生的成绩记录；若有则返回5；若没有提供学号，则返回15。CREATEPROCsp_sc_score_1@_snumCHAR(4)=NULLAS

IF@_snum

ISNULL

RETURN15

ELSE

IFEXISTS(SELECT

*FROM

scWHEREsnum=@_snumAND

score<60)

RETURN5

ELSE

BEGIN

SELECT

*

FROMsc

WHEREsnum=@_snum

RETURN0ENDCREATEPROCsp_sc_score_2@_snumchar(4)=nullASDECLARE@return_statusINTEXEC@return_status=sp_sc_score_1@_snum

IF@return_status=15

PRINT

'缺少输入参数！'IF@return_status=5PRINT'该生有不及格的记录！'新建一存储过程调用sp_sc_score_1服务器端执行sp_sc_score_2服务器端执行sp_sc_score_2,指定学号为s001：EXEC

sp_sc_score2

@_snum='s001'服务器端执行sp_sc_score_2,不指定学号EXECsp_sc_score服务器端执行sp_sc_score_2,指定学号为s005EXEC

@return_status=sp_sc_score's005'可以通过系统存储过程sp_helptext，来查看用户自定义的存储过程。

sp_helptext存储过程名

可以在对象资源管理器中，展开指定的数据库节点，选择【可编程性】→【存储过程】，选择目标存储过程，点击鼠标右键，从快捷菜单中选择【修改】，便可在查询设计器中查看。查看存储过程ALTERPROC存储过程名[参数列表]

AS

<存储过程体>修改存储过程DROPPROC

存储过程名删除存储过程T-SQL提供三种函数行集函数Opendatasource(providername,init_string)

例如：聚合函数标量函数：表5.9中列出的函数、数据转换函数函数SELECT*FROMOPENDATASOURCE('SQLNCLI','DataSource=wang\ruizhisqlexpress;IntegratedSecurity=SSPI')

.UNIVERSITY.dbo.student1.

字串函数：23个

常用标量函数-字符串函数函数语法功能描述举例SUBSTRING(str1,start,length)返回字符串从指定的start位置开始截取长度为length的子串。SUBSTRING(‘abced’,2,3)返回’bce’LOWER(str1)将字符串中的所有字母变成小写字母。LOWER(‘AcedBF’)返回

’acedbf’UPPER(str1)将字符串中的所有字母变成大写字母。UPPER(‘AcedBF’)返回

’ACEDBF’LEN(str1)返回字符串的字符个数，其中不包含尾随空格。LEN(‘howareyou’)返回11函数语法功能描述举例DATEPART(datepart,date)返回指定日期的指定部分的整数DATEPART(day,’05132010’)返回13DAY(date)返回指定日期中天的整数DAY(‘05/13/2010’)返回13GETDATE()返回当期系统日期和时间GETDATE()返回’0513201021:30PM’MONTH(date)返回指定日期中的月份MONTH(‘05/13/2010’)返回5YEAR(date)返回指定日期中的年份YEAR(‘05/13/2010’)返回2010DATEADD(datepart,number,date)在指定的日期上加一段时间/日期间隔，返回新的日期值DATEADD(Year,1,‘2014-1-1’)返回2015-01-01DATEDIFF(datepart,startdate,enddate)计算两个日期/时间之间的间隔，返回整数Select

DATEDIFF(YEAR,birthday,GETDATE())as年龄FromStudent时间日期函数：7个常用标量函数-日期时间函数数据类型转换函数CONVERT(data_type[(length)],expression[,style])其中参数说明：expression:待转换数据表达式.data_type:目标数据类型.Length：指定目标数据类型长度，默认值为30.style：表示将日期时间型数据转换为字符数据的样式。如，样式为101，则返回“mm/dd/yyyy”；样式为102，返回“yyyy.mm.dd”；样式11，返回“yy/mm/dd”常用标量函数-数据类型转换函数CAST(expression

ASdata_type[(length)])（续上）例用CAST()函数将表student中birthday由日期时间类型转换为字符串，要求只显示日期信息。SELECTsnum,sname,CAST(birthdayAS

CHAR(10))asbirthdayfromstudent标量函数

用户自定义函数—标量函数CREATEFUNCTION函数名(形式参数定义表)

RETURNS

返回值数据类型

[AS]BEGIN

函数体

RETURN返回值表达式

END已知SC(snum,secnum,score),Sections(secnum,cnum,pnum)创建一个标量函数，返回某课程的平均分。

例5.9自定义标量函数CREATEFUNCTIONget_cnum_avg(@_cnumchar(4))RETURNS

intASBEGIN

DECLARE@tempint

SELECT@temp=avg(score)

FROMSC,Sections

WHERESC.secnum=Sections.secnuma