存储过程和函数
函数与存储过程最大的区别就是函数调用有返回值，调用存储过程用call语句，而调用函数就直接引用函数名+参数即可
创建存储过程和函数详解
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
[DEFINER = { user | CURRENT_USER }] #定义用户范围
Definer和sql security子句指定安全环境
简单理解就是定义这个存储过程可以在哪个范围用这个存储过程
Definer是MySQL的特殊的访问控制手段，当数据库当前没有这个用户权限时，执行存储过程可能会报错
sql secuirty的值决定了调用存储过程的方式，取值 ：definer（默认）或者invoker
definer:在执行存储过程前验证definer对应的用户如：user@主机 是否存在，以及
是否具有执行存储过程的权限，若没有则报错
invoker:在执行存储过程时判断inovker即调用该存储过程的用户是否有相应权限，若
没有则报错
proc_parameter:
[ IN | OUT | INOUT ] param_name type
存储过程参数
IN,OUT,INOUT三个参数前的关键词只适用于存储过程，对函数而言所有的参
数默认都是输入参数
IN输入参数用于把数值传入到存储过程中；
OUT输出参数将数值传递到调用者，初始值是NULL；
INOUT输入输出参数把数据传入到存储过程，在存储过程中修改之后再传递到调用者
定义参数要写上参数类型
characteristic: 典型的
COMMENT 'string'
| LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
Comment 用来写入对存储过程和函数的注释
Language子句用来表示此存储过程和函数的创建语言
存储过程和函数被标注为deterministic表明当输入相同的参数是会返回相同的结果，反之如果是not deterministic则表示相同参数不会是相同结果，默认是not deterministic
Contains sql表明此存储过程或函数不包含读或者写数据的语句，这是默认属性
以下相关属性短语只有咨询含义，并不是强制性的约束
NO SQL表示此存储过程或函数不包含SQL语句
Reads sql data表示此存储过程包含诸select的查询数据的语句，但不包含插入或删除数据的语句
Modifies sql data表示此存储过程包含插入或删除数据的语句
routine_body:
Valid SQL routine statement
可以包含一个简单的SQL语句，也可以包含多个SQL语句， 通过begin…end将这多个SQL语句包含在一起
func_parameter:
param_name type
参数名和类型
函数没有IN ,OUT,INOUT
type:
Any valid MySQL data type
任何mysql数据类型
|
例创建一个简易的函数和存储过程
Delimiter命令是改变语句的结束符， MySQL默认的结束符为;号，由于procedure和function中的;号并不代表创建的结束，所以要替换成另外的结束符以便表示创建的结束
call proc_name() 调用存储过程
存储过程优缺点
优点：
存储过程是一组预先创建并用指定的名称存储在数据库服务器上的 SQL 语句，将使用比较频繁或者比较复杂的操作，预先用 SQL 语句写好并存储起来，以后当需要数据库提供相同的服务时，只需再次执行该存储过程。
1.具有更好的性能
存储过程是预编译的，只在创建时进行编译，以后每次执行存储过程都不需再重新编译，而一般 SQL 语句每执行一次就编译一次，因此使用存储过程可以提高数据库执行速度。
2.功能实现更加灵活
存储过程中可以应用条件判断和游标等语句，有很强的灵活性，可以直接调用数据库的一些内置函数，完成复杂的判断和较复杂的运算。
3.减少网络传输
复杂的业务逻辑需要多条 SQL 语句，当客户机和服务器之间的操作很多时，将产生大量的网络传输。如果将这些操作放在一个存储过程中，那么客户机和服务器之间的网络传输就会减少，降低了网络负载。
4.具有更好的安全性
（1）数据库管理人员可以更好的进行权限控制，存储过程可以屏蔽对底层数据库对象的直接访问，使用 EXECUTE 权限调用存储过程，无需拥有访问底层数据库对象的显式权限。
（2）在通过网络调用过程时，只有对执行过程的调用是可见的。无法看到表和数据库对象名称，不能嵌入SQL 语句，有助于避免 SQL 注入攻击。
缺点：
1 .架构不清晰，不够面向对象
存储过程不太适合面向对象的设计，无法采用面向对象的方式将业务逻辑进行封装，业务逻辑在存储层实现，增加了业务和存储的耦合，代码的可读性也会降低，
2 .开发和维护要求比较高
存储过程的编写直接依赖于开发人员，如果业务逻辑改动较多，需要频繁直接操作数据库，大量业务降维到数据库，很多异常不能在代码中捕获，出现问题较难排查，需要数据库管理人员的帮助。
3 .可移植性差
过多的使用存储过程会降低系统的移植性。在对存储进行相关扩展时，可能会增加一些额外的工作。
存储过程与SQL语句如何抉择？
架构设计没有绝对，只有在当前的场景下最合适的。
因此：
普通的项目开发中，不建议大量使用存储过程，对比SQL语句，存储过程适用于业务逻辑复杂，比较耗时，同时请求量较少的操作，例如后台大批量查询、定期更新等。
（1）当一个事务涉及到多个SQL语句时或者涉及到对多个表的操作时可以考虑应用存储过程
（2）在一个事务的完成需要很复杂的商业逻辑时可以考虑应用存储过程
（3）比较复杂的统计和汇总可以考虑应用后台存储过程
查看存储过程和函数
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
查询所有存储过程和函数
select routine_schema,routine_name,routine_type,routine_body from information_schema.routines where routine_schema='库名'
select name,type from mysql.proc where db='your_db_name'
select name,type from mysql.proc where db = 'your_db_name' and `type` = 'PROCEDURE' #查看所有存储过程
select name,type from mysql.proc where db='your_db_name' and type='function'; #查看所有函数
show create procedure proc_name;
show create function func_name;
查看存储过程和函数详细信息
[例]
mysql> show create procedure simpleproc\G;
*************************** 1. row ***************************
Procedure: simpleproc
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `simpleproc`(in param1
COMMENT '查询大于parmam1的 学生的个数'
begin
select count(*) into param2 from students where sid> param1;
end
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_unicode_ci
1 row in set (0.00 sec)
|
删除存储过程和函数
| 1 2 3 4 5 6 7 8 |
mysql> HELP DROP PROCEDURE
Name: 'DROP PROCEDURE'
Description:
Syntax:
DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name
If exists关键词用来避免在删除一个本身不存在的存储过程或函数
时， MySQL返回错误
|
标签：语句,存储,函数,MySQL,SQL,过程,name From： https://www.cnblogs.com/ataoxz/p/18686693