MySQL存储过程、触发器、自定义函数、事务

1、存储过程

MySQL中存储过程的参数中有IN、OUT、INOUT类型，但是函数的参数只能是IN类型的。

“in” 参数：跟 C 语言的函数参数的值传递类似， MySQL 存储过程内部可能会修改此参数，但对 in 类型参数的修改，对调用者来说是不可见的。 

DROP PROCEDURE IF EXISTS prol_pl;
DELIMITER $$#定义一个分解符
CREATE PROCEDURE prol_pl(
   IN i1 INT
)
BEGIN
      DECLARE d1 INT;#声明一个变量
      DECLARE d2 INT DEFAULT 1;
      SET d1 = i1 + d2;
      SELECT * FROM employee WHERE id > d1;
END $$
DELIMITER ; 
set @id = 10;
CALL prol_pl(@id); #调用存储过程 #结果为图1
SELECT * FROM employee WHERE id > @id;#结果为图2

可以看出虽然设置了变量id的值为1，但是在存储过程内部修改了id的值为2，id的值并未返回给调用者。（in也可以粗暴的理解为进入，必须传值）

 “out” 参数：从存储过程内部传值给调用者。在存储过程内部，该参数初始值为 null，无论调用者是否给存储过程参数设置值。 

 1 delimiter //
 2 
 3 create procedure pr_test(out id int)
 4 begin
 5 if(id is not null)then set id = id + 1;
 6 else set id = 0;
 7 end if;
 8 select id as in_id;
 9 end;
10 //
11 
12 delimiter ;
13 
14 set @id = 10;
15 
16 /*-----运行的结果in_id = 0-----*/
17 call pr_test(@id);
18 
19 /*-----运行的结果in_id = 0-----*/
20 select @id as out_id;

可以看出虽然设置了变量id的值为10，但是在存储过程内部id的值为null,最后id的值在存储过程内修改后返回调用者。

inout 参数跟 out 类似，都可以从存储过程内部传值给调用者。不同的是：调用者还可以通过 inout 参数传递值给存储过程。 

 1 delimiter //
 2 
 3 create procedure pr_test(inout id int)
 4 begin
 5 if(id is not null)then set id = id + 1;
 6 else set id = 0;
 7 end if;
 8 select id as in_id;
 9 end;
10 //
11 
12 delimiter ;
13 
14 set @id = 10;
15 
16 /*-----运行的结果in_id = 11-----*/
17 call pr_test(@id);
18 
19 /*-----运行的结果in_id = 11-----*/
20 select @id as out_id;

可以看出设置了变量id的值为10，在存储内部将id的值修改为11，最后id的值返回给调用者。

综合小练习：

DROP PROCEDURE IF EXISTS prol_p2;
DELIMITER $$
CREATE PROCEDURE prol_p2(
     IN i1 INT,
    INOUT ii INT,
     OUT i2 int
)
BEGIN
     DECLARE d2 int DEFAULT 1;
    set ii = ii + 1;
     IF i1 = 1 THEN
         set i2 = 100 + d2;
     ELSEIF i1 = 2 THEN
         set i2 = 200 + d2;
     ELSE
         SET i2 = 1000 + d2;
     END IF;
END $$
DELIMITER ;

set @c=4;
CALL prol_p2(2,@c,@u);
SELECT @c,@u

结果为5，201

python操作存储过程

import pymysql
conn = pymysql.connect(host='127.0.0.1',port=3306,user='root',passwd='',db='article_spider')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
#执行存储过程的查询结果
row = cursor.callproc('prol_p2',(1,2,3))
#获取存储过程查询结果
selc = cursor.fetchall()
print(selc)
#获取存储过程返回
effect_row = cursor.execute("select @_prol_p2_0,@_prol_p2_1,@_prol_p2_2")
#获取存储过程返回值
result = cursor.fetchone()
print(result)
conn.commit()

2、触发器

简单版

delimiter $$
DROP TRIGGER IF EXISTS tri_before_insert_color $$
CREATE TRIGGER tri_before_insert_color BEFORE INSERT ON color FOR EACH ROW
BEGIN
    INSERT INTO employee(`name`,`depid`) VALUES('大佐','104');
END $$
delimiter ; 
INSERT INTO color(`name`) VALUES('red');

new

delimiter $$
DROP TRIGGER IF EXISTS tri_before_insert_color $$
CREATE TRIGGER tri_before_insert_color BEFORE INSERT ON color FOR EACH ROW
BEGIN
    INSERT INTO employee(`name`,`depid`) VALUES(NEW.name,'105');
END $$
delimiter ; 
INSERT INTO color(name) VALUES('blue'),('orange');

old

delimiter $$
DROP TRIGGER IF EXISTS tri_before_insert_color $$
CREATE TRIGGER tri_before_del_color BEFORE DELETE ON color FOR EACH ROW
BEGIN
    #IF NEW.name = 'red' THEN
        INSERT INTO employee(`name`,`depid`) VALUES(OLD.NAME,'105');
    #END IF;
END $$
delimiter ; 
DELETE FROM color WHERE id=2;

3、函数

自定义函数

delimiter $$
CREATE FUNCTION f1(
　 s1 INT,
　 s2 INT
)
RETURNS INT
BEGIN
　 DECLARE num INT;
　 set num = s1 + s2;
　 RETURN(num);
END $$
delimiter ;

select f1(11,22)

删除函数

drop function func_name;

4、事务处理

delimiter \\
create PROCEDURE p1(
    OUT p_return_code tinyint
)
BEGIN 
  DECLARE exit handler for sqlexception 
  BEGIN 
    -- ERROR 
    set p_return_code = 1; 
    rollback;   #回滚
  END; 
 
  DECLARE exit handler for sqlwarning 
  BEGIN  开始事务
    -- WARNING 
    set p_return_code = 2; 
    rollback; 
  END; 
 
  START TRANSACTION; 
    DELETE from tb1;
    insert into tb2(name)values('seven');
  COMMIT; #提交
 
  -- SUCCESS 
  set p_return_code = 0; 
 
  END\\
delimiter ;

动态执行SQL语句

delimiter \\
DROP PROCEDURE IF EXISTS proc_sql \\
CREATE PROCEDURE proc_sql (
    in strSql VARCHAR(128),
    in nid int
)
BEGIN
    set @p1 = nid;
    set @sq1l = strSql;

    PREPARE prod FROM @sq1l;
    EXECUTE prod USING @p1;
    DEALLOCATE prepare prod; 

END\\
delimiter ;

CALL proc_sql('select * from color where id > ?',1)  #执行语句