儲存過程和函數(shù)就是數(shù)據(jù)器將一些處理封裝起來，調(diào)用

區(qū)別

調(diào)用儲存過程只需要使用CALL，然后指定儲存名稱和參數(shù)，參數(shù)可以是IN、OUT和INOUT

調(diào)用儲存函數(shù)只需要使用SELECT，指定名稱和參數(shù)，儲存函數(shù)必須有返回值，參數(shù)只能是IN

優(yōu)點

• 良好的封裝性
• 應(yīng)用程序和SQL邏輯分離
• 讓SQL也具有處理能力
• 減少網(wǎng)絡(luò)交互
• 能夠提高系統(tǒng)性能
• 降低數(shù)據(jù)出錯的概率，保證數(shù)據(jù)的一致性和完整性
• 保證數(shù)據(jù)的安全性

創(chuàng)建儲存函數(shù)和過程

儲存過程

create procedure sp_name ([proc_parameter[,…]]) [characteristic …] routine_body

create procedure 創(chuàng)建儲存過程關(guān)鍵字

sp_name 儲存過程名稱

proc_parameter 參數(shù)

characteristic 約束

routine_body 執(zhí)行體，使用BEGIN— END包括

proc_parameter

IN | OUT | INOUT param_name type

characteristic

language SQL 執(zhí)行體

[not] deterministic 得出的結(jié)果是否確定，不帶NOT 出入相同，默認(rèn)帶NOT

constains SQL 不包含讀寫SQL , no sql 不包含sql語句， reads sql data 讀數(shù)據(jù)的sql, modifies sql data 包含讀sql語句， 默認(rèn)contains sql

sql security definer 只有創(chuàng)建者菜能執(zhí)行 invoker 表示有權(quán)限訪問的就可執(zhí)行

comment 注釋

下面是創(chuàng)建一個儲存過程的定義，在對應(yīng)的工具中找到創(chuàng)建儲存過程的地方。

create PROCEDURE SelectAllData()
begin
select * from t_goods;
end 

創(chuàng)建好的儲存過程

儲存函數(shù)

create function func_name (func_parameter[,…]) returns type [characteristic …] routine_body

create function 創(chuàng)建儲存函數(shù)關(guān)鍵字

func_name 儲存函數(shù)名字

func_parameter 參數(shù)，儲存函數(shù)只能是IN

returns type 返回數(shù)據(jù)類型

characteristic 函數(shù)約束

routine_body SQL執(zhí)行體

查看儲存過程

show create procedure sp_name

show procedure status like ‘’

從數(shù)據(jù)庫中information_schema中查詢

操作

call 調(diào)用

drop 刪除

alter 修改

變量

declare var_name[,…] type [default value]

declare 定義變量關(guān)鍵字

var_name 變量名稱

type 類型

[default value] 默認(rèn)值

declare totalprice decimal(10,2) default 0.00;

賦值

set 賦值

set totalprice = 399.99

查詢賦值

select sum(t_price) into totalprice from t_goods

變量例子

創(chuàng)建一個儲存過程使用變量的例子

CREATE DEFINER=`root`@`localhost` PROCEDURE `SelectCountAndPrice`()
begin
declare totalcount int default 0;
declare totalprice, avgprice decimal(10, 2) default 0.00;
select count(*) into totalcount from t_goods;
select sum(t_price) totalprice from t_goods;
set avgprice = totalprice / totalcount;
select totalprice,totalcount,avgprice;
end

定義條件和處理過程

條件

declare condition_name condition for condition_value

condition_name 條件名稱

condition_value 條件類型

SQLSTATE [value] sqlstate_value | mysql_error_code

sqlstate_value 長度為5的錯誤信息

mysql_error_code 數(shù)值類型的錯誤代碼

declare exec_refused condition for sqlstate ‘48000’;

處理程序

declare handler_type handler for condition_value[,…] sq_statement

handler_type 定義錯誤的處理方式

condition_value 錯誤類型

sq_statement 遇到錯誤，需要執(zhí)行的儲存過程或函數(shù)

handler_type

continue 繼續(xù)處理

exit 退出

undo 撤回，目前不支持

condition_value

sqlstate [value] sqlstate_value

condition_name

sqlwarning

not found

sqlexception

mysql_error_code

• sqlstate [value] sqlstate_value 長度為5的字符串的錯誤信息
• condition_name 條件名稱
• sqlwarning 所有以01開頭的sqlstate錯誤代碼
• not found 所有以02開頭的sqlstate錯誤代碼
• sqlexception 沒有被sqlwarning和not found 捕捉的錯誤代碼
• mysql_error_code 數(shù)值類型錯誤

declare continue handler for sqlstate ‘29011’ set @log=’ database not found ’

CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertDataNoCondition`()
BEGIN
    set @x = 1;
    insert into t_goodss (id,t_cate,t_remake,ages) values (3,'22','3333',10);
    set @x = 2;
    insert into t_goodss (id,t_cate,t_remake,ages) values (4,'22','3333',10);
    set @x = 3;
END

游標(biāo)

儲存過程查詢數(shù)據(jù)打，使用游標(biāo)對結(jié)果集循環(huán)處理。

聲明游標(biāo)

declare cursor_name cursor for select_statement;

cursor_name 游標(biāo)名稱

select_statement 查詢語句

打開游標(biāo)

open cursor_name

使用游標(biāo)

fetch cursor_name into var_name[,…]

關(guān)閉游標(biāo)

close cursor_name

例子

定義一個StatisticsPrice的儲存過程，參數(shù)是totalprice，定義cursor_price 游標(biāo)，將查詢的結(jié)果使用repeat 語句賦值于cursor_price，計算結(jié)果。

CREATE  PROCEDURE StatisticsPrice(OUT totalprice DECIMAL(10, 2))
BEGIN
    #Routine body goes here...
    declare price decimal(10,2) DEFAULT 0.00;
    declare cursor_price cursor for select t_price from t_goods;
    declare exit HANDLER FOr not found close cursor_price;
    set totalprice = 0.00;
    open cursor_price;
    repeat
  FETCH cursor_price into price;
    set totalprice= totalprice + price;
    until 0 END repeat;
    close cursor_price;
END

流程控制語句

if

loop (leave 退出當(dāng)前流程，iterate 跳出本次循環(huán))

while

case

以上就是詳解MySQL中的存儲過程和函數(shù)的詳細(xì)內(nèi)容，更多關(guān)于MySQL存儲過程 函數(shù)的資料請關(guān)注腳本之家其它相關(guān)文章！

最新評論