查看

MySQL存储了存储过程和函数的状态信息，用户可以使用SHOW STATUS语句或SHOW CREATE语句来查看，也可直接从系统的information_schema数据库中查询。

使用SHOW CREATE语句查看存储过程和函数的创建信息

基本语法

  1. SHOW CREATE {PROCEDURE | FUNCTION} 存储过程名或函数名

例子

  1. SHOW CREATE PROCEDURE show_mgr_name;
  3. SHOW CREATE FUNCTION count_by_id;

使用SHOW STATUS语句查看存储过程和函数的状态信息

基本语法

  1. SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']

例子

  1. # 查看数据库中所有的存储过程和函数的状态信息
  2. SHOW PROCEDURE STATUS;
  4. # 查看指定数据库中的存储过程和函数的状态信息
  5. SHOW PROCEDURE STATUS LIKE 'show_max_salary';
  7. SHOW FUNCTION STATUS LIKE 'email_by_id';

这个语句返回子程序的特征，如数据库、名字、类型、创建者及创建和修改日期。

[LIKE ‘pattern’]：匹配存储过程或函数的名称，可以省略。当省略不写时，会列出MySQL数据库中存在的所有存储过程或函数的信息。

从information_schema.Routines表中查看存储过程和函数的信息

MySQL中存储过程和函数的信息存储在information_schema数据库下的Routines表中。可以通过查询该表的记录来查询存储过程和函数的信息。其基本语法形式如下：

  1. SELECT * FROM information_schema.Routines
  2. WHERE ROUTINE_NAME='存储过程或函数的名' [AND ROUTINE_TYPE = {'PROCEDURE|FUNCTION'}];

说明：如果在MySQL数据库中存在存储过程和函数名称相同的情况，最好指定ROUTINE_TYPE查询条件来指明查询的是存储过程还是函数。

例子

  1. SELECT * FROM information_schema.Routines
  2. WHERE ROUTINE_NAME='email_by_id' AND ROUTINE_TYPE = 'FUNCTION';
  4. SELECT * FROM information_schema.Routines
  5. WHERE ROUTINE_NAME='show_min_salary' AND ROUTINE_TYPE = 'PROCEDURE';

修改

修改存储过程或函数，不影响存储过程或函数功能，只是修改相关特性。使用ALTER语句实现。

  1. ALTER {PROCEDURE | FUNCTION} 存储过程或函数的名 [characteristic ...]

其中，characteristic指定存储过程或函数的特性，其取值信息与创建存储过程、函数时的取值信息略有不同。

  1. { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  2. | SQL SECURITY { DEFINER | INVOKER }
  3. | COMMENT 'string'
  • CONTAINS SQL，表示子程序包含SQL语句，但不包含读或写数据的语句。
  • NO SQL，表示子程序中不包含SQL语句。
  • READS SQL DATA，表示子程序中包含读数据的语句。
  • MODIFIES SQL DATA，表示子程序中包含写数据的语句。
  • SQL SECURITY { DEFINER | INVOKER }，指明谁有权限来执行。
    • DEFINER，表示只有定义者自己才能够执行。
    • INVOKER，表示调用者可以执行。
  • COMMENT 'string'，表示注释信息。

修改存储过程使用ALTER PROCEDURE语句，修改存储函数使用ALTER FUNCTION语句。但是，这两个语句的结构是一样的，语句中的所有参数也是一样的。

例子

  1. ALTER PROCEDURE show_max_salary
  2. SQL SECURITY INVOKER
  3. COMMENT '查询最高工资';

删除

删除存储过程和函数，可以使用DROP语句，其语法结构如下：

  1. DROP {PROCEDURE | FUNCTION} [IF EXISTS] 存储过程或函数的名

IF EXISTS：如果程序或函数不存储，它可以防止发生错误，产生一个用SHOW WARNINGS查看的警告。

例子

  1. DROP FUNCTION IF EXISTS count_by_id;
  3. DROP PROCEDURE IF EXISTS show_min_salary;

存储过程的优缺点

优点

  1. 存储过程可以一次编译多次使用。存储过程只在创建时进行编译，之后的使用都不需要重新编译，这就提升了 SQL 的执行效率。

  2. 可以减少开发工作量。将代码封装成模块，实际上是编程的核心思想之一，这样可以把复杂的问题拆解成不同的模块，然后模块之间可以重复使用，在减少开发工作量的同时，还能保证代码的结构清晰。

  3. 存储过程的安全性强。我们在设定存储过程的时候可以设置对用户的使用权限，这样就和视图一样具有较强的安全性。

  4. 可以减少网络传输量。因为代码封装到存储过程中，每次使用只需要调用存储过程即可，这样就减少了网络传输量。

  5. 良好的封装性。在进行相对复杂的数据库操作时，原本需要使用一条一条的 SQL 语句，可能要连接多次数据库才能完成的操作，现在变成了一次存储过程，只需要连接一次即可。

    缺点

  6. 可移植性差。存储过程不能跨数据库移植，比如在 MySQL、Oracle 和 SQL Server 里编写的存储过程，在换成其他数据库时都需要重新编写。

  7. 调试困难。只有少数 DBMS 支持存储过程的调试。对于复杂的存储过程来说，开发和维护都不容易。虽然也有一些第三方工具可以对存储过程进行调试，但要收费。

  8. 存储过程的版本管理很困难。比如数据表索引发生变化了，可能会导致存储过程失效。我们在开发软件的时候往往需要进行版本管理，但是存储过程本身没有版本控制，版本迭代更新的时候很麻烦。

  9. 它不适合高并发的场景。高并发的场景需要减少数据库的压力，有时数据库会采用分库分表的方式，而且对可扩展性要求很高，在这种情况下，存储过程会变得难以维护，增加数据库的压力，显然就不适用了。