第7章 存储过程和触发器管理.ppt

1、第7章存储过程和触发器管理、存储过程、7.1、触发器、7.2、2、本章要点、 创建存储过程查看和修改运行存储过程的参数存储过程的返回值关于删除存储过程触发器概述inserted和deleted表事务概念和应用程序创建触发器修改和删除重命名触发器在创建7.1存储过程SQLServer数据库应用程序时，Transact-SQL语言是应用程序和SQLServer数据库之间的主要编程接口。 Transact-SQL语句可以通过以下两种方式存储和执行： (本地保存Transact-SQL程序，并向SQLServer发送命令以创建处理结果的应用程序。 (可以将Transact-SQL程序保存到SQLServer。 也就是说，存储过程可以在本地创建存储过程和处理结果的应用程序。 包含一组Transact-SQL语句的代码块可以保存为存储过程。 服务器端处理数据库中的数据，并将结果返回给客户端。 这样可以消除客户端多次连接和访问数据库的需要，减少网络传输量，提高客户端工作效率(因为这些操作都在服务器端进行)。 4、4、4,7.1.1存储过程概述。 存储过程是集中存储在SQL服务器上的SQL语句和进程控制语句的预编译集合，用于实现任务(如查询)。 这些语句存储在名称下，并作为单元处理。 5、使用存储过程的好处可以减少客户端代码重复。 只需创建一次存储过程并将其存储在数据库中，客户端程序就可以多次调用该存储过程。 存储过程可以由数据库编程专家创建，并且可以独立于程序源代码单独修改。 可以更迅速地执行。 如果操作需要大量Transact-SQL代码或需要重复，则使用存储过程比在客户端上执行Transact-SQL批处理代码更快。 存储过程在服务器端预编译，生成查询计划并直接运行。 每次执行Transact-SQL语句时，客户机都会重复发送，并且每次执行语句时，SQLServer都会编译和优化客户机。 因此，存储过程运行得更快。 6、使用存储过程的好处来减少网络流量。 例如，需要数百行Transact-SQL代码的操作可以通过执行过程代码的单独语句实现，而无需在网络上发送数百行代码。 简化数据库管理。 例如，要修改某个查询，如果客户机上存在多个查询，则可以修改所有客户机上的查询，然后使用存储过程进行集中修改。 可作为安全机构使用。 例如，如果用户没有直接执行存储过程中的语句的权限，则可以授予他们执行存储过程的权限。 7、存储过程的分类、内置于系统存储过程：SQLServer中的存储过程的主要目的是执行SQLServer的管理功能，显示关于数据库和用户的信息。 系统存储过程名称以SP_开头，并且可以在任何数据库中运行系统存储过程。 用户存储过程：由用户自己创建并存储在用户数据库中的存储过程。 临时存储过程分为本地临时存储过程和全局临时存储过程。 8、存储过程分类，本地临时存储过程名称以#开头，存储在tempdb数据库中，仅供创建并连接的用户使用，并在该用户断开连接时自动删除本地临时存储过程。 全局临时存储过程名称以#开头，存储在tempdb数据库中，供所有连接的用户使用，并在所有用户断开连接时自动删除。 远程存储过程远程服务器上的存储过程。扩展存储过程：使用以外部语言(例如c )编写的存储过程来扩展新功能，以弥补SQLServer不足。9， 10，CREATEPROCEDURE语句，CREATEPROCEDURE存储过程名称，使用企业管理器菜单命令创建7.1.2存储过程。 编号参数数据类型VARYING=默认值OUTPUT、 n with recompile|encryption|recompile，ENCRYPTIONASSQL语句.n，11，参数说明，存储过程名称要创建本地临时存储过程，可以在存储过程名称前加#或者在存储过程名称前加#来创建全局临时过程。 完整名称(#或# )不得超过128个字符。 编号：可选整数，用于对具有相同名称的存储过程进行分组。 可以在DROPPROCEDURE语句中删除组存储过程。 12、参数说明、参数：进程中的参数。 CREATEPROCEDURE语句可以声明一个或多个参数。 用户在执行存储过程时，必须为每个声明的参数指定一个值(如果未定义参数的默认值)。 参数名称前必须有符号。 参数名称必须符合标识符的命名约定。 每个过程的参数仅用于过程本身。 其他过程也可以使用相同的参数名称。 缺省情况下，只能使用参数代替常量。 不能使用表名称、列名称或其他数据库对象的名称。 13、参数说明、数据类型：参数的数据类型。 所有数据类型(如text、ntext和image )都可以用作存储过程的参数。 但是，cursor数据类型仅适用于OUTPUT参数。 如果指定的数据类型为cursor，则必须同时指定VARYING和OUTPUT关键字。 VARYING :指定支持的结果集作为输出参数(由存储过程动态配置，内容可更改)。 仅适用于游标参数。 14、参数说明、默认值：参数的默认值。 如果定义了默认值，则可以执行存储过程，而无需为参数指定值。 默认值必须为常量或NULL。 如果在存储过程中使用LIKE关键字，则默认值可以包含通配符(%、_、和 )。 OUTPUT :表示参数是返回值。 此选项的值可以返回到EXECUTE。 使用OUTPUT参数将信息返回给调用过程。 text、ntext和image参数可用作OUTPUT参数。 使用OUTPUT关键字的输出参数可以是游标占位符。 15、参数说明，RECOMPILE :表示运行时重新编译的存储过程的执行计划尚未保存。 encryption :指定SQLServer加密syscomments表中包含CREATEPROCEDURE语句文本的条目。 AS指定存储过程要执行的操作。 SQL语句：要包括在存储过程中的Transact-SQL语句。 16、例7-1、编制存储过程“成绩增加”，使表“学生”中所有学生的入学成绩增加10%。 USE学生管理GOCREATEPROCEDURE增加成绩ASUPDATE学生SET入学成绩=入学成绩10GO、17、情况7-1中，在创建存储过程时，如果已经存在同名存储过程，则不允许创建新的存储过程。 可以把上述代码改写成“使用学生管理”。 有“提高成绩”存储过程时，ifexists (selectinamefromsysobjectswherename=增加成绩ANDtype=P)DROPPROCEDURE增加成绩GOCREATEPROCEDURE增加成绩ASUPDATE学生see 删除18，使用企业管理器创建存储过程，在企业管理器中，展开要为其创建存储过程的数据库，用鼠标右键单击存储过程，然后从弹出式菜单中选择“新建存储过程” 使用企业管理器创建存储过程。 此对话框中的文本框包含默认语句create procedure owner . procedurename as，您必须自己输入存储过程的特定文本。例如，提高CREATEPROCEDURE成绩的ASUPDATE学生SET的入学成绩=入学成绩10GO，20， 7.1.3执行存储过程EXECUTE返回状态=存储过程名称|存储过程名称变量参数名称=值|变量OUTPUT|DEFAULT， n with recomping 在将此变量用于EXECUTE语句之前，必须在批处理、存储过程或函数中声明它。 存储过程名称要调用的存储过程的名称。 存储过程名称变量：表示存储过程名称的本地变量名称。 参数名称： CREATEPROCEDURE语句中定义的存储过程的参数。 必须在参数名称前加上符号。 使用格式 参数=值时，参数名称和常量不一定按照CREATEPROCEDURE语句中定义的顺序显示。 但是，如果一个参数使用 参数=值格式，则所有其他参数都必须使用此格式。 22、参数说明、值：流程中的参数值。 如果未指定参数名称，则必须按照CREATEPROCEDURE语句中定义的顺序指定参数值。 如果为CREATEPROCEDURE语句定义了默认值，则用户在执行存储过程时不需要指定相应的参数。 如果存储过程使用带有LIKE关键字的参数名称，则默认值必须是常量，并且可以包括%、_、和通配符。 默认值也可以为NULL。 通常，在定义存储过程时，指定参数值为NULL时要执行的操作。 变量：保存参数或返回参数的变量。 23、参数说明，OUTPUT :指定存储过程必须返回参数。 存储过程的匹配参数也必须使用关键字OUTPUT创建。 使用此关键字可以将光标变量用作参数。 DEFAULT :根据存储过程的定义指定参数的默认值。 如果过程需要的参数值没有预定义、没有参数或指定了DEFAULT关键字，则会发生错误。 WITHRECOMPILE :强制编译新计划。 如果指定的参数是非典型参数，或者数据发生了很大变化，请使用此选项。 将变更后的计划用于今后的程序执行。 此选项不能用于扩展存储过程。 由于此选项会占用大量系统资源，因此建议您尽量不要使用此选项。 24、例7-2、执行存储过程“提高成绩”，确认执行结果。 SQLServer2000支持在存储过程中使用参数，以提高USE学生管理GOEXEC成绩SELECT*FROM学生GO、执行结果：25、7.1.4存储过程的参数和存储过程的灵活性。 存储过程有两种参数：用于将数据导入存储过程的输入参数和用于将存储过程中的数据返回给调用方的输出参数。 定义存储过程时，还可以使用以下格式指定参数： 参数名称数据类型=默认值OUTPUT， n参数后面的OUTPUT关键字表示输出参数。 26、示例7-3、创建存储过程add_proc，计算并输出两个参数的总和。 存储过程createprocedure_procnum1INT=0、 num2int=0as declare num3intset num3= num1 num2print num 3包括两个参数num1，即输入参数27、执行例7-3、无参数执行： EXECadd_proc执行结果：带0参数执行： EXECadd_proc、25执行结果： 38，根据情况需要将存储过程中的计算结果返回给调用程序，为了进行进一步的处理，需要在存储过程中输出路径28、示例7-4、创建存储过程add_proc1，计算两个参数的和，并使用输出参数返回结果。 createprocedureeadd _ proc1 num1int=0、num2INT=0、num3INTOUTPUT-num3是输出参数asset num3= num1 num 2，29，执行示例7-4，declare numasintexe 23 numOUTPUTPRINTnum的运行结果为38，30，示例7-5，创建一个存储过程AvgScore，根据指定的学科名称和类名称计算平均成绩，并使用输出参数返回结果。createprocedureprocedureeavgscore org varchar (100 )、-系名、输入参数classvarchar(50 )、-类名、输入参数scorefloatOUTPUT-成绩、 输出参数as declare orgid intset org GID=0，31， 情况7-5 -基于参数中指定的学部名称org，在学部编号SELECTorgid=记录编号FROM学部WHERE学部名称= org if orgid=0begin set score=0print中指定的学部记录， ENDELSEBEGINSELECTscore=AVG (入学成绩)不存在FROM学生WHERE所属学部=orgidAND班=classGROUPBY所属学部、班级END，32，执行例7-5， DECLAREscorefloatEXECAvgScore软件系列，2班，scoreOUTPUTPRINTscore的执行结果是584，33，7.1.5存储过程的返回值，存储过程中使用RETURN语句的状态值、34、例7-6、制作存储过程AvgScore1，根据给定的学部名和班名计算平均成绩，用输出参数返回结果。 如果有指定的学部存在，返回1，否则返回0。 createprocedureprocedureeavgscore1 org varchar (100 )、classvarchar(50 )、 scoreefloatoutpututasdelare orgid=0， 35、例7-6、-基于由参数指定的学部名org，学部编号SELECTorgid=记录编号FROM学部WHERE学部名= org if orgid=0return0else begin select score=avg (入学成绩) FROM学生WHERE所属学部=orgidAND类=classGROUPBY所属学部类RETURN1ENDGO，36，执行例7-6， 与dec