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

1、第八章 存储过程的创建和使用8.1 概述存储过程就是存储在 SQL Server 服务器中的一组编译成单个执行计划的 Transact-SQL语句。存储过程存储在数据库内，可由应用程序通过调用执行。使用存储过程不但可以提高 Transact-SQL 的执行效率，而且可以使对数据库的管理以及实现应用复杂的业务更容易。存储过程也可看作是一种没有返回值的特殊函数，它与函数的区别是：函数有返回值，其返回值可以直接在表达式、计算列中使用；而存储过程只能调用执行，它不能通过名称得到返回值，也不能直接在表达式、计算列中使用。8.1.1 任务一 理解存储过程的概念存储过程是 SQL 语句和可选控制流语句的预编

2、译集合，以一个名称存储并作为一个单元处理，存储过程可以包含一条 SELECT 语句，也可包含一系列使用控制流的 SQL 语句。它们还可接受参数、返回输出参数、返回状态值，主要用来执行管理任务或实现应用复杂的业务规则。8.1.2 任务二 理解存储过程的优点使用存储过程与本地的 Transact-SQL 程序相比有如下好处。1、 简化用户操作：存储过程将一系列复杂的 Transact-SQL 代码封装在一起，作为数据库对象存放于数据库服务器内，用户使用时不必思考复杂的 Transact-SQL 语句而只需调用存储过程即可得到所需结果，简化了用户操作。2、 执行效率更高：存储过程已被编译，执行时不再

3、花费编译与优化时间。3、 提高系统安全性：通过适当的权限设置，可以使系统的安全性得到有效保障。4、 运行模块化程序设计：可以只创建一次并将其存储在数据库中，以后即可在程序中调用该过程任意次，而且可以统一修改。8.2 创建存储过程8.2.1 任务一 使用企业管理器创建存储过程1、 启动 SQL Server 企业管理器，一次单击控制台树上的“服务器组” “服务器”“数据库” “要创建存储过程的数据库”节点并右击“存储过程”节点，在弹出的快捷菜单中选择“新建存储过程”命令2、 弹出存储过程属性对话框，在“文本”框中输入存储过程。包括修改存储过程名，并在“AS”后输入存储过程内容。3、 单击“检查语

4、法”按钮，检查所输入的 SQL 语句语法是否正确。4、 单击“确定”按钮，创建该存储过程。8.2.2 任务二 使用 Transact-SQL 语句创建存储过程可用 CREATE PROCEDURE 语句创建存储过程，语法格式如下：CREATE PROC EDURE 存储过程名 ;number参数数据类型VARYING=默认值OUTPUT,nWITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONFOR REPLICATIONASSql 语句n参数说明： 存储过程名：新建存储过程名称必须符合标识符规则，且对于数据库及其所有者必须唯一。 要创建局部临时过程，可以在

5、存储过程名前面加一个编号符（#存储过程名） ，要创建全局临时过程，可以在存储过程名前面加两个编号符（#存储过程名） 。 ;number：是可选的整数，用来对同名的过程分组，以便用一条 DROP PROECDURE 语句即可将同组的过程一起除去。 参数：过程中的参数。在 CREATE PROCEDURE 语句中可以声明一个或多个参数。数据类型用于限定参数的数据类型。 OUTPUT：表名参数是否返回参数。该选项的值可以返回给 EXECUTE。使用 OUTPUT 参数可将信息返回给调用过程。 RECOMPILE：表明 SQL Server 不会缓存该过程的计划，该过程将在运行时重新编译。 ENCRY

6、PTION：表示 SQL Server 加密 syscomments 表中包含 CREATE PROCEDURE 语句文本的条目。 FOR REPLICATION：指定不能在订阅服务器上执行为复制创建的存储过程。8.3 执行存储过程当需要执行存储过程时，要使用 EXECUTE 语句。如果存储过程是批处理中的第一条语句，那么不使用 EXECUTE 关键字也可以执行该存储过程。例 8-1 创建一个名为“proc_1”的存储过程，用于查看学生表的所有信息。然后调用该存储过程。SQL 语句如下：USE SCinfoGOCREATE PROCEDURE proc_1AS SELECT *FROM Stu

7、dents例 8-2 创建一个名为“proc_2”的存储过程，用于向学生表的所有字段添加一条记录，记录内容由调用时决定。然后调用该存储过程输入一条记录（1111,关羽,M,English,30 ）。SQL 语句如下：USE SCinfoGOCREATE PROCEDURE proc_2 Sno SMALLINT,Sname VARCHAR(8),Ssex CHAR(1),Sdept VARCHAR(20),Sage TINYINT AS INSERT INTO StudentsVALUES(Sno,Sname,Ssex,Sdept,Sage)GOEXEC proc_2 1111,关羽 ,F,E

8、nglish,30 GO实验八 存储过程和游标的创建和使用一、实验目的1. 理解存储过程的概念，掌握各种存储过程的创建方法。2. 掌握查看、修改和删除存储过程的方法。3. 掌握执行存储过程的方法。4 掌握游标的创建和使用。二、实验内容1 存储过程的创建和使用。（1）、创建一个名为“proc_1” 的存储过程，用于查看学生表的所有信息。然后调用该存储过程。SQL 语句如下：USE SCinfoGOCREATE PROCEDURE proc_1AS SELECT *FROM Students（2）、创建一个名为“proc_2” 的存储过程，用于向学生表的所有字段添加一条记录，记录内容由调用时决定。

9、然后调用该存储过程输入一条记录（1111,关羽,M,English,30 ）。SQL 语句如下：USE SCinfoGOCREATE PROCEDURE proc_2 Sno SMALLINT,Sname VARCHAR(8),Ssex CHAR(1),Sdept VARCHAR(20),Sage TINYINT AS INSERT INTO StudentsVALUES(Sno,Sname,Ssex,Sdept,Sage)GOEXEC proc_2 1111,关羽 ,F,English,30 GO（3）、创建一个名为“proc_3” 的存储过程，用于删除学生表中指定学号的记录，具体学号由调用

10、时决定。然后调用该存储过程将学号为 1111 的记录删除。SQL 语句如下：USE SCinfoGOCREATE PROCEDURE proc_3 Sno SMALLINTAS DELETE FROM StudentsWHERE Sno = SnoGOEXEC proc_3 1111GO（4）、修改存储过程“proc_3” ，用于查询不小于指定年龄的学生的基本信息，具体年龄由调用时决定。然后调用存储过程 proc_3 查询年龄小于 20 岁的学生记录。SQL 语句如下：USE SCinfoGOALTER PROCEDURE proc_3 Sage TINYINTAS SELECT * FROM

11、 StudentsWHERE Sage SageGOEXEC proc_3 20GO2.游标的创建和使用（1）定义一个游标 STU1 并利用游标逐行输出学生表（Students）中学生的学号、姓名、性别，使用完游标后立即关闭并释放该游标。SQL 语句如下：DECLARE STU1 CURSOR -定义游标FOR SELECT Sno,Sname,Ssex FROM StudentsOPEN STU1 -打开游标DECLARE Sno smallint ,Sname varchar(8),Ssex char(1) -定义变量FETCH NEXT FROM STU1 INTO Sno ,Sname

12、,Ssex -获取一行值WHILE FETCH_STATUS = 0BEGINSELECT 学号=Sno, 姓名= Sname, 性别：= SsexFETCH NEXT FROM STU1 INTO Sno ,Sname,Ssex -从游标读取下一行值ENDCLOSE STU1 -关闭游标DEALLOCATE STU1 -释放游标（2）定义一个游标 STU2 并利用该游标输出学生表（Students）中第一个学生的所有信息，然后将该学生姓名改为张飞，并再次输入该学生信息，使用完游标后立即关闭并释放该游标。SQL 语句如下：DECLARE STU2 CURSOR -定义游标FOR SELECT

13、* FROM StudentsFOR UPDATE OF Sno ,Sname,Ssex,Sdept,SageOPEN STU2 -打开游标DECLARE Sno smallint ,Sname varchar(8),Ssex char(1),Sdept varchar(20),Sage tinyint -定义变量FETCH NEXT FROM STU2 INTO Sno ,Sname,Ssex,Sdept,Sage -获取第一行值SELECT 学号=Sno, 姓名 = Sname, 性别：= Ssex,系别=Sdept ,年龄=SageUPDATE Students -将当前行记录修改SET Sname = 张飞WHERE CURRENT OF STU2CLOSE STU2 -关闭游标DEALLOCATE STU2 -释放游标