第八章 存储过程的创建及使用.doc

存储过程的创建和使用 概述 存储过程就是存储在SQL Server服务器中的一组编译成单个执行计划的Transact-SQL 语句。存储过程存储在数据库内，可由应用程序通过调用执行。使用存储过程不但可以提高Transact-SQL的执行效率，而且可以使对数据库的管理以及实现应用复杂的业务更容易。 存储过程也可看作是一种没有返回值的特殊函数，它与函数的区别是：函数有返回值，其返回值可以直接在表达式、计算列中使用；而存储过程只能调用执行，它不能通过名称得到返回值，也不能直接在表达式、计算列中使用。 任务一 理解存储过程的概念 存储过程是SQL语句和可选控制流语句的预编译集合，以一个名称存储并作为一个 单元处理，存储过程可以包含一条SELECT语句，也可包含一系列使用控制流的SQL语句。它们还可接受参数、返回输出参数、返回状态值，主要用来执行管理任务或实现应用复杂的业务规则。 任务二 理解存储过程的优点 使用存储过程与本地的Transact-SQL程序相比有如下好处。 简化用户操作：存储过程将一系列复杂的Transact-SQL代码封装在一起，作为数据库对象存放于数据库服务器内，用户使用时不必思考复杂的Transact-SQL语句而只需调用存储过程即可得到所需结果，简化了用户操作。 执行效率更高：存储过程已被编译，执行时不再花费编译与优化时间。 提高系统安全性：通过适当的权限设置，可以使系统的安全性得到有效保障。 运行模块化程序设计：可以只创建一次并将其存储在数据库中，以后即可在程序中调用该过程任意次，而且可以统一修改。 创建存储过程 任务一 使用企业管理器创建存储过程 启动SQL Server企业管理器，一次单击控制台树上的“服务器组”(“服务器”(“数据库”(“要创建存储过程的数据库”节点并右击“存储过程”节点，在弹出的快捷菜单中选择“新建存储过程”命令 弹出存储过程属性对话框，在“文本”框中输入存储过程。包括修改存储过程名，并在“AS”后输入存储过程内容。 单击“检查语法”按钮，检查所输入的SQL语句语法是否正确。 单击“确定”按钮，创建该存储过程。 任务二 使用Transact-SQL语句创建存储过程 可用CREATE PROCEDURE语句创建存储过程，语法格式如下： CREATE PROC [EDURE] 存储过程名 [;number] [{@参数数据类型}[VARYING][=默认值][OUTPUT]][,…n] [WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}] [FOR REPLICATION] AS Sql语句[…n] 参数说明： 存储过程名：新建存储过程名称必须符合标识符规则，且对于数据库及其所有者必须唯一。 要创建局部临时过程，可以在存储过程名前面加一个编号符（#存储过程名），要创建全局临时过程，可以在存储过程名前面加两个编号符（##存储过程名）。 ;number：是可选的整数，用来对同名的过程分组，以便用一条DROP PROECDURE语句即可将同组的过程一起除去。 @参数：过程中的参数。在CREATE PROCEDURE语句中可以声明一个或多个参数。数据类型用于限定参数的数据类型。 OUTPUT：表名参数是否返回参数。该选项的值可以返回给EXEC[UTE]。使用OUTPUT参数可将信息返回给调用过程。 RECOMPILE：表明SQL Server不会缓存该过程的计划，该过程将在运行时重新编译。 ENCRYPTION：表示SQL Server加密syscomments表中包含CREATE PROCEDURE语句文本的条目。 FOR REPLICATION：指定不能在订阅服务器上执行为复制创建的存储过程。 执行存储过程 当需要执行存储过程时，要使用EXECUTE语句。如果存储过程是批处理中的第一条 语句，那么不使用EXECUTE关键字也可以执行该存储过程。 例8-1 创建一个名为“proc_1”的存储过程，用于查看学生表的所有信息。然后调用该存储过程。 SQL 语句如下： USE SCinfo GO CREATE PROCEDURE proc_1 AS SELECT * FROM Students 例8-2 创建一个名为“proc_2”的存储过程，用于向学生表的所有字段添加一条记录，记录内容由调用时决定。然后调用该存储过程输入一条记录（1111,关羽,M,English,30 ）。 SQL 语句如下： USE SCinfo GO CREATE PROCEDURE proc_2 @Sno SMALLINT, @Sname VARCHAR(8), @Ssex CHAR(1), @Sdept VAR