mysql存储过程原理与使用方法详解
作者:dawn-liu 发布时间:2024-01-25 10:20:05
本文实例讲述了mysql存储过程原理与使用方法。分享给大家供大家参考,具体如下:
存储过程包含了一系列可执行的sql语句,存储过程存放于MySQL中,通过调用它的名字可以执行其内部的一堆sql
存储过程的优点
#1. 用于替代程序写的SQL语句,实现程序与sql解耦
#2. 可以通过直接修改存储过程的方式修改业务逻辑(或bug),而不用重启服务器
#3. 执行速度快,存储过程经过编译之后会比单独一条一条执行要快
#4. 减少网络传输,尤其是在高并 * 况下这点优势大,存储过程直接就在数据库服务器上跑,所有的数据访问都在服务器内部进行,不需要传输数据到其它终端。
存储过程的缺点
1.SQL本身是一种结构化查询语言,加上了一些控制(赋值、循环和异常处理等),但不是OO的,本质上还是过程化的,面对复杂的业务逻辑,过程化的处理会很吃力。这一点算致命伤,即只能应用在逻辑简单的业务上。
2.不便于调试。基本上没有较好的调试器,很多时候是用print来调试,但用这种方法调试长达数百行的存储过程简直是噩梦。好吧,这一点不算啥,C#/java一样能写出噩梦般的代码。
3.没办法应用缓存。虽然有全局临时表之类的方法可以做缓存,但同样加重了数据库的负担。如果缓存并发严重,经常要加锁,那效率实在堪忧。
4.无法适应数据库的切割(水平或垂直切割)。数据库切割之后,存储过程并不清楚数据存储在哪个数据库中。
无参的存储过程
delimiter //
create procedure p1()
BEGIN
select * from blog;
INSERT into blog(name,sub_time) values("xxx",now());
END //
delimiter ;
#在mysql中调用
call p1()
#在python中基于pymysql调用
cursor.callproc('p1')
print(cursor.fetchall())
有参的存储过程
对于存储过程,可以接收参数,其参数有三类:
#in 仅用于传入参数用
#out 仅用于返回值用
#inout 既可以传入又可以当作返回值
带in的存储过程
mysql> select * from emp;
+----+----------+-----+--------+
| id | name | age | dep_id |
+----+----------+-----+--------+
| 1 | zhangsan | 18 | 1 |
| 2 | lisi | 19 | 1 |
| 3 | egon | 20 | 2 |
| 5 | alex | 18 | 2 |
+----+----------+-----+--------+
4 rows in set (0.30 sec)
mysql> delimiter //
mysql> create procedure p2(in n1 int, in n2 int)
-> begin
-> select * from emp where id >n1 and id <n2;
-> end //
Query OK, 0 rows affected (0.28 sec)
mysql> delimiter ;
mysql> call p2(1,3)
-> ;
+----+------+-----+--------+
| id | name | age | dep_id |
+----+------+-----+--------+
| 2 | lisi | 19 | 1 |
+----+------+-----+--------+
1 row in set (0.07 sec)
Query OK, 0 rows affected (0.07 sec)
#在python中基于pymysql调用
cursor.callproc('p2',(1,3))
print(cursor.fetchall())
带有out
mysql> delimiter //
mysql> create procedure p3( in n1 int, out res int)
-> begin
-> select * from emp where id >n1;
-> set res=1;
-> end //
Query OK, 0 rows affected (0.28 sec)
mysql> delimiter ;
mysql> set @res=0;
Query OK, 0 rows affected (0.00 sec)
mysql> call p3(3,@res);
+----+------+-----+--------+
| id | name | age | dep_id |
+----+------+-----+--------+
| 5 | alex | 18 | 2 |
+----+------+-----+--------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
mysql> select @res;
+------+
| @res |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
#在python中基于pymysql调用
cursor.callproc('p3',(3,0)) #0相当于set @res=0
print(cursor.fetchall()) #查询select的查询结果
cursor.execute('select @_p3_0,@_p3_1;') #@p3_0代表第一个参数,@p3_1代表第二个参数,即返回值
print(cursor.fetchall())
带有inout的例子
delimiter //
create procedure p4(
inout n1 int
)
BEGIN
select * from blog where id > n1;
set n1 = 1;
END //
delimiter ;
#在mysql中调用
set @x=3;
call p4(@x);
select @x;
#在python中基于pymysql调用
cursor.callproc('p4',(3,))
print(cursor.fetchall()) #查询select的查询结果
cursor.execute('select @_p4_0;')
print(cursor.fetchall())
事务
#介绍
delimiter //
create procedure p4(
out status int
)
BEGIN
1. 声明如果出现异常则执行{
set status = 1;
rollback;
}
开始事务
-- 由秦兵账户减去100
-- 方少伟账户加90
-- 张根账户加10
commit;
结束
set status = 2;
END //
delimiter ;
#实现
delimiter //
create PROCEDURE p5(
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
-- WARNING
set p_return_code = 2;
rollback;
END;
START TRANSACTION;
DELETE from tb1; #执行失败
insert into blog(name,sub_time) values('yyy',now());
COMMIT;
-- SUCCESS
set p_return_code = 0; #0代表执行成功
END //
delimiter ;
#在mysql中调用存储过程
set @res=123;
call p5(@res);
select @res;
#在python中基于pymysql调用存储过程
cursor.callproc('p5',(123,))
print(cursor.fetchall()) #查询select的查询结果
cursor.execute('select @_p5_0;')
print(cursor.fetchall())
存储过程的执行
mysql中执行
-- 无参数
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)
pymsql中执行
#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 执行存储过程
cursor.callproc('p1', args=(1, 22, 3, 4))
# 获取执行完存储的参数
cursor.execute("select @_p1_0,@_p1_1,@_p1_2,@_p1_3")
result = cursor.fetchall()
conn.commit()
cursor.close()
conn.close()
print(result)
删除存储过程
drop procedure proc_name;
希望本文所述对大家MySQL数据库计有所帮助。
来源:https://www.cnblogs.com/mmyy-blog/p/9852986.html
猜你喜欢
- 示例可以附件中下载 1.加载xml文件 加载XML文件共有2种方法,一是加载指定字符串,二是加载指定文件 2.获取element的方法 a)
- 本文详细讲述了python的内嵌time模块的用法。分享给大家供大家参考之用。具体分析如下: 一、简介time模块提供各种操作时
- 1 与达尔文对话140年前,1858年7月1日,达尔文在英伦岛发表了自己有关自然选择的杰出论文。他提出,生物的发展规律是物竞天择。经过物竞,
- 在sql语句后使用 SCOPE_IDENTITY() 当然您也可以使用 SELECT @@IDENTITY 但是使用 SELECT @@ID
- 在网络浏览器软件中,可以Internet Explorer (IE)现在是一种标准的软件。可以看到,运行不同版本的Windows操作系统(和
- bottle是一个小型web框架,很小只有一个文件,但功能确很强大,学起来也简单,简单和小巧的同时也有很多不足,某些功能支持还不是很完善,比
- 本文实例讲述了Python本地与全局命名空间用法。分享给大家供大家参考。具体如下:x = 1def fun(a): b=3 &n
- 译序:这篇文章是可用性大师 Jakob Nielsen 在10年前总结的,到今天仍然受用。通过这个时间跨度,可以得出,可用性话题不是某个时代
- 1.可能是/usr/local/mysql/data/rekfan.pid文件没有写的权限解决方法 :给予权限,执行 “chown -R m
- TMD这年头做前端真命苦,IE出了N个版本就算了,还跳出个马桶出来。你说你这个马桶,用人家内核就好好用拉。还改人家的东西干啥,那你改就改拉,
- 本文介绍基于Python语言arcpy模块,实现栅格影像图层建立与多幅遥感影像数据批量拼接(Mosaic)的操作。首先,相关操作所需具体代码
- 以Python 3.x版本为主一、缩进式编码解释型:没有编译环节,直接运行执行代码显示效果缩进式编码风格:代码块不使用大括号{}包含类、函数
- 一、采用?a=1&b=2访问修改views.py:views.pyfrom django.shortcuts import rend
- 如下所示:def str_to_hex(s): s = s.split(' ')&nbs
- 前言事情是这样的:今天晚上,女朋友让我十二点催她睡觉。不过,可是我实在太困了,熬不下去…… 是吧
- 安装SDK:pip install baidu-aip如果在pycharm里也可以在setting----Project Interpret
- 其实我这样做的本意是为了防止盗链!大家帮忙看一下通过代码能够根治盗链!只要不是HTTP_REFERER来源于(google.com goog
- Python 内置的 itertools 模块包含了一系列用来产生不同类型迭代器的函数或类,这些函数的返回都是一个迭代器,我们可以通过 fo
- 举几个例子来介绍一下,Python 的 CSV模块的使用方法,包括,reader, writer, DictReader, DictWrit
- 我一直很难理解Javascript语言的继承机制。它没有"子类"和"父类"的概念,也没有"