时间:2021-07-01 10:21:17 帮助过:5人阅读
===========================================================================
另外,下面再附加一个我在项目中编写使用的一个PL/SQL程序块中使用游标的例子:
- <span style="color: #0000ff;">declare</span>
- <span style="color: #008080;">--</span><span style="color: #008080;">cursor v_possvcfmv Is select * from ttg_pos_svcfmv_mike order by id;</span>
- <span style="color: #0000ff;">cursor</span> v_possvcfmv <span style="color: #0000ff;">Is</span>
- <span style="color: #0000ff;">SELECT</span> m.id,m.status,m.PRODUCTID,decode(c.servicetermid ,<span style="color: #0000ff;">null</span>,<span style="color: #800000; font-weight: bold;">0</span><span style="color: #000000;">,c.servicetermid) servicetermid, b.parano,m.tp,m.icv,b.paracode,
- m.l3direct,m.l3indirect,m.l2direct,m.l2indirect,m.l1indirect,m.privateprice
- </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;"> ttg_pos_svcfmv_mike m
- </span><span style="color: #808080;">LEFT</span> <span style="color: #808080;">OUTER</span> <span style="color: #808080;">JOIN</span> (<span style="color: #0000ff;">SELECT</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">FROM</span> ttg_dictionary <span style="color: #0000ff;">WHERE</span> paraid <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">75</span>)b <span style="color: #0000ff;">ON</span> m.OFFERINGTYPE<span style="color: #808080;">=</span><span style="color: #000000;">b.paracode
- </span><span style="color: #808080;">LEFT</span> <span style="color: #808080;">OUTER</span> <span style="color: #808080;">JOIN</span> TTG_SERVICE_POSMAP c <span style="color: #0000ff;">ON</span> m.productid <span style="color: #808080;">=</span> c.productid <span style="color: #808080;">AND</span> b.parano <span style="color: #808080;">=</span><span style="color: #000000;"> c.OFFERINGID
- </span><span style="color: #0000ff;">where</span> m.status<span style="color: #808080;">=</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">1</span><span style="color: #ff0000;">‘</span> <span style="color: #808080;">and</span> m.id <span style="color: #808080;">in</span> (<span style="color: #800000; font-weight: bold;">2616</span>,<span style="color: #800000; font-weight: bold;">2617</span><span style="color: #000000;">)
- </span><span style="color: #0000ff;">order</span> <span style="color: #0000ff;">by</span><span style="color: #000000;"> m.id ;
- s_row v_possvcfmv</span><span style="color: #808080;">%</span><span style="color: #000000;">rowtype;
- </span><span style="color: #0000ff;">begin</span>
- <span style="color: #0000ff;">open</span><span style="color: #000000;"> v_possvcfmv;
- </span><span style="color: #0000ff;">fetch</span> v_possvcfmv <span style="color: #0000ff;">into</span><span style="color: #000000;"> s_row;
- </span><span style="color: #0000ff;">while</span> v_possvcfmv<span style="color: #808080;">%</span><span style="color: #000000;">found loop
- </span><span style="color: #008080;">/*</span><span style="color: #008080;">
- 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 ,‘1‘,s_row.l2direct ,‘1‘ , s_row.l2indirect ,‘1‘ , s_row.l1indirect ,‘1‘ , s_row.privateprice ,‘1‘ ,
- ‘‘,‘‘,‘‘,‘999999999‘);
- update ttg_pos_svcfmv_mike set status=‘1‘ where id=s_row.id;
- </span><span style="color: #008080;">*/</span><span style="color: #000000;">
- a_jack_Test(</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">SPA_POSSVCFMV(</span><span style="color: #ff0000;">‘</span> <span style="color: #808080;">||</span> s_row.productid <span style="color: #808080;">||</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘</span> <span style="color: #808080;">||</span> s_row.servicetermid <span style="color: #808080;">||</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘‘‘</span> <span style="color: #808080;">||</span> s_row.parano <span style="color: #808080;">||</span> <span style="color: #ff0000;">‘‘‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘</span> <span style="color: #808080;">||</span><span style="color: #000000;">
- s_row.tp </span><span style="color: #808080;">||</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘</span> <span style="color: #808080;">||</span> s_row.icv <span style="color: #808080;">||</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘‘‘</span> <span style="color: #808080;">||</span> s_row.paracode <span style="color: #808080;">||</span> <span style="color: #ff0000;">‘‘‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘‘‘‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘‘‘‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘</span> <span style="color: #808080;">||</span><span style="color: #000000;">
- s_row.l3direct </span><span style="color: #808080;">||</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘‘</span><span style="color: #ff0000;">1</span><span style="color: #ff0000;">‘‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘</span> <span style="color: #808080;">||</span> s_row.l3indirect <span style="color: #808080;">||</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘‘</span><span style="color: #ff0000;">1</span><span style="color: #ff0000;">‘‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘</span> <span style="color: #808080;">||</span> s_row.l2direct <span style="color: #808080;">||</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘‘</span><span style="color: #ff0000;">1</span><span style="color: #ff0000;">‘‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘</span> <span style="color: #808080;">||</span> s_row.l2indirect <span style="color: #808080;">||</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘‘</span><span style="color: #ff0000;">1</span><span style="color: #ff0000;">‘‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘</span> <span style="color: #808080;">||</span> s_row.l1indirect <span style="color: #808080;">||</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘‘</span><span style="color: #ff0000;">1</span><span style="color: #ff0000;">‘‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘</span> <span style="color: #808080;">||</span> s_row.privateprice <span style="color: #808080;">||</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘‘</span><span style="color: #ff0000;">1</span><span style="color: #ff0000;">‘‘‘</span> <span style="color: #808080;">||</span>
- <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘‘‘‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘‘‘‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘‘‘‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘‘</span><span style="color: #ff0000;">999999999</span><span style="color: #ff0000;">‘‘</span><span style="color: #ff0000;">)</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">
- );
- dbms_output.put_line(</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">SPA_POSSVCFMV(</span><span style="color: #ff0000;">‘</span> <span style="color: #808080;">||</span> s_row.productid <span style="color: #808080;">||</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘</span> <span style="color: #808080;">||</span> s_row.servicetermid <span style="color: #808080;">||</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘‘‘</span> <span style="color: #808080;">||</span> s_row.parano <span style="color: #808080;">||</span> <span style="color: #ff0000;">‘‘‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘</span> <span style="color: #808080;">||</span><span style="color: #000000;">
- s_row.tp </span><span style="color: #808080;">||</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘</span> <span style="color: #808080;">||</span> s_row.icv <span style="color: #808080;">||</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘‘‘</span> <span style="color: #808080;">||</span> s_row.paracode <span style="color: #808080;">||</span> <span style="color: #ff0000;">‘‘‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘‘‘‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘‘‘‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘</span> <span style="color: #808080;">||</span><span style="color: #000000;">
- s_row.l3direct </span><span style="color: #808080;">||</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘‘</span><span style="color: #ff0000;">1</span><span style="color: #ff0000;">‘‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘</span> <span style="color: #808080;">||</span> s_row.l3indirect <span style="color: #808080;">||</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘‘</span><span style="color: #ff0000;">1</span><span style="color: #ff0000;">‘‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘</span> <span style="color: #808080;">||</span> s_row.l2direct <span style="color: #808080;">||</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘‘</span><span style="color: #ff0000;">1</span><span style="color: #ff0000;">‘‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘</span> <span style="color: #808080;">||</span> s_row.l2indirect <span style="color: #808080;">||</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘‘</span><span style="color: #ff0000;">1</span><span style="color: #ff0000;">‘‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘</span> <span style="color: #808080;">||</span> s_row.l1indirect <span style="color: #808080;">||</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘‘</span><span style="color: #ff0000;">1</span><span style="color: #ff0000;">‘‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘</span> <span style="color: #808080;">||</span> s_row.privateprice <span style="color: #808080;">||</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘‘</span><span style="color: #ff0000;">1</span><span style="color: #ff0000;">‘‘‘</span> <span style="color: #808080;">||</span>
- <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘‘‘‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘‘‘‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘‘‘‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘‘</span><span style="color: #ff0000;">999999999</span><span style="color: #ff0000;">‘‘</span><span style="color: #ff0000;">)</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">
- );
- </span><span style="color: #0000ff;">fetch</span> v_possvcfmv <span style="color: #0000ff;">into</span><span style="color: #000000;"> s_row;
- </span><span style="color: #0000ff;">end</span><span style="color: #000000;"> loop;
- </span><span style="color: #0000ff;">close</span><span style="color: #000000;"> v_possvcfmv;
- </span><span style="color: #008080;">--</span><span style="color: #008080;">commit;</span>
- <span style="color: #000000;">
- Exception
- </span><span style="color: #0000ff;">When</span> Others <span style="color: #0000ff;">Then</span>
- <span style="color: #0000ff;">rollback</span><span style="color: #000000;">;
- </span><span style="color: #0000ff;">end</span><span style="color: #000000;">;
- </span><span style="color: #0000ff;">end</span>;
其中 a_jack_Test 是一个存储过程,只是加了个时间戳,存储过程如下:
- <span style="color: #0000ff;">create</span> <span style="color: #808080;">or</span> <span style="color: #ff00ff;">replace</span> <span style="color: #0000ff;">procedure</span> a_jack_Test(v_username <span style="color: #808080;">in</span><span style="color: #000000;"> nvarchar2)
- </span><span style="color: #0000ff;">as</span><span style="color: #000000;">
- v_time </span><span style="color: #0000ff;">varchar2</span>(<span style="color: #800000; font-weight: bold;">50</span><span style="color: #000000;">);
- </span><span style="color: #0000ff;">begin</span><span style="color: #000000;">
- v_time:</span><span style="color: #808080;">=</span>to_char(systimestamp,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">yyyy-mm-dd hh24:mi:ss.ff4</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">);
- dbms_output.put_line(</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">‘</span> <span style="color: #808080;">||</span> v_time <span style="color: #808080;">||</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">] ---- </span><span style="color: #ff0000;">‘</span> <span style="color: #808080;">||</span><span style="color: #000000;"> v_username);
- </span><span style="color: #0000ff;">end</span>;
出处:http://www.cnblogs.com/sc-xx/archive/2011/12/03/2275084.html
Oracle 游标使用全解
标签:ike offering 创建 情况 off asc distinct pre .sh