存储过程

1. 什么是存储过程

   存储过程是一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后调用不需要再次编译，
   通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。

    

2. 简单实例

   create procedure p1()
   begin
       select * from blog;
   end
   
   --sql语句调用：call p1();
   --pymysql调用:cursor.callproc('p1')

    

3. 传参数(in)

   create procedure p2(
       in n1 int
   )
   begin
       select * from blog where id>n1;
   end
   
   --sql语句调用：call p2(3);
   --pymysql调用：cursor.callproc('p2',(3,))

    

4. 参数(out,inout)

   create procedure p3(
       in n1 int,
       out no2 int
   )
   begin
       set n2 =123;
       select * from blog where id>n1;
   end
   
   --sql语句调用：
   set @v1 = 0;
   call p3(2,@v1);
   select @v1;
   
   --pymysql调用：
   pymsql：
   cursor.callproc('p3',(4,0));
   cursor.fetchall()
   cursor.execute('select @_p3_0,@_p3_1')#@_p3_1指存储过程第一个参数
   cursor.fetchall()
   
   --应用
   用于标识存储过程的执行结果，比如返回0表示执行失败，1表示成功

    

5. 事务

   --事务用于将某些操作的多个SQL作为原子性操作，一旦有某一个出现错误，即可回滚到原来的状态，从而保证数据库数据完整性
   
   create procedure p4(
       out p_return_code tinyint
   )
   begin
       declare exit handler for sqlexception
       begin
           set p_return_code = 1;
           rollback;
       end;
       
       start transaction;
           delete from blog;
           insert into blog(title,ctime) values('q','2019/2/28');
       commit;
   
       set p_return_code = 2;
   end
   
   --结构：
   定义异常时操作：设置标记为未成功，回滚
   事务操作：开始事务，操作，提交事务，设置标记为成功

    

6. 游标

   以将表A的数据放入表B，并把值加1为例
   A
   id      num
   1          5
   2          6
   3          7
   
   B
   id        num
   
   
   create procedure p5()
   begin
       declare row_id int;
       declare row_num int;
       declare temp int;
       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;
           l:LOOP
               fetch my_cursor into row_id,row_num;
               if done then
                   leave l;
               end if;
               set temp = row_num + 1;
               insert into B(num) values(temp);
           end loop l;
       close my_cursor;
   end

    

7. 动态执行SQL，防SQL注入

   --通过预先检测 +格式化
   --占位符 : ？
   create procedure p6(
       in arg int
   )
   begin
       set @x = arg;
       prepare xxx from 'select * from blog where id >?';
       execute xxx using @x;
       deallocate prepare prod;
   end