第7章SQL Server存储过程和触发器.ppt

第7章 SQL Server存储过程和触发器 任课教师：郭黎明 7.2 存储过程的使用 [应用实例1] 统计每一个学生的平均成绩,要求显示学生姓名和平均成绩。 分析:关联查询和分组查询 使用T-SQL语句实现： 7.1 存储过程的使用 [应用实例1]统计学生所选课程所得的总学分,要求显示系科名,学号,姓名和总学分。 使用存储过程实现： 7.1 存储过程概述 存储过程的概念 存储过程是SQL Server服务器上一组预先定义并编译好的Transact-SQL语句，它可以接受参数，返回状态值和参数值。 存储过程应用 涉及服务器处理多，而与用户较少的程序 存储过程的分类 系统存储过程：sp开头 SP_database 本地存储过程 临时存储过程 远程存储过程 扩展存储过程 7.1 存储过程概述 存储过程的优点 模块化的程序设计 创建一个存储过程存放在数据库中后，就可以被其他程序反复使用 快速执行－当存储过程被编译并存储在高速缓冲区中时，它能以相当高的效率执行。 减少网络通信量－时常会有长达数百行的S Q L语句。如果能把那些功能封装进存储过程，就可以通过向SQL Server发送一行代码起到成百行的语句的作用，从而节省大量的网络带宽 安全性－存储过程能作为一种安全性机制使用。当你授权许可一个用户或一组用户使用存储过程，他们将能够执行这个存储过程而不必有访问在存储过程中被访问的下层对象的许可。 7.2 存储过程的使用 存储过程创建 存储过程的执行 存储过程修改 存储过程删除 7.2 存储过程的使用 存储过程创建 存储过程的创建方法 ①利用SQL Server 企业管理器创建存储过程。 ②使用Transact-SQL语句中的CREATE。 ③使用创建存储过程向导创建存储过程。 存储过程的组成 ①所有的输入参数以及传给调用者的输出参数。 ②被执行的针对数据库的操作语句，包括调用其它存储过程的语句。 ③返回给调用者的状态值，以指明调用是成功还是失败。 7.2 存储过程的使用 存储过程创建 使用Transact-SQL语句中的CREATE。 7.2 存储过程的使用 存储过程创建 procedure_name：用于指定要创建的存储过程的名称。 number：该参数是可选的整数，它用来对同名的存储过程分组，以便用一条 DROP PROCEDURE 语句即可将同组的过程一起除去。 @parameter：过程中的参数。在 CREATE PROCEDURE 语句中可以声明一个或多个参数。 data_type：用于指定参数的数据类型。 VARYING：用于指定作为输出OUTPUT参数支持的结果集。 Default：用于指定参数的默认值。 OUTPUT：表明该参数是一个返回参数。 7.2 存储过程的使用 存储过程创建 【实例1－－无参数的存储过程】：统计学生所选课程所得的总学分,要求显示系科名,学号,姓名和总学分。 分析:关联查询和分组查询 7.2 存储过程的使用 存储过程创建 【实例2----带输入参数的存储过程】 】 建立一存储过程，用户输入学生姓名，学号，如果student表中存在此项，则删除后再插入，若不存在此学生，则直接插入该记录。 说明：exists(select_statement)函数是指select语句是否能返回行，如果能返回，则为真，否则为假。 分析:显然学号和姓名应该作为存储过程的传入参数 7.2 存储过程的使用 【Ins_Del_S】代码 CREATE PROCEDURE Ins_Del_S @sno AS CHAR(6), @sname AS CHAR(10) AS IF EXISTS(SELECT * FROM student WHERE sno=@sno ) BEGIN DELETE student WHERE sno=@sno INSERT INTO student(sno,sname) VALUES( @sno ,@sname) END ELSE INSERT INTO student(sno,sname) VALUES( @sno ,@sname) GO 7.2 存储过程的使用 存储过程创建 【实例3－－带输出参数的存储过程】：根据用户键入的学生姓名，查询学生的年龄。 7.2 存储过程的使用 存储过程创建 【实例4－－通过Return参数返回状态】：创建一个存储过程,检查某学生的选课的门数 RETURN语句返回的是整形值 7.2 存储过程的使用 存储过程的执行 直接执行存储过程可以