SQLServer存储过程与触发器.pptVIP

第4章 SQL Server 存储过程与触发器 4.1 SQL Server 存储过程 4.1.1存储过程概述 存储过程(Stored Procedure)是一组为了完成特定功能T-SQL语句集合，经编译后存储在SQL Server服务器端数据库中。存储过程可以分为两类：系统存储过程和自定义存储过程。 系统存储过程 系统存储过程 系统存储过程在SQL Server安装成功后，就已经存储在系统数据库Master中，这些存储过程都是以sp_为前缀命名的 它们主要是从系统表中获取信息，系统管理员可以通过简单调用系统存储过程而完成复杂的SQL Server管理工作。可以通过系统存储过程完成许多管理性或信息的操作。 系统存储过程在Master数据库中，在其他数据库中可以直接调用，调用时不必在存储过程名前加上数据库名。 自定义存储过程 自定义存储过程是由用户创建并能完成某一特定功能的存储过程。 存储过程的优点 1．提高应用程序的通用性和可移植性 2．可以更有效地管理用户操作数据库的权限 3．可以提高T-SQL的速度 4．减轻服务器的负担 5．块化程序设计。 6．减少操作错误。 7．能自动处理复杂的或敏感的事务。 8．可以实现管理任务自动化。 4.1.2存储过程的创建与执行 创建前确定 所有的输入参数以及传给调用者的输出参数。 被执行的针对数据库的操作语句，包括调用其它存储过程的语句。 返回给调用者的状态值，以指明调用是成功还是失败。 一个存储过程的最大尺寸为128M 1.直接创建存储过程 (1)打开Microsoft SQL Server Manager管理器 (2)单击数据库前面的“+”号，然后单击“Material_Data1”数据库前面的“+”号，再单击“可编程性”前面的“+”号，选择“存储过程”，单击鼠标右键，在弹出的快捷菜单中单击“新建存储过程”命令。 (3)打开了一个创建存储过程的数据库引擎查询模板，修改相应参数即可。 直接创建存储过程 2.代码创建存储过程 语法 CREATE PROC[EDURE] procedure_name[;number] [{@parameter data_type} [VARYING][=default][OUTPUT]][,...n]  WITH????{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}] AS sql_statement [ ...n ] begin 命令行或命令块 end 说明 procedure_name：用于指定要创建的存储过程的名称。 number：该参数是可选的整数，它用来对同名的存储过程分组，以便用一条 DROP PROCEDURE 语句即可将同组的过程一起除去。 @parameter：过程中的参数。在 CREATE PROCEDURE 语句中可以声明一个或多个参数。 data_type：用于指定参数的数据类型。 Default：用于指定参数的默认值。 OUTPUT：表明该参数是一个返回参数。 recompile:表示每次执行此存储过程时都重新编译一次 encryption:所创建的存储过程的内容会被加密 3.代码执行存储过程 利用T-SQL执行存储过程的语法格式如下： execute 过程名[参数值，…[output] (1)没有参数的存储过程创建 create proc hyprocl as select * from manager where wage 1800 执行该存储过程 execute hyprocl (2) 有参数存储过程创建 create proc hyproc2 @mingz int, @maxgz int as select * from manager where wage between @mingz and @maxgz 执行实例 假设要显示工资在1000到2000之间的manager信息，具体代码： execute hyproc2 1005，1800 4.1.3 修改存储过程 修改存储过程具体格式如下： alter proc过程名 @parameter参数类型 @parameter参数类型output as sql_statement [ ...n ] begin 命令行或命令块 end 实例 修改存贮过程hyproc2, 输出manager性别分类人员数与总工资。 alter proc hyproc2