資料庫：mysql內建功能-預存程序

標籤：cursor   cut   pytho   功能   語句   name   基於   key   缺點   

一 預存程序介紹

預存程序包含了一系列可執行檔sql語句，預存程序存放於MySQL中，通過調用它的名字可以執行其內部的一堆sql

使用預存程序的優點：

#1. 用於替代程式寫的SQL語句，實現程式與sql解耦#2. 基於網路傳輸，傳別名的資料量小，而直接傳sql資料量大

使用預存程序的缺點：

#1. 程式員擴充功能不方便

　

補充：程式與資料庫結合使用的三種方式

　

#方式一：    MySQL：預存程序    程式：調用預存程序#方式二：    MySQL：    程式：純SQL語句#方式三：    MySQL:    程式：類和對象，即ORM（本質還是純SQL語句）

　　

二 建立簡單預存程序（無參）

先建立表：mysql> create table blog( id int primary key auto_increment,    -> name varchar(20),    -> sub_time date);建立預存程序delimiter //create procedure p1()BEGINselect * from blog;INSERT INTO blog(name,sub_time) VALUES ("測試", NOW());END //delimiter;

　

#在mysql中調用call p1() #在python中基於pymysql調用cursor.callproc(‘p1‘) print(cursor.fetchall())

　　

三 建立預存程序（有參）

對於預存程序，可以接收參數，其參數有三類# in        僅用於傳入參數用# out        僅用於返回參數用# inout        既可以傳入又可以當作傳回值

　　

delimiter //create procedure p2(    in n1 int,    in n2 int)BEGIN    select * from blog where id > n1;END //delimiter ;#在mysql中調用call p2(3,2)#在python中基於pymysql調用cursor.callproc(‘p2‘,(3,2))print(cursor.fetchall())

　　

delimiter //create procedure p3(    in n1 int,    out res int)BEGIN    select * from blog where id > n1;    set res = 1;END //delimiter ;#在mysql中調用set @res=0; #0代表假（執行失敗），1代表真（執行成功）call p3(3,@res);select @res;#在python中基於pymysql調用cursor.callproc(‘p3‘,(3,0)) #0相當於set @res=0print(cursor.fetchall()) #查詢select的查詢結果cursor.execute(‘select @_p3_0,@_p3_1;‘) #@p3_0代表第一個參數，@p3_1代表第二個參數，即傳回值print(cursor.fetchall())

　　

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())

　　

四 執行預存程序

　

-- 無參數call proc_name()-- 有參數，全incall proc_name(1,2)-- 有參數，有in,out,inoutset @t1=0;set @t2=3;call proc_name(1,2,@t1,@t2)執行預存程序在MySQL中執行預存程序-- 無參數call proc_name()-- 有參數，全incall proc_name(1,2)-- 有參數，有in，out，inoutset @t1=0;set @t2=3;call proc_name(1,2,@t1,@t2)

　　

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(‘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內建功能-預存程序