數(shù)據(jù)庫對象表時存儲和操作數(shù)據(jù)的邏輯結(jié)構(gòu)，而數(shù)據(jù)庫對象存儲過程和函數(shù)，則是用來實現(xiàn)將一組關(guān)于表操作的sql語句當(dāng)作一個整體來執(zhí)行。在數(shù)據(jù)庫系統(tǒng)中，當(dāng)調(diào)用存儲過程和函數(shù)時，則會執(zhí)行這些對象中所設(shè)置的sql語句組，從而實現(xiàn)相應(yīng)功能。
1. 為什么使用存儲過程和函數(shù)的操作 
    有時針對表的一個完整操作往往不是單條sql語句就可以實現(xiàn)的，而是需要一組sql語句來實現(xiàn)。在具體應(yīng)用當(dāng)中，一個完整的操作會包含多條sql語句，在執(zhí)行過程中需要根據(jù)前面sql語句的執(zhí)行結(jié)果有選擇地執(zhí)行后面sql語句。
    存儲過程和函數(shù)可以簡單理解為一條或多條sql語句的集合。存儲過程和函數(shù)就是事先經(jīng)過編譯并存儲在數(shù)據(jù)庫中的一段sql語句集合。
    存儲過程和函數(shù)有什么區(qū)別呢？這兩者的主要區(qū)別在于函數(shù)必須有返回值，而存儲過程則沒有。存儲過程的參數(shù)類型遠(yuǎn)遠(yuǎn)多于函數(shù)的參數(shù)類型。 

關(guān)于存儲過程和函數(shù)的優(yōu)點(diǎn)如下：
      1. 存儲過程和函數(shù)允許標(biāo)準(zhǔn)組件式編程，提高了sql語句的重用性、共享性和可移植性。
      2. 存儲過程和函數(shù)能夠?qū)崿F(xiàn)較快的執(zhí)行速度，能夠減少網(wǎng)絡(luò)流量。
      3. 存儲過程和函數(shù)可以作為一種安全機(jī)制來利用。 

關(guān)于存儲過程和函數(shù)的缺點(diǎn)如下：
      1. 存儲過程和函數(shù)的編寫比單句sql語句復(fù)雜，需要用戶有更高的技能和更豐富的經(jīng)驗。
      2. 在編寫存儲過程和函數(shù)時，需要創(chuàng)建這些數(shù)據(jù)庫對象的權(quán)限。=

2. 創(chuàng)建存儲過程和函數(shù) 
2.1 創(chuàng)建存儲過程語法形式：

語法形式如下：

create procedure procedure_name([procedure_parameter[,...]])
  [characteristic...] routine_body

//說明：procedure_name參數(shù)表示所要創(chuàng)建的存儲過程的名字，procedure_parameter參數(shù)表示存儲過程的參數(shù)，
characteristic參數(shù)表示存儲過程的特性，routine_body參數(shù)表示存儲過程的sql語句代碼，可以用begin...end來標(biāo)志sql語句的開始和結(jié)束。
//注意：在具體創(chuàng)建存儲過程時，存儲過程名不能和已經(jīng)存在的存儲過程名重復(fù)，推薦存儲過程名為procedure_xxx或者proce_xxx;

//procedure_parameter 中每個參數(shù)的語法形式為：
[IN|OUT|INOUT] parameter_name type

//該語句中每個參數(shù)由三部分組成，分別為輸入/輸出類型、參數(shù)名和參數(shù)類型。

characteristic參數(shù)的取值為：
language sql
|[not] deterministic
|{constains sql | no sql | reads sql data|modifies sql data}
|sql security {definer | invoker}
|comment 'string'

    1. language sql，表示存儲過程的routine_body部分由sql語言的語句組成。為mysql軟件所有默認(rèn)的語句。
    2. [not] deterministic，表示存儲過程的執(zhí)行結(jié)果是否確定。如果值是deterministic表示執(zhí)行結(jié)果是確定的。即每次執(zhí)行存儲過程時，如果輸入相同的參數(shù)將得到相同的輸出；如果值為not deterministic，表示執(zhí)行結(jié)果不確定，即相同的輸入可能得到不同的輸出。默認(rèn)值為deterministic。
    3. {contains sql|no sql|reads sql data|modifies sql data}，表示sql語句的限制，如果值為contains sql表示可以包含sql語句，但不包含讀或?qū)憯?shù)據(jù)的語句；如果值為no sql表示不包含sql語句；如果值為reads sql data表示包含讀數(shù)據(jù)的語句；如果值為modifies sql data表示包含讀數(shù)據(jù)的語句。默認(rèn)值為contains sql。
    4. sql security{definer|invoker}，設(shè)置誰有權(quán)限來執(zhí)行。如果值為definer，表示只有定義者才能執(zhí)行，如果值為invoker表示調(diào)用者可以執(zhí)行。默認(rèn)值為definer。
    5. comment ‘string', 表示注釋語句。 

2.2 創(chuàng)建函數(shù)語法形式：

語法形式如下：

create function function_name([function_parameter[,...]])
  [characteristic...] routine_body

    上述語句中，function_name參數(shù)表示所要創(chuàng)建的函數(shù)的名字；function_parameter參數(shù)表示函數(shù)的參數(shù)，characteristic參數(shù)表示函數(shù)的特性，該參數(shù)的取值與存儲過程中的取值相同。routine_body參數(shù)表示函數(shù)的sql語句代碼，可以用begin…end來表示sql語句的開始和結(jié)束。

function_parameter中每個參數(shù)的語法形式如下：
parameter_name type

    在上述語句中每個參數(shù)由兩部分組成，分別為參數(shù)名和參數(shù)類型。parameter_name表示參數(shù)名。type表示參數(shù)類型。 

2.3 創(chuàng)建簡單的存儲過程和函數(shù)：

//查詢雇員表中所有雇員工資的存儲過程：
示例：

mysql> delimiter $$
mysql> delimiter $$ create procedure proce_employee_sal() 
    comment '查詢所有雇員的工資' 
    begin 
     select sal from t_employee;
    end $$
    dilimiter ;

    通常在創(chuàng)建存儲過程時，通過命令delimiter && 將sql語句的結(jié)束符由“；”符號修改成兩個美元符號。這主要是因為sql語句中默認(rèn)語句結(jié)束符為分好（；）,即存儲過程中的sql語句也需要用分號來結(jié)束，將結(jié)束符號修改成兩個美元符之后，就可以在執(zhí)行過程中避免沖突。不過最后不要忘記將通過命令“delimiter ;”將結(jié)束符修改為sql語句中默認(rèn)的結(jié)束符號。

創(chuàng)建函數(shù)示例：

delimiter $$
create function func_employee_sal (empno int(11))
 returns double(10,2)
 comment '查詢某個雇員的工資'
 begin
 return (
  select sal from t_employee where t_employee.empno=empno;
 )
end$$
delimiter ;

    創(chuàng)建了一個名為func_employee_sal的函數(shù)，該函數(shù)擁有一個類型為int(11)，名為empno的參數(shù)，返回值為double(10,2)類型。select語句從t_employee表中查詢empnoo字段值等于所傳入?yún)?shù)empno值的記錄，同時將該條記錄的sal字段的值返回。

3. 關(guān)于存儲過程和函數(shù)的表達(dá)式 

3.1 操作變量：
    變量是表達(dá)式語句中最基本的元素，可以用來臨時存儲數(shù)據(jù)?？梢酝ㄟ^變量存儲從表中查詢到的數(shù)據(jù)。 

    3.1.1 聲明變量：

語法形式如下：
declare var_name[,...] type [default value]

    在上述語句中，var_name參數(shù)表示要聲明的變量的名字；參數(shù)type表示所要聲明變量的類型；default value用來實現(xiàn)設(shè)置變量的默認(rèn)值，如果無該語句默認(rèn)值為null。在具體聲明變量時，可以同時定義多個變量。 

    3.1.2 賦值變量：

語法形式如下：
語法一：
set var_name=expr[,...]
語法二：

select filed_name[,...] into var_name[,...]
  from table_name
    where condition

    var_name參數(shù)表示所要賦值變量名字，參數(shù)expr是關(guān)于變量的賦值表達(dá)式。在為變量賦值時，可以同時為多個變量賦值，各個變量的賦值語句之間用逗號隔開。
語法二中將查詢到的結(jié)果賦值給變量，參數(shù)filed_name表示查詢的字段名，參數(shù)var_name表示變量名。將查詢結(jié)果賦值給變量，該查詢語句的返回結(jié)果只能是單行。

示例：

declare employee_sal int default 1000;

declare employee_sal int default 1000;
set employee_sal = 3500;

select sal into employee_sal from t_employee where empno=7556;

3.2 操作條件：
    3.2.1 定義條件：

語法形式如下：

declare condition_name condition for condition_value
condition_value:
  sqlstate[value] sqlstate_value
  |mysql_error_code

condition_name參數(shù)表示所要定義的條件名稱；參數(shù)condition_value用來實現(xiàn)設(shè)置條件的類型；參數(shù)sqlstate_value和mysql_error_code用來設(shè)置條件的錯誤。 

    3.2.2 定義處理程序：

語法形式為：

declare handler_type handler for condition_value[,...] sp_statement
handler_type:
  continue
  |exit
  |undo
condition_value:
  sqlstate[value] sqlstate_value
  |condition_name
  |sqlwarning
  |not found
  |sqlexception
  |mysql_error_code

    這個語句指定每個可以處理一個或多個條件的處理程序。如果產(chǎn)生一個或多個條件，指定的語句被執(zhí)行。對一個continue處理程序，當(dāng)前子程序的執(zhí)行處理程序語句之后繼續(xù)。對于exit處理程序，當(dāng)前begin…end復(fù)合語句的執(zhí)行被終止。undo處理程序類型語句還不被支持。
1. sqlwarning是對所有以01開頭的sqlstate代碼的速記。
2. not found是對所有以02開頭的sqlstate代碼的速記。
3. sqlexception 是對所有沒有被sqlwarning或not found捕獲的sqlstate代碼的速記。 

3.3 使用游標(biāo)：
    mysql的查詢語句可以返回多條記錄結(jié)果，那么在表達(dá)式中如何遍歷這些記錄結(jié)果呢？mysql提供了游標(biāo)來實現(xiàn)。通過指定由select語句返回的行集合（包括滿足該語句的where子句所列條件的所有行），由該語句返回完整的行集合叫結(jié)果集。應(yīng)用程序需要一種機(jī)制來一次處理結(jié)果集中的一行或連續(xù)的幾行，而游標(biāo)通過每次指定一條記錄完成與應(yīng)用程序的交互。
    游標(biāo)可以看做一種數(shù)據(jù)類型，可以用來遍歷結(jié)果集，相當(dāng)是指針或數(shù)組的下標(biāo)。處理結(jié)果集的方法可以通過游標(biāo)定位到結(jié)果集的某一行，從當(dāng)前結(jié)果集的位置搜索一行或者一部分行或者結(jié)果集中的當(dāng)前行進(jìn)行數(shù)據(jù)修改。

    3.3.1 聲明游標(biāo)：

語法形式如下：
declare cursor_name cursor for select_statement;

    上述語句中，cursor_name參數(shù)表示有游標(biāo)的名稱，參數(shù)select_statement表示select語句。因為游標(biāo)需要遍歷結(jié)果集中的每一行，增加了服務(wù)器的負(fù)擔(dān)，導(dǎo)致游標(biāo)的效率并不高。如果游標(biāo)操作的數(shù)據(jù)超過1萬行，那么應(yīng)該采用其他方式，另外如果使用了游標(biāo)，還應(yīng)盡量避免在游標(biāo)循環(huán)中進(jìn)行表連接操作。 

    3.3.2 打開游標(biāo)：

語法形式為：
open cursor_name

//注意，打開一個游標(biāo)時，游標(biāo)并不指向第一條記錄，而是指向第一條記錄的前邊。

    3.3.3 使用游標(biāo)：

語法形式如下：
fetch cursor_name into var_name [,var_name] ...

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

語法形式如下：
close cursor_name

4. 修改存儲過程和函數(shù) 
    對于已經(jīng)創(chuàng)建好的存儲過程和函數(shù)，當(dāng)使用一段時間后，就會需要進(jìn)行一些定義上的修改。可以通過alter procedure語句實現(xiàn)修改存儲過程，通過alter function語句實現(xiàn)修改函數(shù)。
  4.1 修改存儲過程：

語法形式如下：

alter procedure procedure_name
  [characteristic...]

    procedure_name參數(shù)表示所要修改存儲過程的名字，而characteristic參數(shù)指定修改后存儲過程的特性，與定義存儲過程的該參數(shù)相比，取值只能是如下值：

|(contains sql|no sql|reads sql data|modifys sql data) 
|sql security {definer|invoker} 
|comment ‘string' 
） 

  4.2 修改函數(shù)：

語法形式如下：

alter function function_name
  [characteristic...]

    function_name參數(shù)表示所要修改函數(shù)的名字，而characteristic參數(shù)指定修改后的函數(shù)特性，與定義函數(shù)的該參數(shù)相比，取值只能是如下值：
|(contains sql|no sql|reads sql data|modifys sql data)
|sql security {definer|invoker}
|comment ‘string'

5. 刪除存儲過程和函數(shù) 
  5.1 通過drop語句刪除存儲過程：

語法形式如下：
drop prcedure proce_name;

  5.2 通過drop function語句刪除函數(shù)：

語法形式如下：
drop function func_name;

以上就是本文的全部內(nèi)容，希望對大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。

最新評論