MySQL学习总结（存储过程、存储函数、流程控制）_数据库存储过程语句

目录

一、存储过程

1、创建存储过程

2、查看存储过程

（1）使用SHOW PROCEDURE STATUS 语句显示存储过程的状态信息

（2）使用SHOW CREATE PROCEDURE 语句显示存储过程的创建信息

3、调用存储过程

4、修改存储过程

5、删除存储过程

二、存储函数

1、创建存储函数

2、查看存储函数

（1）使用 SHOW FUNCTION STATUS语句显示存储函数的状态信息，基本语法格式如下。 

（2）使用 SHOW CREATE FUNCTION 语句显示存储函数的创建信息，基本语法格式如下。

（3）在information_schema.Routines 表中查看存储函数的信息，基本语法格式如下。

3、调用存储函数

4、删除存储函数

三、流程控制

1、判断语句

(1) IF 语句

（2）CASE 语句

2、循环语句

（1） LOOP语句

（2） REPEAT语句

（3） WHILE 语句

3、跳转语句

---

      学习存储过程中的创建存储过程、查看存储过程、调用存储过程、修改存储过程、删除存储过程

      学习存储函数中的创建存储函数、查看存储函数、调用存储函数、删除存储函数

一、存储过程

1、创建存储过程

      在MySQL中，可以使用CREATE PROCEDURE 语句创建存储过程。

创建存储过程的基本语法格式

CREATE PROCEDURE 存储过程名 ([[ IN | OUT | INOUT ] 参数名称 参数类型 ])
[characteristic ...] routine_body

      上述语法格式中，存储过程的参数是可选的，使用参数时，如果参数有多个，参数之间使用逗号分隔。参数和选项的具体含义如下。

• IN：表示输入参数，该参数需要在调用存储过程时传入。
• OUT：表示输出参数，初始值为NULL，它是将存储过程中的值保存到OUT指定的参数中，返回给调用者。
• INOUT：表示输入输出参数，既可以作为输入参数也可以作为输出参数。
• characteristic：表示存储过程中的例程可以设置的特征，可用的特征值如下所示

• routine_body：表示存储过程中的过程体，是包含在存储过程中有效的SQL例程语句，以BEGIN表示过程体的开始，以END表示过程体的结束。如果过程体中只有一条SQL语句，则可以省略BEGIN和END的标志。

      例如，员工管理系统中经常需要查询数据库ems的员工表emp中工资大于指定金额的员工信息，技术人员决定将这个需求编写成存储过程，以提高数据处理的效率

        上述语句创建了一个名为pro_emp的存储过程，该存储过程的输入参数名为tmp_money,使用SELECT语句查询员工表emp中工资大于指定金额tmp_money的员工信息。

       需要说明的是，上述执行语句中DELIMITER// 语句的作用是将MySQL的结束符设置为 // 。因为MySQL默认的语句结束符号为分号(；)，而在创建存储过程时，存储过程体可能包含多条SQL语句，所以为避免分号与存储过程中SQL语句的结束符相冲突，需要使用DELIMITER改变存储过程的结束符。存储过程定义完毕后使用DELIMITER；语句恢复默认结束符。当然，DELIMITER还可以指定其他符号作为结束符，只不过需要注意的是，它与要设定的结束符之间一定要有一个空格，否则设定无效。

2、查看存储过程

      存储过程创建之后，用户可以使用SHOW PROCEDURE STATUS语句和SHOW CREATE  PROCEDURE语句分别显示存储过程的状态信息和创建信息，也可以在数据information_schema 的 Routines数据表中查询存储过程的信息。

（1）使用SHOW PROCEDURE STATUS 语句显示存储过程的状态信息

      SHOW PROCEDURE STATUS语句可以显示存储过程的状态信息，如存储过程名称、类型、创建者及修改日期。

SHOW PROCEDURE STATUS语句显示存储过程状态信息的基本语法格式

SHOW PROCEDURE STATUS [ LIKE '存储过程名' ]

      在上述语法格式中，PROCEDURE 表示存储过程

      例如，显示数据库ems下存储过程pro_emp的状态信息

      上述SHOW PROCEDURE STATUS语句指定显示数据库中名称为pro_emp的存储过程的状态信息；从查询结果可知，SHOW PROCEDURE STATUS语句显示了存储过程pro_emp的名称、修改时间、创建时间和字符集等信息。

（2）使用SHOW CREATE PROCEDURE 语句显示存储过程的创建信息

      使用SHOW CREATE PROCEDURE 语句可以显示存储过程的创建语句等信息，

语法格式

SHOW CREATE PROCEDURE 存储过程名;

      在上述语法格式中，PROCEDURE表示存储过程，存储过程名为显示创建信息的存储过程名称。

      例如，显示数据库ems下存储过程pro_emp的创建信息

       上述SHOW CREATE PROCEDURE 语句指定显示数据库中名称pro_emp的存储过程的创建信息；显示结果中包含了创建pro_emp 存储过程的具体定义和字符集等信息。

3、调用存储过程

      想要使用创建好的存储过程，需要调用对应的存储过程。在MySQL中，存储过程通过CALL语句进行调用。由于存储过程和数据库相关，因此如果想要执行其他数据库中的存储过程，需要在调用时指定数据库名称，

基本语法格式

CALL [ 数据库名称. ] 存储过程名称 ([ 实参列表 ]);

      在上述语法格式中，实参列表传递的参数需要与创建存储过程的形参相对应。当形参被指定为IN时，实参值可以为变量或者是具体的数据；当形参被指定为OUT或INOUT时，调用存储过程传递的参数必须是一个变量，用于接收返回给调用者的数据。

      例如，技术人员想要验证存储过程pro_emp的效果。他调用数据库ems中的存储过程pro_emp，查询数据库ems的员工表emp中工资大于3000的员工信息

       上述语句中，使用CALL语句传入参数3000调用存储过程pro_emp。从执行结果可以看出，工资大于3000的员工有6个。

4、修改存储过程

      在实际开发中，业务需求更改的情况时有发生，这样就不可避免的需求修改存储过程。在MySQL中，可以使用ALTER语句修改存储过程，

其基本语法格式

ALTER PROCEDURE 过程名称 [ characteristic ... ];

      需要注意的是，上述语法格式不能修改存储过程的参数，只能修改存储过程的特征值

      存储过程的例程默认情况是该存储过程的定义者才有权执行。下面根据修改存储过程的语法格式，修改ems中的存储过程pro_emp例程的特征值，将执行存储过程proemp例程的执行权限从定义者修改为调用者，并且添加注释信息。

 可通过查询存储过程的状态语句进行验证

5、删除存储过程

     存储过程被创建后，会一直保存在数据库服务器上，如果当前存储过程需要被废弃，可以对其进行删除。

基本语法格式

DROP PROCEDURE [ IF EXISTS ] 存储过程名称;

       在上述语法格式中，存储过程名称指的是要删除的存储过程的名称；IF EXISTS用于断要删除的存错过程是否存在，如果要删除的存储过程不存在，它可以产生一个警告避免发生错误。IF EXISTS产生的警告可以使用SHOW WARNNGS进行查的。

       例如，技术人员认为存储过程pro_emp还可以优化，想要先删除数据库ems中的存储过程pro_emp

      从上述执行结果的描述可以得出，DROP PROCEDURE语句成功执行。

      可以查询intomation_schema数据库下 Routines表中存储过程pro_emp的记录，验证存储过程pro_emp是否删除成功

二、存储函数

1、创建存储函数

      存储函数和存储过程类似，都是存储在数据库中的一段SOL语句的集合；它们的区别在于存储过程没有直接返回值，主要用于执行操作，而存储函数可以通过RETURN语句返回数据。

创建存储函数的基本语法格式

CREATE FUNCTION func_name([ func_parameter[ ... ] ])
RETURNS type
[ characteristic ... ]
Routine_body

      在上述语法格式中，func_name表示存储函数的名称；fune_parameter表示存储函数的参数列表，其形式和存储过程相同；RETURNS type指定函数返回值的类型；characteristic 参数指定存储函数中例程的特性，该参数的取值与存储过程是一样的；Routine_body表示包含在存储函数中的过程体，是包含在存储函数中有效的SQL例程语句，和存储过程中的SQL语句块一样，可以用BEGIN...END来标识SQL代码的开始和结束。Routine_body中必须包含一个RETURN value语句，其中value的数据类型必须和定义的返回值类型一致。

      例如，员工管理系统中经常需要根据输入员工的姓名返回对应的工资信息，技术人员决定将这个需求编写成存储函数，以提高数据处理的效率。不可直接创建存储函数，否则会出现错误

       上述语句中，func_emp是定义的函数名称，emp_name是函数的形式参数，形式参数后面的是参数的数据类型，RETURNS指定返回值的类型，函数体中使用SELECT语句根据输入员工的姓名查询对应的工资信息，并通过RETURN将查询的结果返回。从执行结果的描述信息可以得出，存储函数已经创建成功。

2、查看存储函数

      存储函数创建之后，用户可以使用SHOW FUNCTION STATUS语句和SHOW CREATE FUNCTION语句分别显示存储函数的状态信息和创建信息，也可以在数据库information_schema的Routines数据表中查询存储函数的信息。

（1）使用 SHOW FUNCTION STATUS语句显示存储函数的状态信息，基本语法格式如下。 

SHOW FUNCTION STATUS [ LIKE '存储函数名' ];

（2）使用 SHOW CREATE FUNCTION 语句显示存储函数的创建信息，基本语法格式如下。

SHOW CREATE FUNCTION 存储函数名 ;

（3）在information_schema.Routines 表中查看存储函数的信息，基本语法格式如下。

SELECT * FROM information_schema.Routines
WHERE FOUTINE_NAME='存储函数名' AND ROUTINE_TYPE='FUNCTION' \G

      从上述3种查看语法格式可以看出，查看存储函数和查看存储过程的区别在于，存储过程的查看使用PROCEDURE关键字，存储函数的查看使用FUNCTION关键字。

       下面以SHOW FUNCTION STATUS语句为例，查看数据库ems中存储函数func_emp的状态信息

       上述SHOW FUNCTION STATUS语句指定获取数据库中名称为func_emp的存储函数的状态信息；查看结果中显示了fune_emp存储函数的修改时间、创建时间和字符集等信息。

3、调用存储函数

       和存储过程一样，如果想让创建的存储函数在程序中发挥作用，需要调用才能使其执行

基本语法格式

SELECT [ 数据库名 .] 函数名1 (实参列表)[, 函数名2 (实参列表) ...];

       在上述语法格式中，数据库名是可选参数，指调用存储函数时函数所属的数据库的名称，如不指定则默认为当前数据库；实参列表中的值须和定义存储函数时设置的类型一致。

      例如，调用ems中的存储函数func_emp

 上述语句在调用函数func_emp时传递了参数 “ 刘一 ” ，函数执行后返回了数据表中刘一对应的工资信息。

4、删除存储函数

      在MySQL中，如果需要删除存储函数，可以使用DROP FUNCTION 语句。

语法格式

DROP FUNCTION [ IF EXISTS ] 函数名称 ;

      在上述语法格式中，IFEXISTS是可选参数，用于防止因删除不存在的存储函数而引发错误。

      例如，技术人员认为存储函数func _emp还可以优化，想要先删除数据库ems中的存储函数func_emp

      从上述执行结果的描述可以得出，存储函数已经成功删除。

三、流程控制

1、判断语句

(1) IF 语句

      IF 语句可以对条件进行判断，根据条件的真假来执行不同的语句，

语法格式

IF 条件表达式1 THEN 语句列表
     [ ELSEIF 条件表达式2 THEN 语句列表 ]...
     [ ELSE 语句列表 ]
END IF

       在上述语法格式中，当条件表达式1结果为真时，执行THEN子句后的语句列表；当条件表达式1结果为假时，继续判断条件表达式2，如果条件表达式2结果为真，则执行对应的THEN子句后的语句列表，以此类推；如果所有的条件表达式结果都为假，则执行ELSE子句后的语句列表。需要注意的是，每个语句列表中至少必须包含一个SQL语句。

      例如，员工管理系统中经常需要根据输入的员工姓名返回对应的员工信息，如果输入为空，则显示输入的值为空；如果输入的员工姓名在员工表中不存在，则显示员工不存在。

       在上述代码中，创建了一个存储过程proc_isnull ，其中IF 语句根据输入参数e_name的值进行判断，显示不同的内容。

     例如：下面调用存储过程proc_isnull 

      从上述执行结果可以看出，调用存储过程proc _isnull时，如果传递的参数为NULL，则显示输入的值为空；如果输入的员工姓名在员工表中不存在，则显示员工不存在；如果员工姓名在员工表中存在，则显示员工对应的信息。

（2）CASE 语句

      CASE 语句也可以对条件进行判断，它可以实现比 IF 语句更复杂的条件判断。

语法格式1

# 语法格式1
CASE 表达式
    WHEN 值1 THEN 语句列表
    [ WHEN 值2 THEN 语句列表 ]...
    [ ELSE 语句列表 ]
END CASE

      从上述语法格式可以看出，CASE语句中可以有多个WHEN子句，CASE后面的表达式的结果决定哪一个WHEN子句会被执行。当WHEN子句后的值与表达式结果值相同时，执行对应的THEN关键字后的语句列表；如果所有WHEN子句后的值都和表达式结果值不同，则执行ELSE后的语句列表。END CASE表示CASE语句的结束。

​# 语法格式2
CASE 表达式
    WHEN 条件表达式1 THEN 语句列表
    [ WHEN 条件表达式2 THEN 语句列表 ]...
    [ ELSE 语句列表 ]
END CASE

      在上述语法格式中，当WHEN子句后的条件表达式结果为真时，执行对应THEN后的语句列表；当所有WHEN子句后的条件表达式都不为真时，执行ELSE后的语句列表。

      例如，员工管理系统中经常需要根据输入的员工工资返回对应的工资等级，如果工资大于或等于5000元，则返回高薪资；如果小于5000元并且大于或等于4000元则返回中等薪资；如果小于4000元并且大于或等于2000元则返回低薪资；其他金额则返回不合理薪资。技术人员决定将这个需求编写成存储函数

      在上述代码中，创建了一个存储函数fune_level，其中使用CASE语句判断参数esal的值对应的等级。调用存储函数func_levell时，如果参数esal的值大于或等于5000元，则返回高薪资；如果小于5000元并且大于或等于4000元则返回中等薪资；如果小于4000元并且大于或等于2000元则返回低薪资；其他金额则返回不合理薪资。

      调用语句为

select 存储函数();

2、循环语句

（1） LOOP语句

      LOOP语句通常用于实现一个简单的循环

基本语法格式

[ 标签 : ] LOOP
       语句列表
END LOOP [ 标签 ];

      在上述语法格式中，标签是可选参数，用于标志循环的开始和结束。标签的定义只需要符合MySQL标识符的定义规则即可，但两个位置的标签名称必须相同。LOOP会重复执行语句列表，因此在循环时务必给出结束循环的条件，否则会出现死循环。LOOP语句本身没有停止语句，如果要退出LOOP循环，需要使用LEAVE语句。

      例如，在存储过程中实现0-9的整数的累加计算

       上述程序定义了一个存储过程proc_sum。在存储过程proc_sum中，定义了局部变量i和sum并分别设置默认值0，然后在LOOP语句中判断i的值是否大于或等于10。如果是，则输出i和sum当前的值并退出循环；如果不是，则将i的值累加到sum变量中并对i进行自增1，然后再次执行LOOP语句中的内容。
      例如：存储过程proc_sum通过LOOP语句实现了0-9的累加计算，下面调用它查看循环后i和sum的值

      从上述结果执行可以看出，循环后i的值为10，sum的值为45.可以看出当i等于10时，不再对sum进行累加，因此得出sum的值是0~9整数的累加。

（2） REPEAT语句

      REPEAT语句用于循环执行符合条件的语句列表，每次循环时，都会对语句中的条
在表达式进行判断，如果表达式返回值为 TRUE，则结束确环，否朝重复我行语不中的
语句。

基本语法格式

[ 标签 : ] REPEAT
        语句列表
     UNTIL 条件表达式
END REPEAT [ 标签 ]

      在上述语法格式中，程序会无条件地先执行一次REPEAT语句中的语句列表，然后再判断UNTIL后的条件表达式的结果是否为TRUE。如果为TRUE，则结束循环；如果不为TRUE，则继续执行语句列表。

      例如，在存储过程内实现0～10奇数的累加计算

      上述程序定义了一个存储过程proc_odd。在存储过程proc_odd中，定义了局部变量i和sum并分别设置默认值0，然后在REPEAT的语句列表中判断i的值是否为奇数。如果是，则将i的值累加到sum变量中，结束判断后对i进行自增1。语句列表执行完之后，判断i是否大于10，如果是，则结束循序；如果不是，则继续语句列表的执行。

      例如：存储过程proc_odd，通过REPEAT语句实现了0～10奇数的累加计算，下面调用它查看循环后i和sum的值

      上述执行结果可以看出，REPEAT 循环结束后i的值为11，0~10的奇数累加和为25。

（3） WHILE 语句

      WHILE语句用于循环执行符合条件的语句列表，但与REPEAT语句不同的是，WHILE语句是先判断条件表达式，再根据判断结果确定是否执行循环内的语句列表。

基本语法格式

[ 标签 : ] WHILE 条件表达式 DO
    语句列表
END WHILE [ 标签 ]

      在上述语法格式中，只有条件表达式为真时，才会执行DO后面的语句列表。语句列表执行完之后，再次判断条件表达式的结果，如果结果为真，继续执行语句列表；如果结果为假，则退出循环。在使用WHILE循环语句时，可以在语句列表中设置循环的出口，以防出现死循环的现象。

       例如，在存储过程内实现0~10偶数的累加计算

       上述程序定义了一个存储过程proc_even。在 proc_even中，定义了局部变量i和sum并分别设置默认值0，然后在WHILE 后判断1是否小于或等于10，如果是，则执行DO后面的语句列表。语句列表中先判断i是否是偶数，如果是偶数，则将i的值累加到sum变量中，然后结束IF语句并对i进行自增1，接着再次对WHILE后的条件语句进行判断，当i的值大于10时，结束循环。

      例如：存储过程proc_even通过WHILE语句实现了0-10偶数的累加计算，下面调用它查看循环后i和sum的值

       从上述执行结果可以看出，WHILE循环结束后i的值为11，0~10偶数的累加和为30。

3、跳转语句

      跳转语句用于实现执行过程中的流程跳转。MySQL中常用的跳转语句有LEAVE语句和ITERATE 语句

基本语法格式

{ITERATE | LEAVE} 标签名;

      在上述语法格式中，ITERATE语句用于结束本次循环的执行，开始下一轮循环的执行；而LEAVE语句用于终止当前循环，跳出循环体。

      例如：为了能更好地理解LEAVE语句和ITERATE语句的使用及区别，下面通过计算5以下的正偶数的累加和进行演示

       上述程序定义了一个存储过程proe_jump。在 proe_jump中，首先定义了局部变量num并设置num的默认初始值为0；接着执行LOOP语句，LOOP语句的语句列表中执行的顺序为先设置num的值自增2，然后判断num的值是否小于5。如果是，则使用ITERATE语句结束当前顺序并执行下一轮循环；如果不是，则查询num的值并跳出my_loop循环。

      例如：存储过程proe _jump通过LEAVE语句和ITERATE语句控制循环的跳转，下面调用它查看循环后num的值

      从上述执行结果可以看出，LOOP循环结束后num的值为6。