PL/SQL 記錄與集合

一。記錄類型簡介

不使用記錄的PLSQL語句塊

declare

v_empno  NUMBER;

v_ename  varchar2(20);

v_job varchar2(9);

v_mgr NUMBER;

v_hiredate DATE;

v_sal NUMBER(7,2);

v_comm  NUMBER(7,2);

v_deptno NUMBER(2);

begin

select empno,ename,job,mgr,hiredate,sal,comm,deptno

into

v_empno,v_ename,v_job,v_mgr,v_hiredate,v_sal,v_comm,v_deptno

from emp where empno = :empno;

insert into emp_copy

(empno,ename,job,mgr,hiredate,sal,comm,deptno)

values

(v_empno,v_ename,v_job,v_mgr,v_hiredate,v_sal,v_comm,v_deptno);

exception

when others

then

null;

end; 

使用記錄的PLSQL語句塊

type t_emp is record （

）;

emp_info t_emp;

select * into emp_info from emp where empno=:empno;

insert into emp_copy values emp_info;

end;

使用%ROWTYPE

type emp_rec is record (

dept_row dept%ROWTYPE,

empno NUMBER.

ename varchar(20),

job varchar(10),

sal NUMBER(7,2)

);

emp_info emp_rec;

初始化操作

empname varchar(12) := '李斯特'，

empno NUMBER NOT NULL DEFAULT 7369,

hiredate DATE DEFAULT SYSDATE,

empinfo emp_rec;

為記錄指派并讀取記錄内容

type emp_rec is record(

empname varchar(12) :='李斯特',

empno NUMBER NOT NULL default 7369,

hiredate DATE default sysdate,

empinfo.empname:='史密斯';

empinfo.empno:=7010;

empinfo.hiredate:=TO_DATE('1982-01-01','YYYY-MM-DD');

empinfo.sal:=5000;

dbms_output.put_line('員工名稱：'|| empinfo.empname);

dbms_output.put_line('員工編号：'|| empinfo.empno);

dbms_output.put_line('雇傭日期：'|| TO_CHAR(empinfo.hiredate,'YYYY-MM-DD'));

dbms_output.put_line('員工薪資：'|| empinfo.sal);

為記錄類型附記錄類型的值

empno NUMBER,

ename varchar2(20)

type emp_rec_dept is record(

emp_info1 emp_rec;

emp_info2 emp_rec;

emp_info3 emp_rec_dept;

procedure printrec(empinfo emp_rec)

as

dbms_output.put_line('員工名稱：'|| empinfo.ename);

emp_info1.empno:=7890;

emp_info1.ename:='張大千';

dbms_output.put_line('emp_info1的資訊如下：');

printrec(emp_info1);

emp_info2:=emp_info1;

dbms_output_put_line('emp_info2的資訊如下：');

printrec(emp_info2);

如果在上面添加emp_info3:=emp_info1; 則會報錯

如果一個記錄類型的變量賦給另一個記錄類型，兩個記錄的類型必須完全一緻

%ROWTYPE定義的記錄賦給标準記錄類型

type dept_rec is record(

deptno NUMBER(10),

dname varchar2(30),

loc varchar2(30)

dept_rec_db dept%ROWTYPE;

dept_info dept_rec;

select * into dept_rec_db from dept where deptno=20;

dept_info:=dept_rec_db;

盡管dept_info 與 dept_rec_db 并不是相同的dept_rec類型 但是因為%ROETYPE的運作機制及dept_rec中的記錄成員與dept表相同，是以指派是成功的

如果要清空一個記錄類型的變量，可以簡單的為該變量赴一個空的或未初始化的記錄類型，即可清空所有的記錄成員值

操縱記錄類型  insert資料

deptno NUMBER(2),

dname varchar2(14),

loc varchar2(13)

dept_row dept%ROWTYPE;

dept_norow dept_rec;

dept_row.deptno:=70;

dept_row.dname:='工程部';

dept_row.loc:='上海';

dept_norow.deptno:=80;

dept_norow.dname:='電腦部';

dept_no row.loc:='北京';

insert into dept values dept_row;

insert into dept values dept_norow;

commit;

update資料

dname VARCHAR(14),

select * into dept_info from dept where deptno = 80;

dept_info.dname:='資訊管理部';

update dept set row = dept_info where deptno = dept_info.deptno;

在returning子句中使用記錄

dept_returning dept%ROWTYPE;

select * into dept_info from dept where deptno=20;

update dept set row = dept_info where deptno = dept_info.deptno returning deptno,dname,loc into dept_returning;

dept_info.deptno:=12;

dept_info.dname:='維修部';

insert into dept values dept_info returning deptno,dname,loc into dept_returning;

delete from dept where deptno = dept_info.deptno returning deptno,dname,loc into dept_returning;

1.在update語句中set子句的右邊可以使用記錄變量

2.在insert語句中values子句的後面，可以使用記錄插入資料，values後面不需要使用括号

3.在returning語句中into子句的後面，可以将受影響的行插入到記錄變量

4.記錄變量是不允許出現在select清單，where子句，group by子句或order by子句中

使用嵌套記錄

v_empno NUMBER,

v_ename varchar2(20),

v_job varchar2(9),

v_mgr NUMBER(4),

v_hiredate DATE,

v_sal NUMBER(7,2),

v_comm NUMBER(7,2),

v_dept_rec dept_rec              定義嵌套的

dept_info dept_rec;

select * into dept_info from dept where deptno = (select deptno from emp where empno=7369);

emp_info.v_dept_rec:=dept_info;

select empno,ename,job,mgr,hiredate,sal,comm into emp_info.v_empno,emp_info.v_ename,emp_info.v_job,emp_info.v_mgr,emp_info.v_hiredate,emp_info.v_sal,emp_info.v_comm  from emp where empno=7369;

dbms_output.put_line('員工所屬部門為：'||emp_info.v_dept_rec.dname);

集合簡介

集合類似于進階語言中的清單或一維數組，主要用來存儲具有相同類型的元素的有序集合，每一個元素都有唯一的下标來辨別目前元素在集合中的位置

在PLSQL提供3種類型的集合

1.索引表：也稱為關聯數組，這種類型的集合可以通過數字或字元串作為下标來查找其中的元素，類似于其他語言中的哈希表，索引表是一種僅在PLSQL中使用的資料結構

2.嵌套表：使用有序數字作為嵌套表的下标，可以容納任意個數的元素，嵌套表與索引表最大的差別在于可以定義嵌套表類型，把嵌套表存儲到資料庫中，并能通過sql語句進行操作

3.變長數組：在定義時儲存固定數量的元素，但可以在運作時改變其容量，變長數組與嵌套表一樣，使用有序數字作為下标，也可以儲存到資料庫中，但是不如嵌套表靈活。

索引表隻能在PLSQL中使用，如果需要在記憶體中儲存和維護清單，則優先選擇索引表

如果集合的内容還要存儲到資料庫中，那麼可以在嵌套表和變長數組之間進行選擇。

嵌套表和索引表統稱為PL/SQL 表

  非連續存儲

索引表    7356   3432  4234 4235   8924

   5   -4    7    13    jasd

下标可以是正數  負數  字元  元素個數不确定

索引表不需要進行初始化，沒有構造方法，在為其指派之前不需要配置設定初始空間，是以不需要動态地擴充其容量

索引表不僅可以使用數字作為索引下标，而且可以使用變長的字元串來索引其中的元素

當使用數字類型作為索引下标時，索引鍵可以為正數，負數或0，并且數字可以不連續

type type_name as table of element_type[not null] index by [PLS_INTEGER | BINARY_INTEGER | VARCHAR2(SIZE)];

雇傭日期索引表集合

type hiredate_idxt is table of date index by pls_integer;

部門編号集合

type deptno_idxt is table of dept.deotno%TYPE NOT NULL index by pls_integer;

由部門名稱辨別的部門記錄的集合

type deptname_idxt is table of dept%ROWTYPE index by dept.dname%TYPE;

定義集合的集合

type private_collection_tt is table of deptname_idxt index by varchar2(100);

操縱索引表

TYPE idx_table IS table of varchar(12)

index by PLS_INTEGER;

v_emp idx_table;

v_emp(1) := 'a';

v_emo(20):='b';

v_emp(40):='c';

v_emp(-10):='d';

dbms_output.put_line(v_emp(8));

通路一個未配置設定記憶體的元素，将抛出異常

由于未配置設定的元素會觸發異常，是以可以使用exists語句檢查索引表是否存在值

IF v_emp.exists(8) THEN

end if;

運用字元串作為索引表的下标類型，類似于進階語言中的哈希表，通過健來尋找值

type idx_deptno_table is table of number(2)

index by varchar2(20);

v_deptno idx_deptno_table;

v_deptno('a'):=10;

v_deptno('b'):=20;

v_deptno('c'):=30;

dbms_output.put_line('a編号為：'|| v_deptno('a'));

a b c 可以是中文 例如 财務部  研究部

在使用varchar2作為索引鍵時，必須為varchar2指定一個大小，以便存放合适的索引鍵。也可以在索引表的定義中使用%TYPE或%ROWTYPE

type idx_dept_table is table of dept%ROWTYPE

index by dept.dname%TYPE;

v_dept idx_dept_table;

cursor dept_cur

is

select * from dept;

for deptrow in dept_cur

loop

v_dept (deptrow.dname) :=deptrow;

dbms_output.put_line(v_dept(deptrow.dname).loc);

end loop;

将遊标行資料檢索并賦給索引表，這相當于對dept表進行了一次複制，建立了dept表的一個副本。

定義嵌套表

嵌套表是對索引表的擴充，與索引表最大的不同在于嵌套表可以存儲到oracle資料庫表中，而索引表僅僅是記憶體表，除此之外，使用嵌套表時必須使用其構造文法對嵌套表進行初始化。嵌套表沒有index by 子句，這是與索引表之間最明顯的差別，因為嵌套表必須有序的關鍵字建立，而且關鍵字不能為負數。

嵌套表

3123 null 3124 7879 6783

1     2    3    4    5

元素個數不确定 下标必須是有序的 不能為負數 隻能從1開始 

type dept_table is table of dept%ROWTYPE; 部門資訊嵌套表

type emp_name_table is table of varchar2(20);  員工名稱嵌套表

type deptno_table is table of number(2);     部門編号嵌套表

dept_info dept_table;  聲明嵌套表變量   聲明并初始化嵌套表變量

emp_name_info emp_name_table := emp_name_table('張小','李斯特');

deptno_info   deptno_table := deptno_table(20，30，40);

操縱嵌套表

由于在嵌套表使用之前必須進行構造，未構造的嵌套表被自動地賦初始值NULL。

嵌套表的初始化與通路

type emp_name_table is table of varchar2(20);      員工名稱嵌套表

type deptno_table is table of number(2);           部門編号嵌套表

deptno_info deptno_table;

emp_name_info emp_name_table:=emp_name_table（'張小','李斯特'）;

dbms_output.put_line（'員工1：'||emp_name_info(1)）;      通路嵌套表元素

dbms_output.put_line（'員工2：'||emp_name_info（2））;

if deptno_info is null                     判斷嵌套表是否被初始化

deptno_info:=deptno_table（）;     

deptno_info.EXTEND(5);

for i IN 1 .. 5                    擴充元素的個數

loop                               循環周遊嵌套表元素個數

deptno_info(i):= i*10;

end loop;                           顯示部門個數

dbms_output.put_line('部門個數:'||deptno_info.COUNT);

為了向deptno_info嵌套表中插入元素，必須首先使用嵌套表的EXTEND方法擴充指定的元素個數，然後才能通過下标進行通路。

下面介紹EXTEND具有相同的效果

IF deptno_info is null

deptno_info:=deptno_table（NULL,NULL,NULL,NULL,NULL）;

與索引表一樣，嵌套表也可以是不連續的，可以通過delete來删除嵌套表中的元素。

資料庫中的嵌套表

為了讓嵌套表類型能在資料表中使用，要求嵌套表類型必須儲存到資料字典中，是以需要使用create type 語句建立一個持久的嵌套表類型。

create or replace type type_name

as table of element_type[NOT NULL];

在資料表中使用嵌套表示例

建立嵌套表類型

create type empname_type is table of varchar(20);

建立資料表時指定嵌套表列，同時要使用STORE AS 指定嵌套表的存儲表

create table dept_nested

(

deptno number(2)，

dname varchar2(20)，

emplist empname_type

)NESTED TABLE emplist STORE AS empname_table;

1.使用create type定義一個嵌套表類型，在定義了類型之後，類型被儲存到oracle資料字典中，以便像對待普通的列一樣來使用表類型

2.在資料表定義的末尾要使用NESTED TABLE語句給嵌套表指明一個存儲表的名字，用來存儲嵌套表裡的資料。

表中嵌套表列的内容是單獨進行存放的，oracle将嵌套表列的内容存儲到建立表時指定的存儲表中。資料庫表中的列實際上是指向對存儲表的一個引用，類似于一個REF變量。

操縱嵌套表列資料

       emp_list  empname_type

:= empname_type('史密斯'，'傑克'，'馬丁','斯大林'，'布什'，'小平');

insert into dept_nested

values(10,'國務院',emp_list);

values(20，'财務司'，empname_type('李林'，'張傑','馬新'，'蔡文'));

select emplist into emp_list from dept_nested where deptno=10;

emp_list(1):='少校';

emp_list(2):='大校';

emp_list(3):='中校';

emp_list(4):='學校';

emp_list(5):='無效';

emp_list(6):='藥效';

updata dept_nested

set emplist=emp_list

where deptno=10;

要向資料庫表中插入嵌套表列資料，有以下2種方式

（1）定義一個嵌套表變量，為嵌套表變量指派，然後在insert語句中使用嵌套表變量插入一條記錄

(2) 直接在資料表中使用嵌套表結構構造文法構造一個嵌套表執行個體進行插入。

不能再where子句中使用嵌套表，以及其他一些隐含需要比較的地方，比如order by，group by，distinct子句中都不能使用嵌套表。

當使用sql*plus 查詢包含嵌套表列的資料表時，sql*plus 将列出嵌套表列中的内容。

select * from dept_nested;

為了正确删除類型empname_type，必須使用drop table 語句先把dept_nested删除，然後在調用drop type語句删除empname_type類型。

定義變長數組，是指數組長度可變化的數組，變長數組與C或Java數組的資料類型非常相似。

變長數組在聲明時會具有一個上界值，元素插入到變長數組中時，以索引1開始，直到在變長數組中聲明的最大長度。而且變長數組的元素在記憶體中是連續存儲的，變長數組中的元素順序相對較固定，變長數組與進階語言數組的比較。

變長數組

7345  7845 8932  2342  5364   元素個數固定

1      2    3    4     5  

下标必須為有序類型，不可以為負數，隻能從1開始

定義

type projectlist is varray(50) of varchar2(16);

與嵌套表一樣，變長數組在未初始化之前，其本身為null，如果通路一個未被初始化的變長數組，将觸發ORA-06531這樣的異常。是以必須使用構造函數進行初始化，當初始化一個變長數組時，在構造函數中傳入的參數個數是變長數組實際具有的元素個數，可以使用COUNT傳回目前變長數組已配置設定空間的元素個數，或者是使用EXTEND擴充元素的個數。

操縱變長數組

初始化示例

type empno_type is varray(10) of number(4);

project_list projectlist:=projectlist('網站','ERP','CRM'，'CMS');

empno_list empno_type;

dbms_output.put_line(project_list(3));

project_list.EXTEND;

project_list(5):='WORKFLOW';

empno_list:=empno_type(7011,7012,7013,7014，NULL,NULL,NULL,NULL,NULL,NULL);

empno_list(9):=8011;

dbms_OUTPUT.put_line（empno_list(9)）;

資料庫中的變長數組

和嵌套表一樣，變長數組也可以作為資料庫表的列資料被存儲到資料庫中，在使用前，必須先使用create type語句在資料字典中建立一個變長數組的類型。

建立并使用變長數組類型

create or replace type empname_varray_type is varray(20) of varchar2(20);

create table dept_varray

{

emplist empname_varray_type

};

操縱變長數組列

emp_list empname_varray_type:=empname_varray_type('史密斯','傑克','湯姆','麗莎'，'簡','史泰龍');

insert into dept_varray values(20，'維修組'，emp_list);

insert into dept_varray values(30，'機加工',empname_varray_type('張3','劉七','趙五','阿4','阿5','阿六'));

select emplist

into emp_list

from dept_varray

where deptno=20;

emp_list(1):='傑克張';

update dept_varray set emplist=emp_list where deptno=20;

delete from dept_varray where deptno =30;

select * from dept_varray;

選擇集合類型

嵌套表與索引表，這兩種類型統稱為PL/SQL

嵌套表自索引表擴充而來，是以嵌套表包含索引表的所有表屬性

嵌套表與索引表都是使用下标對集合中的元素進行通路

嵌套表與索引表的資料類型具有相同的結構

兩種表的不同點

1.嵌套表可以存儲到資料庫中，而索引表不能，是以如果表類型需要儲存到資料庫中，應該考慮使用嵌套表

2.嵌套表合法的下标範圍是1~214748361，下标不能為負數;而索引表可以為負下标，範圍為-2147483647~2147483647 是以如果考慮帶負數的下标，應該使用索引表

3.索引表在每次調用語句塊或在包初始化時在記憶體中自動建構，能夠儲存容量不固定的資訊，因為它的長度是大小可變的，其值不能為NULL;而嵌套表是一種對象類型，如果不顯式使用構造函數，則其值為NULL，可以使用IS NULL進行檢查。

4.嵌套表可以使用其他的方法，比如EXTEND和TRIM等方法進行操作，而索引表不需要.

5.PL/SQL會自動在主機數組和索引表之間進行轉換，而嵌套表不能再主機數組之間進行轉換。

如果需要将集合類型儲存到資料庫中，可以再變長數組與嵌套表之間進行選擇，這兩種類型的相同之處

1.變長數組與嵌套表都使用下标符号對單個元素進行通路，在使用前都必須使用構造函數進行初始化

2.都可以存儲在資料庫表中，都可以應用集合方法

差別決定了是否選擇此種類型

1.變長數組一旦聲明，元素數目就被固定，而嵌套表沒有一個明确的大小上限

2.當存儲到資料庫中時，變長數組保持了元素的排序和下标的值，而嵌套表則不同

是以基本的結論：如果是隻需要在PL/SQL中使用的集合。且元素個數較少，則優先考慮索引表，而如果要存儲到資料庫中，則需要選擇嵌套表。如果資料元素可以固定，則優先考慮使用變長數組。

使用集合方法

集合方法隻能在PL/SQL使用 不能再sql中使用

在PL/SQL提供的集合方法中，EXISTS,COUNT，LIMIT，FIRST，LAST，PRIOR和NEXT是函數;

EXTEND，TRIM和DELETE是過程。EXISTS,PRIOR，NEXT，TRIM，EXTEND和DELETE對應的參數是集合的下标索引，通常是整數，但對于關聯數組來說也可能是字元串。

隻有EXISTS能用于空集合，如果在空集合上調用其他方法，會抛出異常。

使用EXISTS方法：

用于判斷集合中指定的元素是否存在，如果指定的元素存在，傳回true否則false。使用這個方法主要用于在通路一個未配置設定值的下标元素時，避免oracle彈出NO DATA FOUND 這樣的錯誤

type projectlist is varray(50) of varchar(16);

project_list projectlist := projectlist('網站','ERP','CRM','CMS');

if project_list.EXISTS(5);

dbms_output.put_line('元素存在,其值為:'|| project_list(5));

else

dbms_output.put_line('元素不存在');

輸出是：元素不存在    因為沒有配置設定第五個元素

使用COUNT方法

count方法能夠傳回集合中包含的元素個數，該函數在判斷集合的目前元素個數時非常有用，因為集合的目前大小并不總是能夠确定，特别是對于嵌套表和索引表這類大小不固定的集合。對于變長數組來說，count值與LAST方法值恒等，但對于嵌套表來說，正常情況下count值會和last值相等，但是，當我們從嵌套表中間删除一個元素時，count值就會比last值小。

在計算元素的個數時，count方法會跳過已被删除的元素

type emp_name_table is table of varchar2(20);

type deptno_table is table of number(2);

emp_name_info emp_name_table := emp_name_table（'張小','李斯特'）;

deptno_info:=deptno_table();

dbms_output.put_line('deptno_info的元素個數為:'||deptno_info.COUNT);

dbms_output.put_line('emp_name_info的元素個數為:'|| emp_name_info.COUNT);

deptno_info的元素個數為：5

emp_name_info的元素個數為：2

使用LIMIT方法

用于傳回集合元素的最大個數，對于變長數組來說，因為其元素個數固定，可以傳回變長數組所允許的最大元素個數。而對于嵌套表和索引表來說，由于其元素個數沒有限制，是以調用該方法将總是傳回NULL。在PL/SQL 程式設計過程中，LIMIT 方法一般用在條件表達式中用來比較目前的最大值

project_list projectlist := projectlist('網站','ERP','CRM'，'CMS');

dbms_output.put_line('變長數組的上限值為：'|| project_list.LIMIT);

project_list.EXTEND(8);

dbms_output.put_line（'變長數組的目前個數為：'||project_list.COUNT）;

最大長度為50個元素，構造函數中加入了4個元素，又使用EXTEND擴充了8個元素

輸出：

變長數組的上限值為：50

變長數組的目前個數為：12

FIRST和LAST方法

分别傳回集合第一個和最後一個元素的索引數字。而不是元素的值。如果集合為空則傳回NULL。如果集合僅包含1個元素，那麼将傳回相同的數字。

對于索引表來說，如果是以varchar2類型作為索引表的鍵，那麼将會基于字元串中的字元的二進制值來傳回最高和最低的鍵值，

type projectlist is varray(50) of varchar（16）;

project_list projectlist:=projectlist（'網站','ERP','CRM','CMS'）;

dbms_output.put_line('peoject_list的第一個元素下标:'||project_list.FIRST);

dbms_output.put_line('project_list的最後一個元素的下标:'||project_list.LAST);

代碼中構造了名為projectlist的變長數組，在聲明project_list時，使用構造函數配置設定了4個元素，因為變長數組是從1開始的有序序列，是以FIRST傳回1.因為EXTEND擴充了8個元素是以LAST傳回是12個元素

project_list的第一個元素下标：1

project_list的最後一個元素的下标：12

對于變長數組來說 FIRST恒等于1，LAST恒等于COUNT。但對于嵌套表來說FIRST正常傳回1，如果把第一個元素删了FIRST就會大于1，同樣在中間删除一個LAST也會比COUNT大。

FRIOR和NEXT方法

傳回集合中特定索引值的元素的前一個索引值，NEXT會傳回集合中特定索引值參數所指向的元素的下一個索引值，如果特定的元素沒有前一個或後一個索引值，那麼将傳回NULL。

FRIOR和NEXT 通常用來循環便利所有的元素值，這種周遊方法比通過固定的下标索引更加可靠，因為在循環過程中，有些元素可能被插入或删除。特别是索引表，因為它的下标索引可能是不連續的，有可能是（1，2，4，6，7）或（A.V.G.H.J）這樣的形式

type idx_table is table of varchar(12)

i PLS_INTEGER;

v_emp（1）:='史密斯';

v_emp(20) :='克拉克';

v_emp(40):='史瑞克';

v_emp(-10):='傑瑞';

dbms_output.put_line('第-10個元素的下一個值：'||v_emp(v_emp.NEXT（-10）));

dbms_output.put_line('第40個元素的上一個值:'||v_emp(v_emp.PRIOR(40)));

i:=v_emp.FIRST;

while i is not null

dbms_output.put_line('v_emp('|| i || ')=' || v_emp（i）);

i:=v_emp.NEXT（i）;

第-10個元素的下一個值：史密斯

第40個元素的上一個值：克拉克

v_emp（-10）=傑瑞

v_emp（1）=史密斯

v_emp（20）=克拉克

v_emp（40）=史瑞克

EXTEND方法

為嵌套表和變長數組擴充元素，不能用于索引表。有三種形式

EXTEND：在集合末端添加一個空元素

EXTEND（n）：在集合末端添加n個空元素

EXTEND（n，i）：把第i個元素複制n份，并添加到集合的末端。

如果一個集合未使用構造文法進行初始化，是不能使用EXTEND進行擴充的，如果嵌套表或變長數組添加了NOT NULL限制，也不能使用EXTEND的前面兩種形式。

type courselist is table of varchar2(10);

courses courselist;

courses:=courselist('生物','實體','化學');

courses.delete(3);

courses.EXTEND;

courses（4）:='英語';

courses.EXTEND（5,1）;

i:=courses.FIRST;

while i is not null loop

dbms_output.put_line('courses(' || i || '）=' || courses(i));

i:=courses.NEXT(i)；

courses（1）=生物

courses（2）=實體

courses（3）=英語

courses（4）=生物

courses（5）=生物

courses（6）=生物

courses（7）=生物

courses（8）=生物

courses（9）=生物

假設初始化一個長度為5的嵌套表 然後删除了第2個第5個，這時内部長度是5，COUNT傳回值是3，LAST傳回是4，EXTEND方法會把所有被删除的元素都一樣的對待。

TRIM方法

從嵌套表或變長數組的尾部删除元素，有2種方式

TRIM：從集合末端删除一個元素

TRIM：從集合末端删除n個元素

TRIM也不會忽略被删除的元素

type courseslist is table of varchar2(10);

courses:=courselist('生物'，'實體'，'化學'，'音樂'，'數學','地理');

courses.TRIM(2);

dbms_output.put_line（'目前的元素個數：'||courses.COUNT）;

courses（courses.COUNT）:='國文';

courses.TRIM;

dbms_output.put_line('courses（'|| i || ')=’ || courses（i));

i:=courses.NEXT（i）;

目前的元素個數：4

courses（3）=化學

courses（4）=音樂

DELETE方法

用于從索引表和嵌套表中删除一個或多個元素，有3種方式

DELETE:删除集合中所有元素

DELETE（n）：從以數字做主鍵的關聯數組或者嵌套表中删除第n個元素。如果關聯數組有一個字元串鍵，對應該鍵值的元素就會被删除。如果n為空，DELETE（n）不會做任何事情

DELETE（m，n）：從關聯數組或嵌套表中，把索引範圍m到n的所有元素删除，如果m值大于n或m和n中有一個空，那麼DELETE（m，n）就不做任何事情。

由于變長數組的元素個數是固定的，是以在變長數組上使用DELETE是非法的

如果DELETE方法發現被删除的元素不存在，将隻是簡單地忽略它，并不抛出異常

i PLS_INTEGER;

courses:=courselist（'生物','實體','化學','音樂','數學','地理'）;

courses.DELETE（2）;

dbms_output.put_line（'目前的元素個數：'|| courses.COUNT）;

courses（courses.LAST）:='國文';

courses.DELETE（4，courses.COUNT）;

dbms_output.put_line('courses（'|| i || ')=’ || courses（i）);

目前的元素個數：5

目前的元素個數：6

courses（7）=國文

集合的異常處理

1.COLLECTION_IS_NULL:調用一個空集合的方法

2.NO_DATA_FOUNT：下标索引指向一個被删除的元素或索引表在中不存在的元素

3.SUBSCRIPT_BEYOND_COUNT:下标索引值超過集合中的元素個數

4.SUBSCRIPT_OUTSIDE_LIMIT:下标索引超過允許範圍

5.VALUE_ERROR：下标索引值為空，或不能轉換成正确的鍵類型。當鍵被定義在PLS_INTEGER的範圍内，而下标索引值超過這個範圍時，就可能抛出這個異常。

使用批量綁定

PL/SQL和SQL  頻繁的互動造成性能下降。例如要删除emp表中特定部門編号的記錄。代碼通過循環依次向SQL引擎發送SQL語句。

如果使用批量綁定特性，将一次性向SQL引擎發送所有的sql語句，會顯著提高執行的性能。批量傳遞集合中元素來執行，這個過程稱為批量綁定。

type dept_type is varray（20） of number;

depts dept_type:=dept_type（10，30，70）;

forall i in depts.FIRST .. depts.LAST

DELETE from emp

where deptno=depts（i）;

for i IN 1 .. depts.COUNT LOOP

dbms_output.put_line('部門編号'

||depts（i）

||'的删除操作受影響的行為：'

||SQL%BULK_ROWCOUNT(i)

在操縱sql資料操縱語句時，sql引擎會隐式地打開一個名為SQL的遊标,這個遊标的标量屬性%FOUND，%ISOPEN.%NOTFOUND和%ROWCOUNT,能夠提供最近一次執行的sql資料操作語句資訊。

由于批量綁定是一次性對多個SQL語句進行操作，是以要擷取目前哪個sql語句執行後受影響的行數資訊，可以使用%BULK_ROWCOUNT，該變量接收一個集合元素的索引值，

部門編号10的删除操作受影響的行為：2

部門編号30的删除操作受影響的行為：6

部門編号70的删除操作受影響的行為：0

使用BULK COLLECT

與forall 相反，BULK COLLECT關鍵字則可以批量地從SQL引擎中批量接收資料到一個集合，可以在select-into   fetch-into  returning-into 子句中使用BULK COLLECT

type numtab is table of emp.empno%TYPE;

type nametab is table of emp.ename%TYPE;

nums numtab;

names nametab;

select empno，ename BULK COLLECT into nums，names from emp;

for i in 1 .. nums.COUNT

dbms_output.put_line('num('|| i || ')=' || nums(i) ||'  ');

dbms_output.put_line('names(' || i ||')=' || names(i));

num(1)=7369 names(1)=史密斯

。。。。。。。。。。。

BULK COLLECT一些限制

1.不能對字元串類型作為鍵的索引表使用BULK COLLECT子句

2.隻能在伺服器端的程式中使用BULK COLLECT，如果在用戶端使用，就會産生一個不支援這種特性的錯誤

3.BULK COLLECT INTO 的目标對象必須是集合類型。

      本文轉自潘闊 51CTO部落格，原文連結http://blog.51cto.com/pankuo/1630248：，如需轉載請自行聯系原作者