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

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