您现在的位置是:首页 >技术杂谈 >【SQL 必知必会】- 第十九课 使用存储过程网站首页技术杂谈

【SQL 必知必会】- 第十九课 使用存储过程

Roki Zhang 2023-06-04 00:00:03
简介【SQL 必知必会】- 第十九课 使用存储过程

目录

写在前面

19.1 存储过程

19.2 为什么要使用存储过程

19.3 执行存储过程

19.4 创建存储过程

        注释代码


        这一课介绍什么是存储过程,为什么要使用存储过程,如何使用存储过程,以及创建和使用存储过程的基本语法。

写在前面

        本课的内容主要是讲述【存储过程】,即常说的 procedure,在实际的工作中,国内的很多公司都会刻意避开这个方式(至少我了解的北京、上海、杭州等公司是如此的),并且本课的内容也是极其的枯燥,procedure 的语法我这都没有介绍,可以说很复杂,并且不那么容易理解。

        如果你同时会好几种语言,那么你一定感觉出来了,不同的语言之间存在细微的差异,甚至一个功能,每个语言的实现方式 / 名称都不同,语言的混杂会让你后续的工作 / 学习愈加艰难。

        建议不是工作需要,这节课可以跳过,或者只做了解。如果需要深入了解的话,本课是不能满足你的需求的,你需要去寻找更加合适的博客 / 课程。

19.1 存储过程

        迄今为止,我们使用的大多数 SQL 语句都是针对一个或多个表的单条语句。并非所有操作都这么简单,经常会有一些复杂的操作需要多条语句才能完成。

        简单来说,存储过程就是为以后使用而保存的一条或多条 SQL 语句。可将其视为批文件,虽然它们的作用不仅限于批处理。

        存储过程很复杂,全面介绍它需要很大篇幅。本课不打算讲解存储过程的所有内容,只给出简单介绍,让读者对它们的功能有所了解。


19.2 为什么要使用存储过程

        我们知道了什么是存储过程,那么为什么要使用它们呢?理由很多,下面列出一些主要的。

  • 通过把处理封装在一个易用的单元中,可以简化复杂的操作(如前面例子所述)。
  • 由于不要求反复建立一系列处理步骤,因而保证了数据的一致性。如果所有开发人员和应用程序都使用同一存储过程,则所使用的代码都是相同的。这一点的延伸就是防止错误。需要执行的步骤越多,出错的可能性就越大。防止错误保证了数据的一致性。
  • 简化对变动的管理。如果表名、列名或业务逻辑(或别的内容)有变化,那么只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化。这一点的延伸就是安全性。通过存储过程限制对基础数据的访问,减少了数据讹误(无意识的或别的原因所导致的数据讹误)的机会。
  • 因为存储过程通常以编译过的形式存储,所以 DBMS 处理命令所需的工作量少,提高了性能。
  • 存在一些只能用在单个请求中的 SQL 元素和特性,存储过程可以使用它们来编写功能更强更灵活的代码。

        换句话说,使用存储过程有三个主要的好处,即简单、安全、高性能。显然,它们都很重要。不过,在将 SQL 代码转换为存储过程前,也必须知道它的一些缺陷。

  • 不同DBMS 中的存储过程语法有所不同。
  • 一般来说,编写存储过程比编写基本 SQL 语句复杂,需要更高的技能,更丰富的经验。

        尽管有这些缺陷,存储过程还是非常有用的,并且应该使用。事实上,多数 DBMS 都带有用于管理数据库和表的各种存储过程。

        其实不然,现在有更好的解决方案。
        使用集算器 SPL 可以替代存储过程,实现“库外存储过程”又或者将逻辑处理放在前后端代码中而不是在 SQL中。想要替换存储过程的主要原因:

  1. 调试困难
  2. 编写困难
  3. 阅读困难

19.3 执行存储过程

        存储过程的执行远比编写要频繁得多,因此我们先介绍存储过程的执行。执行存储过程的SQL 语句很简单,即 EXECUTE。EXECUTE 接受存储过程名和需要传递给它的任何参数。

EXECUTE AddNewProduct( 'JTS01','Stuffed Eiffel Tower',
6.49,'Plush stuffed toy with the text LaTour Eiffel in red white and blue' );

        这里执行一个名为AddNewProduct 的存储过程,将一个新产品添加到Products 表中。AddNewProduct 有四个参数,分别是:供应商ID(Vendors 表的主键)、产品名、价格和描述。这4 个参数匹配存储过程中4 个预期变量(定义为存储过程自身的组成部分)。此存储过程将新行添加到Products 表,并将传入的属性赋给相应的列。

        我们注意到,在 Products 表中还有另一个需要值的列 prod_id 列,它是这个表的主键。为什么这个值不作为属性传递给存储过程?要保证恰当地生成此 ID,最好是使生成此 ID 的过程自动化(而不是依赖于最终用户的输入)。这也是这个例子使用存储过程的原因

        简单来说就是让主键自动生成。

        以下是存储过程所完成的工作:

  • 验证传递的数据,保证所有 4 个参数都有值;
  • 生成用作主键的唯一ID;

        将新产品插入Products 表,在合适的列中存储生成的主键和传递的数据。

  • 这就是存储过程执行的基本形式。对于具体的 DBMS,可能包括以下的执行选择:
  • 参数可选,具有不提供参数时的默认值;
  • 不按次序给出参数,以“参数=值”的方式给出参数值。
  • 输出参数,允许存储过程在正执行的应用程序中更新所用的参数。
  • 用 SELECT 语句检索数据。
  • 返回代码,允许存储过程返回一个值到正在执行的应用程序。

19.4 创建存储过程

        正如所述,存储过程的编写很重要。为了获得感性认识,我们来看一个简单的存储过程例子,它对邮件发送清单中具有邮件地址的顾客进行计数。下面是该过程的 Oracle 版本:

CREATE PROCEDURE MailingListCount (
    ListCount OUT INTEGER
)
IS
v_rows INTEGER;
BEGIN
SELECT COUNT(*) INTO v_rows
FROM Customers
WHERE NOT cust_email IS NULL;
ListCount := v_rows;
END;

        这个存储过程有一个名为 ListCount 的参数。此参数从存储过程返回一个值而不是传递一个值给存储过程。关键字OUT 用来指示这种行为。Oracle 支持 IN(传递值给存储过程)、OUT(从存储过程返回值,如这里)、INOUT(既传递值给存储过程也从存储过程传回值)类型的参数。存储过程的代码括在 BEGIN 和 END 语句中,这里执行一条简单的 SELECT 语句,它检索具有邮件地址的顾客。然后用检索出的行数设置 ListCount(要传递的输出参数)。

        调用 Oracle 例子可以像下面这样:

var ReturnValue NUMBER EXEC MailingListCount(:ReturnValue);
SELECT ReturnValue;

        这段代码声明了一个变量来保存存储过程返回的任何值,然后执行存储过程,再使用 SELECT 语句显示返回的值。


        注释代码

        应该注释所有代码,存储过程也不例外。增加注释不影响性能,因此不存在缺陷(除了增加编写时间外)。注释代码的好处很多,包括使别人(以及你自己)更容易地理解和更安全地修改代码。对代码进行注释的标准方式是在之前放置--(两个连字符)。

风语者!平时喜欢研究各种技术,目前在从事后端开发工作,热爱生活、热爱工作。