存储过程 / 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 ;
