mysql存储过程和函数

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)

mysql存储过程和函数

相关推荐