搜索
首页数据库mysql教程学习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         set x=2;
         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 |
+------+------+--------+-------+--------+-------+-------+

声明
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn
您可以使用哪些工具来监视MySQL性能?您可以使用哪些工具来监视MySQL性能?Apr 23, 2025 am 12:21 AM

如何有效监控MySQL性能?使用mysqladmin、SHOWGLOBALSTATUS、PerconaMonitoringandManagement(PMM)和MySQLEnterpriseMonitor等工具。1.使用mysqladmin查看连接数。2.用SHOWGLOBALSTATUS查看查询数。3.PMM提供详细性能数据和图形化界面。4.MySQLEnterpriseMonitor提供丰富的监控功能和报警机制。

MySQL与SQL Server有何不同?MySQL与SQL Server有何不同?Apr 23, 2025 am 12:20 AM

MySQL和SQLServer的区别在于:1)MySQL是开源的,适用于Web和嵌入式系统,2)SQLServer是微软的商业产品,适用于企业级应用。两者在存储引擎、性能优化和应用场景上有显着差异,选择时需考虑项目规模和未来扩展性。

在哪些情况下,您可以选择SQL Server而不是MySQL?在哪些情况下,您可以选择SQL Server而不是MySQL?Apr 23, 2025 am 12:20 AM

在需要高可用性、高级安全性和良好集成性的企业级应用场景下,应选择SQLServer而不是MySQL。1)SQLServer提供企业级功能,如高可用性和高级安全性。2)它与微软生态系统如VisualStudio和PowerBI紧密集成。3)SQLServer在性能优化方面表现出色,支持内存优化表和列存储索引。

MySQL如何处理角色集和碰撞?MySQL如何处理角色集和碰撞?Apr 23, 2025 am 12:19 AM

mySqlManagesCharacterSetsetSandCollat​​ionsyutusututf-8asthEdeFault,允许ConfigurationAtdataBase,table和columnlevels,AndrequiringCarefullageLignmentToavoidMismatches.1)setDefeaultCharactersetTercharactersetEtCollacterSeteTandColletationForAdataBase.2)conformentcollecharactersettersetertersetcollat​​ertersetcollat​​ioncollat​​ion

MySQL中有什么触发器?MySQL中有什么触发器?Apr 23, 2025 am 12:11 AM

MySQL触发器是与表相关联的自动执行的存储过程,用于在特定数据操作时执行一系列操作。1)触发器定义与作用:用于数据校验、日志记录等。2)工作原理:分为BEFORE和AFTER,支持行级触发。3)使用示例:可用于记录薪资变更或更新库存。4)调试技巧:使用SHOWTRIGGERS和SHOWCREATETRIGGER命令。5)性能优化:避免复杂操作,使用索引,管理事务。

您如何在MySQL中创建和管理用户帐户?您如何在MySQL中创建和管理用户帐户?Apr 22, 2025 pm 06:05 PM

在MySQL中创建和管理用户账户的步骤如下:1.创建用户:使用CREATEUSER'newuser'@'localhost'IDENTIFIEDBY'password';2.分配权限:使用GRANTSELECT,INSERT,UPDATEONmydatabase.TO'newuser'@'localhost';3.修正权限错误:使用REVOKEALLPRIVILEGESONmydatabase.FROM'newuser'@'localhost';然后重新分配权限;4.优化权限:使用SHOWGRA

MySQL与Oracle有何不同?MySQL与Oracle有何不同?Apr 22, 2025 pm 05:57 PM

MySQL适合快速开发和中小型应用,Oracle适合大型企业和高可用性需求。1)MySQL开源、易用,适用于Web应用和中小型企业。2)Oracle功能强大,适合大型企业和政府机构。3)MySQL支持多种存储引擎,Oracle提供丰富的企业级功能。

与其他关系数据库相比,使用MySQL的缺点是什么?与其他关系数据库相比,使用MySQL的缺点是什么?Apr 22, 2025 pm 05:49 PM

MySQL相比其他关系型数据库的劣势包括:1.性能问题:在处理大规模数据时可能遇到瓶颈,PostgreSQL在复杂查询和大数据处理上表现更优。2.扩展性:水平扩展能力不如GoogleSpanner和AmazonAurora。3.功能限制:在高级功能上不如PostgreSQL和Oracle,某些功能需要更多自定义代码和维护。

See all articles

热AI工具

Undresser.AI Undress

Undresser.AI Undress

人工智能驱动的应用程序,用于创建逼真的裸体照片

AI Clothes Remover

AI Clothes Remover

用于从照片中去除衣服的在线人工智能工具。

Undress AI Tool

Undress AI Tool

免费脱衣服图片

Clothoff.io

Clothoff.io

AI脱衣机

Video Face Swap

Video Face Swap

使用我们完全免费的人工智能换脸工具轻松在任何视频中换脸!

热工具

SublimeText3 Linux新版

SublimeText3 Linux新版

SublimeText3 Linux最新版

VSCode Windows 64位 下载

VSCode Windows 64位 下载

微软推出的免费、功能强大的一款IDE编辑器

MinGW - 适用于 Windows 的极简 GNU

MinGW - 适用于 Windows 的极简 GNU

这个项目正在迁移到osdn.net/projects/mingw的过程中,你可以继续在那里关注我们。MinGW:GNU编译器集合(GCC)的本地Windows移植版本,可自由分发的导入库和用于构建本地Windows应用程序的头文件;包括对MSVC运行时的扩展,以支持C99功能。MinGW的所有软件都可以在64位Windows平台上运行。

Dreamweaver Mac版

Dreamweaver Mac版

视觉化网页开发工具

DVWA

DVWA

Damn Vulnerable Web App (DVWA) 是一个PHP/MySQL的Web应用程序,非常容易受到攻击。它的主要目标是成为安全专业人员在合法环境中测试自己的技能和工具的辅助工具,帮助Web开发人员更好地理解保护Web应用程序的过程,并帮助教师/学生在课堂环境中教授/学习Web应用程序安全。DVWA的目标是通过简单直接的界面练习一些最常见的Web漏洞,难度各不相同。请注意,该软件中