mysql存储过程

2021-10-19 05:16:15

存储过程

需要MySQL 5 MySQL 5添加了对存储过程的支持，因此，本章内容适用于MySQL 5及以后的版本。

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

在使用的时候直接调用即可

为什么要使用存储过程

既然我们知道了什么是存储过程，那么为什么要使用它们呢？有许多理由，下面列出一些主要的理由。
? 通过把处理封装在容易使用的单元中，简化复杂的操作（正如前
面例子所述）。
? 由于不要求反复建立一系列处理步骤，这保证了数据的完整性。
如果所有开发人员和应用程序都使用同一（试验和测试）存储过
程，则所使用的代码都是相同的。
这一点的延伸就是防止错误。需要执行的步骤越多，出错的可能
性就越大。防止错误保证了数据的一致性。
? 简化对变动的管理。如果表名、列名或业务逻辑（或别的内容）
有变化，只需要更改存储过程的代码。使用它的人员甚至不需要
知道这些变化。
这一点的延伸就是安全性。通过存储过程限制对基础数据的访问减
少了数据讹误（无意识的或别的原因所导致的数据讹误）的机会。
? 提高性能。因为使用存储过程比使用单独的SQL语句要快。
? 存在一些只能用在单个请求中的MySQL元素和特性，存储过程可
以使用它们来编写功能更强更灵活的代码（在下一章的例子中可
以看到。）
换句话说，使用存储过程有3个主要的好处，即简单、安全、高性能。
显然，它们都很重要。不过，在将SQL代码转换为存储过程前，也必须知
道它的一些缺陷。
----------------------缺陷--------------------------------
? 一般来说，存储过程的编写比基本SQL语句复杂，编写存储过程
需要更高的技能，更丰富的经验。
? 你可能没有创建存储过程的安全访问权限。许多数据库管理员限
制存储过程的创建权限，允许用户使用存储过程，但不允许他们
创建存储过程。
尽管有这些缺陷，存储过程还是非常有用的，并且应该尽可能地使
用。

不能编写存储过程？你依然可以使用 MySQL将编写存储过程的安全和访问与执行存储过程的安全和访问区分开来。这
是好事情。即使你不能（或不想）编写自己的存储过程，也仍然可以在适当的时候执行别的存储过程。

使用存储过程

MySQL称存储过程的执行为调用，因此MySQL执行存储过程的语句为 CALL 。 CALL 接受存储过程的名字以及需要传递给它的任意参数

CALL productpricing(@pricelow,@pricemax,@priceavg); -- 传入参数
select @pricelow,@pricemax,@priceavg; -- 查询数据

创建存储过程

DELIMITER //  -- 指定分隔符为//
CREATE PROCEDURE productpricing()  -- 创建存储过程
BEGIN
    SELECT AVG(salary) AS price FROM emp;  -- 指定查询语句
END//
DELIMITER ; 

CALL productpricing(); -- 调用存储过程
DELIMITER ;

DROP PROCEDURE productpricing; -- 删除存储过程

此存储过程名为productpricing ，用 CREATE PROCEDURE productpricing() 语句定义。如果存储过程接受参数，它们将在 () 中列举出来。此存储过程没有参数，但后跟的 () 仍然需要。 BEGIN 和 END 语句用来限定存储过程体，过程体本身仅是一个简单的 SELECT 语句

在MySQL处理这段代码时，它创建一个新的存储过程 productpricing 。没有返回数据，因为这段代码并未调用存储过程，这里只是为
以后使用而创建它。

mysql 命令行客户机的分隔符


    如果你使用的是 mysql 命令行实用程序，应该仔细阅读此说明。默认的MySQL语句分隔符为 ; （正如你已经在迄今为止所使用的MySQL语句中所看到的那样）。 mysql 命令行实用程序也使用 ; 作为语句分隔符。如果命令行实用程序要解释存储过程自身内的 ; 字符，则它们最终不会成为存储过程的成分，这会使存储过程中的SQL出现句法错误。解决办法是临时更改命令行实用程序的语句分隔符，如下所示：其中， DELIMITER // 告诉命令行实用程序使用 // 作为新的语句结束分隔符，可以看到标志存储过程结束的 END 定义为 END // 而不是 END; 。这样，存储过程体内的 ; 仍然保持不动，并且正确地传递给数据库引擎。最后，为恢复为原来的语句分隔符，可使用 DELIMITER ; 。除 \ 符号外，任何字符都可以用作语句分隔符。如果你使用的是 mysql 命令行实用程序，在阅读本章时请记住这里的内容。

使用参数

productpricing 只是一个简单的存储过程，它简单地显示 SELECT 语句的结果。一般，存储过程并不显示结果，而是把结果返回给你指定的变量

变量（variable）内存中一个特定的位置，用来临时存储数据

delimiter //
create procedure productpricing(  -- 创建带参数的存储过程
     out pl decimal(8,2),
     out ph decimal(8,2),
     out pa decimal(8,2)
)
begin 
    select min(salary) into pl from emp;
    select max(salary) into ph from emp;
    select avg(salary) into pa from emp;
end//
delimiter ;


call productpricing(@pricelow,@pricemax,@priceavg); -- 传入参数

select @pricelow;
select @pricelow,@pricemax,@priceavg; -- 查询数据

此存储过程接受3个参数： pl 存储产品最低价格， ph 存储产品最高价格， pa 存储产品平均价格。每个参数必须具有指定的类
型，这里使用十进制值。关键字 OUT 指出相应的参数用来从存储过程传出一个值（返回给调用者）。MySQL支持 IN （传递给存储过程）、 OUT （从存储过程传出，如这里所用）和 INOUT （对存储过程传入和传出）类型的参数。存储过程的代码位于 BEGIN 和 END 语句内，如前所见，它们是一系列SELECT 语句，用来检索值，然后保存到相应的变量（通过指定 INTO 关键字）。

参数的数据类型

 存储过程的参数允许的数据类型与表中使用的数据类型相同。附录D列出了这些类型。
注意，记录集不是允许的类型，因此，不能通过一个参数返回多个行和列。这就是前面的例子为什么要使用3个参数（和3条 SELECT 语句）的原因。

为调用此修改过的存储过程，必须指定3个变量名，如下所示：

call productpricing(@pricelow,@pricemax,@priceavg); -- 传入参数

由于此存储过程要求3个参数，因此必须正好传递3个参数，不多也不少。所以，这条 CALL 语句给出3个参数。它们是存储过
程将保存结果的3个变量的名字。

变量名 所有MySQL变量都必须以 @ 开始。

为了显示检索出的产品平均价格，可如下进行：

select @pricelow;
select @pricelow,@pricemax,@priceavg; -- 查询数据

下面是另外一个例子，这次使用 IN 和 OUT 参数。 ordertotal 接受订单
号并返回该订单的合计：

delimiter //
create procedure ordertotal(
    in onnumber int,  -- in订单号被传入存储过程
    out total decimal(8,2)-- 从存储过程返回合计
)
begin 
   select sum(salary) from emp where id>=onnumber into total;
end//
delimiter ;

call ordertotal(1004,@total); -- 添加参数 查询id大于1004的salary数据值的和

select @total;

onumber 定义为 IN ，因为订单号被传入存储过程。 total定义为 OUT ，因为要从存储过程返回合计。 SELECT 语句使用这两个
参数， WHERE 子句使用 onumber 选择正确的行， INTO 使用 total存储计算出来的合计。

建立智能存储过程

迄今为止使用的所有存储过程基本上都是封装MySQL简单的 SELECT语句。虽然它们全都是有效的存储过程例子，但它们所能完成的工作你直接用这些被封装的语句就能完成（如果说它们还能带来更多的东西，那就是使事情更复杂）。只有在存储过程内包含业务规则和智能处理时，它们的威力才真正显现出来。考虑这个场景。你需要获得与以前一样的订单合计，但需要对合计增加营业税，不过只针对某些顾客（或许是你所在州中那些顾客）。那么，你需要做下面几件事情：

? 获得合计（与以前一样）；
? 把营业税有条件地添加到合计；
? 返回合计（带或不带税）。

delimiter //
create procedure ordertotal2(
   in onumber int,  
   in taxable boolean, 
   out ototal decimal(8,2)
)
begin 

   declare total decimal(8,2);
   declare taxrate int default 6;
   select salary from emp where id=onumber into total;

   if taxable then   -- 如果taxable为true则对total进行增加
       select total+(total*100) into total;
       
   end if;
   
   select total into ototal;  -- 无论taxable存不存在都会执行 并合计到ototal
   
end//
delimiter ;   

call ordertotal2(1001,1,@ototal);   -- 传入参数  0代表false   1代表true
select @ototal; 


show create procedure ordertotal2; -- 查看创建存储过程的sql语句

此存储过程有很大的变动。首先，增加了注释（前面放置 -- ）。在存储过程复杂性增加时，这样做特别重要。添加了另外一个
参数 taxable ，它是一个布尔值（如果要增加税则为真，否则为假）。在存储过程体中，用 DECLARE 语句定义了两个局部变量。 DECLARE 要求指定变量名和数据类型，它也支持可选的默认值（这个例子中的 taxrate 的默认被设置为 6% ）。 SELECT 语句已经改变，因此其结果存储到 total （局部变量）而不是 ototal 。 IF 语句检查 taxable 是否为真，如果为真，则用另一 SELECT 语句增加营业税到局部变量 total 。最后，用另一 SELECT 语句将total （它增加或许不增加营业税）保存到 ototal 。

IF 语句 这个例子给出了MySQL的 IF 语句的基本用法。 IF 语句还支持 ELSEIF 和 ELSE 子句（前者还使用 THEN 子句，后者不
使用）。在以后章节中我们将会看到 IF 的其他用法（以及其他流控制语句）。

为了获得包括何时、由谁创建等详细信息的存储过程列表，使用 SHOW PROCEDURE STATUS 。

限制过程状态结果 SHOW PROCEDURE STATUS 列出所有存储过程。为限制其输出，可使用 LIKE 指定一个过滤模式，例如：

SHOW PROCEDURE STATUS LIKE ‘ordertotal‘;