mysql 存储过程

1. 创建存储过程

无参数存储过程

    -- 创建存储过程

    delimiter //
    create procedure p1()
    BEGIN
        select * from t1;
    END//
    delimiter ;



    -- 执行存储过程

    call p1()

对于存储过程，可以接收参数，其参数有三类：

 1. in          仅用于传入参数用
 2. out        仅用于返回值用
 3. inout     既可以传入又可以当作返回值

1. 有参数存储过程

   -- 创建存储过程
   delimiter \\
   create procedure p1(
       in i1 int,
       in i2 int,
       inout i3 int,
       out r1 int
   )
   BEGIN
       DECLARE temp1 int;
       DECLARE temp2 int default 0;
   
       set temp1 = 1;
   
       set r1 = i1 + i2 + temp1 + temp2;
   
       set i3 = i3 + 100;
   
   end\\
   delimiter ;
   
   -- 执行存储过程
   set @t1 =4;
   set @t2 = 0;
   CALL p1 (1, 2 ,@t1, @t2);
   SELECT @t1,@t2;
   

2. 结果集

       delimiter //
       create procedure p1()
       begin
           select * from v1;
       end //
       delimiter ;
   

3. 结果集+out值

       delimiter //
          create procedure p2(
                in n1 int,
                inout n3 int,
                out n2 int,
            )
            begin
                declare temp1 int ;
                declare temp2 int default 0;
   
            select * from v1;
              set n2 = n1 + 100;
              set n3 = n3 + n1 + 100;
          end //
          delimiter ;
   

4. 实务

   delimiter \\
        create PROCEDURE p1(
             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 = 0; 
   
           END\\
     delimiter ;
   

5. 游标

   delimiter //
          create procedure p3()
          begin 
              declare ssid int; -- 自定义变量1  
              declare ssname varchar(50); -- 自定义变量2  
              DECLARE done INT DEFAULT FALSE;
   
   
              DECLARE my_cursor CURSOR FOR select sid,sname from student;
              DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
   
              open my_cursor;
                  xxoo: LOOP
                      fetch my_cursor into ssid,ssname;
                      if done then 
                          leave xxoo;
                      END IF;
                      insert into teacher(tname) values(ssname);
                  end loop xxoo;
              close my_cursor;
          end  //
          delimter ;
   

6. 动态执行SQL

               delimiter \\
                   CREATE PROCEDURE p4 (
                       in nid int
                   )
                   BEGIN
                       PREPARE prod FROM 'select * from student where sid > ?';
                       EXECUTE prod USING @nid;
                       DEALLOCATE prepare prod; 
                   END\\
                   delimiter ;
   

   删除存储过程

   drop procedure proc_name;
   

执行存储过程

        -- 无参数
    call proc_name()

    -- 有参数，全in
    call proc_name(1,2)

    -- 有参数，有in，out，inout
    set @t1=0;
    set @t2=3;
    call proc_name(1,2,@t1,@t2)

pymysql执行存储过程

    #!/usr/bin/env python
    # -*- coding:utf-8 -*-
    import pymysql

    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    # 执行存储过程
    cursor.callproc('p1', args=(1, 22, 3, 4))
    # 获取执行完存储的参数
    cursor.execute("select @_p1_0,@_p1_1,@_p1_2,@_p1_3")
    result = cursor.fetchall()

    conn.commit()
    cursor.close()
    conn.close()


    print(result)