存储过程与触发器

系统第九章存储过程与触发器前台后台数据库调用包含用户的操作界面。例如：登陆界面、商品入库界面、卖出商品界面等第九章存储过程与触发器前台后台数据库执行select…命令，并将结果返回传递商品查询界面根据关键词写出select…语句返回第九章存储过程与触发器前台后台数据库自动执行已编写好的命令，将结果返回调用商品查询界面根据关键词写出调用语句返回第九章存储过程与触发器存储过程综述存储过程的基本操作创建和执行带有参数的存储过程存储过程重新编译系统存储过程和扩展存储过程第九章存储过程与触发器存储过程综述1、存储过程概念存储过程是一种数据库对象，是为了实现某个特定任务，将一组预编译的SQL语句以一个存储单元的形式存储在服务器上，供用户调用。存储过程在第一次执行时进行编译，然后将编译好的代码保存在高速缓存中以便以后调用，这样可以提高代码的执行效率。存储过程同其他编程语言中的过程相似，有如下特点：1）接受输入参数并以输出参数的形式将多个值返回至调用过程或批处理。2）包含执行数据库操作（包括调用其它过程）的编程语句。3）向调用过程或批处理返回状态值，以表明成功或失败（以及失败原因）。第九章存储过程与触发器存储过程综述优点：1、安全机制：只给用户访问存储过程的权限，而不授予用户访问表和视图的权限。2、改良了执行性能：在第一次执行后，会在SQLserver的缓冲区中创建查询树，以后执行无需编译。3、减少网络流量：存储过程存在于服务器上，调用时，只需传递执行存储过程的执行命令和返回结果。4、模块化的程序设计：增强了代码的可重用性，提高了开发效率。第九章存储过程与触发器存储过程综述2、存储过程类型系统存储过程：系统存储过程存储在master数据库中，并以sp_为前缀，主要用来从系统表中获取信息，为系统管理员管理SQLServer提供帮助，为用户查看数据库对象提供方便。本地存储过程：本地存储过程是用户根据需要，在自己的普通数据库中创建的存储过程。临时存储过程：临时存储过程通常分为局部临时存储过程和全局临时存储过程。创建局部临时存储过程时，要以“#”作为过程名称的第一个字符。创建全局临时存储过程时，要以“##”作为过程名称的前两个字符。远程存储过程：远程存储过程是SQLServer2000的一个传统功能，是指非本地服务器上的存储过程。扩展存储过程：扩展存储过程以xp_为前缀，它是关系数据库引擎的开放式数据服务层的一部分，其可以使用户在动态链接库(DLL)文件所包含的函数中实现逻辑，从而扩展了Transact-SQL的功能，并且可以象调用Transact-SQL过程那样从Transact-SQL语句调用这些函数。第九章存储过程与触发器存储过程的基本操作---创建创建存储过程时，需要注意下列事项：1、只能在当前数据库中创建存储过程。2、数据库的所有者可以创建存储过程，也可以授权其他用户创建存储过程。3、存储过程是数据库对象，其名称必须遵守标识符命名规则。4、不能将CREATEPROCEDURE语句与其它SQL语句组合到单个批处理中。5、创建存储过程时，应指定所有输入参数和向调用过程或批处理返回的输出参数、执行数据库操作的编程语句和返回至调用过程或批处理以表明成功或失败的状态值。第九章存储过程与触发器存储过程的基本操作---创建使用SQL语句创建不带参数的存储过程语法格式如下：CREATEPROC[EDURE]procedure_name[WITH{RECOMPILE|ENCRYPTION}]ASsql_statement[...n]procedure_name：新存储过程的名称。过程名必须符合标识符规则，且对于数据库及其所有者必须唯一。RECOMPILE：SQL不会缓存该过程的计划，该过程将在运行时重新编译。ENCRYPTION：SQLServer加密使用ENCRYPTION可防止将过程作为SQLServer复制的一部分发布。第九章存储过程与触发器存储过程的基本操作---创建例、在STUDENT数据库中，创建一个查询存储过程ST_PROC_BJ，该存储过程将返回计算机系的班级名称。其程序清单如下：USESTUDENTGOCREATEPROCDBO.ST_PROC_BJASSELECT班级名称FROM班级,系部WHERE系部.系部代码=班级.系部代码and系部.系部名称='计算机系'GO第九章存储过程与触发器存储过程的基本操作---执行执行存储过程对存储在服务器上的存储过程，可以使用EXECUTE命令或其名称执行它，其语法格式如下：[EXEC[UTE]]procedure_name[number]}如果存储过程是批处理中的第一条语句，EXECUTE命令可以省略，可以使用存储过程的名字执行该存储过程。例：在查询分析器中执行存储过程ST_PROC_BJ，其代码清单如下：USESTUDENTEXECUTEST_PROC_BJGO第九章存储过程与触发器存储过程的基本操作---创建使用SQL语句创建带参数的存储过程语法格式如下：CREATEPROC[EDURE]procedure_name[number][{@parameterdata_type}[VARYING][=default][OUTPUT]][,...n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]ASsql_statement[...n]第九章存储过程与触发器存储过程的基本操作---创建其中：procedure_name：新建存储过程的名称，其名称必须符合标识符命名规则，且对于数据库及其所有者必须唯一。number：该参数是可选的整数，用来对同名的过程分组，以便用一条DROPPROCEDURE语句即可将同组的过程一起删除。例如，名为orders的应用程序使用的过程可以命名为orderproc1、orderproc2等。DROPPROCEDUREorderproc语句将删除整个组。parameter：存储过程中的输入和输出参数。data_type：参数的数据类型。Default：指参数的默认值，必须是常量或NULL。如果定义了默认值，不必指定该参数的值也可执行过程。第九章存储过程与触发器存储过程的基本操作---创建其中：OUTPUT：表明参数是返回参数。该选项的值可以返回给EXEC[UTE]。使用OUTPUT参数可将信息返回给调用过程。RECOMPILE：表明SQLServer不保存存储过程的计划，该过程将在运行时重新编译。ENCRYPTION：表示SQLServer加密syscomments表中包含CREATEPROCEDURE语句文本的条目。sql_statement：指存储过程中的任意数目和类型的Transact-SQL语句。在存储过程中使用参数，可以扩展存储过程的功能。使用输入参数，可以将外部信息传入到存储过程；使用输出参数，可以将存储过程内的信息传出到外部。第九章存储过程与触发器存储过程的基本操作---创建例：在STUDENT数据库中，建立一个名为XIBU_INFOR的存储过程，它带有一个参数，用于接受系部代码，显示该系部名称和系主任信息。其程序清单如下：USESTUDENTGOCREATEPROCEDUREXIBU_INFOR@系部代码CHAR(2)ASSELECT系部名称,系主任FROM系部WHERE系部代码=@系部代码GO第九章存储过程与触发器存储过程的基本操作---执行执行待参数的存储过程对存储在服务器上的存储过程，可以使用EXECUTE命令或其名称执行它，其语法格式如下：[EXEC[UTE]]{[@return_status=]procedure_name[number]}[[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]][,...n]其中：如果存储过程是批处理中的第一条语句，EXECUTE命令可以省略，可以使用存储过程的名字执行该存储过程。@return_status：是一个可选的整型变量，用来保存存储过程的返回状态。@parameter：存储过程的参数。例如：执行存储过程：EXECXIBU_INFOR‘01’第九章存储过程与触发器存储过程的基本操作---创建例：usepubsgocreateproceduretitle_sum@titlevarchar(40)=‘%’,@summoneyoutputasselect‘titlename’=titlefromtitleswheretitlelike@titleselect@sum=sum(price)fromtitleswheretitlelike@titlego第九章存储过程与触发器存储过程的基本操作---执行执行：declare@totalcostmoneyexecutetitle_sum‘the%’,@totalcostoutputif@totalcost=200beginselect‘thetotalcostofthesetitleis$’+rtrim(cast(@totalcostasvarchar(20)))注意：必须在createprocedure和execute语句中都指定output关键字。如果output关键字在存储过程执行过程中被忽略了，存储过程仍然执行，但是他将会产生一个错误状态。第九章存储过程与触发器存储过程的基本操作---创建使用企业管理器例：在STUDENT数据库中，创建一个名称为ST_CHAXUN_01的存储过程，该存储过程返回计算机系学生的姓名、性别和年龄信息。其操作步骤如下：1）打开企业管理器，展开控制台目录，依次展开服务器组、服务器、数据库节点。2）单击相应的数据库（这里我们选择student数据库），在其右边的详细窗格中右击“存储过程”图标，在弹出的快捷菜单中选择“新建存储过程”命令。3）执行“新建存储过程”命令，打开“存储过程属性”对话框。在其文本框中首先输入所有者和存储过程名称，例如用所有者DBO替换OWNER，用过程名ST_CHAXUN_01替换PROCEDURENAME。4）在文本框的第二行输入存储过程文本，根据题意输入如下语句：SELECT姓名,性别,YEAR(GETDATE())-YEAR(出生日期)AS年龄FROM学生WHERE系部代码='01'5）输入完成后，单击“语法检查”按钮，检查语法是否正确，然后单击“确定”。第九章存储过程与触发器存储过程的基本操作---查看1、使用企业管理器查看存储过程的步骤为：1）打开企业管理器，展开控制台目录，依次展开服务器组、服务器、数据库节点。2）单击相应的数据库（这里我们选择student数据库），选择“存储过程”节点，在右边的列表中显示出当前数据库中所有的存储过程。3）选择需要查看的存储过程右击，例如“ST_PROC_BJ”，在弹出的快捷菜单中选择“属性”命令，打开“存储过程属性”对话框。4）在“属性”对话框中，既可以查看过程定义信息，又可以在文本框中对存储过程的定义进行修改。修改后，可以单击“应用”或“确定”按钮，保存修改。第九章存储过程与触发器存储过程的基本操作---查看2、使用系统存储过程查看存储过程信息在SQLServer中，根据不同需要，可以使用sp_helptext、sp_depends、sp_help等系统存储过程来查看存储过程的不同信息。具体作用和语法如下：sp_helptext查看存储过程的文本信息，其语法格式为：sp_helptext存储过程名sp_depends查看存储过程的相关性，其语法格式为：sp_depends存储过程名sp_help查看存储过程的一般信息，其语法格式为：sp_help存储过程名例：使用有关系统过程查看STUDENT数据库中名为ST_PROC_BJ的存储过程的定义、相关性以及一般信息。其程序清单如下：USESTUDENTGOEXECSP_HELPTEXTST_PROC_BJEXECSP_DEPENDSST_PROC