Oracle 游标使用全解

另外，下面再附加一个我在项目中编写使用的一个PL/SQL程序块中使用游标的例子：

declare

  --cursor v_possvcfmv Is       select * from ttg_pos_svcfmv_mike order by id;

cursor v_possvcfmv Is
        SELECT m.id,m.status,m.PRODUCTID,decode(c.servicetermid,null,0,c.servicetermid) servicetermid,b.parano,m.tp,m.icv,b.paracode,m.l3direct,m.l3indirect,m.l2direct,m.l2indirect,m.l1indirect,m.privateprice
        FROM ttg_pos_svcfmv_mike m
        LEFT OUTER JOIN  (SELECT * FROM ttg_dictionary WHERE paraid = 75)b ON m.OFFERINGTYPE=b.paracode 
        LEFT   OUTER JOIN TTG_SERVICE_POSMAP c ON     m.productid = c.productid  AND    b.parano = c.OFFERINGID
        where m.status=‘1‘ and m.id in (2616,2617)
        order by m.id ;

s_row v_possvcfmv%rowtype;
begin

      open v_possvcfmv;
           fetch v_possvcfmv into s_row;
           while v_possvcfmv%found loop
               
               /*
               SPA_POSSVCFMV(s_row.productid,s_row.servicetermid,s_row.parano,s_row.tp,s_row.icv,s_row.paracode,‘‘,s_row.l3direct,‘1‘,s_row.l3indirect,s_row.l2direct,s_row.l2indirect,s_row.l1indirect,s_row.privateprice,‘999999999‘);

               update ttg_pos_svcfmv_mike set status=‘1‘ where id=s_row.id;
*/

               a_jack_Test(‘SPA_POSSVCFMV(‘ || s_row.productid || ‘,‘ || s_row.servicetermid || ‘,‘‘‘ || s_row.parano || ‘‘‘,‘ || 
               s_row.tp || ‘,‘ || s_row.icv || ‘,‘‘‘ || s_row.paracode || ‘‘‘,‘‘‘‘,‘ || 
               s_row.l3direct || ‘,‘‘1‘‘,‘ || s_row.l3indirect || ‘,‘ || s_row.l2direct || ‘,‘ || s_row.l2indirect || ‘,‘ || s_row.l1indirect || ‘,‘ || s_row.privateprice || ‘,‘‘1‘‘‘ ||
               ‘,‘‘999999999‘‘)‘
               );
               dbms_output.put_line(‘SPA_POSSVCFMV(‘ || s_row.productid || ‘,‘‘999999999‘‘)‘
               );
               
               
           fetch v_possvcfmv into s_row;
           end loop;
      close v_possvcfmv;
   
      --commit;

     Exception
       When Others Then
       rollback;
     end;

end;

（编辑：泰州站长网）

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