MySQL-存储过程

存储过程总结

简介
SQL语句需要先编译然后执行，而存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，
用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。

优点
简化复杂的操作、速度快、安全、减少流量
缺点
移植困难，换了数据库，需要重新编写；
不利于分层管理，容易混乱；
适用于个别对性能要求较高的业务；

特性
分隔符
delimiter ;
参数
多个参数用","分割开。
•IN:参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值
•OUT:该值可在存储过程内部被改变，并可返回
•INOUT:调用时指定，并且可被改变和返回
变量
declare 变量名 类型 default 默认值
过程体
BEGIN
......
END


CREATE PROCEDURE 过程名([[IN|OUT|INOUT] 参数名 数据类型[,[IN|OUT|INOUT] 参数名 数据类型…]]) [特性 ...] 过程体
DELIMITER $$;
CREATE PROCEDURE myproc(OUT s int)    
BEGIN
DECLARE n int default 0;      
SELECT COUNT(*) INTO s FROM students;    
END    
$$;

创建
delimiter $$;
create procedure pro_copy()
begin
declare i int default 20;
while i>1 do
insert into poll(ip,time,iid) values('127.0.0.1',now(),i);
insert into item(id,descp) values(i,'testtesttest');
set i=i-1;
end while;
end
$$;

调用
call pro_copy();

删除
drop procedure if exists pro_copy;

查询存储过程
SELECT name FROM mysql.proc WHERE db='数据库名';
select name from mysql.proc where db='sqltest';
SELECT routine_name FROM information_schema.routines WHERE routine_schema='数据库‘；
select routine_name from information_schema.routines where routine_schema='sqltest‘；
SHOW PROCEDURE STATUS WHERE db='数据库名';
show procedure status where db='sqltest';
查看存储过程
select specific_name,body from mysql.proc;
查看存储过程详细信息
SHOW CREATE PROCEDURE 数据库.存储过程名;
show create procedure sqltest.pro_copyG;
修改
ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...]
characteristic:{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }| SQL SECURITY { DEFINER | INVOKER }| COMMENT 'string'
•sp_name参数表示存储过程或函数的名称；
•characteristic参数指定存储函数的特性。
•CONTAINS SQL表示子程序包含SQL语句，但不包含读或写数据的语句；
•NO SQL表示子程序中不包含SQL语句；
•READS SQL DATA表示子程序中包含读数据的语句；
•MODIFIES SQL DATA表示子程序中包含写数据的语句。
•SQL SECURITY { DEFINER | INVOKER }指明谁有权限来执行，DEFINER表示只有定义者自己才能够执行；INVOKER表示调用者可以执行。
•COMMENT 'string'是注释信息。
 alter procedure pro_copy modifies sql data sql security invoker;