MySQL学习-存储过程之修改删除以及优缺点

圆之蓝调
444 阅读4分钟

携手创作，共同成长！这是我参与「掘金日新计划 · 8 月更文挑战」的第15天，点击查看活动详情

前言

上篇我们学习了MySQL中的存储函数。有兴趣的小伙伴可以阅读(# MySQL学习-存储函数)。
下面继续学习MySQL中的存储函数与存储过程的修改删除与查看，以及优缺点。

查看

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

  1. 使用SHOW CREATE语句查看创建信息。
SHOW CREATE {PROCEDURE | FUNCTION} 存储过程名或函数名
  1. 使用SHOW STATUS语句查看状态信息。
SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']

这个语句返回子程序的特征，如数据库，名称，类型，创建者以及创建修改日期。
[LIKE 'pattern']:匹配存储过程或函数的名字，可以省略。当省略时，会列出MySQL数据库中存在的所有存储过程或函数的信息。

  1. 从information_schema.Routines表中查看信息。
    MySQL中存储过程和函数的信息存储在information_schema数据库下的Routines中。可以通过查询该表的记录来查询存储过程和函数的信息。
SELECT *
FROM information_schema.Routines 
WHERE ROUTINE_NAME = 'count_by_deptid' 
AND ROUTINE_TYPE = 'FUNCTION';

修改存储过程函数

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

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

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

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

举例一

修改存储过程emp_by_name，将读写权限改为MODIFIES SQL DATA，并指明调用者可以执行。

ALTER emp_by_name
MODIFIES SQL DATA
SQL SECURITY INVOKER;

删除存储过程函数

删除存储过程或函数，使用DROP语句。

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

举例二

删除存储过程emp_by_name。

DROP PROCEDURE IF EXISTS emp_by_name;

存储过程优缺点

优点

  1. 存储过程可以一次编译多次使用。存储过程只在创建时进行编译，之后的使用不需要重新编译，提升了SQL的执行效率。
  2. 减少开发工作量。
  3. 存储过程安全性强。可以设定存储过程的使用权限，这样就和视图一样具有较强的安全性。
  4. 减少网络传输量。因为代码封装在存储过程中，每次使用只需要调用存储过程即可，减少了网络传输量。
  5. 良好的封装性。在进行相对复杂的数据库操作时，原本需要使用一条一条的SQL语句，可能要连接多次数据库才能完成的操作，现在变成了一次存储过程，只需要连接一次即可。

缺点

虽然存储过程有很多优点，但我们发现阿里不推荐使用存储过程，下面列举一下缺点。

  1. 可移植性差。存储过程不能跨数据库移植，并入在MySQL或Oracle里编写的存储过程，换成其他数据库时需要重新编写。
  2. 调试困难。只有少数的DBMS支持调试。对于复杂的存储过程来说，开发和维护都不容易。
  3. 存储过程的版本管理困难。比如数据表索引变了，可能导致存储过程失效。开发中需要进行版本管理，但是存储过程本身没有版本控制，版本迭代更新很麻烦。
  4. 不适合高并发场景。高并发需要减少数据库的压力，有时候数据库会采用分库分表的方式，对可扩展性要求高。在这种情况下，存储过程会变得难以维护，增加数据库的压力，显然就不适用了。

今天先学习到这里，明天继续。

avatar
文章
阅读
粉丝