迅闻网
让更多人看到你

oracle游标(oracle游标三种循环)

  oracle游标

订阅专栏
1、游标的概念
游标(CURSOR):游标是把从数据表中提取出来的数据,以临时表的方式存放在内存中,在游标中有一个数据指针,在初始状态下指向的是首记载,利用fetch句子能够移动该指针,从而对游标中的数据进行各种操作。
2、游标的效果
游标是用来处理运用SELECT句子从数据库中检索到的多行记载的东西。借助于游标的功能,数据库应用程序能够对一组记载逐条进行处理,每次处理一行。
3、游标的类型
显式游标(ExplicitCursor):显式游标需求界说声明,在运用前要翻开和获取,运用结束后要封闭。多用于回来多行的SELECT句子
隐式游标(ImplicitCursor):在履行一个SQL句子时,服务器将自动创建一个隐式游标,该游标是内存中的作业区,存储了履行SQL句子的成果,可通过游标的特点取得SQL的履行成果及状态信息。多用于只回来一行的SQL句子
4、隐式游标
(ORACLE在创建隐式游标时,默认的游标名为SQL)
1)游标的首要特点(显现游标、隐式游标)
%FOUND布尔型特点,当SQL句子至少影响一行时为TRUE,否则为FALSE
%NOTFOUND布尔型特点,当SQL句子没有影响的行时为TRUE,否则为FALSE
%ISOPEN布尔型特点,当游标已翻开时回来TRUE,否则为FALSE(对用户而言,隐式游标永远是false)
%ROWCOUNT数字型特点,回来受到SQL影响的行数
留意:
特点名与游标名之间没有空格。
游标的特点只能在PL/SQL块中运用,而不能在SQL句子中运用
例1将PRODUCTS表中类型为1的一切产品的单价打9折,并显现该更新所影响的行数.
BEGIN
UPDATEproducts
SETunitprice=unitprice*0.9
WHEREcategoryid=1;
IFSQL%FOUNDTHEN
dbms_output.put_line(‘更新了’||SQL%ROWCOUNT||’条记载’);
ELSE
dbms_output.put_line(‘没有更新记载’);
ENDIF;
END;
5.显式游标
1)界说游标
在运用显现游标之前,有必要先在声明部分界说游标,其界说语法如下:CURSORcursor_name[(parameter,…)]ISselect_statement;阐明:参数parameter方式如下:para_name[IN]data_type[:=|DEFAULTvalue]
(2)翻开游标
当翻开游标时,ORACLE会履行游标所对应的SELECT句子,并将成果存放到成果集,其界说语法如下:OPENcursor_name[(parameter,…)];
(3)提取数据语法如下:
FETCHcursor_nameINTOvariable[,…];
阐明:
对游标第一次履行FETCH句子时,它将作业区中的第一条记载赋给赋给变量,并使作业区内的指针指向下一条记载。
作业区中的游标指针只能向下移动,不能回退。
在运用FETCH句子之前,有必要先翻开游标,才能确保作业区内有数据。
INTO子句中的变量,顺序、类型有必要与作业区中每行记载的字段数、顺序以及数据类型一一对应
(4)封闭游标
封闭游标可开释其成果集,语法如下:CLOSEcursor_name;阐明:封闭游标,就是使游标所对应的内存作业区变为无效,并开释与游标相关的体系资源
显式游标——无参游标
例2:用显式游标显现输出products表中供货商编号为6的产品的信息。
DECLARE
CURSORprod_cursorIS
select*fromproductswhere
supplierid=6;
prod_recordproducts%rowtype;
BEGIN
OPENprod_cursor;
LOOP
FETCHprod_cursorINTOprod_record;
EXITWHEN
prod_cursor%NOTFOUND;
dbms_output.put_line(‘产品编号’||prod_record.productid);
dbms_output.put_line(‘产品名称’||prod_record.productname);
dbms_output.put_line(‘供货商编号’||prod_record.supplierid);
ENDLOOP;
CLOSEprod_cursor;
END;
显式游标——参数游标
例3:用显式参数游标显现输出products表中供货商编号为XX的产品的信息。
DECLARE
CURSORprod_cursor(suppIDinnumberDEFAULT1)IS
select*fromproductswhere
supplierid=suppId;
prod_recordproducts%rowtype;
BEGIN
OPENprod_cursor(2);
LOOP
FETCHprod_cursorINTOprod_record;
EXITWHEN
prod_cursor%notfound;
dbms_output.put_line(‘产品编号’||prod_record.productid);
dbms_output.put_line(‘产品名称’||prod_record.productname);
dbms_output.put_line(‘供货商编号’||prod_record.supplierid);
ENDLOOP;
CLOSEprod_cursor;
END;
留意:在为参数游标界说参数的数据类型时,不能运用长度约束
显式游标——游标FOR循环
语法格局:FOR循环变量IN游标类型名LOOP循环句子ENDLOOP;
留意:
循环变量不需求界说,体系隐含地界说其数据类型为%ROWTYPE的变量
运用游标FOR循环时,不能运用OPEN、FETCH、CLOSE句子。
例4:用显式参数游标显现输出products表中供货商编号为XX的产品的信息。
DECLARE
CURSORprod_cursor(suppIDinnumberDEFAULT1)IS
select*fromproductswheresupplierid=suppId;
BEGIN
FORv_prINprod_cursor(3)LOOP
dbms_output.put_line(‘产品编号’||v_pr.productid);
dbms_output.put_line(‘产品名称’||v_pr.productname);
dbms_output.put_line(‘供货商编号’||v_pr.supplierid);
ENDLOOP;
END;
6.显现游标与隐式游标比较
7.游标变量
游标包含显现游标和隐式游标,在界说时与特定的查询绑定,即在声明中界说查询,其结构是不变的,因此又称静态变量。
游标变量是一个指向多行查询成果集的指针,不与特定的查询绑定,能够在翻开游标变量时界说查询,能够回来不同结构的成果集。
在PL/SQL中,运用游标变量包含定界说游标引证类型(REFCURSOR),声明游标变量,翻开游标变量、检索游标变量、封闭游标变量等几个根本过程。
游标变量根本过程:
(1)界说游标CURSOR类型的指针TYPEtype_nameISREFCURSOR[RETURNreturn_type];
type_name是新引证类型的姓名
而return_type表明该游标变量回来的记载类型。
(2)声明REFCURSOR类型的变量
游标变量type_name;
(3)翻开游标变量的语法格局如下所示:
OPEN游标变量名FORSELECT句子;
(4)检索游标变量
检索游标变量的方法与检索静态游标相似,运用FETCH….INTO句子循环检索游标变量的
成果会集的记载。语法为:
LOOP
FETCHcursor_variableINTOvariablel,variable2……..;
EXITWHENCursor_variables%NOTFOUND;
……..
ENDLOOP;
检索游标变量时只能运用简略循环或WHILE循环,不能选用FOR循环.
(5)封闭游标变量
检索并处理完游标变量所对应的成果集后,能够封闭游标变量,开释存储空间。语法为;

oracle

oracle游标三种循环

1.情形展现
Oracle遍历游标的四种方法(for、fetch、while、bulkcollect+forall)
2.问题分析
我们能够把游标想象成一张表,想要遍历游标,就要取到游标的每行数据,所以问题的要害就成了:怎么取到行数据?
3.解决方案
方法一:FOR循环(引荐运用)
变形一:遍历显式游标
仿制代码
/*假如是在存储进程外运用显式游标,需求运用DECLARE要害字*/DECLARE/*创建游标*/CURSORCUR_FIRST_INDEXISSELECTA.IDA_ID,–一级目标IDA.INDEXNAMEA_INDEXNAME–一级目标称号FROMINDEX_AAORDERBYA_ID;/*界说游标变量,该变量的类型为基于游标CUR_FIRST_INDEX的行记载*/ROW_CUR_FIRST_INDEXCUR_FIRST_INDEX%ROWTYPE;/*游标处理*/BEGIN/*遍历显式游标*/–FOR循环FORROW_CUR_FIRST_INDEXINCUR_FIRST_INDEXLOOP–循环体DBMS_OUTPUT.PUT_LINE(‘{“ID”:”‘||ROW_CUR_FIRST_INDEX.A_ID||'”,”称号”:”‘||ROW_CUR_FIRST_INDEX.A_INDEXNAME||'”}’);ENDLOOP;END;
履行,输出结果
变形二:遍历隐式游标(引荐运用)
for循环遍历游标,其实又能够分为两种方法,一种是显式游标的遍历,另一种是隐式游标的遍历。
/*假如是在存储进程外运用隐式游标,假如用不到变量无需声明DECLARE要害字*//*游标处理*/BEGIN/*遍历隐式游标*/–FOR循环FORROW_CUR_FIRST_INDEXIN(SELECTA.IDA_ID,–一级目标IDA.INDEXNAMEA_INDEXNAME–一级目标称号FROMINDEX_AAORDERBYA_ID)LOOP–循环体DBMS_OUTPUT.PUT_LINE(‘{“ID”:”‘||ROW_CUR_FIRST_INDEX.A_ID||'”,”称号”:”‘||ROW_CUR_FIRST_INDEX.A_INDEXNAME||'”}’);ENDLOOP;END;
隐式游标相较于显式游标用法更加简略,无需声明直接调用即可。
方法二:FETCH循环
/*游标声明代码和方法一共同,此处省掉,直接展现游标处理代码*/BEGIN/*遍历游标*/–FETCH循环OPENCUR_FIRST_INDEX;–必须要明确的打开和封闭游标LOOPFETCHCUR_FIRST_INDEXINTOROW_CUR_FIRST_INDEX;EXITWHENCUR_FIRST_INDEX%NOTFOUND;–循环体DBMS_OUTPUT.PUT_LINE(‘{“ID”:”‘||ROW_CUR_FIRST_INDEX.A_ID||'”,”称号”:”‘||ROW_CUR_FIRST_INDEX.A_INDEXNAME||'”}’);ENDLOOP;CLOSECUR_FIRST_INDEX;END;
方法三:WHILE循环
/*游标声明代码和方法一共同,此处省掉,直接展现游标处理代码*/BEGIN/*遍历游标*/OPENCUR_FIRST_INDEX;–必须要明确的打开和封闭游标FETCHCUR_FIRST_INDEXINTOROW_CUR_FIRST_INDEX;WHILECUR_FIRST_INDEX%FOUNDLOOP–循环体DBMS_OUTPUT.PUT_LINE(‘{“ID”:”‘||ROW_CUR_FIRST_INDEX.A_ID||'”,”称号”:”‘||ROW_CUR_FIRST_INDEX.A_INDEXNAME||'”}’);FETCHCUR_FIRST_INDEXINTOROW_CUR_FIRST_INDEX;ENDLOOP;CLOSECUR_FIRST_INDEX;END;
留意:运用while循环时,需求fetch两次。
方法四:BULKCOLLECT+FORALL(速度最快)
仿制代/*假如是在存储进程外运用显现游标,需求运用DECLARE要害字*//*声明游标*/DECLARE/*创建显式游标*/CURSORCUR_FIRST_INDEXISSELECTA.IDA_ID,–一级目标IDA.INDEXNAMEA_INDEXNAME–一级目标称号FROMINDEX_AAORDERBYA_ID;/*界说表类型,该表的表结构为游标CUR_FIRST_INDEX的行记载(能够存储多条游标记载)*/TYPETABLE_CUR_FIRST_INDEXISTABLEOFCUR_FIRST_INDEX%ROWTYPE;/*声明表变量*/TAB_FIRST_INDEXTABLE_CUR_FIRST_INDEX;/*游标处理进程*/BEGIN/*遍历游标*/OPENCUR_FIRST_INDEX;
LOOP–将n行游标数据放到表中FETCHCUR_FIRST_INDEXBULKCOLLECTINTOTAB_FIRST_INDEXLIMIT1;–数据量太少,仅当时测试运用哦,实际开发建议500左右–退出条件EXITWHENTAB_FIRST_INDEX.COUNT=0;–循环表数据FORIINTAB_FIRST_INDEX.FIRST..TAB_FIRST_INDEX.LASTLOOP
DBMS_OUTPUT.PUT_LINE(‘{“ID”:”‘||TAB_FIRST_INDEX(I).A_ID||'”,”称号”:”‘||TAB_FIRST_INDEX(I).A_INDEXNAME||'”}’);ENDLOOP;ENDLOOP;CLOSECUR_FIRST_INDEX;END;
留意上面语句的FOR,原为写成了FORALL是不正确的(我被成功带到沟里才发现不对)。
以下内容出自另一篇博客,更加全面详细可参考https://www.cnblogs.com/hellokitty1/p/4584333.html
DECLARECURSORemp_curISSELECTempno,ename,hiredateFROMemp;
TYPEemp_rec_typeISRECORD
(
empnoemp.empno%TYPE,
enameemp.ename%TYPE,
hiredateemp.hiredate%TYPE
);–界说基于记载的嵌套表TYPEnested_emp_typeISTABLEOFemp_rec_type;–声明调集变量emp_tabnested_emp_type;–界说了一个变量来作为limit的值v_limitPLS_INTEGER:=5;–界说变量来记载FETCH次数v_counterPLS_INTEGER:=0;BEGINOPENemp_cur;
LOOP–fetch时运用了BULKCOLLECT子句FETCHemp_curBULKCOLLECTINTOemp_tab
LIMITv_limit;–运用limit子句约束提取数据量EXITWHENemp_tab.COUNT=0;–留意此刻游标退出运用了emp_tab.COUNT,而不是emp_cur%notfoundv_counter:=v_counter+1;–记载运用LIMIT之后fetch的次数FORiINemp_tab.FIRST..emp_tab.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(‘当时记载:’||emp_tab(i).empno||CHR(9)||emp_tab(i).ename||CHR(9)||emp_tab(i).hiredate);ENDLOOP;ENDLOOP;CLOSEemp_cur;
DBMS_OUTPUT.put_line(‘总共获取次数为:’||v_counter);END;
4.总结
运用for循环的优势在于:
不需求手动打开&封闭游标(声明游标的开启和封闭);
不需求手动捕获数据(主动将数据fetch到记载型变量);
不需求重视何时要退出,也就是不需求写退出循环的满意条件(遍历完结就会退出)。
第4方法与前3种的差异在于:
前三种的游标变量:ROW_CUR_FIRST_INDEX,只能存储游标的一条数据;
第四种的表变量:TAB_FIRST_INDEX,能够存储游标的多条数据。
大数据批量处理的时分,第4种方法的优势将会凸显出来。

未经允许不得转载:迅闻网 » oracle游标(oracle游标三种循环)
分享到: 更多 (0)

评论 抢沙发

评论前必须登录!

 

迅闻网-让更多人看到你

登录/注册返回首页