Postgresql 游标的使用教学

用于教学目的,仅供参考.

1.不带参数的游标

/****************************************************************************************
    改变组织机构关系
****************************************************************************************/
create or replace function organization_move(isrc bigint,itarget bigint)
returns void as
$$
	declare
		vobjectid bigint;
		vIndex integer;
		--定义游标
		vcur cursor
			for select objectid from organization order by sort;
	begin
		vIndex := 1;
		open vcur;
		loop fetch vcur into vobjectid;
			exit when not found;-- 假如没有检索到(主表)数据，结束循环处理
			update organization set sort =vIndex where objectid=vobjectid;
			vIndex := vIndex + 1;
		end loop;
		close vcur;  --用守后一定要close
		update  organization set parentid=itarget,sort=vIndex where objectid=isrc;
	end;
$$ language plpgsql;

2.传入参数的游标

/****************************************************************************************
    改变组织机构关系
****************************************************************************************/
create or replace function organization_move(isrc bigint,itarget bigint)
returns void as
$$
	declare
		vobjectid bigint;
		vIndex integer;
		--定义游标,支持一个输入参数
		vcur cursor( vid bigint) 
			for select objectid from organization where parentid=vid order by sort;
	begin
		vIndex := 1;
		open vcur(itarget);  --从外部传入参数给游标
		loop fetch vcur into vobjectid;
			exit when not found;-- 假如没有检索到(主表)数据，结束循环处理
			update organization set sort =vIndex where objectid=vobjectid;
			vIndex := vIndex + 1;
		end loop;
		close vcur;  --用守后一定要close
		update  organization set parentid=itarget,sort=vIndex where objectid=isrc;
	end;
$$ language plpgsql;