1.简单示例
delimiter || CREATE PROCEDURE p1( in n1 int, out n2 int ) BEGIN set n2=123; SELECT * FROM student WHERE stid>n1; END|| delimiter ; set @v1=0 call p1(10,@v1) SELECT @v1;
pymysql调用
import pymysql # 创建连接 conn = pymysql.connect(host='192.168.224.161', port=3306, user='root', passwd='123456', db='test', charset='utf8') # 创建游标 cursor = conn.cursor() # 执行SQL，并返回收影响行数 cursor.callproc("p1",(3,4)) #4为out参数 r1=cursor.fetchall() print(r1) cursor.execute("select @_p1_0,@_p1_1") r2=cursor.fetchall() #输出((3, 123),) print(r2) # 关闭游标 cursor.close() # 关闭连接 conn.close()
例子
mysql> show create table t1G; *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(12) DEFAULT NULL, `age` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec)
查看数据
mysql> select * from t1; +----+---------+-----+ | id | name | age | +----+---------+-----+ | 1 | linzi1 | 20 | | 2 | linzi2 | 21 | | 3 | linzi3 | 23 | | 4 | linzi4 | 24 | | 5 | linzi5 | 25 | | 6 | linzi6 | 26 | | 7 | linzi7 | 27 | | 8 | linzi8 | 28 | | 9 | linzi9 | 29 | | 10 | linzi10 | 30 | | 11 | linzi11 | 31 | | 12 | linzi12 | 32 | | 13 | linzi13 | 33 | | 14 | linzi14 | 34 | | 15 | linzi15 | 35 | | 16 | linzi16 | 36 | | 17 | linzi17 | 37 | | 18 | linzi18 | 38 | | 19 | linzi19 | 39 | | 20 | linzi20 | 40 | +----+---------+-----+ 20 rows in set (0.00 sec)
写一个存储过程;
mysql> d || mysql> create procedure p2(in length_num int,out count_num int) -> begin -> declare now_id int default 0; -> declare end_id int default 0; -> select max(id)+1 into now_id from t1; -> select now_id+length_num into end_id; -> while(now_id<end_id)do -> insert into t1 values (now_id,(select concat("linzi",now_id)),20+now_id); -> set now_id=now_id+1; -> end while; -> select count(*) into count_num from t1; -> end || Query OK, 0 rows affected (0.01 sec) mysql> d ; mysql> call p2(30,@total_num); Query OK, 1 row affected (0.14 sec) mysql> select @total_num; +------------+ | @total_num | +------------+ | 50 | +------------+ 1 row in set (0.01 sec) mysql> select * from t1; +----+---------+-----+ | id | name | age | +----+---------+-----+ | 1 | linzi1 | 20 | | 2 | linzi2 | 21 | | 3 | linzi3 | 23 | | 4 | linzi4 | 24 | | 5 | linzi5 | 25 | | 6 | linzi6 | 26 | | 7 | linzi7 | 27 | | 8 | linzi8 | 28 | | 9 | linzi9 | 29 | | 10 | linzi10 | 30 | | 11 | linzi11 | 31 | | 12 | linzi12 | 32 | | 13 | linzi13 | 33 | | 14 | linzi14 | 34 | | 15 | linzi15 | 35 | | 16 | linzi16 | 36 | | 17 | linzi17 | 37 | | 18 | linzi18 | 38 | | 19 | linzi19 | 39 | | 20 | linzi20 | 40 | | 21 | linzi21 | 41 | | 22 | linzi22 | 42 | | 23 | linzi23 | 43 | | 24 | linzi24 | 44 | | 25 | linzi25 | 45 | | 26 | linzi26 | 46 | | 27 | linzi27 | 47 | | 28 | linzi28 | 48 | | 29 | linzi29 | 49 | | 30 | linzi30 | 50 | | 31 | linzi31 | 51 | | 32 | linzi32 | 52 | | 33 | linzi33 | 53 | | 34 | linzi34 | 54 | | 35 | linzi35 | 55 | | 36 | linzi36 | 56 | | 37 | linzi37 | 57 | | 38 | linzi38 | 58 | | 39 | linzi39 | 59 | | 40 | linzi40 | 60 | | 41 | linzi41 | 61 | | 42 | linzi42 | 62 | | 43 | linzi43 | 63 | | 44 | linzi44 | 64 | | 45 | linzi45 | 65 | | 46 | linzi46 | 66 | | 47 | linzi47 | 67 | | 48 | linzi48 | 68 | | 49 | linzi49 | 69 | | 50 | linzi50 | 70 | +----+---------+-----+ 50 rows in set (0.00 sec)
例子继续
mysql> d || mysql> create procedure p3(in para1 int) -> begin -> declare now_id int default 0; -> select max(id)+1 into now_id from t3; -> if(para1<=now_id)then -> insert into t3 values (now_id,(select concat("lin",now_id)),20+now_id); -> else -> insert into t3 values (para1,(select concat("lin",para1)),20+para1); -> end if; -> end || Query OK, 0 rows affected (0.00 sec) mysql> d ; mysql> call p3(2); Query OK, 1 row affected (0.01 sec) mysql> select * from t3; +----+------+-----+ | id | name | age | +----+------+-----+ | 1 | lin1 | 21 | | 2 | lin2 | 22 | +----+------+-----+ 2 rows in set (0.00 sec) mysql> call p3(1); Query OK, 1 row affected (0.01 sec) mysql> select * from t3; +----+------+-----+ | id | name | age | +----+------+-----+ | 1 | lin1 | 21 | | 2 | lin2 | 22 | | 3 | lin3 | 23 | +----+------+-----+ 3 rows in set (0.00 sec) mysql> call p3(5); Query OK, 1 row affected (0.00 sec) mysql> select * from t3; +----+------+-----+ | id | name | age | +----+------+-----+ | 1 | lin1 | 21 | | 2 | lin2 | 22 | | 3 | lin3 | 23 | | 5 | lin5 | 25 | +----+------+-----+ 4 rows in set (0.00 sec) mysql> call p3(1); Query OK, 1 row affected (0.01 sec) mysql> select * from t3; +----+------+-----+ | id | name | age | +----+------+-----+ | 1 | lin1 | 21 | | 2 | lin2 | 22 | | 3 | lin3 | 23 | | 5 | lin5 | 25 | | 6 | lin6 | 26 | +----+------+-----+ 5 rows in set (0.00 sec)
函数的例子：
mysql> d || mysql> create function f1(p1 int) -> returns int -> begin -> declare now_id int default 0; -> declare total_count int default 0; -> select max(id)+1 into now_id from t3; -> if(p1<=now_id) then -> insert into t3 values (now_id,(select concat('lin',now_id)),20+now_id); -> else -> insert into t3 values (p1,(select concat('lin',p1)),20+p1); -> end if; -> select count(*) into total_count from t3; -> return total_count; -> end || Query OK, 0 rows affected (0.00 sec) mysql> d ; mysql> select f1(1); +-------+ | f1(1) | +-------+ | 6 | +-------+ 1 row in set (0.05 sec) mysql> select * from t3; +----+------+-----+ | id | name | age | +----+------+-----+ | 1 | lin1 | 21 | | 2 | lin2 | 22 | | 3 | lin3 | 23 | | 5 | lin5 | 25 | | 6 | lin6 | 26 | | 7 | lin7 | 27 | +----+------+-----+ 6 rows in set (0.00 sec) mysql> select f1(9); +-------+ | f1(9) | +-------+ | 7 | +-------+ 1 row in set (0.00 sec) mysql> select * from t3; +----+------+-----+ | id | name | age | +----+------+-----+ | 1 | lin1 | 21 | | 2 | lin2 | 22 | | 3 | lin3 | 23 | | 5 | lin5 | 25 | | 6 | lin6 | 26 | | 7 | lin7 | 27 | | 9 | lin9 | 29 | +----+------+-----+ 7 rows in set (0.00 sec)