存储过程:是一个SQL语句集合，当主动去调用存储过程时，其中内部的SQL语句会按照逻辑执行。
1.创建存储过程:
-- 创建存储过程
delimiter //
create procedure p1()
BEGIN
select * from t1;
END//
delimiter ;
-- 执行存储过程
call p1()
存储过程参数:
in:仅用于传入参数用
out:仅用于返回值用
inout:既可以传入又可以当作返回值
1 -- 创建存储过程
2 delimiter \\
3 create procedure p1(
4 in i1 int,
5 in i2 int,
6 inout i3 int,
7 out r1 int
8 )
9 BEGIN
10 DECLARE temp1 int;
11 DECLARE temp2 int default 0;
12
13 set temp1 = 1;
14
15 set r1 = i1 + i2 + temp1 + temp2;
16
17 set i3 = i3 + 100;
18
19 end\\
20 delimiter ;
21
22 -- 执行存储过程
23 set @t1 =4;
24 set @t2 = 0;
25 CALL p1 (1, 2 ,@t1, @t2);
26 SELECT @t1,@t2;
示例:有参存储过程
1 delimiter //
2 create procedure p1()
3 begin
4 select * from v1;
5 end //
6 delimiter ;
示例:结果集
1 delimiter //
2 create procedure p2(
3 in n1 int,
4 inout n3 int,
5 out n2 int,
6 )
7 begin
8 declare temp1 int;
9 declare temp2 int default 0;
10 select * from v1;
11 set n2 = n1 + 100;
12 set n3 = n3 + n1 + 100;
13 end //
14 delimiter;
示例:结果集+Out
1 delimiter \ \
2 create PROCEDURE p1(OUT p_return_code tinyint)
3 BEGIN
4 DECLARE exit handler for sqlexception
5 BEGIN
6 --执行失败
7 set p_return_code = 1;
8 rollback;
9 END;
10 DECLARE exit handler for sqlwarning
11 BEGIN
12 --警示
13 set p_return_code = 2;
14 rollback;
15 END;
16
17 START TRANSACTION;
18 DELETE from tb1;
19 insert into tb2(name) values('seven');
20 COMMIT;
21 -- SUCCESS
22 set p_return_code = 0;
23 END\ \
24 delimiter;
示例:事务
1 delimiter //
2 create procedure p3()
3 begin
4 declare ssid int; -- 自定义变量1
5 declare ssname varchar(50); -- 自定义变量2
6 DECLARE done INT DEFAULT FALSE;
7
8
9 DECLARE my_cursor CURSOR FOR select sid,sname from student;
10 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
11
12 open my_cursor;
13 xxoo: LOOP
14 fetch my_cursor into ssid,ssname;
15 if done then
16 leave xxoo;
17 END IF;
18 insert into teacher(tname) values(ssname);
19 end loop xxoo;
20 close my_cursor;
21 end //
22 delimter ;
示例:游标
1 delimiter \\
2 CREATE PROCEDURE p4 (
3 in nid int
4 )
5 BEGIN
6 PREPARE prod FROM 'select * from student where sid > ?';
7 EXECUTE prod USING @nid;
8 DEALLOCATE prepare prod;
9 END\\
10 delimiter ;
示例:动态执行SQL
2.删除存储过程:
drop procedure proc_name;
3.执行存储过程
-- 无参数
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)
1 #!/usr/bin/env python
2 # -*- coding:utf-8 -*-
3 import pymysql
4 conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
5 cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
6 # 执行存储过程
7 cursor.callproc('p1', args=(1, 22, 3, 4))
8 # 获取执行完存储的参数
9 cursor.execute("select @_p1_0,@_p1_1,@_p1_2,@_p1_3")
10 result = cursor.fetchall()
11 conn.commit()
12 cursor.close()
13 conn.close()
14 print(result)
PyMysql
发表评论