mysql存储过程原理与使用方法详解

发布时间：2022-09-27 14:45:14 所属栏目：MySql教程来源：

导读：　　本文实例讲述了mysql存储过程原理与使用方法。分享给大家供大家参考，具体如下：

　　存储过程包含了一系列可执行的sql语句，存储过程存放于MySQL中，通过调用它的名字可以执行其内部的一堆sql

　　存储

　　本文实例讲述了mysql存储过程原理与使用方法。分享给大家供大家参考，具体如下：

　　存储过程包含了一系列可执行的sql语句，存储过程存放于MySQL中，通过调用它的名字可以执行其内部的一堆sql

　　存储过程的优点

　　#1. 用于替代程序写的SQL语句，实现程序与sql解耦

　　#2. 可以通过直接修改存储过程的方式修改业务逻辑（或bug）mysql原理，而不用重启服务器

　　#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 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相关内容感兴趣的读者可查看本站专题：《MySQL存储过程技巧大全》、《MySQL常用函数大汇总》、《MySQL日志操作技巧大全》、《MySQL事务操作技巧汇总》及《MySQL数据库锁相关技巧汇总》

　　希望本文所述对大家MySQL数据库计有所帮助。

（编辑：我爱制作网_池州站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

MySQL8.0新特征支持原	如何处理Mongodb中关于
mysql8 更改my.ini配置	mysql运用命令行模式备