资源描述
6 存储过程与触发器存储过程与触发器(1)(1)存储过程是存储过程是T-SQLT-SQL语句的集合，它作为数据库对象之一语句的集合，它作为数据库对象之一被存储在数据库中。被存储在数据库中。(2)(2)存储过程的作用和使用方式类似于一些编程语言中的存储过程的作用和使用方式类似于一些编程语言中的过程。过程。使用存储过程有以下优点：使用存储过程有以下优点：1)1)可以在一个存储过程中执行多条可以在一个存储过程中执行多条SQLSQL语句；语句；2)2)可通过输入参数的变化调用存储过程进行动态执行；可通过输入参数的变化调用存储过程进行动态执行；3)3)存存储储过过程程在在创创建建时时就就在在服服务务器器端端进进行行了了编编译译，节节省省SQLSQL语句的运行时间；语句的运行时间；4)4)提提供供了了安安全全机机制制，它它限限制制了了用用户户访访问问SQLSQL语语句句的的权权利利，只为特定用户开放存储过程。只为特定用户开放存储过程。6 存储过程与触发器使用存储过程有以下优点：6.1 创建存储过程创建存储过程 用户首先创建存储过程以实现特定的功能，然后可在程用户首先创建存储过程以实现特定的功能，然后可在程序中调用该存储过程执行。序中调用该存储过程执行。1 1使用企业管理器创建视图使用企业管理器创建视图 【例【例5-635-63】在在SchoolSchool数据数据库中，中，创建存建存储过程程proc_Courseproc_Course，查询所有所有课程信息。程信息。1 1）打开企业管理器，展开）打开企业管理器，展开SchoolSchool数据库文件夹，右单击数据库文件夹，右单击“存储过程存储过程”，在弹出的快捷菜单上选择，在弹出的快捷菜单上选择“新建存储过程新建存储过程”命令，打开新建存储过程对话框。命令，打开新建存储过程对话框。2 2）将）将“OWNER”OWNER”和和“PROCEDURE PROCEDURE NAME”NAME”参数分别替换参数分别替换成存储过程所有者的成存储过程所有者的名称和存储过程的名名称和存储过程的名称。默认为称。默认为dbodbo和和 PROCEDURE NAMEPROCEDURE NAME。6.1 创建存储过程 1使用企业管理器创建视图【例5-63 3）从）从“文本文本”列表框的第二行开始列表框的第二行开始输入存入存储过程的程的T-SQLT-SQL语句。句。这里里输入的入的T-SQLT-SQL语句句仅有一条有一条:SELECT*FROM T_CourseSELECT*FROM T_Course。4 4）单击【语法法检查】按按钮可以可以检查创建存建存储过程的程的T-SQLT-SQL语句的句的语法是否正确。法是否正确。单击【另存另存为模板模板】按按钮可将所可将所创建建的存的存储过程程设置置为模板。模板。5 5）完成后，）完成后，单击【确定确定】按按钮即即创建和保存了存建和保存了存储过程程proc_Courseproc_Course。图5-64 创建存储过程示例3）从“文本”列表框的第二行开始输入存储过程的T-SQL语句2 2使用使用T-SQLT-SQL语句创建存储过程语句创建存储过程 可以直接在可以直接在SQLSQL查询分析器中定义和调用存储过程。查询分析器中定义和调用存储过程。常用存常用存储过程的程的语法格式：法格式：CREATE PROCDURE CREATE PROCDURE 存储过程名存储过程名 形式参数形式参数 数据类型数据类型VARYING=VARYING=默认值默认值OUTPUTOUTPUT AS AS SQL SQL语句语句1 1 SQL SQL语句句n n 说明：说明：“形式参数形式参数”名称必名称必须符合符合标识符符规则；OUTPUTOUTPUT表示表示该参数参数是可以返回的，可将信息返回是可以返回的，可将信息返回调用者；如果有多个参数，用者；如果有多个参数，可以依次按以上参数定可以依次按以上参数定义规则列出，用逗号列出，用逗号“,”,”隔开。隔开。2使用T-SQL语句创建存储过程 常用存储过程的语法格式：【例【例5-645-64】在在SchoolSchool中创建一个的存储过程中创建一个的存储过程proc_SearchStudent,proc_SearchStudent,查询指定学生的选课情况。查询指定学生的选课情况。CREATE PROC proc_SearchStudentCREATE PROC proc_SearchStudent stcode char(8)stcode char(8)ASASSELECT T_Student.StudentName,T_Course.CourseNameSELECT T_Student.StudentName,T_Course.CourseNameFROM T_Student JOIN FROM T_Student JOIN T_Grade JOIN T_CourseT_Grade JOIN T_CourseON T_Grade.CourseCode=T_Course.CourseCodeON T_Grade.CourseCode=T_Course.CourseCodeON T_Student.StudentCode=T_Grade.StudentCodeON T_Student.StudentCode=T_Grade.StudentCodeWHERE T_Student.StudentCode=scodeWHERE T_Student.StudentCode=scode 【例5-64】在School中创建一个的存储过程proc_S6.2 执行存储过程执行存储过程 存存储过程定程定义后，可通后，可通过EXECTEEXECTE语句来句来执行。行。语法格式：法格式：EXECUTE EXECUTE 存储过程名存储过程名形参形参=实参值实参值|变量变量 OUTPUT|DEFAULTOUTPUT|DEFAULT1)1)“形参形参”是是创建存建存储过程程时定定义的形参名；的形参名；2)“2)“实参参值”是是输入参数的入参数的值；3)“3)“变量量”表示用来保存参数或者返回参数的表示用来保存参数或者返回参数的变量；量；OUTPUTOUTPUT表示指定表示指定参数参数为返回参数；返回参数；4)DEFAULT4)DEFAULT表示使用表示使用该参数的默参数的默认值作作为实参。参。5)5)如有多个参数，可依次按以上参数定如有多个参数，可依次按以上参数定义规则列出，用逗号列出，用逗号“，”隔开。隔开。调用【例调用【例5-635-63】创建的存储过程创建的存储过程proc_Courseproc_Course的语句为：的语句为：EXEC proc_CourseEXEC proc_Course调用【例调用【例5-645-64】创建的存储过程创建的存储过程proc_SearchStudentproc_SearchStudent，查询查询0510110105101101学生的选课情况。学生的选课情况。EXEC proc_SearchStudent 05101101EXEC proc_SearchStudent 05101101或或EXEC proc_ SearchStudent scode=05101101EXEC proc_ SearchStudent scode=05101101采用采用“参数参数=值值”的形的形式，各个参数的顺序可式，各个参数的顺序可以任意排列。以任意排列。实实参参顺顺序序和和定定义义时时的参数顺序一致。的参数顺序一致。6.2 执行存储过程 语法格式：1)“形参”是创建存储过【例【例5-655-65】在在SchoolSchool中中创建一个的存建一个的存储过程程proc_SearchStudentAvgGradeproc_SearchStudentAvgGrade，查询某个学生的某个学生的选课数目和数目和平均成平均成绩。CREATE PROC proc_SearchStudentAvgGradeCREATE PROC proc_SearchStudentAvgGrade stcode char(8),stcode char(8),stcount int OUTPUT,stavg int OUTPUT stcount int OUTPUT,stavg int OUTPUTASASSELECT SELECT stcount=COUNT(T_Grade.StudentCode),stavg=AVG(T_Grade.Gradestcount=COUNT(T_Grade.StudentCode),stavg=AVG(T_Grade.Grade)FROM T_GradeFROM T_GradeWHERE T_Grade.StudentCode=stcodeWHERE T_Grade.StudentCode=stcodeGROUP BY T_Grade.StudentCodeGROUP BY T_Grade.StudentCode本例是带一个输入参本例是带一个输入参数和二个输出参数的数和二个输出参数的存储过程，在定义输存储过程，在定义输出参数时要用出参数时要用OUTPUT进行说明。进行说明。调用存用存储过程程proc_SearchStudentAvgGradeproc_SearchStudentAvgGrade，查询0510110105101101学生的学生的选课数目和平均成数目和平均成绩。DECLARE scount int,savg int DECLARE scount int,savg int EXECEXEC proc_SearchStudentAvgGrade 05101101,scount OUTPUT,savg OUTPUT proc_SearchStudentAvgGrade 05101101,scount OUTPUT,savg OUTPUT PRINT PRINT 学学 生生+05101101+05101101+的的 选选 课课 数数 目目 为为+CAST(scount CAST(scount AS AS char(2)+char(2)+门门 PRINT PRINT 学生学生+05101101+05101101+的平均成的平均成绩为+STR(savg,5,2)+STR(savg,5,2)+分分 【例5-65】在School中创建一个的存储过程proc_S6.3 查看、修改和删除存储过程查看、修改和删除存储过程 用户可以利用企业管理器或用户可以利用企业管理器或T-SQLT-SQL语句来查看、修改和语句来查看、修改和删除用户创建的存储过程。删除用户创建的存储过程。（1 1）查看和修改存储过程）查看和修改存储过程在在企企业业管管理理器器中中，展展开开存存储储过过程程所所属属的的数数据据库库，单单击击“存存储过程储过程”文件夹，在右边窗格中就可看到所有存储过程；文件夹，在右边窗格中就可看到所有存储过程；右右击击相相应应的的存存储储过过程程名名，在在弹弹出出的的快快捷捷菜菜单单上上选选择择“属属性性”命命令令，打打开开存存储储过过程程属属性性窗窗口口，出出现现和和创创建建存存储储过过程程相相似似的的窗窗口口，在在中中间间的的文文本本编编辑辑子子窗窗口口中中可可以以查查看看或或修修改改该该存储过程的文本。存储过程的文本。1 1使用企业管理器创建视图使用企业管理器创建视图 （2 2）删除存储过程）删除存储过程右右击要要删除除的的存存储过程程名名，在在弹出出的的快快捷捷菜菜单上上选择“删除除”命命令令，弹出出“除除去去对象象”对话框框。单击【显示示相相关关性性】按按钮可可查看看该存存储过程程所所依依附附的的对象象，从从而而了了解解删除除该存存储过程程对数数据据库的的影影响响。单击【全全部部除除去去】按按钮，即即删除除了相了相应的存的存储过程程。6.3 查看、修改和删除存储过程（1）查看和修改存储过程16.4 触发器触发器 触发器是一种特殊的存储过程。触发器的创建主要触发器是一种特殊的存储过程。触发器的创建主要用来维护数据表中的数据一致性，当对数据表进行插入、用来维护数据表中的数据一致性，当对数据表进行插入、删除、更新等操作时，触发器可自动执行。删除、更新等操作时，触发器可自动执行。SQL ServerSQL Server提供了两种类型的触发器：提供了两种类型的触发器：（1 1）AFTERAFTER触触发发器器：是是在在表表中中数数据据被被修修改改之之后后才才被被触触发发，触触发发器器对对变变动动的的数数据据进进行行检检查查，如如果果发发现现错错误误，将将拒拒绝绝或回滚变动的数据。或回滚变动的数据。（2 2）INSTEAD INSTEAD OFOF触触发发器器：在在数数据据修修改改以以前前被被触触发发，并并取取代修改数据的操作，转去执行触发器定义的操作。代修改数据的操作，转去执行触发器定义的操作。6.4 触发器SQL Server提供了两种类型的触发器：6.4.1 创建触发器创建触发器 1 1使用企业管理器创建触发器使用企业管理器创建触发器 【例例5-685-68】在在SchoolSchool数据数据库中中创建一个建一个简单的触的触发器器tri_StudentInsDeltri_StudentInsDel，当用，当用户插入或插入或删除除T_StudentT_Student表中学表中学生生记录时，能自，能自动显示表中的内容。示表中的内容。1 1）展展开开School“School“数数据据库/表表”文文件件夹，在在右右边边窗窗格格中中表表中中，右右击击T_StudentT_Student表表，在在弹出出的的快快捷捷菜菜单上上选择“所所有有任任务/管管理触理触发器器”命令，打开触命令，打开触发器属性器属性对话框。框。6.4.1 创建触发器【例5-68】在School数据库中2 2）系系统创建建触触发器器语句句了了CREATE CREATE TRIGGERTRIGGER的的框框架架。修修改改框框架中的内容架中的内容为：CREATE TRIGGER tri_StudentInsDel ON dbo.T_Student FOR INSERT,DELETE AS SELECT*FROM T_Student3 3）单击【语法法检查】按按钮可以可以检查创建触建触发器的器的T-SQLT-SQL语句的句的语法是否正确。法是否正确。4 4）单击【确定确定】按按钮即保存和即保存和创建了名称建了名称为“tri_StudentInsDel”tri_StudentInsDel”的触的触发器。器。当对表当对表T_Student的数据进行插入或删除操的数据进行插入或删除操作时，触发器作时，触发器“tri_StudentUpdate”将会自动执行。将会自动执行。2）系统创建触发器语句了CREATE TRIGGER的框架。使用使用T-SQLT-SQL创建触发器创建触发器 触发器语法格式：触发器语法格式：CREATE TRIGGER 触发器名触发器名ON 表名表名|视图名视图名FOR INSERT,UPDATE,DELETEASSQL语句段语句段【例例 5-695-69】在在 SchoolSchool数数 据据 库 的的 T_StudentT_Student表表 上上 创 建建 一一 个个 触触 发 器器tri_StudentCodeUpdatetri_StudentCodeUpdate，当当对学学号号列列进行行修修改改时，给出出提提示示信信息息并并取消修改操作。取消修改操作。CREATE TRIGGER tri_StudentCodeUpdateCREATE TRIGGER tri_StudentCodeUpdateON T_StudentON T_StudentFOR UPDATEFOR UPDATEASAS DECLARE text varchar(50)DECLARE text varchar(50)IF UPDATE(StudentCode)IF UPDATE(StudentCode)BEGIN BEGIN SET text=SET text=学生数据被修改学生数据被修改!RAISERROR(text,16,1)RAISERROR(text,16,1)ROLLBACK TRANSACTION ROLLBACK TRANSACTION END END RAISEERROR(text,16,1)语句的作用是返回用户定义的语句的作用是返回用户定义的错误信息错误信息,并给出与信息关联并给出与信息关联的严重级别的严重级别(118)和有关错和有关错误调用状态的信息误调用状态的信息(1127)。这里分别取。这里分别取“16”和和“1”。使用T-SQL创建触发器【例5-69】在School数【例【例5-70】在】在School数据库的数据库的T_Grade表上创建一个触发器表上创建一个触发器tri_Grade，当向，当向T_Grade表中插入一条记录时，检查该记录的学号是否在表中插入一条记录时，检查该记录的学号是否在T_Student表中，如果不存在则取消插入操作表中，如果不存在则取消插入操作,否则显示否则显示“插入操作插入操作成功完成成功完成”。CREATE TRIGGER tri_GradeON T_GradeFOR INSERTAS DECLARE text varchar(50)IF EXISTS(SELECT*FROM inserted WHERE inserted.StudentCode NOT IN(SELECT StudentCode FROM T_Student)BEGIN SET text=学生的学号不存在，将取消该插入操作学生的学号不存在，将取消该插入操作 RAISERROR(text,16,1)ROLLBACK TRANSACTION ENDElse SET text=插入操作成功完成插入操作成功完成 SELECT text【例5-70】在School数据库的T_Grade表上创建一6.4.2 查看、修改和删除触发器查看、修改和删除触发器 1 1）在在企企业业管管理理器器，展展开开“表表”文文件件夹夹，右右击击触触发发器器所所属属的的表表，在在弹弹出出的的快快捷捷菜菜单单上上选选择择“所所有有任任务务/管管理理触触发发器器”命命令令，打打开开触触发发器器属属性性对对话话框框。在在名名称称栏栏可可选选择择已建立的触发器。修改方法同创建方法相同。已建立的触发器。修改方法同创建方法相同。2 2）如如要要删删除除，单单击击【删删除除】按按钮钮，在在弹弹出出的的确确认认对对话话框中单击【是】，即删除了所选择的触发器。框中单击【是】，即删除了所选择的触发器。6.4.2 查看、修改和删除触发器
展开阅读全文