MySQL存储过程


存储过程

  概念
    保存在MySQL数据库上的 -> 一坨SQL语句
    
    方式一:
        mysql:存储过程
        程序员:调用存储过程名
        
    方式二:
        mysql:.....
        程序员:自己写代码实现(SQL语句)
        
    方式三:
        mysql:.....
        程序员:创建类和对象(SQL语句)
        
        1. 简单
            create procedure p1()
            BEGIN
                select * from student;
                INSERT into teacher(tname) values("程序猿");
            END
            在pymysql中
            call p1()
            cursor.callproc('p1')
            
        2. 传参数(in,out,inout)
            参数in
                delimiter //
                create procedure p2(
                    in n1 int,
                    in n2 int
                )
                BEGIN
                    select * from student where sid > n1;
                END //
                delimiter ;
                
                call p2(10,2)
                在pymysql中
                cursor.callproc('p2',(10,2))
            
            参数 out
                delimiter //
                create procedure p3(
                    in n1 int,
                    out n2 int
                )
                BEGIN
                    set n2 = 10086;
                    select * from student where sid > n1;
                END //
                delimiter ;
                
                set @v1 = 10;  相当于session 传进来的值10拿不到,拿到的是存储过程中的,但是在pymysql中调用callproc()时,可以将两个数值(10,10086)都可以拿到,用来验证存储过程是否正确;
  
                call p3(12,@v1)
                select @v1;
                
                在pymysql中
                cursor.callproc('p3',(12,2))
                r1 = cursor.fetchall()
                print(r1)

                cursor.execute('select @_p3_0,@_p3_1')  传进来的值@_p3_1拿不到,拿到的是存储过程中的10086  
                r2 = cursor.fetchall()
                print(r2)

            =======> 特殊
                    a. 可传参:in   out   inout
                    b.无返回值, 通过out伪造返回值
                    c. pymysql
                        
                            cursor.callproc('p3',(12,2))
                            r1 = cursor.fetchall()
                            print(r1)

                            cursor.execute('select @_p3_0,@_p3_1')
                            r2 = cursor.fetchall()
                            print(r2)
                                        
        为什么有结果集又有out伪造的返回值?
        
        
            delimiter //
            create procedure p3(
                in n1 int,
                out n2 int  用于标识存储过程的执行结果  1,2
            )
            BEGIN
                insert into vv(..)
                insert into vv(..)
                insert into vv(..)
                insert into vv(..)
                insert into vv(..)
                insert into vv(..)
            END //
            delimiter ;
            
    3.事务  -> 原子性操作
        delimiter //
            create procedure p4(
                out status int
            )
            BEGIN
                1. 声明如果出现异常则执行{
                    set status = 1;0
                    rollback;
                }
                   
                开始事务
                    -- 由秦兵账户减去100
                    -- 方少伟账户加90
                    -- 张根账户加10
                    commit;
                结束
                
                set status = 2;
                
                
            END //
            delimiter ;
            
            ===============================
            delimiter \\
                create 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('seven');
                    COMMIT; 
                 
                    -- SUCCESS 
                    set p_return_code = 2; 
                 
                END\\
            delimiter ;
    4.游标(cursor)  -> 如果对单独的表每行进行分类时,一般优先选择
        先声明在调用
        新建表A和表B
        delimiter //
            create procedure p6()
            begin 
                declare row_id int; -- 自定义变量1  
                declare row_num int; -- 自定义变量2 
                declare done INT DEFAULT FALSE;
                declare temp int;
                
                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 
                            leave xxoo;
                        END if;
                        set temp = row_id + row_num;
                        insert into B(number) values(temp);
                    end loop xxoo;
                close my_cursor;
                
            end  //
        delimiter ;
        
    5.动态执行SQL    
        delimiter //
            create procedure p7(
                in tpl varchar(255),
                in arg int
            )
            begin 
                1. 预检测某个东西 SQL语句合法性
                2. SQL =格式化 tpl + arg 
                3. 执行SQL语句
                
                set @xo = arg;
                PREPARE xxx FROM 'select * from student where sid > ?';
                EXECUTE xxx USING @xo;
                DEALLOCATE prepare prod; 
            end  //
        delimiter ;
            
            call p7("select * from tb where id > ?",9)
        
            ===>
        delimiter \\
            CREATE PROCEDURE p8(
                in nid int
            )
            BEGIN
                set @nid = nid;
                PREPARE prod FROM 'select * from student where sid > ?';
                EXECUTE prod USING @nid;
                DEALLOCATE prepare prod; 
            END\\
        delimiter ;
    6.数据库相关操作:
    1. SQL语句 *****
        - select xx() from xx ;
        如果性能要求高,则不建议用函数
    2. 利用MySQL内部提供的功能
            

 

posted @ 2018-12-02 22:39  随性岁分享  阅读(7)  评论(0)    收藏  举报