MySQL優化---儲存過程和儲存函式-1-轉自部落格園

wanghaitao4j發表於2018-10-11

轉自網際網路.mysql優化

  當一個大型系統在建立時,會發現,很多的SQL操作是有重疊的,個別計算是相同的,比如:業務系統中,計算一張工單的計算方式。當遇到這些情況時,我們運用儲存過程就是一個非常棒的優化啦。那麼,什麼是儲存 過程和儲存函式呢?

一、MYSQL儲存過程簡介(技術文):

儲存過程是一個可程式設計的函式,它在資料庫中建立並儲存。它可以有SQL語句和一些特殊的控制結構組成。當希望在不同的應用程式或平臺上執行相同的函式,或者封裝特定功能時,儲存過程是非常有用的。資料庫中的儲存過程可以看做是對程式設計中物件導向方法的模擬。它允許控制資料的訪問方式。儲存過程通常有以下優點:

1)儲存過程能實現較快的執行速度。

如果某一操作包含大量的Transaction-SQL程式碼或分別被多次執行,那麼儲存過程要比批處理的執行速度快很多。因為儲存過程是預編譯的。在首次執行一個儲存過程時查詢,優化器對其進行分析優化,並且給出最終被儲存在系統表中的執行計劃。而批處理的Transaction-SQL語句在每次執行時都要進行編譯和優化,速度相對要慢一些。

心得:編譯優化,快!

2)儲存過程允許標準元件是程式設計。

儲存過程被建立後,可以在程式中被多次呼叫,而不必重新編寫該儲存過程的SQL語句。而且資料庫專業人員可以隨時對儲存過程進行修改,對應用程式原始碼毫無影響。

心得:封裝與抽象,簡單呼叫

3)儲存過程可以用流控制語句編寫,有很強的靈活性,可以完成複雜的判斷和較複雜的運算。

心得:功能強大,邏輯強大

4)儲存過程可被作為一種安全機制來充分利用。

系統管理員通過執行某一儲存過程的許可權進行限制,能夠實現對相應的資料的訪問許可權的限制,避免了非授權使用者對資料的訪問,保證了資料的安全。

心得:限制與安全

5)儲存過程能過減少網路流量。

針對同一個資料庫物件的操作(如查詢、修改),如果這一操作所涉及的Transaction-SQL語句被組織程儲存過程,那麼當在客戶計算機上呼叫該儲存過程時,網路中傳送的只是該呼叫語句,從而大大增加了網路流量並降低了網路負載。

心得:減少網路流量(封裝的好)

二、那儲存函式(自定義函式)又是什麼呢?:

封裝一段sql程式碼,完成一種特定的功能,必須返回結果。其餘特性基本跟儲存過程相同。

三、儲存函式與儲存過程的區別(技術文):

1) 儲存函式有且只有一個返回值,而儲存過程不能有返回值。就是說能不能使用return。(函式可返回返回值或者表物件,絕對不能返回結果集)

wps4C05.tmp

2) 函式只能有輸入引數,而且不能帶in, 而儲存過程可以有多個in,out,inout引數。

3) 儲存過程中的語句功能更強大,儲存過程可以實現很複雜的業務邏輯,而函式有很多限制,如不能在函式中使用insert,update,delete,create等語句;儲存函式只完成查詢的工作,可接受輸入引數並返回一個結果,也就是函式實現的功能針對性比較強。比如:工期計算、價格計算。

4)儲存過程可以呼叫儲存函式。但函式不能呼叫儲存過程。

5)儲存過程一般是作為一個獨立的部分來執行(call呼叫)。而函式可以作為查詢語句的一個部分來呼叫。

四、MySQL 建立一個最簡單的儲存過程(技術文):

“pr_add” 是個簡單的 MySQL 儲存過程,這個儲存過程有兩個 int 型別的輸入引數 “a”、“b”,返回這兩個引數的和。

wps4C06.tmp

五、MySQL 儲存過程特點(技術文):

建立 MySQL 儲存過程的簡單語法為:

create procedure 儲存過程名字()

(

[in|out|inout] 引數 datatype

)

begin

MySQL 語句;

end;

MySQL 儲存過程引數如果不顯式指定“in”、“out”、“inout”,則預設為“in”。習慣上,對於是“in” 的引數,我們都不會顯式指定。

1 MySQL 儲存過程名字後面的“()”是必須的,即使沒有一個引數,也需要“()”

2 MySQL 儲存過程引數,不能在引數名稱前加“@”,如:“@a int”。下面的建立儲存過程語法在 MySQL 中是錯誤的(在 SQL Server 中是正確的)。 MySQL 儲存過程中的變數,不需要在變數名字前加“@”,雖然 MySQL 客戶端使用者變數要加個“@”。

create procedure pr_add( @a int,// 錯誤 b int //正確)

3 MySQL 儲存過程的引數不能指定預設值。

4 MySQL 儲存過程不需要在 procedure body 前面加 “as”。而 SQL Server 儲存過程必須加 “as” 關鍵字。

create procedure pr_add( a int, b int)as - 錯誤,MySQL 不需要 “as”begin mysql statement ...;end;

如果 MySQL 儲存過程中包含多條 MySQL 語句,則需要 begin end 關鍵字。

create procedure pr_add( a int, b int)begin mysql statement 1 ...; mysql statement 2 ...;end;

6 MySQL 儲存過程中的每條語句的末尾,都要加上分號 “;”

... declare c int; if a is null then set a = 0; end if; ...end;

7 不能在 MySQL 儲存過程中使用 “return” 關鍵字。

set c = a + b;select c as sum; /* return c;- 不能在 MySQL 儲存過程中使用。return 只能出現在函式中。 */end;

8 呼叫 MySQL 儲存過程時候,需要在過程名字後面加“()”,即使沒有一個引數,也需要“()”,呼叫out及inout引數格式為@arguments_name形式。

call pr_no_param();

9 因為 MySQL 儲存過程引數沒有預設值,所以在呼叫 MySQL 儲存過程時候,不能省略引數。可以用 null 來替代。

call pr_add(10, null);

1,實戰前提(技術文):

需要MySQL 5及以上 ,我用的是MYSQL的客戶端Navicat Premium,貼出的程式碼都是我編譯沒有錯誤的。如果讀者沒有安裝客戶端或者在你的電腦上報錯,這裡需要用到是DELIMITER //和DELIMITER ;兩句,DELIMITER是分割符的意思,因為MySQL預設以”;”為分隔符,如果我們沒有宣告分割符,那麼編譯器會把儲存過程當成SQL語句進行處理,則儲存過程的編譯過程會報錯,所以要事先用DELIMITER關鍵字申明當前段分隔符,這樣MySQL才會將”;”當做儲存過程中的程式碼,不會執行這些程式碼,用完了之後要把分隔符還原

2,變數

使用DECLARE來宣告,DEFAULT賦預設值,SET賦值

Java程式碼 複製程式碼

DECLARE counter INT DEFAULT 0; SET counter = counter+1;

3,條件判斷

IF THEN、ELSEIF、ELSE、END IF

DROP PROCEDURE IF EXISTS discounted_price; CREATE PROCEDURE discounted_price(normal_price NUMERIC(8, 2), OUT discount_price NUMERIC(8, 2)) BEGIN IF (normal_price > 500) THEN SET discount_price = normal_price * 0.8; ELSEIF (normal_price > 100 and normal_price<=500) THEN SET discount_price = normal_price * 0.9; ELSE SET discount_price = normal_price; END IF; select discount_price as price; END;call discounted_price(600.0,@discount);//out引數呼叫時可以用@任意字串

4,迴圈

LOOP、END LOOP

drop procedure if exists simple_loop;create procedure simple_loop(out counter int)BEGIN declare temp int default 0; set counter=0; my_loop:LOOP set counter=counter+1; set temp=temp+1; if counter=10 THEN leave my_loop; end if; end loop my_loop; select temp as result;end;call simple_loop(@a);

5、WHILE DO、END WHILE

DROP PROCEDURE IF EXISTS simple_while; CREATE PROCEDURE simple_while(OUT counter INT) BEGIN declare temp int default 0; SET counter =0; WHILE counter != 10 DO SET counter =counter+1; set temp =temp+1; END WHILE; select counter as temp1; END; call simple_while(@a);

6、REPEAT、UNTILL

drop PROCEDURE if exists simple_repeat;create procedure simple_repeat(out counter int)BEGIN set counter=0; REPEAT set counter=counter+1; until counter=10 end repeat; select counter as temp;end;call simple_repeat(@q);

7,儲存方法

儲存方法與儲存過程的區別

1,儲存方法的引數列表只允許IN型別的引數,而且沒必要也不允許指定IN關鍵字

2,儲存方法返回一個單一的值,值的型別在儲存方法的頭部定義

3,儲存方法可以在SQL語句內部呼叫

4,儲存方法不能返回結果集

語法:

create function 函式([函式引數[,….]]) Returns 返回型別

Begin

If

Return (返回的資料)

Else

Return (返回的資料)

end if;

end;

一個簡單的儲存函式例項

drop function if exists purchase_and_redeem_function;

CREATE function purchase_and_redeem_function(date int)

returns varchar(80)

BEGIN

return (SELECT tbalance FROM user_purchase_and_redeem WHERE report_date=date); //這裡面的SQL語句根據自己資料庫表編寫

END;

select purchase_and_redeem_function(20140501);//這是呼叫儲存函式

8,觸發器

觸發器在INSERT、UPDATE或DELETE等DML語句修改資料庫表時觸發

觸發器的典型應用場景是重要的業務邏輯、提高效能、監控表的修改等

觸發器可以在DML語句執行前或後觸發

DROP TRIGGER sales_trigger;

CREATE TRIGGER sales_trigger

BEFORE INSERT ON salesFOR EACH ROW

BEGIN

IF NEW.sale_value > 500 THEN

SET NEW.free_shipping = 'Y';

ELSE

SET NEW.free_shipping = 'N';

END IF;

IF NEW.sale_value > 1000 THEN

SET NEW.discount = NEW.sale_value * .15;

ELSE

SET NEW.discount = 0;END IF;

END;

相關文章