ORACLE中RECORD、VARRAY、TABLE的使用詳解

1     說明

1.1       RECORD

定義記錄資料型別。它類似於C語言中的結構資料型別(STRUCTURE)，PL/SQL提供了將幾個相關的、分離的、基本資料型別的變數組成一個整體的方法，即RECORD複合資料型別。在使用記錄資料型別變數時，需要在宣告部分先定義記錄的組成、記錄的變數，然後在執行部分引用該記錄變數本身或其中的成員。

    定義記錄資料型別的語法如下：

TYPE RECORD_NAME IS RECORD(

V1  DATA_TYPE1 [NOT NULL][:=DEFAULT_VALUE],

V2  DATA_TYPE2 [NOT NULL][:=DEFAULT_VALUE],

VN  DATA_TYPEN [NOT NULL][:=DEFAULT_VALUE]);

1.2       VARRAY

陣列是具有相同資料型別的一組成員的集合。每個成員都有一個唯一的下標，它取決於成員在陣列中的位置。在PL/SQL中，陣列資料型別是VARRAY（variable array，即可變陣列）。

定義VARRAY資料型別的語法如下：

TYPE VARRAY_NAMEIS VARRAY(SIZE) OF ELEMENT_TYPE [NOT NULL];

其中，varray_name是VARRAY資料型別的名稱，size是正整數，表示可以容納的成員的最大數量，每個成員的資料型別是element_typeo預設時，成員可以取空值，否則需要使用NOT NULL加以限制。

 

1.3       TABLE

定義記錄表（或索引表）資料型別。它與記錄型別相似，但它是對記錄型別的擴充套件。它可以處理多行記錄，類似於C語言中的二維陣列，使得可以在PL/SQL中模仿資料庫中的表。

定義記錄表型別的語法如下：

TYPE TABLE NAME IS TABLE OF ELEMENT_TYPE [NOT NULL]

INDEX BY [BINARY_INTEGER|PLS_INTEGER|VARRAY2];

關鍵字INDEX BY表示建立一個主鍵索引，以便引用記錄表變數中的特定行。

BINARY_INTEGER的說明

如語句：TYPE NUMBERS  IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;其作用是,加了”INDEX BYBINARY_INTEGER ”後，NUMBERS型別的下標就是自增長，NUMBERS型別在插入元素時，不需要初始化，不需要每次EXTEND增加一個空間。

而如果沒有這句話“INDEXBY BINARY_INTEGER”，那就得要顯示對初始化，且每插入一個元素到NUMBERS型別的TABLE中時，都需要先EXTEND。

 

2     舉例

2.1       建立表結構以及資料準備

 

2.2       RECORD的使用舉例

先定義一個只與SF_ORG表中某幾個列的資料型別相同的記錄資料型別TYPE_ORG_RECORD，然後宣告一個該資料型別的記錄變數V_ORG_RECORD，最後用替換變數&ORG_ID接受輸入的僱員編碼，查詢並顯示該僱員的這幾列中的資訊。注意，在使用RECORD資料型別的變數時要用“.”運算子指定記錄變數名限定詞。

    一個記錄型別的變數只能儲存從資料庫中查詢出的一行記錄，如果查詢出了多行記錄，就會出現錯誤。

 

2.3       VARRAY的使用舉例

先定義一個能儲存5個VARCHAR2(25)資料型別的成員的VARRAY資料型別ORG_VARRAY_TYPE，然後宣告一個該資料型別的VARRAY變數V_ORG_VARRAY，最後用與ORG_VARRAY_TYPE資料型別同名的建構函式語法給V_ORG_VARRAY變數賦予初值並顯示賦值結果。

注意，在引用陣列中的成員時．需要在一對括號中使用順序下標，下標從1開始而不是從0開始。

 

2.4       TABLE使用舉例

2.4.1      儲存單列多行

這個和VARRAY類似。但是賦值方式稍微有點不同，不能使用同名的建構函式進行賦值。具體的如下：

 

2.4.2      儲存多列多行和ROWTYPE結合使用

採用bulkcollect可以將查詢結果一次性地載入到collections中。而不是透過cursor一條一條地處理。

 

2.4.3      儲存多列多行和RECORD結合使用

採用bulkcollect可以將查詢結果一次性地載入到collections中。而不是透過cursor一條一條地處理。

 

3     問題

VARRAY和TABLE集合不能直接對其進行查詢。只能對其進行遍歷。

------------------------&gt>record詳解

Oracle系列：記錄（Record）

一，什麼是記錄（Record）？
 由單行多列的標量構成的複合結構。可以看做是一種使用者自定義資料型別。組成類似於多維陣列。
將一個或多個標量封裝成一個物件進行操作。是一種臨時複合物件型別。
 
 記錄可以直接賦值。RECORD1 :=RECORD2；
 記錄不可以整體比較.
 記錄不可以整體判斷為空。
 
二，%ROWTYPE和記錄（Record）？
 請區別%ROWTYPE和記錄（Record）型別。%ROWTYPE可以說是Record的升級簡化版。
 區別在與前者結構為表結構，後者為自定義結構。二者在使用上沒有很大區別。前者方便，後者靈活。在實際中根據情況來具體決定使用。
 Record + PL/SQL表可以進行資料的多行多列儲存。
 
三，如何建立和使用記錄？
 
  ①建立記錄型別
  語法：
  TYPE  記錄名  IS RECORD
　　(
   filed1 type1 [NOT NULL] [：=eXPr1],
　　     ....... ,
　　  filedN typen [NOT NULL] [：=exprn]
  ）
  其中，filed1是標量的名字。
  
  ②宣告記錄型別變數：
   記錄型別變數名 記錄型別
   
  ③填充記錄。
   
  ④訪問記錄成員
   記錄型別變數名.filed1
   .........
   記錄型別變數名.filedN
   
   
  注意：
  表欄位型別修改後，還需要修改記錄欄位型別，有時候可能會忘記，從而出現錯誤。
  對於記錄內每個欄位（filed1.。。。），可以指定也可以使用%TYPE和%ROWTYPE動態指定記錄欄位型別。 

  好處是表欄位發生變化，記錄欄位自動改變。但是，由於每次執行前，遇到%TYPR或%ROWTYPE，
  資料庫系統都會去檢視對應表欄位型別，會造成一定的資料庫開銷，如果系統中大量使用記錄型別，則對效能會有一定影響。
  另外如果刪除了某一欄位，而自定義記錄中使用了該欄位，也會有可能忘記刪除該欄位。
       對資料庫負荷偏低的系統，效能問題一般可以不重點關注，但是對於高負荷資料庫伺服器，
       各個環節都要考慮效能問題，每處節省一點出來，效能整體就有很大提高。
  
  語法：
  TYPE  記錄名  IS RECORD
　　(
   filed1 table.Filed%Type [NOT NULL] [：=eXPr1] ,
   filed2 table.Filed%Type [NOT NULL] [：=eXPr1] ,
　　     ....... ,
　　  filedn table.Filed%Type [NOT NULL] [：=exprn]
  ）;
 
 例子：記錄可以整體賦值
 /*conn scott/tiger
 Create Table  empa  As  Select * From emp;
 */
 Declare
     Type EmpType is Record(
        EMPNO number(4),
        ENAME  varchar2(10),
        JOB varchar2(15),
        SAL number(7,2),
        DEPTNO number(2)
     );
   EmpRec1  EmpType;
   EmpRec2  EmpType;
 Begin
   EmpRec1.Empno:=7369;
   EmpRec1.Ename:='SMITH';
   EmpRec1.Job:='CLERK';
   EmpRec1.Sal:=800;
   EmpRec1.Deptno:=10;
   EmpRec2 := EmpRec1;
   DBMS_output.put_line(EmpRec2.empno);
 End;
 例子：記錄不可以整體比較，只可以比較記錄欄位
 Declare
     Type EmpType is Record(
        EMPNO number(4),
        ENAME  varchar2(10),
        JOB varchar2(15),
        SAL number(7,2),
        DEPTNO number(2)
     );
   EmpRec1  EmpType;
   EmpRec2  EmpType;
 Begin
   EmpRec1.Empno:=7369;
   EmpRec1.Ename:='SMITH';
   EmpRec1.Job:='CLERK';
   EmpRec1.Sal:=800;
   EmpRec1.Deptno:=10;
   if EmpRec1.sal < EmpRec2.sal then
     DBMS_output.put_line('Xiao Xiao Xiao');
   end if;
 End;
 
 例子：記錄不可以整體判斷為空，只可以判斷記錄欄位。
 Declare
     Type EmpType is Record(
        EMPNO number(4),
        ENAME  varchar2(10),
        JOB varchar2(15),
        SAL number(7,2),
        DEPTNO number(2)
     );
   EmpRec  EmpType;
 Begin
   if EmpRec.ename is null then
     DBMS_output.put_line('Kong Kong Kong'); 
   end if;
 End;
 
 例子：使用%TYPE和%ROWTYPE動態指定記錄欄位。
 /*conn scott/tiger
 Create Table  empa  As  Select * From emp;
 */
 DECLARE
  Type MyRecType Is  Record
  (
   RENO  EMPA.EMPNO%Type,
   RENAME   EMPA.ENAME%Type,
   RJOB   EMPA.JOB%Type
  );
  EmpRec  MyRecType;
 Begin
  Select   EMPNO, ENAME, JOB  InTo  EmpRec  From empa Where empa.EMPNO = '7369';
  If  EmpRec.RJOB  = 'CLERK'  Then
   DBMS_OUTPUT.PUT_LINE('Name: '||EmpRec.RENAME);
  End If;
 End;
 
 例子：資料集中的記錄和記錄型別中的資料關係。
 DECLARE
  Type MyRecType Is  Record
  (
   RENO  EMPA.EMPNO%Type,
   RENAME   EMPA.ENAME%Type,
   RJOB   EMPA.JOB%Type
  );
  EmpRec  MyRecType;
  vJob  EMPA.JOB%Type;
 Begin
  Select   EMPNO, ENAME, JOB  InTo  EmpRec  From empa Where empa.EMPNO = '7369';
  DBMS_OUTPUT.PUT_LINE('MyRecType.RJOB: '||EmpRec.RJOB);
  EmpRec.RJOB  := '修改值後'  ;
  DBMS_OUTPUT.PUT_LINE('MyRecType.RJOB: '||EmpRec.RJOB);
  Select JOB  InTo vJob  from empa  Where empa.EMPNO = EmpRec.RENO;
  DBMS_OUTPUT.PUT_LINE('EMPA.JOB: '||vJob);
 End;
 /
 
 
四，使用記錄向表中插入資料？
 根據表結構合理安排記錄欄位。比如主外來鍵。
 如果用記錄(RECORD)插入資料，那麼只能使用記錄成員；
 如果用%ROWTYPE插入資料,可以直接使用%ROWTYPE。
 
 例子：使用記錄成員向表中插入資料
 DECLARE
  Type MyRecType Is  Record
  (
   RENO  EMPA.EMPNO%Type,
   RENAME   VARCHAR2(10),
   RJOB   EMPA.JOB%Type
  );
  EmpRec MyRecType; 
 Begin
  Select   EMPNO, ENAME, JOB  InTo  EmpRec  From empa Where empa.EMPNO = '7369';
  DBMS_OUTPUT.PUT_LINE(EmpRec.RENO||'  '||EmpRec.RENAME||'  '||EmpRec.RJOB);
  
  EmpRec.RENO  := 1001;
  EmpRec.RENAME := '傑克';
  EmpRec.RJOB  := '辦事員';

  Insert InTo  empa(EMPNO,ENAME,JOB)  Values(EmpRec.RENO, EmpRec.RENAME,EmpRec.RJOB);
 
  Select  EMPNO, ENAME, JOB  InTo  EmpRec  From empa Where empa.EMPNO = '1001';
  DBMS_OUTPUT.PUT_LINE(EmpRec.RENO||'  '||EmpRec.RENAME||'  '||EmpRec.RJOB);
 End;
 
五，使用記錄更新資料？
 如果用記錄(RECORD)更新資料，那麼只能使用記錄成員；
 如果用%ROWTYPE更新資料,可以直接使用%ROWTYPE。
 
 例子：使用%ROWTYPE向表中插入資料
 DECLARE
  vEmp empa%RowType;
 Begin
  Select  *  InTo  vEmp  From empa Where empa.EMPNO = '7369';
  UpDate empa Set ROW = vEmp Where EMPNO = 1001;
 End;

六，使用記錄刪除資料？
 刪除記錄時，只能在delete語句的where子句中使用記錄成員。

-------&gt>http://blog.csdn.net/liangweiwei130/article/details/38223319