MySQL学习之存储过程

存储过程

是存储在数据库中的一个别名，这个别名对应着一个SQL语句集合

存储过程优点

1. 用于替代程序写的SQL语句，实现程序与sql解耦

2. 基于网络传输，传别名的数据量小，而直接传sql数据量大

存储过程缺点

1. 程序员扩展功能不方便

存储过程和视图的区别

1，视图是虚拟的一张表，数据来源于物理表
2，存储过程可以进行任意操作
3，视图的调用方式是select
4，存储过程的调用方式是call

创建

建议： 在哪个库就把存储过程建在那里。

# 形式一：无参数

          MySQL中：
            delimiter //
            create procedure p1()
            BEGIN
                select * from student;
                select * from score where class_id = 1;
                update student set sname = '牛奶' where sname = '理解';
            END //
            delimiter ;

          MySQL执行：
            call p1();

          Python中执行：
            cursor.callproc('p1')

          删除：
            drop procedure [if exists] p1;

# 形式二：有参数
            参数的类型有三种：
                in：仅用于传参用
                out：仅用于返回值
                inout：既可用于传参，又可用于返回值
            创建：
                create PROCEDURE p1(in num1 int, in num2 int)
                begin
                select * from student where sid > num1;
                select repeat('abc', num2);
                end
            执行：
                call p1(1, 4)
                Python中执行：
                    cursor.callproc('p1', (1, 4))
            创建二：
                create PROCEDURE p1(in num1 int, out num2 int)
                begin
                select * from student where sid > num1;
                set num2 = 123123;
                end
            执行：
                set @v1=1;  # 创建变量，因为out num2 只能接受一个变量，并且是session级别的
                call p1(1, @v1);
                select @v1;
                Python中执行：
                    ret = cursor.callproc('p1', (1, 3))
                    print(cursor.fetchall())
                    cursor.execute('select @_p1_0, @_p1_1')
                    print(cursor.fetchall()
            创建三：
                    delimiter //
                    create procedure p2(
                        in n1 int,
                        inout n3 int,
                        out n2 int
                    )
                    begin
                        declare temp1 int ;
                        declare temp2 int default 0;
            # declare在存储过程或函数执行过程中生效 （必须要写在最顶部）
            # set是整个会话期间都起作用，相当于一个会话期间的全局变量
                        select * from student;
                        set n2 = n1 + 100;
                        set n3 = n3 + n1 + 100;
                    end //
                    delimiter ;
            执行：
                set @v2 = 1;
                set @v3 = 3;
                call p2(100, @v3, @v2);
                select @v2, @v3;

使用

call p1();

删除

drop procedure [if exists] p1;

存储过程之事务操作

用于将某些操作的多个SQL作为原子性操作，一旦有某一个出现错误，
即可回滚到原来的状态，从而保证数据库数据完整性。

create procedure p6(out p_status_code tinyint)
BEGIN

	declare exit handler for sqlexception
    BEGIN
            -- 失败
        set p_status_code = 1;
        rollback; # 回滚
    END;

    DECLARE exit handler for sqlwarning
    BEGIN
        -- WARNING
        set p_return_code = 2;
        rollback;
    END;

	start transaction;
		delete from transaction_test;  # 返回2
		# delete from transaction_test_1;  返回1
		insert into student(gender, class_id, sname)values('女', 2, 'belle');
	commit;
    -- 成功
	set p_status_code = 2;
END;

set @status_code = 0;
call p6(@status_code);
select @status_code;

存储过程之游标操作

如果想对表的每一行都进行操作，就用游标，其实游标的性能不高
例子：将transaction_test表的每一行的id, num相加，赋值给tran_test_B的num

create procedure p8()
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 transaction_test;
	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;    # iterate xxoo; 相当于python中的continue
			end if;
			set temp = row_id + row_num;
			insert into tran_test_B(number) values(temp);
		end loop xxoo;
	close my_cursor;
end;

存储过程之防SQL注入

create procedure p9(in username varchar(255), in passcode varchar(255))
BEGIN
	set @username = username;
	set @passcode = passcode;
	prepare pre_sql from 'select * from userinfo where uname = ? and upwd = ?';
	execute pre_sql using @username, @passcode;
	deallocate prepare pre_sql;
end;