第9章存储过程.ppt

学习要点： 存储过程的基本概念 创建存储过程 管理存储过程等内容 9.1 存储过程的基本概念 9.2 创建存储过程 9.3 执行存储过程 9.4 存储过程的参数 9.5 存储过程的返回值 9.6 存储过程的查看、修改和删除 9.1 存储过程概述 9.1.1 存储过程的基本概念 存储过程是独立存在于表之外的数据库对象，由被编译在一起的一组Transact-SQL语句组成。它可以被客户调用，也可以被另一个存储过程或触发器调用，它的参数可以被传递，它的出错代码也可以被检验。 在SQL Server中，使用存储过程的优点如下： ① 存储过程在服务器端运行，执行速度快。 ② 存储过程执行一次后，其执行规划就驻留在高速缓冲存储器，在以后的操作中，只需从高速缓冲存储器中调用已编译好的二进制代码执行，提高了系统性能。 ③ 确保数据库的安全。使用存储过程可以完成所有的数据库操作，并可通过编程方式控制上述操作对数据库信息访问的权限。 ④ 自动完成需要预先执行的任务。存储过程可以在系统启动时自动执行，而不必在系统启动后再进行手工操作，大大方便了用户的使用，可以自动完成一些需要预先执行的任务。 9.1.2 存储过程的类型 SQL Server支持五种类型的存储过程：系统存储过程、本地存储过程、临时存储过程、远程存储过程和扩展存储过程。在不同情况下需要执行不同的存储过程。 1．系统存储过程 系统存储过程是由系统提供的存储过程，可以作为命令执行各种操作。系统存储过程定义在系统数据库master中，其前缀是sp_ 。 2．本地存储过程 本地存储过程是指在用户数据库中创建的存储过程，这种存储过程完成特定数据库操作任务，其名称不能以sp_为前缀。 3．临时存储过程 临时存储过程属于本地存储过程。如果本地存储过程的名称前面有一个“#”，该存储过程就称为局部临时存储过程，这种存储过程只能在一个用户会话中使用；如果本地存储过程的名称前有两个“##”，该过程就是全局临时存储过程，这种存储过程可以在所有用户会话中使用。 4．远程存储过程 远程存储过程指从远程服务器上调用的存储过程。 5．扩展存储过程 在SQL Server环境之外执行的动态链接库称为扩展存储过程，其前缀是sp_。使用时需要先加载到SQL Server系统中，并且按照使用存储过程的方法执行。 9.2 存储过程的创建 默认情况下，用户创建的存储过程归数据库所有者拥有，数据库的所有者可以把许可授权给其他用户。 存储过程由CREATE PROCEDURE语句创建，存储过程的定义包括：过程名和参数的说明以及过程体，即包含执行存储过程操作的Transact-SQL语句。要使用存储过程，首先要创建一个存储过程。 9.2.1 使用CREATE PROCEDURE语句创建 1．语法格式 CREATE PROC [EDURE] procedure_ name [;number] [{@parameter data_type} [VARYING] [=default] [OUTPUT]] [ …n ] [ WITH {RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION } ] [FOR REPLICATION] AS sql_statement […n] 2．参数说明 procedure_name：新存储过程的名称。过程名必须符合标识符规则，且对于数据库及其所有者必须惟一。 如果要创建局部临时过程，可以在procedure_name前面加一个编号符#，要创建全局临时过程，可以在procedure_name前面加两个编号符##procedure_name。完整的名称包括（#或##）不能超过128个字符。指定过程所有者的名称是可选的。 number：是可选的整数，用来对同名的过程分组，以便用一条[DROP PROCEDURE]语句即可将同组的过程一起除去。 @parameter：过程中的参数。在[CREATE PROCEDURE]语句中可以声明一个或多个参数。用户必须在执行过程时提供每个所声明参数的值（除非定义了该参数的默认值）。存储过程最多可以有2100个参数。使用@符号作为第一个字符来指定参数名称。参数名称必须符合标识符的规则。每个过程的参数仅用于该过程本身；相同的参数名称可以用在其他过程中。默认情况下，参数只能代替常量，而不能用于代替表名、列名或其