MySQL存储过程用法详解教程

一、创建

创建存储过程，使用CREATE PROCEDURE语句，语法格式如下：

CREATE PROCEDURE proc_name ([proc_parameter]) [characteristics...] body

1、CREATE PROCEDURE：创建存储过程关键字

2、proc_name：存储过程名称

3、proc_parameter：参数列表，可选，格式：[IN | OUT | INOUT] param_name type

IN：输入参数

OUT：输出参数

INOUT：既可以输入也可以输出

param_name：参数名称

type：参数类型，MySQL中的任意类型，如varchar等

4、characteristics：指定存储过程特性，取值如下：

LANGUAGE SQL：说明body部分由SQL语句组成，LANGUAGE可选值只有SQL

[NOT] DETERMINISTIC：指明存储过程执行结果是否确定。默认值：NOT DETERMINISTIC

DETERMINISTIC：结果确定，每次执行存储过程时，相同的输入会得到相同的输出

NOT DETERMINISTIC：结果不确定，相同输入可能得到不同输出。

{CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}：指定子程序使用存储过程的限制。默认值：CONTAINS SQL

CONTAINS SQL：说明子程序包含SQL语句，但是不包含写数据语句

NO SQL：说明子程序不包含SQL语句

READS SQL DATA：说明子程序包含读数据读数据语句

MODIFIES SQL DATA：说明子程序包含写数据语句

SQL SECURITY {DEFINER | INVOKER}：指明谁有权限执行，默认值：DEFINER

DEFINER：只有定义者才能执行

INVOKER：拥有权限的调用者才可以执行

COMMNET：注释信息

5、body：SQL代码内容，可以使用BEGIN...END表示SQL代码的开始和技术

DELIMITER $$
CREATE PROCEDURE proc_countUser()
BEGIN
    SELECT COUNT(*) FROM t_user;
END $$

注：DELIMITER ：将MySQL的结束符置为 ，因为MySQL默认语句结束符是';'，避免与存储过程中SQL语句冲突，使用DELIMITER改变存储过程结束符，以'END

二、调用

使用CALL语句进行调用，语法如下：

call proc_name ([parameter[,...

1、调用无参 存储过程见上

 CALL proc_countUser(); 

2、调用有参

DELIMITER $$
CREATE PROCEDURE proc_countByName(IN uname VARCHAR(100), OUT total INT)
BEGIN
    SELECT COUNT(*) INTO total FROM njit_user WHERE NAME LIKE CONCAT('%', uname, '%');
END $$ 

调用，并查看查询结果

CALL proc_countByName('小明', @total);
SELECT @total;

三、删除存储过程

使用DROP语句，语法如下：

 DROP {PROCEDURE | FUNCTION} [IF EXISTS] proc_name

删除：

DROP PROCEDURE  proc_countByName;
DROP PROCEDURE IF EXISTS proc_countByName;

四、修改

使用ALTER语句修改，语法如下：

 ALTER {PROCEDURE | FUNCTION} proc_or_func [characterustic...]

{CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}：指定子程序使用存储过程的限制。

CONTAINS SQL：            说明子程序包含SQL语句，但是不包含写数据语句
NO SQL：                  说明子程序不包含SQL语句
READS SQL DATA：          说明子程序包含读数据读数据语句
MODIFIES SQL DATA：       说明子程序包含写数据语句
SQL SECURITY {DEFINER | INVOKER}：指明谁有权限执行，默认值：DEFINER
DEFINER：                 只有定义者才能执行
INVOKER：                 拥有权限的调用者才可以执行
COMMNET：                 注释信息

例如：

ALTER PROCEDURE proc_countByName
MODIFIES SQL DATA
SQL SECURITY INVOKER;

执行后查看存储过程信息：

SELECT * FROM information_schema.ROUTINES 
WHERE ROUTINE_NAME = 'proc_countByName';

五、使用SHOW STATUS查看状态

SHOW STATUS查看存储过程和函数的状态，语法如下：

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

这个语句是MySQL的扩展，它返回子程序的特征，如数据库、名字、类型、创建者及创建和修改日期。如果没有指定样式，根据使用的语句，所有存储程序或存储函数的信息都被列出。PROCEDURE和FUNCTION分别表示查看存储过程和函数；LIKE语句表示匹配存储过程或函数名称。

示例：

SHOW PROCEDURE STATUS LIKE 'proc_%';

结果：

六、使用SHOW CREATE查看定义

使用SHOW CREATE语句查看存储过程和函数的状态，语法如下：

SHOW CREATE {PROCEDURE | FUNCTION} proc_or_func

这个语句是一个MySQL的扩展，类似于SHOW CREATE TABLE，它返回一个可用来重新创建已命名子程序的确切字符串。PROCEDURE和FUNCTION分别表示查看存储过程和函数，proc_or_func表示匹配存储过程或函数名。

示例：

 SHOW CREATE PROCEDURE proc_countByName;

结果：

七、从information_schema.Routines查看信息

MySQL存储过程和函数的信息存储在information_schema数据库下的Routines表中。通过查询该表的记录查询信息，SQL如下：

SELECT * FROM information_schema.ROUTINES 
WHERE ROUTINE_NAME = 'proc_or_func'

八、库中的存储位置

8.1 存储过程或函数

mysql数据库=====》proc表存放对存储过程或函数的描述；

information_schema数据库=====》ROUTINES表存放具体的存储过程

8.2 视图

information_schema数据库=====》VIEWES表存放具体的视图定义

排版：Aiti

来源：https://blog.csdn.net/mytt_10566/article/details/80327209