数据库：存储过程

前言

存储过程：事先编译好并存储在数据库中的一段SQL语句的集合，可以重复直接调用

1、存储过程的优缺点

优点：1.在创建的时候直接编译，可以提高执行效率
          2.一条语句可能会连接多个表，要对数据库连接好几次存储过程只会连接一次
          3.存储过程安全：数据库管理员可以向访问数据库中存储过程的应用程序授予适当的权限，而不向基础数据库表提供任何权限
缺点：1、可移植性差
          2、对于简单的SQL语句无意义
          3、团队开发后期维护很麻烦
          4、对于开发调试不方便
          5、复杂的业务逻辑存储麻烦

2、存储语法

存储过程

语法： Delimiter 符号
           create procedure  名称（[参数]）
           comment '注释'
           begin
               SQL语句
           end
           符号
           Delimiter

（1）简单存储：

语法： Delimiter 符号
           create procedure  名称（[参数]）
           comment '注释'
           begin
               SQL语句
           end
           符号
           Delimiter

（2）带输入参数的存储过程

语法： Delimiter 符号
           create procedure  名称（in 列名1 数据类型1，in 列名2 数据类型2，...）
           comment '注释'
           begin
               set @v1=列名1
               set @v2=列名2
               SQL语句
           end
           符号
           Delimiter

（3）带输出参数的存储过程

语法： Delimiter 符号
           create procedure  名称（out 列名 数据类型）
           comment '注释'
           begin
               SQL语句 into列名
           end
           符号
           Delimiter
查询：call 名称(@符号)
           select @符号

（4）带输入和输出参数的存储过程
存储过程中，可以使用if then ... else ... end if; 结构来做条件判断语句

语法： Delimiter 符号
           create procedure  名称（in 列名1 数据类型1，out列名2 数据类型2，...）
           comment '注释'
           begin
               if 条件 then
                  set @v = 条件1;
               else
                  set @v = 条件2;
               end if;
               SQL语句
           end
           符号
           Delimiter
查询：call 名称(条件 @符号)
           select @符号

（5）既做输入又做输出参数的存储过程

语法： Delimiter 符号
           create procedure  名称（inout 条件 类型）
           comment '注释'
           begin
               if 条件 then
                  set @v = 条件1;
               else
                  set @v = 条件2;
               end if;
               SQL语句
           end
           符号
           Delimiter
查询：call 名称(条件 @符号)
           select @符号

3、基本用法

-- 简单存储过程
Delimiter @@
create procedure get_text_count()

comment '查询text表中数据条数的存储过程'
begin
select count(*) from text;
insert into lld values(4,'苏苏',23);
end;

@@
DElimiter;
call get_text_count();

#带输入参数的存储过程
Delimiter @@
create procedure text_insert (in id int , in text_name varchar(20),in age int)

begin
-- 声明变量
declare v1 int default 0;
declare v2 varchar(20);
declare v3 int default 0;
-- 把参数赋给变量
set v1 =id;
set v2 = text_name;
set v3 = age;
insert into text(id,name,age) values(v1,v2,v3);
end;

@@
DElimiter;

call lld_insert(6,'雅雅',30);

##带输出参数的存储过程
-- 获取text 表中ID最大值
Delimiter @@

create procedure get_max_id(out maxid int)
begin
select max(id) into maxid from text;
end;

@@
DElimiter;

call get_max_id(@max);
select @max

## 带输入和输出参数的存储过程，当输入是1时，向表中添加ID为1000的数据，如果输入的不是1，则添加一条ID为2000的数据，最后通过输出参数获取最大的id
Delimiter @@
create procedure sp(in p1 int,out p2 int)

begin
    if p1=1 then
    set @v1=1000;
    else 
    set @v1=2000;
    end if;
    
    insert into text (id) values (@v1);
    select max(id) into p2 from text;
end;

@@
DElimiter;

call sp(3,@a);
select @a;

## 即做输入又做输出参数的存储过程
Delimiter @@
create procedure sp1(inout p4 int)

begin
    if p4=4 then
    set @v=400;
    else 
    set @v = 500;
    end if;
    select @v;
end;

@@
DElimiter;

set @pp=4;
call sp1(@pp)