存储过程&&游标

存储过程

简单存储过程

drop procedure if exists sp//
create procedure sp() select 1 //
call sp()//

带输入参数的存储过程

drop procedure if exists sp1 //

create procedure sp1(in p int)
comment 'insert into a int value'
begin
/* 定义一个整形变量 */
declare v1 int;

/* 将输入参数的值赋给变量 */
set v1 = p;

/* 执行插入操作 */
insert into test(id) values(v1);
end
//
/* 调用这个存储过程 */
call sp1(1)//
/* 去数据库查看调用之后的结果 */
select * from test//

带输出参数的存储过程

drop procedure if exists sp2 //
create procedure sp2(out p int)
begin
select max(id) into p from test;
end
//
/* 调用该存储过程，注意：输出参数必须是一个带@符号的变量 */
call sp2(@pv)//
/* 查询刚刚在存储过程中使用到的变量 */

select @pv//

带输入和输出参数的存储过程

drop procedure if exists sp3 //
create procedure sp3(in p1 int , out p2 int)
begin

if p1 = 1 then
/* 用@符号加变量名的方式定义一个变量，与declare类似 */
set @v = 10;
else
set @v = 20;
end if;

/* 语句体内可以执行多条sql，但必须以分号分隔 */

insert into test(id) values(@v);
select max(id) into p2 from test;

end
//

/* 调用该存储过程，注意：输入参数是一个值，而输出参数则必须是一个带@符号的变量 */
call sp3(1,@ret)//

select @ret//

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

drop procedure if exists sp4 //
create procedure sp4(inout p4 int)
begin
if p4 = 4 then
set @pg = 400;
else
set @pg = 500;
end if; 

select @pg;

end//

call sp4(@pp)//

/* 这里需要先设置一个已赋值的变量，然后再作为参数传入 */
set @pp = 4//
call sp4(@pp)//

CREATE DEFINER=`root`@`localhost` PROCEDURE `addDep`(in depName varchar(32),in parentId int,in enabled boolean,out result int,out result2 int)
begin
  declare did int;
  declare pDepPath varchar(64);
  insert into department set name=depName,parentId=parentId,enabled=enabled;
  select row_count() into result;
  select last_insert_id() into did;
  set result2=did;
  select depPath into pDepPath from department where id=parentId;
  update department set depPath=concat(pDepPath,'.',did) where id=did;
  update department set isParent=true where id=parentId;
end */$$
DELIMITER ;

动态SQL中使用存储过程

<!--注明statementType="CALLABLE"表示调用存储过程-->
<!--传入传出参数要注明mode=IN/OUT 并要注明jdbcType，返回参数要注明对应的resultMap-->
<select id="addDep" parameterType="com.zsl.baiweiserver.pojo.Department" statementType="CALLABLE">
  call addDep(
  #{name,jdbcType=VARCHAR,mode=IN},
  #{parentid,jdbcType=INTEGER,mode=IN},
  #{enabled,jdbcType=BOOLEAN,mode=IN},
  #{result,jdbcType=INTEGER,mode=OUT},
  #{id,jdbcType=INTEGER,mode=OUT}
  )
</select>

CREATE DEFINER=`root`@`localhost` PROCEDURE `deleteDep`(in did int,out result int)
begin
  declare ecount int;
  declare pid int;
  declare pcount int;
  declare a int;
  select count(*) into a from department where id=did and isParent=false;
  if a=0 then set result=-2;
  else
  select count(*) into ecount from employee where departmentId=did;
  if ecount>0 then set result=-1;
  else 
  select parentId into pid from department where id=did;
  delete from department where id=did and isParent=false;
  select row_count() into result;
  select count(*) into pcount from department where parentId=pid;
  if pcount=0 then update department set isParent=false where id=pid;
  end if;
  end if;
  end if;
end */$$
DELIMITER ;

动态SQL中使用存储过程

<select id="deleteDepById" parameterType="com.zsl.baiweiserver.pojo.Department" statementType="CALLABLE">
  call deleteDep(#{id,jdbcType=INTEGER,mode=IN},#{result,jdbcType=INTEGER,mode=OUT})
</select>

游标

• 语法：
o 1.定义游标：declare 游标名 cursor for select语句;
o 2.打开游标：open 游标名;
o 获取结果：fetch 游标名 into 变量名[,变量名];
o 关闭游标:close 游标名;

create procedure p1()
begin
    declare id int;
    declare name varchar(15);
    -- 声明游标
    declare mc cursor for select * from class;
    -- 打开游标
    open mc;
    -- 获取结果
    fetch mc into id,name;
    -- 这里是为了显示获取结果
    select id,name;
    -- 关闭游标
    close mc;
    
end;

create procedure p2()
begin
    declare id int;
    declare name varchar(15);
    -- 声明游标
    declare mc cursor for select * from class;
    -- 打开游标
    open mc;
    -- 获取结果
    loop  -- 循环，将表的内容都转移到class2中
    fetch mc into id,name;
    -- 这里是为了显示获取结果
    insert into class2 values(id,name);
    -- 关闭游标
    end loop;
    close mc;
    
end;

使用游标
• 游标每一次fetch都是获取一行结果，可以使用变量来获取fetch到的每一列的值

create procedure p3()
begin
    declare id int;
    declare name varchar(15);
    declare flag int default 0;
    -- 声明游标
    declare mc cursor for select * from class;
    declare continue handler for not found set flag = 1;
    -- 打开游标
    open mc;
    -- 获取结果
    l2:loop 
    
    fetch mc into id,name;
    if flag=1 then -- 当无法fetch会触发handler continue
        leave l2;
    end if;
    -- 这里是为了显示获取结果
    insert into class2 values(id,name);
    -- 关闭游标
    end loop;
    close mc;
    
end;

call p3();-- 不报错
select * from class2;