第7章 存储过程、触发器、视图95

2020年4月28日星期二数据库技术第7章存储过程、触发器、视图主要内容7.1存储过程7.2触发器7.3视图2020年4月28日星期二数据库技术7.1存储过程存储过程：是SQL-Server服务器上一组预先编译好的T-SQL语句。以一个名称存储在数据库中，作为一个独立的数据库对象。系统存储过程：存储于MASTER数据库中并以SP_为前缀，用以执行特定的功能。用户自定义的存储过程由用户创建并能完成特定功能的存储过程。用户定义的存储过程必须创建在当前的数据库中。2020年4月28日星期二数据库技术7.1.1使用企业管理器管理存储过程利用创建存储过程向导创建存储过程：步骤：企业管理器→数据库结点→工具→向导→数据库→创建存储过程向导→选择数据库→选择存储过程→选择表和相应操作→编辑→完成。企业管理器中直接创建存储过程2020年4月28日星期二数据库技术7.1.2使用T-SQL语言管理存储过程语法：CREATEPROC[EDURE]procedure_name[{@parameterdata_type}[=default]][,…n][with{RECOMPLE|ENCRYPTION|]ASsql_statement[,…n]2020年4月28日星期二数据库技术7.1.2使用T-SQL语言管理存储过程Procedure_name:指定存储过程的名称；@parameterdate_type[=default]：指定存储过程的参数。Parameter为参数名称，date_type参数的数据类型，[=default]用于指定参数的默认值。默认值必须是常数或空值。[with{RECOMPLE|ENCRYPTION|}]：RECOMPLE指定每执行一次存储过程都要重新编译，ENCRYPTION表示SQLServer加密syscomments表中包含CREATEPROCEDURE的语句。ASsql_statement：过程中要包含的T-SQL语句。2020年4月28日星期二数据库技术7.1.2使用T-SQL语言管理存储过程例:从xscj数据库的三个表中查询,返回学生学号、姓名、课程号、成绩、学分。UsexscjIfexists(selectnamefromsysobjectswherename='student_info'andtype='p')Dropprocedurestudent_infoGoCreateprocedurestudent_infoAsSelecta.学号,课程名,成绩,总学分fromxsainnerjoinxs_kcbona.学号=b.学号innerjoinkctonb.课程号=t.课程号2020年4月28日星期二数据库技术7.1.2使用T-SQL语言管理存储过程使用带参数的存储过程Createprocedurestudent_info1@namechar(8),@cnamechar(16)AsSelecta.学号,姓名,课程名,成绩fromxsainnerjoinxs_kcbona.学号=b.学号innerjoinkctonb.课程号=t.课程号Wherea.姓名=@nameandt.课程名=@cnameExecstudent_info11'王林','计算机基础'2020年4月28日星期二数据库技术7.1.2使用T-SQL语言管理存储过程使用带有通配符号的存储过程Createprocedurest_info@namevarchar(8)=‘刘%’AsSelecta.学号,姓名,课程名,成绩fromxsainnerjoinxs_kcbona.学号=b.学号innerjoinkctonb.课程号=t.课程号Where姓名like@nameExecst_infoExecst_info'王%'2020年4月28日星期二数据库技术7.1.2使用T-SQL语言管理存储过程使用带output参数的存储过程createproceduretotalcredit@namechar(8),@totalintoutputasselect@total=sum(总学分)fromxs,kc,xs_kcwhere姓名=@nameandxs.学号=xs_kc.学号groupbyxs.学号2020年4月28日星期二数据库技术7.1.2使用T-SQL语言管理存储过程UsexscjDeclare@totalintExectotalcredit'王林',@totaloutputSelect'王林',@totalastotal2020年4月28日星期二数据库技术7.2触发器触发器：是一种实施数据完整性的特殊类型的存储过程。触发器主要是通过事件进行触发而被执行的一段T-SQL语句，当用户修改表中数据时(insert,update,delete)，触发器将自动执行。2020年4月28日星期二数据库技术7..2.1触发器的类型SQLServer2000支持两种类型的触发器：AFTER触发器和INSTEADOF触发器。1、AFTER类型触发器这类触发器将在表中的数据变动（INSERT、UPDATE、DELETE）完成以后才被激发，是为了对变动的数据进行检查，如果发现错误，将拒绝或回滚变动的数据。2、INSTEADOF类型触发器INSTEADOF触发器是SQLSERVER2000中新增的功能，这种类型的触发器将在数据变动之前被激活，并取代数据的操作（INSERT、UPDATE、DELETE），转而去执行触发器定义的操作。2020年4月28日星期二数据库技术7.2.2T-SQL语言创建和管理触发器格式：CREATETRIGGERtrigger_nameON{table|view}[WITHENCRYPTION]{FOR|AFTER|INSTEADOF}{[UPDATE][,][INSERT][,][DELETE]}ASsql_statement[,…n]2020年4月28日星期二数据库技术7.2.2T-SQL语言创建和管理触发器createtriggertrigger_comeonxsafterinsertasPrint'欢迎新生来到本校'gocreatetriggertrigger_leaveonxsafterdeleteasPrint'祝贺你顺利毕业'Deletefromxswhere学号=‘123’2020年4月28日星期二数据库技术7.2.2T-SQL语言创建和管理触发器对现存的触发器进行修改，使用ALTERTIGGER语法格式：ALTERTRIGGERtrigger_nameON{table|view}[WITHENCRYPTION]{FOR|AFTER|INSTEADOF}{[UPDATE][,][INSERT][,][DELETE]}ASsql_statement[,…n]2020年4月28日星期二数据库技术7.2.2T-SQL语言创建和管理触发器删除触发器DROPTRIGGER语法格式：DROPTRIGGERtrigger_name[,…n]例：删除触发器trigger_come,trigger_leaveDroptriggertrigger_come,trigger_leave2020年4月28日星期二数据库技术7.2.3触发器生成的两个临时表Inserted表和deleted表执行INSERT语句：新纪录同时插入到数据表和Inserted表。执行UPDATE语句：先删除数据表中原有记录，将该记录插入到deleted表，新插入的记录同时插入到Insert表。执行DELETE语句：删除的记录将被插入到deleted表。2020年4月28日星期二数据库技术7.2.3触发器生成的两个临时表CREATETRIGGERtrigger_totalONxs_kcFORINSERTASIF(SELECT成绩FROMinserted)100BEGINPRINT'成绩不能超过100分'ROLLBACKENDGOINSERTINTOxs_kc(学号,课程号,成绩)VALUES('001102','101',130)2020年4月28日星期二数据库技术7.2.3触发器生成的两个临时表createtriggertrigger_deleteonxsafterdeleteasifexists(select*fromdeletedwhere总学分50)beginprint'这个学生不能删除'rollbackend2020年4月28日星期二数据库技术7.2.4创建（INSTEADOF）触发器INSTEADOF触发器可以用来取代INSERT，UPDATE，DELETE操作，转而由触发器中的程序控制数据的修改。但是，当INSTEADOF触发器被激活时，临时表INSERTED和DELETED都已经输入数据。AFTER触发器在激活以前，表中的数据就已经修改完毕了；而INSTEADOF触发器被激活时，实际上还没有修改表中的数据，因此可以用INSTEADOF触发器控制数据修改的过程。2020年4月28日星期二数据库技术7.2.4创建（INSTEADOF）触发器CREATETRIGGERstudentONxsinsteadofinsertASbeginINSERTINTOxc_kcSELECT学号,课程号,成绩FROMinsertedend2020年4月28日星期二数据库技术7.3视图视图是一种数据库对象，是虚拟表。它是从基本表或者视图中导出的表,用来存储用户想看到的数据。视图与数据表之间的区别：视图是引用存储在数据库中的查询语句时动态创建的，它本身并不存储数据，真正的数据依然存储在数据表中。2020年4月28日星期二数据库技术7.3.1视图的创建通过企业管理器通过向导用sql语言创建2020年4月28日星期二数据库技术7.3.1视图的创建语法格式如下:CREATEVIEWview_name[(column[,…n])][withENCRYPTION]ASselect_statement[WITHCHECKOPTION]视图的名称对下面的SELECT语句进行加密定义视图的查询语句强制所有通过视图修改的数据满足语句中指定的选择条件。字段名2020年4月28日星期二数据库技术7.3.1视图的创建例:利用表xs创建视图V1,该视图的结果集中包含学号,姓名,专业名CREATEVIEWv1ASSELECT学号,姓名,专业名FROMxswhere专业名='计算机'2020年4月28日星期二数据库技术7.3.1视图的创建例:查找平均成绩在80分以上的学生的学号和平均成绩CREATEVIEWxs_kc_avg(num,score_avg)asselect学号,avg(成绩)fromxs_kcgroupbyxs_kc.学号2020年4月28日星期二数据库技术7.3.2通过视图管理数据可以通过修改视图中的数据来修改基表中的数据。视图与表具有相似的结构，当向视图中插入或更新数据时，实际上对视图所引用的基表执行数据的插入和更新。但是通过视图插入、更新数据和表相比有一些限制。2020年4月28日星期二数据库技术7.3.2通过视图管理数据例:创建一个视图，然后通过视图把计算机专业学生的总学分加5。CREATEVIEWvwxsASSELECT*fromxsSELECT*FROMvwxsUPDATEvwxsSET总学分=总学分+5Where专业名='计算机'SELECT*FROMvwxs2020年4月28日星期二数据库技术7.3.2通过视图管理数据注意：如果修改将影响多个基本表，则你不能在视图中一次性修改数据，否则可以。不能修改那些内容为计算结果的列。例如，一个经过计算的列或一个集合函数。图引用多个表时，无法用DELETE命令删除数据。确认那些不包括在视图列中，但属于表的列，是否允许NULL值或有缺省值的情况。2020年4月28日星期二数据库技术7.3.2通过视图管理数据例:创建一个视图,显示学号,姓名,课程号,成绩createviewcs_kcasSe