mysql 自定义函数 事务_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 DEFAULT1;

set ii= ii + 1;

IF i1= 1THEN

set i2= 100 +d2;

ELSEIF i1= 2THEN

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操作存储过程

importpymysql

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)

View Code

删除函数

drop function func_name;

View Code

4、事务处理

delimiter \\

create PROCEDURE p1(

OUT p_return_code tinyint

)

BEGIN

DECLARE exit handlerforsqlexception

BEGIN--ERROR

set p_return_code= 1;

rollback;#回滚

END;

DECLARE exit handlerforsqlwarning

BEGIN 开始事务--WARNING

set p_return_code= 2;

rollback;

END;

START TRANSACTION;

DELETEfromtb1;

insert into tb2(name)values('seven');

COMMIT;#提交

--SUCCESS

set p_return_code=0;

END\\

delimiter ;

View Code

动态执行SQL语句

delimiter \\

DROP PROCEDURE IF EXISTS proc_sql \\

CREATE PROCEDURE proc_sql (in strSql VARCHAR(128),innid 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) #执行语句