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

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

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

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

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

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

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

6、Name”参数对应的名称修改为“StuInfo”，单击“确定”按钮，关闭此对话框。在创建存储过程的窗口中将对应的SELECT语句修改为以下程序代码：Select S_NUMBER AS 学号,S_NAME AS 姓名,SEX AS 性别,BIRTHDAY AS 出生日期,POLITY AS 政治面貌FROM T_STUDENTWHERE LEFT(S_NUMBER,5)=05541输入完毕后，单击窗口工具栏上的“执行”按钮执行以上程序段，就会创建一个新的存储过程“StuInfo”。现在学习的是第11页，共54页9.2.2 使用Transact-SQL语句创建存储过程可以使用CREATE PRO

7、CEDURE命令创建存储过程，考虑下列几个事项：CREATE PROCEDURE语句不能与其他SQL语句在单个批处理中组合使用。必须具有数据库的CREATE PROCEDURE权限。只能在当前数据库中创建存储过程。不要创建任何使用sp_作为前缀的存储过程。现在学习的是第12页，共54页9.2.2 使用Transact-SQL语句创建存储过程用CREATE PROCEDURE创建存储过程的语法如下：CREATE PROC|PROCEDURE schema_name.procedure_name parameter type_schema_name.data_type VARYING =defau

8、lt OUT|OUTPUT ,.n WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION AS BEGIN sql_statement END现在学习的是第13页，共54页9.2.2 使用Transact-SQL语句创建存储过程其中，各参数的意义如下：schema_name：过程所属架构的名称。procedure_name：新存储过程的名称。parameter：过程中的参数。type_schema_name.data_type：参数以及所属架构的数据类型。VARYING：指定作为输出参数支持的结果集。仅适用于cursor参数。现在学习的是第14页，共54

9、页9.2.2 使用Transact-SQL语句创建存储过程default：参数的默认值。OUTPUT：指示参数是输出参数。RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONRECOMPILE：表明 SQL Server 不会缓存该过程的计划，该过程将在运行时重新编译。ENCRYPTION：表示 SQL Server 加密 syscomments 表中包含 CREATE PROCEDURE 语句文本的条目。：要包含在过程中的一个或多个Transact-SQL语句。现在学习的是第15页，共54页创建存储过程1、创建普通的存储过程例：在student数据库中，创建一个

10、名称为myproc的存储过程，该存储过程的功能是从数据表student_info中查询所有男同学的信息。USE studentGOCREATE PROCEDURE myproc ASSELECT*FROM student_info WHERE sex=男GO 现在学习的是第16页，共54页2、创建带有参数的存储过程例：在student数据库中，创建一个名称为InsertRecord的存储过程，该存储过程的功能是向数据表stud_info中插入一条记录，新记录的值由参数提供。现在学习的是第17页，共54页USE studentGOCREATE PROCEDURE InsertRecord(sno

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

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

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

14、ects WHERE name=StuScoreInfo AND type=P)DROP PROCEDURE StuScoreInfoGO现在学习的是第23页，共54页-创建存储过程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

15、_SCORE.SCORE AS 考试分数FROM T_STUDENT,T_COURSE,t_SCOREWHERE T_STUDENT.S_NUMBER=t_SCORE.S_NUMBER AND T_COURSE.C_NUMBER=t_SCORE.C_NUMBERGO现在学习的是第24页，共54页例9-2-2：创建一个带有参数的存储过程Stu_Info，该存储过程根据传入的学生编号，在T_STUDENT中查询此学生的信息。USE STUDENTIF EXISTS(SELECT name FROM sysobjects WHERE name=Stu_Info AND type=P)DROP PRO

16、CEDURE Stu_InfoGO-创建存储过程USE STUDENTGO现在学习的是第25页，共54页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_STUDENTWH

17、ERE S_NUMBER=S_NUMBERGO现在学习的是第26页，共54页例9-2-3：创建一个带有参数的存储过程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现在学习的是第27页，共54页CREATE PROCEDURE

18、Stu_Age S_NUMBER varchar(10),Age int OUTPUTAS-定义并初始化局部变量，用于保存返回值DECLARE ErrorValue intSET ErrorValue=0-求此学生的年龄SELECT Age=YEAR(GETDATE()-YEAR(BIRTHDAY)FROM T_STUDENTWHERE S_NUMBER=S_NUMBER-根据程序的执行结果返回不同的值IF(ERROR0)SET ErrorValue=ERRORRETURN ErrorValueGO现在学习的是第28页，共54页9.3 执行存储过程存储过程创建成功后，保存在数据库中。在SQL

19、Server中可以使用EXECUTE命令来直接执行存储过程，语法形式如下：EXECUTE return_status=procedure_name|procedure_name_var parameter=value|variableOUTPUT|DEFAULT,.n 现在学习的是第29页，共54页9.3 执行存储过程其中，各选项的含义如下：EXECUTE：执行存储过程的命令关键字。return_status：是一个可选的整型变量，保存存储过程的返回状态。procedure_name：指定执行的存储过程的名称。procedure_name_var：是局部定义变量名，代表存储过程名称。param

20、eter：是在创建存储过程时定义的过程参数。现在学习的是第30页，共54页注意：如果省略EXECUTE关键字，则存储过程必须是批处理中的第一条语句，否则会出错。例：执行数据库student中的存储过程 myproc。EXECUTE myproc例：执行数据库student中的存储过程 InsertRecord。EXECUTE InsertRecord sno=S1,sn=王大伟,sex=男,age=18,dept=计算机系例：执行数据库student中的存储过程 InsertRecordDefa。EXECUTE InsertRecord sno=S10,sn=高平,sex=女,age=18SE

21、LECT 姓名=sn,系别=dept9.3 执行存储过程现在学习的是第31页，共54页例：执行数据库student中的存储过程Query_Study。DECLARE sn char(20)DECLARE dept char(10)EXECUTE Query_Study S10,sn OUTPUT,dept OUTPUT现在学习的是第32页，共54页9.3 执行存储过程例9-3-1：执行前面创建的StuInfo存储过程，它是一个无参的存储过程。程序清单如下：USE STUDENTEXEC StuInfo或直接写存储过程的名称：USE STUDENTGOStuInfo现在学习的是第33页，共54页

22、9.3 执行存储过程例9-3-2：执行存储过程StuScoreInfo。程序清单如下：USE STUDENTEXEC StuScoreInfo现在学习的是第34页，共54页9.3 执行存储过程例9-3-3：执行存储过程Stu_Info，该存储过程有一个输入参数“学号”，在执行时需要传入一个学号值。程序清单如下：USE STUDENTGOEXECUTE Stu_Info 0554101或：USE STUDENTGOEXECUTE Stu_Info S_NUMBER=0554101现在学习的是第35页，共54页9.3 执行存储过程例9-3-4：执行存储过程Stu_Age，该存储过程有一个输入参数“

23、学号”，另外，还有一个输出参数Age。存储过程执行完后，有一个返回的状态值，这个值可以从变量ErrorValue得到。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 c

24、har(2)+岁。现在学习的是第36页，共54页9.3 执行存储过程或：USE STUDENTGODECLARE 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

25、)+岁。现在学习的是第37页，共54页9.4 查看和修改存储过程现在学习的是第38页，共54页9.4.1 查看存储过程1使用SQL Server管理控制台查看用户创建的存储过程在SQL Server管理控制台中，选择指定的服务器和数据库，展开数据库中的“可编程性”文件夹，单击其中的“存储过程”，在右边的窗口中就会显示出当前数据库中的所有存储过程。现在学习的是第39页，共54页9.4.1 查看存储过程2使用系统存储过程查看用户创建的存储过程sp_help：用于显示存储过程的参数及其数据类型sp_help objname=name参数name为要查看的存储过程的名称。现在学习的是第40页，共54页

26、9.4.1 查看存储过程sp_helptext：用于显示存储过程的源代码sp_helptext objname=name参数name为要查看的存储过程的名称。现在学习的是第41页，共54页9.4.1 查看存储过程sp_depends：用于显示和存储过程相关的数据库对象sp_depends objname=object参数object为要查看依赖关系的存储过程的名称。现在学习的是第42页，共54页9.4.1 查看存储过程sp_stored_procedures：用于返回当前数据库中的存储过程列表sp_stored_proceduressp_name=name,sp_owner=owner,sp_

27、qualifier=qualifier其中，sp_name=name 用于指定返回目录信息的过程名；sp_owner=owner 用于指定过程所有者的名称；qualifier=qualifier 用于指定过程限定符的名称。现在学习的是第43页，共54页9.4.1 查看存储过程例9-4-1：使用系统存储过程查看Stu_Age存储过程的参数及其数据类型。例 9-4-2：使 用 系 统 存 储 过 程 查 看StuScoreInfo存储过程的相关的数据库对象。现在学习的是第44页，共54页9.4.2 修改存储过程1使用SQL Server管理控制台修改存储过程使用SQL Server管理控制台可以很

28、方便地修改存储过程的定义。在SQL Server管理控制台中，展开存储过程，右击要修改的存储过程，从弹出的快捷菜单中选择“修改”选项，则会出现与创建存储过程时类似的窗口。在该窗口中，可以直接修改定义该存储过程的Transact-SQL语句。现在学习的是第45页，共54页9.4.2 修改存储过程2使用Transact-SQL语句修改存储过程使用ALTER PROCEDURE语句可以更改存储过程，但不会更改权限，也不影响相关的存储过程或触发器。其语法形式如下：ALTER PROC|PROCEDURE schema_name.procedure_name parameter type_schema_

29、name.data_type VARYING =default OUT PUT ,.n WITH ENCRYPTIONAS sql_statement .n 现在学习的是第46页，共54页9.4.2 修改存储过程修改存储过程时，应该注意以下几点：如 果 原 来 的 过 程 定 义 是 使 用 WITH ENCRYPTION创建的，那么只有在ALTER PROCEDURE中也包含这个选项时，这个选项才有效。每次只能修改一个存储过程。用ALTER PROCEDURE更改的存储过程的权限保持不变。现在学习的是第47页，共54页例9-4-3：修改前面创建的Stu_Info存储过程，使之完成以下功能：根

30、据传入的学号，在表T_STUDENT、表T_COURSE和表t_SCORE中查询此学生的班级、学号、姓名、性别、考试课程名称和考试分数。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

31、课程名称,SCORE AS 考试成绩FROM T_STUDENT,T_COURSE,t_SCOREWHERE T_STUDENT.S_NUMBER=S_NUMBER AND T_STUDENT.S_NUMBER=t_SCORE.S_NUMBER AND T_COURSE.C_NUMBER=t_SCORE.C_NUMBERGO现在学习的是第48页，共54页9.5 重命名和删除存储过程现在学习的是第49页，共54页9.5.1 重命名存储过程1使用SQL Server管理控制台修改存储过程名称通过SQL Server管理控制台可以修改存储过程的名称。方法是：在SQL Server管理控制台中，右击要

32、操作的存储过程名称，从弹出的快捷菜单中选择“重命名”选项，当存储过程名称变成可输入状态时，就可以直接修改该存储过程的名称。现在学习的是第50页，共54页9.5.1 重命名存储过程2使用系统存储过程修改存储过程名称修改存储过程的名称也可以使用系统存储过程sp_rename，其语法形式如下：sp_rename 原存储过程名称，新存储过程名称例9-5-1：使用系统存储过程将Stu_Info存储过程的名称修改为Stu_Info_SCORE。现在学习的是第51页，共54页9.5.2 删除存储过程1使用SQL Server管理控制台删除存储过程在SQL Server管理控制台中，右击要删除的存储过程，从弹

33、出的快捷菜单中选择“删除”选项，会弹出“删除对象”对话框。在该对话框中，单击“确定”按钮，即可完成删除操作。现在学习的是第52页，共54页9.5.2 删除存储过程2使用Transact-SQL语句删除存储过程删除存储过程也可以使用Transact-SQL语言中的DROP命令，DROP命令可以将一个或者多个存储过程或者存储过程组从当前数据库中删除，其语法形式如下：DROP PROC|PROCEDURE schema_name.procedure ,.n 现在学习的是第53页，共54页9.5.2 删除存储过程例9-5-2：使用DROP命令删除StuInfo和StuScoreInfo两个存储过程。USE STUDENTGODROP procedure StuInfo,StuScoreInfoGO现在学习的是第54页，共54页