mysql的存储过程

存储过程可以优化mysql性能，所谓的存储过程就类似于java中的函数，把一些操作封装的存储过程中；里面可以包括if判读，case等操作

mysql查看存储过程的命令

一、show procedure status\G;

创建存储过程之前，需要对mysql的结束符合进行修改。使用命令delimiter $；这样就把mysql的书写语句的结束符合由“；”变为“$”.

二、创建存储过程的命令

create procedure p()

begin

    select id from tabel1；

end$

这样就创建了一个最简单的存储过程。

三、如何执行存储过程

call p ()$;这样就可以调用存储过程，注意p的括号里面可以传递参数。

现在我们就创建一个带参数的存储过程

create procedure p2( n int)

begin 

  select * from table1 where num > n;

end$

四、使用mysql的存储过程来编写一个1到100相加的和

 create porcedure p3(n smallint)

begin

 declare i int;

 declare s int;

 set i=1;

 set s=0;

 while i<=n do

   set s=s+i;

   set i=i+1;

 end while;//结束while循环

  select s; //输出结果

end$

调用使用call p3(100)$后结果显示5050，完全正确

五、删除存储过程的命令

drop procedure 存储过程的名字；

六、存储过程的权限控制

mysql中用户对存储过程的权限有：

ALTER ROUTINE 编辑或删除存储过程

CREATE ROUTINE 创建存储过程

EXECUTE运行存储过程

存储过程的创建者拥有存储过程的ALTER、CREATE、EXECUTE权限。

definer用于指明存储过程是有那个用户定义的，默认存储过程的定义者是存储过程，跟存储过程的使用权限无关。

INVOKER用于指定哪些用户有调用存储过程的权限，此时会以调用者的权限去执行存储过程

如果在创建存储过程时指定为root@%，将有可能导致root@localhost在使用存储过程时出现权限问题。

查看存储过程的Definer信息：

select db,name,type,definer from mysql.proc where name='存储过程名' and type='PROCEDURE';

创建存储过程时指定definer字段：

CREATE
DEFINER = ‘root@localhost’  -- 默认值
PROCEDURE 存储过程名 ........

DELIMITER $$

USE `exercise_sql`$$

DROP PROCEDURE IF EXISTS `testTrancation`$$
//下面的是为这个存储过程添加权限，并建立存储过程testTrancation
CREATE DEFINER=`root`@`localhost` PROCEDURE `testTrancation`(IN id_for_del INT,IN id_with_constraint INT)
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION,
  SQLWARNING ROLLBACK ;
  START TRANSACTION ;
  DELETE 
  FROM
    customer 
  WHERE cust_id = id_for_del;
  # 对应的引用表里面没有对应的记录，可以删除
  DELETE 
  FROM
    customer 
  WHERE cust_id = id_with_constraint ;
  # cust_id 同样被引用为外键， cust_id =1 对应有记录，不能删除
  COMMIT ;
END$$

DELIMITER ;