pg数据库游标的使用

1.案例，单表直接用表接收返回值；

DECLARE

  v_campaign      zxdb_ob.ob_campaign;---- zxdb_ob.ob_campaign为数据库中存在表

BEGIN

   

for v_campaign in (select *

              from ob_campaign

             where status = 7) loop

XXXXXX;----业务操作

 end loop;

END；

 

2.案例，多表关联查询：

 

DECLARE

  rec_cc   RECORD;

  cc  CURSOR(p_teamid Integer,p_vcid Integer) FOR select distinct a.campaignid, a.teamid, a.vcid

               from zxdb_ob.ob_cp_team a, zxdb_ob.ob_campaign b

              where a.teamid = p_teamid

                and a.vcid = p_vcid

and a.campaignid = b.campaignid

and b.status < 6;

BEGIN

open cc(p_teamid, p_vcid );

  loop

   FETCH cc INTO rec_cc;

EXIT WHEN NOT FOUND;

 

    XXXXX；----业务操作

end loop;

  close cc;

END;

 

3.单字段：

DECLARE

 v_campaignid integer;

BEGIN

   

for v_campaignid in (select  campaignid

              from ob_campaign

             where status = 7) loop

XXXXXX;----业务操作

 end loop;

END；