plsql專題

PLSQL學習(二) 數組專題

    PLSQL中提供了三種數據的形式，功能還是比較強大的。但是總的來說，PLSQL中的數組知識掌握最簡單的那種，其他大致了解就可以了。因為從實際應用的角度來說，在PLSQL中用到數組的幾率不是很大。這主要是由于PLSQL跟數據庫的緊密結合特性所決定的，數據庫的表可以很輕易得實現基本的數組功能。當然這是我個人的一點理解，不一定正確。下面是詳細介紹

1、PLSQL中的數組共分三類：

I  - 嵌套表(Nested tables)

TYPE nested_type IS TABLE OF VARCHAR2 ( 30 ) [not null];

-- 值為 varchar2 的數組，下標為默認 int ；

特征：可刪除信息，下標不變

II - 變長數組(Variable-Sized Arrays)

TYPE Calendar IS VARRAY ( 366 ) OF NUMBER ;

--366 個 number 數組，下標 1-366( 不是 0-365)

特征：一般在可預知數組個數的情況下使用，類似其他語言的Array

III- 索引表(Associative Array)

TYPE population_type IS TABLE OF NUMBER

INDEX BY VARCHAR2 ( 64 );

-- 下標是 varchar2 ，值是 number 的數組

特征：可使用不連續數字、負數、字符作為下標，長度大小可變

      在創建數組時便分配內存，無需之后申請

      index by 只能跟BINARY_INTEGER、PLS_INTEGER、VARCHAR2三種類型

2、數組中的賦值錯誤問題：

DECLARE

  TYPE wordlist IS TABLE OF VARCHAR2 ( 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位是否存在)

IF courses.EXISTS(i) THEN

  courses(i)    := new_course;

END IF ;

2、COUNT(數組中的元素個數)

FOR i IN 1 .. courses.COUNT LOOP ...

注意：COUNT會忽略已經被刪除的元素

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

IF (projects.COUNT + 15 ) < projects.LIMIT THEN ...

注意：LIMIT一般只對變長數組有效(其他兩類均返回NULL)

4、FIRST和LAST(第一個和最后一個元素的下標)

FOR i IN courses.FIRST .. courses.LAST LOOP ...

注意：返回值是下標，而不是值！在遍歷元素時，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、語法結構：

FORALL i IN pnums.FIRST .. pnums.LAST

INSERT INTO partno VALUES (pnums(i));   ---注意：不用再Loop了

2、可使用%BULK_ROWCOUNT屬性來計算FORALL語句所影響到的行數

IF SQL % BULK_ROWCOUNT ( 3 ) = 0 THEN ...

表示如果第3次操作沒有對數據影響的行數為0話……

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

3、使用%BULK_EXCEPTIONS屬性來控制FORALL異常

DECLARE

  TYPE numlist IS TABLE OF NUMBER ;

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

  ERRORS        NUMBER ;

  dml_errors   EXCEPTION ;

  PRAGMA EXCEPTION_INIT (dml_errors, - 24381 );

BEGIN

  FORALL i IN num_tab.FIRST .. num_tab.LAST SAVE EXCEPTIONS

    DELETE FROM emp

          WHERE sal > 500000 / num_tab(i);

EXCEPTION

  WHEN dml_errors THEN

    ERRORS     := SQL %BULK_EXCEPTIONS.COUNT;

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

    FOR i IN 1 .. ERRORS LOOP

      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));

    END LOOP ;

END ;