python 存储过程_python学习笔记(75) 存储过程

存储过程 / SQL语句 / 类和对象

#########################################

delimiter //

creat PROCEDURE p1()

BEGIN

select * from student;

INSERT inti teacher(tname) values("ct");

END

delimiter ;

call p1();

cursor.callproc('p1')　　# pymysql模块 (charset = 'utf-8')

conn.commit()

#########################################

传参数：

delimiter //

creat procedure p2(

in n1 int,

out n2 int　　# inout等于in加out

)

BEGIN

set n2 = 123123;

select * from student where sid > n1;

END //

delimiter ;

set @vi =10;　　# session级别的变量v1

call p2(12,@v1);

select @v1;

cursor.callproc('p3',(12,2))

r1 = cursor.fetchall()

print(r1)

cursor.execute('select @_p3_0, @_p3_1')　　#获取输出值的格式

r2 = cursor.fetchall()

print(r2)

out常用来判断操作是否成功

#####################################

事物：

原子型操作

delimiter \\

creat PROCEDURE p5(

OUT p_return_code tinyint

)

BEGIN

DECLARE exit handler for sqlexception

BEGIN

--ERROR

set p_return_code = 1；

rollback;

END;

START TRANSACTION;

DELETE from tb1;

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

COMMIT;

--SUCESS

set p_return_code = 2;

END \\

delimeter ;

##########################################

游标(MySQL里的)：　　# 性能不高，对每一项分别进行操作的时候用

deliniter \\

creat procedure p6()

BEGIN

declare row_id int;

declare row_num int;

declare done int DEFAULT FALSE;

declare temp int;

declare my_cursor CURSOR FOR select in,num from db1;

declare CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

open my_cursor;

xxoo:LOOP

fetch my_cursor into row_id,row_num;

if done then

leave xxoo;

END IF;

set temp = row_id + row_num;

insert into db2(num)values(temp);

end loop xxoo;

close my_cursor;

end \\

delimiter ;

###########################################

动态执行SQL(防注入)：

delimiter \\

CREAT PROCEDURE p7(

in nid int;

)

BEGIN

set @nid = nid;

PREPARE prod FROM 'select * from student where sid > ?';

EXECUTE prod USING @nid;

DEALLOCATE prepare prod;

END \\

delimiter ;