腳本之家服務(wù)器常用軟件

快捷導(dǎo)航

詳解MySQL中的存儲(chǔ)過(guò)程和函數(shù)

更新時(shí)間：2022年08月11日 08:33:54 作者：我的天才女友

這篇文章主要為大家詳細(xì)介紹了MySQL數(shù)據(jù)庫(kù)中的存儲(chǔ)過(guò)程和函數(shù)的相關(guān)操作，文中的示例代碼講解詳細(xì)，感興趣的小伙伴可以跟隨小編一起了解一下

區(qū)別

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

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

優(yōu)點(diǎn)

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

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

儲(chǔ)存過(guò)程

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

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

sp_name 儲(chǔ)存過(guò)程名稱(chēng)

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 不包含讀寫(xiě)SQL , no sql 不包含sql語(yǔ)句， reads sql data 讀數(shù)據(jù)的sql, modifies sql data 包含讀sql語(yǔ)句，默認(rèn)contains sql

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

comment 注釋

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

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

創(chuàng)建好的儲(chǔ)存過(guò)程

儲(chǔ)存函數(shù)

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

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

func_name 儲(chǔ)存函數(shù)名字

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

returns type 返回?cái)?shù)據(jù)類(lèi)型

characteristic 函數(shù)約束

routine_body SQL執(zhí)行體

查看儲(chǔ)存過(guò)程

show create procedure sp_name

show procedure status like ‘’

從數(shù)據(jù)庫(kù)中information_schema中查詢(xún)

操作

call 調(diào)用

drop 刪除

alter 修改

變量

declare var_name[,…] type [default value]

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

var_name 變量名稱(chēng)

type 類(lèi)型

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

declare totalprice decimal(10,2) default 0.00;

賦值

set 賦值

set totalprice = 399.99

查詢(xún)賦值

select sum(t_price) into totalprice from t_goods

變量例子

創(chuàng)建一個(gè)儲(chǔ)存過(guò)程使用變量的例子

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

定義條件和處理過(guò)程

條件

declare condition_name condition for condition_value

condition_name 條件名稱(chēng)

condition_value 條件類(lèi)型

SQLSTATE [value] sqlstate_value | mysql_error_code

sqlstate_value 長(zhǎng)度為5的錯(cuò)誤信息

mysql_error_code 數(shù)值類(lèi)型的錯(cuò)誤代碼

declare exec_refused condition for sqlstate ‘48000’;

處理程序

declare handler_type handler for condition_value[,…] sq_statement

handler_type 定義錯(cuò)誤的處理方式

condition_value 錯(cuò)誤類(lèi)型

sq_statement 遇到錯(cuò)誤，需要執(zhí)行的儲(chǔ)存過(guò)程或函數(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 長(zhǎng)度為5的字符串的錯(cuò)誤信息
condition_name 條件名稱(chēng)
sqlwarning 所有以01開(kāi)頭的sqlstate錯(cuò)誤代碼
not found 所有以02開(kāi)頭的sqlstate錯(cuò)誤代碼
sqlexception 沒(méi)有被sqlwarning和not found 捕捉的錯(cuò)誤代碼
mysql_error_code 數(shù)值類(lèi)型錯(cuò)誤

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)

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

聲明游標(biāo)

declare cursor_name cursor for select_statement;

cursor_name 游標(biāo)名稱(chēng)

select_statement 查詢(xún)語(yǔ)句

打開(kāi)游標(biāo)

open cursor_name

使用游標(biāo)

fetch cursor_name into var_name[,…]

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

close cursor_name

例子

定義一個(gè)StatisticsPrice的儲(chǔ)存過(guò)程，參數(shù)是totalprice，定義cursor_price 游標(biāo)，將查詢(xún)的結(jié)果使用repeat 語(yǔ)句賦值于cursor_price，計(jì)算結(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