MySQL——储存过程

1、创建一个存储过程

delimiter //
CREATE PROCEDURE p1()
BEGIN
	SELECT * from user;
	INSERT into user(name,PASSWORD,email) VALUE("we","123","12312");
END //
delimiter ;

数据库调用执行:

call p1();

pycharm中执行语句：

cursor.callproc("p1")
rs1 = cursor.fetchall()
print(rs1)

2、带in关键词的参数传递

delimiter //
CREATE PROCEDURE p2(
	in n1 int
)
BEGIN
	SELECT * from user where id>n1;
END //
delimiter ;

数据库调用执行:

call p2(12);

pycharm中执行语句：

cursor.callproc("p2",(12,))
rs1 = cursor.fetchall()
print(rs1)

3、带out关键词的参数传递

delimiter //
CREATE PROCEDURE p3(
 	in n1 int,
 	out n2 INT
)
BEGIN
	SET n2 = 1213;
	SELECT * from user where id>n1;
END //
delimiter ;

数据库调用执行:

SET @v1 = 0;
CALL p3(5,@v1);
select @v1;

pycharm中执行语句：

cursor.callproc("p3",(5,10))
rs1 = cursor.fetchall()
print(rs1)
    # 用来拿到out关键词的值
cursor.execute("select @_p3_0,@_p3_1")
rs2 = cursor.fetchall()
print(rs2)

4、存储过程——事务

delimiter //
CREATE PROCEDURE p4(
	OUT re INT
)
BEGIN
	declare exit handler for SQLEXCEPTION
	BEGIN
		SET re = 1;
		ROLLBACK;
	END;
	
	DECLARE exit HANDLER for SQLWARNING	
	BEGIN
		SET re = 2;
		ROLLBACK;
	END;

	START TRANSACTION;
		UPDATE user set name="老三" WHERE id=5;
		INSERT into tb1(name) VALUES("老四");
	COMMIT;

	SET re = 0;
END //
delimiter ;

数据库调用执行:

SET @v1 = 0;
CALL p4(@v1);
select @v1;

pycharm中执行语句：

cursor.callproc("p4",(5,))
conn.commit()
cursor.execute("select @_p4_0")
rs = cursor.fetchall()
print(rs)

5、存储过程——循环

delimiter //
CREATE PROCEDURE p5()
BEGIN
	DECLARE ssid int;
	DECLARE ssname VARCHAR(50);
	DECLARE done INT DEFAULT FALSE;
	
	DECLARE my_cursor CURSOR FOR select id,name from user;
	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 tb1(name) VALUES(ssname);
		END LOOP xxoo;
	CLOSE my_cursor;
END //
delimiter;

数据库调用执行:

call p5();

pycharm中执行语句：

cursor.callproc("p5")
conn.commit()

6、动态SQL（防注入）

delimiter \\
CREATE PROCEDURE p6 (
	in nid int
)
BEGIN
	SET @nid = nid;
	PREPARE prod FROM 'select * from user where id > ?';
	EXECUTE prod USING @nid;
	DEALLOCATE prepare prod; 
END\\
delimiter ;