存储过程市公开课一等奖省赛课获奖课件

3.10存放过程利用T-SQL以存放过程和触发器实现编程存储过程第1页一、存放过程定义二、存放过程特点三、存放过程使用四、存放过程种类五、触发器六、事务存储过程第2页一.什么是存放过程（Stored-Procedure）存放过程就是利Transact-SQL语言编写存放在数据库内能够执行一些特殊或惯用数据处理程序，它是数据库对象之一，能够经过编译在数据库后台执行，是数据库端编程主要技术。即：存放过程是存放在数据库服务器上预先定义与编译好T-SQL语句集合，是一个独立数据库对象。存储过程第3页我们能够将一些需要屡次调用实现某个特定任务代码段编写成一个过程，将其保留在数据库中，并由SQLServer服务器经过过程名来调用它们，这些过程就叫做存放过程。存放过程在创建时就被编译和优化，在第一次执行时进行语法检验和编译。编译好版本存放在过程高速缓存中供给用程序屡次调用说明：存储过程第4页二、存放过程特点

实现了模块化编程。存放过程含有对数据库马上访问功效。使用存放过程能够加紧程序运行速度。使用存放过程能够降低网络流量。使用存放过程能够提升数据库安全性。存放过程由应用程序激活，而不是由系统自动执行存放过程能够接收输入参数和返回值。存储过程第5页三、存放过程使用

1.创建存放过程 2.执行存放过程 3.修改和删除存放过程存储过程第6页1.存放过程创建

存放过程定义主要包含两部分：一是过程名及参数说明；二是过程体说明。创建存放过程语句普通形式以下：CREATEPROCEDURE<存放过程名>[<参数列表>]AS<过程体>

存储过程第7页说明：⑴参数列表：由一个或多个参数说明组成，每个参数说明包含参数名和参数数据类型。当然，存放过程能够没有任何参数。参数格式为：@参数名数据类型[=缺省值][OUTPUT]⑵过程体：是实现存放过程功效一组T-SQL语句，能够包含任意多SQL语句。①但sql语句中不能使用CREATE（VIEW、TRIGER、DEFAULT、RULE、PROCEDURE等）语句，同时要慎重使用其它CREATE、DROP等语句。存储过程第8页②为了使存放过程设计更方便，功效更强大。可使用流程控制语句，主要有以下几个：·赋值语句。可将SQL表示式值赋值给局部变量。·分支语句。用于设计分支程序。如：IF语句、CASE语句等。·循环语句。如：WHILE等语句。·调用存放过程语句CALL，或EXECUTE和从存放过程返回语句RETURN。③在存放体中除了能够使用流程控制语句外，还能够使用游标。存储过程第9页（3）类别：无参有参不带output带有output存储过程第10页例1创建一个不带参数存放过程，完成查询每位学生选课情况及其成绩。CREATEPROCEDUREProc1ASSELECTStudent.Sno,Sname,Course.Cno,Cname,GradeFROMStudent,Course,SCWHEREStudent.Sno=SC.SnoANDCourse.Cno=SC.CnoGO存储过程第11页例2创建一个带参数存放过程，完成查询指定课程选修情况。CREATEPROCEDUREProc2@xCHAR(2)ASSELECTStudent.Sno,Sname,Course.Cno,Cname,GradeFROMStudent,Course,SCWHEREStudent.Sno=SC.SnoANDCourse.Cno=SC.CnoandSC.Cno=@xORDERbySC.SnoGO存储过程第12页例3创建一个带参数并有返回值存放过程，完成输出指定学号学生‘2’号课程成绩，并将结果，赋给一输出参数。CREATEPROCEDUREProc3@xCHAR(5),@vgradeINTOUTPUTASSELECT@vgrade=GradeFROMSCWHERESno=@xANDCno=‘2’RETURNGO存储过程第13页2.执行存放过程

在SQLServer中，使用EXECUTE语句执行存放过程。EXECUTE语句普通格式以下：

EXEC[UTE]<存放过程名>[[过程参数变量=]{值|变量[OUTPUT]…}例4执行例2定义存放过程。EXECProc2@x=’3’或EXECProc2’3’例5执行例8定义存放过程。DECLARE@v1CHAR(5),@v2INTSELECT@v1=’00101’EXECProc3@v1,@v2OUTPUTSELECT@v2存储过程第14页3.修改和删除存放过程

⑴修改存放过程：语句格式：

ALTERPROC[EDURE]<存放过程名>{同定义}⑵删除存放过程：DROPPROCEDURE

语句格式：

DROPPROC[EDURE]<存放过程名>存储过程第15页SQLServer提供存放过程

SQLServer提供了很多现成、实用存放过程供用户使用。惯用存放过程分为5类：系统存放过程(sp_)扩展存放过程(xp_)远程存放过程局部存放过程暂时存放过程四、存放过程种类存储过程第16页⑴系统存放过程(sp_)

SQLServer系统存放过程是为管理员而提供，SQLServer安装时在master数据库中创建并由系统管理员拥有。使用户能够很轻易地从系统表中取出信息，管理数据库，并执行包括更新系统表其它任务。系统存放过程命令均以sq_打头，其作用进行数据库管理。

SQLServer提供了许多系统存放过程以方便检索和操纵存放在系统表中信息，系统存放过程能够在任意一个数据库中执行。存储过程第17页比如，惯用系统存放过程有：·sp_helpdb(database_name)：返回指定数据库信息

·sp_help(object)：返回指定数据库对象信息。

·sp_addlogin：建立SQLServer用户帐号。

·sp_datatype_info：返回由当前环境支持数据类型信息。·sp_monitor：按一定格式显示系统全局变量当前值。

存储过程第18页⑵扩展存放过程(xp_)

扩展存放过程提供一个类似于存放过程方式，它们是动态装入和执行动态连接库（DLL）内函数，无缝地扩展SQLServer功效。SQLServe之外动作能够很轻易地触发，外部信息返回到SQLServer。另外，扩展存放过程支持返回状态码和输出。

注意：必须从master数据库执行扩展存放过程。

用户能够创建自己扩展存放过程。

比如，下面是一些扩展存放过程：

·xp_cmdshell：作为一个操作系统外壳执行指定命令串，并以文本形式返回任何输出。

·xp_logevent：在SQLServer日志文件或WindowsNT事件查看器中统计用户定义信息。

·xp_msver：返回SQLServer版本信息及各种环境信息。存储过程第19页⑶远程存放过程

是从连接到不一样服务器远程服务器或客户机调用存放过程。

⑷局部存放过程

局部存放过程在各个用户数据库中创建。只能由创建它用户调用。

⑸暂时存放过程

暂时存放过程可是局部，名字前前缀是"#"；也可是全局，名字前前缀是"##"。暂时存放过程存放在tempdb数据库中。

局部暂时存放过程在单个用户会话中使用，该用户退出时，自动被删除。

全局暂时存放过程全部用户都能够使用，当最终一个用户退出时，自动被删除。

详细详细信息可参阅相关资料。存储过程第20页本节小结存放过程分系统存放过程、扩展存放过程和用户自定义存放过程。系统存放过程是SQLServer本身配置含有特殊功效和用途存放过程，SQLServer也配置了部分扩展存放过程。用户自定义存放过程是用户使用Transact-SQL编写用户惯用一些程序，这些程序代存放在SQLServer数据库中，并能够经过编译行。调用存放过程类似调用系统函数，能够输入参数，也能够经过参数输出执行结果，大大提了代码重用性、安全性和执行速度。存储过程第21页五、触发器

触发器（Trigger）不但能实现完整性规则，而且能确保一些较复杂业务规则实施。所谓触发器就是一类由事件驱动特殊过程（特殊类型存放过程），当一个触发器建立后，它作为一个数据库对象被存放。当某个触发事件发生时，触发器被触发，执行一系列操作。触发器一旦由某个用户建立，任何用户对该触发器指定数据进行增、删或改操作时，DBMS系统将自动激活对应触发器，定义在触发器中功效将被DBMS执行，在关键层进行集中完整性控制。存储过程第22页1.

触发器概念⑴本质

⑵特点⑶优点存储过程第23页

触发器主要优点⑴触发器能够实施比外键约束，检验约束和规则对象等更为复杂数据完整性检验。⑵和约束相比，触发器提供了更多灵活性。约束将系统错误信息返回给用户，而触发器能够打印错误信息，调用其它存放过程，或依据需要纠正错误。⑶不论对表中数据进行何种更新（增删改操作），对应触发器都能被激活，对数据实施完整性检验和处理。⑷触发器能够级联更新数据库中表内容存储过程第24页2.

触发器支持功效⑴触发器能够在事件之前、之后执行，还能够替换触发语句操作。比如能够定义触发器在对某关系执行INSERT、UPDATE和DELETE操作之前后触发。

⑵触发器有语句级触发器和行级触发器之分。对被事件影响修改每一行（FOREACHROW）即每一元组执行一次触发过程，称为行级触发器。对整个事件只执行一次触发过程（FOREACHSTATEMENT），称为语句级触发器，该方式是触发器默认方式。⑶

对于UPDATE事件能够定义对哪个关系、或关系中哪一列修改时，触发器触发。存储过程第25页⑷

能够指定执行条件，当触发器被触发后，触发器功效代码只有在条件成立时才执行。

⑸

触发器代码能够引用事件中对于元组修改前后值（OLD值和NEW值）。

⑹

触发器能够完成一些复杂数据检验，能够实现一些操作前后处理等。

⑺

触发器定义约束能够在任何颗粒级别上实现、表示动态或静态约束、延迟或不延迟进行触发检验、能够用SQL语句定义约束触发器功效。存储过程第26页3.触发器类别⑴按触发事件分：有INSERT触发器、UPDATE触发器和DELETE触发器之分。⑵按触发时间分：有BEFORE触发器和AFTER触发器之分。BEFORE触发器是在事件发生之前触发，AFTER触发器是在事件发生之后触发。⑶按触发方式分：有语句级触发器和行级触发器之分。注：SQLServer对上述部分功效可实现，但在语法描述上有区分。存储过程第27页4.

SQLServer中触发器创建、删除和修改触发器定义包含两个方面；指明触发器触发事件和指明触发器执行动作。

⑴创建触发器语句创建触发器语句普通格式为：CREATETRIGGER<触发器名>ON{<表名>|<视图名>}[WITHENCRYPTION]

FOR{[INSERT][,][UPDATA][,][DELETE]}AST-SQL语句序列

存储过程第28页说明：

①表名,视图名：为触发该触发器表名或视图名。SQLServer中，只有表全部者才有权建立触发器。②INSERT,UPDATA,DELETE：说明触发触发器事件。一个定义语句允许定义多个触发事件，用逗号分开。③WITHENCRYPTION：该选项对触发器定义文本加密。

④T-SQL操作语句序列：指定触发器动作。该语句中可以指定多个触发器操作，这时要用BEGIN…END将它们组成语句块。存储过程第29页⑵删除和修改触发器

删除触发表时，触发器被随之删除。能够用DROP语句删除已定义触发器。也能够使用ALTERTRIGGER语句修改触发器定义。比如，删除已定义触发器语句格式：

DROPTRIGGER<触发器名>[，<触发器名>…]存储过程第30页⑶触发器中可使用特殊表：

使用触发器时，SQLServer提供了两张特殊暂时表：

inserted表和deleted表。这两张表存在于高速缓存中，它们与创建触发器表有相同结构。

．用户能够使用该表检验一些修改操作效果。

．但用户不能直接修改该表中数据。

．用户能够使用该表内容作为查询操作判断条件，但要在FROM中写出使用表名（inserted或deleted）。

存储过程第31页①inserted表：

存放被INSERTE插入和UPDATE更新新数据。当向表中插入数据时，INSERT触发器被触发。新统计增加到触发器表中和inserted表中。inserted表是一个逻辑表，保留了所插入统计拷贝，触发器能够检验inserted表，来确定该触发器操作是否应该执行和怎样执行。

存储过程第32页②deleted表：存放被DELETE删除和UPDATE更新旧数据。当触发一个DELETE触发器时，被删除统计放在一个特殊deleted表中。deleted表是一个逻辑表，用来保留已经从表中删除统计。DELETE触发器能够参考deleted表中数据。

存储过程第33页注：UPDATE触发器可使用deleted表和inserted表修改一条统计等于删除一条旧统计和插入一条新统计。UPDATE能够看成是由DELETE语句和INSERT语句组成。当在一个有UPDATE触发器表上修改一条统计时，表中原来统计移动到deleted表中，修改过统计插入到inserted表中。UPDATE触发器能够参考deleted表和inserted表，方便确定怎样执行触发器操作。存储过程第34页假设学生表S新增一属性Cnum(类型为INT,初值均为0),统计该学生选课数，分别创建以下触发器：⑴INSERT触发器例8创建INSERT触发器Tri1，其功效是：在学习表SC插入一条统计后，马上更新学生表SCnum属性，即将原值加一。CREATETRIGGERTri1ONSCFORINSERTASUPDATEStudentSETCnum=Cnum+1WHEREStudent.Sno=(SELECTSnoFROMinsertedWHEREStudent.Sno=inserted.Sno)

5.

触发器应用举例存储过程第35页⑵DELETE触发器例9创建DELETE触发器Tri2，其功效是：在删除学习表SC一条统计后，马上更新学生表SCnum属性，即将原值减一。CREATETRIGGERTri2ONSCFORDELETEASUPDATEStudentSETCnum=Cnum-1WHEREStudent.Sno=(SELECTSnoFROMdeletedWHEREStudent.Sno=deleted.Sno)

存储过程第36页⑶UPDATE触发器例10创建UPDATE触发器Tri3，功效是：在SC表更新一条统计学号后，马上更新学生表SCnum属性，改前学号学生Cnum值减1，改后学号学生Cnum值加1。存储过程第37页CREATETRIGGERTri3ONSCFORUPDATEASIFUPDATE(Sno)BEGINUPDATEStudentSETCnum=Cnum-1WHEREStudent.Sno=(SELECTSnoFROMdeletedWHEREStudent.Sno=deleted.Sno)UPDATEStudentSETCnum=Cnum+1WHEREStudent.Sno=(SELECTSnoFROMinsertedWHEREStudent.Sno=inserted.Sno)END存储过程第38页·触发器最大用途是维护数据完整性，而不是返回结果。

·只是在必要时候使用触发器。假如使用约束，规则，默认就能够实现预定数据完整性时，应优先考虑使用这3种办法。

·使触发器定义语句尽可能清楚简单。

·触发器普通定义在操作发生之后执行，约束在操作发生之前起作用。假如在触发器表上有约束，那么这些约束在触发器执行之前进行检验。假如操作与约束有冲突，那么触发器不执行。

·有DBMS系统触发器不能在暂时表或视图上创建，但能够参考这些对象。

·触发器和激活它语句作为单个事务处理，假如检验到严重错误，整个事务自动撤消。

6.使用触发器考虑

存储过程第39页六、事务定义一个数据库操作序列一个不可分割工作单位恢复和并发控制基本单位事务和程序比较在关系数据库中，一个事务能够是一条或多条SQL语句,也能够包含一个或多个程序。一个程序通常包含多个事务存储过程第40页2、事务特征(ACID特征)事务ACID特征：原子性（Atomicity）一致性（Consistency）隔离性（Isolation）连续性（Durability）存储过程第41页3、SQL对事务支持

ANSI（AmericanNationalStandardsInstitute，美国国家标准委员会）公布SQL92标准已经明确了对事务支持。各个DBMS除了都支持表示事务结束COMMIT和ROLLBACK语句外，各个不一样DBMS在其它详细事务实现中还是有一些差异。在最惯用数据库系统SQLSERVER中要求：在事务中除以下语句不可使用外，其它全部SQLSERVER支持SQL语句均可使用：存储过程第42页ALTERDATABASELOADDATABASEBACKUPLOGLOADTRANSACTIONCREATEDATABASERECONFIGUREDISKINITRESTOREDATABASEDROPDATABASERESTORELOGDUMPTRANSACTIONUPDATESTATISTICS存