第六章存储过程与触发器 本章内容)存储过程的基本概念 存储过程的特点与作用 触发器的基本概念 触发器的特点与作用 存储过程创建、执行以及参数应用的方法 触发器的创建及使用方法 重点难点)存储过程的参数应用方法 2 数据库技术 2 SOL Server2008
数据库技术 本章内容 重点难点 第六章存储过程与触发器 存储过程的基本概念 存储过程的特点与作用 触发器的基本概念 触发器的特点与作用 存储过程创建、执行以及参数应用的方法 触发器的创建及使用方法 存储过程的参数应用方法 2
问题提出? 为什么需要存储过程?存储过程是什么? 为什么要触发器?触发器是什么? 3 数据库技术 2 SOL Server2008
数据库技术 问题提出 为什么需要存储过程？存储过程是什么？ 为什么要触发器？触发器是什么？ 3
6.1存储过程概述 存储过程的特点和类型 存储过程的创建和执行 存储过程参数和执行状态 存储过程的查看和修改 存储过程的删除 数据库技术 2 SOL Server2008
数据库技术 6.1 存储过程概述 存储过程的特点和类型 存储过程的创建和执行 存储过程参数和执行状态 存储过程的查看和修改 存储过程的删除 4
6.1.1存储过程的特点和类型 存储过程 是存储在服务器上的 Transact-SQL语句的命名集合 是封装重复任务的方法 存储过程的特点 封装复杂操作 当加快系统运行速度 查询通知的工作流 实现代码重用 Database Updater 增强安全性 Service Program 3.5 数据库监视 减少网络流量 雪调用方便 2 数据库技术 2 SOL Server2008
数据库技术 6.1.1 存储过程的特点和类型 存储过程 是存储在服务器上的Transact-SQL语句的命名集合。 是封装重复任务的方法 存储过程的特点 封装复杂操作 加快系统运行速度 实现代码重用 增强安全性 减少网络流量 调用方便 5 查询通知的工作流 数据库监视
6.1.1存储过程的特点和类型 存储过程的类型 SQL Server2008中常用的存储过程类型有3种: 系统存储过程(sp) 由数据库系统自身创建,存储在 master数据库中,以 “sp_”前缀标识 用户定义存储过程(本地存储过程) 在单独的用户数据库内由用户创建。 临时存储过程:可以是局部的,名称以“#”开头;也可 以是全局的,名称以“##”开头。 扩展存储过程(xp) 参以动态链接库(DL)的形式实现。以“xp”为前缀,只 能添加到 master数据库中,在 SQL Server环境外执行。 6 数据库技术 2 SOL Server2008
数据库技术 6.1.1 存储过程的特点和类型 存储过程的类型 SQL Server 2008中常用的存储过程类型有3种： 系统存储过程（sp_）: 由数据库系统自身创建，存储在master数据库中，以 “sp_” 前缀标识 用户定义存储过程（本地存储过程）: 在单独的用户数据库内由用户创建。 临时存储过程：可以是局部的，名称以“#”开头；也可 以是全局的，名称以“##”开头。 扩展存储过程（xp_）: 以动态链接库（DLL）的形式实现。以“xp_”为前缀，只 能添加到master数据库中，在SQL Server 环境外执行。 6
6.1.2存储过程的创建和执行 创建存储过程的过程 存储过程名称 参数的说明 存储过程的主体 (包含执行过程操作的T-SQL语句)两部分 可以使用3种方法创建存储过程: 拳使用图形工具 拳使用向导 拳使用 Transact-SQL语言中的 CREATE PR0 CEDURE语句 7 数据库技术 2 SOL Server2008
数据库技术 6.1.2 存储过程的创建和执行 创建存储过程的过程 存储过程名称 参数的说明 存储过程的主体 （包含执行过程操作的 T-SQL 语句）两部分。 可以使用3种方法创建存储过程： 使用图形工具 使用向导 使用Transact-SQL语言中的CREATE PROCEDURE语句 7
6.1.2存储过程的创建和执行 ■使用图形工具创建存储过程 【例6-1】在 School数据库中,创建带输入参数的存储过程 SQLQuery3.sqI-(local). School(sa(55))* 的受生信自 B CREATE PROCEDURE proc SearchStudent Add the parameters for the stored procedure here 8 Shame nvarchar(20)--输入参数:学生姓名 chool (sa(53 BEGIN here Param1 SELECT FRoM Student Param2 WHere StudentName=csname 查询姓名为 gname的学生选课信息 ND esult sets from 自消息 命令已成功完成。 张 林不国心5阴只Y只49m!rNEa⊥C 2当nar=22 StudentCode Student Name Class Code Sex Birthday iven Dorm Telephone Photo Description Password 120151414201张三153030301男19901010000 67792280 NULL NULL vr2008
数据库技术 【例6-1】在School数据库中，创建带输入参数的存储过程 proc_SearchStudent， 查询指定学生姓名的学生信息。 1）打开SSMS，展开School数据库文件夹，右单击“存储过程”， 在弹出的快捷菜单上选择“新建存储过程”命令，打开新建存储 过程对话框。 2）将“” 参 数替换成存储过程存储过程的名称。 3）在“—Add Parameters”行下添加输入参数。 4）在BEGIN…END中输入查询语句。 5）执行存储过程：EXEC proc_SearchStudent '张三' 6.1.2 存储过程的创建和执行 使用图形工具创建存储过程
6.1.2存储过程的创建和执行 使用 CREATE PROCEDURE语句创建存储过程 常用存储过程的语法格式: CREATE PROC[DURE]存储过程名 @形式参数数据类型} LVARYING][=默认值][ OUTPUT AS SQL语句1 SQL语句n 注意事项: “形式参数”名称必须符合标识符规则;0 UTPUT表示该参数 是可以返回的,可将信息返回调用者;如果有多个参数,可 以依次按以上参数定义规则列出,用逗号“,”隔开。 9 数据库技术 2 SOL Server2008
数据库技术 6.1.2 存储过程的创建和执行 使用CREATE PROCEDURE语句创建存储过程 常用存储过程的语法格式： CREATE PROC[DURE] 存储过程名 {@形式参数 数据类型}[VARYING][=默认值][OUTPUT] AS SQL语句1 … SQL语句n 9 注意事项： • “形式参数”名称必须符合标识符规则；OUTPUT表示该参数 是可以返回的，可将信息返回调用者；如果有多个参数，可 以依次按以上参数定义规则列出，用逗号“,”隔开
6.1.2存储过程的创建和执行 【例6-2】在 School中创建一个的存储过程proc_ Student,查 询所有学生的信息。 CREATE PROC proc Student AS SELECT米 FROM Student 7+X SQLQuery2.sqI-dlocal)School(sa(53))*L 辔製。7回日 CREATE PRoC proc student 结果消息 Student Code Student Name Class Code Sex Birthday LiveIn Dorm Telephone Photo Description Passwo 20151414219张三153030301男19970101000 67792280 NULL NULL abcd 22015141442002李四 153030302男1997014020000 13701010101 NULL NULL 32015141442003王五 153030303男199701430000 13802020202 NULL NULL abcd 42015141442004赵六 153030301 女 1997020200:00:001 13903030303 NULL NULL abcd 5201514144005钱七 153030302女1997030300000 17004040404 NULL NULL abcd 1 62015141442006孙八 153030303男1997040400000 17105050505 NULL NULL abcd 72015141442007李九 153030301男1997050500:000 17206060606 NULL NULL 82015141442008周十 153030302男1997060600001 17307070707 NULL NULL abcd 92015141442009吴十 153030303女1998010100001 17408080808 NULL NULL 102015141442010郑十二153030301男1998020200000 17509090909 NULLNULL abcd 112015141442011王+三 153030302男19990303000000 17610101010 NULLNULL JyL ocI ver2008
数据库技术 【例6-2】在School中创建一个的存储过程proc_Student,查 询所有学生的信息。 CREATE PROC proc_Student AS SELECT * FROM Student 执行存储过程 EXEC proc_Student 6.1.2 存储过程的创建和执行
6.1.2存储过程的创建和执行 使用 EXECUTE(或EXEC)命令执行存储过程 语法格式如下: L LEXEC LUTE] I TOreturn status=] procedure name [ number] @procedure name var j [[Oparameter-] value @variable [OUTPUT] [DEFAULT]] n」 LWITH RECOMPILEI 注意事项: 执行存储过程必须具有执行该过才程在运行时重新编 如果存储过程是批处理中的第一条语句, EXECUTE命令可 以省略 存储过程的最大大小为128MB 数据库技术 2 SOL Server2008
数据库技术 6.1.2 存储过程的创建和执行 使用EXECUTE(或EXEC)命令执行存储过程 语法格式如下： [[EXEC[UTE]] { [@return_status=] procedure_name [;number] |@procedure_name_var } [[@parameter=]{value|@variable [OUTPUT]|[DEFAULT]] [,...n] [WITH RECOMPILE] 注意事项： 执行存储过程必须具有执行该过程的权限许可 如果存储过程是批处理中的第一条语句，EXECUTE命令可 以省略 存储过程的最大大小为128MB 11 WITH RECOMPILE表示过 程在运行时重新编译