MySQL存储过程事务

day61

保存在MySQL上的一个别名   >   一坨SQL语句

 

复制代码
-- delimiter //
-- create procedure p1()
-- BEGIN
--     select * from student;
--     INSERT into teacher(tname) values("ct");
-- END//
-- delimiter;

call p1(); #把sql语句封装进p1中
复制代码

注释内容(创建存储过程)执行完,可以通过call调用(执行存储过程)。

在函数中:

 

也可通过pymysql调用存储过程

复制代码
 1 import pymysql
 2 
 3 #打开
 4 conn = pymysql.connect(host= "localhost", user = "root", password="112358", database = "db3")
 5 #
 6 cursor = conn.cursor()
 7 cursor.callproc("p1")#p1存储过程
 8 result = cursor.fetchall() #
 9 
10 print(result)
11 #关闭数据库
12 cursor.close()
13 conn.close()
复制代码
cursor.callproc("p1")

执行结果:

((1, "", 1, "理解"), (2, "", 1, "钢蛋"), (3, "", 1, "张三"), (4, "", 1, "张一"), (5, "", 1, "张二"), (6, "", 1, "张四"), (7, "", 2, "铁锤"), (8, "", 2, "李三"), (9, "", 2, "李一"), (10, "", 2, "李二"), (11, "", 2, "李四"), (12, "", 3, "如花"), (13, "", 3, "刘三"), (14, "", 3, "刘一"), (15, "", 3, "刘二"), (16, "", 3, "刘四"), (18, "", 1, "触发"), (19, "", 1, "触发"), (20, "", 1, "触发"), (21, "", 1, "啦啦"))

Process finished with exit code 0

 

传参查询

in

复制代码
#传参数(in,out,inout)

delimiter //
create procedure p2(
    in n1 int,
    in n2 int
)
BEGIN
                
    select * from student where sid > n1;
END //
delimiter ;
复制代码

 調用方式:

call p2(12,2)
cursor.callproc("p2",(12,2))  #python中

 

out

复制代码
delimiter //
create procedure p3(
                in n1 int,
                inout n2 int
            )
            BEGIN
                set n2 = 123123;
                select * from student where sid > n1;
            END //
            delimiter ;
            
            set @v1 = 0;
            call p2(12,@v1)
复制代码

set @v1 = 0,傳入p2后,n1为12,n2为@v1,@v1为123123,相当传一个引用。

调用call p2(12,@v1)

查看 select @v1;

注意该查询过程需保存,p3才会生效。

在pymysql中

上半部分对应

 set @v1 = 10;
 call p2(12,@v1)

下半部分对应

select @v1;

其中@__p3_0,@__p3__1对应n1,n2。

 

存储过程在服务端,客户端可以调用。

 

事务 
delimiter //
create procedure p4(
                out status int
)
BEGIN
1. 声明如果出现异常则执行{
           set status = 1;    #出现错误
           rollback;             #回滚

}             
开始事务
         -- 由秦兵账户减去100
         -- 方少伟账户加90
         -- 张根账户加10
               commit;        #提交
          结束        
          set status = 2;          #说明没出错     

END //
delimiter ;
  

事务:  

http://www.cnblogs.com/wupeiqi/articles/5713323.html

支持事务操作

复制代码
delimiter \
create PROCEDURE p5(
    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 = 2;   #未出错
 
  END\
delimiter ;
复制代码

事务操作不会因为数据传输意外中断,而发生错账。

 

游标:

对表每一行都要进行操作,需要使用游标。

将表A导入B时,加上当前行的id。

复制代码
delimiter //
create procedure p3()
begin 
      declare row_id int; -- 自定义变量1  
      declare row_num int; -- 自定义变量2  
      declare done INT DEFAULT FALSE;#设初始值


       DECLARE my_cursor CURSOR FOR select id,num from A;#创建游标
       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    #如果数据取完done 为 TRUE
                                leave xxoo; #离开循环
                       END IF;
                       set temp = row_num + row_id; #导入B表后还需要加上当前行的id
                       insert into B(num) values(temp);
              end loop xxoo;
        close my_cursor;#关闭游标
end  //
delimter ;
复制代码

放在服务端,客户端通过p3名字调用。

A

B

 

删除:

drop procedure proc_name;