postgresql对于各种游标的使用示例

CREATE OR REPLACE FUNCTION cursor_demo()
  RETURNS refcursor AS
$BODY$
declare
	unbound_refcursor refcursor;
	v_id int;
	v_step_desc varchar(1000);
begin
	open unbound_refcursor for execute 'select id,step_desc from t_runtime_step_log';
	loop
		fetch unbound_refcursor into v_id,v_step_desc;
		
		if found then
			raise notice '%-%',v_id,v_step_desc;
		else
			exit;
		end if;
	end loop;
	close unbound_refcursor;
	raise notice 'the end of msg...';
	return unbound_refcursor;
exception when others then
	raise exception 'error--(%)',sqlerrm;
end;
$BODY$
  LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION cursor_demo1(refcursor)
  RETURNS refcursor AS
$$
begin
	open $1 for select * from t_runtime_step_log;
	return $1;
exception when others then
	raise exception 'sql exception--%',sqlerrm;
end;
$$
  LANGUAGE plpgsql;

begin;
select cursor_demo1('a');
fetch all in a;
--commit;

CREATE OR REPLACE FUNCTION cursor_demo2()
  RETURNS refcursor AS
$$
declare
  bound_cursor cursor for select * from t_runtime_step_log;
begin
  open bound_cursor;
  return bound_cursor;
end;
$$
  LANGUAGE plpgsql;

begin;
select cursor_demo2();
fetch all in bound_cursor;
--commit;

CREATE OR REPLACE FUNCTION cursor_demo3(p_condition integer)
  RETURNS refcursor AS
$BODY$
declare
	bound_param_cursor cursor(id_condition integer) for select * from t_runtime_step_log where id > id_condition;
begin
	open bound_param_cursor(p_condition);
	return bound_param_cursor;
end;
$BODY$
  LANGUAGE plpgsql;

begin;
select cursor_demo3(5);
fetch all in bound_param_cursor;
--commit;


 CREATE OR REPLACE FUNCTION cursor_demo4(p_condition integer)
  RETURNS refcursor AS
$$
declare
	bound_param_cursor cursor for select * from t_runtime_step_log where id > p_condition;
begin
	open bound_param_cursor;
	return bound_param_cursor;
end;
$$
  LANGUAGE plpgsql;

begin;
select cursor_demo4(5);
fetch all in bound_param_cursor;
--commit;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!