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

Posted on by

儲存過程和函數就是數據器將一些處理封裝起來，調用

區別

調用儲存過程隻需要使用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

流程控制語句

if

loop (leave 退出當前流程，iterate 跳出本次循環)

while

case

以上就是詳解MySQL中的存儲過程和函數的詳細內容，更多關於MySQL存儲過程 函數的資料請關註WalkonNet其它相關文章！

推薦閱讀：