存储过程和触发器Word格式文档下载.docx
《存储过程和触发器Word格式文档下载.docx》由会员分享，可在线阅读，更多相关《存储过程和触发器Word格式文档下载.docx（15页珍藏版）》请在冰豆网上搜索。
（1）存储过程在服务器端运行，执行速度快。
（2）存储过程执行一次后，其执行规划就驻留在高速缓冲存储器，在以后的操作中，只需从高速缓冲存储器中调用已编译好的二进制代码执行，提高了系统性能。
（3）确保数据库的安全。
使用存储过程可以完成所有数据库操作，并可通过编程方式控制上述操作对数据库信息访问的权限。
（4）自动完成需要预先执行的任务。
存储过程可以在系统启动时自动执行，而不必在系统启动后再进行手工操作，大大方便了用户的使用，可以自动完成一些需要预先执行的任务。
调用存储过程
EXECstudent_gradeGO
通过上例了解了存储过程的使用，下面介绍创建和执行存储过程的语法格式。
1）创建存储过程
语法格式:
CREATEPROC[EDURE]procedure_name[;
number]/*定义过程名*/
[{@parameterdata_type}/*定义参数的类型*/
[VARYING][=default][OUTPUT]
]/*定义参数的属性*/
[,...n1]
[WITH
{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]
/*定义存储过程的处理方式*/
[FORREPLICATION]
ASsql_statement[...n2]/*执行的操作*/
对于存储过程要注意下列几点：
（1）用户定义的存储过程只能在当前数据库中创建（临时过程除外，临时过程总是在tempdb中创建）。
（2）成功执行CREATEPROCEDURE语句后，过程名称存储在sysobjects系统表中，而CREATEPROCEDURE语句的文本存储在syscomments中。
（3）自动执行存储过程
SQLServer启动时可以自动执行一个或多个存储过程。
这些存储过程必须由系统管理员在master数据库中创建，并在sysadmin固定服务器角色下作为后台过程执行。
（4）sql_statement的限制
除了SETSHOWPLAN_TEXT和SETSHOWPLAN_ALL外，其它SET语句均可在存储过程内使用。
（5）权限.CREATEPROCEDURE的权限默认授予sysadmin固定服务器角色成员,db_owner和db_ddladmin固定数据库角色成员.
2）存储过程的执行
通过EXEC命令可以执行一个已定义的存储过程。
语法格式：
[EXEC[UTE]]
{
[@return_status=]
{procedure_name[;
number]|@procedure_name_var}
[[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]}
[,...n]
[WITHRECOMPILE]｝
存储过程的执行要注意下列几点：
（1）如果存储过程名的前三个字符为sp_，SQLServer会在Master数据库中寻找该过程。
如果没能找到合法的过程名，SQLServer会寻找所有者名称为dbo的过程。
（2）参数可以通过value或@parameter_name=value提供。
（3）执行存储过程时，若语句是批处理中的第一个语句，则不一定要指定EXECUTE关键字。
3）举例
（1）设计简单的存储过程
【例7.1】从XSCJ数据库的三个表中查询，返回学生学号、姓名、课程名、成绩、学分。
该存储过程不使用任何参数。
USEXSCJ
/*检查是否已存在同名的存储过程，若有，删除。
*/
IFEXISTS（SELECTnameFROMsysobjects
WHEREname='
student_info'
ANDtype='
P'
）
DROPPROCEDUREstudent_infoGO/*创建存储过程*/
CREATEPROCEDUREstudent_info
ASSELECTa.学号,姓名,课程名,成绩,学分FROMXSaINNERJOINXS_KCb
ONa.学号=b.学号INNERJOINKCt
ONb.课程号=t.课程号GO
（2）使用带参数的存储过程
【例7.2】从XSCJ数据库的三个表中查询某人指定课程的成绩和学分。
该存储过程接受与传递参数精确匹配的值。
student_info1'
DROPPROCEDUREstudent_info1GO
CREATEPROCEDUREstudent_info1
@namechar（8）,@cnamechar（16）
ASSELECTa.学号,姓名,课程名,成绩,学分
FROMXSaINNERJOINXS_KCb
ONb.课程号=t.课程号
WHEREa.姓名=@nameandt.课程名=@cnameGO
（3）使用带有通配符参数的存储过程
【例7.3】从三个表的联接中返回指定学生的学号、姓名、所选课程名称及该课程的成绩。
该存储过程在参数中使用了模式匹配，如果没有提供参数，则使用预设的默认值。
st_info'
DROPPROCEDUREst_info
GOCREATEPROCEDUREst_info
@namevarchar（30）='
刘%'
ASSELECTa.学号,a.姓名,c.课程名,b.成绩
FROMXSaINNERJOINXS_KCb
ONa.学号=b.学号INNERJOINKCc
ONc.课程号=b.课程号
WHERE姓名LIKE@nameGO
（4）使用带OUTPUT参数的存储过程
【例7.4】用于计算指定学生的总学分，存储过程中使用了一个输入参数和一个输出参数。
USEXSCJGO
IFEXISTS（SELECTnameFROMsysobjects
WHEREname='
totalcredit'
DROPPROCEDUREtotalcredit
GOUSEXSCJ
GOCREATEPROCEDUREtotalcredit@namevarchar（40）,
@totalintOUTPUT
ASSELECT@total=SUM（学分）
FROMXS,XS_KC,KC
WHERE姓名=@nameANDXS.学号=XS_KC.学号
GROUPBYXS.学号GO
（5）使用OUTPUT游标参数的存储过程
OUTPUT游标参数用于返回存储过程的局部游标。
【例7.5】在XSCJ数据库的XS表上声明并打开一个游标。
st_cursor'
andtype='
DROPPROCEDUREst_cursorGO
CREATEPROCEDUREst_cursor@st_cursorCURSORVARYINGOUTPUT
ASSET@st_cursor=CURSORFORWARD_ONLYSTATICFOR
SELECT*FROMXS
OPEN@st_cursorGO
（6）使用WITHENCRYPTION选项
WITHENCRYPTION子句对用户隐藏存储过程的文本。
【例7.6】创建加密过程，使用sp_helptext系统存储过程获取关于加密过程的信息，然后尝试直接从syscomments表中获取关于该过程的信息。
IFEXISTS（SELECTnameFROMsysobjects
encrypt_this'
DROPPROCEDUREencrypt_thisGO
USEXSCJGO
CREATEPROCEDUREencrypt_thisWITHENCRYPTION
ASSELECT*
FROMXSGO
（7）创建用户定义的系统存储过程
【例7.7】创建一个过程，显示表名以xs开头的所有表及其对应的索引。
如果没有指定参数，该过程将返回表名以kc开头的所有表及对应的索引。
sp_showtable'
DROPPROCEDUREsp_showtableGO
USEmasterGO
CREATEPROCEDUREsp_showtable@TABLEvarchar（30）='
kc%'
ASSELECTtab.nameASTABLE_NAME,
inx.nameASINDEX_NAME,
indidASINDEX_ID
FROMsysindexesinxINNERJOINsysobjectstabONtab.id=inx.id
WHEREtab.nameLIKE@TABLEGO
EXECsp_showtable'
xs%'
GO
7.1.3用户存储过程的编辑修改
ALTERPROC[EDURE]procedure_name[;
number]
[{@parameterdata_type}
[VARYING][0=default][OUTPUT]]
[,...n1]
{RECOMPILE|ENCRYPTION
|RECOMPILE,ENCRYPTION
}]
[FORREPLICATION]
AS
sql_statement[...n2]
【例7.8】对存储过程student_info1进行修改。
GOALTERPROCEDUREstudent_info1
@namechar（8）,@cnamechar（16）
FROMXSaINNERjoinXS_KCb
ONa.学号=b.学号INNERJOINKCt
ONb.课程号=t.课程号
【例7.9】创建名为select_students的存储过程，默认情况下，该过程可查询所有学生信息，随后授予权限。
GOIFEXISTS（SELECTnameFROMsysobjectsWHEREname='
select_students'
DROPPROCEDUREselect_studentsGO/*若该存储过程已存在，则删除*/
USEXSCJGO
CREATEPROCEDUREselect_students/*创建存储过程*/
ASSELECT*
FROMXS
ORDERBY学号GO
使用DROPPROCEDURE
语句可永久地删除存储过程。
在此之前，必须确认该存储过程没有任何依赖关系。
DROPPROCEDURE{procedure}[,...n]
【例7.10】删除XSCJ数据库中的student_info1存储过程。
DROPPROCEDUREstudent_info1
procedure指要删除的存储过程或存储过程组的名称；
n：
表示可以指定多个存储过程同时删除。
语法格式
CREATETRIGGERtrigger_name
ON{table|view}/*指定操作对象*/
[WITHENCRYPTION]/*说明是否采用加密方式*/
{{{FOR|AFTER|INSTEADOF}{[INSERT][,][UPDATE]}
[WITHAPPEND]
[NOTFORREPLICATION]/*说明该触发器不用于复制*/
AS[{IFUPDATE（column）
[{AND|OR}UPDATE（column）]
[...n]
|IF（COLUMNS_UPDATED（）{bitwise_operator}updated_bitmask）
{comparison_operator}column_bitmask[...n]
}]/*两个IF子句用于说明触发器执行的条件*/
sql_statement[...n]}}/*一条或若干条SQL语句*/
7.2.1利用SQL命令创建触发器
2．触发器中使用的特殊表
inserted逻辑表：
当向表中插入数据时，INSERT触发器触发执行，新的记录插入到触发器表和inserted表中。
deleted逻辑表：
用于保存已从表中删除的记录，当触发一个DELETE触发器时，被删除的记录存放到deleted逻辑表中。
3．使用触发器的限制
使用触发器有下列限制：
（1）CREATETRIGGER必须是批处理中的第一条语句，并且只能应用到一个表中。
（2）触发器只能在当前的数据库中创建，但触发器可以引用当前数据库的外部对象。
（3）如果指定触发器所有者名限定触发器，要以相同的方式限定表名。
（4）在同一CREATETRIGGER语句中，可以为多种操作（如INSERT和UPDATE）定义相同的触发器操作。
（5）一个表的外键在DELETE、UPDATE操作上定义了级联，不能在该表上定义INSTEADOF7.2.1利用SQL命令创建触发器
（7）在触发器内可以指定任意的SET语句,所选择的SET选项在触发器执行期间有效，并在触发器执行完后恢复到以前的设置。
（8）触发器中不允许包含以下T-SQL语句：
CREATEDATABASE、ALTERDATABASE、LOADDATABASE、RESTOREDATABASE、DROPDATABASE、LOADLOG、RESTORELOG、DISKINIT、DISKRESIZE和RECONFIGURE
（9）触发器不能返回任何结果，为了阻止从触发器返回结果，不要在触发器定义中包含SELECT语句或变量赋值。
4．权限
CREATETRIGGER权限默认授予定义触发器的表所有者、sysadmin固定服务器角色成员、db_owner和db_ddladmin固定数据库角色成员，并且不可转让。
5．举例
【例7.11】对于XSCJ数据库，如果在XS表中添加或更改数据，则将向客户端显示一条信息。
/*使用带有提示消息的触发器*/
USEXSCJ
IFEXISTS（SELECTnameFROMsysobjects
reminder'
TR'
DROPTRIGGERreminderGO
CREATETRIGGERreminderONXS
FORINSERT,UPDATE
ASRAISERROR（4008,16,10）GO
【例7.12】在数据库XSCJ中创建一触发器，当向XS_KC表插入一记录时，检查该记录的学号在XS表是否存在，检查课程号在KC表中是否存在，若有一项为否，则不允许插入。
check_trig'
DROPTRIGGERcheck_trigGO
CREATETRIGGERcheck_trig
ONXS_KC
FORINSERT
ASSELECT*
FROMinserteda
WHEREa.学号NOTIN（SELECTb.学号FROMXSb）ORa.课程号NOTIN（SELECTc.课程号FROMKCc）
BEGINRAISERROR（'
违背数据的一致性.'
16,1）
ROLLBACKTRANSACTIONEND
【例7.13】在XSCJ数据库的XS_KC表上创建一触发器，若对学号列和课程号列修改，则给出提示信息，并取消修改操作。
CREATETRIGGERupdate_trig
ONXS_KCFORupdate
AS/*检查学号列（C0）和课程号列（C1）是否被修改，如果有某些列被修改了，则取消修改操作。
IF（COLUMNS_UPDATED（）&
3）>
0
BEGINRAISERROR（'
ROLLBACKTRANSACTIONENDGO
6.INSTEADOF触发器的设计
如果视图的数据来自于多个基表，则必须使用INSTEADOF触发器支持引用表中数据的插入、更新和删除操作。
如果视图的列为以下几种情况之一：
（1）基表中的计算列。
（2）IDENTITYINSERT为OFF的基表中的标识列。
（3）具有timestamp数据类型的基表列。
该视图的INSERT语句必须为这些列指定值，INSTEADOF触发器在构成将值插入基表的INSERT语句时会忽略指定的值。
【例7.14】在XSCJ数据库中创建表、视图和触发器，以说明INSTEADOFINSERT触发器的使用。
CREATETABLEbooks
（BookKeyintIDENTITY（1,1）,
BookNamenvarchar（10）NOTNULL,
Colornvarchar（10）NOTNULL,
ComputedColAS（BookName+Color）,
Pagesint）GO
/*建立一个视图，包含基表的所有列*/
CREATEVIEWView2
ASSELECTBookKey,BookName,Color,ComputedCol,Pages
FROMbooksGO
/*在View2视图上创建一个INSTEADOFINSERT触发器*/
CREATETRIGGERInsteadTrigonView2
INSTEADOFINSERT
ASBEGIN
/*实际插入时，INSERT语句中不包含BookKey字段和.ComputedCol.字段的值*/
INSERTINTObooks
SELECTBookName,Color,PagesFROMinsertedENDGO
7.2.3触发器的修改
1．利用SQL命令修改触发器
ALTERTRIGGERtrigger_name
ON（table|view）
[WITHENCRYPTION]
{（FOR|AFTER|INSTEADOF）{[DELETE][,][INSERT][,][UPDATE]}
[NOTFORREPLICATION]
ASsql_statement[...n]
}
|{（FOR|AFTER|INSTEADOF）{[INSERT][,][UPDATE]}
AS{IFUPDATE（column）
[{AND|OR}UPDATE（column）][...n]
|IF（COLUMNS_UPDATED（）{bitwise_operator}updated_bitmask）
升级会员 