数据库-存储过程 - 智科优网络技术服务中心

一、存储过程的概念

1、存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集。经编译后存储在数据库中，是”数据库函数”。
2、存储过程是数据库中的一个重要对象，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。
3、存储过程是由流控制和 SQL语句书写的过程，这个过程经编译和优化后存储在数据库服务器中。
4、存储过程可由应用程序通过一个调用来执行，而且允许用户声明变量。
5、存储过程可以接收和输出参数、返回执行存储过程的状态值，也可以嵌套调用。

二、存储过程的功能

主要提供以下功能，让用户可以设计出符合引用需求的程序
①变量说明
②ANSI兼容的SQL命令(如select,update…)
③一般流程控制命令(if else、while)
④内部函数

CREATE procedure 名称 (in/out/inout 参数名 参数数据类型)
	Begin
		执行语句
	End

参数说明：
1、默认in参数，只用于传入
2、out为传出参数，可以改变，并且可以返回
3、inout同事支持传入和返回
简单示例如下：

CREATE PROCEDURE test()
	BEGIN
		declare username varchar(32);
		declare username_cur cursor for select 名字 from users where id%2=0; //定义游标
		open username_cur; //打开游标
		fetch username_cur into username; //游标前进1步
		while(stopflag=0)do
			...
			fetch username_cur into username; //游标前进一步
		end while;
		close username_cur;//关闭游标
	END

三、存储过程的优缺点

3.1 优点

①增强了SQL语言的功能和灵活性
存储过程可以用流控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。企业规则的特点是要经常变化，如果把体现企业规则的运算程序放入应用程序中，则当企业规则发生变化时，就需要修改应用程序工作量非常之大（修改、发行和安装应用程序）。
如果把体现企业规则的运算放入存储过程中，则当企业规则发生变化时，只要修改存储过程就可以了，应用程序无须任何变化。
②可重复使用
存储过程可以重复使用,可减少数据库开发人员的工作量，复用性高。
③执行速度较快
在运行存储过程前，数据库已对其进行了语法和句法分析，并给出了优化执行方案。这种已经编译好的过程可极大地改善SQL语句的性能。于执行SQL语句的大部分工作已经完成，所以存储过程能以极快的速度执行。存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
④保证数据的安全性和完整性
体现在以下2点，第一点是通过存储过程可以使没有权限的用户在控制之下间接地存取数据库，从而保证数据的安全。第二点是通过存储过程可以使相关的动作在一起发生，从而可以维护数据库的完整性。
⑤节省网络开销
客户端调用存储过程只需要传存储过程名和相关参数即可，与传输SQL语句相比自然数据量少了很多。从而可以降低网络的通信量，即节省网络开销。

3.2 缺点

① 调试麻烦
但是用 PL/SQL Developer 调试很方便！弥补这个缺点。
② 移植问题
数据库端代码当然是与数据库相关的。但是如果是做工程型项目，基本不存在移植问题。
③ 重新编译问题
因为后端代码是运行前编译的，如果带有引用关系的对象发生改变时，受影响的存储过程、包将需要重新编译（不过也可以设置成运行时刻自动编译）。
④ 维护性较难
如果在一个程序系统中大量的使用存储过程，到程序交付使用的时候随着用户需求的增加会导致数据结构的变化，接着就是系统的相关问题了，最后如果用户想维护该系统可以说是很难很难、而且代价是空前的，维护起来更麻烦。
======================================================
作出以下总结：
优点：
存储过程可封装，并隐藏复杂的商业逻辑。
存储过程可以回传值，并可以接受参数。
存储过程无法使用 SELECT 指令来运行，因为它是子程序，与查看表，数据表或用户定义函数不同。
存储过程可以用在数据检验，强制实行商业逻辑等。
缺点：
存储过程，往往定制化于特定的数据库上，因为支持的编程语言不同。当切换到其他厂商的数据库系统时，需要重写原有的存储过程。
存储过程的性能调校与撰写，受限于各种数据库系统。
======================================================

四、存储过程的类型

4.1系统存储过程
以sp_开头,用来进行系统的各项设定，取得信息，相关管理工作。
4.2本地存储过程
用户创建的存储过程是由用户创建并完成某一特定功能的存储过程，事实上一般所说的存储过程就是指本地存储过程。此种用法最多。
4.3临时存储过程
一是本地临时存储过程，以井字号(#)作为其名称的第一个字符，则该存储过程将成为一个存放在tempdb数据库中的本地临时存储过程，且只有创建它的用户才能执行它;
二是全局临时存储过程，以两个井字号(##)号开始，则该存储过程将成为一个存储在tempdb数据库中的全局临时存储过程，全局临时存储过程一旦创建，以后连接到服务器的任意用户都可以执行它，而且不需要特定的权限。
4.4远程存储过程
在SQL Server2005中，远程存储过程(Remote Stored Procedures)是位于远程服务器上的存储过程，通常可以使用分布式查询和EXECUTE命令执行一个远程存储过程。
4.5扩展存储过程
扩展存储过程(Extended Stored Procedures)是用户可以使用外部程序语言编写的存储过程，而且扩展存储过程的名称通常以xp_开头。
===================================================
因此，总结如下：
系统存储过程【名字以sp_为前缀，存储在master库中】
本地存储过程【存储在用户定义的数据库中】
临时存储过程【名字以#或##开头的】
扩展存储过程【名字都已xp_为前缀，储存在master库中】

===================================================

五、存储过程的用法

5.1创建存储过程

①打开navicat，选中函数，鼠标右击

②选择过程，输入存储对象名，点击下一步

③下拉框中，选择参数名，参数类型

④在规定的模板内，书写SQL语句

⑤最后输入参数长度，添加(长度)

5.2使用存储过程

①点击运行，输入参数

②是否会产生对应的结果

-- 常规的SQL查询 :查询信息管理系学生的考试情况，列出学生的姓名、所在系、课程名、考试成绩
SELECT Sname,Sdept,Cname,Grade
	from Student s INNER JOIN SC
	ON s.Sno = SC.Sno INNER JOIN Course c
	ON c.Cno = SC.Cno
	where Sdept = "信息管理系"


/*
如果使用存储过程来怎么书写SQL???
例如：带输入参数的存储过程：查询某个指定系学生的考试情况，列出学生的姓名、所在系、课程名、考试成绩
*/
CREATE DEFINER=`root`@`localhost` PROCEDURE `student_grade3`(IN `dept` char(25))
BEGIN
	SELECT Sname,Sdept,Cname,Grade
	from Student s INNER JOIN SC
	ON s.Sno = SC.Sno INNER JOIN Course c
	ON c.Cno = SC.Cno
	where Sdept = @dept;
END
-- 执行上述存储过程，查询 信息管理系 学生的修课情况
CALL student_grade3 信息管理系

调用存储对象的好处就是，不需要每一次都重新写sql语句，只需要调入所需的参数就可以立马出结果，省时省力。像上面的例子，还可以查询计算机系、通信工程系的学生的考试情况，很灵活。

5.3 修改存储过程

ALTER

5.4 删除存储过程

DROP

-- 删除存储过程：DROP PROCEDURE 存储过程名;
DROP PROCEDURE student_grade3

5.5存储过程就是一个函数

可以把存储过程，当成一个函数。合理有效的利用函数，可以一次性插入大量数据，比如一次性插入上千条、上万条数据。

参考的博客链接：
https://blog.csdn.net/BADAO_LIUMANG_QIZHI/article/details/87855148、
https://blog.csdn.net/BADAO_LIUMANG_QIZHI/article/details/129179745

声明：本站所有文章，如无特殊说明或标注，均为本站原创发布。任何个人或组织，在未征得本站同意时，禁止复制、盗用、采集、发布本站内容到任何网站、书籍等各类媒体平台。如若本站内容侵犯了原著者的合法权益，可联系我们进行处理。

数据库—存储过程