学习MYSQL存储过程_MySQL
一、 简介
存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。存储过程是数据库中的一个重要对象,任何一个设计良好的数据库应用程序都应该用到存储过程。
虽然常用术语是存储过程(stored procedure),但MySQL实际上实现了两中类型,除了存储过程外,还有存储函数(stored routine),它们统称为存储例程。
二、基本格式
1、存储过程
CREATE PROCEDURE 过程名 ([过程参数[,...]])
[特性 ...] 过程体
如创建: CEATE PROCEDURE p1 (a INT)
SELECT a;
调用一下:CALL p1(8);
将显示:
+------+
| a |
+------+
| 8 |
+------+
1 row in set (0.00 sec)
2、存储函数
CREATE FUNCTION 函数名 ([函数参数[,...]])
RETURNS 返回类型
[特性 ...] 函数体
如创建:CREATE FUNCTION f1 (x INT)
RETURNS INT
RETURN x; /* 过程函数一次只能返回一个值
调用一下:SELECT f1 (3);
将显示:
+-------+
| f1(3) |
+-------+
| 3 |
+-------+
1 row in set (0.00 sec)
3、过程参数
[ IN | OUT | INOUT ] 参数名 参数类型
4、函数参数
参数名 参数类型
5、返回类型
有效的MySQL数据类型即可
6、过程体/函数体格式如下
BEGIN
有效的SQL语句
END
7、特性(一般不要求)
LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'
8、存储过程中参数的IN,OUT,INOUT类型
存储过程可以接受输入参数,并把参数返回给调用方。不过,对于每个参数,需要声明其参数名、数据类型,还要指定此参数是用于向过程传递信息,还是从过程传回信息,或是二者兼有。
为声明参数的作用,使用如下3个关键字之一:
˜ IN : IN参数只用来向过程传递信息,为默认值。
˜ OUT : OUT参数只用来从过程传回信息。
˜ INOUT : INOUT参数可以向过程传递信息,如果值改变,则可再从过程外调用。
如果仅仅想把数据传给 MySQL 存储过程,那就使用“in” 类型参数;如果仅仅从 MySQL 存储过程返回值,那就使用“out” 类型参数;如果需要把数据传给 MySQL 存储过程,还要经过一些计算后再传回给我们,此时,要使用“inout” 类型参数。
对于任何声明为OUT或INOUT的参数,,当调用存储过程时需要在参数名前加上@符号,这样该参数就可以在过程外调用了。
下面举三个实例:MySQL 存储过程 “in” 参数:跟 C 语言的函数参数的值传递类似, MySQL 存储过程内部可能会修改此参数,但对 in 类型参数的修改,对调用者(caller)来说是不可见的(not visible)。
create procedure pr_param_in( in id int -- in 类型的 MySQL 存储过程参数)begin if (id is not null) then set id = id + 1; end if; select id as id_inner;end;set @id = 10; call pr_param_in(@id); select @id as id_out;mysql> call pr_param_in(@id);+----------+| id_inner |+----------+| 11 |+----------+ mysql> select @id as id_out;+--------+| id_out |+--------+| 10 |+--------+可以看到:用户变量 @id 传入值为 10,执行存储过程后,在过程内部值为:11(id_inner),但外部变量值依旧为:10(id_out)。
MySQL 存储过程 “out” 参数:从存储过程内部传值给调用者。在存储过程内部,该参数初始值为 null,无论调用者是否给存储过程参数设置值。
create procedure pr_param_out( out id int)begin select id as id_inner_1; /* id 初始值为 null*/ if (id is not null) then set id = id + 1; select id as id_inner_2; else select 1 into id; end if; select id as id_inner_3;end;set @id = 10; call pr_param_out(@id); select @id as id_out;mysql> set @id = 10;mysql>mysql> call pr_param_out(@id); /*外部变量不能传给存储过程*/+------------+| id_inner_1 |+------------+| NULL |+------------+ +------------+| id_inner_3 |+------------+| 1 |+------------+ mysql> select @id as id_out; /*过程将改变的值传给外部变量*/+--------+| id_out |+--------+| 1 |+--------+可以看出,虽然我们设置了用户定义变量 @id 为 10,传递 @id 给存储过程后,在存储过程内部,id 的初始值总是 null(id_inner_1)。最后 id 值(id_out = 1)传回给调用者。
MySQL 存储过程 inout 参数跟 out 类似,都可以从存储过程内部传值给调用者。不同的是:调用者还可以通过 inout 参数传递值给存储过程。
drop procedure if exists pr_param_inout; create procedure pr_param_inout( inout id int)begin select id as id_inner_1; -- id 值为调用者传进来的值 if (id is not null) then set id = id + 1; select id as id_inner_2; else select 1 into id; end if; select id as id_inner_3;end;set @id = 10; call pr_param_inout(@id); select @id as id_out;mysql> set @id = 10; mysql>mysql> call pr_param_inout(@id);+------------+| id_inner_1 |+------------+| 10 |+------------+ +------------+| id_inner_2 |+------------+| 11 |+------------+ +------------+| id_inner_3 |+------------+| 11 |+------------+mysql>mysql> select @id as id_out;+--------+| id_out |+--------+| 11 |+--------+从结果可以看出:我们把 @id(10),传给存储过程后,存储过程最后又把计算结果值 11(id_inner_3)传回给调用者。 MySQL 存储过程 inout 参数的行为跟 C 语言函数中的引用传值类似。
通过以上例子:如果仅仅想把数据传给 MySQL 存储过程,那就使用“in” 类型参数;如果仅仅从 MySQL 存储过程返回值,那就使用“out” 类型参数;如果需要把数据传给 MySQL 存储过程,还要经过一些计算后再传回给我们,此时,要使用“inout” 类型参数。
9、声明和设置变量
(1) 声明变量
在存储例程使用局部变量之前,必须声明局部变量,变量声明通过DECLARE语句实现,其原型如下:
DECLARE variable_name TYPE ;
如:DECLARE x VARCHAR(254);
在声明变量时,声明必须放在BEGIN/END块中。此外声明必须在执行该块任何其它语句之前进行。
(2) 设置变量
SET语句用来设置声明的存储例程变量值。其原型如下:
SET variable_name =value;
如下声明和设置变量过程:
DECLARE x INT;
SET x = 155;
也可使用SELECT…..INTO语句设置变量。
如:
SELECT 155 INTO x;
当然,此变量是声明该变量的BEGIN/END块作用范围内的一个局部变量。如果希望在存储例程外使用此变量,需要将其作为OUT变量传递。
10、执行存储例程
执行存储例程一般是通过CALL和SELECT语句来完成的。
三、多语句存储例程
单语句存储例程非常有用,但存储例程的真正功能在于它能够封装和执行多个SQL语句。
下面介绍创建多语句存储例程时常用的语法。
1、BEGIN和END
当创建多语句存储例程时,需要将语句包围在BEGIN/END块中。
此块的原型如下:
BEGIN
statement 1;
statement 2;
……
statement N;
END
注意,块中每条语句必须以分号结尾。
2、条件语句
˜ IF-ELSEIF-ELSE语句
和C语言中if语句相似。
其原型如下:
IF condition1 THEN
statement1;
ELSEIF condition2 THEN
Statement2;
…….
END IF
˜ CASE语句
需要比较一组可能的值时CASE语句很有用。也是一种条件判断语句。
其原型如下:
CASE
WHEN condition1 THEN statement1;
WHEN condition2 THEN statement2;
………
END CASE;
3、迭代
有些任务需要能够重复地执行一组语句。下面介绍能够迭代执行和退出循环的各种方法。
˜ ITERATE语句
执行ITERATE语句将使嵌入该语句的LOOP、REPEAT或WHILE循环返回顶部,并在此执行。
其原型如下:
ITERATE label
˜ LEAVE语句
在得到变量的值或特定任务的结果后,可能希望通过LEAVE命令立即退出循环或BEGIN/END块。
其原型如下:
LEAVE label
˜ LOOP语句
LOOP语句将不断的迭代处理定义在其代码块中的一组语句,直到遇到LEAVE为止。
其原型如下:
[begin_label:] LOOP
Statement_list
END LOOP [end_label]
˜ REPEAT语句
REPEAT语句在操作上几乎与WHILE相同,很想C语言中的DO….WHERE语句。
其原型如下:
REPEAT
Statement_list
UNTIL condition
END REPEAT
˜ WHILE语句
其原型如下:
WHILE condition DO
Statement_list
END WHILE
下面写一个循环语句的存储过程:
DELIMITER $$
DROP PROCEDURE IF EXISTS `yyw`.`p2` $$
CREATE PROCEDURE `yyw`.`p2` ()
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 vs values(v); /*将循环值插入数据表vs中*/
set v=v+1;
if v>=5 then
leave LOOP_LABLE;
end if;
end loop;
END $$
DELIMITER ;
四、从另一个例程中调用例程
DELIMITER //
CREATE PROCEDURE p1()
BEGIN
Statement_list
END//
CREATE PROCEDURE p2()
BEGIN
Statement_list
END//
CREATE PROCEDURE p3()
BEGIN
CALL p1();
CALL p2();
END//
注意:直接采用MySQL的Administrator管理器编辑时,可以直接采用函数文本录入;
但若在脚本中自动导入存储过程或函数时,由于MySQL默认以";"为分隔符,则过程体的每一句都被MySQL以存储过程编译,则编译过程会报错;所以要事先用DELIMITER关键字申明当前段分隔符
用完了就把分隔符还原。 如下所示:
DELIMITER $$
Stored Procedures and Functions
DELIMITER ;
五、删除和查看存储例程
1、删除存储例程
要删除存储例程,可以执行DROP语句。
其原型如下:
DROP (PROCEDURE|FUNCTION) P_name;
2、查看例程状态
其原型如下:
SHOW (PROCEDURE|FUNCTION) STATUS LIKE ‘P_name’
如:
SHOW PROCEDURE STATUS LIKE ‘P3’/G;
注意: 使用/G选项以垂直格式显示输出信息。
3、查看例程的创建语法
通过SHOW CREATE语句可以查看创建特定例程所用的语法。
其原型如下;
SHOW CREATE (PROCEDURE|FUNCTION) Db_name.P_name
六、实例
一般在MySQL Query Browser中更方便的创建存储过程及修改内容。
(1)简单的加法运算
DELIMITER $$
DROP PROCEDURE IF EXISTS `yyw`.`p4` $$
CREATE DEFINER=`yang`@`10.10.19.161` PROCEDURE `p4`(a int,b int)
BEGIN
declare c int; /*声明的变量
if a is null then /*IF语句
set a = 0;
end if;
if b is null then
set b = 0;
end if;
set c = a + b;
select c as sum; /*结果显示c的值
END $$
DELIMITER ;
调用以下:CALL p4(3,4);
将显示:
+------+
| sum |
+------+
| 7 |
+------+
1 row in set (0.00 sec)
(2)存储过程中的循环语句、数据表数据的导入导出及SQL函数的使用
DELIMITER $$
DROP PROCEDURE IF EXISTS `yyw`.`p4` $$
CREATE DEFINER=`yang`@`10.10.19.161` PROCEDURE ‘pro_prime2’(in num int)
BEGIN
declare i,j,x,y int default 0; /*声明变量并默认为0*/
select yywID into j from text1; /*从数据表txte1中字段yywID的值赋给变量j*/
select 'count ',j; /*显示count字符和j的值*/
while i
pp1:while x if j%x=0 then /*if循环语句*/
set y=1;
leave pp1;
else
set x=x+1;
end if;
end while;
if y=1 then
set y=0;
else
set i=i+1;
insert into text2 values(j); /*将j的值插入数据表text2*/
end if;
set j=j+1; /*实现j的自增*/
end while;
END $$
DELIMITER ;
假如原先在数据库中分别建立表text1和text2,text1中有一个字段初始值为3,text2为空;
下面执行一下此存储过程:
mysql> CALL pro_prime2(5);
+--------+------+
| count | j |
+--------+------+
| count | 3 |
+--------+------+
1 row in set (0.00 sec)
mysql> select *from text2;
+-------+
| yywID |
+-------+
| 3 |
| 5 |
| 7 |
| 11 |
| 13 |
+-------+
5 rows in set (0.00 sec)
(3)用存储过程实现计算数据库中某个成绩表总分及平均分,并且调用过程后
能够自动显示基本信息(如学号、姓名、总分、平均分等)。
首先在数据库中建一个成绩表(命名为chengjibiao)如下:
+-----+------+--------+-------+--------+
| NUM | Name | Enlish | Maths | Physis |
+-----+------+--------+-------+--------+
| 1 | 杨业 | 92 | 87 | 96 |
| 2 | 剑锋 | 82 | 98 | 93 |
| 3 | 张美 | 96 | 86 | 94 |
| 4 | 张文 | 76 | 99 | 95 |
| 5 | 叶倩 | 97 | 86 | 88 |
| 6 | 方文 | 87 | 96 | 94 |
| 7 | 李丽 | 97 | 86 | 83 |
| 8 | 贾宇 | 67 | 89 | 77 |
| 9 | 王勃 | 89 | 67 | 75 |
| 10 | 刘三 | 85 | 78 | 95 |
+-----+------+--------+-------+--------+
‚ 用SQL语句写存储过程p1
DELIMITER $$
DROP PROCEDURE IF EXISTS `yyw`.`p1` $$
CREATE DEFINER=`yang`@`10.10.19.161` PROCEDURE `p1`(N int)
BEGIN
declare a int; /* 变量的声明 */
declare b int;
declare c int;
declare d int;
declare e int;
declare f char(100);
declare g decimal(4,2);
set e=1;
create table zongping (NUM int,Name char(255),Enlish int,Maths int,Physis int,Total int,aver decimal( 4,2)); /* 建一个数据表以存放要显示的内容*/
repeat /* 引进一个REPEAT循环,来计算每位学生总分及平均分*/
select Enlish,Maths,Physis,Name into a,b,c,f from chengjibiao where NUM=e;
/* 导出数据库chengjibiao中的三门成绩及姓名,并把它们分别赋给变量a,b,c,f;*/
set d=a+b+c; /*求和*/
set g=(a+b+c)/3; /*求平均分*/
insert into zongping (NUM,Name,Enlish,Maths,Physis,Total,aver) values (e,f,a,b,c,d,g);
/*将学号,姓名,三门成绩,总分,平均分的数据插入新建的数据表zongping中
set e=e+1; /*该条件可结束循环*/
until e=N /*N是调用存储过程时根据学生数目来设定的*/
end repeat;
select *from zongping; /* 以数据表的形式显示运行结果*/
drop table zongping; /*显示结果后删除表,也可不删*/
END $$
DELIMITER ;
l 调用存储过程
CALL P1 (11); /* 因为原成绩表中有10列数据 ,故设N=11,也可根据不同的成绩表另设
显示结果如下:
+------+------+--------+-------+--------+-------+
| Name | Enlish | Maths | Physis | Total | Aver |
+------+------+--------+-------+--------+-------+-------+
| 1 | 杨业 | 92 | 87 | 96 | 275 | 91.67 |
| 2 | 剑锋 | 82 | 98 | 93 | 273 | 91.00 |
| 3 | 张美 | 96 | 86 | 94 | 276 | 92.00 |
| 4 | 姜文 | 76 | 99 | 95 | 270 | 90.00 |
| 5 | 叶倩 | 97 | 86 | 88 | 271 | 90.33 |
| 6 | 方文 | 87 | 96 | 94 | 277 | 92.33 |
| 7 | 李丽 | 97 | 86 | 83 | 266 | 88.67 |
| 8 | 贾宇 | 67 | 89 | 77 | 233 | 77.67 |
| 9 | 王勃 | 89 | 67 | 75 | 231 | 77.00 |
| 10 | 刘三 | 85 | 78 | 95 | 258 | 86.00 |
+------+------+--------+-------+--------+-------+-------+

Outils d'IA chauds

Undresser.AI Undress
Application basée sur l'IA pour créer des photos de nu réalistes

AI Clothes Remover
Outil d'IA en ligne pour supprimer les vêtements des photos.

Undress AI Tool
Images de déshabillage gratuites

Clothoff.io
Dissolvant de vêtements AI

AI Hentai Generator
Générez AI Hentai gratuitement.

Article chaud

Outils chauds

Bloc-notes++7.3.1
Éditeur de code facile à utiliser et gratuit

SublimeText3 version chinoise
Version chinoise, très simple à utiliser

Envoyer Studio 13.0.1
Puissant environnement de développement intégré PHP

Dreamweaver CS6
Outils de développement Web visuel

SublimeText3 version Mac
Logiciel d'édition de code au niveau de Dieu (SublimeText3)

Sujets chauds

Avec le développement rapide des médias sociaux, Xiaohongshu est devenue l'une des plateformes sociales les plus populaires. Les utilisateurs peuvent créer un compte Xiaohongshu pour montrer leur identité personnelle et communiquer et interagir avec d'autres utilisateurs. Si vous avez besoin de trouver le numéro Xiaohongshu d’un utilisateur, vous pouvez suivre ces étapes simples. 1. Comment utiliser le compte Xiaohongshu pour trouver des utilisateurs ? 1. Ouvrez l'application Xiaohongshu, cliquez sur le bouton « Découvrir » dans le coin inférieur droit, puis sélectionnez l'option « Notes ». 2. Dans la liste des notes, recherchez la note publiée par l'utilisateur que vous souhaitez rechercher. Cliquez pour accéder à la page de détails de la note. 3. Sur la page de détails de la note, cliquez sur le bouton « Suivre » sous l'avatar de l'utilisateur pour accéder à la page d'accueil personnelle de l'utilisateur. 4. Dans le coin supérieur droit de la page d'accueil personnelle de l'utilisateur, cliquez sur le bouton à trois points et sélectionnez « Informations personnelles »

L'utilitaire Utilisateurs et groupes locaux est intégré à la gestion de l'ordinateur et est accessible depuis la console ou indépendamment. Cependant, certains utilisateurs constatent que les utilisateurs et groupes locaux manquent dans Windows 11. Pour certaines personnes qui y ont accès, le message suggère que ce composant logiciel enfichable pourrait ne pas fonctionner avec cette version de Windows 10. Pour gérer les comptes d'utilisateurs pour cet ordinateur, utilisez l'outil Comptes d'utilisateurs dans le Panneau de configuration. Le problème a été signalé dans les itérations précédentes de Windows 10 et est généralement dû à des problèmes ou à des oublis de la part de l'utilisateur. Pourquoi les utilisateurs et groupes locaux sont-ils absents dans Windows 11 ? Vous exécutez l'édition Windows Home, les utilisateurs et groupes locaux sont disponibles sur l'édition Professional et supérieure. Activité

Dans les systèmes Ubuntu, l'utilisateur root est généralement désactivé. Pour activer l'utilisateur root, vous pouvez utiliser la commande passwd pour définir un mot de passe, puis utiliser la commande su- pour vous connecter en tant que root. L'utilisateur root est un utilisateur disposant de droits d'administration système illimités. Il dispose des autorisations nécessaires pour accéder et modifier les fichiers, la gestion des utilisateurs, l'installation et la suppression de logiciels et les modifications de la configuration du système. Il existe des différences évidentes entre l'utilisateur root et les utilisateurs ordinaires. L'utilisateur root possède la plus haute autorité et des droits de contrôle plus étendus sur le système. L'utilisateur root peut exécuter des commandes système importantes et modifier des fichiers système, ce que les utilisateurs ordinaires ne peuvent pas faire. Dans ce guide, j'explorerai l'utilisateur root Ubuntu, comment se connecter en tant que root et en quoi il diffère d'un utilisateur normal. Avis

Certains dossiers ne sont pas toujours accessibles en raison des autorisations, et dans le guide d'aujourd'hui, nous allons vous montrer comment accéder aux dossiers utilisateur sur votre ancien disque dur sous Windows 11. Le processus est simple mais peut prendre un certain temps, parfois même des heures, selon la taille du lecteur, alors soyez extrêmement patient et suivez exactement les instructions de ce guide. Pourquoi ne puis-je pas accéder à mes dossiers utilisateur sur mon ancien disque dur ? Les dossiers utilisateur appartiennent à un autre ordinateur, vous ne pouvez donc pas les modifier. Vous ne disposez d'aucune autorisation sur le dossier autre que la propriété. Comment ouvrir les fichiers utilisateur sur un ancien disque dur ? 1. Devenez propriétaire du dossier et modifiez les autorisations. Recherchez l'ancien répertoire utilisateur, cliquez dessus avec le bouton droit et sélectionnez Propriétés. Accédez à "Un

sudo (exécution du superutilisateur) est une commande clé dans les systèmes Linux et Unix qui permet aux utilisateurs ordinaires d'exécuter des commandes spécifiques avec les privilèges root. La fonction de sudo se reflète principalement dans les aspects suivants : Fournir un contrôle des autorisations : sudo réalise un contrôle strict sur les ressources système et les opérations sensibles en autorisant les utilisateurs à obtenir temporairement les autorisations de superutilisateur. Les utilisateurs ordinaires ne peuvent obtenir des privilèges temporaires via sudo qu'en cas de besoin et n'ont pas besoin de se connecter en permanence en tant que superutilisateur. Sécurité améliorée : en utilisant sudo, vous pouvez éviter d'utiliser le compte root lors des opérations de routine. L'utilisation du compte root pour toutes les opérations peut entraîner des dommages inattendus au système, car toute opération incorrecte ou imprudente bénéficiera de toutes les autorisations. et

Microsoft a commencé à déployer KB2 auprès du public en tant que mise à jour facultative pour Windows 503145511H22 ou version ultérieure. Il s'agit de la première mise à jour à activer les fonctionnalités de Windows 11 Moment 4 par défaut, notamment Windows Copilot dans les zones prises en charge, la prise en charge de l'aperçu des éléments du menu Démarrer, le dissociation de la barre des tâches, etc. De plus, il corrige plusieurs bogues de Windows 11, notamment des problèmes de performances potentiels qui provoquaient des fuites de mémoire. Mais ironiquement, la mise à jour facultative de septembre 2023 sera un désastre pour les utilisateurs tentant d'installer la mise à jour, voire pour ceux qui l'ont déjà installée. De nombreux utilisateurs n'installeront pas ce Wi-Fi

De nombreux utilisateurs ont été ajoutés au système Ubuntu. Je souhaite supprimer les utilisateurs qui ne sont plus utilisés. Comment les supprimer ? Jetons un coup d'œil au didacticiel détaillé ci-dessous. 1. Ouvrez la ligne de commande du terminal et utilisez la commande userdel pour supprimer l'utilisateur spécifié. Assurez-vous d'ajouter la commande d'autorisation sudo, comme indiqué dans la figure ci-dessous. 2. Lors de la suppression, assurez-vous d'être dans le répertoire des utilisateurs ordinaires. Je n'ai pas cette autorisation, comme le montre la figure ci-dessous. 3. Une fois la commande de suppression exécutée, comment juger si elle a été réellement supprimée ? Ensuite, nous utilisons la commande cat pour ouvrir le fichier passwd, comme le montre la figure ci-dessous 4. Nous voyons que les informations utilisateur supprimées ne sont plus dans le fichier passwd, ce qui prouve que l'utilisateur a été supprimé, comme le montre la figure ci-dessous. 5. Ensuite, nous entrons dans le fichier personnel

Ce site Web a rapporté le 7 mars que le Dr Zhou Yuefeng, président de la gamme de produits de stockage de données de Huawei, a récemment assisté à la conférence MWC2024 et a spécifiquement présenté la solution de stockage magnétoélectrique OceanStorArctic de nouvelle génération conçue pour les données chaudes (WarmData) et les données froides (ColdData). Zhou Yuefeng, président de la gamme de produits de stockage de données de Huawei, a publié une série de solutions innovantes Source de l'image : Le communiqué de presse officiel de Huawei joint à ce site est le suivant : Le coût de cette solution est 20 % inférieur à celui de la bande magnétique, et son coût est de 20 % inférieur à celui de la bande magnétique. la consommation électrique est 90 % inférieure à celle des disques durs. Selon les médias technologiques étrangers blockandfiles, un porte-parole de Huawei a également révélé des informations sur la solution de stockage magnétoélectrique : le disque magnétoélectronique (MED) de Huawei est une innovation majeure dans le domaine des supports de stockage magnétiques. ME de première génération
