《《SQL Server数据库应用与开发》-李德有 彭德林-电子教案 SQL 2005第6章》由会员分享，可在线阅读，更多相关《《SQL Server数据库应用与开发》-李德有 彭德林-电子教案 SQL 2005第6章（55页珍藏版）》请在金锄头文库上搜索。
1、第6章 存储过程,主要内容,存储过程及其创建 执行存储过程 管理存储过程的方法,第六章 存储过程,6.1存储过程概述 6.2存储过程的创建 6.3存储过程的维护,6.1存储过程概述,6.1.1 存储过程简介 SQL Server提供了一种方法：可以将一些预先编译的SQL语句集中起来由SQL Server数据库服务器来完成，以实现某个任务，这就是存储过程。,6.1存储过程概述,6.1.2 存储过程优点 1.存储过程是SQL Server数据库服务器上一组预先编译好的Transact-SQL语句。 2.存储程以一个名称存储在数据库中，可作为一个独立的数据库对象；可作为一个单 元供用户在应用程序中调
2、用。 3.存储过程可以接收和输出参数，返回执行存储过程的状态值。 4.一个存储过程可以调用另一个存储过程。 5.存储过程会返回执行情况的状态代码给调用它的程序。,6.1存储过程概述,6.1.2 存储过程优点 1.存储过程因为SQL语句已经预编绎过了，因此运行的速度比较快。存储过程已经经过编译，无须经过SQL语句的再次分析，可以直接执行。根据经验，存储过程在数据库上运行，平均要比SQL语句执行速度快上210倍。 2.存储过程可以接受参数、输出参数、返回单个或多个结果集以及返回值。可以向程序返回错误原因。 3.存储过程运行比较稳定，不会有太多的错误。只要一次成功，以后都会按这个程序运行。 4.存储
3、过程主要是在服务器上运行，减少对客户机的压力。,6.1存储过程概述,6.1.2 存储过程优点 5.存储过程可以包含程序流、逻辑以及对数据库的查询。同时可以实体封装和隐藏了数据逻辑。 6.存储过程可以在单个存储过程中执行一系列SQL语句。 7.存储过程可以减轻服务器的负担。当用户的操作是针对于数据数据库对象的操作时，使用存储过程，只要直接发送存储过程的调用命令即可，从而增加了网络流量，降低网络负担。如果使用单条调用语句的方式，网络上就必须传输大量的SQL语句。,6.1存储过程概述,6.1.3存储过程分类 1.系统存储过程 系统存储过程就是系统创建的存储过程，是在SQL Server安装成功后，就
4、已经存储在系统数据库中了，目的在于能够方便地从系统表中查询信息或完成与更新数据库表相关的管理任务或其它的系统管理任务。 系统过程以“sp_”为开头，并存放在“sys”构架中，为数据库管理者所有。,6.1存储过程概述,6.1.3存储过程分类 2.本地存储过程 本地存储过程是在用户数据库中创建的存储过程。本地存储过程实际上就是用户存储过程，在以后的数据库开发中，涉及到的存储过程几乎都是本地存储过程。 这种存储过程是用户自己创建的普通数据库，完成特定数据库操作任务，其名称不能以“sp_”为前缀。,6.1存储过程概述,6.1.3存储过程分类 3.临时存储过程 临时存储过程首先是属于本地存储过程。 我们
5、在创建本地存储过程时，如果在本地存储过程的名称前有一个“#”，该存储过程就被称为局部临时存储过程，只能在一个用户会话中使用；如果在本地存储过程的名称前有两个“#”，该存储过程就被称为全局临时存储过程，可以在所有用户会话中使用。,6.1存储过程概述,6.1.3存储过程分类 4.远程存储过程 远程存储过程，顾名思义，就是指从远程服务器上调用的存储过程，也就是非本地服务器上的存储过程。,6.1存储过程概述,6.1.3存储过程分类 5.扩展存储过程 扩展存储程序是指在SQL Server环境之外执行的动态链接库DLL。它们是以例如用C语言等编写的外部程序，以动态链接库（DLL）形式存储在服务器上，SQ
6、L Server可以动态装载并执行它们。扩展存储过程只能添加到Master数据库。,6.1存储过程概述,6.1.3存储过程分类 6. CLR过程 CLR存储过程是SQL Server 2005新增的存储过程，是针对微软的.NET Framework公共语言运行时方法的引用，可以接受和返回用户提供的参数。CLR存储过程要在Microsoft Visual Studio 2005中来实现。,6.2存储过程的创建,6.2.1 使用Create procedure语句 CREATE PROC EDURE procedure_name ;number parameter data_type VARYIN
7、G =default OUTPUT ,.n WITH RECOMPILE | ENCRYPTION | RECOMPILE,ENCRYPTION FOR REPLICATION AS sql_statement .n ,6.2存储过程的创建,例6-1：创建一个存储过程exp1，查询学生表中所有内容的。 USE 教务管理 GO CREATE PROCEDURE exp1 AS SELECT * FROM 学生 GO,6.2存储过程的创建,例6-2：创建一个存储过程exp2，加入一条学生记录到学生表中，并查询此表中所有学生的总学分。 USE 教务管理 GO CREATE PROCEDURE exp
8、2 ch_学号 nvarchar(10),ch_姓名 nvarchar(max),ch_性别 nvarchar(2),ch_身份证号 nvarchar(14),da_出生年月 datetime,ch_联系电话 nvarchar(max), ch_民族 nvarchar(max), ch_家庭住址 nvarchar(max), ch_邮编 nvarchar(6) AS INSERT INTO学生 (学号,姓名,性别,身份证号,出生年月,联系电话,民族,家庭住址,邮编) VALUES(ch_学号,ch_姓名,ch_性别,ch_身份证号,da_出生年月,ch_联系电话, ch_民族, ch_家庭住址
9、, ch_邮编) GO,6.2存储过程的创建,例6-3：创建一个存储过程exp3，给出一个性别，显示所有该型别的学生信息。 USE 教务管理 GO CREATE PROCEDURE exp3 ch_性别 nvarchar(2) AS SELECT * FROM 学生 WHERE 性别=ch_性别 GO,6.2存储过程的创建,6.2.2 在SQL Server Management Studio中创建 1.启动SQL Server Management Studio，并登录所要使用的服务器，如图6.1所示。,图 6.1 登录服务器,6.2存储过程的创建,6.2.2 在SQL Server Man
10、agement Studio中创建 2.在SQL Server Management Studio窗口左端的树状结构中，选择要创建存储过程的数据库，如教务管理数据库，鼠标左键单击+展开，如图6.2所示。,图 6.2 展开数据库,6.2存储过程的创建,6.2.2 在SQL Server Management Studio中创建 3.选择【可编程性】节点，鼠标左键单击+展开，如图6.3所示。,图 6.3 展开可编程性节点图,6.2存储过程的创建,4.选择【存储过程】节点，单击鼠标右键，在弹出的菜单中选择【新建存储过程】命令，如图6.4所示。,图 6.4 新建存储过程,6.2存储过程的创建,5.在打
11、开的文本框中输入创建存储过程的Transact-SQL语句就可，如图6.5所示。,图 6.5 弹出的存储过程输入窗口,6.2存储过程的创建,6.2.3 存储过程的执行 1.使用Transact-SQL语句执行存储过程 EXECUTE return_statur = procedure_name ;number | procedure_name_var parameter= value | variable OUTPUT | DEFAULT ,n WITH RECOMPILE,6.2存储过程的创建,6.2.3 存储过程的执行 1.使用Transact-SQL语句执行存储过程 例6-4：执行存储过
12、程exp1。 EXECUTE exp1 执行结果将显示所有学生的信息。,6.2存储过程的创建,6.2.3 存储过程的执行 2.使用SQL Server Management Studio执行存储过程 （1）.启动SQL Server Management Studio，并登录所要使用的服务器，在SQL Server Management Studio窗口左端的树状结构中，选择要创建存储过程的数据库，如教务管理数据库，鼠标左键单击+展开，如图6.6所示。,图 6.6 展开数据库,6.2存储过程的创建,6.2.3 存储过程的执行 （2）.选择【可编程性】节点下的【存储过程】节点，显示存储在数据库中
13、的所有的存储过程，如图6.7所示。,图 6.7 选择存储过程,6.2存储过程的创建,6.2.3 存储过程的执行 （3）.在要执行的存储过程上单击鼠标右键，在弹出的快捷菜单中选择【执行存储过程】命令，如图6.8所示。,图 6.8 选择执行存储过程,6.2存储过程的创建,6.2.3 存储过程的执行 （4）.选择【执行存储过程】命令后，会弹出【执行过程】窗口，在该窗口中显示了系统得状态、存储过程的参数等相关信息，单击【确定】按钮则开始执行该存储过程。如图6.9所示。,图 6.9 显示执行过程对话框,6.2存储过程的创建,6.2.3 存储过程的执行 （5）.存储过程执行完后，会返回执行的结果，在窗口的
14、右下角，会看到执行的结果，以及执行存储过程的相关消息。如图6.10所示。,图 6.10 显示执行结果,6.2存储过程的创建,6.2.4 存储过程参数的使用 1.带输入参数的存储过程 例6-5：创建一个带输入参数的存储过程，完成输入一个学号，如果存在，则显示“该学号存在”，如果不存在该学号，则显示“该学号不存在”。 CREATE PROCEDURE exp4 ch_学号 nvarchar(10) AS BEGIN DECLARE bit_exist bit IF EXISTS(SELECT * FROM 学生 WHERE 学号=ch_学号) SELECT bit_exist=1 ELSE SEL
15、ECT bit_exist=0 IF bit_exist=1 PRINT 该学号存在 ELSE PRINT 该学号不存在 END,6.2存储过程的创建,6.2.4 存储过程参数的使用 2.带输出参数的存储过程 例6-6：创建一个带OUTPUT参数的存储过程，根据输入的课程号，统计该课程的所有学生的总成绩。 USE 教务管理 GO CREATE PROCEDURE exp5 ch_课程号 nvarchar(10), all_score int output AS SELECT all_score=sum(成绩) FROM 成绩 WHERE 课程号=ch_课程号 GO,6.2存储过程的创建,6.2
16、.5 创建及使用存储过程注意事项 1.不要使用 sp_ prefix sp_ prefix 是为系统存储过程保留的。数据库引擎将始终首先在主数据库中查找具有此前缀的存储过程。这意味着当引擎首先检查主数据库，然后检查存储过程实际所在的数据库时，将需要较长的时间才能完成检查过程。而且，如果碰巧存在一个名称相同的系统存储过程，则您的过程根本不会得到处理。,6.2存储过程的创建,6.2.5 创建及使用存储过程注意事项 2.尽量少用可选参数 在频繁使用可选参数之前，请仔细考虑。通过执行额外的工作会很轻易地影响性能，而根据为任意指定执行输入的参数集合，这些工作时不需要的。您可以通过对每种可能的参数组合使用条件编码来解决此问题，但这相当费时并会增大出错的几率。,6.2存储过程的创建,3.在可能的情况下使用 OUTPUT 参数 通过使用 OUTPUT 参数返回
