En termes simples, une procédure stockée est une combinaison d'une ou plusieurs instructions SQL, qui peut être considérée comme un fichier batch, mais son r?le ne se limite pas au traitement par lots.

(1) Créer une procédure stockée
(2) Créer une fonction stockée
(3) Utilisation de variables
(4) Définir des conditions et des gestionnaires
(5) Utilisation du curseur
(6) Utilisation du contr?le de processus

(recommandation d'apprentissage gratuite?: tutoriel vidéo mysql)

---

(1) Créer une procédure stockée

Pour créer une procédure stockée, vous devez utiliser l'instruction create procedure Le format de syntaxe de base est le suivant?:

create?procedure?sp_name(?[?proc_parameter?]?)[?characteristics?...?]?routine_body

create procedure est utilisé pour créer un mot-clé de fonction stockée?; sp_name est le nom de la procédure stockée?; proc_parameter est la liste des paramètres de la procédure stockée?:

[in?|?out?|?inout]?param_name?type

in représente. le paramètre d'entrée
• out représente le paramètre de sortie
inout indique qu'il peut être entré ou sorti
• param_name indique le nom du paramètre indique le type de paramètre

characteristics précise les caractéristiques de la procédure stockée, avec les valeurs suivantes :

• langage SQL : Description La partie routine_body est composée d'instructions SQL Le langage actuellement supporté par. le système est SQL et SQL est la seule valeur de l'attribut de langage.

• [non] déterministe : Indique si le résultat de l'exécution de la procédure stockée est correct. Déterministe signifie que la même entrée obtiendra la même sortie à chaque fois que la procédure stockée est exécutée?; alors que non déterministe signifie que la même entrée peut obtenir des sorties différentes. La valeur par défaut n'est pas déterministe.

• {contient du SQL | aucun SQL | lit la date SQL | modifie la date SQL }?: Indique les restrictions sur l'utilisation des instructions SQL par les sous-programmes. contient SQL indique que le sous-programme contient des instructions SQL ; aucun SQL indique que le sous-programme ne lit pas de données SQL ; indique que le sous-programme contient des instructions pour lire des données ; modifie des données SQL indique que le sous-programme contient des instructions pour écrire des données. La valeur par défaut contient SQL.

• Sécurité SQL {definer | invocateur}?: Spécifiez qui a l'autorisation d'exécuter. le définisseur signifie que seul le définisseur peut l'exécuter ; l'invocateur signifie que l'appelant autorisé peut l'exécuter. La valeur par défaut est le définisseur.

• comment 'string'?: informations de commentaire, qui peuvent être utilisées pour décrire des procédures ou des fonctions stockées.

routine_body est le contenu du code SQL Vous pouvez utiliser start...end pour représenter le début et la fin du code SQL.

[Exemple 1] Créez une procédure stockée pour afficher la table des fruits. L'instruction de code est la suivante?:

create?procedure?proc()
	BEGIN
	select?*?from?fruits;
	END?;

Ce code crée une procédure stockée pour afficher la table des fruits. est le suivant?:

mysql>?delimiter?//mysql>?create?procedure?Proc()
????->?begin
????->?select?*?from?fruits;
????->?end?//Query?OK,?0?rows?affected?(0.36?sec)mysql>?delimiter?;

Astuce?: La fonction de l'instruction "delimiter //" est de définir le symbole de fin de MySQL sur //, car le symbole de fin d'instruction par défaut de MySQL est un point-virgule " ;". Ceci est fait pour éviter les conflits avec SQL dans les procédures stockées. Les terminateurs d'instruction sont en conflit. Une fois la procédure stockée définie, utilisez ??delimiter?;?? pour restaurer le terminateur par défaut. Lorsque vous utilisez la commande delimiter, vous devez éviter d'utiliser la barre oblique inverse "" car la barre oblique inverse est un caractère d'échappement dans MySQL.

[Exemple 2] Créez une procédure stockée nommée CountProc Le code est le suivant :

create?procedure?CountProc?(OUT?paraml?int)beginselect?count(*)?into?paraml?from?fruits;end;

Le code ci-dessus crée une procédure stockée pour obtenir le nombre d'enregistrements dans la table des fruits. . Le nom est CountProc. Après le calcul count(*), le résultat est mis dans le paramètre paraml. Le résultat de l'exécution du code est le suivant?:

mysql>?delimiter??//mysql>?create?procedure?CountProc(OUT?paraml?int?)
????->?begin
????->?select?count(*)?into?paraml?from?fruits;
????->?end?//Query?OK,?0?rows?affected?(0.08?sec)mysql>?delimiter?;

(2) Créer une fonction stockée

Pour créer une fonction stockée, vous devez utiliser l'instruction create function La syntaxe de base est la suivante. suit?:

create?function?func_name?(?[?func_parameter]?)
returns?type
[characteristic?...]?routine_body

create function est le mot-clé utilisé pour créer une fonction stockée
• func_name représente le nom de la fonction stockée
• func_parameter est la liste des paramètres de la procédure stockée. La liste des paramètres est sous la forme?:
• [in | out | inout] param_name type

Parmi eux, in représente les paramètres d'entrée, out représente les paramètres de sortie, inout représente à la fois l'entrée et la sortie. le type de paramètre ; l'instruction return type représente le type de données renvoyées par la fonction ; la caractéristique spécifie les caractéristiques de la fonction stockée, la valeur est la même que lors de la création de la procédure stockée.

[Exemple 3] Créez une fonction de stockage nommée NameByZip. Cette fonction renvoie le résultat de la requête de l'instruction select. Le type numérique est de type cha?ne :

create?function?NameByZip()returns?char(?50)return(select?s_name?from?suppliers?where?s_call?='48075');

L'exécution. le résultat du code est le suivant?:

mysql>?delimiter?//mysql>?create?function?NameByZip()
????->?returns?char(50)
????->?return?(select?s_name?from?suppliers?where?s_call?=?'48075');
????->?//Query?OK,?0?rows?affected?(0.06?sec)mysql>?delimiter;

Si une instruction ruturn dans une fonction stockée renvoie une valeur d'un type différent du type spécifié dans la clause return de la fonction, la valeur de retour sera forcée au type approprié. .

Remarque?: La spécification de paramètres comme in, out ou inout n'est légale que pour les procédures. (La fonction utilise toujours par défaut le paramètre in.) La clause return ne peut être spécifiée que pour la fonction et est obligatoire pour la fonction. Il est utilisé pour spécifier le type de retour de la fonction et le corps de la fonction doit contenir une instruction de valeur de retour.

(3) Utilisation de variables

Les variables peuvent être déclarées et utilisées dans des sous-programmes, et la portée de ces variables est dans le programme de début... de fin.

1. Définir les variables

Utilisez l'instruction declar pour définir les variables dans la procédure stockée. Le format de syntaxe est le suivant?:

declare?var_name[,varname]...?date_type?[default?value];

var_name est le nom de la variable locale. . La clause de valeur par défaut fournit une valeur par défaut pour la variable. En plus d'être déclarée comme constante, la valeur peut également être spécifiée comme expression. S'il n'y a pas de clause par défaut, la valeur initiale est nulle.

【例4】定義名稱為myparam的變量，類型為int類型，默認值為100，代碼如下：

declare?myparam?int?default?100;

2.為變量賦值

set?var_name?=?expr?[,var_name?=?expr]...;

存儲程序中的set語句是一般set語句的擴展版本。被參考變量可能是子程序內聲明的變量，或者是全局服務器變量，如系統(tǒng)變量或者用戶變量。

【例5】聲明3個變量，分別為var1，var2和var3，數(shù)據(jù)類型為int，使用set為變量賦值，代碼如下：

declare?var1，var2，var3?int;set?var1?=?10,?var2?=20;set?var3?=?var1?+?var2;

MySQL中還可以通過select…into為一個或多個變量賦值，語句如下：

select?col_name[,...]?into?var_name[,...]?table_expr;

這個select語法把選定的列直接存儲到對應位置的變量。col_name表示字段名稱;var_name表示定義的變量名稱；table_expr表示查詢條件表達式，包括表名稱和where子句。

【例6】聲明變量fruitname和fruitprice，通過select…into語句查詢指定記錄并為變量賦值,代碼如下:

declare?fruitname?char(50);declare?fruitprice?decimal(8,2);select?f_name,f_price?into?fruitname,fruitpricefrom?fruits?where?f_id='a1;

(4)定義條件和處理程序

特定條件需要特定處理。定義條件是事先定義程序執(zhí)行過程中遇到的問題，處理程序定義了在遇到這些問題時應當采取的處理方式，并且保證存儲過程或函數(shù)在遇到警告或錯誤時能繼續(xù)執(zhí)行。這樣就增強了存儲程序處理問題的能力，避免程序異常停止運行。

1.定義條件
定義條件使用declare語句，語法格式如下：

declare?conditon_name?Condition?for?[condition_type][condition_type];SQLSTATE?[value]?sqlstate_value?|?mysql_error_code

• condition_name表示條件的名稱
• condition_type表示條件的類型
• sqlstate_value和mysql_error_code都可以表示MySQL的錯誤
• sqlstate_value為長度為5的字符類型錯誤代碼
• mysql_error_code為數(shù)值類型錯誤代碼

例如：ERROR1142(42000)中，sqlstate_value的值是42000，mysql_error_code的值為1142。

這個語句指定需要特殊處理的條件。它將一個名字和指定的錯誤條件關聯(lián)起來。這個名字可以隨后被用在定義處理程序的declare handler語句中。

【例7】定義"error 1148(42000)"錯誤，名稱為command_not_allowed?？梢杂脙煞N不同的方法來定義，代碼如下：

[方法一]：使用sqlstate_valuedeclare?command_not_allowed?condition?for?sqlstate?'42000'[]方法二]:使用mysql_error_codedeclare?command_not_allowed?condition?for?1148

2.定義處理程序

定義處理程序時，使用declare語句的語法如下：

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

其中，

• handler_type為錯誤處理方式，參數(shù)取3個值：continue、exit和undo。
• continue表示遇到錯誤不處理，繼續(xù)執(zhí)行；
• exit遇到錯誤馬上退出；
• undo表示遇到錯誤后撤回之前的操作，MySQL中暫時不支持這樣的操作。

condition_value表示錯誤類型，可以有以下取值：

• sqlstate[value] sqlstate_value包含5個字符串錯誤值
• condition_name表示declare condition定義的錯誤條件名稱
• sqlwarning匹配所有以01開頭的sqlstate錯誤代碼
• notfound 匹配所有以02開頭的sqlstate錯誤代碼
• sqlexception匹配所有沒有被sqlwarning或not found捕獲的sqlstate錯誤代碼
• mysql_error_code匹配數(shù)值類型錯誤代碼

sp_statement參數(shù)為程序語句段，表示在遇到定義的錯誤時，需要執(zhí)行的存儲過程或函數(shù)。

【例8】定義處理程序的幾種方式如下：

方法1：捕獲sqlstate_valuedeclare?continue?handler?for?sqlstate?'42S02'?set?@info='No_SUCH_TABLE';方法2:捕獲mysql_error_codedeclare?continue?handler?for?1146?set?@info='No_SUCH_TABLE';方法3:先定義條件,然后調用declare?no_such_table?condition?for?1146;declare?continue?handler?for?NO_SUCH_TABLE?SET?@info='NO_SUCH_TABLE';方法4:使用sqlwarningdeclare?exit?handler?for?sqlwarning?set?@info='ERROR';方法5:使用not?founddeclare?exit?handler?for?not?found?set?@info='?NO_SUCH_TABLE?';方法6:使用sqlexceptiondeclare?exit?handler?forsqlexception?set?@info='ERROR';

上述代碼是6種定義處理程序的方法。

第一種，捕獲sqlstate_value值。如果遇到sqlstate_value值為"42S02"，執(zhí)行continue操作，并且輸出"NO_SUCH_TABLE"信息。
第二種，捕獲mysql_error_code值。如果遇到mysql_error_code值為1146，就執(zhí)行continue操作，并且輸出"NO_SUCH_TABLE"信息。
第三種，先定義條件再調用條件。這里先定義no_such_table條件，遇到1146錯誤就執(zhí)行continue操作。
第四種，使用sqlwarning。sqlwarning捕獲所有以01開頭的sqlstate_value值，然后執(zhí)行exit操作，并且輸出"ERROE"信息。
第五種，使用not found。not found捕獲所有以02開頭的sqlstate_value值，然后執(zhí)行exit操作，并且輸出"NO_SUCH_TABLE"信息。
第六種，使用SQLEXCEPTION。sqlexception捕獲所有沒有被sqlwarning或not found捕獲的sqlstate_value值，然后執(zhí)行exit操作，并且輸出"ERROR"信息。

【例9】定義條件和處理程序，具體執(zhí)行的過程如下：

mysql>?create?table?test.t(s1?int,primary?key?(s1));Query?OK,?0?rows?affected?(0.14?sec)mysql>?delimiter?//mysql>?create?procedure?handlerdemo()
????->?begin
????->?declare?continue?handler?for?sqlstate?'23000'?set?@x2=1;
????->?set?@x?=1;
????->?insert?into?test.t?values(1);
????->?set?@x=2;
????->?insert?into?test.t?values(1);
????->?set?@x=3;
????->?end;
????->?//Query?OK,?0?rows?affected?(0.06?sec)[調用存儲過程]mysql>?delimiter?;mysql>?call?handlerdemo();Query?OK,?0?rows?affected?(0.08?sec)[查看調用過程結果]mysql>?select?@x;+------+|?@x???|+------+|????3?|+------+1?row?in?set?(0.00?sec)

可以看到，@x 是一個用戶變量，執(zhí)行結果@x等于3，這表明MySQL被執(zhí)行到程序末尾。

• "var_name"表示用戶變量，使用set語句為其賦值。用戶變量與連接有關，一個客戶端定義的變量不能被其他客戶端看到或使用。當客戶端退出時，該客戶端連接的所有變量將自動釋放。

(5)光標的使用

MySQL中光標只能在存儲過程和函數(shù)中使用。

查詢語句可能返回多條記錄，如果數(shù)據(jù)量非常大，需要在存儲過程和存儲函數(shù)中使用光標來逐條讀取查詢結果集中的記錄。光標必須在聲明處理程序之前被聲明，并且變量和條件還必須在聲明光標或處理程序之前被聲明。

1.聲明光標

MySQL中使用declare關鍵字來聲明光標，語法形式如下：

declare?cursor_name?cursor?for?select_statement

其中，cursor_name參數(shù)表示光標的名稱；select_statement表示select語句的內容，返回一個用于創(chuàng)建光標的結果集。

【例10】聲明名稱為cursor_fruit的光標，代碼如下：

declare?cursor_fruit?cursor?for?select?f_name,f_price?from?fruits;

該代碼中光標名稱為cursor_fruit，select語句部分從fruits表匯總查詢出f_name和f_price字段的值。

2.打開光標

open?cursor_name{光標名稱}

這個語句打開先前聲明的名稱為cursor_name的光標。

【例11】打開名稱為cursor_fruit的光標，代碼如下：

open?cursor_fruit?;

3.使用光標

使用光標的語法格式:

fetch?cursor_name?into?var_name?[，var_name?]?...?{參數(shù)名稱}

其中，cursor_name參數(shù)表示光標的名稱；var_name表示將光標中的select語句查詢出來的信息存入該參數(shù)中，var_name必須在聲明光標之前就定義好。

【例12】使用名稱為cursor_fruit的光標。將查詢出來的數(shù)據(jù)存入fruit_name和fruit_price兩個變量中，代碼如下:

fetch?cursor_fruit?into?fruit_name,fruit_price;

4.關閉光標
關閉光標的語法格式:

close?cursor_name(光標名稱)

這個語句關閉先前打開的光標。
如果未被明確地關閉，那么光標將在它被聲明的復合語句的末尾被關閉。

【例13】關閉名稱為cursor_fruit的光標，代碼如下：

close?cursor_fruit;

(6)流程控制的使用

流程控制語句用來根據(jù)條件控制語句的執(zhí)行。MySQL中用來構造控制流程的語句有IF語句、case語句、loop語句、leave語句、iterate語句、repeat語句和while語句。每個流程中可能包含一個單獨語句，或者是使用begin…end構造的符合語句，構造可以被嵌套。

1.if語句

if語句包含多個條件判斷，根據(jù)判斷的結果為true或false執(zhí)行相應的語句，語法格式如下：

if?expr_condition?then?statement_list	[elseif?expr_condition?then?statement_list]...
	[else?statement_list]end?if

如果expr_condition求值為真，相應的SQL語句列表被執(zhí)行；如果沒有expr_condition匹配，則else子句里的語句列表被執(zhí)行。statement_list列表可包括一個或多個語句。

MySQL中還有一個if()函數(shù)，它不同于這里描述的if語句。

【例14】if語句示例

if?val?is?null
	then?select?‘val?is?null’;
	else?select?'val?is?not?null';end?if

該示例判斷val值是否為空，如果為空輸出字符串"val is null"；否則輸出字符串"val is not null"。if語句都需要使用end if來結束。

2.case語句

case是另一個進行條件判斷的語句，有兩種語句格式，第一種：

case?case_expr	when?when_value?then?statement_list	[when?when_value?then?statement_list]...
	[else?statement_list]end?case

• case_expr表示條件判斷的表達式，決定了哪一個when語句會被執(zhí)行
• when_value表示表達式可能的值。
• 如果某個when_value表達式和case_expr表達式結果相同，則執(zhí)行對應的then關鍵字后面的statement_list中的語句。
• statement_list表示不同when_value值的執(zhí)行語句。

【例15】使用case流程控制語句的第1種格式，判斷val值等于1、等于2或者兩者都不等，SQL語句如下：

case?val	when?1?then?select?‘val?is?1’；	when?2?then?select?‘val?is?2’；	else?select?‘val?is?not?1?or?2’；end?case;

當val值為1時，輸出字符串"val is 1";當val值為2時，輸出字符串"val is 2";否則輸出字符串"val is not 1 or 2"。

case語句的第2種格式如下：

case
	when?expr_condition?then?statement_list	[when?expr_condition?then?statement_list]
	[else?statement_list]end?case

• expr_condition表示條件判斷語句
• statement_list表示不同條件的執(zhí)行語句

該語句中，when語句將被逐個執(zhí)行，直到某個expr_condition表達式為真，則執(zhí)行對應then關鍵字后面的statement_list語句。如果沒有條件匹配，else子句里的語句被執(zhí)行。

注意：存儲程序中的case語句和case控制流程函數(shù)的區(qū)別：
存儲程序中的case語句不能有else null子句，并且用end case替代end來終止。

【例16】使用case流程控制語句的第2種格式，判斷val是否為空、小于0、大于0或者等于0，SQL語句如下：

case
	when?val?is?null?then?select??‘val?is?null’;
	when?val?< 0 then  select &#39;val is less than 0&#39;;
	when val >?0?then?select?'val?is?greater?than?0';
	else?select?'val?is?0';end?case;

當val值為空時，輸出字符串"val is null";當val值小于0時，輸出字符串"val is less than 0";當val值大于0時，輸出字符串"val is greater than 0";否則輸出字符串"val is 0"。

3.loop語句

loop循環(huán)語句用來重復執(zhí)行某些語句，與if和case語句相比，loop只是創(chuàng)建一個循環(huán)操作過的過程，并不進行條件判斷。退出循環(huán)過程使用leave子句。loop語法格式如下:

[loop_label:]?loop
	statement_listend?loop?[loop_label]

loop_label表示loop語句的標注名稱，該參數(shù)可省略。statement_list參數(shù)表示需要循環(huán)執(zhí)行的語句。

【例17】使用loop語句進行循環(huán)操作，id值小于等于10之前，將重復執(zhí)行循環(huán)過程，SQL語句如下：

declare?id?int?default?10add_loop:loopset?id?=?id?+1;
	if?>=10?then?leave?add_loop;
	end?if;end?loop?add_?loop;

該示例循環(huán)執(zhí)行id加1的操作。當id值小于10時，循環(huán)重復執(zhí)行。當id值大于或等于10時，使用leave語句退出循環(huán)。loop循環(huán)都以end loop結束。

4.leave語句

leave語句用來退出任何被標注的流程控制構造，leave語句基本格式如下：

leave?label

其中，label參數(shù)表示循環(huán)的標志。leave和begin…end或循環(huán)一起被使用。

【例18】使用leave語句退出循環(huán)，代碼如下：

add_num:loopset?@count=@count+1;if?@count=50?then?leave?add_num;end?loop?add_num;

該示例循環(huán)執(zhí)行count加1的操作，當count的值等于50時，使用leave語句跳出循環(huán)。

5.iterate語句

iterater label語句將執(zhí)行順序轉到語句段開頭處，語法格式如下：

iterate?label

iterate只可以出現(xiàn)在loop、repeat和while語句內。iterate的意思為"再次循環(huán)"，label參數(shù)表示循環(huán)的標志。iterate語句必須跟在循環(huán)標志前面。

【例19】iterate語句示例：

create?procedure?doiterate()begin
?declare?p1?int?default?0;
?declare?p1?int?default?0;
?my_loop:loop;
?set?p1?=?p1?+?1;
?if?p1?< 10 then iterate my_loop;
 elseif p1 >?20?then?leave?my_loop;
?end?if;
?select?'p1?is?between?10?and?20';end?loop?my_loop;end

首先定義p1=0，當p1的值小于10時重復執(zhí)行p1加1操作；當p1大于等于10并且小于等于20時，打印消息"p1 is between 10 and 20";當p1大于20時，退出循環(huán)。

6.repeat語句

repeat語句創(chuàng)建一個帶條件判斷的循環(huán)過程，每次語句執(zhí)行完畢之后，會對條件表達式進行判斷，若表達式為真，則循環(huán)結束；否則重復執(zhí)行循環(huán)中的語句。repeat語句的語法格式如下：

[repeat_label:]?repeat
	statement_list
until?expr_conditionend?repeat?[repeat_label]

repeat_label為repeat語句的標注名稱，該參數(shù)可以省略；repeat語句內的語句或語句群被重復，直至expr_condition為真。

【例20】repeat語句示例，id值等于10之前，將重復執(zhí)行循環(huán)過程，代碼如下：

declare?id?int?default?0；repeatset?id?=?id?+?1；
until?id?>=?10end?repeat;

該示例循環(huán)執(zhí)行id加1的操作。當id值小于10時，循環(huán)重復執(zhí)行；當id值大于或者等于10時，退出循環(huán)。repeat循環(huán)都以end repeat結束。

7.while語句

while語句創(chuàng)建一個帶條件判斷的循環(huán)過程，與repeat不同，while在執(zhí)行語句執(zhí)行時，先對指定的表達式進行判斷，如果為真，就執(zhí)行循環(huán)內的語句，否則退出循環(huán)。while語句的基本格式如下：

[while_label:]?while?expr_condition?do
	statement_listend?while?[while_label]

• while_label為while語句的標注名稱
• expr_condition為進行判斷的表達式，如果表達式結果為真，while語句內的語句或語句群被執(zhí)行，直至expr_condition為假，退出循環(huán)。

【例21】while語句示例，i值小于10時，將重復執(zhí)行循環(huán)過程，代碼如下：

declare?i?int?default?0；while?i?<10?doset?i?=?i?+?1;end?while;

相關免費學習推薦：mysql數(shù)據(jù)庫(視頻)

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!