原文：https://blog.ansheng.me/article/python-full-stack-way-mysql-stored-procedures
Python全棧之路系列之MySQL存儲(chǔ)過(guò)程
存儲(chǔ)過(guò)程是一個(gè)SQL語(yǔ)句集合，當(dāng)主動(dòng)去調(diào)用存儲(chǔ)過(guò)程時(shí)，其中內(nèi)部的SQL語(yǔ)句會(huì)按照邏輯執(zhí)行。
存儲(chǔ)過(guò)程過(guò)接收的參數(shù)
參數(shù)描述
in僅用于傳入?yún)?shù)用
out僅用于返回值用
inout既可以傳入又可以當(dāng)作返回值
創(chuàng)建存儲(chǔ)過(guò)程
創(chuàng)建一個(gè)簡(jiǎn)單的存儲(chǔ)過(guò)程
-- 修改SQL語(yǔ)句的結(jié)束符為%delimiter%-- 創(chuàng)建這個(gè)存儲(chǔ)過(guò)程先刪除DROPPROCEDUREIFEXISTSproc_p1%CREATEPROCEDUREproc_p1()-- 開始BEGIN-- SQL語(yǔ)句塊select*fromcolor;-- 結(jié)束END%-- 把SQL語(yǔ)句的結(jié)束符改為;delimiter;
通過(guò)call調(diào)用存儲(chǔ)過(guò)程
callproc_p1();
輸出為
+-----+--------+| nid | title? |+-----+--------+|? 1 | red? ? ||? 2 | yellow |+-----+--------+2 rowsinset(0.00 sec)Query OK, 0 rows affected(0.01 sec)
刪除存儲(chǔ)過(guò)程
DROPPROCEDUREproc_p1;
創(chuàng)建一個(gè)存儲(chǔ)過(guò)程，接收一個(gè)參數(shù)，傳入的參數(shù)就是顯示數(shù)據(jù)的個(gè)數(shù)，
delimiter%DROPPROCEDUREIFEXISTSproc_p1%createPROCEDUREproc_p1(-- i1就是傳入的參數(shù)，傳入的數(shù)據(jù)類型必須是int類型ini1int)BEGIN-- 定義兩個(gè)局部變量d1和d2，數(shù)據(jù)類型都為int，d1默認(rèn)值為空，d2默認(rèn)值為1DECLAREd1int;DECLAREd2intDEFAULT1;-- d1的值等于傳入過(guò)來(lái)的i1加上定義的局部變量d2的值SETd1=i1+d2;-- 查找person_info表中的nid大于d1的數(shù)據(jù)SELECT*FROMperson_infoWHEREnid>d1;END%delimiter;
查詢，括號(hào)內(nèi)輸入定義的參數(shù)
CALL proc_p1(4);
顯示結(jié)果
+-----+------+------------------+-------------+----------+----------+---------+-----------+| nid | name | email? ? ? ? ? ? | phone? ? ? | part_nid | position | caption | color_nid |+-----+------+------------------+-------------+----------+----------+---------+-----------+|? 6 | w? ? | as@anshengme.com | 13800138000 |? ? ? ? 5 | Python? | NULL? ? |? ? ? NULL ||? 9 | aa? | a@ansheng.me? ? | 13800138000 |? ? ? ? 3 | DBA? ? ? | NULL? ? |? ? ? ? 2 ||? 10 | b? ? | b.ansheng.me? ? | 13800138000 |? ? ? ? 3 | DBA? ? ? | NULL? ? |? ? ? ? 1 |+-----+------+------------------+-------------+----------+----------+---------+-----------+3 rowsinset(0.00 sec)Query OK, 0 rows affected(0.01 sec)
這次把nid大于5的數(shù)據(jù)全部輸出出來(lái)了，傳入的值是4，我們?cè)趦?nèi)部讓4+1了，所以就是大于5的數(shù)據(jù).
delimiter%DROPPROCEDUREIFEXISTSproc_p1%createPROCEDUREproc_p1(-- 接收了三個(gè)參數(shù)，類型都是intini1int,inoutiiint,outi2int)BEGIN-- 定義一個(gè)局部變量d2，默認(rèn)值是3，數(shù)據(jù)類型為intDECLAREd2intDEFAULT3;-- ii = ii + 1setii=ii+1;-- 如果傳入的i1等于1IFi1=1THEN-- i2 = 100 + d2seti2=100+d2;-- 如果傳入的i1等于2ELSEIFi1=2THEN-- i2 = 200 + d2seti2=200+d2;-- 否則ELSE-- i2 = 1000 + d2seti2=1000+d2;ENDIF;END%delimiter;
查看數(shù)據(jù)
set@o=5;CALLproc_p1(1,@o,@u);SELECT@o,@u;
顯示的結(jié)果
+------+------+| @o? | @u? |+------+------+|? ? 6 |? 103 |+------+------+1 rowinset(0.00 sec)
使用pymysql模塊操作存儲(chǔ)過(guò)程
Python代碼為：
importpymysqlconn=pymysql.connect(host="127.0.0.1",port=3306,user='root',passwd='as',db="dbname")cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)# 執(zhí)行存儲(chǔ)過(guò)程row=cursor.callproc("proc_p2",(1,2,3))# 存儲(chǔ)過(guò)程的查詢結(jié)果selc=cursor.fetchall()print(selc)# 獲取存儲(chǔ)過(guò)程返回effect_row=cursor.execute('select @_proc_p2_0, @_proc_p2_1, @_proc_p2_2')# 取存儲(chǔ)過(guò)程返回值result=cursor.fetchone()print(result)conn.commit()cursor.close()conn.close()
顯示的結(jié)果
C:\Python\Python35\python.exe D:/PycharmProjects/pymysql_存儲(chǔ)過(guò)程.py[{'nid': 1,'name':'man1'},{'nid': 2,'name':'man2'},{'nid': 3,'name':'man3'}]{'@_proc_p2_1': 3,'@_proc_p2_0': 1,'@_proc_p2_2': 103}Process finished withexitcode 0
存儲(chǔ)過(guò)程使用into
into其實(shí)就是把一個(gè)select的執(zhí)行結(jié)果當(dāng)作另一個(gè)select的參數(shù)，例如下面的實(shí)例：
delimiter%DROPPROCEDUREIFEXISTSproc_p2%CREATEPROCEDUREproc_p2()BEGIN-- 定義一個(gè)局部變量n，類型為intDECLAREnint;-- 獲取color_nid = 2的數(shù)據(jù)并賦值給nSELECTcolor_nidintonFROMperson_infowherecolor_nid=2;-- 輸出nid = n的數(shù)據(jù)SELECT*fromcolorWHEREnid=n;END%delimiter;
執(zhí)行
call proc_p2();
結(jié)果
+-----+--------+| nid | title? |+-----+--------+|? 2 | yellow |+-----+--------+1 rowinset(0.00 sec)Query OK, 0 rows affected(0.01 sec)
