SQL第11章存储过程PPT教学课件
合集下载
存储过程.ppt
存储过程相 当于C语言 中的函数
int sum(int a,int b) {
int s; s =a+b; return s ; }
什么是存储过程
存储过程可以包含数据操纵语句、变量、逻辑 控制语句等
存储过程
----------------------
单个 SELECT 语句 SELECT 语句块
可以包含
FROM Students INNER JOIN Student_course ON
优秀的学
Students.Student_id=Student_course.Student_id
生信息
WHERE Student_grade>=85
GO
调用存储过程
EXECUTE（执行）语句用来调用存储过程 调用的语法
常用的系统存储过程
系统存储过程 sp_databases sp_helpdb sp_renamedb sp_tables sp_columns sp_help sp_helpconstraint sp_helpindex sp_stored_procedures sp_password sp_helptext
创建文件夹D:\bank
EXEC xp_cmdshell 'mkdir d:\bank', NO_OUTPUT
IF EXISTS(SELECT * FROM sysdatabases
WHERE name='bankDB')
DROP DATABASE bankDB
GO
CREATE DATABASE bankDB
返回某个表列的信息
EXEC sp_help Students 查看表stuInfo的信息
存储过程PPT课件
1
11.1 存储过程综述
优点： 1、安全机制：只给用户访问存储过程的权限，而不授予用户访问表
和视图的权限。 2、改良了执行性能：在第一次执行后，会在SQL server的缓冲区中
创建查询树，以后执行无需编译。 3、减少网络流量：存储过程存在于服务器上，调用时，只需传递执
行存储过程的执行命令和返回结果。 4、模块化的程序设计：增强了代码的可重用性，提高了开发效率。
2
谢谢您的指导
THANK YOU FOR YOUR GUIDANCE.
感谢阅读！为了方便学习和使用，本文档的内容可以在下载后随意修改，调整和打印。欢迎下载！
汇报人：XXXX 日期：20XX年XX月XX日
3
11.1 存储过程综述
1、存储过程概念 存储过程是一种数据库对象，是为了实现某个特定任务，将存储 过程在第一次执行时进行编译，然后将编译好的代码保存在高速缓存中以 便以后调用，这样可以提高代码的执行效率。 存储过程同其它编程语言中的过程相似，有如下特点： 接收输入参数并以输出参数的形式将多个值返回至调用过程或批处理。 包含执行数据库操作（包括调用其它过程）的编程语句。 向调用过程或批处理返回状态值，以表明成功或失败以及失败原因。
11.1 存储过程综述
优点： 1、安全机制：只给用户访问存储过程的权限，而不授予用户访问表
和视图的权限。 2、改良了执行性能：在第一次执行后，会在SQL server的缓冲区中
创建查询树，以后执行无需编译。 3、减少网络流量：存储过程存在于服务器上，调用时，只需传递执
行存储过程的执行命令和返回结果。 4、模块化的程序设计：增强了代码的可重用性，提高了开发效率。
2
谢谢您的指导
THANK YOU FOR YOUR GUIDANCE.
感谢阅读！为了方便学习和使用，本文档的内容可以在下载后随意修改，调整和打印。欢迎下载！
汇报人：XXXX 日期：20XX年XX月XX日
3
11.1 存储过程综述
1、存储过程概念 存储过程是一种数据库对象，是为了实现某个特定任务，将存储 过程在第一次执行时进行编译，然后将编译好的代码保存在高速缓存中以 便以后调用，这样可以提高代码的执行效率。 存储过程同其它编程语言中的过程相似，有如下特点： 接收输入参数并以输出参数的形式将多个值返回至调用过程或批处理。 包含执行数据库操作（包括调用其它过程）的编程语句。 向调用过程或批处理返回状态值，以表明成功或失败以及失败原因。
第11章 MySQL存储过程与函数 第1节存储过程与函数简介 (1)
（2）创建函数
创建存储函数语法格式： create function sp_name ([func_parameter[，..]]) returns type [characteristic ..] routine_body
说明：在MySQL中，存储函数的使用方法与MySQL内部函数的 使用方法是一样的。换言之，用户自己定义的存储函数与MySQL 内部函数condition then statement_list [elseif search_condition then statement_list] … [else search_condition then statement_list] end if
数据库原理及MySQL应用 ——第十一章（第1节）
存储过程与函数简介
1.概念 2.存储过程和函数区别
1. 概念
一个存储过程是可编程的，它在数据库中创建并保存。它可以有SQL语句 和一些特殊的控制结构组成。
存储过程的优点： 存储过程增强了SQL语言的功能和灵活性; 存储过程允许标准组件是编程。 存储过程能实现较快的执行速度。 存储过程能过减少网络流量。 存储过程可被作为一种安全机制来充分利用。
【例19】删除存储过程studentcount
系统函数
1. 数学函数 2. 字符串函数 3. 日期和时间函数 4. 系统信息函数 5. 加密函数
具体使用 请参考教
材讲义
（6）repeat语句
repate语句是有条件控制的循环语句。 语法形式： [begin_label:] repeat statement_list until search_confition end repeat [end_label]
（7）while语句也是有条件控制的循环语句。
SQL编程基础及存储过程PPT教学课件
4. 比较运算符
=（等于）>（大于）<（小于）>=（大于或等于）<=
（小于或等于）<>（不等于）
比较运算符的结果为布尔数据类型，它有三种取值：
TRUE、FALSE和UNKNOWN。
2020/12/10
6
基本知识
5. 逻辑运算符
ALL 若一系列的比较均为TRUE，则为TRUE AND 若两个布尔表达式均为TRUE，则为TRUE ANY 若一系列的比较中有任一结果为TRUE，则为TRUE BETWEEN 若操作数在某个范围之内，则为TRUE EXISTS 若子查询包含一些行，则为TRUE IN 若操作数等于表达式列表中的一个，则为TRUE LIKE 若操作数与一种模式相匹配，则为TRUE NOT 对任何其他布尔运算符的值取反 OR若两个布尔表达式中的一个为TRUE，则为TRUE SOME 若在一系列比较中，有些为TRUE，则为TRUE
1896。
7. decimal常用引号。例如，
1893.1209，2.0。
7. float和real常量
float和real常量使用科学记数法表示。例如，101.5E5，0.5E-2。
9. money常量
money常量用一串数字，可以包含或不包含小数点，以一个货币符号（$）作
一 Transact-SQL
与SQL Server通信的所有应用程序都通过向服务器发送 Transact-SQL语句来进行通讯。Transact-SQL由多种应用程 序生成，例如：
使用Visual C++、Visual Basic或Delphi等可视化开发工具通 过数据库应用程序接口（如ADO、OLE DB以及ODBC）创建的 应用程序。
存储过程 PL-SQL讲义 课件
存储过程单元
触发类型
行触发：for each row 语句触发：在默认的情况下，before和after为语句触
发，instead of 为行触发
新旧参数值引用限制
对lob建立的before和after型，只可以读old，不可以读 new或写old, new
对lob建立的instead of，只可以读old, new, 不可以写 old, new
存储程序单元
特点
有名字 可带参数和返回值 存储在数据字典中 可被多个用户使用
注:存储过程有时候泛指存储过程和存储函数，二者唯 一的差别是函数要给调用者返回一个单个的值， 但过程不返回值。
存储程序单元
参数构成
名称：合法的PL/SQL标识符 模式：IN（默认）,OUT, IN OUT
注：函数中避免使用OUT和IN OUT，即返回 多个值。
如：DROP PROCEDURE Old_sal_raise; 所需权限(删除包同此)
DROP PROCEDURE DROP ANY PROCEDURE
存储过程单元
外部过程
Oracle服务器可以调用第三代语言写的过程。 这些过程的在独立于Oracle服务器的地址空 间里面运行。
存储过程单元
触发约束：布尔表达式，触发事件发生时，只有 此表达式为真才执行触发动作
触发动作：Action
存储过程单元
优点
灵活：触发事件多种多样 可以实现复杂的操作：触发动作任意，不似
foreign key, check
注意事项
避免级联 分布式不同节点的参照完整性用触发器，而同一
数据库内部则用主外码 尽量是用完整性约束，无法解决再用触发器
存储过程单元
TSQL基础和存储过程.ppt
日期和时间函数的类型
例如：从GETDATE函数返回的日期中提取月份数。
SELECT DATEPART(month, GETDATE()) AS 'Month Number'
运行结果为： Month Number -----------3
23
例如：从日期 03/15/2006 中返回月份数、天数和 年份数。
set @myage=25 或者
select sno=‘ec’+sno ,sname,sage from student
9
位运算符
•位运算符使我们能够在整型数据或者二进制数据（image 数据 类型除外）之间执行位操作。此外，在位运算符左右两侧的操 作数不能同时是二进制数据。
•
位
运算符
含义
运 算 &（按位 AND）
10
比较运算符
•比较运算符用于比较两个表达式的大小或是否 相同，其比较的结果是布尔值，即TRUE（表示 表达式的结果为真）、FALSE（表示表达式的 结果为假）以及UNKNOWN。除了 text、ntext 或 image 数据类型的表达式外，比较运算符可 以用于所有的表达式。
操作符: >,<,=,>=,<=,<>,……
按位 AND（两个操作数）。
符
|（按位 OR）
按位 OR（两个操作数）。
^（按位互斥 OR） 按位异或（两个操作数）。
例如:
Declare @a int,@b int
Set @a=2
Set @b=8
Select @a&@b as '@a&@b', @a|@b as '@a|@b' ,
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
• ① 进入企业管理器，展开相应的服务器组和相应的服务器节点。
• ② 按顺序展开“数据库”节点、要创建视图所属的数据库（假设为 JWGL）、再展开“存储过程”子节点。
• ③ 点击工具栏中“新建”按钮（或右击“存储过程”，在系统弹出的 快捷菜单上点击“新建存储过程”），出现如图11-1的“新建存储过 程”窗口。
第11章 存储过程的创建和使用
11.1 存储过程的定义 11.2 存储过程的优点 11.3 存储过程的创建 11.4 重新编译存储过程 11.5 自动执行存储过程 11.6 查看、修改和删除存储过程 11.7 扩展存储过程
返回目录
11.1 存储过程的定义
▪ 存储过程是一系列预先编辑好的、能实现特定数 据操作功能的SQL代码集，它与特定的数据库相
2020/12/12
9
11.3.3 创建带输入参数的存储过程
▪ 输入参数是指由调用程序向存储过程传递的参数。 ▪ 具体语法如下:
@parameter_name dataype[=default]
▪ 其中：
● @parameter_name：存储过程的输入参数名，必须以@符 号为前缀。执行该存储过程时，应该向输入参数提供相 应的值。
ON a.student_id = d.student_id INNER JOIN course b ON d.course_id = b.course_id INNER JOIN teacher_course_class e ON (e.class_id = a.class_id) and (e.course_id = d.course_id) INNER JOIN teacher c ON c.teacher_id = e.teacher_id
[OUTPUT])][ , . . . n ] [WITH {RECOMPILE | ENCRYPTION | RECOMPILE ,
ENCRYPTION} ] AS sql_statement [...n] ▪ 只有两个必需的参数必须传递给CREATE PROCEDURE语句： 创建存储过程所需的procedure_name和sql_statements。
使用T-SQL语句创建存储过程 使用企业管理器创建存储过程 创建带输入参数的存储过程 创建带输出参数的存储过程
2020/12/12
4
11.3.1 使用T-SQL语句创建存储过程
▪ 1.创建存储过程的SQL语句语法 ▪ 创建一个存储过程的语法如下：
CREATE PROC [EDURE] [OWNER.] procedure_name [({@parameter data_type} [VARYING] [=default]
2020/12/12
2
11.2 存储过程的优点
存储过程具有以下优点： • 1）存储过程提供了处理复杂任务的能力 • 2）增强代码的重用性和共享性 • 3）减少网络数据流量 • 4）加快系统运行速度 • 5）加强系统安全性
2020/12/12
3
11.3 存储过程的创建
▪ 11.3.1 ▪ 11.3.2 ▪ 11.3.3 ▪ 11.3.4
创建存储过程时可以使用参数WITH ENCRYPTION。
• ③ 一般存储过程都是在服务器上创建和测试，在客户机 上使用时，还应该进行测试。
2020/12/12
8
11.3.2 使用企业管理器创建存储பைடு நூலகம்程
▪ 在SQL Server企业管理器中创建存储过程仍然可以按照上面所 述的步骤进行，具体步骤如下：
2020/12/12
6
▪ 3. 存储过程创建示例 【例11-1】创建一存储过程，要求该存储过程返回学生姓名、所
学课程和任课教师。
CREATE PROCEDURE spStuCouTea_name AS SELECT a.student_name,b.course_name,c.teacher_name FROM student a INNER JOIN student_course d
• ④ 在这个窗口中，直接输入上面设计的语句。然后点击“确定”，就 可以在数据库中建立了新的存储过程spStuCouTea_name。观察数据库 JWGL，你会发现系统中多了一个名为spStuCouTea_name的存储过程。
▪ 在该窗口中，“检查语法”按钮用于检查我们的SQL语句是否 存在语法错误，“另存为模板”按钮则用于把输入的语句作为 一个模板，在下次创建存储过程时，新的存储过程的模板。
2020/12/12
5
▪ 在以上语句的语法中：
▪ ● procedure_name：为新创建的存储过程所指定的名字，它 必须遵循标准SQL Server命名约定，且必须在同一个数据库中
是唯一的。
▪ ● @parameter：存储过程的输入或输出参数。
▪ ● default： 参数缺省值。
▪ ● WITH RECOMPILE：重编译选项。
关联，存储在SQL Server服务器上。
▪ 存储过程分为三类：
▪ 1）系统存储过程
系统存储过程是指安装SQL Server时由系统创建的存储 过程。存储在master数据库中，其前缀为sp_。
2）扩展存储过程
扩展存储过程是对动态链接库（DLL）函数的调用。其 前缀为xp_。
3）用户定义的存储过程
由用户为完成某一特定功能而编写的存储过程。
▪ ● sql_statements：存储过程中实现功能的SQL语句。
▪ 2.创建步骤
▪ 一般来说，创建一个存储过程应按照以下步骤进行：
• ① 编写SQL语句。
• ② 测试SQL语句是否正确，并能实现功能要求。
• ③ 若得到的结果数据符合预期要求，则按照存储过程的语 法，创建该建存储过程。
• ④ 执行该存储过程，验证其正确性。
▪ 执行以上脚本，便可创建存储过程spStuCouTea_name。如要执 行该存储过程，可在查询分析器中执行如下语句：
▪ EXEC spStuCouTea_name
2020/12/12
7
▪ 4.创建存储过程的注意事项 ▪ 在创建存储过程的时候，需要注意如下几点：
• ① 每个存储过程应该完成一项单独的工作。 • ② 为防止别的用户看到自己所编写的存储过程的脚本，
• ② 按顺序展开“数据库”节点、要创建视图所属的数据库（假设为 JWGL）、再展开“存储过程”子节点。
• ③ 点击工具栏中“新建”按钮（或右击“存储过程”，在系统弹出的 快捷菜单上点击“新建存储过程”），出现如图11-1的“新建存储过 程”窗口。
第11章 存储过程的创建和使用
11.1 存储过程的定义 11.2 存储过程的优点 11.3 存储过程的创建 11.4 重新编译存储过程 11.5 自动执行存储过程 11.6 查看、修改和删除存储过程 11.7 扩展存储过程
返回目录
11.1 存储过程的定义
▪ 存储过程是一系列预先编辑好的、能实现特定数 据操作功能的SQL代码集，它与特定的数据库相
2020/12/12
9
11.3.3 创建带输入参数的存储过程
▪ 输入参数是指由调用程序向存储过程传递的参数。 ▪ 具体语法如下:
@parameter_name dataype[=default]
▪ 其中：
● @parameter_name：存储过程的输入参数名，必须以@符 号为前缀。执行该存储过程时，应该向输入参数提供相 应的值。
ON a.student_id = d.student_id INNER JOIN course b ON d.course_id = b.course_id INNER JOIN teacher_course_class e ON (e.class_id = a.class_id) and (e.course_id = d.course_id) INNER JOIN teacher c ON c.teacher_id = e.teacher_id
[OUTPUT])][ , . . . n ] [WITH {RECOMPILE | ENCRYPTION | RECOMPILE ,
ENCRYPTION} ] AS sql_statement [...n] ▪ 只有两个必需的参数必须传递给CREATE PROCEDURE语句： 创建存储过程所需的procedure_name和sql_statements。
使用T-SQL语句创建存储过程 使用企业管理器创建存储过程 创建带输入参数的存储过程 创建带输出参数的存储过程
2020/12/12
4
11.3.1 使用T-SQL语句创建存储过程
▪ 1.创建存储过程的SQL语句语法 ▪ 创建一个存储过程的语法如下：
CREATE PROC [EDURE] [OWNER.] procedure_name [({@parameter data_type} [VARYING] [=default]
2020/12/12
2
11.2 存储过程的优点
存储过程具有以下优点： • 1）存储过程提供了处理复杂任务的能力 • 2）增强代码的重用性和共享性 • 3）减少网络数据流量 • 4）加快系统运行速度 • 5）加强系统安全性
2020/12/12
3
11.3 存储过程的创建
▪ 11.3.1 ▪ 11.3.2 ▪ 11.3.3 ▪ 11.3.4
创建存储过程时可以使用参数WITH ENCRYPTION。
• ③ 一般存储过程都是在服务器上创建和测试，在客户机 上使用时，还应该进行测试。
2020/12/12
8
11.3.2 使用企业管理器创建存储பைடு நூலகம்程
▪ 在SQL Server企业管理器中创建存储过程仍然可以按照上面所 述的步骤进行，具体步骤如下：
2020/12/12
6
▪ 3. 存储过程创建示例 【例11-1】创建一存储过程，要求该存储过程返回学生姓名、所
学课程和任课教师。
CREATE PROCEDURE spStuCouTea_name AS SELECT a.student_name,b.course_name,c.teacher_name FROM student a INNER JOIN student_course d
• ④ 在这个窗口中，直接输入上面设计的语句。然后点击“确定”，就 可以在数据库中建立了新的存储过程spStuCouTea_name。观察数据库 JWGL，你会发现系统中多了一个名为spStuCouTea_name的存储过程。
▪ 在该窗口中，“检查语法”按钮用于检查我们的SQL语句是否 存在语法错误，“另存为模板”按钮则用于把输入的语句作为 一个模板，在下次创建存储过程时，新的存储过程的模板。
2020/12/12
5
▪ 在以上语句的语法中：
▪ ● procedure_name：为新创建的存储过程所指定的名字，它 必须遵循标准SQL Server命名约定，且必须在同一个数据库中
是唯一的。
▪ ● @parameter：存储过程的输入或输出参数。
▪ ● default： 参数缺省值。
▪ ● WITH RECOMPILE：重编译选项。
关联，存储在SQL Server服务器上。
▪ 存储过程分为三类：
▪ 1）系统存储过程
系统存储过程是指安装SQL Server时由系统创建的存储 过程。存储在master数据库中，其前缀为sp_。
2）扩展存储过程
扩展存储过程是对动态链接库（DLL）函数的调用。其 前缀为xp_。
3）用户定义的存储过程
由用户为完成某一特定功能而编写的存储过程。
▪ ● sql_statements：存储过程中实现功能的SQL语句。
▪ 2.创建步骤
▪ 一般来说，创建一个存储过程应按照以下步骤进行：
• ① 编写SQL语句。
• ② 测试SQL语句是否正确，并能实现功能要求。
• ③ 若得到的结果数据符合预期要求，则按照存储过程的语 法，创建该建存储过程。
• ④ 执行该存储过程，验证其正确性。
▪ 执行以上脚本，便可创建存储过程spStuCouTea_name。如要执 行该存储过程，可在查询分析器中执行如下语句：
▪ EXEC spStuCouTea_name
2020/12/12
7
▪ 4.创建存储过程的注意事项 ▪ 在创建存储过程的时候，需要注意如下几点：
• ① 每个存储过程应该完成一项单独的工作。 • ② 为防止别的用户看到自己所编写的存储过程的脚本，