教学：数据库-存储过程资料
第9章 存储过程的创建与使用
第9章存储过程的创建和使用
自定义函数的创建与使用？ 自定义函数的创建与使用？
CREATE FUNCTION 函数名 ( 参数表 ) RETURNS 返回值的类型 [ AS ] BEGIN 函数体 RETURN 返回的表达式 END 调用： SET @result=dbo.fun_SumCount(@maxprice,@minprice)
5．允许模块化程序设计
存储过程可以封装企业的功能模块，这种企业的功能模块也为商业规则或 者商业策赂，可以只创建一次并将其存储在数据库中，以后即可在程序中调用该 过程任意次，而且可以统一修改。
9.1.3 掌握存储过程的分类
存储过程分为两大类：系统存储过程和用户自定义存储过程。
系统存储过程：由系统定义的存储过程，存放在master数据库中，
p_topic_by_sid存储过程可以通过以下方法执行： USE bbsdb GO EXEC p_topic_by_sid 2 -- Or EXEC p_topic_by_sid 2
练习： 练习：
（2）在student数据库中，创建名为p_tj2的存储过 程，查询选修某门指定课程的学生人数、最高成绩、最 低成绩和平均成绩 ； 执行该存储过程，例如，查询选修‘C1’课程的信息
练习： 练习：
（1）在student数据库中，创建名为p_tj1的存储过 程，查询选修每门课程的学生人数、最高成绩、最低成 绩和平均成绩 ；
2．创建带有参数的简单存储过程 【例9-2】创建存储过程，除【例9-1】的要求（不显示代码,需 要显示代码意义）外， 还要求只返回指定某版块编号的帖子信息。 【分析】： （1）创建存储过程，该存储过程有输入参数“版块编号”，无 输出参数。 （2）帖子信息存放于TOPIC表，发帖人信息存放于USERS表， 版块信息存放于SECTION表, 三张表作联接行存储过程 查看和修改存储过程 常用的系统存储过程
oracle存储过程介绍
存储过程优点（2）
4）重复使用。存储过程可以重复使用，从而可以减少数 据库开发人员的工作量。 5）灵活：使用存储过程，可以实现存储过程设计和编码 工作分开进行，只要将存储过程名、参数、及返回信 息告诉编码人员即可。
存储过程缺点（1）
1）移植性差：使用存储过程封装业务逻辑将限制应用程 序的可移植性； 2）维护成本高：如果更改存储过程的参数或者其返回的 数据及类型的话，需要修改应用程序的相关代码，比 较繁琐。
执行存储过程
执行存储过程语法： ，...］)；
CALL/PERFORM Procedure 过程名(［参数1，参数2
在PL/SQL中，数据库服务器支持在过程体中调用其他 存储过程 使用CALL或者PERFORM等方式激活存储过程的执行。 调用时”()”是不可少的，无论是有参数还是无参数。
过程名：数据库服务器合法的对象标识 参数列表：用名字来标识调用时给出的参数值，必须 指定值的数据类型。参数也可以定义输入参数、输出 参数或输入/输出参数。默认为输入参数。 过程体：是一个<PL/SQL块>。包括声明部分和可执 行语句部分 ;不用 declare 语句
创建存储过程(2)
例子： ［例1］ 利用存储过程来实现下面的应用: 从一个账户转指定数额的款项到 另一个账户中。 CREATE PROCEDURE TRANSFER(inAccount INT， outAccount INT ， amount FLOAT) AS totalDeposit FLOAT; BEGIN /* 检查转出账户的余额 */ SELECT total INTO totalDeposit FROM ACCOUNT WHERE ACCOUNTNUM=outAccount; IF totalDeposit IS NULL THEN /* 账户不存在或账户中没有存款 */ ROLLBACK; RETURN; END IF;
SQL Server 2005数据库简明教程-第8章 存储过程的操作与管理
图8-3 查看存储过程
8.2.1 查看存储过程
（2）使用系统存储过程来查看用户创建的存储过程 。
可供使用的系统存储过程及其语法形式如下： •sp_help，用于显示存储过程的参数及其数据类型，其语法为： sp_help [[@objname=] name]，参数name为要查看的存储过程的名称。 •sp_helptext，用于显示存储过程的源代码，其语法为： sp_helptext [[@objname=] name]，参数name为要查看的存储过程的名称。 •sp_depends，用于显示和存储过程相关的数据库对象，其语法为： sp_depends [@objname=]’object’，参数object为要查看依赖关系的存储过程 的名称。 •sp_stored_procedures，用于返回当前数据库中的存储过程列表，其语法为： sp_stored_procedures[[@sp_name=]'name'] [,[@sp_owner=]'owner'] [,[@sp_qualifier =] 'qualifier'] 其中，[@sp_name =] 'name' 用于指定返回目录信息的过程名；[@sp_owner =] 'owner' 用于指定过程所有者的名称；[@qualifier =] 'qualifier' 用于指定过程 限定符的名称。
8.2查看、修改和删除存储过程
8.2.1 查看存储过程 8.2.2 修改存储过程
8.2.3 重命名和删除存储过程
8.2.1 查看存储过程
（1）使用SQL Server管理平台查看用户创建的存储过程。 在SQL Server管理平台中，展开指定的服务器和数据库，选择并依次展开“程序→存储 过程”，然后右击要查看的存储过程名称，如图8-3所示，从弹出的快捷菜单中，选择 “创建存储过程脚本为→CREATE到→新查询编辑器窗口”，则可以看到存储过程的源 代码。
数据库自定义函数、存储过程和触发器
自定义函数、 第9章 自定义函数、存储过程和触发器 《 SQL Server 数据库管理与开发》
1自定义函数 概念 创建 查看 调用 修改 删除 2存储过程 3触发器 实训 小结
1 自定义函数
1.1 自定义函数的概念 1.2 创建自定义函数 1.3 查看自定义函数信息 1.4 调用自定义函数 1.5 调用自据库管理与开发》
自定义函数、 第9章 自定义函数、存储过程和触发器
自定义函数的概念 创建自定义函数 9.1 自定义函数 查看自定义函数信息 调用自定义函数 存储过程的概念 调用自定义函数 创建存储过程 9.2 存储过程 删除自定义函数 查看存储过程信息 触发器的概念 执行存储过程 创建触发器 修改存储过程 9.3 触发器 触发器使用限制 删除存储过程 修改触发器 常用系统存储过程 删除触发器 使用触发器的优点
《 SQL Server 数据库管理与开发》
自定义函数、 第9章 自定义函数、存储过程和触发器
教学提示：在数据库实际应用中， 教学提示：在数据库实际应用中，存在有带变量数据 处理需求，如某班学生信息表、 处理需求，如某班学生信息表、某老师带过的学 某班某门课不及格学生等。自定义函数、 生、某班某门课不及格学生等。自定义函数、存 储过程、触发器是由一系列的T 储过程、触发器是由一系列的T-SQL 语句组成的 子程序，用来满足更高的应用需求，可以说是SQL 子程序，用来满足更高的应用需求，可以说是SQL 程序设计的灵魂， 程序设计的灵魂，掌握和使用好它们对数据库的 开发与应用非常重要。 开发与应用非常重要。 教学要求： 教学要求： 自定义函数、存储过程、触发器的概念、用途、 自定义函数、存储过程、触发器的概念、用途、 创建方法。 创建方法。 编写简单的自定义函数、存储过程、触发器。 编写简单的自定义函数、存储过程、触发器。
数据库 第13章 实现存储过程
存储过程可以显式地重新编译，但应尽量少 做，仅当
存储过程所引用的表中的数据发生巨大的变化时 存储过程所引用的对象的架构发生变更时，如增加 删除列、规则、约束，或者为底层表增加了存储过 程可能从中受益的索引时
Copyright@2008
22
显式地重新编译存储过程（续）
三种显式重新编译存储过程的方法
CREATE PROCEDURE [WITH RECOMPILE]
当前的环境和计划编译时的环境相同。服务器、数据库 和连接的设置决定了环境 存储过程引用的对象不需要名称解析。若被不同用户拥 有的对象具有相同的名字，则需要名称解析。
一个执行计划产生后，驻留在过程缓存中。仅当 需要空间时，SQL Server 将老的、没用的计划移 出缓存
Copyright@2008 6
存储过程的后续处理（续）
检索到的执行计划
执行计划
执行上下文
连接1
8082
SELECT * FROM dbo.member WHERE member_no = ?
连接2
24
连接3
1003
未用过的计划过时被清除
Copyright@2008
7
第13章 实现存储过程
存储过程介绍
创建和管理存储过程 在存储过程中使用参数 系统和扩展存储过程 处理错误信息
Copyright@2008
13
执行存储过程
单独执行存储过程
不带参数的情况： [[EXEC[UTE] 存储过程名 [ WITH RECOMPILE]
USE Northwind GO EXEC OverdueOrders GO
在 INSERT 语句内执行存储过程
语法：INSERT INTO 表名 EXEC[UTE] …… 将本地或远程存储过程返回的结果集插入本地表中 在 INSERT 语句内执行的存储过程必须返回关系结 果集
MySQL数据库应用实战教程 第5章 MySQL函数和存储过程
5.2.3 日期和时间函数
通过日期和时间函数可以获取当前日期、当前时间、年份、月份、 天、小时等关于日期的函数。日期和时间函数如表5.3所示。
表5.3 日期和时间函数
续表
常用的日期和时间函数如下。 （1）NOW()：当前日期和时间，如2017-11-29 23:21:19。 （2）CURDATE()：当前日期，如2017-11-29。 （3）CURTIME()：当前时间，如23:22:49。 （4）YEAR(d)：提取日期中的年份，如YEAR('2017-11-30')。 （5）MONTH(d)：提取日期中的月份，如MONTH('20170819')。 （6）DAYOFYEAR(d)：提取日期里一年中的第几天，如DAYOFYEAR ('2017-11-30')。
（11）DATE_ADD() ：向后推时间。DATE_ADD(NOW(),INTERVAL 3 YEAR)表示当前时间往后推3年；DATE_ADD(NOW(),INTERVAL 3 MONTH)表示当前时间往后推3个月；DATE_ADD(NOW(),INTERVAL 3 DAY)表示当前时间往后推3天。
表5.1 数学函数
续表
常用的数学函数如下。 （1）CEILING(x)：返回大于x的最小整数值，它是向上取整。 （2）FLOOR(x)：返回小于x的最大整数值，它是向下取整。 （3）ROUND(x,y)：返回参数x的四舍五入的有y位小数的值，进行 四舍五入，保留y位小数。 （4）TRUNCATE(x,y)：返回数字x截短为y位小数的结果，不进行 四舍五入，直接保留y位小数。 （5）MOD(x,y)：返回x/y的模，也是取余数，和x%y是等价的。
3．调用自定义函数
MySQL存储过程实例教程
MySQL存储过程实例教程MySQL 5.0以后的版本开始支持存储过程，存储过程具有一致性、高效性、安全性和体系结构等特点，本节将通过具体的实例讲解PHP是如何操纵MySQL存储过程的。
1：存储过程的创建这是一个创建存储过程的实例实例说明为了保证数据的完整性、一致性，提高应用的性能，常采用存储过程技术。
MySQL 5.0之前的版本并不支持存储过程，随着MySQL技术的日趋完善，存储过程将在以后的项目中得到广泛的应用。
本实例将介绍在MySQL 5.0以后的版本中创建存储过程。
技术要点一个存储过程包括名字、参数列表，以及可以包括很多SQL语句的SQL语句集。
下面为一个存储过程的定义过程： create procedure proc_name (in parameterinteger)begindeclare variable varchar(20);if parameter=1 thensetvariable='MySQL';elseset variable='PHP';end if;insert into tb (name) values (variable);end;MySQL中存储过程的建立以关键字create procedure开始，后面紧跟存储过程的名称和参数。
MySQL的存储过程名称不区分大小写，例如PROCE1()和proce1()代表同一个存储过程名。
存储过程名不能与MySQL数据库中的内建函数重名。
存储过程的参数一般由3部分组成。
第一部分可以是in、out或inout。
in表示向存储过程中传入参数；out表示向外传出参数；inout表示定义的参数可传入存储过程，并可以被存储过程修改后传出存储过程，存储过程默认为传入参数，所以参数in可以省略。
第二部分为参数名。
第三部分为参数的类型，该类型为MySQL数据库中所有可用的字段类型，如果有多个参数，参数之间可以用逗号进行分割。
高职数据库第七章 存储过程
在调用存储过程时，一定注意参数的类型一定要 和存储过程保持一致或者可自动转换。向参数传 递值有两种方式。
EXEC EXEC
存储过程名称 参数值1，参数值2...
存储过程名称 参数名1=参数值1，参数名2=参 数值2...(可以不考虑参数的顺序)
SQL SERVER 2005 案例教程
第七章 存储过程
7.1 存储过程的基础知识
定义：存储过程（stored procedure）是一组 为了完成特定功能的SQL语句集，经编译后存 储在数据库中，用户通过指定存储过程的名 字并给出参数（如果该存储过程带有参数） 来执行它。 通常情况下，将常用或很复杂的 语句编写成存储过程。
AS
sql_statement 说明：使用AS来引导任意数目的Transact-SQL语句， 既可以是DML语句，也可以是DDL语句，这些语句 构成存储过程的主体。如果AS后包含多条语句，需 要将这些语句放在BEGIN... END之间。
7.2 存储过程的创建、修改与删除
注意：关键字“PROCEDURE”可简写为“PROC”。 【例7.1】创建存储过程getxs1，获取学生的XH、XM 信息。 USE CJGL GO CREATE PROC getxs1 AS
可以使用DROP PROC[EDURE]命令，从当前数据库中 删除一个或多个存储过程或者过程组。 【例7.3】删除例7.2修改后的存储过程getxs1。 DROP PROC getxs1 GO
2、使用SSMS删除存储过程 演示使用图形工具删除存储过程的步骤 。
7.3 带参数存储过程
使用存储过程的一个很重要的目的在于代码的重用。 一般我们把可重用部分放在存储过程的AS语句后， 对不能重用的部分信息通过参数来传递。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
教学三：存储过程一、教学目的（1）掌握T-SQL流控制语句；（2）掌握创建存储过程的方法；（3）掌握存储过程的执行方法；（4）掌握存储过程的管理和维护。
二、教学内容1、创建简单存储过程（1）创建一个名为stu_pr的存储过程，该存储过程能查询出051班学生的所有资料，包括学生的基本信息、学生的选课信息（含未选课同学的信息）。
要求在创建存储过程前请判断该存储过程是否已创建，若已创建则先删除，并给出“已删除！”信息，否则就给出“不存在，可创建！”的信息。
if exists(select name from sysobjects where name='stu_pr'and type='p')beginprint'已删除！'drop procedure stu_prendelseprint'不存在，可创建！'gocreate procedure stu_prasselect*from Student_20103322left outer join SC_20103322on (Student_20103322.Sno=SC_20103322.Sno)left outer join Course_20103322on (Course_o=SC_o)where classno='051'exec stu_pr2、创建带参数的存储过程（1）创建一个名为stu_proc1的存储过程，查询某系、某姓名的学生的学号、姓名、年龄，选修课程名、成绩。
系名和姓名在调用该存储过程时输入，其默认值分别为“%”与“林%”。
执行该存储过程，用多种参数加以测试。
if exists(select name from sysobjects where name='stu_proc1'and type='p')beginprint'已删除!'drop procedure stu_proc1endelseprint'不存在,可创建!'gocreate procedure stu_proc1@Sdept char(8)='%',@Sname varchar(8)='林%'asselect Sdept,Student_20103322.Sno,Sname,DATEDIFF(YEAR,Birth,GETDATE())age,Cname,Grade from Student_20103322,SC_20103322,Course_20103322where Student_20103322.Sno=SC_20103322.Snoand Course_o=SC_oand Sdept like@Sdeptand Sname like@Snameexecute stu_proc1'计算机系','林红'execute stu_proc1'信息安全','胡光璟'（2）创建一个名为Student_sc的存储过程，可查询出某段学号的同学的学号、姓名、总成绩。
（学号起始号与终止号在调用时输入，可设默认值）。
执行该存储过程。
if exists(select name from sysobjects where name='Student_sc'and type='p') beginprint'已删除！'drop procedure student_scendelseprint'不存在，可创建！'gocreate procedure Student_sc@Sno1char(8),@Sno2char(8)asselect Student_20103322.Sno,Sname,SUM(Grade)总成绩from Student_20103322,SC_20103322,Course_20103322where Student_20103322.Sno=SC_20103322.Snoand Course_o=SC_oand Student_20103322.Sno>=@Sno1and Student_20103322.Sno<=@Sno2group by Student_20103322.Sno,Snameexecute Student_sc'20110000','20110003'3、创建带输出参数的存储过程（1）创建一个名为Course_sum的存储过程，可查询某门课程考试的总成绩。
总成绩可以输出，以便进一步调用。
if exists(select name from sysobjects where name='Course_sum'and type='p') beginprint'已删除！'drop procedure Course_sumendelseprint'不存在，可创建！'gocreate procedure Course_sum@Cname varchar(20),@sum int outputasselect@sum=sum(Grade)from SC_20103322,Course_20103322where Course_o=SC_oand Cname=@Cnamegroup by SC_o,Cnamedeclare@ping intexec Course_sum'高数',@ping outputprint'高数的考试总成绩为：'+cast(@ping as varchar(20))（2）创建一执行该存储过程的批处理，要求当总成绩小于100时，显示信息为：“XX课程的总成绩为：XX,其总分未达100分”。
超过100时，显示信息为：“XX课程的总成绩为：XX”。
declare@sum intdeclare@Cname varchar(20)Exec Course_sum@cname,@sum outbeginif@sum<100print cast(@cname as varchar)+'课程的总成绩为:'+cast(@sum as varchar)+',其总分未达分' elseprint cast(@cname as varchar)+'课程的总成绩为:'+cast(@sum as varchar)enddeclare@sum intdeclare@Cname varchar(20)set@Cname='高数'Exec Course_sum@cname,@sum outbeginif@sum<100print cast(@cname as varchar)+'课程的总成绩为:'+cast(@sum as varchar)+',其总分未达分' elseprint cast(@cname as varchar)+'课程的总成绩为:'+cast(@sum as varchar)end4、创建带重编译及加密选项的存储过程创建一个名为update_sc、并带重编译及加密选项的存储过程，可更新指定学号、指定课程号的学生的课程成绩。
（学号、课程号由调用时输入）if exists(select name from sysobjects where name='update_sc'and type='p')beginprint'已删除！'drop procedure update_scendelseprint'不存在，可创建！'gocreate procedure update_sc@sno char(8),@cno char(3),@grade tinyintwith RECOMPILE,ENCRYPTIONasupdate SC_20103322set Grade=@gradewhere Sno=@sno and Cno=@cnodeclare@sno char(8),@cno char(3),@grade tinyintset@sno='20103322'set@cno='003'set@grade='100'exec update_sc@sno,@cno,@gradebeginprint cast(@sno as varchar)+'的'+cast(@cno as varchar)+'课程成绩为：'+cast(@grade as varchar)end5、使用T-SQL语句管理和维护存储过程（1）使用sp_helptext查看存储过程Student_sc的定义脚本exec sp_helptext student_sc（2）使用select语句查看Student_sc存储过程的定义脚本（提示：通过查询表sysobjects和表syscomments）select*from sysobjects,syscommentswhere name='Student_sc'（3）将存储过程stu_pr改为查询学号为2011001的学生的详细资料。
alter procedure stu_prasselect*from Student_20103322left outer join SC_20103322on (Student_20103322.Sno=SC_20103322.Sno)left outer join Course_20103322on (Course_o=SC_o)where Student_20103322.Sno='2011001'（4）删除存储过程stu_pr。
drop procedure stu_pr6、使用SQL Server Management Studio管理存储过程（1）在SQL Server Management Studio中重新创建刚删除的存储过程stu_prcreate procedure stu_prasbeginselect*from Student_20103322left outer join SC_20103322on (Student_20103322.Sno=SC_20103322.Sno)left outer join Course_20103322on (Course_o=SC_o)where Student_20103322.Sno='2011001'end（2）查看存储过程stu_pr，并将该过程修改为查询051班女生的所有资料。