Maison > base de données > tutoriel mysql > le corps du texte

Comment créer des procédures stockées et des fonctions MySQL

王林
Libérer: 2023-06-03 09:22:42
avant
1839 Les gens l'ont consulté

1.0 Créer des procédures et des fonctions stockées

Créer des procédures et des fonctions stockées consiste à combiner un ensemble d'instructions SQL fréquemment utilisées et à stocker ces instructions SQL dans leur ensemble dans le serveur MySQL# 🎜🎜#

1. Créer une procédure stockée

CREATE PROCEDURE procedure_name ([proc_param[,...]])
    [characteristic ...] routine_body
Copier après la connexion

  • procedure_name représente le nom de la procédure stockée à créer

  • proc_param représente les paramètres de la procédure stockée

  • characteristic représente les caractéristiques de la procédure stockée

  • #🎜 🎜#routine_body représente L'instruction SQL de la procédure stockée
  • procedure_name ne peut pas avoir de noms en double

proc_param La forme syntaxique de chaque paramètre dans proc_param. est le suivant. Chaque paramètre se compose de trois parties. Ils sont composés du type d'entrée/sortie, du nom du paramètre et du type de paramètre. Il existe trois types d'entrée et de sortie, à savoir IN, OUT et INOUT. param_name représente le nom du paramètre, type représente le type du paramètre

[ IN | OUT | INOUT ] param_name type
Copier après la connexion

characteristic spécifie les caractéristiques de la procédure stockée, avec les valeurs suivantes :

    LANGAGE SQL : Description La procédure stockée est écrite en langage SQL. Exécutez la procédure stockée, la même entrée obtiendra la même sortie ; NON DÉTERMINISTE signifie que le résultat est incertain, la même entrée peut obtenir une sortie différente, la valeur par défaut est NON DÉTERMINISTE#🎜 🎜#
  • #🎜🎜 #{CONTAINS SQL | NO SQL | LIT SQL DATA | MODIFIE SQL DATA} : Indique les restrictions sur l'utilisation des instructions SQL par le sous-programme CONTIENT SQL signifie que le sous-programme le fait. ne contient pas d'instructions pour lire ou écrire des données, NO SQL signifie que le sous-programme ne contient pas de SQL, READS SQL DATA signifie que le sous-programme contient des instructions pour lire des données, mais ne contient pas d'instructions pour écrire des données. instructions pour l'écriture de données

  • SQL SECURITY {DEFINER | INVOKER} : Indique qui a l'autorisation d'exécuter DEFINER signifie que seul le définisseur peut exécuter INVOKER signifie que l'appelant. avec autorisation peut s'exécuter. La valeur par défaut est DEFINER

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

    #🎜🎜. #
  • Exemple : Créez une procédure stockée pour interroger le salaire de tous les employés de la table t_employee

    DELIMITER $$
    CREATE PROCEDURE proc_employee()
    COMMENT '查询员工薪水'
    BEGIN
    	SELECT salary FROM t_employee
    END;
    $$
    DELIMITER ;
    Copier après la connexion

    MySQL utilise le point-virgule comme terminateur d'instruction par défaut dans les instructions SQL stockées. les procédures doivent être terminées par un point-virgule. Afin d'éviter les conflits, utilisez d'abord DELIMITER $$ pour définir le terminateur MySQL sur $$, puis utilisez DELIMITER ; Restaurez le caractère de fin par un point-virgule
  • 2. Créer une fonction stockée

    CREATE FUNCTION fun_name([func_param,[,...]])
    	[characteristic ...] routine_body
    Copier après la connexion

fun_name représente le nom de la fonction

#🎜🎜 #

func_param représente le paramètre de la fonction #🎜🎜 #

characteristic représente les caractéristiques de la fonction, la valeur est la même que la valeur de la procédure stockée
    #🎜 🎜#
  • routine_body représente la Instruction SQL de la fonction

  • La forme syntaxique de chaque paramètre dans func_param est la suivante, qui sont le nom du paramètre et le type de paramètre#🎜 🎜#

    #🎜 🎜#param_type type
  • Toujours l'exemple précédent

    DELIMITER $$
    	CREATE FUNCTION func_employee(id INT(4))
    		RETURNS INT(6)
    	COMMENT '查询员工薪水'
    	BEGIN
    		SELECT salary FROM t_employee
    	END;
    	$$
    DELIMITER ;
    Copier après la connexion
  • 2|0 variable
  • in Dans MySQL, les variables peuvent être définies et utilisé, et la portée de ces variables se trouve dans la section du programme BEGIN...END

  • 1 Définir les variables
DECLARE var_name[,...] type [DEFAULT value]
Copier après la connexion

#🎜 🎜#Le mot-clé DECLARE est. utilisé pour déclarer des variables

Le paramètre var_name est le nom de la variable, plusieurs variables peuvent être définies en même temps

#🎜🎜 #

Le type de paramètre est utilisé pour spécifier le type de la variable

La clause DEFAULT value définit la valeur par défaut de la variable sur value Lorsque la clause DEFAULT est définie sur value. n'est pas utilisé, la valeur par défaut est NULL

  • Exemple : Définissez la variable sql_test, le type de données est INT, la valeur par défaut est 10

    DECLARE test_sql INT DEFAULT 10;
    Copier après la connexion
    #🎜🎜 #2. Affectation des variables
  • SET var_name=expr[,var_name=expr]...
    Copier après la connexion
  • Le mot-clé SET est utilisé pour attribuer des valeurs aux variables
  • Le paramètre var_name est le nom de la variable
  • #🎜🎜 #
  • Le paramètre expr est une expression d'affectation

Exemple : Attribuez la variable sql_test à 30

SET test_sql = 30;
Copier après la connexion

3| 0Définir les conditions et les gestionnaires

    Définir les conditions et les gestionnaires consiste à définir à l'avance les problèmes qui peuvent être rencontrés lors de l'exécution du programme, et les La capacité à résoudre ces problèmes peut être définie dans le gestionnaire
  • #🎜 🎜#1 Define conditions

    DECLARE condition_name CONDITION FOR condition_value
    condition_value:
    SQLSTATE[VALUE] sqlstate_value|mysql_error_code
    Copier après la connexion

  • Le paramètre condition_name représente le nom de la condition#🎜🎜. #

  • Le paramètre condition_value représente le type de condition# 🎜🎜#
  • Le paramètre sqlstate_value et le paramètre mysql_error_code peuvent représenter des erreurs MySQL#🎜 🎜#

Exemple : Define ERROR 1146(42S02 ) L'erreur, nommée can_not_find, peut être définie de deux manières différentes

// 方法一:使用sqlstate_value
DECLARE can_not_find CONDITION FOR SQLSTATE '42S02';
// 方法二:使用mysql_error_code
DECLARE can_not_find CONDITION FOR 1146;
Copier après la connexion

2 Définissez le gestionnaire #. 🎜🎜#
DECLARE handler_type HANDLER FOR condition_value[,...] proc_statement
handler_type:
CONTINUE|EXIT|UNDO
condition_value:
SQLSTATE[VALUE]sqlstate_value|condition_name|SQLWARNING|NOT FOUND|SQLEXCEPTION|mysql_error_code
Copier après la connexion

handler_type indique l'erreur Méthode de traitement : Ce paramètre a trois valeurs :

  • CONTINUE : Indique qu'une erreur ne sera pas traitée et que l'exécution continuera

    #🎜🎜 #
  • EXIT : Indique la sortie immédiate lorsqu'une erreur se produit

    #🎜🎜 #
  • UNDO : Indique l'annulation de l'opération précédente après avoir rencontré une erreur
  • #🎜 🎜#

  • condition_value représente le type d'erreur et peut avoir les valeurs suivantes : #🎜 🎜#

SQLSTATE[VALUE]sqlstate_value Une valeur d'erreur de chaîne contenant 5 caractères

  • condition_name représente l'erreur nom de la condition défini par DECLARE CONDITION

  • SQLWARNING 匹配所有 01 开头的 SQLSTATE 错误代码

  • NOT FOUND 匹配所有 02 开头的 SQLSTATE 错误代码

  • SQLEXCEPTION 匹配所有没有被 SQLWARNING 或 NOT FOUND 捕获的 SQLSTATE 错误代码

  • mysql_error_code 匹配数值类型错误代码

  • proc_statement 匹配数值类型错误代码

  • 下面是定义处理程序的几种示例方式:

    // 方法一:捕获sqlstate_value
    DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02'
    SET @info='NOT FOUND';
    // 方法二:使用mysql_error_code 
    DECLARE CONTINUE HANDLER FOR 1146
    SET @info='NOT FOUND';
    // 方法三:先定义条件,然后调用
    ECLARE can_not_find CONDITION FOR 1146;
    DECLARE CONTINUE HANDLER FOR can_not_find
    SET @info='NOT FOUND';
    // 方法四:使用SQLWARNING
    DECLARE EXIT HANDLER FOR SQLWARNING 
    SET @info='ERROR';
    // 方法五:使用NOT FOUND
    DECLARE EXIT HANDLER FOR NOT FOUND
    SET @info='NOT FOUND';
    // 方法六:使用SQLEXCEPTION 
    DECLARE EXIT HANDLER FOR 使用SQLEXCEPTION
    SET @info='ERROR';
    Copier après la connexion

    4|0光标

    在存储过程和函数中,可以使用光标逐条读取查询结果集中可能出现的多条记录。光标必须声明在处理程序之前,声明在变量和条件之后

    1. 声明光标

    DECLARE cursor_name CURSOR
    FOR select_statement;
    Copier après la connexion
    • 参数 cursor_name 表示光标名称

    • 参数 select_statement 表示 SELECT 语句

    下面声明一个名为 cur_employee 的光标

    DECLARE cur_employee CURSOR
    FOR SELECT name,age FROM t_employee;
    Copier après la connexion

    2. 打开光标

    OPEN cursor_name;
    Copier après la connexion
    • 参数 cursor_name 表示光标的名称

    下面打开一个名为 cur_employee 的光标

    OPEN cur_employee;
    Copier après la connexion

    3. 使用光标

    FETCH cursor_name
    INTO var_name[,var_name...]
    Copier après la connexion
    • cursor_name 表示光标的名称

    • var_name 表示将光标中的 SELECT 语句查询出来的信息存入该参数,该参数必须在声明光标之前就定义好

    下面打开一个名为 cur_employee 的光标,将查询的数据存入 emp_name 和 emp_age 这两个变量中

    FETCH cur_employee INTO emp_name,emp_age;
    Copier après la connexion

    4. 关闭光标

    CLOSE cursor_name
    Copier après la connexion
    • cursor_name 表示光标的名称

    5|0流程控制

    1. IF 语句

    IF search_condition THEN statement_list
     [ELSEIF search_condition THEN statement_list]...
     [ELSE statement_list]
    END IF
    Copier après la connexion
    • 参数 search_condition 表示条件判断语句

    • 参数 statement_list 表示不同条件的执行语句

    下面是一个 IF 语句的示例

    IF age>20 THEN SET @count1=@conut1+1;
    	ELSEIF age=20 THEN @count2=@conut2+1;
    	ELSE @count3=@count3+1;
    END IF;
    Copier après la connexion

    2. CASE 语句

    CASE case_value
    	WHEN when_value THEN statement_list
    	[WHEN when_value THEN statement_list]...
    	[ELSE statement_list]
    END CASE
    Copier après la connexion
    • 参数 case_value 表示条件判断的变量

    • 参数 when_value 表示变量的取值

    • 参数 statement_list 表示不同 when_value 的执行语句

    下面的是一个 CASE 语句的示例

    CASE age
    	WHEN 20 THEN SET @count1=@count1+1;
    	ELSE SET @count2=@count2+1;
    END CASE;
    Copier après la connexion

    3. LOOP 语句

    [begin_label:]LOOP
    	statement_list
    END LOOP [end_label]
    Copier après la connexion
    • 参数 begin_label 和参数 end_label 分别表示循环开始和结束的标志,必须相同,而且都可以省略

    • 参数 statement_list 表示需要循环执行的语句

    下面是一个 LOOP 语句的示例

    add_num:LOOP
    	SELECT @count1=@count1+1;
    END LOOP add_num
    Copier après la connexion

    4. LEAVE 语句

    主要用于跳出循环

    LEAVE label
    Copier après la connexion
    • 参数 label 表示循环的标志

    下面是一个 LEAVE 语句的示例

    add_num:LOOP
    	SELECT @count1=@count1+1;
    	IF @count1=100 THEN
    		LEAVE add_num;
    END LOOP add_num
    Copier après la connexion

    5. ITERATE 语句

    也是用于跳出循环,不过是跳出本次循环,直接进入下次循环,类似 Java 的 continue

    ITERATE label
    Copier après la connexion

    ITERATE label

    • 参数 label 表示循环的标志

    下面是一个 ITERATE 语句的示例

    add_num:LOOP
    	SELECT @count1=@count1+1;
    	IF @count1=100 THEN
    		LEAVE add_num;
    	ELSE IF MOD(@count1,3)=0 THEN
    		ITERATE add_num;
    	SELECT * FROM employee;
    END LOOP add_num
    Copier après la connexion

    6. REPEAT 语句

    REPEAT 语句是由条件的控制循环语句,当满足特定条件就会退出循环语句

    [begin_label:]REPEAT
    	statement_list
    	UNTIL search_condition
    END REPEAT [end_label]
    Copier après la connexion
    • 参数 statement_list 表示需要循环执行的语句

    • 参数 search_condition 表示结束循环的条件,满足该条件即结束循环

    下面是一个 REPEAT 语句的示例

    add_num:REPEAT
    	SELECT @count1=@count1+1;
    	UNTIL @count1=100
    END REPEAT add_num
    Copier après la connexion

    7. WHILE 语句

    WHILE 语句也是有条件控制的循环语句,与 REPEAT 语句不同的是,WHILE 语句是当满足条件时执行循环内的语句

    [begin_label:]WHILE search_condition DO
    	statement_list
    END WHILE [end_label]
    Copier après la connexion
    • 参数 statement_list 表示需要循环执行的语句

    • 参数 search_condition 表示结束循环的条件,满足该条件即结束循环

    下面是一个 WHILE 语句的示例

    WHILE @count<100 DO
    	SET @count=@count+1;
    END WHILE;
    Copier après la connexion

    6|0操作存储过程和函数

    1. 调用存储过程

    在 MySQL 中,使用 CALL 语句来调用存储过程

    CALL proc_name([parameter[,...]])
    Copier après la connexion
    • proc_name 是存储过程的名称

    • paramter 是指存储过程的参数

    2. 调用存储函数

    在 MySQL 中,存储函数的使用方法和 MySQL 内部函数是一样的

    3. 使用 SHOW STATUS 语句查看存储过程和函数的状态

    SHOW {PROCEDURE|FUNCTION} STATUS{LIKE &#39;pattern&#39;}
    Copier après la connexion
    • 参数 PROCEDURE 表示查询存储过程,FUNCTION 表示存储函数

    • 参数 LIKE 'pattern' 用来匹配存储过程或函数的名称

    4. 使用 SHOW CREATE 语句查看存储过程和函数的定义

    SHOW CREATE {PROCEDURE|FUNCTION} proc_name
    Copier après la connexion

    5. 从 information_schema.Routine 表中查看存储过程和函数的信息

    SELECT * FROM information_schema.Routine
    WHERE ROUTINE_NAME=&#39;proc_name&#39;
    Copier après la connexion

    6. 修改存储过程和函数

    ALTER {PROCEDURE|FUNCTION} proc_name[characteristic...];
    characteristic:
    	{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}
    |SQL SECURITY{DEFINER|INVOKER}
    |COMMENT &#39;string&#39;
    Copier après la connexion
    • 参数 proc_name 表示存储过程或函数的名称

    • 参数 characteristic 指定存储过程的特性:

      • CONTAINS SQL 表示子程序包含 SQL 语句,但不包含读或写数据的语句

      • NO SQL 表示子程序不包含 SQL 语句

      • READS SQL DATA 表示子程序中包含读数据的语句

      • MODIFIES SQL DATA 表示子程序中包含写数据的语句

    • SQL SECURITY{DEFINER|INVOKER} 指明谁有权限执行:

      • DEFINER 表示只有定义者才能执行

      • INVOKER 表示调用者可以执行

    • COMMENT 'string' 是注释信息

    7. 删除存储过程和函数

    通过 DROP 语句删除存储过程

    DROP PROCEDURE proc_name;
    Copier après la connexion

    通过 DROP FUNCTION 语句删除存储函数

    DROP FUNCTION func_name;
    Copier après la connexion

    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!

    Étiquettes associées:
    source:yisu.com
    Déclaration de ce site Web
    Le contenu de cet article est volontairement contribué par les internautes et les droits d'auteur appartiennent à l'auteur original. Ce site n'assume aucune responsabilité légale correspondante. Si vous trouvez un contenu suspecté de plagiat ou de contrefaçon, veuillez contacter admin@php.cn
    Tutoriels populaires
    Plus>
    Derniers téléchargements
    Plus>
    effets Web
    Code source du site Web
    Matériel du site Web
    Modèle frontal