MySQL 5.0 版本开始支持存储过程。

存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。

存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。

存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

 

优点(为什么要用存储过程？)：

　　①将重复性很高的一些操作，封装到一个存储过程中，简化了对这些SQL的调用

　　②批量处理：SQL+循环，减少流量，也就是“跑批”

　　③统一接口，确保数据的安全

相对于oracle数据库来说，MySQL的存储过程相对功能较弱，使用较少。

优点

  • 存储过程可封装，并隐藏复杂的商业逻辑。
  • 存储过程可以回传值，并可以接受参数。
  • 存储过程无法使用 SELECT 指令来运行，因为它是子程序，与查看表，数据表或用户定义函数不同。
  • 存储过程可以用在数据检验，强制实行商业逻辑等。

缺点

  • 存储过程，往往定制化于特定的数据库上，因为支持的编程语言不同。当切换到其他厂商的数据库系统时，需要重写原有的存储过程。
  • 存储过程的性能调校与撰写，受限于各种数据库系统。

一、存储过程的创建和调用

  • 存储过程就是具有名字的一段代码，用来完成一个特定的功能。
  • 创建的存储过程保存在数据库的数据字典中。
CREATE
    [DEFINER = { user | CURRENT_USER }]
　PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body
 
proc_parameter:
    [ IN | OUT | INOUT ] param_name 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
 
[begin_label:] BEGIN
　　[statement_list]
　　　　……
END [end_label]

MYSQL 存储过程中的关键语法

声明语句结束符，可以自定义:


DELIMITER $$ 或 DELIMITER //


声明存储过程:


CREATE PROCEDURE demo_in_parameter(IN p_in int)


存储过程开始和结束符号:


BEGIN .... END


变量赋值:


SET @p_in=1


变量定义:


DECLARE l_int int unsigned default 4000000;


创建mysql存储过程、存储函数:


create procedure 存储过程名(参数)


存储过程体:


create function 存储函数名(参数)


实例

创建数据库，备份数据表用于示例操作：

mysql> create database db1;
mysql> use db1;    
mysql> create table PLAYERS as select * from TENNIS.PLAYERS;
mysql> create table MATCHES  as select * from TENNIS.MATCHES;

下面是存储过程的例子，删除给定球员参加的所有比赛：

mysql> delimiter $$　　#将语句的结束符号从分号;临时改为两个$$(可以是自定义)
mysql> CREATE PROCEDURE delete_matches(IN p_playerno INTEGER)
    -> BEGIN
    -> 　　DELETE FROM MATCHES
    ->    WHERE playerno = p_playerno;
    -> END$$
Query OK, 0 rows affected (0.01 sec)
 
mysql> delimiter;　　#将语句的结束符号恢复为分号

解析：默认情况下，存储过程和默认数据库相关联，如果想指定存储过程创建在某个特定的数据库下，那么在过程名前面加数据库名做前缀。 在定义过程时，使用 DELIMITER $$ 命令将语句的结束符号从分号 ; 临时改为两个 $$，使得过程体中使用的分号被直接传递到服务器，而不会被客户端（如mysql）解释。 

 调用存储过程：

call sp_name[(传参)];