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 $$'结束存储过程。存储过程定义结束后,使用'DELIMITER ;'恢复默认结束符。也可以指定其他符号作为结束符

二、调用

使用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_%';

结果:

image.png

六、使用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;

结果:image.png

七、从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表存放具体的视图定义