数据库---存储过程

目录

• 一、存储过程的概念
• 二、存储过程的功能
• 三、存储过程的优缺点
  • 3.1 优点
  • 3.2 缺点
• 四、存储过程的类型
• 五、存储过程的用法
  • 5.1创建存储过程
  • 5.2使用存储过程
  • 5.3 修改存储过程
  • 5.4 删除存储过程
  • 5.5存储过程就是一个函数

 

---

回到顶部

一、存储过程的概念

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

回到顶部

二、存储过程的功能

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

?

1 2 3 4

<code-pre class="code-pre" id="pre-w5KYR4"><code-line class="line-numbers-rows"></code-line><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">procedure</span> 名称 (<span class="hljs-keyword">in</span>/<span class="hljs-keyword">out</span>/inout 参数名 参数数据类型) <code-line class="line-numbers-rows"></code-line>   <span class="hljs-keyword">Begin</span> <code-line class="line-numbers-rows"></code-line>       执行语句 <code-line class="line-numbers-rows"></code-line>   <span class="hljs-keyword">End</span></code-pre>

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13

<code-pre class="code-pre" id="pre-ihYPxZ"><code-line class="line-numbers-rows"></code-line><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">PROCEDURE</span> <span class="hljs-keyword">test</span>() <code-line class="line-numbers-rows"></code-line>   <span class="hljs-keyword">BEGIN</span> <code-line class="line-numbers-rows"></code-line>       <span class="hljs-keyword">declare</span> username <span class="hljs-built_in">varchar</span>(<span class="hljs-number">32</span>); <code-line class="line-numbers-rows"></code-line>       <span class="hljs-keyword">declare</span> username_cur <span class="hljs-keyword">cursor</span> <span class="hljs-keyword">for</span> <span class="hljs-keyword">select</span> 名字 <span class="hljs-keyword">from</span> <span class="hljs-keyword">users</span> <span class="hljs-keyword">where</span> <span class="hljs-keyword">id</span>%<span class="hljs-number">2</span>=<span class="hljs-number">0</span>; //定义游标 <code-line class="line-numbers-rows"></code-line>       open username_cur; //打开游标 <code-line class="line-numbers-rows"></code-line>       fetch username_cur into username; //游标前进1步 <code-line class="line-numbers-rows"></code-line>       while(stopflag=0)<span class="hljs-keyword">do</span> <code-line class="line-numbers-rows"></code-line>           ... <code-line class="line-numbers-rows"></code-line>           <span class="hljs-keyword">fetch</span> username_cur <span class="hljs-keyword">into</span> username; //游标前进一步 <code-line class="line-numbers-rows"></code-line>       <span class="hljs-keyword">end</span> <span class="hljs-keyword">while</span>; <code-line class="line-numbers-rows"></code-line>       close username_cur;//关闭游标 <code-line class="line-numbers-rows"></code-line>   <span class="hljs-keyword">END</span> </code-pre>

回到顶部

三、存储过程的优缺点

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使用存储过程

①点击运行，输入参数

②是否会产生对应的结果

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

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

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

5.3 修改存储过程

ALTER

5.4 删除存储过程

DROP

?

1 2

<code-pre class="code-pre" id="pre-M8ZCre"><code-line class="line-numbers-rows"></code-line><span class="hljs-comment">-- 删除存储过程：DROP PROCEDURE 存储过程名;</span> <code-line class="line-numbers-rows"></code-line><span class="hljs-keyword">DROP</span> <span class="hljs-keyword">PROCEDURE</span> student_grade3</code-pre>

5.5存储过程就是一个函数

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

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

__EOF__

本文作者：excellent_1
本文链接：https://www.cnblogs.com/xj-excellent/p/18067324.html
关于博主：互联网小萌新一名，希望从今天开始慢慢提高，一步步走向技术的高峰！
版权声明：本博客所有文章除特别声明外，均采用 BY-NC-SA 许可协议。转载请注明出处！
声援博主：如果您觉得文章对您有帮助，可以点击文章右下角【推荐】一下。您的鼓励是博主的最大动力！