游标.doc

四、使用游标FOR循环在大多数需要使用显式游标的情况下，我们都可以用一个游标FOR循环来代替OPEN、FETCH和CLOSE语句。游标FOR循环隐式地声明了一个 %ROWTYPE类型的记录作为它的循环索引，打开游标，然后反复执行把结果集中的行放到索引中去，最后在所有行都被处理完成后关闭游标。思考下面PL/SQL块例子，它能从一个实验中计算出结果，然后把结果保存在一张临时表中。FOR循环的索引c1_rec是被隐式声明的记录。它的每一个域都保存来自游标c1中取出的值。对独立的域的引用可以使用点标志。 DECLARERESULTtemp.col1%TYPE;CURSORc1ISSELECTn1,n2,n3FROMdata_tableWHEREexper_num=1;BEGINFORc1_recINc1LOOP/*calculateandstoretheresults*/RESULT:=c1_rec.n2/(c1_rec.n1+c1_rec.n3);INSERTINTOtempVALUES(RESULT,NULL,NULL);ENDLOOP;COMMIT;END; 当进入游标FOR循环时后，游标的名称就不属于用OPEN语句打开的游标，也不属于封闭游标FOR循环。在每个循环之前，PL/SQL会把数据放到隐式声明的记录中去。记录的有效作用范围只在循环内，所以我们不能在循环的外部引用它。循环内的语句序列会为每一个满足条件的结果行执行一次，当游标离开循环时，游标会被自动地关闭，这包括正常地使用EXIT或GOTO语句来结束循环，或是因异常抛出而退出循环的情况。 1、使用子查询代替显式游标有时候我们并不需要声明游标，因为PL/SQL允许我们使用子查询来进行替代。下面的游标FOR循环先计算奖金值，然后把结果插入数据表中： DECLAREbonusREAL;BEGINFORemp_recIN(SELECTempno,sal,commFROMemp)LOOPbonus:=(emp_rec.sal*0.05)+(emp_m*0.25);INSERTINTObonusesVALUES(emp_rec.empno,bonus);ENDLOOP;COMMIT;END; 2、使用游标子查询我们可以使用游标子查询(又称游标表达式)把一个查询结果集作为参数传递给函数。如下例： SELECT*FROMTABLE(StockPivot(CURSOR(SELECT*FROMStockTable); 游标子查询通常可以用在表函数中，这将在第八章详细讨论。3、在游标FOR循环中为表达式定义别名隐式声明的记录中每个域存放着最近取得的数据。记录的域名称和SELECT列表中的字段相对应。但是，如果SELECT中含有表达式时会发生什么呢？看一下下面的例子： CURSORc1ISSELECTempno,sal+NVL(comm,0)wages,jobFROM. 这样的情况下，我们就必须为表达式起一个别名。如下例，wages就是表达式sal+NVL(comm,0)的一个别名：CURSORc1ISSELECTempno,sal+NVL(comm,0)wages,jobFROM. 如果要引用对应的域，就得使用别名进行代替，如下例所示：IFemp_rec.wages2000.00THENhigh_paid:=high_paid+1;ENDIF;IFemp_memp_record.salTHENhigher_comm:=higher_comm+1;ENDIF;ENDLOOP;INSERTINTOtempVALUES(high_paid,higher_comm,TotalWages:|TO_CHAR(total_wages);COMMIT;END; 五、使用游标变量跟游标一样，游标变量也是指向多行查询的结果集中的当前行。但是，游标与游标变量不同之处就和常量与变量不同之处类似。游标是静态的，而游标变量是动态的，因为游标变量并不与某个特定的查询相绑定。所以，游标变量可以打开任何类型兼容的查询，灵活性很大。 并且，我们还可以为游标变量赋新值，把它作为参数传递给本地和存储子程序。这就很容易地让我们把数据检索集中化处理。 游标变量可以在每个PL/SQL客户端使用。例如，我们可以在OCI或Pro*C这样的主环境中声明游标变量，然后把它作为输入主变量(绑定变量) 传给PL/SQL。并且，像Oracle Forms和Oracle Reports这样的含有PL/SQL引擎的开发工具，完全可以在客户端使用游标变量。Oracle服务器也有一个PL/SQL引擎。所以，我们可以在应用程序和服务器之间通过远程调用(RPC)来回传递游标变量。 1、什么是游标变量游标变量同C或Pascal语言中的指针类似，它指向一块内存地址，而不是地址中的内容本身。所以，声明一个游标变量可以创建一个指针，而不是具体的内容。在PL/SQL中，指针是一个REF X类型，REF是REFERENCE的缩写，而X代表对象的类型。因此，游标变量的数据类型是REF CURSOR。 为了执行多行查询，Oracle会开启一个未命名的工作区来存放处理信息。我们可以用显式游标为工作区命名然后访问相关的信息；或者声明指向工作区的一个游标变量。无论在什么地方使用游标，它总是指向同一个查询工作区，而游标变量则可以指向不同的工作区。所以，游标和游标变量不能交互使用；也就是说，我们不能在该使用游标的地方使用游标变量，不能在该使用游标变量的地方使用游标。 2、为什么使用游标变量我们主要是在PL/SQL存储子程序和各种客户端之间用游标变量来传递查询结果。PL/SQL和其他客户端程序都不拥有结果集，它们只是共享一个指向存放结果集工作区的指针而已。例如，一个OCI客户端，一个Oracle Forms应用程序和Oracle服务器可以引用同一个工作区。 只要有游标变量指向查询工作区，我们就可以引用它。因此，我们可以把游标变量的值自由地从一个作用域传递到另一个。例如，我们把主游标变量传递到嵌套在Pro*C程序中的PL/SQL块，游标变量指向的工作区就可以被访问。 如果客户端含有PL/SQL引擎，那么从客户端调用服务器端就不会有什么约束。假如我们在客户端声明游标变量，在服务器端打开并取得数据，然后把取得的结果返回给客户端。这些操作都是在服务器端完成，从而也减少了网络流量。3、定义REF CURSOR类型创建游标变量分为两个步骤。首先，我们得定义REF CURSOR类型，然后声明该类型的游标变量。我们可以在任何PL/SQL块、子程序或包内使用下面语法来定义REF CURSOR类型：TYPEref_type_nameISREFCURSORRETURNreturn_type; ref_type_name是类型区分符，return_type必须是记录类型或是代表数据表中的行类型。在下面的例子中，我们把返回的数据类型指定为数据表dept的行类型：DECLARETYPEDeptCurTypISREFCURSORRETURNdept%ROWTYPE; REF CURSOR类型可以是强类型也可以是弱类型。如下例所示，强类型REF CURSOR需要指定返回类型，而弱类型没有这个要求： DECLARETYPEEmpCurTypISREFCURSORRETURNemp%ROWTYPE;-strongTYPEGenericCurTypISREFCURSOR;-weak 强类型REF CURSOR在编译时会检查类型兼容，这样能更好的避免发生错误。但是，弱类型REF CURSOR具有更大的灵活性，因为它允许我们把游标变量与任何相兼容的查询关联起来。 4、声明游标变量定义了REF CURSOR后，就可以在PL/SQL块或子程序中声明该类型的游标变量了。在下面的程序中，我们声明了游标变量dept_cv： DECLARETYPEDeptCurTypISREFCURSORRETURNdept%ROWTYPE;dept_cvDeptCurTyp;-declarecursorvariable 注意：不能在包里声明游标变量。与打包变量不同，游标变量没有一个持久的状态。记住，声明游标变量就是创建了一个指针，它只是指向一个内容，本身并不存在，所以，游标变量是不能存放到数据库中的。变量的作用域规则也同样适用于游标变量。本地的PL/SQL游标变量在我们进入块或子程序时被初始化，在退出时被销毁。在REF CURSOR定义的RETURN子句中，我们可以使用%ROWTYPE指定返回类型，如下例：DECLARETYPEtmpcurtypISREFCURSORRETURNemp%ROWTYPE;tmp_cvtmpcurtyp;-declarecursorvariableTYPEempcurtypISREFCURSORRETURNtmp_cv%ROWTYPE;emp_cvempcurtyp;-declarecursorvariable 同样，也可以使用%TYPE指定返回值类型，如下例：DECLAREdept_recdept%ROWTYPE;-declarerecordvariableTYPEdeptcurtypISREFCURSORRETURNdept_rec%TYPE;dept_cvdeptcurtyp;-declarecursorvariable 最后一个例子，我们把RETURN子句的返回类型指定为用户自定义的RECORD类型：DECLARETYPEemprectypISRECORD(empnoNUMBER(4),enameVARCHAR2(1O),salNUMBER(7,2);TYPEempcurtypISREFCURSORRETURNemprectyp;emp_cvempcurtyp;-declarecursorvariable 作为参数的游标变量 我们可以把游标变量声明为函数和过程的形式参数。在下面例子中，我们定义REF CURSOR类型的EmpCurTyp，然后把该类型的游标变量作为过程的形式参数： DECLARETYPEempcurtypISREFCURSORRETURNemp%ROWTYPE;PROCEDUREopen_emp_cv(emp_cvINOUTempcurtyp)IS. 注意：跟所有的指针一样，游标变量也能增加参数别名出现的可能性。5、控制游标变量我们可以使用三个语句来控制游标变量：OPEN-FOR、FETCH和CLOSE。首先，使用OPEN打开游标变量，然后从结果集中FETCH数据，当完成所有的处理后，就可以使用CLOSE语句关闭游标变量。 打开游标变量 OPEN-FOR语句把一个游标变量和一个多行查询关联起来并执行查询。语法如下： OPENcursor_variable|:host_cursor_variableFORselect_statement|dynamic_stringUSINGbind_argument,bind_argument.; host_cursor_variable是声明在PL/SQL主环境中的游标变量，dynamic_string代表多行查询的字符串表达式。注意，这里只讨论使用静态SQL的情况。与游标不同，游标变量是没有参数的。但这个并不影响灵活性，因为我们可以把整个查询传递给游标变量。被传递的查询语句可以使用主变量、PL/SQL变量、参数和函数。下例中，我们首先打开游标变量emp_cv。与游标相似，我们也可以在游标变量上使用%FOUND、%NOTFOUND、%ISOPEN和%ROWCOUNT： IFNOTemp_cv%ISOPENTHEN/*Opencursorvariable.*/OPENemp_cvFORSELECT*FROMemp;ENDIF; 其它的OPEN-FOR语句可以使用不同的查询打开同样的游标变量。在重新打开游标变量之前是不需要关闭它的(但对一个静态游标使用多次OPEN操作时，系统会抛出预定义异常CURSOR_ALREADY_OPEN)。为执行一个不同的查询而重新打开游标变量时，前面的查询结果就会丢失。一般地，我们可以把游标变量传递给过程，然后由过程负责打开它，如下例，打包过程打开游标变量emp_cv： CREATEPACKAGEemp_dataAS.TYPEempcurtypISREFCURSORRETURNemp%ROWTYPE;PROCEDUREopen_emp_cv(emp_cvINOUTempcurtyp);ENDemp_data;CREATEPACKAGEBODYemp_dataAS.PROCEDUREopen_emp_cv(emp_cvINOUTempcurtyp)ISBEGINOPENemp_cvFORSELECT*FROMemp;ENDopen_emp_cv;ENDemp_data; 当我们把游标变量声明为一个打开游标变量的子程序的形式参数时，就必须要指定参数模式为IN OUT模式。那样的话，子程序就可以把一个打开的游标变量返回给调用者。另外，我们还可以使用独立的过程来打开游标变量。只要简单的在包里定义REF CURSOR类型，然后在一个独立的过程中引用它就行了。例如，如果我们创建了下面的无体包，我们就能在独立的过程中引用包中所定义的REF CURSOR了： CREATEPACKAGEcv_typesASTYPEgenericcurtypISREFCURSOR;TYPEempcurtypISREFCURSORRETURNemp%ROWTYPE;TYPEdeptcurtypISREFCURSORRETURNdept%ROWTYPE;.ENDcv_types; 下例中，我们创建一个引用REF CURSOR类型EmpCurTyp的过程，这个类型是在包cv_types中定义的。 CREATEPROCEDUREopen_emp_cv(emp_cvINOUTcv_types.empcurtyp)ASBEGINOPENemp_cvFORSELECT*FROMemp;ENDopen_emp_cv; 为把数据检索集中化处理，我们可以把一个存储过程中类型兼容的查询进行分组。在下面的例子中，打包过程声明了一个选择器作为它的形势参数。调用时，过程会为选定的查询打开游标变量： CREATEPACKAGEemp_dataASTYPEempcurtypISREFCURSORRETURNemp%ROWTYPE;PROCEDUREopen_emp_cv(emp_cvINOUTempcurtyp,choiceINT);ENDemp_data;CREATEPACKAGEBODYemp_dataASPROCEDUREopen_emp_cv(emp_cvINOUTempcurtyp,choiceINT)ISBEGINIFchoice=1THENOPENemp_cvFORSELECT*FROMempWHEREcommISNOTNULL;ELSIFchoice=2THENOPENemp_cvFORSELECT*FROMempWHEREsal2500;ELSIFchoice=3THENOPENemp_cvFORSELECT*FROMempWHEREdeptno=20;ENDIF;END;ENDemp_data; 为了获取更大的灵活性，我们可以把游标变量和选择器传递给过程，让它执行查询然后返回不同的查询结果。如下例所示： CREATEPACKAGEadmin_dataASTYPEgencurtypISREFCURSOR;PROCEDUREopen_cv(generic_cvINOUTgencurtyp,choiceINT);ENDadmin_data;CREATEPACKAGEBODYadmin_dataASPROCEDUREopen_cv(generic_cvINOUTgencurtyp,choiceINT)ISBEGINIFchoice=1THENOPENgeneric_cvFORSELECT*FROMemp;ELSIFchoice=2THENOPENgeneric_cvFORSELECT*FROMdept;ELSIFchoice=3THENOPENgeneric_cvFORSELECT*FROMsalgrade;ENDIF;END;ENDadmin_data; 使用游标变量作为主变量 我们可以在OCI或Pro*C程序这样的PL/SQL主环境中声明游标变量。在使用游标变量之前，我们需要把它作为主变量传递给PL/SQL。在下面的Pro*C例子中，我们把主游标变量和选择器一并传递给PL/SQL块，然后为被选择的查询打开游标变量： EXECSQLBEGINDECLARESECTION;./*Declarehostcursorvariable.*/SQL_CURSORgeneric_cv;intchoice;EXECSQLENDDECLARESECTION;./*Initializehostcursorvariable.*/EXECSQLALLOCATE:generic_cv;./*PasshostcursorvariableandselectortoPL/SQLblock.*/EXECSQLEXECUTEBEGINIF:choice=1THENOPEN:generic_cvFORSELECT*FROMemp;ELSIF:choice=2THENOPEN:generic_cvFORSELECT*FROMdept;ELSIF:choice=3THENOPEN:generic_cvFORSELECT*FROMsalgrade;ENDIF;END;END-EXEC; 主游标变量与任何查询的返回类型都兼容，它们就像PL/SQL中的弱类型游标变量一样。 从游标变量中取得数据 FETCH语句能从多行查询的结果集中取得数据，语法如下： FETCHcursor_variable_name|:host_cursor_variable_nameBULKCOLLECTINTOvariable_name,variable_name.|record_name; 下面的例子中，我们每次都从游标变量emp_cv中取出一条数据放到用户定义的记录emp_rec中： LOOP/*Fetchfromcursorvariable.*/FETCHemp_cvINTOemp_rec;EXITWHENemp_cv%NOTFOUND;-exitwhenlastrowisfetched-processdatarecordENDLOOP; 我们可以使用BULK COLLECT子句批量地从游标变量中取得数据放到一个或多个集合中。如下例所示：DECLARETYPEempcurtypISREFCURSORRETURNemp%ROWTYPE;TYPEnamelistISTABLEOFemp.ename%TYPE;TYPEsallistISTABLEOFemp.sal%TYPE;emp_cvempcurtyp;namesnamelist;salssallist;BEGINOPENemp_cvFORSELECTename,salFROMemp;FETCHemp_cvBULKCOLLECTINTOnames,sals;.END; 当游标变量被打开时，关联查询中的所有变量都会被计算。如果要改变查询中的结果集或要使用变量的最新值，我们就必须重新打开游标变量。不过我们可以为每一个从游标变量中取得数据使用不同的INTO子句。PL/SQL能保证游标变量的返回类型与FETCH语句中的INTO子句后面的类型相兼容。对于游标变量的关联查询返回的每一个字段，INTO子句后面都必须有一个与之相对应的、类型兼容的域或变量。同样，字段的个数和域的个数也应该相同。否则的话，就会产生错误。如果游标变量是强类型的话，这个错误在编译期就会发生；如果是弱类型，错误会在运行时发生。在运行时，PL/SQL会在第一次取得数据之前抛出预定义异常 ROWTYPE_MISMATCH。所以，如果我们捕获到错误，并使用一个不同的INTO子句再次执行FETCH语句，就不会丢失数据。如果我们把游标变量声明为从游标变量中取得数据的子程序的形式参数，那么我们必须指定参数模式为IN或IN OUT模式。但是，如果在子程序中还需要打开游标变量的话，就必须使用IN OUT模式。 如果我们从一个已经关闭了的或是一个未打开的游标变量中选取数据，PL/SQL就会抛出预定义异常INVALID_CURSOR。 关闭游标变量 CLOSE语句会关闭游标变量。如果执行了关闭操作，相关的结果集就不确定了。关闭操作的语法如下： CLOSEcursor_variable_name|:host_cursor_variable_name); 在下面的例子中，当最后一行数据也被处理完毕时，我们就可以关闭游标变量emp_cv： LOOPFETCHemp_cvINTOemp_rec;EXITWHENemp_cv%NOTFOUND;-processdatarecordENDLOOP;/*Closecursorvariable.*/CLOSEemp_cv; 当把游标变量作为用于关闭游标变量的子程序的形式参数时，我们必须指定它的参数模式为IN或IN OUT模式。如果我们从一个已经关闭了的或是一个未打开的游标变量中选取数据，PL/SQL就会抛出预定义异常INVALID_CURSOR。6、游标变量示例：主从表思考下面的存储过程，它的作用是搜索图书馆数据库中的图书、期刊和磁带。主表存放标题和类别编号(其中1=书，2=期刊，3=磁带)。三个详细表分别保存特定类别的信息。在调用时，存储过程会按照标题来搜索主表，然后利用主表提供的类别编号到从详细表中检索详细内容。 CREATEPACKAGEcv_typesASTYPElibcurtypISREFCURSOR;.ENDcv_types;CREATEPROCEDUREFIND_ITEM(titleVARCHAR2,lib_cvINOUTcv_types.libcurtyp)AScodeBINARY_INTEGER;BEGINSELECTitem_codeINTOcodeFROMtitlesWHEREitem_title=title;IFcode=1THENOPENlib_cvFORSELECT*FROMbooksWHEREbook_title=title;ELSIFcode=2THENOPENlib_cvFORSELECT*FROMperiodicalsWHEREperiodical_title=title;ELSIFcode=3THENOPENlib_cvFORSELECT*FROMtapesWHEREtape_title=title;ENDIF;ENDFIND_ITEM; 7、游标变量示例：客户端PL/SQL块一个客户端应用程序可能会使用下面的PL/SQL块来显示检索出来的信息： DECLARElib_cvcv_types.libcurtyp;book_recbooks%ROWTYPE;periodical_recperiodicals%ROWTYPE;tape_rectapes%ROWTYPE;BEGINget_title(:title);-titleisahostvariableFIND_ITEM(:title,lib_cv);FETCHlib_cvINTObook_rec;display_book(book_rec);EXCEPTIONWHENROWTYPE_MISMATCHTHENBEGINFETCHlib_cvINTOperiodical_rec;display_periodical(periodical_rec);EXCEPTIONWHENROWTYPE_MISMATCHTHENFETCHlib_cvINTOtape_rec;display_tape(tape_rec);END;END; 8、游标变量示例：Pro*C程序下面的Pro*C程序让用户选择一张数据表，然后使用游标变量进行查询，并返回查询结果：#include#includevoidsql_error();main()chartemp32;EXECSQLBEGINDECLARESECTION;char*uid=scott/tiger;SQL_CURSORgeneric_cv;/*cursorvariable*/inttable_num;/*selector*/struct/*EMPrecord*/intemp_num;charemp_name11;charjob_title10;intmanager;charhire_date10;floatsalary;floatcommission;intdept_num;emp_rec;struct/*DEPTrecord*/intdept_num;chardept_name15;charlocation14;dept_rec;struct/*BONUSrecord*/charemp_name11;charjob_title10;floatsalary;bonus_rec;EXECSQLENDDECLARESECTION;/*HandleOracleerrors.*/EXECSQLWHENEVERSQLERRORDOsql_error();/*ConnecttoOracle.*/EXECSQLCONNECT:uid;/*Initializecursorvariable.*/EXECSQLALLOCATE:generic_cv;/*Exitloopwhendonefetching.*/EXECSQLWHENEVERNOTFOUNDDObreak;for(;)printf(n1=EMP,2=DEPT,3=BONUS);printf(nEntertablenumber(0toquit):);gets(temp);table_num=atoi(temp);if(table_numPRINTreturn_codeRETURN_CODE-1 SQL*Plus中的REF CURSOR数据类型可以让我们声明游标变量，这样就可以使用存储子程序返回的查询结果。在下面的脚本中，我们声明了REFCURSOR类型的主变量。我们还可以在SQL*Plus中使用SET AUTOPRINT ON命令来自动地显示查询结果： CREATEPACKAGEemp_dataASTYPEemprectypISRECORD(emp_idNUMBER(4),emp_nameVARCHAR2(10),job_titleVARCHAR2(9),dept_nameVARCHAR2(14),dept_locVARCHAR2(13);TYPEempcurtypISREFCURSORRETURNemprectyp;PROCEDUREget_staff(dept_noINNUMBER,emp_cvINOUTempcurtyp);END;/CREATEPACKAGEBODYemp_dataASPROCEDUREget_staff(dept_noINNUMBER,emp_cvINOUTempcurtyp)ISBEGINOPENemp_cvFORSELECTempno,ename,job,dname,locFROMemp,deptWHEREemp.deptno=dept_noANDemp.deptno=dept.deptnoORDERBYempno;END;END;/COLUMNEMPNOHEADINGNumberCOLUMNENAMEHEADINGNameCOLUMNJOBHEADINGJobTitleCOLUMNDNAMEHEADINGDepartmentCOLUMNLOCHEADINGLocationSETAUTOPRINTONVARIABLEcvREFCURSOREXECUTEemp_data.get_staff(20,:cv) 10、在向PL/SQL块传递主游标变量时减少网络负荷在向PL/SQL传递主游标变量时，我们可以把多个OPEN-FOR语句组合在一起使用，以便减少网络流量。例如，下面的PL/SQL块： /*anonymousPL/SQLblockinhostenvironment*/BEGINOPEN:emp_cvFORSELECT*FROMemp;OPEN:dept_cvFORSELECT*FROMdept;OPEN:grade_cvFORSELECT*FROMsalgrade;OPEN:pay_cvFORSELECT*FROMpayroll;OPEN:ins_cvFORSELECT*FROMinsurance;END; 在Oracle Forms中，这种方法可能很有用，比如我们在填充一个多模块窗体的时候。 当我们向PL/SQL块传递一个主游标变量时(游标变量由该块负责打开)，游标变量指向的查询工作区在块结束后还是能够被访问的。这就能让我们的OCI或Pro*C程序在普通的游标操作中继续使用这些工作区。下面的例子中，我们在一个块中打开了多个这样的工作区： BEGINOPEN:c1FORSELECT1FROMDUAL;OPEN:c2FORSELECT1FROMDUAL;OPEN:c3FORSELECT1FROMDUAL;OPEN:c4FORSELECT1FROMDUAL;OPEN:c5FORSELECT1FROMDUAL;.END; 赋给c1、c1、c1、c1、c1的游标都可以正常使用。当使用完毕时，只要像下面这样简单的关闭就可以了：BEGINCLOSE:c1;CLOSE:c2;CLOSE:c3;CLOSE:c4;CLOSE:c5;.END; 11、避免游标变量的错误如果在赋值操作中的两个游标变量都是强类型，那么它们必须有着完全相同的数据类型。下面的例子中，虽然游标变量的返回类型相同，但是在赋值操作时也会引起异常，这是因为它们的数据类型不相同： DECLARETYPEempcurtypISREFCURSORRETURNemp%ROWTYPE;TYPEtmpcurtypISREFCURSORRETURNemp%ROWTYPE;PROCEDUREopen_emp_cv(emp_cvINOUTempcurtyp,tmp_cvINOUTt