存储过程
存储过程是一组为了完成特定功能的SQL语句集,经过预编译后存放在数据库中。由于存储过程,经过变异后存储在数据库中,
执行速度本身就快了,并且减少了和数据库的交互,从而大大提高了效率。
1、创建存储过程
create procedure proc_name
@[参数名] [类型][=default],@[参数名] [类型][=default]
as
begin
.........
end
以上格式还可以简写成:
create proc proc_name
@[参数名] [类型],@[参数名] [类型]
as
begin
.........
end
/*注:“proc_name”为需要创建的存储过程的名字,该名字不可以以阿拉伯数字开头*/
其中,每个参数前要有一个“@”符号,参数类型是除了IMAGE以外的所有数据库数据类型。
例子:
-- 判断是否含有子文件
create proc existChildFolder(@folderId numeric(10,0))
as
begin
if(exists(select * from z_biz_folder where parent_id = @folderId))
return 1
else if(exists(select * from z_biz_fileinfo where folder_id = @folderId))
return 0
else
return -1
end
2、删除存储过程
drop procedure proc_name
注意:不能在存储过程中删除另外一个存储过程,只能调用另外一个存储过程。
3、调用存储过程
exec proc_name
4、存储过程返回值
①、 OUTPUT 返回值获取
数据库中获取:
-- 创建存储过程
create procedure findPersonName(
@id numeric(10,0),
@age tinyint=null OUTPUT
)
as
begin
set @age = (select age from person where id=@id)
print @age
end
-- 数据库中获取output返回值
declare @result tinyint
exec findPersonName 1, @result output
select @result
-- drop 存储过程
drop proc findPersonName
java获取:
/**
* 通过output获取存储过程返回值
*
*/
public static void getProcByOutput(){
// get connection
Connection con = CommonTools.getConnection();
// sql
String sql = "{call findPersonName(?,?)}";
CallableStatement call = null;
Integer result = null;
try {
call = con.prepareCall(sql);
call.setLong(1, 1L);
call.registerOutParameter(2, Types.INTEGER);
call.execute();
// get result
result = call.getInt(2);
log.info("通过output获取存储过程返回值:"+result);
} catch (SQLException e) {
e.printStackTrace();
}
}
②、RETURN 返回值
数据库中获取:
-- 创建存储过程2
create proc findPersonAge(
@id numeric(10,0)
)
as
begin
declare @age int
select @age=age from person where id=@id
print @age
return @age
end
--执行
declare @result int
exec @result = findPersonAge 1
java 中获取:
/**
* 通过return 获取存储过程返回值
*
*/
public static void getResultByReturn(){
// get connection
Connection con = CommonTools.getConnection();
// sql
String sql = "{?=call findPersonAge(?)}";
CallableStatement call = null;
Integer result = null;
try {
call = con.prepareCall(sql);
call.setLong(2, 1L);
call.registerOutParameter(1, Types.INTEGER);
call.execute();
// get result
result = call.getInt(1);
log.info("通过return 获取存储过程返回值:"+result);
} catch (SQLException e) {
e.printStackTrace();
}
}