搜索
高级PL/SQL-集合及游标单世民SsmPL/SQL集合•Oracle的第一个集合就是在Oracle7中引入的PL/SQL表。Oracle8又添加了两种新的集合类型，同时PL/SQL表也被更名为index-by表。Oracle10g中index-by表又被再次更名，现在它的名字叫联合数组。集合使我们可以在表的一行记录中存储多个数据集。•集合就是列表，可能有序也可能无序。所有的集合（不包括记录）都是一维的，可使用标准的数据类型或用户自定义的数据类型（记录或对象）。虽然记录和对象更像是多维集合，但Oracle数据库还是将其看作一维集合。集合类型是在PL/SQL程序设计中经常使用的一些很关键的结构类型。SsmPL/SQL集合•记录•PL/SQL表（联合数组，index-by表）•嵌套表•VARRAYSsm记录•记录是在Oracle7引入的，它是有几个相关值构成的复合变量，是表中单行数据结构的一个镜像，可用于支持SELECT语句的返回值。记录将一行数据看成一个单元，而不是将每一列单独处理Ssm记录•当在PL/SQL中使用记录时，首先需要定义记录的结构，然后可以设置记录类型的变量。这与通常定义变量的方式不同，因为大多数标量类型或%type/%rowtype类型都已经定义了。当用户声明了记录类型的变量之后，就可以为记录变量的单独属性赋值。Ssm记录类型的定义•显式定义•隐式定义使用%ROWTYPE和%TYPETYPErecord_typeISRECORD(Variable_1datatype1[,Variable_2datatype2[,...]]);Ssm记录•记录示例DECLARETYPENewRecordTYPEISRECORD(idnumber,namevarchar2(10));recTestNEWRECORDTYPE;BEGINSELECTempno,enameINTOrecTestFROMempWHEREempno=7369;DBMS_OUTPUT.PUT_LINE(recTest.id||'号员工的姓名是'||recTest.name);END;/SsmVARRAY•Varray（Varrays）是Oracle10g数据类型或用户自定义的记录/对象类型的一维结构体。可以在表、记录和对象定义中使用varray，也可以在SQL和PL/SQL中访问它们。可以使用顺序索引值来引用varray中的元素。SsmVARRAY•定义语法TYPEtype_nameIS{VARRAY|VARINGARRAY}(size)OFdata_type[NOTNULL];CREATEORREPLACETYPEtype_nameIS{VARRAY|VARINGARRAY}(size)OFdata_type[NOTNULL];SsmVARRAY•Varray是密集数组，作用更像是传统的程序设计数组，存储在永久性的表中，并可以通过SQL语句进行访问。在创建时，它们都有一个固定的大小，而且这个大小不能改变。Varray中的元素可以使用数字序列的下标进行访问，其下标索引值从1开始。•默认情况下，Varray中可以出现null值。SsmVARRAY•VARRAY示例DECLARETYPEint_varrayISVARRAY(2)OFINTEGER;varray_intINT_VARRAY:=int_varray(null,null);BEGINvarray_int(1):=3;varray_int(2):=4;FORiIN1..2LOOPdbms_output.put_line('int_varray['||i||']='||varray_int(i));ENDLOOP;END;/使用Varray类型数据必须进行初始化！！SsmVARRAY•VARRAY与SQL——不得不说的故事Varray的强大功能并不局限在过程化的程序设计当中。从Oracle8到Oracle10g，Varray提供了一些独特的表现数据的功能。这也是Oracle数据库能成为闻名的对象-关系数据库管理系统（ORDBMS）的原因之一。SsmVARRAY•VARRAY与SQLCREATEORREPLACETYPEaddr_varrayASVARRAY(2)OFVARCHAR2(50CHAR);CREATETABLEempaddr(idnumber(38)notnull,namevarchar2(20)notnull,addressADDR_VARRAYnotnull,constraintpk_empaddrprimarykey(id));SsmVARRAY•VARRAY与SQLINSERTINTOempaddrVALUES(1,’ShanShimin’,addr_varray(‘Office302ofSchoolofSoftware’,‘DalianUniversityofTechnology’));UPDATEempaddrSETaddress=addr_varray(‘Office303ofSchoolofSoftware’,‘DalianUniversityofTechnology’)WHEREID=1;插入操作是以要么全有要么全无的方式处理Varray类型数据的SsmVARRAY•VARRAY与SQL•上述查询的方法无法得到什么有用的输出，需要定义一个嵌套表集合结构，才能真正访问到varray类型的数据。SELECTaddressFROMempaddr;SsmVARRAY•VARRAY与SQLCREATEORREPLACETYPEvarray_nested_tabISTABLEOFVARCHAR2(50CHAR);SELECTCOLUMN_VALUEFROMTHE(SELECTCAST(addressASvarray_nested_tab)FROMempaddrWHEREid=1);保留字column_value是访问嵌套表中记录行的一种方法（嵌套表中还有一个隐藏列nested_table_id，映射到父表中的记录行上）。保留字THE用于从嵌套表的一个查询中检索出column_value列。Ssm嵌套表•嵌套表是在Oracle8中引入的集合类型。最开始被定义为密集数组，但是将记录从中删除后，它就变成稀疏数组。它可以存储在永久性的数据表中，可以使用SQL进行访问，也可以进行动态扩展。•嵌套表的大小是可以动态分配的。Ssm嵌套表•定义语法•嵌套表的使用方式与varray类似，但是两者至少在两方面存在区别：¤嵌套表可以动态调整它的大小。¤使用嵌套表定义数据列时，此数据列不能采用NOTNULL约束。CREATEORREPLACETYPEtype_nameASTABLEOFdata_type[NOTNULL];SsmPL/SQL表•PL/SQL表，有时也称为索引表，是可以在PL/SQL例程中使用、能够模仿数组的非永久表。•用户可以定义一个PL/SQL表类型，然后声明这种类型的变量，接下来就可以将记录增加到用户的PL/SQL表中，并且采用与引用数组元素大体相同的方式引用这些记录。这些表是一维数组，不要将它们与Oracle表对象混淆。SsmPL/SQL表•PL/SQL表示例setserveroutputonDECLARETYPEmy_text_table_typeIStableofvarchar2(20)INDEXBYbinary_integer;l_text_tablemy_text_table_type;BEGINl_text_table(1):='一条高速公路';l_text_table(2):='一大片麦田';dbms_output.put_line('我们有'||l_text_table.count||'个varchar2变量');dbms_output.put_line('变量(1)='||l_text_table(1));dbms_output.put_line('变量(2)='||l_text_table(2));END;/SsmPL/SQL表•PL/SQL表不必是密集数组，可以是稀疏数组，其下标值可以是非常离散的值，甚至可以是字符串的哈希值。此时，要遍历PL/SQL表就需要借助于其特定的成员变量（first，last，next）。其中，next类似于Java语言中对于集合类型的迭代器（Iterator）。SsmPL/SQL表setserveroutputonDECLARETYPEmy_text_table_typeISTABLEOFVARCHAR2(200)INDEXBYbinary_integer;l_text_tablemy_text_table_type;l_indexNUMBER;BEGINFORemp_recIN(select*fromemp)loopl_text_table(emp_rec.empno):=emp_rec.ename;ENDLOOP;l_index:=l_text_table.first;LOOPEXITWHENl_indexISNULL;dbms_output.put_line(l_index||':'||l_text_table(l_index));l_index:=l_text_table.next(l_index);ENDLOOP;END;/Ssm游标•游标是一种PL/SQL控制结构，可以对SQL语句的处理进行显式控制，便于对游标的行数据逐条进行处理。•游标分类¤显式游标¤隐式游标Ssm显式游标•显式游标是必须通过编写必要的PL/SQL例程来进行管理的游标。游标的整个生命期都在用户的控制之下，因此，用户可以详细地控制PL/SQL怎样在结果集中访问记录。用户可以定义游标、打开游标、从游标中获取数据、使用合适的PL/SQL代码关闭游标。Ssm显式游标•定义语法：•显式游标的使用方法：1.定义（CURSORcur_nameIS..）2.打开（OPENcur_name）3.取值（FETCHcur_nameINTO..）4.关闭（CLOSEcur_name）CURSORcursor_name[(parameter[,parameter]…)][RETURNreturn_type]ISselectstatement;Ssm遍历显式游标的3种方式1.简单LOOP2.WHILELOOP3.FORLOOP使用FORLOOP方式的独特之处在于它不需要显式的OPEN、FETCH或CLOSE命令。另外，FORLOOP还使用了一个在代码块的声明部分从没有声明过的一个变量Ssm游标的使用DECLARECURSORcurTestISSELECT*FROMemp;recEmpemp%ROWTYPE;BEGINOPENcurTest;FETCHcurTestINTOrecEmp;WHILEcurTest%FOUNDLOOPDBMS_OUTPUT.PUT_LINE(recEmp.empno||'号员工的姓名是'||recEmp.ename);FETCHcurTestINTOrecEmp;ENDLOOP;CLOSEcurTest;END;/Ssm游标的属性%FOUND检验FETCH语句是否取得了记录%ISOPEN检查游标当前是否处在打开状态%NOTFOUND%FOUND的相反属性%ROWCOUNT检测任意给定的时刻，已从游标中获取的记录行数%BULK_EXCEPTIONS为批操作或BulkCollect操作中产生的异常提供相关信息%BULK_ROWCOUNT提供Bulk操作过程中更改的行数信息Ssm游标的使用DECLARECURSORcur_with_Param(didnumber)ISselect*fromempwheredeptno=did;recempemp%rowtype;BEGINOPENcur_with_Param(10);LOOPIFcur_with_Param%NOTFOUND=trueTHENdbms_output.put_line('游标的当前NOTFO