Mysql存储过程与存储函数

柔情只为你懂 2024-03-08 00:43 131阅读 0赞

#### 文章目录 ####

*   *  1. 简介
     *  2. 存储过程的特点
     *  3. 存储过程操作语法
     *  4. 存储过程变量
     *  5. 其它语法
     *  6. 存储函数

### 1. 简介 ###

存储过程是事先经过编译并存储在数据库中的一段SQL集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。存储过程思想上很简单，就是数据库SQL语言层面的代码封装和重用。

### 2. 存储过程的特点 ###

*  封装和复用
 *  可以接受参数，也可以返回数据
 *  减少网络交互，效率提升

### 3. 存储过程操作语法 ###

*  创建存储过程

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

> 下面创建一个存储过程p1

create procedure p1()
    begin
      select count(*) from 用户表;
    end;

![在这里插入图片描述][6bf566ea3dc04b1ab5ba04a46bf818ed.png]

> 下面调用上面的存储过程

call p1;

![在这里插入图片描述][4346399a402b4c2998d674597b798cdb.png]

*  查看存储过程

**查询指定数据库的存储过程及状态信息**

select * from information_schema.routines  where routine_schema='数据库名称';
    -- 使用
    select * from information_schema.routines  where routine_schema='Mysql_learn';

![在这里插入图片描述][c7d2d7d313de491588e3bedf33a544a9.png]

**查询某个存储过程的定义**

show create procedure 存储过程名称;
    --使用
    show create procedure p1;

![在这里插入图片描述][8f8eeb6f1f3a47e7aa4103d59fd5feb9.png]

*  删除存储过程

drop procedure [if exists] 存储过程名称;

`注意：在命令行中执行创建存储过程的语法时可能会出现问题，因为命令行默认';'就结束sql语句，所以我们要通过关键字delimiter指定sql语句的结束符`

delimiter $$;

### 4. 存储过程变量 ###

*  系统变量

系统变量指的是Mysql服务器提供，不是用户定义的，属于服务器层面。分为全局变量(Global，所有会话有效)和会话变量(SESSION)

**查看系统变量**

-- 查看所有系统变量（默认为session级别）
    show [session|global] variables;
    -- 可以通过like模糊匹配的方式查找变量
    show [session|global] variables like '....';
    -- 查看指定变量的值
    select @@[session|global].系统变量名;

![在这里插入图片描述][eb5fa1721986402c910bbe1178db2cf9.png]

**设置系统变量**

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

*  用户自定义变量

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

**赋值**

set @var_name= expr [, @var_name=expr]...;
    set @var_name :=expr...;
    select @var_name :=expr ....;
    select 字段名 into @var_name from 表名;

**使用**

select @var_name;

set @my_name :="你好";
    select @my_name;

![在这里插入图片描述][ad6d420d533740f1b5c8fcb383c37a26.png]

*  局部变量

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

**声明**

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

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

**赋值**

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

### 5. 其它语法 ###

*  if

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

*  参数

<table> 
 <thead> 
  <tr> 
   <th>类型</th> 
   <th>含义</th> 
   <th>备注</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>IN</td> 
   <td>该类参数作为输入，也就是需要调用时传入值</td> 
   <td>默认</td> 
  </tr> 
  <tr> 
   <td>OUT</td> 
   <td>该类参数作为输出，也就是该参数可以作为返回值</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>INOUT</td> 
   <td>既可以作为输入又可以作为输出</td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

create procedure 名称([in/out/inout 参数名 参数类型])
    begin
    
    end;

create procedure p2(in score int ,out result varchar(10))
    begin
    if score > 18 then
      set result := '优秀';
    elseif score > 60 then 
      set result := '及格';
    else 
      set result := '不及格';
    end if;
    end;
    
    call p2(68,@result);
    select @result;

![在这里插入图片描述][8f858a400e764401b2ffcdf196e1ca17.png]

*  case

**语法一**

case case_value
       when when_value1 then statement_list1;
       .....
       else statement_list;
    end case;

create procedure p3(in season int ,out result varchar(10))
    begin
       case 
    	   when season > 1 and season <= 3 then set result :="第一季度";
    		 when season > 4 and season <= 6  then set result :="第二季度";
    		 when season > 7 and season <= 9 then set result :="第三季度";
    		 when season > 10 and season <= 12 then set result :="第四季度";
    		 else set result :="月份不符合标准";
    	 end case;
    end;
    
    call p3(12,@result);
    select @result;

![在这里插入图片描述][7709e79ef5a747ec8ac7131eda265add.png]

**语法二**

case
       when serch_condition1 then statement_list
       ......
       else statement_list
    end case;

*  while循环

while 条件 DO
    
    end while;

*  repeat循环

> repeat 时有条件的循环控制语句，当满足条件的时候推出循环

repeat
       sql 逻辑
       until 条件
    end repeat

create procedure p4(in n int)
    begin
      declare total int default 0;
    	
      repeat 
    	   set total := total+n;
    	   set n :=n -1;
    	 until n<=0
    	end repeat;
    	select total as '累加和';
    end;
    
    call p4(14);

![在这里插入图片描述][052d92d298c3427682bc1d26788d80f8.png]

*  loop循环

> loop实现简单的循环，如果不在sql逻辑中增加退出循环的条件，可以用其来实现死循环，loop可以配置以下两个语句使用:
> 
>  *  leave：配合循环使用，退出循环
>  *  iterate：必须在循环中，作用时跳出当前循环剩下的语句，直接进入下一个循环

[begin_label:] loop
    
    end loop [end_label];
    leave label; --退出指定标记的循环体
    iterate label; --直接进入下一次循环

CREATE DEFINER=`root`@`localhost` PROCEDURE `p5`(in n int)
    begin
      declare total int default 0;
    	sum: loop
    	   if n<=0 then 
    		      leave sum;
    	   end if;
    		 set total := total+n;
    		 set n :=n-1;
    	end loop sum;
      SELECT total;
    end;
    call p5(14);

![在这里插入图片描述][fd9d4c84091647108eca1c7ad93373af.png]

*  游标

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

**声明**

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

**打开游标**

open 游标名称;

**获取游标记录**

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

**关闭游标**

close 游标名称;

*  条件处理程序

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

declare handler_action handler for condition_value [,condition_value]... statement;
    
    handler_cation
       continue: 继续执行当前程序
       exit: 终止执行当前程序
    condition_value
       sqlstate sqlstate_value:状态码，如02000
       sqlwarning: 所有以01开头的sqlstate代码的简写
       not found: 所有以02开头的sqlstate代码的简写
       sqlexception：所有没有被sqlwarning或not found捕获的sqlstate代码的简写

create procedure p6(in uage int)
    begin
       -- 普通变量要在游标之前声明
       declare u_id int;
    	 declare u_name varchar(100);
    	 declare u_age int;
       declare u_cursor cursor for select 用户ID,姓名,年龄 from 用户表 where 年龄 < uage; 
    	 declare exit handler for SQLSTATE '02000' close u_cursor;
    	 drop table if exists tb_user;
    	 create table if not exists tb_user(
    	    id int primary key auto_increment,
    			user_id int not null,
    			user_name varchar(100),
    			user_age  int
    	 );
    	 open u_cursor;
    	 -- 这里使用条件处理程序来解决游标遍历完后结束循环的问题
    	 while true do
    	    fetch u_cursor into u_id,u_name,u_age;
    			insert into tb_user values(null,u_id,u_name,u_age);
    	 end while;
    end;
    
    call p6(27);

![在这里插入图片描述][0d1cc7f581814d12839071031bb13f55.png]

### 6. 存储函数 ###

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

create function 存储函数名称([参数列表])
    returns type [characteristic...]
    begin 
    
       return ...,
    end;

> characteristic说明：
> 
>  *  deterministic：相同的输入参数总是产生相同的结果
>  *  no sql：不包含sql语句
>  *  reads sql date：包含读取数据的语句，但不包含写入数据的语句

-- 从1到n的累加
    create function fun1(n int)
    returns int deterministic
    begin
      declare total int default 0;
    	while n>0 do
    	  set total :=total+n;
    		set n :=n-1;
    	end while;
    	return total;
    end;

![在这里插入图片描述][cb6866af7fca4d2e9d57f55a3e3d3388.png]

select fun1(14);

![在这里插入图片描述][bceceb85ec154da29028d5c1e0fd732f.png]

[6bf566ea3dc04b1ab5ba04a46bf818ed.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/08/cddbd2174e3643d4be0eabc8db1879de.png
[4346399a402b4c2998d674597b798cdb.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/08/51c4b643ec674e73987a0326fd82b2bf.png
[c7d2d7d313de491588e3bedf33a544a9.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/08/4c0abf4612dd44c7937268ebbcd9a4bf.png
[8f8eeb6f1f3a47e7aa4103d59fd5feb9.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/08/67e9e0fa0e194918bb3244d8881ea56d.png
[eb5fa1721986402c910bbe1178db2cf9.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/08/a8c2acb813cb414d93b71d6590d074b9.png
[ad6d420d533740f1b5c8fcb383c37a26.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/08/69c0e66b6a9748c399786492703828d4.png
[8f858a400e764401b2ffcdf196e1ca17.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/08/22f4177336fa49c49316e62cc2736b04.png
[7709e79ef5a747ec8ac7131eda265add.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/08/5ad03ce565b542959a761e03da108967.png
[052d92d298c3427682bc1d26788d80f8.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/08/2f4ebade44834de49903389116d1573e.png
[fd9d4c84091647108eca1c7ad93373af.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/08/e926dd0bc3e2403cb719c0b3ed629b30.png
[0d1cc7f581814d12839071031bb13f55.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/08/c83199dc55374978bc87230111c9b364.png
[cb6866af7fca4d2e9d57f55a3e3d3388.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/08/57a82b5e88274567885361793035afff.png
[bceceb85ec154da29028d5c1e0fd732f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/08/753e0e58e3ab484c8030f896e7513041.png