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

1.1 什么是存儲(chǔ)過(guò)程

存儲(chǔ)過(guò)程是一組為了完成某項(xiàng)特定功能的sql語(yǔ)句集，其實(shí)質(zhì)上就是一段存儲(chǔ)在數(shù)據(jù)庫(kù)中的代碼，他可以由聲明式的sql語(yǔ)句（如CREATE,UPDATE,SELECT等語(yǔ)句）和過(guò)程式sql語(yǔ)句（如IF...THEN...ELSE控制結(jié)構(gòu)語(yǔ)句）組成。存儲(chǔ)過(guò)程思想上很簡(jiǎn)單，就是數(shù)據(jù)庫(kù) SQL 語(yǔ)言層面的代碼封裝與重用。

1.2 存儲(chǔ)過(guò)程的優(yōu)缺點(diǎn)

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

1.可增強(qiáng)sql語(yǔ)言的功能和靈活性
存儲(chǔ)過(guò)程可以用流程控制語(yǔ)言編寫(xiě)，有很強(qiáng)的靈活性，可以完成復(fù)雜的判斷和較復(fù)雜的運(yùn)算。

2.良好的封裝性
存儲(chǔ)過(guò)程被創(chuàng)建后，可以在程序中被多次調(diào)用，而不必?fù)?dān)心重寫(xiě)編寫(xiě)該存儲(chǔ)過(guò)程的sql語(yǔ)句。

3.高性能
存儲(chǔ)過(guò)程執(zhí)行一次后，其執(zhí)行規(guī)劃就駐留在高速緩沖存儲(chǔ)器中，以后的操作中只需要從高速緩沖器中調(diào)用已編譯好的二進(jìn)制代碼執(zhí)行即可，從而提高了系統(tǒng)性能。

缺點(diǎn):

存儲(chǔ)過(guò)程，往往定制化于特定的數(shù)據(jù)庫(kù)上，因?yàn)橹С值木幊陶Z(yǔ)言不同。當(dāng)切換到其他廠商的數(shù)據(jù)庫(kù)系統(tǒng)時(shí)，需要重寫(xiě)原有的存儲(chǔ)過(guò)程。

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

1.3.1 DELIMITER定界符

在sql中服務(wù)器處理sql語(yǔ)句默認(rèn)是以分號(hào)作為語(yǔ)句的結(jié)束標(biāo)志，然而在創(chuàng)建存儲(chǔ)過(guò)程時(shí)，存儲(chǔ)過(guò)程體中可能包含多條sql語(yǔ)句，這些sql語(yǔ)句如果仍以分號(hào)作為語(yǔ)句結(jié)束符，那么服務(wù)器在處理時(shí)會(huì)以第一條sql語(yǔ)句處的分號(hào)作為整個(gè)程序的結(jié)束符，而不再去處理后面的sql。
為解決這個(gè)問(wèn)題，通常使用DELIMITER命令，將sql語(yǔ)句的結(jié)束符臨時(shí)修改為其他符號(hào)。

DELIMITER語(yǔ)法格式：

DELIMITER $$

$$是用戶(hù)定義的結(jié)束符，通常這個(gè)符號(hào)可以是一些特殊的符號(hào)。另外應(yīng)避免使用反斜杠,因?yàn)樗寝D(zhuǎn)義字符。
若希望換回默認(rèn)的分號(hào)作為結(jié)束標(biāo)記，只需再在命令行輸入下面的sql語(yǔ)句即可。

DELIMITER ;

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

在Mysql中，使用CREATE PROCEDURE語(yǔ)句來(lái)創(chuàng)建存儲(chǔ)過(guò)程。

CREATE PROCEDURE p_name([proc_parameter[,...]])
routine_body

其中，語(yǔ)法項(xiàng)“proc_parameter”的語(yǔ)法格式是：

[IN|OUT|INOUT]parame_name type

1."p_name"用于指定存儲(chǔ)過(guò)程的名稱(chēng)。

2."proc_parameter"用于指定存儲(chǔ)過(guò)程中的參數(shù)列表。其中，語(yǔ)法項(xiàng)"parame_name"為參數(shù)名，"type"為參數(shù)的類(lèi)型（類(lèi)型可以是Mysql中任意的有效數(shù)據(jù)類(lèi)型）。Mysql的存儲(chǔ)過(guò)程支持三種類(lèi)型的參數(shù)，即輸入?yún)?shù)IN，輸出參數(shù)OUT,輸入輸出參數(shù)INOUT。輸入?yún)?shù)是使數(shù)據(jù)可以傳遞給一個(gè)存儲(chǔ)過(guò)程；輸出參數(shù)是用于存儲(chǔ)過(guò)程需要返回的一個(gè)操作結(jié)果；輸入輸出參數(shù)既可以充當(dāng)輸入?yún)?shù)也可以充當(dāng)輸出結(jié)果。
參數(shù)的取名不要和表中的列名相同，否則盡管不會(huì)返回出錯(cuò)信息，但儲(chǔ)存過(guò)程中的sql語(yǔ)句會(huì)將參數(shù)名當(dāng)做列名，從而引發(fā)不可預(yù)知的錯(cuò)誤。

3.語(yǔ)法項(xiàng)"rountine_body"表示存儲(chǔ)過(guò)程的主體部分，也成為存儲(chǔ)過(guò)程體，其包含了需要執(zhí)行的sql。過(guò)程體以關(guān)鍵字BEGIN開(kāi)始，以關(guān)鍵字END結(jié)束。若只有一條sql可以忽略BEGIN....END標(biāo)志。

1.3.3 局部變量

在存儲(chǔ)過(guò)程體中可以聲明局部變量，用來(lái)存儲(chǔ)過(guò)程體中的臨時(shí)結(jié)果。在Mysql中使用DECLARE語(yǔ)句來(lái)聲明局部變量。

DECLARE var_name type [DEFAULT value]

"var_name"用于指定局部變量的名稱(chēng)；"type"用來(lái)聲明變量的類(lèi)型；"DEFAULT"用來(lái)指定默認(rèn)值，如果沒(méi)有指定則為NULL。

注意：局部變量只能在存儲(chǔ)過(guò)程體的BEGIN...END語(yǔ)句塊中;局部變量必須在存儲(chǔ)過(guò)程體的開(kāi)頭處聲明;局部變量的作用范圍僅限于聲明它的BEGIN...END語(yǔ)句塊，其他語(yǔ)句塊中的語(yǔ)句不可以使用它。

1.3.4 用戶(hù)變量

用戶(hù)變量一般以@開(kāi)頭。

注意：濫用用戶(hù)變量會(huì)導(dǎo)致程序難以理解及管理。

1.3.5 SET語(yǔ)句

在Mysql中通過(guò)SET語(yǔ)句對(duì)局部變量賦值，其格式是：

SET var_name = expr[,var_name2 = expr]....

1.3.6 SELECT....INTO語(yǔ)句

在Mysql中，可以使用SELECT...INTO語(yǔ)句把選定的列的值存儲(chǔ)到局部變量中。格式是：

SELECT col_name[,..] INTO var_name[,....] table_expr

其中"col_name"用于指定列名；"var_name"用于指定要賦值的變量名；"table_expr"表示SELECT語(yǔ)句中FROM后面的部分。

注意：SELECT...INTO語(yǔ)句返回的結(jié)果集只能有一行數(shù)據(jù)。

1.3.7 流程控制語(yǔ)句

條件判斷語(yǔ)句

if-then-else 語(yǔ)句:

mysql > DELIMITER && 
mysql > CREATE PROCEDURE proc2(IN parameter int) 
 -> begin 
 -> declare var int; 
 -> set var=parameter+1; 
 -> if var=0 then 
 -> insert into t values(17); 
 -> end if; 
 -> if parameter=0 then 
 -> update t set s1=s1+1; 
 -> else 
 -> update t set s1=s1+2; 
 -> end if; 
 -> end; 
 -> && 
mysql > DELIMITER ; 

case語(yǔ)句:

mysql > DELIMITER && 
mysql > CREATE PROCEDURE proc3 (in parameter int) 
 -> begin 
 -> declare var int; 
 -> set var=parameter+1; 
 -> case var 
 -> when 0 then 
 -> insert into t values(17); 
 -> when 1 then 
 -> insert into t values(18); 
 -> else 
 -> insert into t values(19); 
 -> end case; 
 -> end; 
 -> && 
mysql > DELIMITER ; 

循環(huán)語(yǔ)句
while ···· end while:

mysql > DELIMITER && 
mysql > CREATE PROCEDURE proc4() 
 -> begin 
 -> declare var int; 
 -> set var=0; 
 -> while var<6 do 
 -> insert into t values(var); 
 -> set var=var+1; 
 -> end while; 
 -> end; 
 -> && 
mysql > DELIMITER ;

repeat···· end repea：

它在執(zhí)行操作后檢查結(jié)果，而 while 則是執(zhí)行前進(jìn)行檢查。

mysql > DELIMITER && 
mysql > CREATE PROCEDURE proc5 () 
 -> begin 
 -> declare v int; 
 -> set v=0; 
 -> repeat 
 -> insert into t values(v); 
 -> set v=v+1; 
 -> until v>=5 
 -> end repeat; 
 -> end; 
 -> && 
mysql > DELIMITER ;

repeat
 --循環(huán)體
 until 循環(huán)條件 
end repeat;

loop ·····endloop:

loop 循環(huán)不需要初始條件，這點(diǎn)和 while 循環(huán)相似，同時(shí)和 repeat 循環(huán)一樣不需要結(jié)束條件, leave 語(yǔ)句的意義是離開(kāi)循環(huán)。

mysql > DELIMITER && 
mysql > CREATE PROCEDURE proc6 () 
 -> begin 
 -> declare v int; 
 -> set v=0; 
 -> LOOP_LABLE:loop 
 -> insert into t values(v); 
 -> set v=v+1; 
 -> if v >=5 then 
 -> leave LOOP_LABLE; 
 -> end if; 
 -> end loop; 
 -> end; 
 -> && 
mysql > DELIMITER ;

ITERATE迭代：

mysql > DELIMITER && 
mysql > CREATE PROCEDURE proc10 () 
 -> begin 
 -> declare v int; 
 -> set v=0; 
 -> LOOP_LABLE:loop 
 -> if v=3 then 
 -> set v=v+1; 
 -> ITERATE LOOP_LABLE; 
 -> end if; 
 -> insert into t values(v); 
 -> set v=v+1; 
 -> if v>=5 then 
 -> leave LOOP_LABLE; 
 -> end if; 
 -> end loop; 
 -> end; 
 -> && 
mysql > DELIMITER ;

1.3.8 游標(biāo)

MySQL中的游標(biāo)可以理解成一個(gè)可迭代對(duì)象(類(lèi)比Python中的列表、字典等可迭代對(duì)象)，它可以用來(lái)存儲(chǔ)select 語(yǔ)句查詢(xún)到的結(jié)果集，這個(gè)結(jié)果集可以包含多行數(shù)據(jù)，從而使我們可以使用迭代的方法從游標(biāo)中依次取出每行數(shù)據(jù)。

MySQL游標(biāo)的特點(diǎn)：
1.只讀：無(wú)法通過(guò)光標(biāo)更新基礎(chǔ)表中的數(shù)據(jù)。
2.不可滾動(dòng)：只能按照select語(yǔ)句確定的順序獲取行。不能以相反的順序獲取行。 此外，不能跳過(guò)行或跳轉(zhuǎn)到結(jié)果集中的特定行。
3.敏感：有兩種游標(biāo)：敏感游標(biāo)和不敏感游標(biāo)。敏感游標(biāo)指向?qū)嶋H數(shù)據(jù)，不敏感游標(biāo)使用數(shù)據(jù)的臨時(shí)副本。敏感游標(biāo)比一個(gè)不敏感的游標(biāo)執(zhí)行得更快，因?yàn)樗恍枰R時(shí)拷貝數(shù)據(jù)。MySQL游標(biāo)是敏感的。

1.聲明游標(biāo)

游標(biāo)聲明必須在變量聲明之后。如果在變量聲明之前聲明游標(biāo)，MySQL將會(huì)發(fā)出一個(gè)錯(cuò)誤。游標(biāo)必須始終與select語(yǔ)句相關(guān)聯(lián)。

declare cursor_name cursor for select_statement;

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

使用open語(yǔ)句打開(kāi)游標(biāo)，只有先打開(kāi)游標(biāo)才能讀取數(shù)據(jù)。

open cursor_name;

3.讀取游標(biāo)

使用fetch語(yǔ)句來(lái)檢索游標(biāo)指向的一行數(shù)據(jù)，并將游標(biāo)移動(dòng)到結(jié)果集中的下一行。

fetch cursor_name into var_name;

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

使用close語(yǔ)句關(guān)閉游標(biāo)。

close cursor_name;

當(dāng)游標(biāo)不再使用時(shí)，應(yīng)該關(guān)閉它。   當(dāng)使用MySQL游標(biāo)時(shí)，還必須聲明一個(gè)notfound處理程序來(lái)處理當(dāng)游標(biāo)找不到任何行時(shí)的情況。 因?yàn)槊看握{(diào)用fetch語(yǔ)句時(shí)，游標(biāo)會(huì)嘗試依次讀取結(jié)果集中的每一行數(shù)據(jù)。 當(dāng)游標(biāo)到達(dá)結(jié)果集的末尾時(shí)，它將無(wú)法獲得數(shù)據(jù)，并且會(huì)產(chǎn)生一個(gè)條件。 處理程序用于處理這種情況。

declare continue handler for not found set type = 1;

type是一個(gè)變量，示游標(biāo)到達(dá)結(jié)果集的結(jié)尾。

delimiter $$
create PROCEDURE phoneDeal()
BEGIN
 DECLARE id varchar(64); -- id
 DECLARE phone1 varchar(16); -- phone
 DECLARE password1 varchar(32); -- 密碼
 DECLARE name1 varchar(64); -- id
 -- 遍歷數(shù)據(jù)結(jié)束標(biāo)志
 DECLARE done INT DEFAULT FALSE;
 -- 游標(biāo)
 DECLARE cur_account CURSOR FOR select phone,password,name from account_temp;
 -- 將結(jié)束標(biāo)志綁定到游標(biāo)
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
 
 -- 打開(kāi)游標(biāo)
 OPEN cur_account; 
 -- 遍歷
 read_loop: LOOP
 -- 取值 取多個(gè)字段
 FETCH NEXT from cur_account INTO phone1,password1,name1;
 IF done THEN
 LEAVE read_loop;
 END IF;
 
 -- 你自己想做的操作
 insert into account(id,phone,password,name) value(UUID(),phone1,password1,CONCAT(name1,'的家長(zhǎng)'));
 END LOOP;
 
 -- 關(guān)閉游標(biāo)
 CLOSE cur_account;
END $$

1.3.7 調(diào)用存儲(chǔ)過(guò)程

使用call語(yǔ)句調(diào)用存儲(chǔ)過(guò)程

call sp_name[(傳參)];

1.3.8 刪除存儲(chǔ)過(guò)程

使用drop語(yǔ)句刪除存儲(chǔ)過(guò)程

DROP PROCEDURE sp_name

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

2.1 什么是存儲(chǔ)函數(shù)

存儲(chǔ)函數(shù)和存儲(chǔ)過(guò)程一樣，都是sql和語(yǔ)句組成的代碼塊。
存儲(chǔ)函數(shù)不能有輸入?yún)?shù)，并且可以直接調(diào)用，不需要call語(yǔ)句，且必須有一條包含RETURN語(yǔ)句。

2.2 創(chuàng)建存儲(chǔ)函數(shù)

在Mysql中使用CREATE FUNCTION語(yǔ)句創(chuàng)建：

CREATE FUNCTION fun_name (par_name type[,...])
RETURNS type
[characteristics] 
fun_body

其中，fun_name為函數(shù)名，并且名字唯一，不能與存儲(chǔ)過(guò)程重名。par_name是指定的參數(shù)，type為參數(shù)類(lèi)型；RETURNS字句用來(lái)聲明返回值和返回值類(lèi)型。fun_body是函數(shù)體，所有存儲(chǔ)過(guò)程中的sql在存儲(chǔ)函數(shù)中同樣可以使用。但是存儲(chǔ)函數(shù)體中必須包含一個(gè)RETURN 語(yǔ)句。
characteristics指定存儲(chǔ)過(guò)程的特性，有以下取值：

• LANGUAGE SQL:說(shuō)明routine_body部分是由SQL語(yǔ)句組成的，當(dāng)前系統(tǒng)支持的語(yǔ)言為SQL，SQL是LANGUAGE特性的唯一值。
• [NOT] DETERMINISTIC:指明存儲(chǔ)過(guò)程執(zhí)行的結(jié)果是否確定。DETERMINISTIC表示結(jié)果是確定的，每次執(zhí)行存儲(chǔ)過(guò)程時(shí)，相同的輸入會(huì)得到相同的輸出，NOT DETERMINISTIC表示結(jié)果是不確定的，相同的輸入可能得到不同的輸出，如果沒(méi)有指定任意一個(gè)值，默認(rèn)為NOT DETERMINISTIC。
• [CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA]：指明子程序使用SQL語(yǔ)句的限制。CONTAINS SQL表明子程序包含SQL語(yǔ)句，但不包含讀寫(xiě)數(shù)據(jù)語(yǔ)句；NO SQL表明子程序不包含SQL語(yǔ)句；READS SQL DATA說(shuō)明子程序包含讀數(shù)據(jù)的語(yǔ)句；MODIFIES SQL DATA表名子程序包含寫(xiě)數(shù)據(jù)的語(yǔ)句。默認(rèn)情況下，系統(tǒng)會(huì)指定為CONTAINS SQL。
• SQL SECURITY[DEFINER|INVOKER]：指明誰(shuí)有權(quán)限來(lái)執(zhí)行。DEFINER表示只有定義著才能執(zhí)行。INVOKER表示用友權(quán)限的調(diào)用者可以執(zhí)行。默認(rèn)情況下，系統(tǒng)指定為DEFINER。
• COMMENT 'string'：注釋信息，用來(lái)描述存儲(chǔ)過(guò)程或函數(shù)。

delimiter $$
create function getAnimalName(animalId int) RETURNS VARCHAR(50)
DETERMINISTIC
begin
 declare name VARCHAR(50);
 set name=(select name from animal where id=animalId);
 return (name);
end$$
delimiter;

-- 調(diào)用
select getAnimalName(4)

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

最新評(píng)論