【MySQL】——存储过程

💻博主现有专栏：

** C51单片机（STC89C516），c语言，c++，离散数学，算法设计与分析，数据结构，Python，Java基础，MySQL，linux，基于HTML5的网页设计及应用，Rust（官方文档重点总结），jQuery，前端vue.js，Javaweb开发，Python机器学习等
🥏主页链接：**

** **Y小夜-CSDN博客

学习推荐：

    在当今这个飞速发展的信息时代，人工智能（AI）已经成为了一个不可或缺的技术力量，它正在逐步改变着我们的生活、工作乃至整个社会的运作方式。从智能语音助手到自动驾驶汽车，从精准医疗到智慧城市，人工智能的应用已经渗透到了我们生活的方方面面。因此，学习和掌握人工智能相关的知识和技能，对于任何希望在这个时代保持竞争力的个人来说，都已经变得至关重要。

    然而，人工智能是一个涉及数学、计算机科学、数据科学、机器学习、神经网络等多个领域的交叉学科，其学习曲线相对陡峭，对初学者来说可能会有一定的挑战性。幸运的是，随着互联网教育资源的丰富，现在有大量优秀的在线平台和网站提供了丰富的人工智能学习材料，包括视频教程、互动课程、实战项目等，这些资源无疑为学习者打开了一扇通往人工智能世界的大门。

    前些天发现了一个巨牛的人工智能学习网站：前言 – 人工智能教程通俗易懂，风趣幽默，忍不住分享一下给大家。

🎯程序设计基础

🎃变量

在MySQL中最常见的变量类型有局部变量和用户自定义变量两种。

1.局部变量

    局部变量一般用在sql语句块中，比如存储过程的begin/end。其作用域仅限于该语句块，在该语句块执行完毕后，局部变量就消失了。

    要定义局部变量必须使用DECLARE来声明，定义的同时可以使用default对局部变量初始化赋值。

    value是给变量提供一个默认值，包含在一个DEFAULT子句中，它的值可以被指定为一个表达式，如果没有DEFAULT子句，初始值为NULL

2.用户自定义变量

    用户自定义变量的声明以“@”开头，形如：@var_name。为了实现不同SQL语句中进行值的传递，可以把一些数值存储在自定义的用户变量中，不同的SQL语句都可以对它进行访问。用户变量在客户端和数据库的连接建立时被定义，当连接断开时，用户变量将会被释放。用户变量无需用DECLARE关键字进行定义，可以直接使用。

    对用户变量赋值有两种方式，一种是直接用"="号，另一种是用":="号。其区别在于使用set命令对用户变量进行赋值时，两种方式都可以使用；当使用select语句对用户变量进行赋值时，只能使用":="方式，因为在select语句中，"="号被看作是比较操作符。

🎃流程控制语句

    在编写存储过程和存储函数时，可以使用流程控制语句对SQL语句进行组织，使其成为符合业务逻辑的代码块。MySQL中常见的流程控制语句主要有：IF语句、CASE语句、LOOP语句、WHILE语句、ITERATE语句、REPEAT语句、leave语句等。

1.IF语句

** ** IF语句可以通过判断一个逻辑条件是TRUE还是FALSE，转去执行相应的语句，它的语法格式如下：

IF expr_condition THEN statement_list

[ELSEIF expr_condition THEN statement_list]

[ELSE statement_list]

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

3.LOOP语句

    LOOP是一个循环语句，用来进行一个语句块的循环操作。LOOP语句并不进行条件判断，会一直执行循环体的语句，如果要退出语句的执行，需要使用LEAVE等语句退出循环。LOOP语句的语法格式如下：

[loop_label:] LOOP Statement_list

END LOOt_listP [loop_label]

4.LEAVE语句

    在循环语句中，当循环条件不满足时，可以使用LEAVE语句跳出循环体。LEAVE语句用于跳出有循环标志的流程控制语句，基本语法结构如下：

LEAVE label

5.ITERATE语句

与LEAVE语句结束整个循环不同，ITERATE语句用于无条件转到语句段的开头处。ITERATE语句的格式如下：

ITERATE lable

该语句类似高级语言中的continue语句

7.WHILE语句

    WHILE语句也用于循环执行一个语句块，但是与REPEAT语句不同，WHILE语句执行时首先判断条件表达式是否为真，如果为真则继续执行循环体内语句，否则直接退出循环体。WHILE语句的格式如下：

[while_lable:] WHILE expr_condition DO

Statement_list

END WHILE [while_lable]

🎃光标

    使用SQL语句进行查询过程中，查询结果将返回很多记录，如果记录量很大时，需要使用光标来对查询结果集中的记录进行逐条读取，它可以对多行数据进行轻松的处理。

1.光标的声明

    在使用光标对结果集中的数据进行处理时，需要首先声明光标，光标的声明必须在声明变量、条件之后，声明处理程序之前。光标的声明格式如下：

DECLARE cursor_name CURSOR FOR select_statement 其中cursor_name表示光标的名字，select_statement代表SELECT语句的内容，返回一个用于创建光标的结果集。

2.光标的使用

    MySQL中使用OPEN关键字来打开光标,打开光标之后就可以使用光标了。其语法的基本形式如下：

OPEN cursor_name ; FETCH cursor_name INTO var_name[,var_name…] ;

3.光标的关闭

    MySQL中使用CLOSE关键字来关闭光标。其语法的基本形式如下：

   CLOSE cursor_name ;

    其中，cursor_name参数表示光标的名称。

🎃异常处理（条件处理）

当程序出错后自动触发的代码。

MySQL允许三种处理器，

• 一种是EXIT处理，遇到错误马上退出。
• 另一种CONTINUE是遇到错误主程序仍然继续运行，
• 第三种是UNDO 遇到错误撤销之前的操作。

🎯存储过程概述

    存储过程是数据库服务器上一组预先编译好的SQL语句的集合，作为一个单元存储在数据库中，可以被应用程序作为一个整体来进行调用。在调用过程中，存储过程可以接收参数，执行后返回参数值。

🎃 存储过程基本概念

    数据库开发人员在进行数据库开发时，为了实现一定的功能，经常会将负责不同功能的语句集中起来而且按照用途分别独立放置，以便能够反复调用，而这些独立放置且拥有不同功能的语句块，称之为“过程”（Procedure）。

     存储过程（Stored Procedure）是一组完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过过程名和给出参数值来调用它们。

🎃存储过程的类型

MySQL的存储程序可分以下四大类:

• (1)存储函数(stored function)。返回一个计算结果，该结果可以用在表达式里。
• (2)存储过程(stored procedure)。不直接返回一个结果，但可以用来完成一般的运算或是生成一个结果集并传递回客户。
• (3)触发器(trigger)。与数据表相关联，当那个数据表被INSERT、DELETE、UPDATE语句修改时，触发器将自动执行。
• (4)事件(event)。根据时间表在预订时刻自动执行。 SELECT @@event_scheduler 查看是否开启事件调度器

🎃存储过程的作用

• (1)存储过程的使用，提高了程序设计的灵活性，增强了SQL语言的功能。
• (2)存储过程把一组功能代码作为单位组件。
• (3)使用存储过程有利于提高程序的执行速度。
• (4)使用存储过程能减少网络访问的负荷。
• (5) 作为一种安全机制，系统管理员可以充分利用存储过程对相应的数据的访问权限的进行限制。

🎯创建和执行存储过程

🎃创建和执行存储过程

CREATE PROCEDURE sp_name ([proc_parameter[,...]])

[characteristic ...] routine_body

其中：

proc_parameter: [ IN | OUT | INOUT ] param_name type

characteristic: LANGUAGE SQL | [NOT] DETERMINISTIC

| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER }

| COMMENT 'string'

routine_body: Valid SQL procedure statement or statements

🎃创建和执行带输入参数的存储过程

创建带输入参数的存储过程

输入参数是指由调用程序向存储过程传递的参数，在创建存储过程语句中定义输入参数，而在执行该存储过程中给出参数相应的值。

🎃创建和执行带输出参数的存储过程

    如果需要从存储过程中返回一个或多个值，可以通过在创建存储过程的语句中定义输出参数来实现。定义输出参数，需要在CREATE PROCEDURE语句中定义参数时在参数名后面指定OUT关键字。

    语法格式如下：

    OUT parameter_name datatype[=default]

🎯管理存储过程

🎃查看存储过程

（1）我们可以用SELECT name FROM mysql.proc WHERE db=’数据库名’，或者SELECT routine_name FROM information_schema.routines WHERE routine_schema=‘数据库名’， 或者SHOW PROCEDURE STATUS WHERE db=‘数据库名’用于显示数据库内存储过程的列表。

（2）使用SHOW CREATE PROCEDURE 数据库.存储过程名;就可以查看当前存储过程的详细。

🎃修改存储过程

修改存储过程是由ALTER PROCEDURE语句来完成的，其语法格式如下： ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...]

characteristic:

{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }   | SQL SECURITY { DEFINER | INVOKER }

| COMMENT 'string’

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

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

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

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

SQL SECURITY { DEFINER |INVOKER } 指明谁有权限来执行。

DEFINER 表示只有定义者自己才能够执行。

INVOKER 表示调用者可以执行。

COMMENT 'string' 表示注释信息。

🎃删除存储过程

存储过程的删除是通过DROP PROCEDURE语句来实现的。其语法格式为：

    DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name IF EXISTS子句是一个MySQL的扩展。如果程序或函数不存储，它防止发生错误。

🎯存储函数

🎃存储过程与存储函数联系与区别

    存储过程与存储函数一样，都是由sql语句和过程式语句所组成的代码片段，并且可以被应用程序和其他sql语句调用。

它们之间的区别在于：

• （1）存储函数不能拥有输出参数，因为存储函数自身就是输出参数；而存储过程可以拥有输出参数。
• （2）可以直接对存储函数进行调用，而不需要使用call语句;而对存储过程的调用，需要使用call语句。
• （3）存储函数中必须包含一条return语句，而这条特殊的sql语句不允许包含于存储过程中。