本文實(shí)例講述了mysql存儲(chǔ)過(guò)程原理與使用方法。分享給大家供大家參考，具體如下：
存儲(chǔ)過(guò)程包含了一系列可執(zhí)行的sql語(yǔ)句，存儲(chǔ)過(guò)程存放于MySQL中，通過(guò)調(diào)用它的名字可以執(zhí)行其內(nèi)部的一堆sql
存儲(chǔ)過(guò)程的優(yōu)點(diǎn)
#1. 用于替代程序?qū)懙腟QL語(yǔ)句，實(shí)現(xiàn)程序與sql解耦
#2. 可以通過(guò)直接修改存儲(chǔ)過(guò)程的方式修改業(yè)務(wù)邏輯（或bug），而不用重啟服務(wù)器
#3. 執(zhí)行速度快，存儲(chǔ)過(guò)程經(jīng)過(guò)編譯之后會(huì)比單獨(dú)一條一條執(zhí)行要快
#4. 減少網(wǎng)絡(luò)傳輸，尤其是在高并發(fā)情況下這點(diǎn)優(yōu)勢(shì)大，存儲(chǔ)過(guò)程直接就在數(shù)據(jù)庫(kù)服務(wù)器上跑，所有的數(shù)據(jù)訪問(wèn)都在服務(wù)器內(nèi)部進(jìn)行，不需要傳輸數(shù)據(jù)到其它終端。
存儲(chǔ)過(guò)程的缺點(diǎn)
1.SQL本身是一種結(jié)構(gòu)化查詢語(yǔ)言，加上了一些控制（賦值、循環(huán)和異常處理等），但不是OO的，本質(zhì)上還是過(guò)程化的，面對(duì)復(fù)雜的業(yè)務(wù)邏輯，過(guò)程化的處理會(huì)很吃力。這一點(diǎn)算致命傷，即只能應(yīng)用在邏輯簡(jiǎn)單的業(yè)務(wù)上。
2.不便于調(diào)試?；旧蠜](méi)有較好的調(diào)試器，很多時(shí)候是用print來(lái)調(diào)試，但用這種方法調(diào)試長(zhǎng)達(dá)數(shù)百行的存儲(chǔ)過(guò)程簡(jiǎn)直是噩夢(mèng)。好吧，這一點(diǎn)不算啥，C#/java一樣能寫(xiě)出噩夢(mèng)般的代碼。
3.沒(méi)辦法應(yīng)用緩存。雖然有全局臨時(shí)表之類的方法可以做緩存，但同樣加重了數(shù)據(jù)庫(kù)的負(fù)擔(dān)。如果緩存并發(fā)嚴(yán)重，經(jīng)常要加鎖，那效率實(shí)在堪憂。
4.無(wú)法適應(yīng)數(shù)據(jù)庫(kù)的切割（水平或垂直切割）。數(shù)據(jù)庫(kù)切割之后，存儲(chǔ)過(guò)程并不清楚數(shù)據(jù)存儲(chǔ)在哪個(gè)數(shù)據(jù)庫(kù)中。
無(wú)參的存儲(chǔ)過(guò)程
delimiter //
create procedure p1()
BEGIN
select * from blog;
INSERT into blog(name,sub_time) values("xxx",now());
END //
delimiter ;
#在mysql中調(diào)用
call p1()
#在python中基于pymysql調(diào)用
cursor.callproc('p1')
print(cursor.fetchall())
有參的存儲(chǔ)過(guò)程
對(duì)于存儲(chǔ)過(guò)程，可以接收參數(shù)，其參數(shù)有三類：
#in 僅用于傳入?yún)?shù)用
#out 僅用于返回值用
#inout 既可以傳入又可以當(dāng)作返回值
帶in的存儲(chǔ)過(guò)程
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調(diào)用
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調(diào)用
cursor.callproc('p3',(3,0)) #0相當(dāng)于set @res=0
print(cursor.fetchall()) #查詢select的查詢結(jié)果
cursor.execute('select @_p3_0,@_p3_1;') #@p3_0代表第一個(gè)參數(shù)，@p3_1代表第二個(gè)參數(shù)，即返回值
print(cursor.fetchall())
帶有inout的例子
delimiter //
create procedure p4(
inout n1 int
)
BEGIN
select * from blog where id > n1;
set n1 = 1;
END //
delimiter ;
#在mysql中調(diào)用
set @x=3;
call p4(@x);
select @x;
#在python中基于pymysql調(diào)用
cursor.callproc('p4',(3,))
print(cursor.fetchall()) #查詢select的查詢結(jié)果
cursor.execute('select @_p4_0;')
print(cursor.fetchall())
事務(wù)
#介紹
delimiter //
create procedure p4(
out status int
)
BEGIN
1. 聲明如果出現(xiàn)異常則執(zhí)行{
set status = 1;
rollback;
}
開(kāi)始事務(wù)
-- 由秦兵賬戶減去100
-- 方少偉賬戶加90
-- 張根賬戶加10
commit;
結(jié)束
set status = 2;
END //
delimiter ;
#實(shí)現(xiàn)
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; #執(zhí)行失敗
insert into blog(name,sub_time) values('yyy',now());
COMMIT;
-- SUCCESS
set p_return_code = 0; #0代表執(zhí)行成功
END //
delimiter ;
#在mysql中調(diào)用存儲(chǔ)過(guò)程
set @res=123;
call p5(@res);
select @res;
#在python中基于pymysql調(diào)用存儲(chǔ)過(guò)程
cursor.callproc('p5',(123,))
print(cursor.fetchall()) #查詢select的查詢結(jié)果
cursor.execute('select @_p5_0;')
print(cursor.fetchall())
存儲(chǔ)過(guò)程的執(zhí)行
mysql中執(zhí)行
-- 無(wú)參數(shù)
call proc_name()
-- 有參數(shù)，全in
call proc_name(1,2)
-- 有參數(shù)，有in，out，inout
set @t1=0;
set @t2=3;
call proc_name(1,2,@t1,@t2)
pymsql中執(zhí)行
#!/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)
# 執(zhí)行存儲(chǔ)過(guò)程
cursor.callproc('p1', args=(1, 22, 3, 4))
# 獲取執(zhí)行完存儲(chǔ)的參數(shù)
cursor.execute("select @_p1_0,@_p1_1,@_p1_2,@_p1_3")
result = cursor.fetchall()
conn.commit()
cursor.close()
conn.close()
print(result)
刪除存儲(chǔ)過(guò)程
drop procedure proc_name;
更多關(guān)于MySQL相關(guān)內(nèi)容感興趣的讀者可查看本站專題：《MySQL存儲(chǔ)過(guò)程技巧大全》、《MySQL常用函數(shù)大匯總》、《MySQL日志操作技巧大全》、《MySQL事務(wù)操作技巧匯總》及《MySQL數(shù)據(jù)庫(kù)鎖相關(guān)技巧匯總》
希望本文所述對(duì)大家MySQL數(shù)據(jù)庫(kù)計(jì)有所幫助。
您可能感興趣的文章:- MySQL中的if和case語(yǔ)句使用總結(jié)
- mysql存儲(chǔ)過(guò)程之游標(biāo)（DECLARE）原理與用法詳解
- mysql存儲(chǔ)過(guò)程之返回多個(gè)值的方法示例
- mysql存儲(chǔ)過(guò)程之創(chuàng)建（CREATE PROCEDURE）和調(diào)用（CALL）及變量創(chuàng)建（DECLARE）和賦值（SET）操作方法
- mysql存儲(chǔ)過(guò)程之引發(fā)存儲(chǔ)過(guò)程中的錯(cuò)誤條件（SIGNAL和RESIGNAL語(yǔ)句）實(shí)例分析
- mysql存儲(chǔ)過(guò)程之錯(cuò)誤處理實(shí)例詳解
- mysql 存儲(chǔ)過(guò)程中變量的定義與賦值操作
- mysql存儲(chǔ)過(guò)程 游標(biāo) 循環(huán)使用介紹
- MySQL存儲(chǔ)過(guò)程例子(包含事務(wù)，輸出參數(shù)，嵌套調(diào)用)
- MySql存儲(chǔ)過(guò)程與函數(shù)詳解
- mysql存儲(chǔ)過(guò)程之if語(yǔ)句用法實(shí)例詳解
