存储过程 面试题（存储过程测试）



【版权声明】未经博主同意，谢绝转载！（请尊重原创，博主保留追究权）
https://www.cnblogs.com/cnb-yuchen/p/
出自【进步*于辰的博客】

“存储过程”是一种存储于数据库、封装了SQL语句和流程控制语句、进而通过类似“方法调用”的形式来调用（如：传参、获取返回值）、从而实现业务功能（即将一定程序业务迁移到数据库内，将业务交由数据库管理）的数据结构。

优点：

1. 存储过程对复杂的SQL语句进行了封装，而调用简便，故简化了一些复杂的操作（如：业务逻辑）。
2. 简化了对变动的管理。
   若数据表变动（如：表名、字段名修改）或业务变动，不需要变动代码。
3. 提高了程序性能。
   因为存储过程存储于数据库，减少了SQL传输的流量（一般会用到存储过程的SQL语句都很长，几十上百行）。并且，数据库会在调用时对存储过程进行编译。大多数数据库（如：Oracle、MySQL）中，编译后的存储过程存储于数据库缓存，其中，MySQL存储过程是“按需编译”。
   若存储过程在==单个连接中被多次调用==，调用的就是缓存内的存储过程，进一步提高了查询速度；否则会先对存储过程进行编译，此时存储过程的执行效率相当于查询。
4. 存储过程提供了一个接口供开发人员调用，这使得开发人员==不必考虑其内部细节==。同时，只需向访问存储过程的应用程序授权，而不必为其提供基础数据表权限，故提高了安全性，且可重用和透明。

缺点：

1. 存储过程会占用当前连接内存。
   因为存储过程经过编译存储于缓存中，而缓存是内存的一部分。其中，由于MySQL设计的初衷是“高效的查询，非逻辑运算”，故若存储过程中使用了大量的逻辑操作则会占用大量的CPU。
2. 存储过程的结构使得开发复杂的存储过程变得困难。
3. 存储过程难以调试（仅有很少的工具可以调试存储过程），使得开发和维护都不容易。
4. 对数据库的依赖性高，难以移植（存储过程的内部就是SQL语句，自然对数据库依赖性高）。

补充说明：

关于流程控制语句，可查阅博文《[MySQL]流程控制语句》。

大都是情况下，存储过程内都会包含流程控制语句。为何？因为使用存储过程的原因无非两种：

1. 封装一条复杂的SQL语句（往往是一个事务）。
2. 封装多个原子操作（SQL语句），而这些原子操作间会进行一些逻辑运算或数据处理。

   工具：navicat。
   
   
   
   
   所有存储过程存储于数据表中。

   数据表：

   需求：查询工资是某员工双倍的员工的员工名。
   （注：此示例非常简单，仅是向大家展示存储过程的基本使用，关键在于后续对存储过程使用的说明）
   rootlocalhostP_admin_EIByENo_SelempNoempName` varchar(20))
   BEGIN

   set doubleSal = doubleSal 2;// 赋值，必须使用 set。注意：此处不兼容：=/+=

   select empName;// 这是固定格式，相当于”result 变量“

END

这是固定格式，无论是在navicat命令行、cmd，还是在程序中。

对应传入参数；对应传出参数，也可以是、，就目前我所知，后的标识任意（存储过程的返回值由决定，与后的标识无关，但传出参数的位置必须至少有一个（相当于占位符）。

存储过程的实参与Java方法实参有一定类似，即赋值类型限制。如示例，可以是，而不能是（字符型）。

什么是游标？“游标”是一种能够对结果集中的每一行记录进行定位、并对所指向记录的数据进行操作的数据结构。
如：Java迭代器（）中的也是游标，也称之为“光标”，其初始指向第一个元素的前面，即。

游标的用途是什么？迭代器有何用途？遍历。因此，存储过程中的游标是用于控制遍历，或者说用于在循环中获取记录。

数据表：

需求：根据用户ID，移除所有用户相关记录。

说明：

1. 游标定义格式：。（其中的是查询型SQL语句）
   1. 游标使用（）前需要先打开（），游标打开时如的游标一般，==初始指向第一行的前面==。使用完后（循环结束）建议关闭游标（）。其中，游标可多次打开。
   2. 获取游标值（下一行记录）：。 1：示例中与是做什么的？

大家用过Java迭代器的就知道，当调用时，在底层会先判断是否存在下一个元素，若存在，则返回此元素；否则返回，不会出现异常。

在中，当时，同样会先判断是否存在下一行记录，若存在则返回此记录。不同的是，若不存在，则返回当前记录（死循环）。

因此，示例中 A 的作用是，当时，A 也会执行，若满足时（不存在下一行记录），执行。那么，就可以使用此变量退出循环，避免“死循环”。

2：“游标可多次打开”是什么意思？

这就关乎游标的定义了（）。在存储过程的规范中，有这么一条：

所有的定义（）必须置于开头，且变量或条件的定义必须在游标（）的定义之前，且游标定义必须在之前。（上文【存储过程的使用】中说道）

所以，的定义也必须置于开头。不过，并不是在游标定义时就执行，而是在打开游标时。

因此，在游标再次打开时，其再次指向第一行的前面（可多次遍历）。

3：示例中的是做什么的？

在plsql中，默认情况下，DML需要手动提交或回滚。其中，可以使用保存点（）设置回滚范围。而在navicat中，由于其默认将某一条SQL都作为一个事务，所有DML都自动提交，故无法自定义回滚。

因此，可以使用（打开事务）自定义事务范围，关闭自动提交功能。

寻常我们在navicat中执行SQL可能并不会注意这个问题，但在存储过程中则必须如此（自定义事务）。

因此，的作用就是当存储过程中的某条SQL出现异常时，执行后面内的内容，最后再通过某个变量判断是否出现异常，进而提交或回滚。（注：存储过程中出现异常时不会停止执行）

本文中的例子是为了方便大家理解、便于阐述存储过程而简单举出或是我曾用过的，不一定有实用性，仅是抛砖引玉。

其实存储过程的细节很多，只是我没有那么细致地一一进行阐述。我阐述的原则是“以吾之理解，着重之阐述”。因此，这篇文章可能并不适合 0 基础。

给大家推荐两篇博文（转发），这是我系统学习MySQL存储过程时参考的文章。

1. MySQL中的存储过程（详细篇）；
2. Mysql存储过程大全。

如果大家想要快速掌握这个知识点，我的建议是“多测试，学以致用”。

本文完结。

版权声明：

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如若内容造成侵权、违法违规、事实不符，请将相关资料发送至xkadmin@xkablog.com进行投诉反馈，一经查实，立即处理！

转载请注明出处，原文链接：https://www.xkablog.com/te-aq/68654.html