MySQL預存程序

標籤：方法   limit   bool   sof   類型   ref   迴圈   依次   roo   

含義

預存程序類似一個函數，資料庫中也支援迴圈語句和判斷語句。我們可以將所有的邏輯、判斷和SQL語句全部寫在資料庫上面，然後取個名字，當程式來操作的時候，可以直接通過這個名字，就能執行對應的功能；不用傳大量的SQL語句。

 

預存程序

一、建立預存程序

關鍵字： procedure

代碼：

create procedure proc_p1()

說明：

• create procedure：表示要建立儲存。
• proc_p1：是建立儲存的名字。

這樣就建立了一個預存程序，名為proc_p1；但是此時裡面並沒內容。

二、建立預存程序內容

代碼：

create procedure proc_p1()begin內容置放位置end

說明：
要操作的內容必須放在，begin和end之間。

案例：

資料庫內容：

作業碼:

create procedure proc_p1()BEGINselect * from part;END

結果；產生的對象會儲存在函數中；

三、調用預存程序

關鍵字：CALL

代碼：

CALL proc_p1();

四、刪除預存程序

代碼：

drop procedure proc_p1;

問題：能否修改預存程序那？

答案是肯定的，但是卻很繁瑣，因此我們一般修改的時候，都是重寫預存程序。

因此，針對修改我們的操作(也是重寫)：

delimiter $$drop procedure proc_p1 $$create procedure proc_p1()BEGIN  select * from part;END $$delimiter ;

代碼

分析：
即先刪除，後寫入。

問題：如果刪除的時候，沒有對應的預存程序那？

答案：會報錯！
因此我們要避險這種情況出現。

delimiter $$drop procedure if EXISTS proc_p1 $$create procedure proc_p1()BEGIN  select * from part;END $$delimiter ;

代碼

分析：
增加了if EXISTS判斷；它表示，如果後面的這個預存程序有則刪除，反之不操作；這樣避免報錯，導致問題。

上面的代碼都是簡單的操作，再看下面的代碼：

 

有參數的預存程序

一、參數 in

delimiter $$drop procedure if EXISTS proc_p1 $$create procedure proc_p1(  in i1 int)BEGINdeclare d1 int;declare d2 int default 2;set d1 = i1 + d2;END $$delimiter ;

in

分析：
in：表示進的，接收參數
declare：表示聲變數。
int：表示變數類型
default：表示設定變數的初始值。
set：表示賦值操作，也可以理解為設定值。

自訂代碼的結束符號

delimiter

drop procedure if EXISTS proc_p1;create procedure proc_p1()BEGINselect * from part;END

上面代碼在Navicat下是能執行的，但是在終端上是要出問題的。
原因就是這個分號；上面的代碼begin和end間代碼，表示一個功能，都是整體傳進去的，但是，代碼在執行的時候，遇到分號；則表示此功能的代碼執行完畢。所以導致後面的代碼不執行，在終端的運行，因為分號的原因，會導致啟動並執行時候出問題，所以，分號要是用在不對的位置，會造成很大的麻煩。

說明：
這個的意思就表示，以後的mysql語句都是以 $$為結束。

注意：

自訂的時候不能將 \\ 設定用來結束；\\ 在終端會報錯！可能是轉義符的原因吧。

驗證上面的說明：

在終端進入mysql用戶端，當我們輸入的代碼不帶分號的時候，結果，會一直處於等待命令的狀態：

 

加上分號：出結果

問題：如果我們這樣改動的話，獲導致全域都受影響，因此我們可以讓這種效果，只在指定範圍有效，離開這個範圍，有還原會之前？

所以，完整的代碼：

delimiter $$drop procedure if EXISTS proc_p1 $$create procedure proc_p1(in i1 int)BEGINdeclare d1 int;declare d1 int;set d1 = i1 + d2;END $$delimiter ;

完整代碼

說明：

我們在end後加上 $$ 表示一個功能的代碼結束，並在最後，又將設定還原會去。還是以分號結尾，這樣其他代碼不受影響

 

下面接著儲存參數in分析

二、參數 out

代碼：

delimiter $$drop procedure if EXISTS proc_p1 $$create procedure proc_p1(in i1 int,out i2 int)BEGINdeclare d2 int default 3;if i1 = 1 thenset i2 = 100 + d2;elseif i1 = 2 THENset i2 = 200 + d2;elseset i2 = 1000 + d2;end if;END $$delimiter ;-- 加入回話變數 @u;call proc_p1(1, @u);select @u;

out

結果：

• 說明：
  @u：預設其值為 none；回話變數，相當於我們在外面建立了一個變數，並將引用傳入 proc_p1函數，這個傳入的引用會被 i2 接收，所以，間接的也相當於在給這個 @u 賦值。
• out：字面意思理解，出；有返回的意思。

三、參數 inout

delimiter $$drop procedure if EXISTS proc_p1 $$create procedure proc_p1(in i1 int,inout ii int,out i2 int)BEGINdeclare d2 int default 3;if i1 = 1 thenset i2 = 100 + d2;set ii = ii + 1;elseif i1 = 2 THENset i2 = 200 + d2;elseset i2 = 1000 + d2;end if;END $$delimiter ;-- 加入回話變數 @u;set @o = 5;call proc_p1(1, @o, @u);select @o,@u;

inout

結果：

分析：
inout：從字面意思理解”進、出”，表示要接收一個帶值的變數，同時函數執行完後，也會將對應的變數返回。

擴充：sql中@變數

帶上一個@叫使用者變數：只要使用者還登入著，這個變數就有效；不管在那個代碼塊中都有效；除非使用者被關閉。

帶上2個@叫全域變數。

注意：使用者變數和函數的局部變數的區別

• 使用者變數：只要使用者還登入著，這個變數就有效；不管在那個代碼塊中都有效；除非使用者被關閉。
• 局部變數：離開函數就沒用了。

python操作資料庫預存程序

#!/usr/bin/env python# -*- coding:utf-8 -*-import pymysqlconn = pymysql.connect(host=‘127.0.0.1‘, port=3306, user=‘root‘, passwd=‘123‘, db=‘t1‘)cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)# 執行預存程序cursor.callproc(‘proc_p1‘, (1,2,3))# 擷取執行完儲存的參數cursor.execute("select @_p1_0,@_p1_1,@_p1_2,@_p1_3")result = cursor.fetchall()conn.commit()cursor.close()conn.close()print(result)

代碼

說明：
cursor.callproc(‘proc_p1’, (1,2,3))：只能給資料庫中的proc_p1儲存方法；根據前面的我們知道資料庫中的proc_p1；只能接收2個參數，所以，即使你傳入了3個也只有2個能用。後面的會自動忽略。

上面的資料庫中的預存程序，沒有SQL語句，只是簡單的，變數操作，但是，如果我們加入SQL操作，那我們在python中怎樣擷取這個SQL語句操作的結果那？

 

擷取預存程序的查詢結果：

cursor.fetchall()

 問題：怎樣擷取變數的傳回值那？

cursor.execute("select @_p1_0”)

分析：
1、首先要用select。
2、一個@_
3、後面在接預存程序的名字。
4、接 _0：表示索引。表示擷取返回第一個值，要想擷取後面的，則依次往後指定，用逗號隔開便可。

cursor.execute("select @_p1_0,@_p1_1,@_p1_2,@_p1_3")

這個擷取獲的值，存放在遊標裡面的，所以，要擷取具體的值，可以通過遊標結合fetchone擷取:

result = cursor.fetchone()

特殊：
雖然預存程序中，in接收參數，但是在python中擷取預存程序資料的時候，in接收傳入的值，會被返回，所以，擷取的第一個值，便是，callproc執行的時候，傳入的第一個參數.

python擷取預存程序分兩步：

• 擷取SQL語句執行的傳回值
• 擷取儲存函數的變數傳回值

 

MySQL預存程序