第10章存储过程与触发器1.ppt

1、SQL Server 2005数据库应用技术（第二版）,第10章 存储过程与触发器（1）,复习,视图简介 创建和管理视图 创建视图(视图加密、架构绑定的目的) 如何获得视图的有关信息 修改与删除视图 使用视图修改数据 使用视图优化性能 索引视图 分区视图,本次课的学习内容,理解存储过程,了解存储过程的用途 创建存储过程的语法与示例(了解输入与输出参数的使用) 存储过程的更改与删除 执行存储过程的方式,10-1 存储过程,导入: 为了查询XS数据库中信息系的学生所修总学分信息，可以使用下列查询： USE XS SELECT 学号，姓名，系名，总学分 FROM XSDA WHERE 系名=信息 O

2、RDER BY 总学分 DESC 问题: 如果网络上有5000个用户执行同一查询 ,结果会怎样? 解决方法: 将代码以一定的形式存放在服务器中，而不是存储在客户机上 。,10-1-1 存储过程基础 存储过程就是在SQL Server数据库中存放的查询，是存储在服务器中的一组预编译过的T-SQL语句，而不是在客户机上的前端代码中存放的查询。 1.存储过程的优点 存储过程除减少网络通信流之外，还有如下优点： （1）存储过程在服务器端运行，执行速度快。 （2）存储过程是预编译过的，当第一次调用以后，就驻留在内存中，以后调用时不必再进行编译，因此，它的运行速度比独立运行同样的程序要快。,10-1 存储

3、过程,优点： （3）简化数据库管理。例如，如果需要修改现有查询，而查询存放在用户机器上，则要在所有的用户机器上进行修改。而如果在服务器中集中存放查询并作为存储过程，则只需要在服务器上改变一次。 （4）存储过程可以接受参数。允许零个或多个输入参数，也允许零个或多个输出参数。 （5）可作为安全机制使用。即使无对应权限的用户，也可授予权限执行存储过程。 注意：并不是每一个发往服务器的查询都要使用存储过程，只有用户经常运行这个查询，才应考虑将该查询生成为存储过程。,10-1 存储过程,2.存储过程的分类 ： 按编写的语言来分：T-SQL存储过程与CLR(公共语言运行)存储过程。 T-SQL 存储过程又

4、可分为： 用户定义存储过程 系统存储过程。,10-1 存储过程,10-1-2 创建存储过程 定义一个存储过程时，首先要确定以下几点： （1）存储过程的名字。 （2）该存储过程所要完成的功能，这是一个存储过程的主体。 （3）是否需要输入参数，是否需要输出参数来输出结果。,10-1 存储过程,10-1-2 创建存储过程 创建存储过程的方法有: 1.使用模板创建存储过程: SSMS/VIEW/模板资源管理器/存储器/创建存储过程 2.用SSMS管理平台创建 对象资源管理器/数据库/可编程性/存储过程/新建存储过程,10-1 存储过程,10-1-2 创建存储过程 3. 用T-SQL语句创建存储过程 语

5、法格式： CREATE PROCEDURESchema_name. procedure_name parameter data_type =defaultOUTPUT, WITH AS sql_statement 注: WITH 各个选项请详见P201,10-1 存储过程,说明： （1）用户自定义存储过程只能在当前数据库中创建。 （2）=default用于指定存储过程输入参数的默认值。默认值必须是常量或NULL，可以包含通配符%、_、和。如果在定义时带了“=default”参数，则执行存储过程时根据情况可不提供实参。 （3）在定义用户存储过程时，不能使用如下对象创建语句 CREATE VIEW

6、、CREATE DEFAULT、CREATE RULE CREATE PROCEDURE、CREATE TRIGGER （4）CREATE PROCEDURE必须是批处理中的第一条语句。存储过程的定义也不能跨越批处理。 （5）用相应架构名称限定存储过程所引用的对象名称，从而确保从存储过程中访问来自不同架构的表、视图或其它对象。,10-1 存储过程,创建存储过程示例,1、创建简单的存储过程 例： USE XS GO IF OBJECT_ID ( dbo.Getxsda, P ) IS NOT NULL DROP PROCEDURE dbo.Getxsda GO CREATE PROCEDURE

7、dbo.Getxsda AS SELECT * FROM XSDA GO -DECLAREreturn_value int -EXECreturn_value = dbo.GetXSDA -SELECTReturn Value = return_value,创建存储过程示例,1、创建简单的存储过程 又例：创建一个存储过程jsjzf_p，用于查询信息系学生总学分的排名情况（从高到低）。 USE XS GO if exists(select * from sys.objects where name=jsjzf_pand type=P) DROP PROC jsjzf_p GO CREATE PR

8、OCEDURE jsjzf_p AS SELECT 学号,姓名,系名,总学分 FROM XSDA WHERE 系名=信息 ORDER BY 总学分 DESC,创建存储过程示例,2、创建带输入参数的存储过程 例：创建一个存储过程，用于根据系名返回XSDA的数据，系名称条件使用输入参数。 IF OBJECT_ID ( dbo.GetXSDA2, P ) IS NOT NULL DROP PROCEDURE dbo.GetXSDA2 GO CREATE PROCEDURE GetXSDA2 xiname varchar(20) AS SELECT * FROM XSDA WHERE 系名 = xin

9、ame GO -exec GetXSDA2 信息 -exec GetXSDA2 xiname=信息 -GetXSDA2 信息,创建存储过程示例,又例：使用输入参数，创建存储过程grkccj_p，查询某人指定课程的成绩和学分。 if object_id(dbo.grkccj_p,P) is not null drop proc dbo.grkccj_p go CREATE PROCEDURE dbo.grkccj_p name char(8),kcname char(20) AS SELECT xsda.学号,姓名,课程名称,成绩,学分FROM XSDA,XSCJ,KCXX WHERE XSDA

10、.学号=XSCJ.学号AND XSCJ.课程编号=KCXX.课程编号 AND 姓名=name AND 课程名称=kcname GO,创建存储过程示例,3、使用带默认值的输入参数 【例】创建一个存储过程xscj_p，查询指定学生的学号，姓名，所选课程名称及该课程的成绩。默认为查询刘姓学生的学习情况。 if exists(select * from sys.objects where name=xscj_p and type=P) DROP PROC DBO.xscj_p GO CREATE PROCEDURE DBO.xscj_p name varchar(10) =刘% AS SELECT x

11、sda.学号,姓名,课程名称,成绩FROM XSDA,XSCJ,KCXX WHERE XSDA.学号=XSCJ.学号AND XSCJ.课程编号=KCXX.课程编号AND 姓名LIKE name,创建存储过程示例,为输入参数预设默认值的好处有： 第一：可以方便用户，因为可以将绝大数用户多数时候想查询的内容作为默认值。 第二：预设了默认值，那么在调用存储过程时，即使用户忘记了为参数赋值，程序也会执行下去。 -EXEC XSCJ_P -EXEC XSCJ_p 李刘王%,创建存储过程示例,4、使用输出参数 如果需要将存储过程的值返回，可使用输出参数。 例：创建一个存储过程kcavg_p，查询所有学生所

12、学指定课程的平均成绩，并将该平均成绩返回。 if object_id(dbo.kcavg_p,P) IS NOT NULL DROP PROC DBO.KCAVG_P GO CREATE PROCEDURE kcavg_p kcname char(20) ,kcavg decimal(3,1) output AS SELECT kcavg=avg(成绩) FROM XSCJ JOIN KCXX ON XSCJ.课程编号=KCXX.课程编号 WHERE 课程名称=kcname,创建存储过程示例,5、返回值 可以使用RETURN 语句从存储过程中返回信息。 例： IF OBJECT_ID ( db

13、o.Getxsda, P ) IS NOT NULL DROP PROCEDURE dbo.Getxsda GO CREATE PROCEDURE dbo.Getxsda AS SELECT * FROM XSDA -return 1 GO DECLAREreturn_value int EXECreturn_value = dbo.GetXSDA SELECTReturn Value = return_value,输出参数和返回值,又例: CREATE PROC HumanResources.AddDepartment Name nvarchar(50), GroupName nvarcha

14、r(50), DeptID smallint OUTPUT AS IF (Name = ) OR (GroupName = ) RETURN -1 INSERT INTO HumanResources.Department (Name, GroupName) VALUES(Name, GroupName) SET DeptID = SCOPE_IDENTITY() RETURN 0,输出参数和返回值,DECLARE dept int, result int EXEC result = AddDepartment Refunds, , dept OUTPUT IF (result = 0) SE

15、LECT dept ELSE SELECT Error during insert,10-1-3 执行存储过程,与系统存储过程一样，用户定义存储过程也是通过EXEC命令来执行。 语法格式： EXECUTE varI=procedure_name value|variable OUTPUT, 说明： （1）当该语句作为批处理的第一条语句时，EXEC（或EXECUTE）可以缺省。 （2）procedure_name为需要执行的存储过程名。 （3）value为输入参数提供实值，variable为一个已定义的变量，OUTPUT紧跟在变量后，说明该变量用于保存输出参数返回的值。 （4）当有多个参数时，彼

16、此用逗号分隔。 （5）可以将存储过程的返回值赋给一个变量,10-1-3 执行存储过程,【例】执行存储过程grkccj_p，查询王红计算机文化基础课的成绩与学分。 USE XS EXEC GRKCCJ_P 王红,计算机文化基础 GO 【例】执行存储过程kcavg_p，查询计算机文化基础课程的平均分。 USE XS GO DECLARE AVG DECIMAL(3,1) EXEC KCAVG_P 计算机文化基础,AVG OUTPUT SELECT AVG AS 计算机文件基础课的平均成绩,10-1-4 修改存储过程,通过T-SQL语句修改存储过程，语法格式也与定义存储过程类似，只需将关键字CREA

17、TE 改为ALTER就行了。 语法格式： ALTER PROCEDURESchema_name procedure_name parameter data_type =defaultOUTPUT, WITH AS sql_statement 其中，各参数的含义与CREATE PROCEDURE相同。,10-1-4 修改存储过程,【例】修改存储过程XZFj_p，将它修改为查询指定系(默认为信息)学生的总学分。 ALTER PROCEDURE xzf_p xiname char(10) =信息 AS SELECT 学号,姓名,系名,总学分FROM XSDA WHERE 系名=xiname ORDE

18、R BY 总学分DESC -XZF_p 管理 /*查询管理系*/ -XZF_p /*查询信息系*/,10-1-5 查看存储过程,存储过程被创建之后，可以进行查看浏览既可以通过SQL Server管理平台查看用户建的存储过程，也可以使用系统存储过程来查看用户创建的存储过程。 使用系统存储过程查看创建的存储过程 SP_help objname=name SP_helptext objname=name SP_depends objname= object SP_stored_procedures -返回当前环境中的存储过程列表,10-1-6 删除存储过程,删除存储过程 语法格式： DROP PROCEDURE procedure_name