什么是存储过程？

存储过程可称为过程化SQL语言，是在普通SQL语句的基础上增加了编程语言的特点，把数据操作语句DML和查询语句DQL组织在过程化代码中，通过逻辑判断、循环等操作实现复杂计算的程序语言。

换句话说，存储过程其实就是数据库内置的一种编程语言，这种编程语言也有自己的变量、if语句、循环语句等。在一个存储过程中可以将多条SQL语句以逻辑代码的方式将其串联起来，执行这个存储过程就是将这些SQL语句按照一定的逻辑去执行，所以一个存储过程也可以看作是一组为了完成特定功能的SQL语句集，每一个存储过程都是一个数据库对象，就像table和view一样，存储在数据库当中，一次编译永久有效，并且每一个存储过程都有自己的名字。客户端程序通过存储过程的名字来调用存储过程。

在数据量特别庞大的情况下利用存储过程能达到倍速的效率提升。

存储过程的优缺点

优点：速度快。降低了应用服务器和数据库服务器之间的网络通讯的开销。尤其是数据量庞大的情况下显著。

缺点：移植性差。编写难度大。维护性差。每一个数据库都有自己的存储过程的语法规则，这种语法规则不是通用的。一旦使用了存储过程，则数据库产品很难更换，例如：编写了mysql的存储过程，这段代码只能在mysql中运行，无法在oracle中运行。对于数据库存储过程这种语法来说，没有专业的ide工具（集成开发环境），所以编码速度较低。自然维护成本也会较高。

实际开发中，存储过程还是很少使用的。只有在系统遇到了性能瓶颈，在进行优化的时候，对于大数量的应用来说，可以考虑使用一些。

第一个存储过程

存储过程的创建

create procedure p1()
begin
    select empno,ename from emp;
edn;

存储过程的调用

call p1();

存储过程的查看

查看创建存储过程的语句：

show create procedure p1;

information_schema.ROUTINES是MySQL数据库中一个系统表，存储了所有存储过程、函数、触发器的详细信息，包括名称、返回值类型、参数、创建时间、修改时间等。

select * from information_schema.routines where coutine_name='p1';

information_schema.ROUTINES表中一些重要的列：

·SPECIFIC_NAME：存储过程的具体名称，包括该存储过程的名字，参数列表

·ROUTINE_SCHEMA：存储过程所在的数据库名称

·ROUTINE_NAME：存储过程的名称

·ROUTINE_TYPE：PROCEDURE表示是一个存储过程，FUNCTION表示是一个函数

·ROUTINE_DEFINITION：存储过程的定义语句

·CREATED：存储过程的创建时间

·LAST_ALTERED：存储过程的最后修改时间

·DATA_TYPE：存储过程的返回值类型、参数类型等

存储过程的删除

drop procedure if exists p1;

delimiter命令

在MySQL中，delimiter命令用于改变MySQL解释语句的定界符。MySQL默认使用分号；作为语句的界定符。而使用delimiter命令可以将分号；更改为其他字符，从而可以在sql语句中使用分号。delimiter命令可以改变MySQL数据库系统中查询sql查询语句的分隔符，从而可使一条sql查询语句包含多个sql语句。这样的话，就方便我们在一个语句里面加入多个语句，而且不会错。

MySQL的变量

MySQL中的变量包括：系统变量，用户变量，局部变量

系统变量

MySQL系统变量是指在MySQL服务器运行时控制其行为的参数。这些变量可以被设置为特定的值来改变服务器的默认设置，以满足不同的需求。MySQL系统变量可以具有全局（global）或会话（session）作用域。

·全局作用域是指对所有连接和所有数据库都适用；

·会话作用域是指只对当前连接和当前数据库适用。

查看系统变量

show [global|session] variables;
show [global|session] variables like '';
select @@[global|session.]系统变量名;

注意：没有指定session或global时，默认是session。

设置系统变量

set [global|session] 系统变量名 =值;
set @@[global|session.]系统变量名=值;

注意：无论是全局设置还是会话设置，当MySQL服务重启之后，之前配置都会失效。可以通过修改MySQL根目录下的my.ini配置文件达到永久的效果。（my.ini是MySQL数据库默认的系统级配置文件，默认是不存在的，需要新建，并参考一些资料进行配置。）

用户变量

用户自定义的变量。只在当前会话有效。所有的用户变量'@'开始。

给用户变量赋值

set @name = 'jackson';
set @age:=30;
set @gender:='男',@addr:='北京大兴区';
select @email:='jack@123.com';
select sal into @sal from emp where ename='SMITH';
#建议变量声明时使用：=进行赋值，虽然=也可以。

   读取用户变量的值

select @name,@age,@gender,@addr,@email,@sal;

注意：MySQL中变量不需要说明。直接赋值就行。如果没有声明变量，直接读取该变量，返回null

局部变量

在存储过程中可以使用局部变量。使用declare声明。在begin和end之间有效。

变量的声明

declare 变量名 数据类型 [default...];

变量的数据类型就是表字段的数据类型。

注意：declare通常出现在begin end 之间的开始部分。

变量的赋值

set 变量名 = 值;
set 变量名 := 值;
select 字段名 into 变量名 from 表名...;

例如：以下程序演示局部变量的声明、赋值、读取：

create PROCEDURE p2()
begin
  /*声明变量*/
  declare emp_count int default 0;
  /*声明变量*/
  declare sal double(10,2) default 0.0;
  /*给变量赋值*/
  select count(*) into emp_count from emp;
  /*给变量赋值*/
  select sal:=5000.0;
  /*读取变量的值*/
  select emp_count;
  /*读取变量的值*/
  select sal;
end;



call p2();

IF语句

语法格式：

if 条件 then
...
elseif 条件 then
...
elseif 条件 then
...
else
...
end if;

案例：员工月薪sal，超过1w的属于“高收入”，6k到1w的属于“中收入”，少于6k的属于“低收入”。

create procedure p3()
begin
  declare sal int default 5000;
  declare grade varchar(20);
  if sal>10000 then
  set grade:='高收入';
  elseif sal>=6000 then
  set grade:='中收入';
  else
  set grade:='低收入';
  end if;
  select grade;
end;



call p3();

参数

存储过程的参数包括三种形式：

·in：入参（未指定时，默认是in）

·out：出参

·inout：即使入参又是出参

案例：员工月薪sal，超过1w的属于“高收入”，6k到1w的属于“中收入”，少于6k的属于“低收入”。

create procedure p4(in sal int , out grade varchar(20))
begin
  if sal>10000 then
  set grade:='高收入';
  elseif sal>=6000 then
  set grade:='中收入';
  else
  set grade:='低收入';
  end if;
end;



call p4(5000,@grade);
select @grade;

案例：将传入的工资上调10%

create procedure p5(inout sal int)
begin
  set sal:=sal*1.1;
end;


set @sal:=10000;
call p5(@sal);
select @sal;

case语句

语法格式：

case 值
  when 值1 then
  ..
  when 值2 then
  ..  
  when 值3 then
  ..  
  else
  ..
end case;
case 
  when 条件1 then
  ..
  when 条件2 then
  ..  
  when 条件3 then
  ..  
  else
  ..
end case;

案例：根据不同的月份，输出不同的季节。

create procedure p8(in month int,out season varchar(20))
begin
  case month
    when 3 then set season :='春季';
    when 4 then set season :='春季';
    when 5 then set season :='春季';
    when 6 then set season :='夏季';
    when 7 then set season :='夏季';
    when 8 then set season :='夏季';
    when 9 then set season :='秋季';
    when 10 then set season :='秋季';
    when 11 then set season :='秋季';
    when 12 then set season :='冬季';
    when 1 then set season :='冬季';
    when 2 then set season :='冬季';
  end case ;
end;



call p8(120,@season);
select @season;
create procedure p8(in month int,out season varchar(20))
begin
  case 
    when month between 3 and 5 then set season:='春季';
    when month between 6 and 8 then set season:='夏季';
    when month between 9 and 11 then set season:='秋季';
    when month = 12 or month=1 or month=2 then set season:='冬季';
  end case ;
end;



call p8(120,@season);
select @season;

while循环

语法格式：

while 条件 do
  循环体
end while;

案例：传入一个数字n，计算1~n中所有偶数的和

create procedure mypro(in n int)
begin
  declare sum int default 0;
  while n>0 do
    if n%2=0 then set sum:=sum+n;
    end if;
    set n:=n-1;
  end while;
  select sum;
end;


call mypro(10);
create procedure p9(in n int,out sum int)
begin
  set sum:=0;
  while n> 0 do
    if n%2=0 then set sum:=sum+n;
    end if;
    set n:=n-1;
  end while;
end;


call p9(100,@sum);
select @sum;

repeat循环

语法格式：
 

repeat 
  循环体;
  until 条件
end repeat;

注意：条件成立时结束循环。

案例：传入一个数字n，计算1~n中所有偶数的和。

create procedure mypro(in n int,out sum int)
begin
  set sum:=0;
  repeat
    if n%2=0 then set sum:=sum+n;
    end if;
    set n:=n-1;
    until n<=0
  end repeat;
end;


call mypro(10,@sum);
select @sum;

loop循环

语法格式：

create procedure mypro()
begin 
    declare i int default 0;
  mylp:loop
       set i:=i+1;
       if i=5 then  
         leave mylp;
       end if;
       select i;
    end loop;
end;

案例：输出1234 6789

create procedure p11()
begin
  declare i int default 0;

  myloop:loop
    set i:=i+1;
    if i=5 then
      iterate myloop;
    end if;

    if i=10 then
      leave myloop;
    end if;

    select i;
  end loop;

end;



call p11();

游标cursor

游标cursor可以理解为一个指向结果集中某条记录的指针，允许程序逐一访问结果集中的每条记录，并对其进行逐行操作和处理。

使用游标时，需要在存储过程或函数中定义一个游标变量，并通过DECLARE语句进行声明和初始化。然后，使用OPEN语句打开游标，使用FETCH语句逐行获取游标指向的记录，并进行处理。最后，使用CLOSE语句关闭游标，释放相关资源。游标可以大大地提高数据库查询的灵活性和效率。

声明游标的语法：

declare 游标名称 cursor for 查询语句;

打开游标的语法：

open 游标名称;

通过游标取数据的语法：

fetch 游标名称 into 变量[变量,变量....]

关闭游标的语法：

close 游标名称：

案例：从dept表查询部门编号和部门名，创建一张新表dept2，将查询结果插入到新表中。

create procedure p12()
begin

/*声明变量*/
  declare dept_no int;
  declare dept_name var_char(255);

/*声明游标，注意：声明游标的语句必须放在声明普通变量的后面*/
  declare dept_cursor cursor for select deptno,dname from dept;

/*删除表dept2*/
  drop table if exists dept2;

/*创建表dept2*/
  create table dept2(
    deptno int,
    dname varchar(255));

/*打开游标*/
  open dept_cursor;

/*通过游标获取数据*/
/*fetch dept_cursor into dept_no,dept_name;*/

/*查看以下变量的值*/
/*select dept_no,dept_name;*/

/*使用循环从游标中取数据*/
  while true do
    fetch dept_cursor into dept_no,dept_name;
    select dept_no,dept_name;
    insert into dept2(deptno,dname)  valuse(dept_no,dept_name);
  end while;

/*关闭游标*/
  close dept_cursor;
end;



call p12();

捕捉异常并处理

语法格式：

DECLARE handler_name HANDLER FOR condition_value action_statement

1.hander_name表示异常处理程序的名称，重要取值包括：

        1.CONTINUE:发生异常后，程序不会停止，会正常执行后续的过程。

        2.EXIT：发生异常后，终止存储过程的执行。（上抛）

2.condition_value是指捕获的异常，重要取值包括：

        1.SQLSTATE sqlstate_value,例如SQLSTSTE'02000'

        2.SQLWARNING，代表所有01开头的SQLSTATE

        3.NOT FOUND，代表所有02开头的SQLSTATE

        4.SQLEXCEPTION，代表除了01和02开头的SQLSTATE

3.action_statement是指异常发生时执行的语句，例如：CLOSE cursor_name

给之前的游标添加异常处理机制：

drop procedure if exists p12;

create procedure p12()
begin
  declare dept_no int;
  declare dept_name varchar(255);
  declare dept_cursor cursor for select deptno,dname from dept;

/*通常在这个位置进行异常的处理*/
  declare exit handler for not found close dept_cursor;
 
  drop table if exists dept2;
  create table dept2(
    deptno int,
    dname varchar(255)
);
  open dept_cursor;
  while true do
    fetch dept_cursor into dept_no,dept_name;
    insert into dept2(deptno,dname) values(dept_no,dept_name);
  end while;
  close dept_cursor;
end;


call p12();

       

存储函数

存储函数：带返回值的存储过程。参数只允许是in（但不能写显示的写in）。没有out，也没有inout。

语法格式：

create function 存储函数名称（参数列表） returns 数据类型 [特征]
begin
  --函数体
  return..;
end;

“特征”的可取重要值如下：

·deterministic：用该特征标记该函数为确定性函数（什么是确定性函数？每次调用函数时传同一个参数的时候，返回值都是固定的）。这是一种优化策略，这种情况下整个函数体的执行就会省略了，直接返回之前缓存的结果，来提高函数的执行效率。

·no sql：用该特征标记该函数执行过程中不会查询数据库，如果确实没有查询语句建议使用，告诉MySQL优化器不需要考虑使用查询缓存和优化器缓存来优化这个函数，这样就可以避免不必要的查询消耗产生，从而提高性能。

·reads sql data:用该特征标记该函数会进行查询操作，告诉MySQL优化器这个函数需要查询数据库的数据，可以使用查询缓存来缓存结果，从而提高查询性能；同时MySQL还会针对该函数的查询进行优化器缓存处理。

案例：计算1~n的所有偶数之和
--删除函数
drop function if exists sum_fun;

--创建函数
create function sum_fun(n int)
returns int deterministic
begin
  declare result int default 0;
  while n>0 do
    if n%2=0 then
      set result :=result+n;
    end if;
    set n:=n-1;
    end while;
    return result;
end;

set @result=sum_fun(100);
select @result;

·

触发器

MySQL触发器是一种数据库对象，它是与表相关联的特殊程序。它可以在特定的数据操作（例如插入insert、更新update或删除delete）触发时自动执行。MySQL触发器使数据库开发人员能够在数据的不同状态之间维护一致性和完整性，并且可以为特定的数据库表自动执行操作。

触发器的作用主要有以下几个方面：

1.强制实施业务规则：触发器可以帮助确保数据表中的业务规则得到强制执行，例如检查插入或更新的数据是否符合某些规则。

2。数据审计：触发器可以声明在执行数据修改时自动记日志或审计数据变化的操作，使数据对数据库管理员和sql审计人员更易于追踪和审计。

3.执行特定业务操作：触发器可以自动执行特定的业务操作，例如计算数据行的总数、计算平均值或总合等。

MySQL触发器分为两种类型：BRFORE和AFTER。BEFORE触发器在执行insert、update、delete语句之前执行，而after触发器在执行insert、update、delete语句之后执行。

创建触发器的语法如下：

create trigger trigger_name
before/after insert/update/delete on table_name for each row
begin
--触发器执行的sql语句
end;

其中：

·trigger_name:触发器的名称

·before/after:触发器的类型，可以是before或者after

·insert/update/delete：触发器所监控的DML调用类型

·table_name:触发器所绑定的表名

·for each row：表示触发器在每行受到DML的影响之后都会执行

·触发器执行的sql语句：该语句会在触发器被触发时执行

需要注意的是，触发器是一种高级的数据库功能，只有在必要的情况下才应该使用，例如在需要实施强制性业务规则时。过多的触发器和复杂的触发器逻辑可能会影响查询性能和扩展性。

关于触发器的NEW和OLD关键字：

在MySQL触发器中，NEW和OLD是两个特殊的关键字，用于引用在触发器中受到修改的行的新值和旧值。

·NEW：在触发insert或update操作期间，NEW用于引用将要插入或更新到表中的新行的值。

·OLD：在触发update或delete操作期间，OLD用于引用更新或删除之前在表中的旧行的值。

通俗的讲，NEW是指除法器执行的操作所要插入或更新到当前行中的新数据；而OLD则是指当前行在触发器执行前原本的数据。

在MySQL触发器中，NEW和OLD使用方法是相似的。在触发器中，可以像引用表的其他列一样引用NEW和OLD。例如：可以使用OLD.column_name从旧行中引用列值，也可以使用NEW.column_name从新行中引用列值。

示例：假设有一个名为my_table的表，其中包含一个名为quantity的列。当在该表上执行update操作时，以下触发器会将旧值OLD.quantity累加到新值NEW.quantity中：

create trigger my_trigger
before update on my_table
for each row 
begin
set new.quantity = new.quantity+old.quantity;
end;

在此触发器中，OLD.quantity引用原始行的quantity值（旧值），而NEW.quantity引用更新行的quantity值（新值）。在触发器执行期间，数据行的quantity值将设置为旧值加上新值。

create table oper_log(
    id bigint primary key auto_increment,
    table_name varchar(100) not null comment '操作的哪张表',
    oper_type varchar(100) not null comment '操作类型包括insert delete update',
    oper_time datetime not null comment '操作时间',
    oper_id bigint not null comment '操作的那行记录的id',
    oper_desc text comment '操作描述'
);

/*
触发器
需求：当向dept表当中insert插入数据之后(after)，在oper_log表中记录日志。
*/

drop trigger if exists trigger_dept_insert;

create trigger trigger_dept_insert
/* 触发规则 */
after insert on dept for each row
begin
    /* 一旦触发之后需要执行的SQL语句 */
    insert into oper_log(id,table_name,oper_type,oper_time,oper_id,oper_desc) values
    (null,'dept','insert',now(),new.deptno,
     concat('插入数据：deptno=',new.deptno,',dname=',new.dname,',loc=',new.loc));
end;



/*
触发器
需求：更新dept表之后，在oper_log中记录日志。
*/

drop trigger if exists trigger_dept_update;

create trigger trigger_dept_update
after update on dept for each row
begin
    insert into oper_log(id,table_name,oper_type,oper_time,oper_id,oper_desc) values
    (null, 'dept', 'update', now(), new.deptno,
     concat('更新数据,更新前: deptno=', old.deptno,
            ',dname=', old.dname,
            ',loc=', old.loc,
            ',更新后: deptno=', new.deptno,
            ',dname=', new.dname,
            ',loc=', new.loc));
end;



/*
触发器
需求：删除dept表当中的某条记录之后，记录日志
*/

drop trigger if exists trigger_dept_delete;

create trigger trigger_dept_delete
after delete on dept for each row
begin
    insert into oper_log(id,table_name,oper_type,oper_time,oper_id,oper_desc) values
    (null, 'dept', 'delete', now(), old.deptno,
     concat('删除操作，被删除的数据是: deptno=', old.deptno,
            ',dname=', old.dname,
            ',loc=', old.loc));
end;

# mysql # 数据库
Logo
https://edu.csdn.net/learn/39067/627173?utm_source=2019755004

汇聚全球AI编程工具，助力开发者即刻编程。

更多推荐

cover

当 AI 修图师装上“万能插头“：MCP 如何改变修图工作流

avatar AI编程社区
cover

2026年8月开发者实战指南：ChatGPT Plus / Pro + Codex 如何协同完成需求分析、编码、测试、调试与代码审查

avatar AI编程社区
cover

投资一台B300，部署Claude Opus 5多久才能回本？

avatar AI编程社区