詳解MySQL中的儲存過程和函數

2022-08-11 10:00:03

區別

呼叫儲存過程只需要使用CALL，然後指定儲存名稱和引數，引數可以是IN、OUT和INOUT

呼叫儲存函數只需要使用SELECT，指定名稱和引數，儲存函數必須有返回值，引數只能是IN

優點

良好的封裝性
應用程式和SQL邏輯分離
讓SQL也具有處理能力
減少網路互動
能夠提高系統效能
降低資料出錯的概率，保證資料的一致性和完整性
保證資料的安全性

建立儲存函數和過程

儲存過程

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

create procedure 建立儲存過程關鍵字

sp_name 儲存過程名稱

proc_parameter 引數

characteristic 約束

routine_body 執行體，使用BEGIN— END包括

proc_parameter

IN | OUT | INOUT param_name type

characteristic

language SQL 執行體

[not] deterministic 得出的結果是否確定，不帶NOT 出入相同，預設帶NOT

constains SQL 不包含讀寫SQL , no sql 不包含sql語句， reads sql data 讀資料的sql, modifies sql data 包含讀sql語句，預設contains sql

sql security definer 只有建立者菜能執行 invoker 表示有許可權存取的就可執行

comment 註釋

下面是建立一個儲存過程的定義，在對應的工具中找到建立儲存過程的地方。

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

建立好的儲存過程

儲存函數

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

create function 建立儲存函數關鍵字

func_name 儲存函數名字

func_parameter 引數，儲存函數只能是IN

returns type 返回資料型別

characteristic 函數約束

routine_body SQL執行體

檢視儲存過程

show create procedure sp_name

show procedure status like ‘’

從資料庫中information_schema中查詢

操作

call 呼叫

drop 刪除

alter 修改

變數

declare var_name[,…] type [default value]

declare 定義變數關鍵字

var_name 變數名稱

type 型別

[default value] 預設值

declare totalprice decimal(10,2) default 0.00;

賦值

set 賦值

set totalprice = 399.99

查詢賦值

select sum(t_price) into totalprice from t_goods

變數例子

建立一個儲存過程使用變數的例子

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 數值型別的錯誤程式碼

declare exec_refused condition for sqlstate ‘48000’;

處理程式

declare handler_type handler for condition_value[,…] sq_statement

handler_type 定義錯誤的處理方式

condition_value 錯誤型別

sq_statement 遇到錯誤，需要執行的儲存過程或函數

handler_type

continue 繼續處理

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 數值型別錯誤

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

遊標

儲存過程查詢資料打，使用遊標對結果集迴圈處理。

宣告遊標

declare cursor_name cursor for select_statement;

cursor_name 遊標名稱

select_statement 查詢語句

開啟遊標

open cursor_name

使用遊標

fetch cursor_name into var_name[,…]

關閉遊標

close cursor_name

例子

定義一個StatisticsPrice的儲存過程，引數是totalprice，定義cursor_price 遊標，將查詢的結果使用repeat 語句賦值於cursor_price，計算結果。

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