MySQL函数及存储过程
MySQL函数和存储过程
函数
数据库中的函数是一种可重复使用的命名代码块，用于在数据库中执行特定的操作或计算。
在MySQL中提供了很多函数，为我们的SQL提供了便利
- 内置函数
mysql> select count(r_id),max(r_id),min(r_id),avg(r_id) from resume_library;
+-------------+-----------+-----------+------------+
| count(r_id) | max(r_id) | min(r_id) | avg(r_id) |
+-------------+-----------+-----------+------------+
| 26764 | 27195 | 1 | 13491.3612 |
+-------------+-----------+-----------+------------+
1 row in set (0.03 sec)
mysql> select r_id,reverse(姓名) from resume_library where 姓名 like '于金_';
+-------+---------------+
| r_id | reverse(姓名) |
+-------+---------------+
| 285 | 曼金于 |
| 20499 | 淼金于 |
| 286 | 龙金于 |
+-------+---------------+
3 rows in set (0.00 sec)
mysql> select r_id,concat('阿龙','真帅'),now(),date_format(now(),'%Y-%m-%d %H:%i:%s') from resume_library where r_id=1;
+------+-----------------------+---------------------+----------------------------------------+
| r_id | concat('阿龙','真帅') | now() | date_format(now(),'%Y-%m-%d %H:%i:%s') |
+------+-----------------------+---------------------+----------------------------------------+
| 1 | 阿龙真帅 | 2024-09-22 17:30:22 | 2024-09-22 17:30:22 |
+------+-----------------------+---------------------+----------------------------------------+
1 row in set (0.00 sec)
-- 字符拼接
mysql> select concat('阿龙','真帅');
+-----------------------+
| concat('阿龙','真帅') |
+-----------------------+
| 阿龙真帅 |
+-----------------------+
1 row in set (0.00 sec)
-- 睡眠5秒
mysql> select sleep(5);
+----------+
| sleep(5) |
+----------+
| 0 |
+----------+
1 row in set (5.00 sec)
- 创建函数
-- 更改结束标志符号，方便创建函数
mysql> delimiter $$
mysql> create function f1()
-> returns int
-> begin
-> declare num int;
-> declare minid int;
-> declare maxid int;
-> select max(r_id) from resume.resume_library into maxid;
-> select min(r_id) from resume.resume_library into minid;
-> set num = maxid + minid;
-> return (num);
-> end $$
Query OK, 0 rows affected (0.00 sec)
-- 更改结束标志符号
mysql> delimiter ;
- 执行函数
select f1() from resume.resume_library;
- 删除函数
mysql> drop function f1;
Query OK, 0 rows affected (0.00 sec)
存储过程
存储过程，是一个存储MySQL中上sql语句的集合，当主动去调用存储过程时，其中内部的sql语句按照逻辑执行。
- 创建存储过程
mysql> create procedure p1()
-> begin
-> select * from t3;
-> end $$
Query OK, 0 rows affected (0.00 sec)
- 执行存储过程
call p1();
- Python程序执行存储过程
# -*- coding: UTF-8 -*-
"""
@Project ：lufei-learning-city
@File ：存储过程.py
@IDE ：PyCharm
@Author ：于金龙@阿龙的代码在报错
@Date ：2024/9/22 下午6:29
"""
import pymysql
conn = pymysql.connect(
host='localhost',
port=3306,
user='root',
password='20020115',
db='resume',
charset='utf8'
)
cursor = conn.cursor()
cursor.callproc('p1')
result = cursor.fetchall()
cursor.close()
conn.close()
print(result)
- 删除存储过程
mysql> drop procedure p1;
Query OK, 0 rows affected (0.00 sec)
参数类型
存储过程的参数可以分为三种类型：
- in,仅用于传入参数
- out,仅用于返回参数
- inout,既可以传入又可以当做返回值
use user;
-- 更改结束标识符
delimiter $$
create procedure p2(
in i1 int,
in i2 int,
inout i3 int,
out r1 int
)
begin
declare temp1 int;
declare temp2 int default 0;
set temp1 = 1;
set r1 = i1 + i2 + temp1 + temp2;
set i3 = i3 + 100;
end $$
-- 更改结束标识符
delimiter ;
调用存储过程
set @t1=4;
set @t2=0;
call p2(1,2,@t1,@t2)
select @t1,@t2
python进行执行
# -*- coding: UTF-8 -*-
"""
@Project ：lufei-learning-city
@File ：存储过程.py
@IDE ：PyCharm
@Author ：于金龙@阿龙的代码在报错
@Date ：2024/9/22 下午6:29
"""
import pymysql
conn = pymysql.connect(
host='localhost',
port=3306,
user='root',
password='20020115',
db='user',
charset='utf8'
)
cursor = conn.cursor()
# cursor.callproc('p1')
# result = cursor.fetchall()
cursor.callproc('p2', args=(1, 22, 3, 4))
table = cursor.fetchall()
cursor.execute('select @_p2_0,@_p2_1,@_p2_2,@_p2_3')
result = cursor.fetchall()
cursor.close()
conn.close()
print(result)
返回值和返回集
- 创建存储过程
delimiter $$
create procedure p3(
in n1 int,
inout n2 int,
out n3 int
)
begin
set n2 = n1 + 100;
set n3 = n2 + n1 + 100;
select * from girl;
end $$
delimiter ;
set @t1 = 4;
set @t2 = 0;
call p3(1, @t1, @t2);
select @t1, @t2;
# -*- coding: UTF-8 -*-
"""
@Project ：lufei-learning-city
@File ：存储过程.py
@IDE ：PyCharm
@Author ：于金龙@阿龙的代码在报错
@Date ：2024/9/22 下午6:29
"""
import pymysql
conn = pymysql.connect(
host='localhost',
port=3306,
user='root',
password='20020115',
db='user',
charset='utf8'
)
cursor = conn.cursor()
cursor.callproc('p3', args=(22, 3, 4))
table = cursor.fetchall() # 执行过程中的结果集
# 获取执行完存储参数
cursor.execute('select @_p3_0,@_p3_1,@_p3_2')
result = cursor.fetchall()
cursor.close()
conn.close()
print(result)
print(table)
- 事务和异常
事务，成功都成功，失败都失败
delimiter $$
create procedure p4(
out p_return_code tinyint
)
begin
declare exit handler for sqlexception
begin
-- error
set p_return_code = 1;
rollback;
end;
declare exit handler for sqlwarning
begin
-- waring
set p_return_code = 2;
rollback;
end;
start transaction ; -- 开启事务
delete from girl;
insert into girl(name) values ('1');
commit; -- 提交事务
set p_return_code = 0;
end $$
delimiter ;
python进行执行
# -*- coding: UTF-8 -*-
"""
@Project ：lufei-learning-city
@File ：存储过程.py
@IDE ：PyCharm
@Author ：于金龙@阿龙的代码在报错
@Date ：2024/9/22 下午6:29
"""
import pymysql
conn = pymysql.connect(
host='localhost',
port=3306,
user='root',
password='20020115',
db='user',
charset='utf8'
)
cursor = conn.cursor()
cursor.callproc('p4', args=(100,))
cursor.execute("select @_p4_0")
result = cursor.fetchall()
cursor.close()
conn.close()
print(result)
油标和存储过程
delimiter $$
create procedure p5()
begin
declare sid int;
declare sname varchar(50);
declare done int default false;
-- 声明油标
declare my_cursor cursor for select id, name
from girl;
declare continue handler for NOT FOUND set done = TRUE;
OPEN my_cursor;
xxoo:loop
fetch my_cursor into sid,sname;
if done then
leave xxoo;
end if;
insert into t1(name) values (sname);
end loop xxoo;
close my_cursor;
end $$
delimiter ;
call p5();