《SQLServer2005程序 设计 语言》由会员分享，可在线阅读，更多相关《SQLServer2005程序 设计 语言（67页珍藏版）》请在金锄头文库上搜索。
1、SQL Server 2005 数据库程序设计,第10章 存储过程,存储过程概述 设计存储过程 创建存储过程 修改存储过程 执行存储过程 删除存储过程 常用系统存储过程 建立CLR存储过程,第10章 存储过程,在SQL Server中, 存储过程既子程序就是将常用的或复杂的工作，预先用SQL语句写好并用指定的名称存储起来。用EXECUTE执行。 存储过程 是存储在服务器上的 T-SQL 语句的命名集合 是封装重复性任务的方法 支持用户声明变量、条件执行以及其他强有力的编程特性,10.1 存储过程概述,SQL Server 中的存储过程可以 包含执行数据库操作（包括调用其他过程）的编程语句 接受
2、输入参数 向调用过程或批处理返回状态值，以表明成功或失败（以及失败原因） 以输出参数的形式将多个值返回至调用过程或批处理,10.1 存储过程概述,存储过程 - - -,单个 SELECT 语句,SELECT 语句块,SELECT语句与逻辑控制语句,可以包含,存储过程可以包含数据操纵语句、变量、逻辑 控制语句等,10.1 存储过程概述,10.1 存储过程概述,在SQL Server2005中，按编写的语言，存储过程有两种类型：T-SQL或CLR。 T-SQL（SQL Server的存储过程）：T-SQL存储过程是指保存的T-SQL语句集合。 CLR：是指对.NET公共语言运行时(CLR)方法的引
3、用。,SQL Server 支持的T-SQL存储过程 系统存储过程（sp_）：存储在 master 数据库内，以“sp_”前缀标识 本地存储过程：在单独的用户数据库内创建 临时存储过程：可能是局部的，名称以“#”开头；也可能是全局的，名称以“#”开头 远程存储过程：是指从远程服务器上调用的存储过程，分布式查询支持这项功能 扩展存储过程（sp_或xp_）：在 SQL Server 环境外执行,10.1 存储过程概述,关于系统存储过程的前缀 sp_ 所有的系统存储过程的名字都以 sp_ 开头 任何创建在 master 数据库中的以“sp_”开头的存储过程都可以在其他数据库中被直接调用而不需要用数据
4、库名完全引用，不必使用完全合法名称,10.1 存储过程概述,执行扩展存储过程,常用的扩展存储过程 例：exec xp_cmdshell dir d:,EXEC sp_configure show advanced option, 1; RECONFIGURE; EXEC sp_configure xp_cmdshell, 1; RECONFIGURE;,使用存储过程的优点： 只在创造时进行编译 ，执行速度快 提高系统性能 确保数据库的安全 自动完成需要预先执行的任务 可重复使用,可减少数据库开发人员的工作量,10.1 存储过程概述,创建存储过程 只能在当前数据库内创建存储过程，除了临时存储过程
5、。临时存储过程总是创建在 tempdb 数据库中 存储过程可以引用表、视图、用户定义函数、其他存储过程以及临时表 若存储过程创建了局部临时表，则当存储过程执行结束后临时表消失 权限,10.1 存储过程概述,存储过程概述 设计存储过程 创建存储过程 修改存储过程 执行存储过程 删除存储过程 常用系统存储过程 建立CLR存储过程,第10章 存储过程,10.2 设计存储过程,用户可以设计自己的存储过程，以提高业务逻辑使用的通用性。设计存储过程通常应该遵循以下原则： 存储过程名称避免使用sp的前缀 尽量不使用临时存储过程。 CREATE/ALTER PROCEDURE 必须是批处理的第一个语句。 使用
6、CREATE PROCEDURE定义SQL语句集合（不能使用下表中的语句）：,10.2 设计存储过程,存储过程概述 设计存储过程 创建存储过程 修改存储过程 执行存储过程 删除存储过程 常用系统存储过程 建立CLR存储过程,第10章 存储过程,10.3.1 在SQL Server Management Studio中建立存储过程,用户可以使用SQL Server Management Studio工具建立存储过程,10.3.2 用CREATE PROCEDURE语句创建存储过程,创建存储过程 只能在当前数据库内创建存储过程，除了临时存储过程。临时存储过程总是创建在 tempdb 数据库中 存储
7、过程可以引用表、视图、用户定义函数、其他存储过程以及临时表 若存储过程创建了局部临时表，则当存储过程执行结束后临时表消失,USE Northwind GO CREATE PROC dbo.OverdueOrders AS SELECT * FROM dbo.Orders WHERE RequiredDate GETDATE() AND ShippedDate IS Null,语法 CREATE PROCEDURE schema_name.proceduce_name;number /*定义存储过程名，number对同名的过程分组*/ parameter data_type /*定义参数类型 V
8、ARYING =default OUTPUT /*定义参数属性 ,n WITH RECOMPILE | ENCRYPTION /*定义存储过程的处理方式 AS sql_statement n /* 执行的操作,10.3.2 用CREATE PROCEDURE语句创建存储过程,注释： 存储过程名必须符合标识符规则。 Number为可选参数，用于区分同名的存储过程 parameter为存储过程的形参， data_type指定数据类型 Default指定存储过程输入参数的默认值，OUTPUT指定参数从存储过程的返回信息。 RECOMPILE表明每次运行该过程，将对其重新编译，ENCRYPTION对包
9、含创建存储过程的文本加密。 sql_statement代表过程体包含的T-SQL语句,10.3.2 用CREATE PROCEDURE语句创建存储过程,-示例：使用带有复杂 SELECT 语句的简单过程 -下面的存储过程从四个表的联接中返回所有作者、出版的书籍以及出版社。该存储过程不使用任何参数。 USE pubs if object_id(au_info_all,P) is not null DROP PROCEDURE au_info_all GO CREATE PROCEDURE au_info_all AS SELECT au_lname, au_fname, title, pub_n
10、ame FROM authors a INNER JOIN titleauthor ta ON a.au_id = ta.au_id INNER JOIN titles t ON t.title_id = ta.title_id INNER JOIN publishers p ON t.pub_id = p.pub_id,查看存储过程的信息 查看所有类型存储过程的额外信息 系统存储过程 sp_help、sp_helptext、sp_depends 显示数据库中的存储过程以及拥有者名字的列表 系统存储过程 sp_stored_procedures 得到存储过程的信息 查询系统视图 sys.sys
11、objects、sys.syscomments、sys.sysdepends,10.3.2 用CREATE PROCEDURE语句创建存储过程,嵌套存储过程：一个存储过程调用另一个 存储过程可以嵌套32层。若试图超过32层嵌套，则整个存储过程调用链失败 当前的嵌套层数存储在系统函数 nestlevel 中 若一个存储过程调用了第二个存储过程，那么第二个存储过程可以访问第一个存储过程创建的所有对象，包括临时表 嵌套的存储过程可以递归调用。,10.3.2 用CREATE PROCEDURE语句创建存储过程,CREATE PROCEDURE innerproc as select NESTLEVEL
12、 AS Inner Level,示例 1,CREATE PROCEDURE outerproc as select NESTLEVEL AS Outer Level EXEC innerproc,示例 2,10.3.2 用CREATE PROCEDURE语句创建存储过程,重命名存储过程,重命名存储过程 sp_rename oldname,newname,限定存储过程所引用的对象名称,创建存储过程的准则,每个任务创建一个存储过程,创建，测试存储过程，并对其进行故障诊断,存储过程名称避免使用 sp_ 前缀,尽可能减少临时存储过程的使用,存储过程的参数分两种： 输入参数 输出参数 输入参数： 用于向
13、存储过程传入值，类似C语言的按值传递; 输出参数： 用于在调用存储过程后， 返回结果，类似C语言的按引用传递;,int sum (int a, int b) int s; s=a+b; return s; ,c=sum（5, 8）,传入参数值,返回结果,存储过程的参数,输入参数允许传递信息到存储过程内 在 CREATE PROCEDURE 中指定 参数名 数据类型 =默认值 指定参数的原则 所有的输入参数值都应在存储过程开始时进行检查，以尽早捕获缺失值和非法值的情况 应为参数提供合适的默认值。若定义了默认值，用户可以在未指定参数值的基础上执行存储过程 参数对存储过程而言是局部的。,输入参数,输
14、入参数,示例：,CREATE PROC Production.LongLeadProducts MinimumLength int AS IF (MinimumLength = MinimumLength ORDER BY DaysToManufacture DESC, Name,EXEC Production.LongLeadProducts MinimumLength=4,CREATE PROCEDURE dbo.GetProducts2 name varchar(50) AS SELECT * FROM Production.Product WHERE Name = name,输入参数,
15、示例：,以下执行语句均正确 exec GetProducts2 Hex Nut 22 exec GetProducts2 name=Hex Nut 22 GetProducts2 Hex Nut 22,输入参数,通过参数名传递值 在 EXECUTE 语句中以“参数名=值”的格式指定参数称为通过参数名传递 当通过参数名传递值时，可以以任何顺序指定参数值，并且可以省略允许空值或具有默认值的参数 若定义了参数的默认值，当调用存储过程时，参数课未指定值或者参数的值指定为 DEFAULT 关键字,输入参数,输入参数,提供合适的默认值，验证输入参数值，包括空值检查,CREATE PROC Producti
16、on.LongLeadProducts MinimumLength int = 1 - default value AS IF (MinimumLength = MinimumLength ORDER BY DaysToManufacture DESC, Name,EXEC Production.LongLeadProducts,通过位置传递参数 只传递值（而没有对被传值参数的引用）称为通过位置传递 参数值必须以参数在 CREATE PROCEDURE 语句中的定义顺序列出 可以忽略有默认值的参数，但不能中断次序,输入参数,CREATE PROCEDURE dbo.sum1 a int, b int AS DECLARE sum int SET sum=a+b print sum,输入参数,以
