PLSQL基础知识点总结 （2）

PLSQL中提供了三种数据的形式，功能还是比较强大的。但是总的来说，PLSQL中的数组知识掌握最简单的那种，其他大致了解就可以了。因为从实际应用的角度来说，在PLSQL中用到数组的几率不是很大。这主要是由于PLSQL跟数据库的紧密结合特性所决定的，数据库的表可以很轻易得实现基本的数组功能。当然这是我个人的一点理解，不一定正确。下面是详细介绍

 

 

1、PLSQL中的数组共分三类：

 

I  - 嵌套表(Nested tables)

TYPEnested_typeISTABLEOFVARCHAR2 ( 30 ) [not null];

--值为varchar2的数组，下标为默认int ；

特征：可删除信息，下标不变

 

II - 变长数组(Variable-SizedArrays)

TYPECalendarISVARRAY(366 ) OFNUMBER;

--366个number数组，下标1-366( 不是 0-365)

特征：一般在可预知数组个数的情况下使用，类似其他语言的Array

 

III- 索引表(Associative Array)

TYPEpopulation_typeISTABLEOFNUMBER

INDEXBYVARCHAR2( 64 );

--下标是varchar2，值是number 的数组

特征：可使用不连续数字、负数、字符作为下标，长度大小可变

      在创建数组时便分配内存，无需之后申请

      index by 只能跟BINARY_INTEGER、PLS_INTEGER、VARCHAR2三种类型

 

 

2、数组中的赋值错误问题：

 

DECLARE

 TYPEwordlistISTABLEOFVARCHAR2 ( 5 );

 words wordlist;

BEGIN

 words( 1 )      := 10; --COLLECTION_IS_NULL,未赋空间

 words         := wordlist(10, 20 , 30 ); --进行赋值

 words( 1 )      := 'yes'; --正确

 words( 2 )      := words( 1 ) || 'no';--正确

 words( 3 )      := 'longer than 5 characters';--VALUE_ERROR，字符过长

 words( 'B' )    := 'dunno'; --VALUE_ERROR，下标错误

 words( 4 )      := 'maybe'; --SUBSCRIPT_BEYOND_COUNT，下标超限

END;

 

在声明 words wordlist; 之后数组未初始化，完全没有用处

必须进行初始化，如：

words := wordlist( 10 , 20 , 30 );--定值初始化；

words := wordlist( );--空值初始化，任何数组必须先初始化；

words.extend(n);--末尾增加N位空间，不加N则为默认增加1位；

然后可以赋值

 

 

3、数组相关集合函数：

 

1、EXISTS(判断第i位是否存在)

IFcourses.EXISTS(i)THEN

 courses(i)    := new_course;

ENDIF;

 

2、COUNT(数组中的 元素个数)

FORiIN1.. courses.COUNTLOOP...

注意：COUNT会忽略已经被删除的元素

 

3、LIMIT(集合的最大容量)

IF(projects.COUNT +15) < projects.LIMITTHEN...

注意：LIMIT一般只对变长数组有效(其他两类均返回NULL)

 

4、FIRST和LAST(第一个和最后一个元素的下标)

FORiINcourses.FIRST .. courses.LASTLOOP...

注意：返回值是下标，而不是值！在遍历元素时，FIRST和LAST都会忽略被删除的元素

 

5、PRIOR和NEXT(返回索引为n的前驱/后驱下标)

n := courses.PRIOR(courses.FIRST); --assigns NULL to n

注意：返回值是下标，而不是值！在遍历元素时，FIRST和LAST都会忽略被删除的元素

 

6、EXTEND(扩大集合容量)

courses.EXTEND(m,n ); --将第n个元素的值复制m份加到集合末端

注意：m默认为1，n默认为null，m包含被删除元素

 

7、TRIM(缩减集合容量)

courses.TRIM(3);--与extend相反

 

8、DELETE(删除集合元素)

courses.DELETE     --删除全部

courses.DELETE(2)  --删除第2个元素

courses.DELETE(2,5 ) --删除第2到第5 个元素

注意：使用delete的时候必须要结合3中数组的不同特征！

 

 

4、Exception的类型及原因：

 

COLLECTION_IS_NULL       ---调用一个空集合的方法,集合未被初始化

NO_DATA_FOUND              ---下标索引指向一个被删除的元素，或是关联数组中不存在的元素

SUBSCRIPT_BEYOND_COUNT  ---下标索引值超过集合中的元素个数

SUBSCRIPT_OUTSIDE_LIMIT---下标索引超过允许范围之外

VALUE_ERROR                 ---下标索引值为空，或是不是指定的下标类型

 

 

5、关于数组的特有批量绑定ForAll

 

1、语法结构：

 

FORALLiINpnums.FIRST .. pnums.LAST

INSERTINTOpartnoVALUES(pnums(i));  ---注意：不用再Loop了

 

 

2、可使用%BULK_ROWCOUNT属性来计算FORALL语句所影响到的行数

 

IFSQL%BULK_ROWCOUNT ( 3 ) = 0THEN...

 

表示如果第3次操作没有对数据影响的行数为0话……

注意%BULK_ROWCOUNT的值是可以大于1的，比如批量插入等

 

3、使用%BULK_EXCEPTIONS属性来控制FORALL异常

 

DECLARE

 TYPEnumlistISTABLEOFNUMBER ;

 

 num_tab      numlist  := numlist(10,0,11,12,30,0,20,199,2,0,9,1);

 ERRORS      NUMBER;

 dml_errors   EXCEPTION;

 PRAGMAEXCEPTION_INIT(dml_errors, -24381 );

BEGIN

 FORALLiINnum_tab.FIRST .. num_tab.LASTSAVEEXCEPTIONS

   DELETEFROMemp

         WHEREsal >500000/ num_tab(i);

EXCEPTION

 WHENdml_errorsTHEN

   ERRORS   :=SQL%BULK_EXCEPTIONS.COUNT;

   DBMS_OUTPUT.put_line('Number of errors is '|| ERRORS);

 

   FORiIN1..ERRORSLOOP

     DBMS_OUTPUT.put_line(  'Error '

                          || i

                          ||' occurred during '

                          ||'iteration '

                          ||SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);

     DBMS_OUTPUT.put_line(  'Oracle error is '

                          ||SQLERRM(-SQL% BULK_EXCEPTIONS (i).ERROR_CODE));

   ENDLOOP;

END;