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)
mit()
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) #执行语句
