存储过程

存储过程

存储过程是一组为了完成特定功能的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();
		}
	}