信息科学与技术学院-计算机系-马新娜TheoryofDatabase数据库编程1.1存储过程的类型(3)临时存储过程
属于本地存储过程。如果本地存储过程的名称前面有一个“#”，该存储过程就称为局部临时存储过程，只能在一个用户会话中
使用。如果本地存储过程的名称前有两个“##”，该过程就是全局临时存储过程，可以在所有用户会话中使用。(4)远
程存储过程远程存储过程指从远程服务器上调用的存储过程。(5)扩展存储过程在SQLServe
r2000环境之外执行的动态链接库称为扩展存储过程，其前缀是sp_。使用时需要先加载到SQLServer系统中，并且按照使用存
储过程的方法执行。1.2用户存储过程的创建与执行创建存储过程——语法格式用户存储过程的执行存储过程的几种情况——例子(
2)使用带参数的存储过程(3)使用带有通配符参数的存储过程(4)使用带OUTPUT参数的存储过程利用企业管理器编辑用
户存储过程1.3用户存储过程的修改示例1.4用户存储过程的删除2.1利用SQL命令创建触发器触发器中使用的
特殊表使用触发器的限制示例示例示例利用企业管理器创建触发器2.3触发器的修改2.3触发器的修改
2.4触发器的删除1存储过程2触发器(1)系统存储过程系统存储过程是由系统提供的存储过程，
可以作为命令执行各种操作。系统存储过程定义在系统数据库master中，其前缀是sp_。系统存储过程允许系统管理员执行修改
系统表的数据库管理任务，可以在任何一个数据库中执行。(2)本地存储过程在用户数据库中创建的存储过程，完成特定数据
库操作任务，其名称不能以sp_为前缀。用户存储过程只能定义在当前数据库中，可以使用SQL命令语句或SQ
LServer的企业管理器创建存储过程。缺省情况下，用户创建的存储过程归数据库所有者拥有，数据库的所有者可以把许可授权给其他用户
。CREATEPROC[EDURE]procedure_name[;number]/定
义过程名/[{@parameterdata_type} /定义参数类型
/[VARYING][=default][OUTPUT]?] /定义参数属性/
[WITH??{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}
] /定义存储过程的处理方式/[FORREPLICATION]ASs
ql_statement /执行的操作/说明：1、参数procedure_name用于指定存储过程名；创建局部
临时过程，前加“#”；创建全局临时过程，前加“##”。2、参数number为可选的整数，用于区分同名的存储过程，以便用一条D
ROPPROCEDURE语句删除一组存储过程；3、FORREPLICATION用于说明不能在订阅服务器上执行为复制创建的存
储过程，该选项不能和WITHRECOMPILE一起使用。对于存储过程要注意下列几点：(1)用户定义的存储过程只能在当前数
据库中创建。(2)成功执行CREATEPROCEDURE语句后，过程名称存储在sysobjects系统表中，而CR
EATEPROCEDURE语句的文本存储在syscomments中。(3)自动执行存储过程:SQLServer启动
时可以自动执行一个或多个存储过程。这些存储过程必须由系统管理员在master数据库中创建，并在sysadmin固定服务器角
色下作为后台过程执行。这些过程不能有任何输入参数。(4)sql_statement的限制:除了SETSHOWPLAN_T
EXT和SETSHOWPLAN_ALL外，其它SET语句均可在存储过程内使用。通过EXEC命令可以执行一个已定义的存储
过程。语法格式：[EXEC[UTE]]?{??[@return_status=]{procedure_n
ame[;number]|@procedure_name_var}[[@parameter=]{val
ue|@variable[OUTPUT]|[DEFAULT]}[WITHRECOMPILE]｝说明
：1、@return_status为可选的整型变量，保存存储过程的返回状态，2、procedure_name和number用
于调用一组存储过程中的某一个。3、@procedure_name_var代表存储过程名。4、@parameter为CREATE
PROCEDURE语句中定义的参数名；value为存储过程的实参；@variable为变量，用于保存OUTPUT参数返回的值
。5、DEFAULT关键字表示不提供实参，而是使用对应的默认值。6、关键字WITHRECOMPILE指定强制编译。存储过
程的执行要注意下列几点：(1)如果存储过程名的前三个字符为sp_，SQLServer会在Master数据库中寻找该过
程。如果没能找到合法的过程名，SQLServer会寻找所有者名称为dbo的过程。(2)参数可以通过value或@
parameter_name=value提供。(3)执行存储过程时，若语句是批处理中的第一个语句，则不一定要指定EXE
CUTE关键字。(1)不使用任何参数的存储过程【例1】从XSCJ数据库的三个表中查询，返回学生学号、姓名、课程名、成绩、学
分。该存储过程不使用任何参数。CREATEPROCEDUREstudent_infoAS
SELECTa.学号,姓名,课程名,成绩,t.学分FROMXSaINNERJOINXS_K
Cb ONa.学号=b.学号INNERJOINKCt ONb.课程号=t.课程
号student_info存储过程可以通过以下方法执行：EXECUTEstudent_info或者
EXECstudent_info如果该过程是批处理中的第一条语句，则可使用：student_info【例2
】从XSCJ数据库的三个表中查询某人指定课程的成绩和学分。该存储过程接受与传递参数精确匹配的值。CREATEPROCEDUR
Estudent_info1@namechar(8),@cnamechar(16)AS
SELECTa.学号,姓名,课程名,成绩,学分FROMXSaINNERJOINXS_KCb
ONa.学号=b.学号INNERJOINKCtONb.课程号=t.课程号WHERE
a.姓名=@nameandt.课程名=@cnamestudent_info1存储过程有多种执行方式，下面列出了一部分：
EXECUTEstudent_info1''王林'',’计算机基础’或者EXECUTEstudent_info1@
name=''王林'',@cname=计算机基础’或者EXECUTEstudent_info1@cname=’计算机基础’,
@name=''王林''【例3】从三个表的联接中返回指定学生的学号、姓名、所选课程名称及该课程的成绩。该存储过程在
参数中使用了模式匹配，如果没有提供参数，则使用预设的默认值。CREATEPROCEDUREst_info@
namevarchar(30)=''刘%''ASSELECTa.学号,a.姓名,c.
课程名,b.成绩 FROMXSaINNERJOINXS_KCb ONa.学号=b.学号INNERJO
INKCc ONc.课程号=b.课程号 WHERE姓名LIKE@namest_info存储过程可以有
多种执行形式，下面列出了一部分：EXECUTEst_info/参数使用默认值/或者
EXECUTEst_info''王%''/传递给@name的实参为''王%''/【例4】用于计算指定学生的总学分，
存储过程中使用了一个输入参数和一个输出参数。CREATEPROCEDUREtotalcredit@namevarcha
r(40), @totalintOUTPUTASSELECT@total=
SUM(KC.学分) FROMXS,XS_KC,KC WHERE姓名=@nameANDXS.学号=XS_KC.学号
GROUPBYXS.学号注意：?OUTPUT变量必须在创建表和使用该变量时都进行定义。定义时的
参数名和调用时的变量名不一定要匹配，不过数据类型和参数位置必须匹。DECLARE@t_creditchar
(20),@totalintEXECUTEtotalcredit''王林'',@totalOUTPUT
如果要通过企业管理器界面定义一个存储过程查询XSCJ数据库中每个同学各门功课的成绩，
步骤如下：第1步在SQLServer企业管理器窗口中，选择相应的服务器、数据库和存储过程图标（本例选择XS
CJ数据库），单击鼠标右键，出现如图1所示的快捷菜单。第2步选择新建存储过程，则出现编辑存储
过程的属性窗口，在窗口中输入定义的存储过程，如图2所示，然后选择“确定”按钮。使用ALTERPROCEDURE
命令可修改已存在的存储过程并保留以前赋予的许可。语法格式：ALTERPROC[EDURE]pro
cedure_name[;number][{@parameterdata_type}
[VARYING][0=default][OUTPUT]][WITH??{R
ECOMPILE|ENCRYPTION??|RECOMPILE,ENCRYPTION?}][FOR
REPLICATION]AS??sql_statement说明
：各参数含义与CREATEPROCEDURE相同。用ALTERPROCEDURE更改后，
过程的权限和启动属性保持不变。【例8】对存储过程student_info1进行修改。USEX
SCJGOALTERPROCEDUREstudent_info1 @name
char(8),@cnamechar(16)ASSELECTa.学号,姓名,
课程名,成绩,t.学分 FROMXSaINNERjoinXS_KCb ONa.学号=b.学
号INNERJOINKCt ONb.课程号=t.课程号 WHEREa.姓名=@nameand
t.课程名=@cnameGO【例9】创建名为select_students的存储过程，默认情况
下，该过程可查询所有学生信息，随后授予权限。当该过程需更改为能检索计算机专业的学生信息时，用ALTERPROCEDURE重新
定义该存储过程。USEXSCJGOCREATEPROCEDUREselect_students /创建存储过程
/ASSELECTFROMXSORDERBY学号GO修改存储过程select_stude
ntsALTERPROCEDUREselect_studentsWITHENCRYPTIONASSELECT
FROMXSWHERE专业名=''计算机''ORDERBY学号GO语法格式
：DROPPROCEDURE{procedure}[,...n]说明：
procedure指要删除的存储过程或存储过程组的名称；n：表示可以指定多个存储过程同时删除。【例10】删除XSCJ
数据库中的student_info1存储过程。USEXSCJGODROP
PROCEDUREstudent_info1CREATETRIGGERtrigger_nameON{table
|view} /指定操作对象/[WITHENCRYPTION] /说明是否采用加密方式/{{{
FOR|AFTER|INSTEADOF}{[INSERT][,][UPDATE]}??[WIT
HAPPEND]????[NOTFORREPLICATION] /
说明该触发器不用于复制/?????AS?????[{IFUPDATE(column)???????????
?[{AND|OR}UPDATE(column)]????????????????|IF(COLUMN
S_UPDATED(){bitwise_operator}updated_bitmask)????????????
????{comparison_operator}column_bitmask[...n]?????}]
/IF子句用于说明触发器执行的条件/????????sql_statement[...n] /一条或若干条SQL
语句/?}}bitwise_operator为用于比较运算的位运算符。updated_bitmask为整型
的位屏蔽码，与实际更新或插入的列对应。comparison_operator为比较运算符；column_bitm
ask为列屏蔽码，用来检查是否已更新或插入了对应列。参数sql_statement为触发器的T-SQL语句，当执
行DELETE、INSERT或UPDATE操作时，对应的触发器操作将生效。n表示触发器中可以包含多条T-SQL语句。
说明执行触发器时，系统创建了两个特殊的逻辑表：inserted逻辑表：当向表中插入数据时，INS
ERT触发器触发执行，新的记录插入到触发器表和inserted表中。deleted逻辑表：用于保存已从表中删除的记录
，当触发一个DELETE触发器时，被删除的记录存放到deleted逻辑表中。修改一条记录等于插入一新记录，同时删除旧记录。当对定
义了UPDATE触发器的表记录修改时，表中原记录移到deleted表中，修改过的记录插入到inserted表中。触发器可检查del
eted表、inserted表及被修改的表。(1)CREATETRIGGER必须是批处理中第一条语句，只
能应用到一个表中(2)触发器只能在当前数据库中创建，但可引用当前数据库的外部对象。(3)如果指定触发器所有者名限定触发
器，要以相同的方式限定表名。(4)同一CREATETRIGGER语句中，可为多种操作定义相同的触发器操作(5)如果一
个表的外键在DELETE、UPDATE操作上定义了级联，则不能在该表上定义INSTEADOFDELETE、INSTEAD
OFUPDATE触发器。(6)在触发器内可以指定任意的SET语句，所选择的SET选项在触发器执行期间有效，并
在触发器执行完后恢复到以前的设置。(7)触发器中不允许包含以下T-SQL语句：CREATEDATABASE、ALT
ERDATABASE、LOADDATABASE、RESTOREDATABASE、DROPDATABASE、LOAD
LOG、RESTORELOG、DISKINIT、DISKRESIZE和RECONFIGURE(8)触发器不能返回任
何结果。【例11】对于XSCJ数据库，如果在XS表中添加或更改数据，则将向客户端显示一条信息。
/使用带有提示消息的触发器/USEXSCJGO
CREATETRIGGERreminderONXS FORINSERT,UPDATEASR
AISERROR(4008,16,10)GO消息4008是sysmessages
中的用户定义消息。【例12】在数据库XSCJ中创建一触发器，当向XS_KC表插入一记录时，检查该记录的学号在XS
表是否存在，检查课程号在KC表中是否存在，若有一项为否，则不允许插入。USEXSCJGO
CREATETRIGGERcheck_trig ONXS_KC FORINSERT
ASSELECTFROMinserteda WHEREa.学号NOTIN(S
ELECTb.学号FROMXSb)OR a.课程号NOTIN(SELECT
c.课程号FROMKCc)BEGIN RAISERROR(''违背数据的一致性.'',16,1)
ROLLBACKTRANSACTIONEND【例13】在XSCJ数据库的XS_KC表
上创建一触发器，若对学号列和课程号列修改，则给出提示信息，并取消修改操作。通过调用COLUMNS_UPDATE
D()函数，可快速测试对学号列和课程号列修改所做的更改。USEXSCJGO
CREATETRIGGERupdate_trig ONXS_KC FORupdateAS
IF(COLUMNS_UPDATED()&3)>0 BEGIN RAISERROR(''
违背数据的一致性.'',16,1) ROLLBACKTRANSACTION END
GO第1步展开服务器组，然后展开服务器。第2步展开“数据库”，定义触发器的表所属的数据
库，单击“表”文件夹。第3步在其上创建触发器的表右击，选择“所有任务”下的“管理触发器”第4步
在“名称”中，单击“新建”，在“文本”框中输入触发器文本。若要检查语法，单击“检查语法”命令。1．利用SQL命令修改触发器：
语法格式：ALTERTRIGGERtrigger_nameON(table|view)
[WITHENCRYPTION]{?{(FOR|AFTER|INSTEADOF){[DELETE][,][INSERT][,][UPDATE]}????????[NOTFORREPLICATION]????????AS????????sql_statement????}??|{(FOR|AFTER|INSTEADOF){[INSERT][,][UPDATE]}????????[NOTFORREPLICATION]????????AS????????{IFUPDATE(column)????????[{AND|OR}UPDATE(column)]????????????????|IF(COLUMNS_UPDATED(){bitwise_operator}updated_bitmask)????????{comparison_operator}column_bitmask[...n]????}????????sql_statement[...n]????} |
|
