存储过程和存储函数（Procedure and Function）

本是古典何须时尚 2021-09-25 15:06 368阅读 0赞

## 存储过程和存储函数（Procedure and Function） ##

存储过程和存储函数类似于程序设计中的方法，是一组为了完成特定功能的SQL语句集。与一般的SQL需要先编译然后立即执行不同，存储过程和存储函数经编译后存储在数据库中，当用户通过指定的程序名称及对应参数来调用才会执行。但在如下方面，二者存在区别：

1.  使用限制不同。存储函数存在较多限制，如不能用临时表，只能用表变量，而存储过程限制相对较少；
2.  返回值不同。存储函数必须有返回值，且仅有一个，而存储过程可以没返回值，但能返回结果集；
3.  调用方式不同。存储函数嵌入到SQL中使用，而存储过程则通过call语句调用；
4.  参数类型不同。存储函数的参数类型类似于IN参数，而存储过程的参数类型可分为IN、OUT、INOUT三类

### 存储过程基本操作 ###

**注意：本文的SQL 在 MySQL 8.0版本中测试通过**

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

CREATE
        [DEFINER = user]
        PROCEDURE proc_name ([proc_parameter[,...]])
        [characteristic ...] 
    BEGIN
    	routine_body
    END
    proc_parameter:
        [ IN | OUT | INOUT ] param_name type
    
    type:
        Any valid MySQL data type
    
    characteristic: {
        COMMENT 'string'
      | LANGUAGE SQL
      | [NOT] DETERMINISTIC
      | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
      | SQL SECURITY { DEFINER | INVOKER }
    }
    
    routine_body:
        Valid SQL routine statement

**DEFINER**：限制权限，表示可使用该存储过程的用户

**proc\_name**：存储过程的名称

**proc\_parameter**：存储过程的参数列表，参数类型分为**IN、OUT、INOUT**三类，其中：

1.  **IN**：表示该参数的值必须在调用存储过程时指定，存储过程中可以使用这些输入的数据，但是在存储过程中修改该参数的值是不能被返回；
2.  **OUT**：该值在调用的时候需要指定一个变量，用来存储从存储过程中返回的数据。在存储过程中可以任意修改该参数的值且该值能够被存储过程外部获得；
3.  **INOUT**：**IN**和**OUT**两个参数的功能；

**characteristic**：存储特性，其取值说明如下：

1.  **LANGUAGE SQL**：说明routine\_body部分是由SQL语言的语句组成，这也是数据库系统默认的语言。
2.  **\[NOT\] DETERMINISTIC**：指明存储过程的执行结果是否是确定的。DETERMINISTIC表示结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出。NOT DETERMINISTIC表示结果是非确定的，相同的输入可能得到不同的输出。默认情况下，结果是非确定的。
3.  **\{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA \}**：指明子程序使用SQL语句的限制。CONTAINS SQL表示子程序包含SQL语句，但不包含读或写数据的语句；NO SQL表示子程序中不包含SQL语句；READS SQL DATA表示子程序中包含读数据的语句；MODIFIES SQL DATA表示子程序中包含写数据的语句。默认情况下，系统会指定为CONTAINS SQL。
4.  **SQL SECURITY \{ DEFINER | INVOKER \}**：指明谁有权限来执行。DEFINER表示只有定义者自己才能够执行；INVOKER表示调用者可以执行。默认情况下，系统指定的权限是DEFINER。
5.  **COMMENT ‘string’**：注释信息

**routine\_body**：存储过程实现主体

#### 查看存储过程 ####

show procedure status like 'proc_name';	--查看状态
    show create procedure proc_name;		--查看定义

#### 修改存储过程 ####

ALTER PROCEDURE proc_name [characteristic ...]
    
    characteristic: {
        COMMENT 'string'
      | LANGUAGE SQL
      | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
      | SQL SECURITY { DEFINER | INVOKER }
    }

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

DROP PROCEDURE [IF EXISTS] sp_name

### 存储过程应用实例 ###

假如有初始商品和订单表信息如下：

mysql> select * from goods;
    +-------+----------+-------+--------+
    | id    | name     | price | number |
    +-------+----------+-------+--------+
    | 10001 | cream    |    80 |    950 |
    | 10002 | cleanser |    40 |   5000 |
    | 10003 | sweater  |    99 |  13000 |
    | 10004 | honey    |    88 |  14000 |
    | 10005 | keyboard |    66 |  12000 |
    +-------+----------+-------+--------+
    5 rows in set (0.00 sec)
    
    mysql> select * from orders;
    +----------+---------+--------------+
    | order_id | good_id | order_number |
    +----------+---------+--------------+
    |     1001 |   10001 |           10 |
    |     1002 |   10002 |          200 |
    |     1003 |   10003 |          300 |
    |     1004 |   10004 |          250 |
    |     1005 |   10005 |          150 |
    |     1006 |   10001 |           50 |
    +----------+---------+--------------+
    6 rows in set (0.01 sec)

建立存储过程get\_orders\_statistics，可以根据商品的id，获取商品的单价和销售数量。

DELIMITER $;
    create procedure get_orders_statistics(IN good_id int, OUT price float, OUT order_number int)
    	comment '根据商品的id，获取商品的单价和销售数量'
    	begin
            declare tmp_good_id int;
            -- store table result into cursor
    		declare cur cursor for 
    			select goods.id as id, goods.price as price, sum(orders.order_number) as number 
    				from goods join orders 
    				where goods.id = orders.good_id and goods.id = good_id
    				group by orders.good_id;
                    
    		open cur;
    		fetch cur into tmp_good_id, price,  order_number;
            close cur;
        end;

验证结果如下：

mysql> call get_orders_statistics(10001, @price, @order_number);
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select @price, @order_number;
    +--------+---------------+
    | @price | @order_number |
    +--------+---------------+
    |     80 |            60 |
    +--------+---------------+
    1 row in set (0.00 sec)
    
    mysql> call get_orders_statistics(10009, @price, @order_number);
    ERROR 1329 (02000): No data - zero rows fetched, selected, or processed

### 存储函数基本操作 ###

#### 创建存储函数 ####

CREATE
        [DEFINER = user]
        FUNCTION func_name ([func_parameter[,...]])
        RETURNS type
        [characteristic ...] routine_body
    
    func_parameter:
        param_name type
    
    type:
        Any valid MySQL data type
    
    characteristic: {
        COMMENT 'string'
      | LANGUAGE SQL
      | [NOT] DETERMINISTIC
      | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
      | SQL SECURITY { DEFINER | INVOKER }
    }
    
    routine_body:
        Valid SQL routine statement

**DEFINER**：限制权限，表示可使用该视图的用户

**func\_name**：存储过程的名称

**func\_parameter**：存储函数的参数列表，

**characteristic**：参见存储过程部分的说明

**routine\_body**：存储函数实现主体

#### 查看存储函数 ####

show function status like 'func_name'; --查看状态
    show create function func_name;		   --查看定义

#### 修改存储函数 ####

ALTER FUNCTION func_name [characteristic ...]
    
    characteristic: {
        COMMENT 'string'
      | LANGUAGE SQL
      | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
      | SQL SECURITY { DEFINER | INVOKER }
    }

#### 删除存储函数 ####

DROP FUNCTION [IF EXISTS] func_name;

### 存储函数应用实例 ###

建立存储过程get\_good\_price，可以根据商品的id，获取商品的单价

DELIMITER $;
    create function get_good_price(good_id int) 
    returns float DETERMINISTIC
    begin
    	return (select price from goods where id = good_id);
    end

结果验证如下：

【作者：墨叶扶风 http://blog.csdn.net/yefufeng】