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=0call 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 t1\G;*************************** 1. row ***************************       Table: t1Create 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=utf8mb41 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
     
       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)