存储过程与存储函数的区别及用法及触发器！！！-EW帮帮网

微信交流群：https://732903.hlcode.cn/?id=NK1fWUR

引言：

存储函数和存储过程，作为数据库中的预编译代码块，能够封装复杂的业务逻辑和数据处理流程，使得数据库操作更加简洁、易读和可维护。而触发器，则像是一个智能的守卫，能够在特定事件发生时自动执行预设的操作，确保数据的完整性和一致性。

简单概述：如果只是需要看大概，看上面就可以了，详解可以往下看！！！

One. 略解：

一、存储过程

定义：存储过程是SQL语句和可选控制流语句的预编译集合，以一个名称存储并作为一个单元处理。
特点：
- 可以执行一系列SQL语句。
- 可以从自己的存储过程内引用其它存储过程。
- 可以返回参数，如记录集，参数类型有in、out、inout三种。
- 声明时不需要返回类型。
- 一般作为一个独立的部分来执行（使用EXEC执行）。
使用场景：存储过程通常用于执行一系列操作，如数据清洗、数据转移、批处理任务等。

二、存储函数

定义：存储函数是由一个或多个SQL语句组成的子程序，可用于封装代码以便重新使用。
特点：
- 有许多限制，如不能用临时表，只能用表变量等。
- 可以直接引用返回值，用表变量返回记录集。
- 不能用于执行一组修改全局数据库状态的操作。
- 函数参数只有in类型。
- 需要描述返回类型，且函数中必须包含一个有效的RETURN语句。
- 可以嵌入在SQL中使用，可以在SELECT中调用。
使用场景：存储函数适用于需要在SQL查询中使用的计算和数据转换，可以将其视为一个可复用的查询片段。

三、存储过程和存储函数的区别

标识符：存储过程的标识符是procedure，而存储函数的标识符是function。
返回类型：存储过程可以有多个返回类型（记录集、单个值等），而存储函数通常只能返回一个值或一个表对象。
调用方式：存储过程需要使用CALL语句进行调用，而存储函数可以直接在SELECT语句中调用。
限制：存储过程通常没有太多限制，而存储函数则有许多限制，如不能使用临时表等。

四、存储过程和存储函数的用法

创建：
- 存储过程：使用CREATE PROCEDURE语句创建。
- 存储函数：使用CREATE FUNCTION语句创建。
调用：
- 存储过程：使用CALL语句调用。
- 存储函数：可以在SELECT语句中直接调用，如SELECT function_name(parameter) FROM table_name。
查看和删除：
- 查看：可以使用SHOW PROCEDURE STATUS或SHOW FUNCTION STATUS语句查看存储过程或存储函数的状态信息。
- 删除：可以使用DROP PROCEDURE或DROP FUNCTION语句删除存储过程或存储函数。

综上所述: 存储过程和存储函数在数据库中都扮演着重要的角色，它们各有特点和使用场景。根据实际需求选择合适的对象进行封装和调用，可以提高数据库操作的效率和可维护性。

Two. 详解：

存储过程：

变量

1. 系统变量：

1.1 定义与作用

系统变量是MySQL服务器内部维护的变量，用于存储系统配置信息和状态信息。这些变量通常由MySQL服务器在启动时初始化，并且可以通过SET命令或运行时参数进行修改。

1.2 分类

系统变量分为全局变量和会话变量两种：

全局变量：影响整个MySQL服务器的操作，作用范围为整个服务器实例。使用@@global.前缀进行访问和修改。
会话变量：仅影响当前会话（连接）的操作，作用范围为当前会话。使用@@session.或@@（默认）前缀进行访问和修改。

1.3 示例

-- 查看全局变量
SHOW VARIABLES LIKE 'max_connections';

-- 设置全局变量（需要管理员权限）
SET GLOBAL max_connections = 200;

-- 查看会话变量
SHOW VARIABLES LIKE 'autocommit';

-- 设置会话变量
SET SESSION autocommit = 0;

2. 用户变量：

2.1 定义与作用

用户变量是在MySQL客户端会话中定义的变量，它们在当前会话范围内有效，可以在会话的任何地方被引用和修改。用户变量通常以@符号开头。

2.2 特性

用户变量在会话开始时创建，在会话结束时销毁。
用户变量无需声明即可使用，赋值操作会自动创建变量。
用户变量的值类型是动态的，可以存储各种数据类型。

2.3 示例

-- 赋值用户变量
SET @counter = 1;

-- 使用用户变量
SELECT @counter;

-- 用户变量可以进行算术运算
SET @counter = @counter + 1;
SELECT @counter;

-- 用户变量可以用于存储查询结果
SELECT @max_salary := MAX(salary) FROM employees;
SELECT @max_salary;

3. 局部变量：

3.1 定义与作用

局部变量是在存储过程、函数或触发器中定义的变量，它们的作用范围仅限于定义它们的代码块内。局部变量需要显式声明，并且可以在声明时指定数据类型。

在begin ~~end 之间使用的 ! ! !

3.2 特性

局部变量在存储过程、函数或触发器开始时创建，在结束时销毁。
局部变量只能用于存储单一的数据类型，且需要在DECLARE语句中显式声明。
局部变量的名称不能与同级的局部变量、用户变量或系统变量重复。

3.3 示例

DELIMITER //

CREATE PROCEDURE CalculateSalary(IN base_salary DECIMAL(10,2), OUT total_salary DECIMAL(10,2))
BEGIN
    -- 声明局部变量
    DECLARE tax_rate DECIMAL(5,2) DEFAULT 0.20;
    DECLARE net_salary DECIMAL(10,2);

    -- 计算净工资
    SET net_salary = base_salary * (1 - tax_rate);

    -- 计算总工资（包括税收）
    SET total_salary = base_salary + (base_salary * tax_rate);
END //

DELIMITER ;

-- 调用存储过程
CALL CalculateSalary(5000, @total_salary);
SELECT @total_salary;

参数：

create  procedure Double_Number(inout number double) -- 创建一个名为Double_Number的存储过程，该过程接受一个inout参数number，类型为double
begin
    set number = number * 0.5; -- 将参数number的值设为它自身的一半
end;

set @a = 195; -- 声明一个用户变

call Double_Number(@a); -- 调用Double_Number存储过程，传入用户变量@a作为参数

select @a; -- 查询用户变量@a的值，此时它的值应该是原值的一半

流程控制：：

if 判断

基本语法：

示列：

# 存储过程 - if
create  procedure hz_if()
begin
    declare  i int default 58;
    if i > 80 then
        select '优秀' as '成绩';
    elseif i > 60 then
        select '良好' as '成绩';
    else
        select '还得练' as '成绩';
    end if;
end;

call hz_if();  # 输出： 还得练

case 判断


create  procedure hz_case(in num int)
begin
    declare the_Month varchar(10);
    case
       when  num >=1 and num <=3 then
        set the_Month = '第一季度';
       when  num >=4 and num <=6 then
        set the_Month = '第二季度';
        when  num >=7 and num <=9 then
        set the_Month = '第三季度';
        when  num >=9 and num <=12 then
        set the_Month = '第四季度';
       else
           set the_Month = '月份错误';
    end case;
    select  concat('你输入的月份',num ,'是第',the_Month) as '结果';
end;


call hz_case(1); 输出： 你输入的月份 1 是第一季度

循环语句

where

示列：

计算从1累加到n的值，n为传入的参数值。

create  procedure   hz_while(in num int)
begin
     declare  i int default 0;
     while num > 0 do
         set  i := i + num;
         set  num = num - 1;
     end while;
     select i as '结果';
end;

call hz_while(10);  # 输出 ： 55

repeat

有点类型与java中的循环控制语句 do where（！循环条件）进入这个循环，先循环一次，在判断条件，如果条件满足就退出循环！！！

示列：

create  procedure hz_repeat(in num int)
begin
    declare  i int default 0;
    repeat
         set  i := i + num;
         set  num = num - 1;
    until num <= 0
    end repeat;
end;

call hz_repeat(10); 输出： 55

loop

语法结构：

类似break 和 continue ！！

示列：

1.计算从1累加到n的值，n为传入的参数值，

# 1 - n 累加 循环
create  procedure hz_loop(in num int)
begin
    declare  i int default 0;
    my_loop: loop
    if num <= 0 then
        leave my_loop;
    end if;
    set  i := i + num;
    set  num = num - 1;
    end loop my_loop;
    select i as '结果';
end;

call hz_loop(10);  # 输出： 55

2.计算从1到n之间的偶数累加的值，n为传入的参数值。

# 1 - n  偶数累加 循环

create  procedure hz_loop2(in num int)
begin
    declare  i int default 0;
    my_loop: loop
        if num <= 0 then
            leave my_loop;
        end if;
        if num % 2 = 1 then
            set  num = num - 1;
            leave my_loop;
        end if;
        set  i := i + num;
        set  num = num - 2;
    end loop my_loop;
    select i as '结果';
end;

call hz_loop2(10);  输出: 30

游标

语法定义：

游标(CURSOR) 是用来存储查询结果集的数据类型,在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明、OPEN、FETCH 和 CLOSE，其语法分别如下。

sqlServer里面有个ROWTYPE，可以定义为某table的一行数据类型，接受一行数据但是在mysql中平常我们定义的变量，都是只能去接收单个变量。如果多行多列，一个表的话，就需要通过musql中提供的游标（Cursor）来实现！！！

简单概述：游标相当于缓冲，数据库查询是查出一组，而外部程序接受是接受一个，存在多对一的矛盾。有了游标就可以暂存查处的数据，解决多对一矛盾

这里简单先做个列子：

任务：

# 通过游标来实现
# 查询people 表中年龄小于20以下的学生的姓名和职位并且存储到一张新的表 new_Students （id , name , position）中:

-- 逻辑

-- A . 声明游标，来存储我们查询表的数据集

-- B. 准备，创建表结构

-- C. 开启游标

-- D. 循环获取游标中的记录数据（注意解决游标数据为空的时候，退出循环，下面在讲解）

-- E. 获取的数据插入到新的表中

-- F. 关闭游标

代码：

注意: 规范，定义了变量，这个变量要在游标声明之前，否贼会报错：

[42000][1337] Variable or condition declaration after cursor or handler declaration

欧克：

# 通过游标 来实现
# 查询people 表中年龄小于20以下的学生的姓名 和 职位 并且存储到一张新的表 new_Students 中
create procedure hz_cursor(in age int)
begin
    # 声明变量
    declare name varchar(20);
    declare position varchar(20);
    # 声明游标
    declare  my_cursor cursor for select name,position from people where age < age;

    # 创建新表
    drop table if exists new_Students;
    create table  if not exists new_Students
    (
        id       int auto_increment primary key comment '主键',
        name     varchar(20) not null comment '姓名',
        position varchar(20) not null comment '职位'
    )comment '新学生表';
    # 打开游标
    open my_cursor;
    # 循环游标
    while true do
        fetch  my_cursor into name,position;
        insert into new_Students(name,position) values (name,position);
    end while;
    # 关闭游标
    close my_cursor;
end;

call     hz_cursor(20);

注：获取数据的，按照我们声明游标的时候，获取的字段顺序来，取数据顺序也是按照这个顺序取就完事了！！！逐读取每一行

测试：

我们通过 Call 来调用我们函数：

可以看到新表已经创建了：数据也成功插入了

但是可以发现，又报了异常：【02000】的

这个就是我们上面循环因为我们每次都会读取一行数据，下一次就会读取下一行，当读取到最后一行读取完了，我们没有做处理，当循环下一次的时候，此时游标下一行是没有数据的了，就会报异常了，报了错误，就自然退出了循环，然后就结束了，此时其实后面的逻辑也不会执行了！

此时就可以用我们Mysql中提供的条件处理程序（Handler）来处理了，类似java中 Try Catch 捕获异常来执行相应的处理：

条件处理程序：

条件处理程序(Handler)可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。具体语法为:

我们这里解决上面的问题，只需要定义一个处理程序，因为我们这里循环，当报错的时候，其实数据已经获取完了。我们只需要来捕获这个异常后退出程序，然后关闭游标。如下：

 declare exit  handler for sqlstate '02000' close my_cursor;

  02000是一个这里错误的编码。你要是处理其他错误，就用其他的编码

不用状态码也可以用其他简写的：

SQLSTATE sqlstate_value:状态码，如 02000
SQLWARNING:         所有以01开头的SQLSTATE代码的简写
NOT FOUND:        所有以02开头的SQLSTATE代码的简写
SQLEXCEPTION:        所有没有被SQLWARNING 或 NOT FOUND捕获的SQLSTATE代码的简写

也可以参考myslq 官方文档；

https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.htmlhttps://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html 里面就描述各种状态码对应的含义

如刚刚的02000

ok ,允许可以看见没有报错了，其实死循环还是没有解决，靠异常捕捉后在执行退出

存储函数：

······其实存储过程了解了，存储函数也差不多了了。语法结构，流程控制都是一样的，只是一些基础语法有一些不同。

存储函数是有返回值的存储过程，存储函数的参数只能是IN类型的。具体语法如下:

简单示列：

从1到n的累加

create function hz_fun(num int)
returns int   
begin
    declare  i int default 0;
    while num > 0 do
        set  i := i + num;
        set  num = num - 1;
    end while;
    return i;
end;

报错： [HY000][1418] This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
是因为mysql 8 默认开启了二进制日志：都会被写进二进制日志然后需要我们声明类型deterministic

具体可参考：https://blog.csdn.net/lv_hang515888/article/details/78094889

create function hz_fun(num int)

returns int  deterministic
begin
    declare  i int default 0;
    while num > 0 do
        set  i := i + num;
        set  num = num - 1;
    end while;
    return i;
end;

select hz_fun(10);

输出：

其实存储函数能够实现的，存储过程也能实现，并且存储函数必须要有返回值

触发器：

介绍：

触发器是与表有关的数据库对象，指在 inset/update/delete 之前或之后，触发并自动执行触发器中定义的SOL语句集合。

触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作

当在触发器上发生插入。修改，删除操作的时候，mysql中会自动生成2张特殊大的临时表，new表和old 表，使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容，这与其他的数据库是相似的，（SQL Server 中，就是inserted和deleted 表）。现在触发器还只支持行级触发，不支持语句级触发。

行级触发器：就是我这个触发器影响了多少行，就会执行多少次这个触发器。

语句触发器： 就是我不管你影响了多少行，我就执行一次

语法：

关键字：trigger

创建：

CREATE TRIGGER  'your_tiggerName'
BEFORE/AFTER INSERT/UPDATE/DELETE
ON tbl name FOR EACH ROW --行级触发器
BEGIN
trigger_stmt ;
END;

'your_tiggerName' : 自己拟一个触发器名字

BEFORE/AFTER ： 指定了触发器的触发时机，就是在操作这个表之前执行下面begin -end之间逻辑还是这个操作这个表之后在执行，就先后顺序

INSERT/UPDATE/DELETE ： 指定了触发器的触发事件。就是操作这个表的类型，如果是insert 就是当这个表插入数据的时候，就会触发

ON tblename : ON 关键字后面跟着的是触发器所关联的表的名称（tbl name）。这意味着触发器会在对该表执行指定的操作时触发。

BEGIN ... END; 关键字用来定义一个触发器体的开始和结束。在这个块内，你可以编写多条SQL语句来定义触发器被触发时要执行的操作。

查看：

SHOW triggers

查看当前数据库所有触发器

删除：

DROP TRIGGER [schema name] trigger_name;--如果没有指定 schema name，默认为当前数据库

schema name : 可以指定数据库的名字，没有，就是默认当前数据库

简单案例：

通过触发器记录 people 表的数据变更日志，将变更日志插入到日志表new_peopleInfo中,包含增加,修改,删除

创建一个日志表：

# 创建一个日志表
create  table people_log(
    id int primary key auto_increment comment '主键',
    operation varchar(20) not null comment '操作l类型',
    operation_time datetime not null comment '操作时间',
    operation_id varchar(250) not null comment '操作id',
    operation_content text comment '操作内容'
)comment '操作学生日志表';

insert：

# 创建触发器
create trigger people_trigger
after  insert on people for each row
begin
    insert into people_log(operation,operation_time,operation_id,operation_content)
        values ('insert',now(),new.id,concat('插入学生信息，姓名：',new.name,' 职位：',new.position));
end;

# 查看
show triggers;

# 删除
drop trigger people_trigger;

# 示列
insert people( id,name, age, create_time, gotime, dormitory, tuanw) values ('220510425', '张三', 20, now(), null, 3, 1);

效果：

update：

同理：

# 更新 触发器
create trigger people_update_trigger
    after  update on people for each row
begin
    insert into people_log(operation,operation_time,operation_id,operation_content)
    values ('insert',now(),new.id,concat('更新之前学生信息，姓名：',OLD.name,' 职位：',OLD.position,'|',
        '更新之后学生信息，姓名：',new.name,' 职位：',new.position));
end;

# 更新id 2205100338
update people set name='何政啊' where id = 2205100338;

delete：

删除之后，只能拿到old 的数据了：

# 创建触发器
create trigger people_delete_trigger
    after  delete on people for each row
begin
    insert into people_log(operation,operation_time,operation_id,operation_content)
    values ('insert',now(),OLD.id,concat('插入学生信息，姓名：',OLD.name,' 职位：',OLD.position));
end;

Three. 结尾：

那就结束了 !!! 寒假我们在开卷！！！不对，相遇！！！祝大家工作顺利！，身体健康，天天开心！！！心想事成！！！

存储过程与存储函数的区别及用法及触发器！！！

One. 略解：

一、存储过程

二、存储函数

三、存储过程和存储函数的区别

四、存储过程和存储函数的用法

Two. 详解：

存储过程：

变量

1. 系统变量：

1.1 定义与作用

1.2 分类

1.3 示例

2. 用户变量：

2.1 定义与作用

2.2 特性

2.3 示例

3. 局部变量：

3.1 定义与作用

3.2 特性

3.3 示例

参数：

流程控制：：

if 判断

case 判断

循环语句

where

repeat

loop

游标

条件处理程序：

存储函数：

触发器：

Three. 结尾：

网站公告

今日签到

热门文章

最新发布

存储过程 与 存储函数的区别及用法 及 触发器 ！！！

One. 略解：

一、存储过程

二、存储函数

三、存储过程和存储函数的区别

四、存储过程和存储函数的用法

Two. 详解：

存储过程：

变量

1. 系统变量：

1.1 定义与作用

1.2 分类

1.3 示例

2. 用户变量：

2.1 定义与作用

2.2 特性

2.3 示例

3. 局部变量：

3.1 定义与作用

3.2 特性

3.3 示例

参数：

流程控制：：

if 判断

case 判断

循环语句

where

repeat

loop

游标

条件处理程序：

存储函数：

触发器：

Three. 结尾：

网站公告

今日签到

热门文章

最新发布

存储过程与存储函数的区别及用法及触发器！！！