您当前的位置: 首页 > 数据库教程 > Oracle教程 > 教快速掌握Oracle数据库游标的使用方法

教快速掌握Oracle数据库游标的使用方法

作者:guanchaofeng 来源:不详 发布时间: 2009-05-09 12:44 点击:
显式游标 当查询返回结果超过一行时,就需要一个显式游标,此时用户不能使用selectinto语句。PL/SQL管理隐式游标,当查询开始时隐式游标打开,查询结束时隐式游标自动关闭。显式游标在PL/SQL块的声明部分声明,在执行部分或异常处理部分打开,取出数据,关闭

教快速掌握Oracle数据库游标的使用方法

  显式游标
  
  当查询返回结果超过一行时,就需要一个显式游标,此时用户不能使用selectinto语句。PL/SQL管理隐式游标,当查询开始时隐式游标打开,查询结束时隐式游标自动关闭。显式游标在PL/SQL块的声明部分声明,在执行部分或异常处理部分打开,取出数据,关闭。
  
  使用游标
  
  这里要做一个声明,我们所说的游标通常是指显式游标,因此从现在起没有特别指明的情况,我们所说的游标都是指显式游标。要在程序中使用游标,必须首先声明游标。
  
  声明游标
  
  语法:
  
  CURSORcursor_nameISselect_statement;
  
  在PL/SQL中游标名是一个未声明变量,不能给游标名赋值或用于表达式中。
  
  例:
  
  DELCARECURSORC_EMPISSELECTempno,ename,salaryFROMempWHEREsalary>2000ORDERBYename;........BEGIN
  
  在游标定义中SELECT语句中不一定非要表可以是视图,也可以从多个表或视图中选择的列,甚至可以使用*来选择所有的列。
  
  打开游标
  
  使用游标中的值之前应该首先打开游标,打开游标初始化查询处理。打开游标的语法是:
  
  OPENcursor_name
  
  cursor_name是在声明部分定义的游标名。
  
  例:
  
  OPENC_EMP;关闭游标。
  
  语法:
  
  CLOSEcursor_name
  
  例:
  
  CLOSEC_EMP;从游标提取数据。从游标得到一行数据使用FETCH命令。每一次提取数据后,游标都指向结果集的下一行。语法如下:
  
  FETCHcursor_nameINTOvariable[,variable,...]
  
  对于SELECT定义的游标的每一列,FETCH变量列表都应该有一个变量与之相对应,变量的类型也要相同。
  
  例:
  
  SETSERVERIUTPUTONDECLAREv_enameEMP.ENAME%TYPE;v_salaryEMP.SALARY%TYPE;CURSORc_empISSELECTename,salaryFROMemp;BEGINOPENc_emp;FETCHc_empINTOv_ename,v_salary;DBMS_OUTPUT.PUT_LINE('SalaryofEmployee'||v_ename||'is'||v_salary);FETCHc_empINTOv_ename,v_salary;DBMS_OUTPUT.PUT_LINE('SalaryofEmployee'||v_ename||'is'||v_salary);FETCHc_empINTOv_ename,v_salary;DBMS_OUTPUT.PUT_LINE('SalaryofEmployee'||v_ename||'is'||v_salary);CLOSEc_emp;END
  
  这段代码无疑是非常麻烦的,如果有多行返回结果,可以使用循环并用游标属性为结束循环的条件,以这种方式提取数据,程序的可读性和简洁性都大为提高,下面我们使用循环重新写上面的程序:
  
  SETSERVERIUTPUTONDECLAREv_enameEMP.ENAME%TYPE;v_salaryEMP.SALARY%TYPE;CURSORc_empISSELECTename,salaryFROMemp;BEGINOPENc_emp;LOOPFETCHc_empINTOv_ename,v_salary;EXITWHENc_emp%NOTFOUND;DBMS_OUTPUT.PUT_LINE('SalaryofEmployee'||v_ename||'is'||v_salary);END
  
  记录变量
  
  定义一个记录变量使用TYPE命令和%ROWTYPE,关于%ROWsTYPE的更多信息请参阅相关资料。
  
  记录变量用于从游标中提取数据行,当游标选择很多列的时候,那么使用记录比为每列声明一个变量要方便得多。
  
  当在表上使用%ROWTYPE并将从游标中取出的值放入记录中时,如果要选择表中所有列,那么在SELECT子句中使用*比将所有列名列出来要得多。
  
  例:
  
  SETSERVERIUTPUTONDECLARER_empEMP%ROWTYPE;CURSORc_empISSELECT*FROMemp;BEGINOPENc_emp;LOOPFETCHc_empINTOr_emp;EXITWHENc_emp%NOTFOUND;DBMS_OUT.PUT.PUT_LINE('SalaryofEmployee'||r_emp.ename||'is'||r_emp.salary);ENDLOOP;CLOSEc_emp;END;
  
  %ROWTYPE也可以用游标名来定义,这样的话就必须要首先声明游标:
  
  SETSERVERIUTPUTONDECLARECURSORc_empISSELECTename,salaryFROMemp;R_empc_emp%ROWTYPE;BEGINOPENc_emp;LOOPFETCHc_empINTOr_emp;EXITWHENc_emp%NOTFOUND;DBMS_OUT.PUT.PUT_LINE('SalaryofEmployee'||r_emp.ename||'is'||r_emp.salary);ENDLOOP;CLOSEc_emp;END;
  
  带参数的游标
  
  与存储过程和函数相似,可以将参数传递给游标并在查询中使用。这对于处理在某种条件下打开游标的情况非常有用。它的语法如下:
  
  CURSORcursor_name[(parameter[,parameter],...)]ISselect_statement;
  
  定义参数的语法如下:
  
  Parameter_name[IN]data_type[{:=|DEFAULT}value]
  
  与存储过程不同的是,游标只能接受传递的值,而不能返回值。参数只定义数据类型,没有大小。
  
  另外可以给参数设定一个缺省值,当没有参数值传递给游标时,就使用缺省值。游标中定义的参数只是一个占位符,在别处引用该参数不一定可靠。
  
  在打开游标时给参数赋值,语法如下:
  
  OPENcursor_name[value[,value]....];
  
  参数值可以是文字或变量。
  
  例:
  
  DECALRECURSORc_deptISSELECT*FROMdeptORDERBYdeptno;CURSORc_emp(p_deptVARACHAR2)ISSELECTename,salaryFROMempWHEREdeptno=p_deptORDERBYenamer_deptDEPT%ROWTYPE;v_enameEMP.ENAME%TYPE;v_salaryEMP.SALARY%TYPE;v_tot_salaryEMP.SALARY%TYPE;BEGINOPENc_dept;LOOPFETCHc_deptINTOr_dept;EXITWHENc_dept%NOTFOUND;DBMS_OUTPUT.PUT_LINE('Department:'||r_dept.deptno||'-'||r_dept.dname);v_tot_salary:=0;OPENc_emp(r_dept.deptno);LOOPFETCHc_empINTOv_ename,v_salary;EXITWHENc_emp%NOTFOUND;DBMS_OUTPUT.PUT_LINE('Name:'||v_ename||'salary:'||v_salary);v_tot_salary:=v_tot_salary+v_salary;ENDLOOP;CLOSEc_emp;DBMS_OUTPUT.PUT_LINE('ToltalSalaryfordept:'||v_tot_salary);ENDLOOP;CLOSEc_dept;END;
  
  游标FOR循环
  
  在大多数时候我们在设计程序的时候都遵循下面的步骤:
  
  1、打开游标。
  
  2、开始循环。
  
  3、从游标中取值。
  
  4、那一行被返回。
  
  5、处理。
  
  6、关闭循环。
  
  7、关闭游标。
  
  可以简单的把这一类代码称为游标用于循环。但还有一种循环与这种类型不相同,这就是FOR循环,用于FOR循环的游标按照正常的声明方式声明,它的优点在于不需要显式的打开、关闭、取数据,测试数据的存在、定义存放数据的变量等等。游标FOR循环的语法如下:
  
  FORrecord_nameIN(corsor_name[(parameter[,parameter]...)]|(query_difinition)LOOPstatementsENDLOOP;
  
  下面我们用for循环重写上面的例子:
  
  DECALRECURSORc_deptISSELECTdeptno,dnameFROMdeptORDERBYdeptno;CURSORc_emp(p_deptVARACHAR2)ISSELECTename,salaryFROMempWHEREdeptno=p_deptORDERBYenamev_tot_salaryEMP.SALARY%TYPE;BEGINFORr_deptINc_deptLOOPDBMS_OUTPUT.PUT_LINE('Department:'||r_dept.deptno||'-'||r_dept.dname);v_tot_salary:=0;FORr_empINc_emp(r_dept.deptno)LOOPDBMS_OUTPUT.PUT_LINE('Name:'||v_ename||'salary:'||v_salary);v_tot_salary:=v_tot_salary+v_salary;ENDLOOP;DBMS_OUTPUT.PUT_LINE('ToltalSalaryfordept:'||v_tot_salary);ENDLOOP;END;
  
  在游标FOR循环中使用查询
  
  在游标FOR循环中可以定义查询,由于没有显式声明所以游标没有名字,记录名通过游标查询来定义。
  
  DECALREv_tot_salaryEMP.SALARY%TYPE;BEGINFORr_deptIN(SELECTdeptno,dnameFROMdeptORDERBYdeptno)LOOPDBMS_OUTPUT.PUT_LINE('Department:'||r_dept.deptno||'-'||r_dept.dname);v_tot_salary:=0;FORr_empIN(SELECTename,salaryFROMempWHEREdeptno=p_deptORDERBYename)LOOPDBMS_OUTPUT.PUT_LINE('Name:'||v_ename||'salary:'||v_salary);v_tot_salary:=v_tot_salary+v_salary;ENDLOOP;DBMS_OUTPUT.PUT_LINE('ToltalSalaryfordept:'||v_tot_salary);ENDLOOP;END;
  
  游标中的子查询
  
  语法如下:
  
  CURSORC1ISSELECT*FROMempWHEREdeptnoNOTIN(SELECTdeptnoFROMdeptWHEREdname!='ACCOUNTING');
  
  可以看出与SQL中的子查询没有什么区别。
  
  游标中的更新和删除
  
  在PL/SQL中依然可以使用UPDATE和DELETE语句更新或删除数据行。显式游标只有在需要获得多行数据的情况下使用。PL/SQL提供了仅仅使用游标就可以执行删除或更新记录的方法。
  
  UPDATE或DELETE语句中的WHERECURRENTOF子串专门处理要执行UPDATE或DELETE操作的表中取出的最近的数据。要使用这个方法,在声明游标时必须使用FORUPDATE子串,当对话使用FORUPDATE子串打开一个游标时,所有返回集中的数据行都将处于行级(ROW-LEVEL)独占式锁定,其他对象只能查询这些数据行,不能进行UPDATE、DELETE或SELECT...FORUPDATE操作。
  
  语法:
  
  FORUPDATE[OF[schema.]table.column[,[schema.]table.column]..[nowait]
  
  在多表查询中,使用OF子句来锁定特定的表,如果忽略了OF子句,那么所有表中选择的数据行都将被锁定。如果这些数据行已经被其他会话锁定,那么正常情况下ORACLE将等待,直到数据行解锁。
  
  在UPDATE和DELETE中使用WHERECURRENTOF子串的语法如下:
  
  WHERE{CURRENTOFcursor_name|search_condition}
  
  例:
  
  DELCARECURSORc1ISSELECTempno,salaryFROMempWHEREcommISNULLFORUPDATEOFcomm;v_commNUMBER(10,2);BEGINFORr1INc1LOOPIFr1.salary<500THENv_comm:=r1.salary*0.25;ELSEIFr1.salary<1000THENv_comm:=r1.salary*0.20;ELSEIFr1.salary<3000THENv_comm:=r1.salary*0.15;ELSEv_comm:=r1.salary*0.12;ENDIF;UPDATEemp;SETcomm=v_commWHERECURRENTOFc1l;ENDLOOP;END

分享到:
本文"教快速掌握Oracle数据库游标的使用方法"由远航站长收集整理而来,仅供大家学习与参考使用。更多网站制作教程尽在远航站长站。
顶一下
(0)
0%
踩一下
(0)
0%
[点击 次] [返回上一页] [打印]
发表评论
请自觉遵守互联网相关的政策法规,严禁发布色情、暴力、反动的言论。
评价:
表情:
用户名: 密码: 验证码:
关于本站 - 联系我们 - 广告合作 - 网站声明 - 友情连接- 网站地图 - 站点地图 - 返回顶部
Copyright © 2007-2013 www.yhzhan.com(远航站长). All Rights Reserved .
远航站长:为中小站长提供最佳的学习与交流平台,提供网页制作与网站编程等各类网站制作教程.
官方QQ:445490277 网站群:26680406 网站备案号:豫ICP备07500620号-4