• 1. 第8章 存储过程 存储过程简介8.1 创建存储过程8.2执行存储过程8.3 修改和删除存储过程8.4
  • 2. 在SQL Server数据库系统中,存储过程具有很重要的作用。存储过程是T-SQL语句的集合,它提供了一种高效和安全的访问数据库的方法,经常被用来访问数据和管理被修改的数据。SQL Server 2005不仅提供了用户自定义存储过程的功能,而且也提供了许多可作为工具使用的系统存储过程。本章重点介绍存储过程的概念、存储过程的创建和管理,并介绍存储过程的一些应用技巧。
  • 3. 8.1 存储过程简介 8.1.1 存储过程的优点 8.1.2 存储过程的类别
  • 4. 存储过程存放在服务器端数据库中,是经编译过的能完成特定功能的T-SQL语句的集合,是作为一个单元来处理的。在存储过程中可以对任何数据及对象进行修改,包括新建或删除表、修改数据库设置等。
  • 5. (1)执行速度快,改善系统性能。 (2)减少网络流量。 (3)增强代码的重用性和共享性。 (4)提供了安全机制。8.1.1 存储过程的优点
  • 6. 8.1.2 存储过程的类别1.用户自定义存储过程(1)Transact-SQL存储过程。 (2)CLR存储过程。
  • 7. 2.系统存储过程 系统存储过程主要从系统表中查询信息或完成与更新数据库表相关的管理任务或其他的系统管理任务。存储在master数据库中,可以在其他数据库中任意进行调用,由前缀“sp_”标识。
  • 8. 3.扩展存储过程 扩展存储过程以在SQL Server环境外执行的动态链接库(DLL)来实现。
  • 9. 8.2.1 使用图形化工具创建 存储过程 8.2.2 使用Transact-SQL创建 存储过程 8.2 创建存储过程
  • 10. 我们将在Sales数据库中,创建一个名为proc_price查询商品编号、商品名称、进货价、零售价的存储过程,步骤如下。 (1)启动SSMS,连接到数据库实例,在“对象资源管理器”窗口里,展开数据库实例。8.2.1 使用图形化工具创建存储过程
  • 11. (2)依次选择“数据库”→要存放存储过程的数据库,这里选择“Sales”→“可编程性”→“存储过程”,右键快捷菜单的“新建存储过程”选项,如图所示。
  • 12. “创建存储过程”模板
  • 13. (3)选择“查询”菜单→“指定模板参数的值”选项,这时打开了“指定模板参数的值”对话框,如图所示。
  • 14. “指定模板参数的值”对话框
  • 15. (4)设置好相应的参数值。单击“确定”按钮,返回到创建存储过程的窗口,此时内容已经改变,如图所示。
  • 16. “指定模板参数的值”对话框
  • 17. (5)由于该存储过程无参数,在“创建存储过程”窗口中,把参数的代码“@p1 int = 0,@p2 int = 0”删除,将代码“SELECT @p1,@p2”更改为“SELECT 商品编号,商品名称,进货价,零售价 FROM Goods”。 (6)单击SQL编辑器工具栏上的 按钮,完成存储过程的创建。
  • 18. 指定模板参数后的“创建存储过程”窗口
  • 19. SQL Server使用CREATE PROCEDURE语句用于创建存储过程,语法格式如下: CREATE PROC[EDURE] [所有者.]存储过程名[;整数] [{@参数 数据类型}[VARYING][= 默认值][OUTPUT][,...n] [WITH {RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION|EXECUTE_AS_Clause}] [FOR REPLICATION] AS {SQL语句[,...n]|EXTERNAL NAME assembly_name.class_name.method_name}8.2.2 使用Transact-SQL创建存储过程
  • 20. 8.3 执行存储过程 8.3.1 使用EXECUTE语句 执行存储过程 8.3.2 使用图形化工具执行 存储过程
  • 21. 8.3.1 使用EXECUTE语句执行存储过程1.通过存储过程自身执行存储过程语法格式如下:[EXEC[UTE]] {[@整型变量=]存储过程名[;分组标识号]|@存储过程变量} [[@参数=]{参量值|@变量 [OUTPUT]|[DEFAULT]}][,...n] [WITH RECOMPLILE]
  • 22. 2.执行字符串 EXECUTE语句的主要用途是执行存储过程。此外,我们还可以将T-SQL语句放在字符串变量中,然后使用EXECUTE语句来执行该字符串,语法格式如下: EXEC[UTE] ({@字符串变量}|[N] 'tsql字符串') [+...n])
  • 23. 这里我们举一个例子说明在SSMS中执行存储过程的步骤。在SSMS中执行存储过程proc_goods的步骤如下。8.3.2 使用图形化工具执行存储过程
  • 24. (1)启动SSMS,连接到数据库实例,在“对象资源管理器”窗口里,展开数据库实例。 (2)依次选择“数据库”→存放存储过程的数据库,这里选择“Sales”→“可编程性”→“存储过程”→proc_goods右键快捷菜单的“执行存储过程”选项,这时就打开了“执行过程”窗口,可以看到该存储过程有一个输入参数“@员工编号”,在该参数的“值”输入框中输入一个值“1001”。
  • 25. (3)单击“确定”按钮,执行存储过程,在结果窗口中就可以看到执行的结果。
  • 26. 8.4 修改和删除存储过程 8.4.1 使用图形化工具查看和 修改存储过程 8.4.2 使用Transact-SQL查看和 修改存储过程
  • 27. 1.查看存储过程定义文本和修改存储过程 2.查看存储过程的依赖关系 3.重命名存储过程 4.删除存储过程8.4.1 使用图形化工具查看和修改存储过程
  • 28. 8.4.2 使用Transact-SQL查看和修改存储过程1.查看存储过程(1)查看存储过程的定义文本。 EXEC sp_helptext 存储过程名
  • 29. (2)查看存储过程的依赖关系。 EXEC sp_depends 存储过程名
  • 30. (3)查看存储过程的参数。 EXEC sp_help 存储过程名
  • 31. ALTER PROC[EDURE] [所有者.]存储过程名[;整数] [{@参数 数据类型}[VARYING][= 默认值][OUTPUT][,...n] [WITH {RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}] [FOR REPLICATION] AS SQL语句[,...n](2)使用ALTER PROCEDURE语句修改存储过程,语法格式如下:
  • 32. (1)重新命名存储过程。 EXEC sp_rename 存储过程原名,存储过程新名2.修改存储过程
  • 33. 3.删除存储过程 使用DROP PROCEDURE语句从当前数据库中删除一个或多个用户定义的存储过程或存储过程组,语法格式如下: DROP PROC[EDURE] {[所有者.]存储过程(组)名}[,...n]
  • 34. 存储过程是一种数据库对象,是存储在服务器上的一组预定义的SQL语句集合。创建存储过程并将编译好的版本存储在高速缓存中,可以加快程序的执行效率。存储过程可以有输入、输出参数,可以返回结果集以及返回值。通过本章的学习,应掌握各种存储过程的创建、执行、修改和删除方法。本章小结