存储过程的创建和使用讲稿.ppt

《存储过程的创建和使用讲稿.ppt》由会员分享，可在线阅读，更多相关《存储过程的创建和使用讲稿.ppt（54页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、存储过程的创建和使用第一页，讲稿共五十四页哦本章学习目标了解存储过程的概念了解使用存储过程的优点了解系统存储过程的特点及用途掌握创建存储过程的方法掌握执行存储过程的方法掌握查看和修改存储过程的方法掌握删除存储过程的方法第二页，讲稿共五十四页哦9.1 概述第三页，讲稿共五十四页哦9.1.1 存储过程的概念在使用Transact-SQL语言编程的过程中，可以将某些需要多次调用的实现某个特定任务的代码段编写成一个过程，将其保存在数据库中，并由SQL Server服务器通过过程名来调用它们，这些过程就叫做存储过程。存储过程在创建时就被编译和优化，调用一次以后，相关信息就保存在内存中，下次调用时可以直接

2、执行。第四页，讲稿共五十四页哦9.1.1 存储过程的概念存储过程有以下特点：存储过程中可以包含一条或多条Transact-SQL语句。存储过程可以接受输入参数并可以返回输出值。在一个存储过程中可以调用另一个存储过程。存储过程可以返回执行情况的状态代码给调用它的程序。第五页，讲稿共五十四页哦9.1.2 存储过程的优点使用存储过程有很多优点，具体如下：实现了模块化编程，一个存储过程可以被多个用户共享和重用。存储过程具有对数据库立即访问的功能。使用存储过程可以加快程序的运行速度。使用存储过程可以减少网络流量。使用存储过程可以提高数据库的安全性。第六页，讲稿共五十四页哦9.1.3 存储过程的分类在SQ

3、L Server中的存储过程分为两类：即系统提供的存储过程和用户自定义的存储过程。系统存储过程：由系统自动创建，系统存储过程出现在每个系统定义数据库和用户定义数据库的sys构架中。在SQL Server 2005中，可将GRANT、DENY和REVOKE权限应用于系统存储过程。用户自定义存储过程：是指封装了可重用代码的模块或例程，由用户创建，能完成某一特定的功能。可以接受输入参数，返回输出参数。第七页，讲稿共五十四页哦9.1.3 存储过程的分类在SQL Server 2005中，用户自定义存储过程有两种类型：Transact-SQL存储过程和CLR存储过程。Transact-SQL存 储 过

4、程：是 指 保 存 的Transact-SQL语句集合。CLR存 储 过 程：是 指 对 Microsoft.NET Framework公共语言运行时(CLR)方法的引用，可以接受和返回用户提供的参数。第八页，讲稿共五十四页哦9.2 创建存储过程在SQL Server中，可以使用两种方法创建存储过程：当创建存储过程时，需要确定存储过程的三个组成部分：所有的输入参数以及传给调用者的输出参数。被执行的针对数据库的操作语句，包括调用其他存储过程的语句。返回给调用者的状态值，以指明调用是成功还是失败。第九页，讲稿共五十四页哦9.2.1 使用SQL Server管理控制台创建存储过程在SQL Serve

5、r管理控制台中，选择指定的服务器和数据库，展开数据库中的“可编程性”文件夹，右击其中的“存储过程”，在弹出的快捷菜单中选择“新建存储过程”选项。第十页，讲稿共五十四页哦9.2.1 使用SQL Server管理控制台创建存储过程例9-2-1：创建一个名称为“StuInfo”的存储过程，要求完成以下功能：在T_STUDENT表中查询05541班学生的学号、姓名、性别、出生日期和政治面貌五个字段的内容。在创建存储过程的窗口中，单击“查询”菜单，选择“指定模板参数的值”，会弹出“指定模板参数的值”对话框。在“指定模板参数的值”对话框中将“Procedure_Name”参数对应的名称修改为“StuInf

6、o”，单击“确定”按钮，关闭此对话框。在创建存储过程的窗口中将对应的SELECT语句修改为以下程序代码：Select S_NUMBER AS 学号,S_NAME AS 姓名,SEX AS 性别,BIRTHDAY AS 出生日期,POLITY AS 政治面貌FROM T_STUDENTWHERE LEFT(S_NUMBER,5)=05541输入完毕后，单击窗口工具栏上的“执行”按钮执行以上程序段，就会创建一个新的存储过程“StuInfo”。第十一页，讲稿共五十四页哦9.2.2 使用Transact-SQL语句创建存储过程可以使用CREATE PROCEDURE命令创建存储过程，考虑下列几个事项：

7、CREATE PROCEDURE语句不能与其他SQL语句在单个批处理中组合使用。必须具有数据库的CREATE PROCEDURE权限。只能在当前数据库中创建存储过程。不要创建任何使用sp_作为前缀的存储过程。第十二页，讲稿共五十四页哦9.2.2 使用Transact-SQL语句创建存储过程用CREATE PROCEDURE创建存储过程的语法如下：CREATE PROC|PROCEDURE schema_name.procedure_name parameter type_schema_name.data_type VARYING =default OUT|OUTPUT ,.n WITH REC

8、OMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION AS BEGIN sql_statement END第十三页，讲稿共五十四页哦9.2.2 使用Transact-SQL语句创建存储过程其中，各参数的意义如下：schema_name：过程所属架构的名称。procedure_name：新存储过程的名称。parameter：过程中的参数。type_schema_name.data_type：参数以及所属架构的数据类型。VARYING：指定作为输出参数支持的结果集。仅适用于cursor参数。第十四页，讲稿共五十四页哦9.2.2 使用Transact-SQL语句创建存储过程d

9、efault：参数的默认值。OUTPUT：指示参数是输出参数。RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONRECOMPILE：表明 SQL Server 不会缓存该过程的计划，该过程将在运行时重新编译。ENCRYPTION：表示 SQL Server 加密 syscomments 表中包含 CREATE PROCEDURE 语句文本的条目。：要包含在过程中的一个或多个Transact-SQL语句。第十五页，讲稿共五十四页哦创建存储过程1、创建普通的存储过程例：在student数据库中，创建一个名称为myproc的存储过程，该存储过程的功能是从数据表stud

10、ent_info中查询所有男同学的信息。USE studentGOCREATE PROCEDURE myproc ASSELECT*FROM student_info WHERE sex=男GO 第十六页，讲稿共五十四页哦2、创建带有参数的存储过程例：在student数据库中，创建一个名称为InsertRecord的存储过程，该存储过程的功能是向数据表stud_info中插入一条记录，新记录的值由参数提供。第十七页，讲稿共五十四页哦USE studentGOCREATE PROCEDURE InsertRecord(sno char(6),sn char(20),age numeric(5),

11、sex char(2),dept char(10)ASINSERT INTO stud_info VALUES(sno,sn,sex,age,dept)GO 第十八页，讲稿共五十四页哦3、创建具有参数默认值的存储过程例：在student数据库中，创建一个名称为InsertRecordDef的存储过程，该存储过程的功能是向数据表student中插入一条记录，新记录的值由参数提供，如果未提供系别dept的值时，由参数的默认值代替。第十九页，讲稿共五十四页哦USE studentGOCREATE PROCEDURE InsertRecordDef(sno char(6),sn char(20),ag

12、e numeric(5),sex char(2),dept char(10)=无)ASINSERT INTO stud_info VALUES(sno,sn,sex,age,dept)GO 第二十页，讲稿共五十四页哦4、创建能够返回值的存储过程例：在student数据库中，创建一个名称为Query_Study的存储过程，该存储过程的功能是从数据表stud_info中根据学号查询某一同学的姓名和系别。第二十一页，讲稿共五十四页哦USE studentGOCREATE PROCEDURE Query_Study(sno char(6),sn char(20)OUTPUT,dept char(10)

13、OUTPUT)ASSELECT sn=sn,dept=deptFROM stud_infoWHERE sno=snoGO 第二十二页，讲稿共五十四页哦例9-2-2：创建一个存储过程StuScoreInfo，完成的功能是在表T_STUDENT、表T_COURSE和表t_SCORE中查询以下字段：班级、学号、姓名、性别、课程名称、考试分数。程序清单如下：-打开STUDENT数据库USE STUDENT-查询是否已存在此存储过程，如果存在，就删除它IF EXISTS(SELECT name FROM sysobjects WHERE name=StuScoreInfo AND type=P)DROP

14、 PROCEDURE StuScoreInfoGO第二十三页，讲稿共五十四页哦-创建存储过程CREATE PROCEDURE StuScoreInfo ASSelect 班级=SUBSTRING(T_STUDENT.S_NUMBER,1,LEN(T_STUDENT.S_NUMBER)-2),学号=SUBSTRING(T_STUDENT.S_NUMBER,LEN(T_STUDENT.S_NUMBER)-1,2),S_NAME AS 姓名,SEX AS 性别,T_COURSE.C_NAME AS 课程名称,t_SCORE.SCORE AS 考试分数FROM T_STUDENT,T_COURSE,t

15、_SCOREWHERE T_STUDENT.S_NUMBER=t_SCORE.S_NUMBER AND T_COURSE.C_NUMBER=t_SCORE.C_NUMBERGO第二十四页，讲稿共五十四页哦例9-2-2：创建一个带有参数的存储过程Stu_Info，该存储过程根据传入的学生编号，在T_STUDENT中查询此学生的信息。USE STUDENTIF EXISTS(SELECT name FROM sysobjects WHERE name=Stu_Info AND type=P)DROP PROCEDURE Stu_InfoGO-创建存储过程USE STUDENTGO第二十五页，讲稿共

16、五十四页哦CREATE PROCEDURE Stu_Info S_NUMBER varchar(10)AS Select 班级=SUBSTRING(T_STUDENT.S_NUMBER,1,LEN(T_STUDENT.S_NUMBER)-2),学号=SUBSTRING(T_STUDENT.S_NUMBER,LEN(T_STUDENT.S_NUMBER)-1,2),S_NAME AS 姓名,SEX AS 性别,BIRTHDAY AS 出生日期,POLITY AS 政治面貌FROM T_STUDENTWHERE S_NUMBER=S_NUMBERGO第二十六页，讲稿共五十四页哦例9-2-3：创建一

17、个带有参数的存储过程Stu_Age，该存储过程根据传入的学生编号，在T_STUDENT中计算此学生的年龄，并根据程序的执行结果返回不同的值，程序执行成功，返回整数0，如果执行出错，则返回错误号。USE STUDENTIF EXISTS(SELECT name FROM sysobjects WHERE name=Stu_Age AND type=P)DROP PROCEDURE Stu_AgeGO-创建存储过程USE STUDENTGO第二十七页，讲稿共五十四页哦CREATE PROCEDURE Stu_Age S_NUMBER varchar(10),Age int OUTPUTAS-定义并

18、初始化局部变量，用于保存返回值DECLARE ErrorValue intSET ErrorValue=0-求此学生的年龄SELECT Age=YEAR(GETDATE()-YEAR(BIRTHDAY)FROM T_STUDENTWHERE S_NUMBER=S_NUMBER-根据程序的执行结果返回不同的值IF(ERROR0)SET ErrorValue=ERRORRETURN ErrorValueGO第二十八页，讲稿共五十四页哦9.3 执行存储过程存储过程创建成功后，保存在数据库中。在SQL Server中可以使用EXECUTE命令来直接执行存储过程，语法形式如下：EXECUTE retur

19、n_status=procedure_name|procedure_name_var parameter=value|variableOUTPUT|DEFAULT,.n 第二十九页，讲稿共五十四页哦9.3 执行存储过程其中，各选项的含义如下：EXECUTE：执行存储过程的命令关键字。return_status：是一个可选的整型变量，保存存储过程的返回状态。procedure_name：指定执行的存储过程的名称。procedure_name_var：是局部定义变量名，代表存储过程名称。parameter：是在创建存储过程时定义的过程参数。第三十页，讲稿共五十四页哦注意：如果省略EXECUTE关键

20、字，则存储过程必须是批处理中的第一条语句，否则会出错。例：执行数据库student中的存储过程 myproc。EXECUTE myproc例：执行数据库student中的存储过程 InsertRecord。EXECUTE InsertRecord sno=S1,sn=王大伟,sex=男,age=18,dept=计算机系例：执行数据库student中的存储过程 InsertRecordDefa。EXECUTE InsertRecord sno=S10,sn=高平,sex=女,age=18SELECT 姓名=sn,系别=dept9.3 执行存储过程第三十一页，讲稿共五十四页哦例：执 行 数 据 库

21、student中 的 存 储 过 程Query_Study。DECLARE sn char(20)DECLARE dept char(10)EXECUTE Query_Study S10,sn OUTPUT,dept OUTPUT第三十二页，讲稿共五十四页哦9.3 执行存储过程例9-3-1：执行前面创建的StuInfo存储过程，它是一个无参的存储过程。程序清单如下：USE STUDENTEXEC StuInfo或直接写存储过程的名称：USE STUDENTGOStuInfo第三十三页，讲稿共五十四页哦9.3 执行存储过程例9-3-2：执行存储过程StuScoreInfo。程序清单如下：USE

22、STUDENTEXEC StuScoreInfo第三十四页，讲稿共五十四页哦9.3 执行存储过程例9-3-3：执行存储过程Stu_Info，该存储过程有一个输入参数“学号”，在执行时需要传入一个学号值。程序清单如下：USE STUDENTGOEXECUTE Stu_Info 0554101或：USE STUDENTGOEXECUTE Stu_Info S_NUMBER=0554101第三十五页，讲稿共五十四页哦9.3 执行存储过程例9-3-4：执行存储过程Stu_Age，该存储过程有一个输入参数“学号”，另外，还有一个输出参数Age。存储过程执行完后，有一个返回的状态值，这个值可以从变量Err

23、orValue得到。USE STUDENTGODECLARE stuAge int,ReturnValue int,S_NUMBER char(10)SET S_NUMBER=0554101EXECUTE ReturnValue=Stu_Age S_NUMBER,stuAge OUTPUTPRINT 本程序的执行结果：PRINT 程序的返回值=+CAST(ReturnValue AS char(2)PRINT 学号为+RTRIM(S_NUMBER)+的学生的年龄是+CAST(stuAge AS char(2)+岁。第三十六页，讲稿共五十四页哦9.3 执行存储过程或：USE STUDENTGOD

24、ECLARE stuAge int,ReturnValue int,S_NUMBER1 char(10)SET S_NUMBER1=0554101EXECUTE ReturnValue=Stu_Age S_NUMBER=S_NUMBER1,Age=stuAge OUTPUTPRINT 本程序的执行结果：PRINT 程序的返回值=+CAST(ReturnValue AS char(2)PRINT 学号为+RTRIM(S_NUMBER1)+的学生的年龄是 +CAST(stuAge AS char(2)+岁。第三十七页，讲稿共五十四页哦9.4 查看和修改存储过程第三十八页，讲稿共五十四页哦9.4.1

25、 查看存储过程1使用SQL Server管理控制台查看用户创建的存储过程在SQL Server管理控制台中，选择指定的服务器和数据库，展开数据库中的“可编程性”文件夹，单击其中的“存储过程”，在右边的窗口中就会显示出当前数据库中的所有存储过程。第三十九页，讲稿共五十四页哦9.4.1 查看存储过程2使用系统存储过程查看用户创建的存储过程sp_help：用于显示存储过程的参数及其数据类型sp_help objname=name参数name为要查看的存储过程的名称。第四十页，讲稿共五十四页哦9.4.1 查看存储过程sp_helptext：用于显示存储过程的源代码sp_helptext objname

26、=name参数name为要查看的存储过程的名称。第四十一页，讲稿共五十四页哦9.4.1 查看存储过程sp_depends：用于显示和存储过程相关的数据库对象sp_depends objname=object参数object为要查看依赖关系的存储过程的名称。第四十二页，讲稿共五十四页哦9.4.1 查看存储过程sp_stored_procedures：用于返回当前数据库中的存储过程列表sp_stored_proceduressp_name=name,sp_owner=owner,sp_qualifier=qualifier其中，sp_name=name 用于指定返回目录信息的过程名；sp_owne

27、r=owner 用于指定过程所有者的名称；qualifier=qualifier 用于指定过程限定符的名称。第四十三页，讲稿共五十四页哦9.4.1 查看存储过程例9-4-1：使用系统存储过程查看Stu_Age存储过程的参数及其数据类型。例 9-4-2：使 用 系 统 存 储 过 程 查 看StuScoreInfo存储过程的相关的数据库对象。第四十四页，讲稿共五十四页哦9.4.2 修改存储过程1使用SQL Server管理控制台修改存储过程使用SQL Server管理控制台可以很方便地修改存储过程的定义。在SQL Server管理控制台中，展开存储过程，右击要修改的存储过程，从弹出的快捷菜单中选

28、择“修改”选项，则会出现与创建存储过程时类似的窗口。在该窗口中，可以直接修改定义该存储过程的Transact-SQL语句。第四十五页，讲稿共五十四页哦9.4.2 修改存储过程2使用Transact-SQL语句修改存储过程使用ALTER PROCEDURE语句可以更改存储过程，但不会更改权限，也不影响相关的存储过程或触发器。其语法形式如下：ALTER PROC|PROCEDURE schema_name.procedure_name parameter type_schema_name.data_type VARYING =default OUT PUT ,.n WITH ENCRYPTIONA

29、S sql_statement .n 第四十六页，讲稿共五十四页哦9.4.2 修改存储过程修改存储过程时，应该注意以下几点：如 果 原 来 的 过 程 定 义 是 使 用 WITH ENCRYPTION创建的，那么只有在ALTER PROCEDURE中也包含这个选项时，这个选项才有效。每次只能修改一个存储过程。用ALTER PROCEDURE更改的存储过程的权限保持不变。第四十七页，讲稿共五十四页哦例9-4-3：修改前面创建的Stu_Info存储过程，使之完成以下功能：根据传入的学号，在表T_STUDENT、表T_COURSE和表t_SCORE中查询此学生的班级、学号、姓名、性别、考试课程名称

30、和考试分数。USE STUDENTGOALTER PROCEDURE Stu_Info S_NUMBER varchar(10)AS Select 班级=SUBSTRING(T_STUDENT.S_NUMBER,1,LEN(T_STUDENT.S_NUMBER)-2),学号=SUBSTRING(T_STUDENT.S_NUMBER,LEN(T_STUDENT.S_NUMBER)-1,2),S_NAME AS 姓名,SEX AS 性别,T_COURSE.C_NAME AS 课程名称,SCORE AS 考试成绩FROM T_STUDENT,T_COURSE,t_SCOREWHERE T_STUDE

31、NT.S_NUMBER=S_NUMBER AND T_STUDENT.S_NUMBER=t_SCORE.S_NUMBER AND T_COURSE.C_NUMBER=t_SCORE.C_NUMBERGO第四十八页，讲稿共五十四页哦9.5 重命名和删除存储过程第四十九页，讲稿共五十四页哦9.5.1 重命名存储过程1使用SQL Server管理控制台修改存储过程名称通过SQL Server管理控制台可以修改存储过程的名称。方法是：在SQL Server管理控制台中，右击要操作的存储过程名称，从弹出的快捷菜单中选择“重命名”选项，当存储过程名称变成可输入状态时，就可以直接修改该存储过程的名称。第五十

32、页，讲稿共五十四页哦9.5.1 重命名存储过程2使用系统存储过程修改存储过程名称修改存储过程的名称也可以使用系统存储过程sp_rename，其语法形式如下：sp_rename 原存储过程名称，新存储过程名称例9-5-1：使用系统存储过程将Stu_Info存储过程的名称修改为Stu_Info_SCORE。第五十一页，讲稿共五十四页哦9.5.2 删除存储过程1使用SQL Server管理控制台删除存储过程在SQL Server管理控制台中，右击要删除的存储过程，从弹出的快捷菜单中选择“删除”选项，会弹出“删除对象”对话框。在该对话框中，单击“确定”按钮，即可完成删除操作。第五十二页，讲稿共五十四页哦9.5.2 删除存储过程2使用Transact-SQL语句删除存储过程删除存储过程也可以使用Transact-SQL语言中的DROP命令，DROP命令可以将一个或者多个存储过程或者存储过程组从当前数据库中删除，其语法形式如下：DROP PROC|PROCEDURE schema_name.procedure ,.n 第五十三页，讲稿共五十四页哦9.5.2 删除存储过程例9-5-2：使用DROP命令删除StuInfo和StuScoreInfo两个存储过程。USE STUDENTGODROP procedure StuInfo,StuScoreInfoGO第五十四页，讲稿共五十四页哦