mysql数据库之存储过程

青旅半醒 2023-10-11 20:58 55阅读 0赞

一、存储过程简介。

存储过程是事先经过编译并存储在数据库中的一段sql语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是也有好处的。

存储过程思想上很简单，就是数据库sql语言层面的代码封装与重用。

二、存储过程的特点

1、封装、重用。

2、可以接受参数，也可以返回数据。

3、减少网络交互 ，效率提升。

三、存储过程的语法。

1、创建存储过程语法。

create procedure 存储过程名称([参数列表])
    begin
      --sql语句
    end;

示例：创建名字为p1的存储过程

create procedure p1()
    begin
      --sql语句
    select count(*) from student;
    end;

2、调用。

call 名称([参数]);

示例： 调用

call p1();

3、查看。

#查询指定数据库的存储过程及状态信息
    select * from information_schema.routine where routine_schema='xxx';
    #查询某个存储过程的定义
    show create procedure 存储过程名称;

示例：

#查询黑马数据库的存储过程信息
    select * from information_schema.routines where routine_schema = 'itcast';
    #查询p1的定义
    show create procedure p1;

4、删除。

#如果存在p1就删除（exists设置一个条件，如果存在就删除的条件）
    drop procedure exists p1;

5、注意：命令行当中，如果执行创建存储过程的sql时，需要通过关键字delimiter指定sql语句的结束符。

#以$$符结束
    delimiter $$;
    #设置;为结束
    delimiter ;

![82579fb42dfd4de3a77ed0c0620e6fa9.png][]

四、变量。

1、系统变量。

系统变量是MySQL服务器提供，不是用户自定义的，属于服务器层面，分为全局变量(global)、会话变量（session）。

a.查看系统变量

#查看所有系统变量
    show [session | global] variables;
    #可以通过like模糊匹配方式查找变量
    show [session | global] variables like '......';
    示例：查看当前会话auto开头的变量
    show [session | global] variables like 'auto%';
    #查看指定变量的值
    select @@[session | global] 系统变量名;
    #示例:查看autocommit变量值。
    select @@session或global autocommit;

b.设置系统变量

#autocommit=0关闭，autocommit=1开启，重启后自动回复默认值
    set [session | global] 系统变量名=值;
    #示例：将当前系统变量自动提交关闭
    set session autocommit=0;
    #示例：将全局系统变量自动提交关闭
    set global autocommit=0;
    #系统变量名=值
    select @@[session | global] 系统变量名=值;

注意：

1、如果没有指定session/global，默认是session，会话变量。

2、mysql服务重新启动之后，所设置的全局参数会失效，要想不失效，可以在/etc/my.cnf中配置。

2、用户自定义变量。

用户自定义变量是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用“@变量名”使用就可以，其作用域为当前连接。

a.赋值语法。

#推荐使用：=
    set @var_name=expr [,@var_name=expr] ...;
    set @var_name:=expr [,@var_name:=expr] ...;
    
    #示例
    set @myname = 'itcast';
    set @myage := '10';
    set @mygender := '男',@myhobby := 'java';

select @var_name:expr [,var_name:expr] ...;
    select 字段名 INTO @var_name from 表名;
    #示例
    select @mycolor := 'red';
    select count(*) into @mycount from tb_user;

b.使用。

select @var_name;
    #示例
    select @myname,@mysge,@mygender,@myhobby;

注意：用户自定义的变量不需要赋值，无需对其进行声明或初始化，只不过获取到的值为null。

3、局部变量。

局部变量是根据需要定义的在局部生效的变量，访问之前，需要declare声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的begin...end块。

#声明局部变量
    declare 变量名 变量类型 [default...];

变量类型就是数据库字段类型：int、bigint、char、varchar、date、time等。

赋值

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

示例

create procedure p1()
    begin
      #声明局部变量
      declare stu_count int default 0;
      #为局部变量赋值
      select count(*) into stu_count from student;
      #展示赋值之后的结果
      select stu_count;
    end;

[82579fb42dfd4de3a77ed0c0620e6fa9.png]: https://img-blog.csdnimg.cn/82579fb42dfd4de3a77ed0c0620e6fa9.png