oracle游标的定义使用.docx

oracle游标的定义使用 2008-02-23 15:12:57| 分类： oracle | 标签： |字号大中小 订阅 游标中定义的参数只要定义类型，不用定义长度，精度等；游标使用一:declarecursor mycur is -声明游标select * from a;myrec a%rowtype; -声明与表字段相同的记录beginopen mycur;-打开游标fetch mycur into myrec;while mycur%found loopdbms_output.put_line(myrec.col1|:|myrec.col2);fetch mycur into myrec;end loop;close mycur;end;/游标使用二: 使用参数declarecursor mycur(id varchar) isselect col2 from a where col1=id;myrec a.col2%type; beginopen mycur(1);loopfetch mycur into myrec;exit when mycur%notfound;dbms_output.put_line(myrec);end loop;close mycur;end;/游标使用二: 使用参数二for循环，在for循环中不用声明游标，也不用打开关闭declarecursor mycur(id varchar) isselect col2 from a where col1=id;beginfor myrec in mycur(1) loopdbms_output.put_line(myrec.col2);end loop;end;/判读游标是否打开，如果没有打开declaremyrec a.col2%type;cursor mycur(id varchar) isselect col2 from a where col1=id;beginif mycur%isopen then/判读打开dbms_output.put_line(游标打开了);else open mycur(1); end if;fetch mycur into myrec;close mycur;dbms_output.put_line(myrec);end;/利用游标获得数据行数declaret_name varchar(10);cursor mycur isselect col2 from a;beginopen mycur;loopfetch mycur into t_name;/数据填充到游标exit when mycur%NOTFOUND or mycur%NOTFOUND IS NULL;END LOOP;DBMS_OUTPUT.PUT_LINE(MYCUR%ROWCOUNT);CLOSE MYCUR;END;/利用游标update数据declarecursor cur isselect col1 from a for UPDATE;/for update 是必须的temp varchar(10);beginopen cur;fetch cur into temp;while cur%FOUND loopupdate a set col1=col1|_why where current of cur;/current of cur也是必须的fetch cur into temp;end loop;close cur;end;/游标中定义的参数只要定义类型，不用定义长度，精度等；游标使用一:declarecursor mycur is -声明游标select * from a;myrec a%rowtype; -声明与表字段相同的记录beginopen mycur;-打开游标fetch mycur into myrec;while mycur%found loopdbms_output.put_line(myrec.col1|:|myrec.col2);fetch mycur into myrec;end loop;close mycur;end;/游标使用二: 使用参数declarecursor mycur(id varchar) isselect col2 from a where col1=id;myrec a.col2%type; beginopen mycur(1);loopfetch mycur into myrec;exit when mycur%notfound;dbms_output.put_line(myrec);end loop;close mycur;end;/游标使用二: 使用参数二for循环，在for循环中不用声明游标，也不用打开关闭declarecursor mycur(id varchar) isselect col2 from a where col1=id;beginfor myrec in mycur(1) loopdbms_output.put_line(myrec.col2);end loop;end;/判读游标是否打开，如果没有打开declaremyrec a.col2%type;cursor mycur(id varchar) isselect col2 from a where col1=id;beginif mycur%isopen then/判读打开dbms_output.put_line(游标打开了);else open mycur(1); end if;fetch mycur into myrec;close mycur;dbms_output.put_line(myrec);end;/利用游标获得数据行数declaret_name varchar(10);cursor mycur isselect col2 from a;beginopen mycur;loopfetch mycur into t_name;/数据填充到游标exit when mycur%NOTFOUND or mycur%NOTFOUND IS NULL;END LOOP;DBMS_OUTPUT.PUT_LINE(MYCUR%ROWCOUNT);CLOSE MYCUR;END;/利用游标update数据declarecursor cur isselect col1 from a for UPDATE;/for update 是必须的temp varchar(10);beginopen cur;fetch cur into temp;while cur%FOUND loopupdate a set col1=col1|_why where current of cur;/current of cur也是必须的fetch cur into temp;end l