存储过程
简单存储过程
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;