mysql之存储过程和函数

定义

  存储过程和函数是事先经过编译并存储在数据库中的一段sql语句的集合，调用存储过程可以减少开发人员的很多工作，减少数据在数据库和应用服务器之间的传递，对提高数据处理的效率有明显的效果。
  存储过程不一定必须要有返回值而函数必须要有返回值，存储过程的参数有三种类型：in，out，inout,而函数的返回类型就只能是in哦！如果有函数从其他类型的数据库迁移到mysql，那么就可能需要将函数改造成存储过程。

相关操作

  对函数和存储过程进行操作的时候，需要首先确认用户是否有权限。比如：创建存储过程或者函数需要create routine权限，修改或者删除需要allter routine权限，执行需要execute权限。

创建、修改存储过程或者函数的语法

CREATE PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body

CREATE FUNCTION sp_name ([func_parameter[,...]])
RETURNS type
[characteristic ...] routine_body

proc_parameter:
[ IN | OUT | INOUT ] param_name type

func_parameter:
param_name type

type:
Any valid MySQL data type

characteristic:
LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'

routine_body:
Valid SQL procedure statement or statements

ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...]

characteristic:
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'

调用存储过程的语法

CALL sp_name([parameter[,...]])

3.编写一个简单的存储过程
  在存储过程和函数之中，都可以包含DDL语句的，也允许在存储过程中执行提交（commit），或者是回滚（rollback）。但是，在存储过程和函数中不允许执行 load data infile语句，它们两者都可以在自己内部调用别的存储过程或函数。
  创建一个简单的存储过程：

DEMILITER $$

create procedure film_in_stock(in p_film_id int, in p_store_id int,out p_film_count int)

reads sql data

BEGIN
    select inventory_id
    from inventory
    where film_id = p_film_id
    and store_id = p_store_id
    and inventory_id(inventory_id);

    select found_rows() into p_film_count
END $$

DEMILITER

  通常在执行或者创建存储过程或者函数之前，都会通过“DELIMITER $$”命令将语句的结束符从“；”修改成其他符号，这样在函数和过程中的“；”就不会被mysql 解释成语句的结束而提示错误。在存储过程或者函数创建完毕，通过“DELIMITER”将结束符改回“；”。

删除存储过程或者函数

  存储过程和函数一次都只能删除一个，删除都需要都alter routine权限，语法如下：

DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

查看存储过程或函数

1.查看存储过程或者函数的状态

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'patter']

  比如我要查询存储过程add_menu,执行结果：

2.查看存储过程或者函数的定义

show create procedure {procedure | function} sp_name

变量

1.定义
  通过DECLARE可以定义一个局部变量，改变量的作用范围只能在BEGIN..END块中。变量的定义必须写在复合语句的开头，并且在任何其它语句的前面，可以一次声明多个相同类型的变量，可以使用DEFAULT赋予默认值。语法如下：

DECLARE var_name[,...] type [DEFAULT value]

2.赋值
  变量赋值分为两种，一种是直接赋值，一种是通过表达式赋值：

SET var_name = expr [, var_name = expr] ...

比如给变量last_month_start赋值：

SET last_month_start = DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH)

  另一种是将查询结果赋给变量，这要求查询返回的结果必须只有一行：

select clo_name[,...] into var_name[,...] table expr