MYSQL存储过程作用及语法

淩亂°似流年 2022-06-07 01:44 278阅读 0赞

> 转载自 [http://www.cnblogs.com/l5580/p/5993238.html][http_www.cnblogs.com_l5580_p_5993238.html]

MySQL5 中添加了存储过程的支持。

大多数SQL语句都是针对一个或多个表的单条语句。并非所有的操作都怎么简单。经常会有一个完整的操作需要多条才能完成

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

### **为什么要使用存储过程**： ###

**优点**

1.  通过吧处理封装在容易使用的单元中，简化复杂的操作
2.  由于不要求反复建立一系列处理步骤，这保证了数据的完整性。如果开发人员和应用程序都使用了同一存储过程，则所使用的代码是相同的。还有就是防止错误，需要执行的步骤越多，出错的可能性越大。防止错误保证了数据的一致性。
3.  简化对变动的管理。如果表名、列名或业务逻辑有变化。只需要更改存储过程的代码，使用它的人员不会改自己的代码了都。
4.  提高性能，因为使用存储过程比使用单条SQL语句要快
5.  存在一些职能用在单个请求中的MySQL元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码

**换句话说3个主要好处简单、安全、高性能**

**缺点**

1.  一般来说，存储过程的编写要比基本的SQL语句复杂，编写存储过程需要更高的技能，更丰富的经验。
2.  你可能没有创建存储过程的安全访问权限。许多数据库管理员限制存储过程的创建，允许用户使用存储过程，但不允许创建存储过程

存储过程是非常有用的，应该尽可能的使用它们

### **执行存储过程** ###

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

CALL productpricing(@pricelow , @pricehigh , @priceaverage);
    
    //执行名为productpricing的存储过程，它计算并返回产品的最低、最高和平均价格

### **创建存储过程** ###

CREATE  PROCEDURE 存储过程名()

一个例子说明：一个返回产品平均价格的存储过程如下代码：

CREATE  PROCEDURE productpricing() BEGIN SELECT Avg(prod_price) AS priceaverage FROM products;
    
    END;
    
    //创建存储过程名为productpricing，如果存储过程需要接受参数，可以在（）中列举出来。即使没有参数后面仍然要跟（）。BEGIN和END语句用来限定存储过程体，过程体本身是个简单的SELECT语句

在MYSQL处理这段代码时会创建一个新的存储过程productpricing。没有返回数据。因为这段代码时创建而不是使用存储过程。

### **Mysql命令行客户机的分隔符** ###

默认的MySQL语句分隔符为分号 ; 。Mysql命令行实用程序也是 ; 作为语句分隔符。如果命令行实用程序要解释存储过程自身的 ; 字符，则他们最终不会成为存储过程的成分，这会使存储过程中的SQL出现句法错误

解决方法是临时更改命令实用程序的语句分隔符

DELIMITER // //定义新的语句分隔符为//
    
    CREATE PROCEDURE productpricing() BEGIN SELECT Avg(prod_price) AS priceaverage FROM products;
    
    END //
    
    DELIMITER ;    //改回原来的语句分隔符为 ;除\符号外，任何字符都可以作为语句分隔符
    
    CALL productpricing();  //使用productpricing存储过程

执行刚创建的存储过程并显示返回的结果。因为存储过程实际上是一种函数，所以存储过程名后面要有()符号

### **删除存储过程** ###

DROP PROCEDURE productpricing ; //删除存储过程后面不需要跟(),只给出存储过程名

为了删除存储过程不存在时删除产生错误，可以判断仅存储过程存在时删除

DROP PROCEDURE IF EXISTS

使用参数

Productpricing只是一个简单的存储过程，他简单地显示SELECT语句的结果。

一般存储过程并不显示结果，而是把结果返回给你指定的变量

CREATE PROCEDURE productpricing( OUT p1 DECIMAL(8,2), OUT ph DECIMAL(8,2), OUT pa DECIMAL(8,2), ) BEGIN SELECT Min(prod_price) INTO p1 FROM products;
    
        SELECT Max(prod_price)
    
            INTO ph
    
            FROM products;
    
        SELECT Avg(prod_price)
    
            INTO pa
    
            FROM products;
    
    END;

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

调用修改过的存储过程必须指定3个变量名：

CALL productpricing(@pricelow , @pricehigh , @priceaverage);

这条CALL语句给出3个参数，它们是存储过程将保存结果的3个变量的名字

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

使用变量

SELECT @priceaverage ;
    
    SELECT @pricelow , @pricehigh , @priceaverage ;   //获得3给变量的值

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

CREATE PROCEDURE ordertotal( IN onumber INT, OUT ototal DECIMAL(8,2) ) BEGIN SELECT Sum(item_price*quantity) FROM orderitems WHERE order_num = onumber INTO ototal;
    
    END;
    
    //onumber定义为IN，因为订单号时被传入存储过程，ototal定义为OUT，因为要从存储过程中返回合计，SELECT语句使用这两个参数，WHERE子句使用onumber选择正确的行，INTO使用ototal存储计算出来的合计

为了调用这个新的过程，可以使用下列语句：

CALL ordertotal(2005 , @total);   //这样查询其他的订单总计可直接改变订单号即可
    
            SELECT @total;

### **建立智能的存储过程** ###

上面的存储过程基本都是封装MySQL简单的SELECT语句，但存储过程的威力在它包含业务逻辑和智能处理时才显示出来

例如：你需要和以前一样的订单合计，但需要对合计增加营业税，不活只针对某些顾客（或许是你所在区的顾客）。那么需要做下面的事情：

1.  获得合计（与以前一样）
2.  吧营业税有条件地添加到合计
3.  返回合计（带或不带税）

存储过程的完整工作如下：

-- Name: ordertotal
    
                -- Parameters: onumber = 订单号
    
                -- taxable = 1为有营业税 0 为没有
    
                -- ototal = 合计
    
                CREATE PROCEDURE ordertotal( IN onumber INT, IN taxable BOOLEAN, OUT ototal DECIMAL(8,2) -- COMMENT()中的内容将在SHOW PROCEDURE STATUS ordertotal()中显示，其备注作用 ) COMMENT 'Obtain order total , optionally adding tax' BEGIN -- 定义total局部变量 DECLARE total DECIMAL(8,2) DECLARE taxrate INT DEFAULT 6;
    
    
    
                -- 获得订单的合计，并将结果存储到局部变量total中
    
                SELECT Sum(item_price*quantity) FROM orderitems WHERE order_num = onumber INTO total;
    
    
    
                -- 判断是否需要增加营业税，如为真，这增加6%的营业税
    
                IF taxable THEN
    
                SELECT total+(total/100*taxrate) INTO total;
    
                      END IF;
    
                -- 把局部变量total中才合计传给ototal中
    
                SELECT total INTO ototal;
    
                END;

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

COMMENT关键字 本列中的存储过程在CREATE PROCEDURE 语句中包含了一个COMMENT值，他不是必需的，但如果给出，将在SHOW PROCEDURE STATUS的结果中显示

IF语句 这个例子中给出了MySQL的IF语句的基本用法。IF语句还支持ELSEIF和ELSE子句（前者还使用THEN子句，后者不使用）

### **检查存储过程** ###

为显示用来创建一个存储过程的CREATE语句，使用SHOW CREATE PROCEDURE语句

SHOW CREATE PROCEDURE ordertotal;

为了获得包括何时、有谁创建等详细信息的存储过程列表。使用SHOW PROCEDURE STATUS.限制过程状态结果，为了限制其输出，可以使用LIKE指定一个过滤模式，例如：SHOW PROCEDURE STATUS LIKE ”ordertotal;

--------------------

## 第二十四章 使用游标 ##

MySQL5添加了对游标的支持

只能用于存储过程

由前几章可知，mysql检索操作返回一组称为结果集的行。都与mysql语句匹配的行（0行或多行），使用简单的SELECT语句，没有办法得到第一行、下一行或前10行，也不存在每次行地处理所有行的简单方法（相对于成批处理他们）

有时，需要在检索出来的行中前进或后退一行或多行。这就是使用游标的原因。游标（cursor）是一个存储在MYSQL服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。

**游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或做出更改**。

### **使用游标** ###

使用游标涉及几个明确的步骤：

1.  在能够使用游标前，必须声明（定义）它，这个过程实际上没有检索数据，它只是定义要使用的SELECT语句
2.  一旦声明后，必须打开游标以供使用。这个过程用钱吗定义的SELECT语句吧数据实际检索出来
3.  对于填有数据的游标，根据需要取出（检索）的各行
4.  在接受游标使用时，必须关闭它 如果不明确关闭游标，MySQL将会在到达END语句时自动关闭它

### **创建游标** ###

游标可用DECLARE 语句创建。 DECLARE命名游标，并定义相应的SELECT语句。根据需要选择带有WHERE和其他子句。如：下面第一名为ordernumbers的游标，使用了检索所有订单的SELECT语句

CREATE PROCEDURE processorders() BEGIN DECLARE ordernumbers CURSOR FOR SELECT order_num FROM orders ;
    
                END;
    
                存储过程处理完成后，游标就消失，因为它局限于存储过程
    
        打开和关闭游标
    
                CREATE PROCEDURE processorders() BEGIN DECLAREordernumbers CURSOR FOR SELECT order_num FROM orders ;
    
                Open ordernumbers ;
    
                Close ordernumbers ;  //CLOSE释放游标使用的所有内部内存和资源，因此，每个游标不需要时都应该关闭
    
                END;

### **使用游标数据** ###

在一个游标被打开后，可以使用FETCH语句分别访问它的每一行。FETCH指定检索什么数据（所需的要列），检索出来的数据存储在什么地方。它还向前移动游标中的内部行指针，使下一条FETCH语句检索下一行，相当于PHP中的each()函数

循环检索数据，从第一行到最后一行

CREATE PROCEDURE processorders() BEGIN -- 声明局部变量 DECLARE done BOOLEAN DEFAULT 0;
    
                DECLARE o INT;
    
    
    
                DECLAREordernumbers CURSOR
    
                FOR
    
                SELECT order_num FROM orders ;
    
                -- 当SQLSTATE为02000时设置done值为1
    
                DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
    
                --打开游标
    
                Open ordernumbers ;
    
                -- 开始循环
    
                REPEAT
    
                -- 把当前行的值赋给声明的局部变量o中
    
                FETCH ordernumbers INTO o;
    
                -- 当done为真时停止循环
    
                UNTIL done END REPEAT;
    
                --关闭游标
    
                Close ordernumbers ;  //CLOSE释放游标使用的所有内部内存和资源，因此，每个游标不需要时都应该关闭
    
                END;

语句中定义了CONTINUE HANDLER ,它是在条件出现时被执行的代码。这里，它指出当SQLSTATE ‘02000’出现时，SET done=1。SQLSTATE ‘02000’是一个未找到条件，当REPEAT没有更多的行供循环时，出现这个条件。

DECLARE 语句次序 用DECLARE语句定义局部变量必须在定义任意游标或句柄之前定义，而句柄必须在游标之后定义。不遵守此规则就会出错

重复和循环 除这里使用REPEAT语句外，MySQL还支持循环语句，它可用来重复执行代码，直到使用LEAVE语句手动退出为止。通常REPEAT语句的语法使它更适合于对游标进行的循环。

为了把这些内容组织起来，这次吧取出的数据进行某种实际的处理

CREATE PROCEDURE processorders() BEGIN -- 声明局部变量 DECLARE done BOOLEAN DEFAULT 0;
    
            DECLARE o INT;
    
            DECLARE t DECIMAL(8,2)
    
    
    
            DECLAREordernumbers CURSOR
    
            FOR
    
            SELECT order_num FROM orders ;
    
            -- 当SQLSTATE为02000时设置done值为1
    
            DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
    
            -- 创建一个ordertotals的表
    
            CREATE TABLE IF NOT EXISTS ordertotals( order_num INT , total DECIMAL(8,2)) --打开游标 Open ordernumbers ;
    
            -- 开始循环
    
            REPEAT
    
            -- 把当前行的值赋给声明的局部变量o中
    
            FETCH ordernumbers INTO o;
    
            -- 用上文讲到的ordertotal存储过程并传入参数，返回营业税计算后的合计传给t变量
    
            CALL ordertotal(o , 1 ,t) -- 把订单号和合计插入到新建的ordertotals表中 INSERT INTO ordertotals(order_num, total) VALUES(o , t);
    
            -- 当done为真时停止循环
    
            UNTIL done END REPEAT;
    
            --关闭游标
    
            Close ordernumbers ;  //CLOSE释放游标使用的所有内部内存和资源，因此，每个游标不需要时都应该关闭
    
            END;

最后SELECT \* FROM ordertotals就能查看结果了

--------------------

## **第二十五章 使用触发器** ##

MySQL5版本后支持触发器

只有表支持触发器，视图不支持触发器

MySQL语句在需要的时被执行，存储过程也是如此，但是如果你想要某条语句（或某些语句）在事件发生时自动执行，那该怎么办呢：例如：

1.  每增加一个顾客到某个数据库表时，都检查其电话号码格式是否正确，区的缩写是否为大写
2.  每当订购一个产品时，都从库存数量中减少订购的数量
3.  无论何时删除一行，都在某个存档中保留一个副本

这写例子的共同之处是他们都需要在某个表发生更改时自动处理。这就是触发器。触发器是MySQL响应一下任意语句而自动执行的一条MySQL语句（或位于BEGIN和END语句之间的一组语句）

1.  DELETE
2.  INSERT
3.  UPDATE

其他的MySQL语句不支持触发器

### **创建触发器** ###

创建触发器需要给出4条信息

1.  唯一的触发器名； //保存每个数据库中的触发器名唯一
2.  触发器关联的表；
3.  触发器应该响应的活动（DELETE、INSERT或UPDATE）
4.  触发器何时执行（处理前还是后,前是BEFORE 后是AFTER）

创建触发器用CREATE TRIGGER

CREATE TRIGGER newproduct AFTER INSERT ON products FOR EACH ROW SELECT'Product added'

创建新触发器newproduct ，它将在INSERT语句成功执行后执行。这个触发器还镇定FOR EACH ROW,因此代码对每个插入的行执行。这个例子作用是文本对每个插入的行显示一次product added

FOR EACH ROW 针对每个行都有作用，避免了INSERT一次插入多条语句

### **触发器定义规则** ###

触发器按每个表每个事件每次地定义，每个表每个事件每次只允许定义一个触发器，因此，每个表最多定义6个触发器（每条INSERT UPDATE 和DELETE的之前和之后）。单个触发器不能与多个事件或多个表关联，所以，如果你需要一个对INSERT 和UPDATE存储执行的触发器，则应该定义两个触发器

触发器失败 如果BEFORE(之前)触发器失败，则MySQL将不执行SQL语句的请求操作，此外，如果BEFORE触发器或语句本身失败，MySQL将不执行AFTER(之后)触发器

### **删除触发器** ###

DROP TRIGGER newproduct；

触发器不能更新或覆盖，所以修改触发器只能先删除再创建

### **使用触发器** ###

我们来看看每种触发器以及它们的差别

INSERT 触发器

INSERT触发器在INSERT语句执行之前或之后执行。需要知道以下几点：

1.  在INSERT触发器代码内，可引用一个名为NEW的虚拟表，访问被插入的行
2.  在BEFORE INSERT触发器中，NEW中的值也可以被更新（允许更改插入的值）
3.  对于AUTO\_INCREMENT列，NEW在INSERT执行之前包含0，在INSERT执行之后包含新的自动生成值

提示：通常BEFORE用于数据验证和净化（目的是保证插入表中的数据确实是需要的数据）。本提示也适用于UPDATE触发器

### **DELETE 触发器** ###

DELETE触发器在语句执行之前还是之后执行，需要知道以下几点：

1.  在DELETE触发器代码内，你可以引用一个名为OLD的虚拟表，访问被删除的行；
2.  OLD中的值全部是只读的，不能更新

**例子演示适用OLD保存将要除的行到一个存档表中**

CREATE TRIGGERdeleteorder BEFORE DELETE ON orders FOR EACH ROW BEGIN INSERT INTO archive_orders(order_num , order_date , cust_id) VALUES(OLD.order_num , OLD.order_date , OLD.cust_id);
    
            END;
    
            //此处的BEGIN END块是非必需的，可以没有

在任何订单删除之前执行这个触发器，它适用一条INSERT语句将OLD中的值（将要删除的值）保存到一个名为archive\_orders的存档表中

BEFORE DELETE触发器的优点是（相对于AFTER DELETE触发器），如果由于某种原因，订单不能被存档，DELETE本身将被放弃执行。

多语言触发器 正如上面所见，触发器deleteorder 使用了BEGIN和END语句标记触发器体。这在此例中并不是必需的，不过也没有害处。使用BEGIN END块的好处是触发器能容纳多条SQL语句。

### **UPDATE触发器** ###

UPDATE触发器在语句执行之前还是之后执行，需要知道以下几点：

1.  在UPDATE触发器代码中，你可以引用一个名为OLD的虚拟表访问（UPDATE语句前）的值，引用一名为NEW的虚拟表访问新更新的值
2.  在BEFORE UPDATE触发器中，NEW中的值可能被更新，（允许更改将要用于UPDATE语句中的值）
3.  OLD中的值全都是只读的，不能更新

例子：保证州名的缩写总是大写（不管UPDATE语句给出的是大写还是小写）

CREATE TRIGGER updatevendor BEFORE UPDATE ON vendores FOR EACH ROW SET NEW.vend_state = Upper(NEW.vend_state)

### **触发器的进一步介绍** ###

1.  与其他DBMS相比，MySQL5中支持的触发器相当初级。以后可能会增强
2.  创建触发器可能需要特殊的安全访问权限，但是触发器的执行时自动的.如果INSERT UPDATE DELETE能执行，触发器就能执行
3.  应该用触发器来保证数据的一致性（大小写、格式等）。在触发器中执行这种类型的处理的优点是它总是进行这个处理，而且是透明地进行，与客户机应用无关
4.  触发器的一种非常有意义的使用创建审计跟踪。使用触发器把更改（如果需要，甚至还有之前和之后的状态）记录到另一表非常容易
5.  遗憾的是，MySQL触发器中不支持CALL语句，这表示不能从触发器中调用存储过程。所需要的存储过程代码需要复制到触发器内

[http_www.cnblogs.com_l5580_p_5993238.html]: http://www.cnblogs.com/l5580/p/5993238.html