Oracle資料型別介紹

Oracle的資料型別

Oracle的資料型別分為標量（Scalar）型別、複合（Composite）型別、引用（Reference）型別和LOB（Large Object）型別4種型別。

因為標量型別沒有內部元件，所以，它又分為四類：數值、字元、布林和日期/時間。

複合型別也叫組合型別，它包含了能夠被單獨操作的內部元件，每個元件都可以單獨存放值，所以，一個複合變數可以存放多個值。因為複合變數型別不是資料庫中已經存在的資料型別，所以，複合變數在宣告型別之前，首先要建立複合型別，複合型別建立後可以多次使用，以便定義多個複合變數。複合變數像標量變數一樣也有資料型別，複合資料型別有記錄（RECORD）、表（TABLE）、巢狀表（Nested TABLE）和陣列（VARRAY）四種型別，其中，表、巢狀表和陣列也稱為集合，而集合型別（表、巢狀表和陣列）在使用時必須先使用TYPE進行定義方可使用。記錄是由一組相關但又不同的資料型別組成的邏輯單元。表是資料的集合，可將表中的資料作為一個整體進行引用和處理。巢狀表是表中之表。一個巢狀表是某些行的集合，它在主表中表示為其中的一列。對主表中的每一條記錄，巢狀表可以包含多個行。在某種意義上，它是在一個表中儲存一對多關係的一種方法。可變陣列（VARRAY）儲存固定數量的元素（在執行中，可以改變元素數量），使用順序數字作下標，可以定義等價的SQL型別，可以儲存在資料庫中。可以用SQL進行儲存和檢索，但比巢狀表缺乏靈活性。

引用型別類似於指標，能夠引用一個值。

LOB（Large Object）型別的值就是一個LOB定位器，能夠指示出大物件的儲存位置。目前Oracle支援的LOB型別具體包括四個子型別（Subtype），分別為CLOB、BLOB、NLOB和BFILE。其中，CLOB、BLOB和NLOB都是將資料儲存在資料庫內部，所以稱為內部LOB，而BFILE型別儲存的核心是檔案指標，真正的檔案是儲存在資料庫外，所以稱為外部LOB。

如果處理單行單列的資料那麼可以使用標量變數；如果處理單行多列資料那麼可以使用PL/SQL記錄；如果處理單列多行資料那麼可以使用PL/SQL集合。

BOOLEAN資料型別用於定義布林型（邏輯型）變數，其值只能為TRUE（真）、FALSE（假）或NULL（空）。需要注意的是，該資料型別是PL/SQL資料型別，不能應用於表列。

下圖是在PL/SQL中可以使用的預定義型別。

圖 3-1 PL/SQL中可以使用的預定義型別

資料型別的作用在於指明儲存數值時需要佔據的記憶體空間大小和進行運算的依據。Oracle的欄位資料型別如下表所示：

表 3-1 Oracle的欄位資料型別

資料型別	描述
VARCHAR2(size)	可變長字元資料。VARCHAR2(n)資料型別用於定義可變長度的字串，其中，n用於指定字串的最大長度，n的值必須是正整數且不超過32767。
CHAR(size)	定長字元資料。CHAR(n)資料型別用於定義固定長度的字串，其中，n用於指定字串的最大長度，n的值必須是正整數且不超過32767。
NUMBER(p,s)	可變長數值資料。NUMBER(precision,scale)資料型別用於定義固定長度的整數和浮點數，其中，precision表示精度，用於指定數字的總位數；scale表示標度，用於指定小數點後的數字位數，預設值為0，即沒有小數位數。
DATE	日期型資料。DATE資料型別用於定義日期時間型別的資料，其資料長度為固定7個位元組，分別描述年、月、日、時、分、秒。
LONG	可變長字元資料，最大可達到2G。LONG資料型別在其它的資料庫系統中常被稱為備註型別，它主要用於儲存大量的可以在稍後返回的文字內容。
TIMESTAMP	TIMESTAMP資料型別也用於定義日期時間資料，但與DATE僅顯示日期不同，TIMESTAMP型別資料還可以顯示時間和上下午標記，如“11-9月-2007 11:09:32.213 AM”。
CLOB	字元資料，最大可達到4G。
RAW和LONG RAW	裸二進位制資料。LONG RAW資料型別在其它資料庫系統中常被稱為大二進位制型別（BLOB），它可以用來儲存圖形、聲音視訊資料，儘管關係型資料庫管理系統最初不是為它們而設計的，但是多媒體資料可以儲存在BLOB或LONG RAW型別的欄位內。
BLOB	二進位制資料，最大可達到4G。
BFILE	儲存外部檔案的二進位制資料，最大可達到4G。
ROWID	行地址，十六進位制串，表示行在所在的表中唯一的行地址，該資料型別主要用於返回ROWID偽列，常用在可以將表中的每一條記錄都加以唯一標識的場合。

真題1、PL/SQL中的%ROWTYPE和%TYPE的區別是什麼？

答案：%TYPE是定義一個變數，其資料型別與已經定義的某個資料變數的型別相同，或者與資料庫表的某個列的資料型別相同，其使用示例如下所示：

DECLARE

V_SAL NUMBER(7) ;

V_ENAME EMP.ENAME%TYPE;

V_HIREDATE EMP.HIREDATE%TYPE;

BEGIN

SELECT SAL,ENAME,HIREDATE INTO V_SAL,V_ENAME,V_HIREDATE FROM EMP WHERE EMPNO = 7369;

INSERT INTO EMP(EMPNO,ENAME) VALUES(1111,'LHR');

UPDATE EMP SET SAL = 2000 WHERE EMPNO= 1010;

DELETE EMP WHERE EMPNO = 1012;

COMMIT;

DBMS_OUTPUT.PUT_LINE(V_SAL || ',' || V_ENAME || ',' || V_HIREDATE);

END;

關於%ROWTYPE，需要了解以下內容：

①　%ROWTYPE返回的是一個記錄型別，其資料型別和資料庫表的資料結構一致。

②　宣告的變數對應於資料庫表或檢視中列的集合。

③　在%ROWTYPE之前加上資料庫表名。

④　記錄內欄位名和資料型別與參照表或檢視中的列相同。

具體而言，%ROWTYPE有如下優點：

①　可以不必知道資料庫中列的數量和型別。

②　在執行期間，資料庫中列的數量和型別可能發生變化，但是卻不用修改程式碼。

③　在SELECT語句中使用該屬性可以有效地檢索表中的行。

%ROWTYPE使用示例如下所示：

DECLARE

V_EMP EMP%ROWTYPE;

BEGIN

SELECT * INTO V_EMP FROM EMP WHERE EMPNO = 7521;

DBMS_OUTPUT.PUT_LINE(V_EMP.EMPNO || ',' || V_EMP.ENAME);

END;

1.2 Oracle資料型別
1.2.1 預定義資料型別
Oracle的資料型別分為標量（Scalar）型別、複合（Composite）型別、引用（Reference）型別和LOB（Large Object）型別4種型別。標量型別沒有內部元件又分為四類：數字、字元、布林和日期/時間；而複合型別包含了能夠被單獨操作的內部元件；引用型別類似於3G語言中的指標，能夠引用一個值；LOB型別的值就是一個lob定位器，能夠指示出大物件的儲存位置。
下圖是在PL/SQL中可以使用的預定義型別。

1.3 標量型別
1.3.1 數字型
數字型別可以儲存整數、實數和浮點數，可以表示數值的大小，參與計算。
1.3.1.1 BINARY_INTEGER
我們可以使用BINARY_INTEGER資料型別來儲存有符號整數。它的範圍是-2**31至2**31。跟PLS_INTEGER一樣，BINARY_INTEGER所需的儲存空間也要小於NUMBER。但是,大多數的BINARY_INTEGER操作要比PLS_INTEGER操作慢。
一、 BINARY_INTEGER子型別
所謂的基型別，就是有子型別繼承於它。子型別在基型別的基礎上新增一些約束限制，也可能重新定義數值範圍。為了使用方便，PL/SQL預定義了下面幾個BINARY_INTEGER的子類。
NATURAL
NATURALN
POSITIVE
POSITIVEN
SIGNTYPE
子型別NATURAL和POSITIVE能讓我們將一個整數變數的取值範圍分別限制在非負數和正整數之內。NATURALN和POSITIVEN不允許為整數型別變數賦空值。SIGNTYPE把整數的取值範圍限定在-1，0，1，這在程式設計中很適合表示三態邏輯(tri-state logic)。
1.3.1.2 NUMBER
我們可以使用NUMBER資料型別來儲存定點或浮點數。它的範圍是1E-130至10E125。如果表示式的值超過這個範圍，我們就會得到數字溢位錯誤(a numeric overflow or underflow error)。我們可以為要儲存的數字指定精度，包括數字的全長和小數長度。語法如下：
NUMBER[(precision,scale)]
其中precision表示數字的總長度，scale代表可以有幾位小數。如果要使用浮點數的話，就不能指定長度和精度，像下面這樣宣告就可以了：
NUMBER
宣告整數直接使用下面的語法：
NUMBER(precision) -- same as NUMBER(precision,0)
不可以用常量或變數指定NUMBER的長度和精度。NUMBER型別最大的長度是38位。如果不指定NUMBER型別的最大長度，就會預設採用這個長度或是使用系統所支援的最大長度。
scale的範圍從-84到127，能夠決定舍入規則。例如，一個scale值為2的數字，舍入後的小數部分將是最接原小數部分的百分位數(3.456舍入為3.46)。如果scale是負數，它就會從小數點左邊開始進行舍入操作。如scale值為-3的數字舍入後的結果將是最接近原值的千位數(3456舍入為3000)。scale為零的數字舍入後的結果還是本身。如果我們不指定scale的值，預設就為0。
一、 NUMBER子型別
為了能和ANSI/ISO和IBM型別相容或是想使用一個更加有描述性意義的名字，我們就可以使用下面的NUMBER子型別。
DEC
DECIMAL
DOUBLE PRECISION
FLOAT
INTEGER
INT
NUMERIC
REAL
SMALLINT
使用DEC、DECIMAL和NUMBERIC可以宣告最大精度為38位十進位制數字的定點數字。而使用DOUBLE PRECISION和FLOAT可以宣告最大精度為126位二進位制數字的浮點數字，大約相當於38位十進位制數字。或是使用REAL宣告最大精度為63位二進位制數字的浮點數字，大約相當於18位十進位制數字。INTEGER、INT和SMALLINT可以宣告最大精度為38位十進位制數字的整數。
1.3.1.3 PLS_INTEGER
我們可以使用PLS_INTEGER資料型別來儲存有符號整數。它的取值範圍在-2**31至2**31之間。PLS_INTEGER所需的儲存空間要比NUMBER少，運算的速度要高於NUMBER和BINARY_INTEGER。雖然PLS_INTEGER和BINARY_INTEGER的取值範圍一樣，但它們不完全相容。PLS_INTEGER在運算時如果有溢位，則會有異常丟擲，而BIANRY_INTEGER發生溢位時，如果結果是要賦給一個NUMBER型別的變數時，就不會有異常丟擲。為了考慮相容性，我們仍可以在舊的應用程式中使用BINARY_INTEGER；但在新的應用程式中，PLS_INTEGER會帶來更好的效能。
1.3.2 字元型
字元型別可以存放字元和數字混合的資料，表現詞和文章，操作字串。
1.3.2.1 CHAR
我們可以使用CHAR型別來儲存定長的字元資料。但該資料的內部表現形式是取決於資料庫字符集的。CHAR型別有一個用於指定最大長度的可選引數，長度範圍在1到32767位元組之間。我們可以採用位元組或字元的形式來設定該引數。語法如下：
CHAR[(maximum_size [CHAR | BYTE] )]
maximum_size不能是常量或變數，只能是範圍在1到32767之間的整數文字。
如果我們不指定最大值，它預設是1。如果我們用位元組形式指定最大值，有時就會出現空間不足的問題(多位元組字元會佔用多於一個位元組的空間)。為了避免這樣的問題發生，我們可以採用按照字元的方式指定其最大值，這樣，即使是那些包含多個位元組的引數可以被靈活地儲存下來。按照字元指定長度的方式，上限大小仍舊是32767位元組，所以，對於雙位元組和多位元組的字符集，我們可以使用位元組最大長度的一半或三分之一作為最大字元個數。
雖然PL/SQL字元變數的長度相對來說比較長，但CHAR型別在資料庫的欄位中最大儲存長度為2000個位元組，所以，我們不能往資料庫CHAR型別欄位中插入超過2000個位元組的字元。但是，我們可以把任意CHAR(n)插入LONG型別的欄位中，因為LONG的最大長度是2**31位元組或是2G(gigabyte)。如果我們不使用CHAR或BYTE來對字元型別長度進行限制的話，初始化引數NLS_LENGTH_SEMANTICS會決定預設長度大小的。CHAR的子型別CHARACTER和CHAR有著相同的取值範圍。也就是說，CHARACTER只是CHAR的一個別名而已。這個子型別能與ANSI/ISO和IBM型別相相容。
1.3.2.2 LONG和LONG RAW
我們可以使用LONG型別來儲存變長的字串。除了LONG型別的最大長度是32760位元組之外，LONG型別和VARCHAR2很相像。我們還可以使用LONG RAW型別來儲存二進位制資料或二進位制字串。LONG RAW和LONG類似，但是它不會被PL/SQL解析。LONG RAW的最大長度也是32760位元組。從9i開始，LOB型別變數可以與LONG和LONG RAW型別交換使用。Oracle推薦將LONG和LONG RAW都對應的轉換成COLB和BLOB型別。
我們可以將LONG型別的值插入欄位型別為LONG的資料庫中，因為在資料庫中LONG的長度是2**31位元組；但是，不可以從LONG欄位中檢索超過32760位元組的字元放到LONG型別變數中去。同樣，對於LONG RAW型別來說，這條規則同樣適用，在資料庫中它的最大長度也是2**31位元組，而變數的長度在32760位元組以內。LONG型別欄位可以儲存文字、字元陣列或短文等。我們可以對LONG欄位進行UPDATE、INSERT和SELECT操作，但不能在表示式、SQL函式呼叫、或某個SQL子句(如：WHERE、GROUP BY和CONNECT BY)中使用它。
注意：在SQL語句中，PL/SQL會將LONG型別的值繫結成VARCHAR2型別，而不是LONG。但是，如果被繫結的VARCHAR2值超過4000個位元組，Oracle會自動地將繫結型別轉成LONG，但LONG並不能應用在SQL函式中，所以，這時我們就會得到一個錯誤訊息。

一、 oracle裡long型別的總結
LONG 資料型別中儲存的是可變長字串，最大長度限制是2GB。
2、對於超出一定長度的文字，基本只能用LONG型別來儲存，資料字典中很多物件的定義就是用LONG來儲存的。
1、LONG 資料型別中儲存的是可變長字串，最大長度限制是2GB。
2、對於超出一定長度的文字，基本只能用LONG型別來儲存，資料字典中很多物件的定義就是用LONG來儲存的。
3、LONG型別主要用於不需要作字串搜尋的長串資料，如果要進行字元搜尋就要用varchar2型別。
4、很多工具，包括SQL*Plus，處理LONG 資料型別都是很困難的。
5、LONG 資料型別的使用中，要受限於磁碟的大小。

能夠操作 LONG 的 SQL 語句：
1、Select語句
2、Update語句中的SET語句
3、Insert語句中的VALUES語句

限制：

1、一個表中只能包含一個 LONG 型別的列。
2、不能索引LONG型別列。
3、不能將含有LONG型別列的表作聚簇。
4、不能在SQL*Plus中將LONG型別列的數值插入到另一個表格中,如insert into ...select。
5、不能在SQL*Plus中通過查詢其他表的方式來建立LONG型別列,如create table as select。
6、不能對LONG型別列加約束條件（NULL、NOT NULL、DEFAULT除外），如：關鍵字列(PRIMARY KEY)不能是 LONG 資料型別。
7、LONG型別列不能用在Select的以下子句中：where、group by、order by，以及帶有distinct的select語句中。
8、LONG型別列不能用於分佈查詢。
9、PL/SQL過程塊的變數不能定義為LONG型別。
10、LONG型別列不能被SQL函式所改變，如：substr、instr。

因為long型別的數值不能用insert into ... select的方法插入,故我們要分兩步走,先插入其它欄位,最後再插入long型別欄位,這可以通過過程來實現.下面是一個我做實驗實現的例子.

SQL>; create table testlong (id number,name varchar2(12),history long);

表已建立。

SQL>; create table testlong1 (id number,name varchar2(12),history long);

表已建立。

SQL>; insert into testlong values(1,'dwh','work in foshan');

已建立 1 行。
SQL>; insert into testlong values(2,'sfg','work in guangzhou');

已建立 1 行。

SQL>; select * from testlong;

ID NAME HISTORY
---------- ------------- -------------------------------------------------------
1 dwh work in foshan
2 sfg work in guangzhou

SQL>; insert into testlong1 select * from testlong;
insert into testlong1 select * from testlong
*
ERROR 位於第 1 行:
ORA-00997: 非法使用 LONG 資料型別

SQL>; Declare
2 CURSOR bcur
3 IS SELECT id,history from testlong;
4 brec bcur%ROWTYPE;
5 BEGIN
6 insert into testlong1(id,name) select id,name from testlong;--其它型別
先插入
7 OPEN bcur;
8 LOOP
9 FETCH bcur INTO brec;
10 EXIT WHEN bcur%NOTFOUND;
11 update testlong1 set history=brec.history where id=brec.id;
12 END LOOP;
13 CLOSE bcur;
14 END;
15 /

PL/SQL 過程已成功完成。

SQL>; select * from testlong1;

ID NAME HISTORY
---------- -------------- ----------------------------------------------

1 dwh work in foshan
2 sfg work in guangzhou
create table testlong (id number,name varchar2(12),history long);

create table testlong1 (id number,name varchar2(12),history long);

insert into testlong values(1,'dwh','work in foshan');
insert into testlong values(2,'sfg','work in guangzhou');

insert into testlong1 select * from testlong; ---報錯
insert into testlong1 select t.id ,t.name,to_lob(t.history) from testlong t;

二、 LONG轉換為字串
①　只能利用基礎表來轉換，或者如果是檢視可以建立臨時表來轉換
②　利用to_lob函式來轉換成lob，然後再轉換為to_char

long列是不允許出現在 create table xx as select * from yyy
裡的
In addition, LONG columns cannot appear in these parts of SQL statements:
....
SELECT lists of
CREATE TABLE ... AS SELECT statements

.....
oracle建議使用clob\blob\nclob來代替。
如:
create table t(a int,b long); insert into t values(1,'1');
--可以這樣建立，不過預設型別就成了clob
create table t1 as select a,to_lob(b) from t;

--也可以這樣,先建立一個表，然後在插入資料時使用to_lob函式轉換一下
create table t2 (a int,b long); insert into t2 select a,to_lob(b) from t;

1、一次“ORA-00997: 非法使用 LONG 資料型別”的錯誤解決
問題提出：
當前使用者下有一系列LIST型別的分割槽表，希望找出其中包含有DEFAULT分割槽的表以及對應的分割槽來。
查詢檢視 USER_TAB_PARTITIONS，此檢視對應每個分割槽有一條記錄，包含的主要欄位有
I、 TABLE_NAME(表名)
II、 PARTITION_NAME(分割槽名)
III、 HIGH_VALUE(最大值（對應LIST分割槽的分割槽值）)
IV、 TABLESPACE_NAME(表空間)
因此，要知道包含有DEFAULT分割槽表的分割槽，只要通過下面的語句就可以了。
select * from user_tab_partitions WHERE high_value='DEFAULT'
事實上，由於user_tab_partitions.high_value是LONG型別的資料，因而無法直接象VARCHAR2型別資料那樣直接進行等值比對得到查詢結果。所以上面的語句執行時會出現錯誤：

ORA-00997: 非法使用 LONG 資料型別

解決方法：
1. 步驟一
通過網上查詢，得到這樣一個函式，sys.dbms_metadata_util.long2varchar，這個sys.dbms_metadata_util包是不在Oracle文件中給出解釋的。利用DESC，得到其函式的引數：

FUNCTION LONG2VARCHAR RETURNS VARCHAR2
引數名稱型別輸入/輸出預設值?
LENGTH NUMBER IN
TAB VARCHAR2 IN
COL VARCHAR2 IN
ROW ROWID IN
這樣我們可以通過這個函式將LONG型別的資料轉換為VARCHAR2型別的資料，從而得到最終的結果。
執行查詢
SELECT *
FROM user_tab_partitions
WHERE UPPER(sys.dbms_metadata_util.long2varchar(1000, 'USER_TAB_PARTITIONS', 'HIGH_VALUE', ROWID)) = 'DEFAULT'
返回錯誤資訊：
第 1 行出現錯誤:
ORA-01446: 無法使用 DISTINCT, GROUP BY 等子句從檢視中選擇 ROWID 或取樣。
由於USER_TAB_PARTITIONS不是基本表，因此不存在ROWID，看來sys.dbms_metadata_util.long2varchar2只對基本表有效。
2. 步驟2
下面我們就通過USER_TAB_PARTITIONS找到其基本表。
連線到sys使用者下，執行：

SELECT owner, object_name, object_type
FROM DBA_OBJECTS
WHERE OBJECT_NAME='USER_TAB_PARTITIONS'

得到結果如下：

OWNER OBJECT_NAME OBJECT_TYPE
SYS USER_TAB_PARTITIONS VIEW
PUBLIC USER_TAB_PARTITIONS SYNONYM

這裡發現USER_TAB_PARTITIONS是一個檢視，取得這個檢視的指令碼。

SELECT DBMS_METADATA.GET_DDL(‘VIEW’,’ USER_TAB_PARTITIONS’)
FROM DUAL;

完整的查詢結果這裡不再給出，通過查詢可以瞭解到USER_TAB_PARTITIONS的HIGH_VALUE對應tabpartv$. Hiboundval，tabpartv$表其他的關鍵欄位還有

Obj# 分割槽物件的物件號，對應dba_objects.object_id
Dataobj# 分割槽物件的資料物件號，對應dba_objects.data_object_id
Bo# 分割槽表對應的物件號，對應dba_objects.object_id(記錄對應為表)
Part# 分割槽表號，對應user_tab_partitions. partition_position

這樣，我們只要執行下面的查詢就可以了，
SELECT *
FROM tabpartv$
WHERE sys.dbms_metadata_util.long2varchar(10, 'SYS.TABPARTV$', 'HIBOUNDVAL', ROWID) = 'DEFAULT'

根據得到的結果，我們利用user_objects查詢對應的obj#，然後再查詢USER_TAB_PARTITIONS就可以了。要指定特定的使用者，比如SCOTT使用者下的分割槽資料，還要關聯obj$和user$基礎表。

下面用一個SQL得到我們想要的結果，
SELECT a.* FROM dba_tab_partitions a JOIN dba_objects b
ON(a.table_owner=b.owner AND
a.table_name=b.object_name and
a.partition_name=b.subobject_name)
WHERE b.object_id IN(
SELECT a.obj#
FROM tabpartv$ a join obj$ b ON(a.obj#=b.obj#)
JOIN user$ c ON(b.owner#=c.user#)
WHERE UPPER(sys.dbms_metadata_util.long2varchar(10, 'SYS.TABPARTV$', 'HIBOUNDVAL', A.ROWID)) = 'DEFAULT'
AND c.name=’SCOTT’)
SELECT a.table_owner,
a.table_name,
a.partition_name,
a.partition_position,
sys.dbms_metadata_util.long2varchar(10,
'SYS.TABPARTV$',
'HIBOUNDVAL',
c.ROWID) high_value
FROM dba_tab_partitions a,
dba_objects b,
SYS.tabpartv$ c
WHERE a.table_owner = b.owner
AND a.table_name = b.object_name
AND a.partition_name = b.subobject_name
AND b.OBJECT_ID = c.obj#
and a.table_owner not in ('SYS','SYSTEM')
ORDER BY a.table_owner,
a.table_name,
a.partition_position;

SQL> alter table plan_table move;
alter table plan_table move
ORA-00997: 非法使用 LONG 資料型別
SQL> desc plan_table
Name Type Nullable Default Comments
----------------- -------------- -------- ------- --------
STATEMENT_ID VARCHAR2(30) Y
PLAN_ID NUMBER Y
.............
PARTITION_STOP VARCHAR2(255) Y
PARTITION_ID INTEGER Y
OTHER LONG Y
DISTRIBUTION VARCHAR2(30) Y
.........
QBLOCK_NAME VARCHAR2(30) Y
檢視錶結構，表裡含有型別為 long 的欄位，是不能move
那我們來把long改為clob
SQL> alter table plan_table modify(other clob);
Table altered
SQL> alter table plan_table move;
Table altered
成功~
QL> alter table plan_table modify(other long);
alter table plan_table modify(other long)
ORA-22859: 無效的列修改
由此可見想要再把欄位型別改回 long 是不能的
另外 plan_table move 以後會多2個物件出來
SQL> /
SEGMENT_NAME FILE_ID EXTENT_ID BLOCK_ID BYTES
---------------------- ---------- ---------- ---------- ----------
IND_T1_N1_V1 11 0 1081 65536
FY_2004_2 11 3 1033 65536
...............
FY_2004_2 14 5 35489 65536
FY_2006 15 0 38313 65536
SYS_LOB0000030377C00026$$ 15 0 1161 65536
SYS_IL0000030377C00026$$ 15 0 1169 65536
FY_2006_IND 15 0 38369 65536
SYS_C003157 15 0 38321 65536
IND_T1_N1 15 0 38377 65536
EMP_TEST 15 0 38361 65536
PLAN_TABLE 15 0 1201 65536
CHAINED_ROWS 15 0 1297 65536
..........
FY_2004_2 18 7 1169 65536
已選擇31行。
其中SYS_LOB0000030377C00026$$ 是clob型別欄位的內容，SYS_IL0000030377C00026$$ 是索引

1.3.2.3 RAW
我們可以使用RAW資料型別來儲存二進位制資料或位元組串。例如，一個RAW型別的變數可以儲存一個數字化圖形。RAW型別資料和VARCHAR2型別資料類似，只是PL/SQL不對其進行解析而已。同樣，在我們把RAW資料從一個系統傳到另一個系統時，Oracle Net也不會對它做字符集轉換。RAW型別包含了一個可以讓我們指定最大長度的可選引數，上限為32767位元組，語法如下：
RAW(maximum_size)
我們不能使用常量或變數來指定這個引數；而且引數的範圍必須是在1到32767範圍內。在資料庫中RAW型別欄位的最大長度是2000個位元組，所以，不可以把超過2000位元組的內容放到RAW型別欄位中。我們可以把任何RAW型別插入到LONG RAW型別的資料庫欄位中，因為LONG RAW在資料庫中是2**31，但是不能把超過32767位元組的LONG RAW型別放入RAW變數中。
1.3.2.4 ROWID和UROWID
在Oracle內部，每個資料表都有一個偽列ROWID，用於存放被稱為ROWID的二進位制值。每個ROWID代表了一行資料的儲存地址。物理ROWID能夠標識普通資料表中的一行資訊，而邏輯ROWID能夠標識索引組織表(index-organized table)中的一行資訊。其中ROWID型別只能儲存物理內容，而UROWID(universal rowid)型別可以儲存物理，邏輯或外來(non-Oracle)ROWID。
建議：只有在舊的應用程式中，為了相容性我們才使用ROWID資料型別。對於新的應用程式，應該使用UROWID資料型別。
當我們把查詢出來的ROWID放到ROWID變數時，可以使用內建函式ROWIDTOCHAR，這個函式能把二進位制內容轉換成18個位元組的字串；還有一個與之對應的CHARTOROWID函式，可以對該過程進行反操作，如果轉換過程中發現字串並不是一個有效的ROWID時，PL/SQL就會丟擲預定義異常SYS_INVALID_ROWID。UROWID變數和字串之間進行轉換也可以直接使用賦值操作符。這時，系統會隱式地實現UROWID和字元型別之間的轉換。
物理ROWID(Physical Rowid)可以讓我們快速的訪問某些特定的行。只要行存在，它的物理ROWID就不會改變。高效穩定的物理ROWID在查詢行集合、操作整個集合和更新子集是很有用的。例如，我們可以在UPDATE或DELETE語句的WHERE子句中比較UROWID變數和ROWID偽列來找出最近一次從遊標中取出的行資料。
物理ROWID有兩種形式。10位元組擴充套件ROWID格式(10-byte extended rowid format)支援相對錶空間塊地址並能辨識分割槽表和非分割槽表中的行記錄。6位元組限定ROWID格式支援向後相容。
擴充套件ROWID使用檢索出來的每一行記錄的實體地址的base-64編碼。例如，在SQL*Plus(隱式地將ROWID轉換成字串)中的查詢：
SQL> SELECT ROWID, ename
SQL> FROM emp
SQL> WHERE empno = 7788;
ROWID ENAME
------------------ ----------
AAAAqcAABAAADFNAAH SCOTT

OOOOOOFFFBBBBBBRRR這樣的形式有四部分組成:
1 "000000"代表資料物件號(data object number)，如上例中的"AAAAqc"，能夠辨識資料庫段。同一段中的模式物件，都有著相同的資料物件號。
2 "FFF"代表檔案號(file number)，如上例中的"AAB"，能辨識出包含行的資料檔案。在資料庫中，檔案號是唯一的。
3 "BBBBBB"代表塊號(block number)，如上例中的"AAADFN"，能辨識出包含行的資料塊。塊號是與它們所在的資料檔案相關，而不是表空間。所以，兩個在同一表空間的行資料，如果它們處於不同的資料檔案中，也可能有著相同的塊號。
4 "RRR"代表了行號(row number)，如上例中的"AAH"，可以辨識塊中的行資料。
邏輯ROWID為訪問特定行提供了最快的訪問速度。Oracle在索引組織表基礎上使用它們構建二級索引。邏輯ROWID沒有持久的實體地址，當新資料被插入時，它的值就會在資料塊上偏移。但是，如果一個行資料的物理位置發生變化，它的邏輯ROWID就無效了。
1.3.2.5 VARCHAR2
我們可以使用VARCHAR2型別來儲存變長的字元資料。至於資料在資料庫中的內部表現形式要取決於資料庫的字符集。VARCHAR2型別需要指明資料長度，這個引數的上限是32767位元組。語法如下：
VARCHAR2(maximum_size [CHAR | BYTE])
我們不能使用常量或變數來指定maximum_size值，maximum_size值的有效範圍在1到32767之間。
對於長度不同的VARCHAR2型別資料，PL/SQL對它們的處理方式也是不同的。值小的PL/SQL會優先考慮到它的效能問題，而對於值大的PL/SQL會優先考慮到記憶體的使用效率問題。截止點(cutoff point)為2000個位元組。在2000位元組以下，PL/SQL會一次性分配我們宣告時所指定大小的空間容納實際的值；2000位元組或2000位元組以上時，PL/SQL會動態的分配VARCHAR2的儲存容量。比如我們宣告兩個VARCHAR2型別的變數，一個長度是1999位元組，一個是2000位元組，如果我們把長度為500位元組的值分別分配給這兩個變數，那麼前者佔用的空間就是1999位元組而後者只需500位元組。
如果我們採用位元組形式而非字元形式指定最大值時，VARCHAR2(n)變數就有可能太小而不能容納n個多位元組字元。為了避免這個問題，就要使用VARCHAR2(n CHAR)形式進行定義，這樣，即使字元中包含多位元組字元也不會出問題。所以，對於雙位元組或多位元組字符集，我們可以指定單位元組字符集中字元個數的1/2或1/3。
雖然PL/SQL字元變數相對比較長，但VARCHAR2型別的資料庫欄位最大長度為4000個位元組。所以，不能把位元組超過4000的VARCHAR2型別值插入VARCHAR2型別的資料庫欄位中。
我們可以把任何VARCHAR2(n)值插入一個LONG型別的資料庫欄位，因為LONG欄位最大長度為2**31位元組。但是，不能把LONG欄位中檢索出來的長度超過32767位元組的值放到VARCHAR2(n)變數中。
如果宣告時不使用CHAR或BYTE限定修飾詞，初始化引數NLS_LENGTH_SEMANTICS會決定預設的設定。當PL/SQL過程編譯時，這個引數的設定就會被記錄下來，這樣，當過程失效後被重新編譯時就會被重新使用。
一、 VARCHAR2的子型別
下面VARCHAR2的子型別的範圍與VARCHAR2完全相同，它們只是VARCHAR2的一個別名而已。
STRING
VARCHAR
我們可以使用這些子型別來與ANSI/ISO和IBM型別相容。注意：目前，VARCHAR和VARCHAR2有著相同意義，但是在以後的PL/SQL版本中，為了符合SQL標準，VARCHAR有可能會作為一個單獨的型別出現。所以最好使用VARCHAR2，而不是VARCHAR。
3、本地字元型
被廣泛使用的單位元組ASCII和EBCDIC字符集很適合表現羅馬字元，但有些亞洲語言，如漢語、日語等包含了成千上萬個字元，這些語言中的一個字元就需要用兩個或三個位元組來表示。為了處理這些語言，Oracle提供了全球化支援，允許我們處理單位元組和多位元組字元資料，並在字符集之間進行資料轉換。Oracle還能讓我們的應用程式執行在不同的語言環境中。
有了全球化支援，數字和日期格式會根據使用者會話中所指定的語言約定(language convention)而自動進行調節。因此，全世界的使用者可以使用他們母語來使用Oracle。
Oracle支援兩種資料庫字符集和一種國家特有字符集，前者用於識別符號和原始碼，後者用於國家特有語言資料。NCHAR和NVARCHAR2型別用於儲存本地字符集。
注意，當在資料庫的不同字符集之間轉換CHAR或VARCHAR2資料時，要確保資料保持良好的形式(well-formed)。
? 比較UTF8和AL16UTF16編碼
國家特有字符集使用Unicode來表現資料，採用UTF8或AL16UTF16編碼。
每個使用AL16UTF16編碼的字元都佔用2個位元組。這將簡化字串的長度計算，避免採用混合語言程式設計時發生截斷錯誤，但是這會比ASCII字元所組成的字串需要更多空間。
每個使用UTF8編碼的字元佔用1、2或3個位元組。這就能讓我們把更多的字元放到變數或資料表的欄位中，但這只是在大多數字符用單位元組形式表現的條件下才能做到。這種編碼在傳遞資料到位元組緩衝器時可能會引起截斷錯誤。
Oracle公司推薦使用預設的AL16UTF16編碼，這樣可以獲取最大的執行時可靠性。如果想知道一個Unicode字串佔用多少位元組，就要使用LENGTHB函式，而不是LENGTH。
? NCHAR
我們用NCHAR型別來儲存定長國家特有字元資料。資料的內部表現取決於資料庫建立時指定的國家特有字符集，字符集可能採用變長編碼(UTF8)或定長編碼(AL16UTF16)。因為這種型別總是與多位元組字元相容，所以我們可以使用它支援任何Unicode字元資料。
NCHAR資料型別可接受一個可選引數來讓我們指定字元的最大長度。語法如下：
NCHAR[(maximum_size)]
因為物理限制是32767個位元組，所以在AL16UTF16編碼格式下最大長度為32767/2，UTF8編碼格式下是32767/3。
我們不能使用常量或變數來指定最大值，只能使用整數文字。
如果我們沒有指定最大長度，它預設值就為1。這個值總是代表字元的個數，不像CHAR型別，既可以採用字元形式又可以採用位元組形式。
my_string NCHAR(100); -- maximum size is 100 characters
NCHAR在資料庫欄位中的最大寬度是2000位元組。所以，我們不能向NCHAR欄位中插入值超過2000位元組的內容。
如果NCHAR的值比NCHAR欄位定義的寬度要小，Oracle就會自動補上空格，填滿定義的寬度。
我們可以在語句和表示式中互動使用CHAR和NCHAR值。從CHAR轉到NCHAR總是安全的，但在NCHAR值轉換到CHAR的過程中，如果CHAR型別不能完全表現NCHAR型別的值，就會引起資料丟失。這樣的資料丟失會導致字元看起來像問號(?)。
? NVARCHAR2
我們可以使用NVARCHAR2資料型別來儲存變長的Unicode字元資料。資料的內部表現取決於資料庫建立時所指定的國家特有字符集，它有可能採用變長編碼(UTF8)或是定長編碼(AL16UTF16)。因為這個型別總與多位元組相容，我們可以用它來支援Unicode字元資料。
NVARCHAR2資料型別需要接受一個指定最大大小的引數。語法如下：
NVARCHAR2(maximum_size)
因為物理限制是32767個位元組，所以在AL16UTF16編碼格式下最大長度為32767/2，UTF8編碼格式下是32767/3。
我們不能使用常量或變數來指定最大值，只能使用整數文字。
最大值總是代表字元的個數，不像CHAR型別，既可以採用字元形式又可以採用位元組形式。
my_string NVARCHAR2(200); -- maximum size is 200 characters
NVARCHAR2在資料庫欄位中的最大寬度是4000位元組。所以，我們不能向NVARCHAR2欄位中插入長度超過4000位元組的值。
我們可以在語句和表示式中互動使用VARCHAR2和NVARCHAR2值。從VARCHAR2向NVARCHAR2轉換總是安全的，但在NVARCHAR2值轉換到VARCHAR2的過程中，如果VARCHAR2型別不能完全表現NVARCHAR2型別的值，就會引起資料丟失。這樣的資料丟失會導致字元看起來像問號(?)。

二、 Oracle資料庫中char()，varchar2()，nvarchar2()三種資料型別的區別
1. char()型別:
（1）如果在資料庫中定義的長度為10位，而我實際入力的資料長度不足10位，系統會在入力資料的後面用空字串補足10位。
（2）一個全形認作2位長度。
2. varchar2()型別：
（1）不足資料庫規定長度，不會補足長度。
（2）一個全形認作2位長度。
3. nvarchar2()型別：
（1）不足資料庫規定長度，不會補足長度。
（2）一個全形認作1位長度。
結論：由此在做設計時，為節省系統資源會盡量選用varchar2()和nvarchar2()型別。
三、 Oracle CHAR，VARCHAR，VARCHAR2,nvarchar型別的區別與使用
一 varchar,nvarchar,
四個型別都屬於變長字元型別， varchar和varchar2的區別在與後者把所有字元都佔兩位元組，前者只對漢字和全形等字元佔兩位元組。 nvarchar和nvarchar2的區別和上面一樣，與上面區別在於是根據Unicode標準所進行的定義的型別，通常用於支援多國語言類似系統的定義。
1.char
char的長度是固定的，比如說，你定義了char(20),即使你你插入abc，不足二十個位元組，資料庫也會在abc後面自動加上17個空格，以補足二十個位元組；
char是區分中英文的，中文在char中佔兩個位元組，而英文佔一個，所以char(20)你只能存20個字母或10個漢字。
char適用於長度比較固定的，一般不含中文的情況
2.varchar/varchar2
varchar是長度不固定的，比如說，你定義了varchar(20),當你插入abc，則在資料庫中只佔3個位元組。
varchar同樣區分中英文，這點同char。
varchar2基本上等同於varchar，它是oracle自己定義的一個非工業標準varchar，不同在於，varchar2用null代替varchar的空字串
varchar/varchar2適用於長度不固定的，一般不含中文的情況
3.nvarchar/nvarchar2
nvarchar和nvarchar2是長度不固定的
nvarchar不區分中英文，比如說：你定義了nvarchar(20),你可以存入20個英文字母/漢字或中英文組合，這個20定義的是字元數而不是位元組數
nvarchar2基本上等同於nvarchar，不同在於nvarchar2中存的英文字母也佔兩個位元組
nvarchar/nvarchar2適用於存放中文
char [ ( n ) ]
固定長度，非 Unicode 字元資料，長度為 n 個位元組。n 的取值範圍為 1 至 8,000，儲存大小是 n 個位元組。
varchar [ ( n | max ) ]
可變長度，非 Unicode 字元資料。n 的取值範圍為 1 至 8,000。max 指示最大儲存大小是 2^31-1 個位元組。儲存大小是輸入資料的實際長度加 2 個位元組，用於反映儲存的資料的長度。所輸入資料的長度可以為 0 個字元。
* 如果列資料項的大小一致，則使用 char。
* 如果列資料項的大小差異相當大，則使用 varchar。
* 如果列資料項大小相差很大，而且大小可能超過 8,000 位元組，請使用 varchar(max)。
如果未在資料定義或變數宣告語句中char 或 varchar 資料型別指定 n，則預設長度為 1。如果在使用 CAST 和 CONVERT 函式時char 或 varchar 資料型別未指定 n，則預設長度為 30。
當執行 CREATE TABLE 或 ALTER TABLE 時，如果 SET ANSI_PADDING 為 OFF，則定義為 NULL 的 char 列將作為 varchar 處理。
另外幫助理解的，只供參考：轉自http://www.51testing.com/?uid-258885-action-viewspace-itemid-141197
也可參照學習http://ce.sysu.edu.cn/garden/dispbbs.asp?boardid=26&ID=8774&replyID=18180&skin=1
1.NULL值(空值)。
a. char列的NULL值佔用儲存空間。
b. varcahr列的NULL值不佔用儲存空間。
c. 插入同樣數量的NULL值，varchar列的插入效率明顯高出char列。
2.插入資料
無論插入資料涉及的列是否建立索引，char的效率都明顯低於varchar。
3. 更新資料
如果更新的列上未建立索引，則char的效率低於varchar，差異不大；建立索引的話，效率較高。
4. 修改結構
a. 無論增加或是刪除的列的型別是char還是varchar，操作都能較快的完成，而且效率上沒有什麼差異。
b. 對於增加列的寬度而言，char與varchar有非常明顯的效率差異，修改varcahr列基本上不花費時間，而修改char列需要花費很長的時間。
5.資料檢索
無論是否通過索引，varchar型別的資料檢索略優於char的掃描。
選擇char還是選擇varchar的建議
1.適宜於char的情況：
a. 列中的各行資料長度基本一致，長度變化不超過50位元組；
b. 資料變更頻繁，資料檢索的需求較少。
c. 列的長度不會變化，修改char型別列的寬度的代價比較大。
d. 列中不會出現大量的NULL值。
e. 列上不需要建立過多的索引，過多的索引對char列的資料變更影響較大。
2.適宜於varchar的情況;
a. 列中的各行資料的長度差異比較大。
b. 列中資料的更新非常少，但查詢非常頻繁。
c. 列中經常沒有資料，為NULL值或為空值
nchar [ ( n ) ]
n 個字元的固定長度的 Unicode 字元資料。n 值必須在 1 到 4,000 之間（含）。儲存大小為兩倍 n 位元組。
nvarchar [ ( n | max ) ]
可變長度 Unicode 字元資料。n 值在 1 到 4,000 之間（含）。max 指示最大儲存大小為 2^31-1 位元組。儲存大小是所輸入字元個數的兩倍 + 2 個位元組。所輸入資料的長度可以為 0 個字元。
註釋
如果沒有在資料定義或變數宣告語句中指定 n，則預設長度為 1。如果沒有使用 CAST 函式指定 n，則預設長度為 30。
如果列資料項的大小可能相同，請使用 nchar。
如果列資料項的大小可能差異很大，請使用 nvarchar。
sysname 是系統提供的使用者定義資料型別，除了不可為空值外，在功能上與 nvarchar(128) 相同。sysname 用於引用資料庫物件名。
為使用 nchar 或 nvarchar 的物件分配的是預設的資料庫排序規則，但可使用 COLLATE 子句分配特定的排序規則。
SET ANSI_PADDING ON 永遠適用於 nchar 和 nvarchar。SET ANSI_PADDING OFF 不適用於 nchar 或 nvarchar 資料型別。
在Oracle中CHAR,NCHAR,VARCHAR,VARCHAR2,NVARCHAR2這五種型別的區別
1.CHAR(size)和VARCHAR(size)的區別
CHAR為定長的欄位，最大長度為2K位元組；
VARCHAR為可變長的欄位，最大長度為4K位元組；
2.CHAR(size)和NCHAR(size)的區別
CHAR如果存放字母數字佔1個位元組，存放GBK編碼的漢字存放2個位元組，存放UTF-8編碼的漢字佔用3個位元組；
NCHAR根據所選字符集來定義存放字元的佔用位元組數，一般都為2個位元組存放一個字元(不管字元或者漢字)
3.VARCHAR(size)和VARCHAR2(size)的區別
在現在的版本中，兩者是沒有區別的；最大長度為4K位元組；推薦使用VARCHAR2；
4.VARCHAR2(size)和NVARCHAR2(size)的區別
最大長度為4K位元組，區別同CHAR與NCHAR的區別；（如果資料庫字符集長度是2，則NVARCHAR2最大為2K）
5.共同特性
當執行insert的時候，插入的值為''，則轉變成null，即insert ... values('') <=> insert ... values(null)
搜尋的條件須用where xx is null
6.例子
比如有一個性別欄位，裡面存放“男，女”的其中一個值，兩種常用選擇
CHAR(2) 和 NCHAR(1)
四、 NLS_LENGTH_SEMANTICS引數
值設定資料庫字符集長度
BYTE ZHS16GBK 2
AL32UTF8 3
CHAR ZHS16GBK 1
AL32UTF8

ORACLE初始化引數：NLS_LENGTH_SEMANTICS
初始化引數NLS_LENGTH_SEMANTICS用於指定CHAR列或VARCHAR2列的長度定義方式，預設值為BYTE。當設定該引數為BYTE時，表示定義CHAR列或VARCHAR2列採用位元組長度方式；當設定該引數為CHAR時，表示定義CHAR列或VARCHAR2列採用字元個數方式。需要注意，設定該引數對於已存在列沒有作用。
該初始化引數是動態引數，可以使用ALTER SESSION或ALTER SYSTEM命令進行修改。示例如下：
SQL>ALTER SESSION SET nls_length_semantics=char;
SQL>CREATE TABLE t1(cola VACHAR2(4));
SQL>INSERT INTO t1 VALUES(‘北京’);
SQL>INSERT INTO t1 VALUES(‘BEIJING’);
上例將報錯ORA-12899。
附甲骨文官方說明：
屬性說明
引數型別 String
語法 NLS_LENGTH_SEMANTICS = string
Example: NLS_LENGTH_SEMANTICS = 'CHAR'
預設值 BYTE
允許動態修改 ALTER SESSION
取值範圍 BYTE | CHAR
NLS_LENGTH_SEMANTICS enables you to create CHAR and VARCHAR2 columns using either byte or character length semantics. Existing columns are not affected.
NCHAR, NVARCHAR2, CLOB, and NCLOB columns are always character-based. You may be required to use byte semantics in order to maintain compatibility with existing applications.
NLS_LENGTH_SEMANTICS does not apply to tables in SYS and SYSTEM. The data dictionary always uses byte semantics.

從9i開始，oracle提供了NLS_LENGTH_SEMANTICS這個引數，其有兩個取值，CHAR和BYTE。當為CHAR時字元型別的長度是按字元個數來計算，而不是按BYTE來計算，這在使用變長字符集(AL32UTF8)的情況下非常有用，因為一個字元所佔用的位元組數是不定的，就給我們準確估計欄位長度(BYTE)帶來不便。同時當為CHAR時，對那些採用7/8bit的字符集(US7ASCII/WE8MSWIN1252)來說也不會帶來空間上的浪費。
下面就使用使用該引數需要注意的一些地方做出描述
1、該引數分為三個級別，分別是資料庫、例項、會話三個級別
可以分別在NLS_DATABASE_PARAMETERS、NLS_INSTANCE_PARAMETERS、NLS_SESSION_PARAMETERS裡查詢到
資料庫級的值在創意資料庫時被指定，例項級的值可以通過修改init.ora/Spfile來指定，會話級的可以使用alter session來指定
2、例項/會話級的的引數只對其修改之後的物件(包括欄位和pl/sql變數)產生作用，修改之前的維持不變。
可以使用ALTER SYSTEM SET NLS_LENGTH_SEMANTICS=CHAR scope=spfile;來修改例項級的引數值
注意，必須需要重啟才會生效！(就算指定了scope=both)
該引數取值決定順序
1、如果沒有客戶端(環境變數，後面會講到)或者會話級指定該值，該引數由例項級的值決定(通過NLS_INSTANCE_PARAMETERS查詢)
2、從10g開始，NLS_LENGTH_SEMANTICS可以在環境變數或者登錄檔中設定，一旦設定之後會話級的值預設為環境變數或登錄檔中的取值
(通過NLS_SESSION_PARAMETERS查詢)
3、可以使用ALTER SESSION SET NLS_LENGTH_SEMANTICS=CHAR改變當前會話的取值。
其它注意事項
Oracle E-business Suite不支援該引數的CHAR語義
在11g以下的資料庫中，不要在建立資料庫時指定 NLS_LENGTH_SEMANTICS=CHAR，而應該使用 NLS_LENGTH_SEMANTICS=BYTE(或者不指定)來建立資料庫，然後在修改ini.ora/spfile裡改引數的值。否則會導致資料建立期間部分XDB和SYS的物件使用不支援的CHAR語義，產生錯誤，不過11.1.0.6之後已經得到解決。詳見 Bug 5545716 和 Bug 4886376
資料庫級取值為BYTE，例項級取值為CHAR 和資料庫級取值為CHAR，例項級取值為CHAR 是相同的，所以沒必要糾結，詳情可以參閱 Note 241047.1 The Priority of NLS Parameters Explained.
你需要在BYTE語義下呼叫使用了STARTUP MIGRATE的指令碼(比如patch指令碼或者 $ORACLE_HOME/RDBMS/ADMIN 下的指令碼(比如catalog.sql ))
If you run patch scripts or scripts from $ORACLE_HOME/RDBMS/ADMIN like catalog.sql use STARTUP MIGRATE; and run then the scripts. ( run them with NLS_LENGTH_SEMANTICS=BYTE )
* Oracle Text does NOT support instance wide NLS_LENGTH_SEMANTICS=CHAR If you are using Oracle Text then the database needs to be created and started with NLS_LENGTH_SEMANTICS=BYTE.
Using CHAR semantics on column definitions itself is supported however.Bug 4465964
This is not documented in the 10.2 docset, it is in the 11g doc: http://download.oracle.com/docs/cd/B28359_01/text.111/b28304/csql.htm#i997737 section "CHARSET COLUMN charset_column_name"
9i以下的客戶端不能識別CHAR語義，當你用8i客戶端去連線9i UTF8資料庫時，資料庫的VARCHAR2(10 CHAR)會以BYTE語義顯示為VARCHAR2(30)
這樣8i的的客戶端才能插入不超過30byte的資料，鑑於此，當使用CHAR語義時，最好使用9i以上的客戶端。
可以使用以下方法限定客戶端版本為9及以上：
在10.1 中可以在init.ora中指定DB_ALLOWED_LOGON_VERSION=9
10.2及以上，在sqlnet.ora中指定SQLNET.ALLOWED_LOGON_VERSION = 9
注意：不要在10.2及以上使用DB_ALLOWED_LOGON_VERSION
設定之後，如果用8i及以下的客戶端訪問資料庫會報ORA-28040: No matching authentication protocol錯誤
預設值
當NLS_LENGTH_SEMANTICS取值為BYTE時，預設為BYTE；當取值為CHAR時，預設為CHAR。
意思就是說，如果NLS_LENGTH_SEMANTICS=BYTE char(10)實際上就是 char(10 byte)
如果NLS_LENGTH_SEMANTICS=CHAR char(10)實際上就是 char(10 CHAR)
不管NLS_LENGTH_SEMANTICS取值為何，都可以在使用時顯示的指定是按CHAR還是BYTE
例如，例項NLS_LENGTH_SEMANTICS=BYTE，在建立表時可以指定char(10 char)就可以使用char語義了
另外，對於單位元組字符集編碼來說，CHAR=BYTE
NLS_LENGTH_SEMANTICS對於屬於SYS的表(對SYSTEM有效)無效，如下：
SQL> ALTER SESSION SET NLS_LENGTH_SEMANTICS=CHAR;
Session altered.
SQL> Create table sys.test1 (Col1 CHAR(20),Col2 VARCHAR2(100));
Table created.
SQL> Create table sys.test2 (Col1 CHAR(20 CHAR),Col2 VARCHAR2(100 CHAR));
Table created.
SQL> desc test1
Name Null? Type
----------------------------------------- -------- ----------------------------
COL1 CHAR(20 BYTE)
COL2 VARCHAR2(100 BYTE)
SQL> desc test2
Name Null? Type
----------------------------------------- -------- ----------------------------
COL1 CHAR(20)
COL2 VARCHAR2(100)

PL/SQL與NLS_LENGTH_SEMANTICS
儲存過程和包裡的在建立或者重新編譯的時候會讀取當前SESSION的NLS_LENGTH_SEMANTICS值，並存入程式碼中。考慮下面的問題
當前會話使用的是BYTE語義，你建立了一個儲存過程，裡面有一個變數的型別宣告為CHAR(10)，那麼此時該變數實際為CHAR(10 BYTE)，
有一天你把BYTE改成了CHAR，然後去呼叫該儲存過程，你會發現報 ORA-06502 PL/SQL Numeric or value error錯誤，因為雖然環境使用的是CHAR語義，
但是儲存過程裡還是使用的BYTE語義，此時你需要重要編譯該儲存過程，它後重新讀取當前SESSION的值，寫入程式碼中。
鑑於此，建議在書寫儲存過程的時候顯示宣告其語義(CHAR(10 CHAR))，以免不必要的麻煩。
可以通過下在的SQL來檢視現有儲存過程使用的語義
SELECT C.owner
||'.'
|| C.name
||' - '
|| C.type
FROM DBA_PLSQL_OBJECT_SETTINGS C
WHERE C.NLS_LENGTH_SEMANTICS = 'CHAR'
-- to have a list of all using BYTE semantics use
-- WHERE C.NLS_LENGTH_SEMANTICS = 'BYTE'
-- to check only for a certain users use
-- and C.owner IN ('SCOTT')
ORDER BY C.owner,C.name
NLS_LENGTH_SEMANTICS
Property Description
Parameter type String
Syntax NLS_LENGTH_SEMANTICS = string
Example: NLS_LENGTH_SEMANTICS = 'CHAR'

Default value BYTE
Modifiable ALTER SESSION
Range of values BYTE | CHAR

The session-level value of NLS_LENGTH_SEMANTICS specifies the default length semantics to use for VARCHAR2 and CHAR table columns, user-defined object attributes, and PL/SQL variables in database objects created in the session. This default may be overridden by the explicit length semantics qualifiers BYTE and CHAR in column, attribute, and variable definitions.

會話級別的nls_length_semantics說明預設的char和varchar2，使用者定義的物件屬性，和pl/sql變數在資料庫物件建立的長度語法。預設值會被顯示說明的引數值覆蓋。

The instance-level value of NLS_LENGTH_SEMANTICS provides a default for the session-level value if NLS_LENGTH_SEMANTICS it is not set explicitly by the database client through the NLS_LENGTH_SEMANTICS client environment variable (does not apply to JDBC Thin clients), or the ALTER SESSION SET NLS_LENGTH_SEMANTICS statement.

例項級別的nls_length_semantics提供預設的會話級別的值，如果資料庫客戶端沒有顯示的設定。

NCHAR, NVARCHAR2, CLOB, and NCLOB columns are always character-based.

Sessions logged in as SYS do not use the NLS_LENGTH_SEMANTICS parameter. They use BYTE length semantics for all created objects unless overridden by the explicit BYTE and CHAR qualifiers in object definitions (SQL DDL statements).

sys使用者不使用nls_length_semantics引數，他們預設使用BYTE，除非在DDL列屬性的時候顯示的指定BYTE或CHAR.

Caution:

Oracle strongly recommends that you do NOT set the NLS_LENGTH_SEMANTICS parameter to CHAR in the instance or server parameter file. This may cause many existing installation scripts to unexpectedly create columns with character length semantics, resulting in runtime errors, including buffer overflows.
oracle強烈建議你不要在例項或者服務引數檔案中設定nls_length_semantics引數為CHAR。這是因為許多存在的安裝指令碼會在使用字元長度語義建立列的時候出現異常，導致執行失敗，包括快取溢位。

Any DBA wanting to use NLS_LENGTH_SEMANTICS.

NLS_LENGTH_SEMANTICS allows you to specify the length of a column datatype in terms of CHARacters rather than in terms of BYTEs. Typically this is when using an AL32UTF8 or other varying width NLS_CHARACTERSET database where one character is not always one byte. While using CHAR semantics has as such no added value in a 7/8 bit characterset it's fully supported so any application code / table setup using CHAR can also be used in a 7/8bit characterset like US7ASCII/WE8MSWIN1252.
This parameter is a 9i (and up) feature and is not available in older releases.

nls_length_semantics允許你說明列型別的長度為CHAR而不是BYTES。典型的當使用AL32UTF8或者其他多位元組字元型別。該引數在9i中出現。

* NLS_LENGTH_SEMANTICS cannot be set as a client side environment/registry parameter in 9i, from 10g onwards NLS_LENGTH_SEMANTICS can be set (please define it in UPPERCASE). If NLS_LENGTH_SEMANTICS is set at client side then any session started from that client will use the value defined in the environment/registry and it can be checked in NLS_SESSION_PARAMETERS.
9i客戶端不能設定nls_length_semantics，從10g開始時可以的。如果在客戶端設定了nls_length_semantics那麼所有使用該客戶端開始的會話將會使用定義在環境或者註冊中，它將會檢查nls_session_parameters中的設定。
* If NLS_LENGTH_SEMANTICS is not set at client side or no alter session is done then the session will use the value found in NLS_INSTANCE_PARAMETERS.
如果nls_length_semantics沒有在客戶端定義，那麼會話將在nls_instance_parameters中查詢。(pfile/spfile)
* The NLS_LENGTH_SEMANTICS parameter can be set at instance level in the init.ora or Spfile. You will then see the parameter change in NLS_INSTANCE_PARAMETERS.

更改nls_instance_parameters屬性在init.ora或者spfile中。你可以使用alter system更改。 SQL>ALTER SYSTEM SET NLS_LENGTH_SEMANTICS=CHAR scope=spfile;

Note :
* Oracle advices to use explicit CHAR semantics in the SQL or PL/SQL syntax or to make sure your application does an alter session when connecting if CHAR semantics is required and the semantic is not defined explicit in SQL.
* A lot of people think simply setting NLS_LENGTH_SEMANTICS=CHAR in the spfile/init.ora is the only thing to do to change an existing db to NLS_LENGTH_SEMANTICS=CHAR, this is not true.
* Oracle recommends to NOT set the NLS_LENGTH_SEMANTICS parameter to CHAR in the instance parameter file when possible.
NLS_LENGTH_SEMANTICS parameter to CHAR in the init.ora/spfile.
* use NLS_LENGTH_SEMANTICS=BYTE in init.ora/spfile and explicit define CHAR semantics in SQL or PL/SQL (= use VARCHAR2(10 CHAR) etc )
* to have your application do an "ALTER SESSION SET NLS_LENGTH_SEMANTICS=CHAR " when connecting if this application wants to use CHAR semantics .
* use an explicit "ALTER SESSION SET NLS_LENGTH_SEMANTICS=CHAR" in the beginning of schema creation scripts if the SQL does not use explicit CHAR semantics ( ...VARCHAR2(10 CHAR) ...etc ) but uses the session setting ( ...VARCHAR2(10) ...etc ) and the itention is to have them created using CHAR semantics.
The whole idea is simply that many (third party / application install / even maybe some Oracle provided ) scripts still assume NLS_LENGTH_SEMANTICS=BYTE and by using NLS_LENGTH_SEMANTICS=BYTE as init.ora/spfile they will not be affected.This is not matter of "supported" it's basically a matter of trying to avoid possible issues. There are currently no known issues with setting NLS_LENGTH_SEMANTICS=CHAR in the spfile/init.ora for Oracle RDBMS scripts for Oracle version 11.1.0.6 and higher.By explicit using CHAR semantics in SQL and PLS/SQL syntax or defining NLS_LENGTH_SEMANTICS=CHAR on session level each time when needed one also avoid problems who might arise from accidentally using a NLS_LENGTH_SEMANTICS=BYTE setting when CHAR is actually intended

在Oracle資料庫中和Oracle FORM中都有這樣一個引數的設定，有必要澄清一下：
引數NLS_LENGTH_SEMANTICS的含義在Oracle文件中這樣描述：
Oracl文件中的說明： Syntax: NLS_LENGTH_SEMANTICS = string Range of values: BYTE | CHAR NLS_LENGTH_SEMANTICS enables you to create CHAR and VARCHAR2 columns using either byte or character length semantics. Existing columns are not affected. NCHAR, NVARCHAR2, CLOB, and NCLOB columns are always character-based. You may be required to use byte semantics in order to maintain compatibility with existing applications. NLS_LENGTH_SEMANTICS does not apply to tables in SYS and SYSTEM. The data dictionary always uses byte semantics.
翻譯過來的意思就是該引數有BYTE和CHAR兩種取值，使你能夠使用這兩種語義建立CHAR或VARCHAR2型別的資料庫表列，而對現有的列沒有影響。
二者的區別就是一個是按字元存放，另一個按位元組存放；一般情況資料庫都會使用UTF-8編碼，一個漢字如果按位元組存放，就需要3個位元組。
可以看到資料庫中引數NLS_LENGTH_SEMANTICS的設定，一般預設為‘BYTE’。
這是因為按位元組存放，3個漢字就需要9個位元組，如果把引數NLS_LENGTH_SEMANTICS設定為‘CHAR’，就可以將資料插入進去了。這是因為按資料庫會按字元計數，而不是位元組。
Oracle Form中：
ITEM屬性設定中也有Data Length Semantics這樣一個屬性設定，它有三種選擇“NULL”、“CHAR”和“BYTE”，如果設定為“NULL”則直接從資料庫中獲取，使用資料庫的設定。設定為“CHAR”和“BYTE”則和上述資料庫中的情況一樣。經過測試發現，如果一個字元型別的ITEM的最大長度為100，則當Data Length Semantics設定為“CHAR”時，最多可以輸入100個漢字或100個英文字元；當Data Length Semantics設定為“BYTE”時，最多可以輸入33個漢字或100個英文字元。
舉例如下：
SQL> show parameter nls_length
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_length_semantics string BYTE
SQL> create table byte_test (a varchar2(3));
表已建立。
SQL> insert into byte_test values('甲骨文');
insert into byte_test values('甲骨文')
*
ERROR 位於第 1 行:
ORA-01401: inserted value too large for column
SQL> alter session set NLS_LENGTH_SEMANTICS='CHAR';
會話已更改。
SQL> create table char_test (a varchar2(3));
表已建立。
SQL> insert into char_test values('甲骨文');
已建立 1 行。
SQL> show parameter NLS_LENGTH_SEMANTICS
NAME TYPE VALUE
------------------------------------ ----------------------------------------------------
nls_length_semantics string CHAR
SQL> desc byte_test;
名稱是否為空? 型別
---------------------------------------- -------- ----------------------------
A VARCHAR2(3 BYTE)
SQL> desc char_test;
名稱是否為空? 型別
----------------------------------------- -------- ----------------------------
A VARCHAR2(3)

NLS_LENGTH_SEMANTICS
Property Description
Parameter type String
Syntax NLS_LENGTH_SEMANTICS = string
Example: NLS_LENGTH_SEMANTICS = 'CHAR'
Default value BYTE
Modifiable ALTER SESSION
Range of values BYTE | CHAR

The session-level value of NLS_LENGTH_SEMANTICS specifies the default length semantics to use forVARCHAR2 and CHAR table columns, user-defined object attributes, and PL/SQL variables in database objects created in the session. This default may be overridden by the explicit length semantics qualifiers BYTE and CHAR in column, attribute, and variable definitions.
會話級別的nls_length_semantics說明預設的char和varchar2，使用者定義的物件屬性，和pl/sql變數在資料庫物件建立的長度語法。預設值會被顯示說明的引數值覆蓋。
The instance-level value of NLS_LENGTH_SEMANTICS provides a default for the session-level value ifNLS_LENGTH_SEMANTICS it is not set explicitly by the database client through theNLS_LENGTH_SEMANTICS client environment variable (does not apply to JDBC Thin clients), or theALTER SESSION SET NLS_LENGTH_SEMANTICS statement.
例項級別的nls_length_semantics提供預設的會話級別的值，如果資料庫客戶端沒有顯示的設定。
NCHAR, NVARCHAR2, CLOB, and NCLOB columns are always character-based.
Sessions logged in as SYS do not use the NLS_LENGTH_SEMANTICS parameter. They use BYTE length semantics for all created objects unless overridden by the explicit BYTE and CHAR qualifiers in object definitions (SQL DDL statements).
sys使用者不使用nls_length_semantics引數，他們預設使用BYTE，除非在DDL列屬性的時候顯示的指定BYTE或CHAR.
Caution:
Oracle strongly recommends that you do NOT set the NLS_LENGTH_SEMANTICS parameter to CHAR in the instance or server parameter file. This may cause many existing installation scripts to unexpectedly create columns with character length semantics, resulting in runtime errors, including buffer overflows.
oracle強烈建議你不要在例項或者服務引數檔案中設定nls_length_semantics引數為CHAR。這是因為許多存在的安裝指令碼會在使用字元長度語義建立列的時候出現異常，導致執行失敗，包括快取溢位。

Any DBA wanting to use NLS_LENGTH_SEMANTICS.

NLS_LENGTH_SEMANTICS allows you to specify the length of a column datatype in terms of CHARacters rather than in terms of BYTEs. Typically this is when using an AL32UTF8 or other varying width NLS_CHARACTERSET database where one character is not always one byte. While using CHAR semantics has as such no added value in a 7/8 bit characterset it's fully supported so any application code / table setup using CHAR can also be used in a 7/8bit characterset like US7ASCII/WE8MSWIN1252.
This parameter is a 9i (and up) feature and is not available in older releases.

nls_length_semantics允許你說明列型別的長度為CHAR而不是BYTES。典型的當使用AL32UTF8或者其他多位元組字元型別。該引數在9i中出現。
* NLS_LENGTH_SEMANTICS cannot be set as a client side environment/registry parameter in 9i, from 10g onwards NLS_LENGTH_SEMANTICS can be set (please define it in UPPERCASE). If NLS_LENGTH_SEMANTICS is set at client side then any session started from that client will use the value defined in the environment/registry and it can be checked in NLS_SESSION_PARAMETERS.
9i客戶端不能設定nls_length_semantics，從10g開始時可以的。如果在客戶端設定了nls_length_semantics那麼所有使用該客戶端開始的會話將會使用定義在環境或者註冊中，它將會檢查nls_session_parameters中的設定。
* If NLS_LENGTH_SEMANTICS is not set at client side or no alter session is done then the session will use the value found in NLS_INSTANCE_PARAMETERS.
如果nls_length_semantics沒有在客戶端定義，那麼會話將在nls_instance_parameters中查詢。(pfile/spfile)
* The NLS_LENGTH_SEMANTICS parameter can be set at instance level in the init.ora or Spfile. You will then see the parameter change in NLS_INSTANCE_PARAMETERS.
更改nls_instance_parameters屬性在init.ora或者spfile中。你可以使用alter system更改。
SQL>ALTER SYSTEM SET NLS_LENGTH_SEMANTICS=CHAR scope=spfile;
Note :
* Oracle advices to use explicit CHAR semantics in the SQL or PL/SQL syntax or to make sure your application does an alter session when connecting if CHAR semantics is required and the semantic is not defined explicit in SQL.
* A lot of people think simply setting NLS_LENGTH_SEMANTICS=CHAR in the spfile/init.ora is the only thing to do to change an existing db to NLS_LENGTH_SEMANTICS=CHAR, this is not true.
* Oracle recommends to NOT set the NLS_LENGTH_SEMANTICS parameter to CHAR in the instance parameter file when possible.
NLS_LENGTH_SEMANTICS parameter to CHAR in the init.ora/spfile.
* use NLS_LENGTH_SEMANTICS=BYTE in init.ora/spfile and explicit define CHAR semantics in SQL or PL/SQL (= use VARCHAR2(10 CHAR) etc )
* to have your application do an "ALTER SESSION SET NLS_LENGTH_SEMANTICS=CHAR " when connecting if this application wants to use CHAR semantics .
* use an explicit "ALTER SESSION SET NLS_LENGTH_SEMANTICS=CHAR" in the beginning of schema creation scripts if the SQL does not use explicit CHAR semantics ( ...VARCHAR2(10 CHAR) ...etc ) but uses the session setting ( ...VARCHAR2(10) ...etc ) and the itention is to have them created using CHAR semantics.
The whole idea is simply that many (third party / application install / even maybe some Oracle provided ) scripts still assume NLS_LENGTH_SEMANTICS=BYTE and by using NLS_LENGTH_SEMANTICS=BYTE as init.ora/spfile they will not be affected.This is not matter of "supported" it's basically a matter of trying to avoid possible issues. There are currently no known issues with setting NLS_LENGTH_SEMANTICS=CHAR in the spfile/init.ora for Oracle RDBMS scripts for Oracle version 11.1.0.6 and higher.By explicit using CHAR semantics in SQL and PLS/SQL syntax or defining NLS_LENGTH_SEMANTICS=CHAR on session level each time when needed one also avoid problems who might arise from accidentally using a NLS_LENGTH_SEMANTICS=BYTE setting when CHAR is actually intended.

1.3.2.6 布林型別(BOOLEAN)
布林型別能儲存邏輯值TRUE、FALSE和NULL(NULL代表缺失、未知或不可用的值)。只有邏輯操作符才允許應用在布林變數上。
資料庫SQL型別並不支援布林型別，只有PL/SQL才支援。所以就不能往資料庫中插入或從資料庫中檢索出布林型別的值。
1.3.2.7 Datetime和Interval型別
Datetime就是日期時間型別，而Interval指的是時間的間隔。Datetime和Interval型別都由幾個域組成，下表是對每個域及其它們對應的有效值描述：
域名稱有效日期時間值有效間隔值
YEAR -4712 到 9999 (不包括0) 任意非零整數
MONTH 01 到 12 0 到 11
DAY 01 到 31 (根據當地的歷法規則，
受MONTH和YEAR值的限制任意非零整數
HOUR 00 到 23 0 到 23
MINUTE 00 到 59 0 到 59
SECOND 00 到 59.9(n)，
9(n)是秒小數部分的精度 0 to 59.9(n)，
9(n)間隔秒的小數部分的精度
TIMEZONE_HOUR -12 到 14 (隨日光節約時間的變化而變化) 不可用
TIMEZONE_MINUTE 00 到 59 不可用
TIMEZONE_REGION 檢視檢視V$TIMEZONE_NAMES 不可用
TIMEZONE_ABBR 檢視檢視V$TIMEZONE_NAMES 不可用
除了TIMESTAMP WITH LOCAL TIMEZONE以外，剩下的都是SQL92所支援的。
? DATE
DATE資料型別能夠儲存定長的日期時間。日期部分預設為當月的第一天；時間部分為午夜時間。函式SYSDATE能夠返回當前的日期和時間。
提示：如果只進行日期的等值比較，忽略時間部分，可以使用函式TRUNC(date_variable)。
有效的日期範圍是從公元前4721年1月1日到公元9999年12月31日。儒略日(Julian date)是自公元前4712年1月1日起經過的天數。我們可以使用日期模式"J"配合函式TO_DATE和TO_CHAR來把日期值轉換成等值的儒略日。
在日期表示式中PL/SQL會自動地將格式為預設日期格式的字元值轉成DATE型別值。預設的日期格式由Oracle的初始化引數NLS_DATE_FORMAT決定的。例如，預設格式可能是"DD-MON-YY"，它包含兩位數字表示一個月中的第幾日，月份名稱的縮寫和兩位記年用的數字。
我們可以對日期進行加減運算。例如，下面的語句就能返回員工自被僱用日起，至今所經過的天數：
SELECT SYSDATE - hiredate
INTO days_worked
FROM emp
WHERE empno = 7499;
在算術表示式中，PL/SQL會將整數文字當作"日"來處理，如"SYSDATE + 1"就代表明天的時間。
一、 TIMESTAMP

? 它是精度更高的時間期資料，使用示例：
? Timestamp ：預設時小數秒精度為6
? TIMESTAMP(n) ：指定小數秒精度為7
? Timestamp WITH TIME Zone
? Timestamp WITH LOCAL Time Zone

我們看看下面的試驗：
Create Table ts_test
(dt Date,
ts Timestamp,
ts1 TIMESTAMP(7),
ts2 Timestamp WITH TIME Zone,
ts3 Timestamp WITH LOCAL Time Zone);

Insert Into Ts_Test
Select Sysdate, Sysdate, Sysdate, Sysdate, Sysdate From Dual;

Select * From Ts_Test

DT TS TS1 TS2 TS3
2008-9-7
17:29
07-9月 -08
05.29.50.000000
下午
07-9月 -08
05.29.50.0000000
下午
07-9月 -08
05.29.50.000000
下午 +08:00
07-9月 -08
05.29.50.000000
下午
在上面的例子中，我們建立了一個表TS_TEST，其中欄位date的資料型別是date，ts 的
資料型別為Timestamp，ts1 的資料型別為Timestamp(7)，ts2 的資料型別為Timestamp WITH
TIME ZONE。
精度7指示小數秒的精度，如果不指定，小數秒的預設精度是6。

TIMESTAMP WITH TIME ZONE是TIMESTAMP的一個變數，它對TIMESTAMP值進行
一個時區轉換，在本地時間和UTC 之間，小時和分鐘的時區轉換是不同的。
UTC代表協調世界時—以前的格林尼治標準時間。如果兩個TIMESTAMP WITH TIME
ZONE在UTC中代表同一時刻，它們的值被認為是相同的，而不管儲存在資料中的TIME
ZONE偏移。
因為TIMESTAMP WITH TIME ZONE也可以儲存時區資訊，它特別適合記錄那些必須
組合或協調地理區域的日期資訊。
例如，
TIMESTAMP '1999-04-15 8:00:00 -8:00'
與
TIMESTAMP '1999-04-15 11:00:00 -5:00'
是相同的。
美國西部標準時間 8:00 a.m. 和東部標準時間 11:00 a.m. 是相同的。
該時間也可以被指定為：
TIMESTAMP '1999-04-15 8:00:00 US/Pacific'
注：小數秒精度指定SECOND日期時間欄位的小數部分數字的數目，其範圍是0到9，默
認是6。

TIMESTAMP WITH LOCAL TIME資料型別，TIMESTAMP WITH LOCAL TIME
ZONE 是TIMESTAMP的另一個變數，它對TIMESTAMP值進行一個時區轉換，儲存在資料
庫中的資料被格式化為資料庫時區，時區的轉換不被作為列資料的一部分儲存；Oracle 以
本地會話時區返回資料，TIMESTAMP WITH LOCAL TIME ZONE資料型別被如下指定：
TIMESTAMP[(fractional_seconds_precision)]WITH LOCAL TIME ZONE。
不像TIMESTAMP WITH TIME ZONE，你可以指定TIMESTAMP WITH LOCAL TIME
ZONE型別作為一個主鍵或唯一鍵的一部分。在本地時間和UTC之間的時區轉換 (小時或分

鍾) 是不同的，對於TIMESTAMP WITH LOCAL TIME ZONE是非文字的。
注：小數秒精度指定SECOND日期時間欄位的小數部分數字的數目，其範圍是0到9，默
認是6。
例如：
CREATE TABLE time_example
(order_date TIMESTAMP WITH LOCAL TIME ZONE);
INSERT INTO time_example VALUES('15-NOV-00 09:34:28 AM');
SELECT *
FROM time_example;
order_date
----------------------------
15-NOV-00 09.34.28.000000 AM
TIMESTAMP WITH LOCAL TIME ZONE型別適合於兩層應用程式，在其中你可以用客
戶系統的時區顯示日期和時間。

TIMESTAMP是對DATE的擴充套件，包含了年月日時分秒，語法如下：
TIMESTAMP[(precision)]
precision是可選引數，用於指定秒的小數部分數字個數。引數precision不可以是常量或變數，其有效範圍是0到9，預設值是6。預設的時間戳(timestamp)格式是由Oracle初始化參NLS_TIMESTAMP_FORMAT決定的。
在下面的例子中，我們宣告瞭一個TIMESTAMP型別的變數，然後為它賦值：
DECLARE
checkout TIMESTAMP ( 3 );
BEGIN
checkout := '1999-06-22 07:48:53.275';
...
END;
這個例子中，秒的小數部分是0.275。

select systimestamp from dual;

1、比較
oracle中TIMESTAMP與DATE比較
oracle資料庫中timestamp資料型別精度

DATE資料型別
　　這個資料型別我們實在是太熟悉了，當我們需要表示日期和時間的話都會想到date型別。它可以儲存月，年，日，世紀，時，分和秒。它典型地用來表示什麼時候事情已經發生或將要發生。DATE資料型別的問題在於它表示兩個事件發生時間間隔的度量粒度是秒。這個問題將在文章稍後討論timestamp的時候被解決。可以使用TO_CHAR函式把DATE資料進行傳統地包裝，達到表示成多種格式的目的。　　

　　我見到的大多數人陷入的麻煩就是計算兩個時間的間隔年數、月數、天數、小時數和秒數。你需要明白的是，當你進行兩個日期的相減運算的時候，得到的是天數。你需要乘上每天的秒數（1天=86400秒），然後，你可以再次計算得到你想要的間隔數。下面就是我的解決方法，可以精確計算出兩個時間的間隔。我明白這個例子可以更簡短些，但是我是為了顯示所有的數字來強調計算方式。

SELECT TO_CHAR(date1,'MMDDYYYY:HH24:MI:SS') date1,
TO_CHAR(date2,'MMDDYYYY:HH24:MI:SS') date2,　　
trunc(86400*(date2-date1))-60*(trunc((86400*(date2-date1))/60)) seconds,　　
trunc((86400*(date2-date1))/60)-60*(trunc(((86400*(date2-date1))/60)/60)) minutes,　　
trunc(((86400*(date2-date1))/60)/60)-24*(trunc((((86400*(date2-date1))/60)/60)/24)) hours,　　
trunc((((86400*(date2-date1))/60)/60)/24) days,　　
trunc(((((86400*(date2-date1))/60)/60)/24)/7) weeks　　
FROM date_table

DATE1　　DATE2　　SECONDS　　MINUTES　 HOURS　　DAYS 　WEEKS
　　　　----------------- ----------------- ---------- ---------- ---------- ---------- ----------
06202003:16:55:14 07082003:11:22:57　　　43　　27　 18　　 17　　2
06262003:11:16:36 07082003:11:22:57　　 21　　　6　　0　　　　12　　1
　
TIMESTAMP 資料型別
　　DATE資料型別的主要問題是它粒度不能足夠區別出兩個事件哪個先發生。ORACLE已經在DATE資料型別上擴充套件出來了TIMESTAMP資料型別，它包括了所有DATE資料型別的年月日時分秒的資訊，而且包括了小數秒的資訊。如果你想把DATE型別轉換成TIMESTAMP型別，就使用CAST函式。

　　正如你看到的，在轉換後的時間段尾部有了一段“.000000”。這是因為從date轉換過來的時候，沒有小數秒的資訊，預設為0。而且顯示格式是按照引數NLS_TIMESTAMP_FORMAT定的預設格式顯示。當你把一個表中date型別欄位的資料移到另一個表的timestamp型別欄位中去的時候，可以直接寫INSERT SELECT語句，oracle會自動為你做轉換的。　
TIMESTAMP資料的格式化顯示和DATE 資料一樣。注意，to_char函式支援date和timestamp，但是trunc卻不支援TIMESTAMP資料型別。這已經清楚表明了在當兩個時間的差別極度重要的情況下，使用TIMESTAMP資料型別要比DATE資料型別更確切　　
　　如果你想顯示TIMESTAMP的小數秒資訊，參考下面：

　　在上例中，我只現實了小數點後3位的內容。　　
　　計算timestamp間的資料差別要比老的date資料型別更容易。當你直接相減的話，看看會發生什麼。結果將更容易理解，第一行的17天，18小時，27分鐘和43秒。
SELECT time1,　　time2,　　
substr((time2-time1),instr((time2-time1),' ')+7,2)　　　seconds,　　
substr((time2-time1),instr((time2-time1),' ')+4,2)　　　minutes,　　
substr((time2-time1),instr((time2-time1),' ')+1,2)　　　hours,　　
trunc(to_number(substr((time2-time1),1,instr(time2-time1,' '))))　 days,　　
trunc(to_number(substr((time2-time1),1,instr(time2-time1,' ')))/7) weeks
FROM date_table

TIME1　　TIME2　　　SECONDS MINUTES HOURS DAYS WEEKS
　　-------------------------　 -------------------------- ------- ------- ----- ---- -----
06/20/2003:16:55:14:000000　07/08/2003:11:22:57:000000　43　　 27　　　18　　17　　2
06/26/2003:11:16:36:000000　07/08/2003:11:22:57:000000　21　　 06　　　00　　12　　1
　　這就意味著不再需要關心一天有多少秒在麻煩的計算中。因此，得到天數、月數、天數、時數、分鐘數和秒數就成為用substr函式摘取出數字的事情了
　　
系統日期和時間
為了得到系統時間，返回成date資料型別。你可以使用sysdate函式。

為了得到系統時間，返回成timestamp資料型別。你可以使用systimestamp函式。

　　你可以設定初始化引數FIXED_DATE指定sysdate函式返回一個固定值。這用在測試日期和時間敏感的程式碼。注意，這個引數對於systimestamp函式無效。
　　SQL> ALTER SYSTEM SET fixed_date = '2003-01-01-10:00:00';
　　System altered.
　　SQL> select sysdate from dual;
　　SYSDATE
　　---------
　　01-JAN-03
　　SQL> select systimestamp from dual;
　　SYSTIMESTAMP
　　---------------------------------------------------------
　　09-JUL-03 11.05.02.519000 AM -06:00
當使用date和timestamp型別的時候，選擇是很清楚的。你可以隨意處置date和timestamp型別。當你試圖轉換到更強大的timestamp的時候，需要注意，它們既有類似的地方，更有不同的地方，而足以造成破壞。兩者在簡潔和間隔尺寸方面各有優勢，請合理選擇。另外，date型別一般很少用，建議大家在產品裡面所有的date資料型別全部改為timestamp。

2、 cast函式
cast 是進行型別轉換的, 可以針對各種Oracle資料型別. 修改的是使用者的資料型別，可以將date型別轉換為timestamp型別的資料。
select cast(sysdate as timestamp) from dual;

select cast('321312' as number(8,2)) from dual ;

select UTL_RAW.CAST_TO_RAW('123') from dual;

二、 TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH TIME ZONE擴充套件了TIMESTAMP，能夠表現時區位移(time-zone displacement)。時區位移在本地時間和通用協調時間(UTC)中是不同的。語法如下：
TIMESTAMP[(precision)] WITH TIME ZONE
precision的用法同TIMESTAMP語法中的precision。預設格式由Oracle初始化引數NLS_TIMESTAMP_TZ_FORMAT決定。
下例中，我們宣告一個TIMESTAMP WITH TIME ZONE型別的變數，然後為其賦值：
DECLARE
LOGOFF TIMESTAMP ( 3 ) WITH TIME ZONE;
BEGIN
LOGOFF := '1999-10-31 09:42:37.114 +02:00';
...
END;
例子中時區位移是+02:00。我們還可以使用符號名稱(symbolic name)來指定時區位移，名稱可以是完整形式也可以是縮寫形式，如"US/Pacific"和"PDT"，或是組合的形式。例如，下面的文字全都表現同一時間。第三種形式最可靠，因為它指定了切換到日光節約時間時的規則。
TIMESTAMP '1999-04-15 8:00:00 -8:00'
TIMESTAMP '1999-04-15 8:00:00 US/Pacific'
TIMESTAMP '1999-10-31 01:30:00 US/Pacific PDT'
我們可以在資料詞典V$TIMEZONE_NAMES的TIMEZONE_REGION和TIMEZONE_ABBR欄位中找到相應的時區名稱和它的縮寫。如果兩個TIMESTAMP WITH TIME ZONE值在通用協調時間中的值一樣，那麼系統就會認為它們的值相同而忽略它們的時區位移。下例兩個值被認為是相同的，因為在通用協調時間裡，太平洋標準時間8:00 AM和(美國)東部時區11:00 AM是相同的：
'1999-08-29 08:00:00 -8:00'
'1999-08-29 11:00:00 -5:00'
? TIMESTAMP WITH LOCAL TIME ZONE
TIMESTAMP WITH LOCAL TIME ZONE是對TIMESTAMP WITH TIME ZONE的擴充套件，它的語法如下：
TIMESTAMP[(precision)] WITH LOCAL TIME ZONE
TIMESTAMP WITH LOCAL TIME ZONE的用途和TIMESTAMP WITH TIME ZONE相似，它們不同之處在於，當我們往資料庫中插入TIMESTAMP WITH LOCAL TIME ZONE型別資料的時候，資料會被轉成資料庫的時區，並且時區位移並不會存放在資料庫中。當進行資料檢索時，Oracle會按我們本地會話的時區設定返回值。下面就是一個使用TIMESTAMP WITH LOCAL TIME ZONE的例子：
DECLARE
LOGOFF TIMESTAMP ( 3 ) WITH LOCAL TIME ZONE;
BEGIN
NULL;
...
END;
我們不可以用文字值為這種型別的變數賦值。

三、 INTERVAL
1、 INTERVAL YEAR TO MONTH

? 使用語法如下：
INTERVAL YEAR [(year_precision)] TO MONTH
? 主要用於求日期n年、m月後的日期是那一天
? 是普通Data日期型的補充
INTERVAL YEAR TO MONTH用年和月日期時間欄位儲存一段時間。
用INTERVAL YEAR TO MONTH表示兩個日期時間值的差，該差值只有年和月的部分。
例如，你可能用該值設定一個往後120個月的提醒日期，或檢查是否從某個特定的日期後6
月已過去。
指定 INTERVAL YEAR TO MONTH 如下語法：
INTERVAL YEAR [(year_precision)] TO MONTH
year_precision 是在YEAR日期時間欄位中數字的數目，年精度的預設值是2。
例如：
CREATE TABLE te1
(loan_duration INTERVAL YEAR (3) TO MONTH);

INSERT INTO te1 (loan_duration)
VALUES (INTERVAL '120' MONTH(3));

SELECT TO_CHAR( sysdate+loan_duration, 'dd-mon-yyyy')
FROM te1;

TO_CHAR(SYSDATE+LOAN_DURATION,
07-9月 -2018

前面的部分要大於後面的部分，例如：INTERVAL '0-1' MONTH TO YEAR 是無效
的，必須寫成：INTERVAL '0-1' YEAR TO MONTH。

我們可以使用INTERVAL YEAR TO MONTH型別用來儲存年月的間隔，語法如下：
INTERVAL YEAR[(precision)] TO MONTH
precision指定間隔的年數。引數precision不能是常量或變數，其範圍在1到4之間，預設值是2，下面的例子中宣告瞭一個INTERVAL YEAR TO MONTH型別的變數，並把間隔值101年3個月賦給它：
DECLARE
lifetime INTERVAL YEAR(3)TO MONTH;
BEGIN
lifetime := INTERVAL '101-3' YEAR TO MONTH; -- interval literal
lifetime := '101-3'; -- implicit conversion from character type
lifetime := INTERVAL '101' YEAR; -- Can specify just the years
lifetime := INTERVAL '3' MONTH; -- Can specify just the months
...
END;
2、 INTERVAL DAY TO SECOND

INTERVAL DAY TO SECOND
? 使用語法：
INTERVAL DAY [(day_precision)]
TO SECOND [(fractional_seconds_precision)]
? 主要用於求日期n天、m小時後的準確時間
? 間隔型資料無法直接檢視，必須和一個日期進行執行後才有效。

INTERVAL DAY TO SECOND根據天、小時、分和秒儲存一段時間。用INTERVAL DAY
TO SECOND來表示兩個日期時間值精確的差。例如，你可能用該值設定一個往後36個小時
的提醒，或記錄一個賽跑的開始和結束之間的時間。為了表示很長的時間跨度，包括很多年，
用很高的精度，你可以用一個很大的值表示天的一部分。
指定 INTERVAL DAY TO SECOND 如下：
INTERVAL DAY [(day_precision)]
TO SECOND [(fractional_seconds_precision)]
day_precision 是在DAY日期時間欄位中數字的數目，可接受的值的範圍是0到9，預設是2。
fractional_seconds_precision 是在SECOND日期時間欄位中數字的數目，可接受的值的範圍是0到9，預設是6。
例如：
CREATE TABLE te2
(day_duration INTERVAL DAY (3) TO SECOND);
INSERT INTO te2 (day_duration)
VALUES (INTERVAL '180' DAY(3));

SELECT sysdate + day_duration "Half Year"
FROM te2;

Half Year
2009-3-6 17:38

我們可以使用INTERVAL DAY TO SECOND資料型別儲存和操作天、小時、分鐘和秒，語法如下：
INTERVAL DAY[(leading_precision)] TO SECOND[(fractional_seconds_precision)]
leading_precision和fractional_seconds_precision分別指定了天數和秒數。這兩個值都不可以用常量或變數指定，且只能使用範圍在0到9之間的整數文字為其賦值。它們的預設值分別為2和6。下面的例子中，我們宣告瞭一個INTERVAL DAY TO SECOND型別的變數：

DECLARE
lag_time INTERVAL DAY(3)TO SECOND(3);
BEGIN
IF lag_time > INTERVAL '6' DAY THEN ...
...
END;

Oracle語法:
INTERVAL '{ integer | integer time_expr | time_expr }'
{ { DAY | HOUR | MINUTE } [ ( leading_precision ) ]
| SECOND [ ( leading_precision [, fractional_seconds_precision ] ) ] }
[ TO { DAY | HOUR | MINUTE | SECOND [ (fractional_seconds_precision) ] } ]

leading_precision值的範圍是0到9, 預設是2. time_expr的格式為:HH[:MI[:SS[.n]]] or MI[:SS[.n]] or SS[.n], n表示微秒.
該型別與INTERVAL YEAR TO MONTH有很多相似的地方,建議先看INTERVAL YEAR TO MONTH再看該文.

範圍值:
HOUR: 0 to 23
MINUTE: 0 to 59
SECOND: 0 to 59.999999999

eg:
INTERVAL '4 5:12:10.222' DAY TO SECOND(3)
表示: 4天5小時12分10.222秒

INTERVAL '4 5:12' DAY TO MINUTE
表示: 4天5小時12分

INTERVAL '400 5' DAY(3) TO HOUR
表示: 400天5小時, 400為3為精度,所以"DAY(3)", 注意預設值為2.

INTERVAL '400' DAY(3)
表示: 400天

INTERVAL '11:12:10.2222222' HOUR TO SECOND(7)
表示: 11小時12分10.2222222秒

INTERVAL '11:20' HOUR TO MINUTE
表示: 11小時20分

INTERVAL '10' HOUR
表示: 10小時

INTERVAL '10:22' MINUTE TO SECOND
表示: 10分22秒

INTERVAL '10' MINUTE
表示: 10分

INTERVAL '4' DAY
表示: 4天

INTERVAL '25' HOUR
表示: 25小時

INTERVAL '40' MINUTE
表示: 40分

INTERVAL '120' HOUR(3)
表示: 120小時

INTERVAL '30.12345' SECOND(2,4)
表示: 30.1235秒, 因為該地方秒的後面精度設定為4, 要進行四捨五入.

INTERVAL '20' DAY - INTERVAL '240' HOUR = INTERVAL '10-0' DAY TO SECOND
表示: 20天 - 240小時 = 10天0秒

INTERVAL DAY TO SECOND型別儲存兩個TIMESTAMP之間的時間差異，用日期、小時、分鐘、秒鐘形式表示。該資料型別的內部程式碼是183，長度位11位元組：

l 4個位元組表示天數（增加0X80000000偏移量）
l 小時、分鐘、秒鐘各用一個位元組表示（增加60偏移量）
l 4個位元組表示秒鐘的小時差異（增加0X80000000偏移量）

以下是一個例子：

SQL> alter table testTimeStamp add f interval day to second ;

表已更改。

SQL> update testTimeStamp set f=(select interval '5' day + interval '10' second from dual);

已更新3行。

SQL> commit;

提交完成。

SQL> select dump(f,16) from testTimeStamp;

DUMP(F,16)

--------------------------------------------------------------------------------

Typ=183 Len=11: 80,0,0,5,3c,3c,46,80,0,0,0
Typ=183 Len=11: 80,0,0,5,3c,3c,46,80,0,0,0
Typ=183 Len=11: 80,0,0,5,3c,3c,46,80,0,0,0

日期：0X80000005-0X80000000=5

小時：60－60＝0
分鐘：60－60＝0
秒鐘：70－60＝10
秒鐘小數部分：0X80000000-0X80000000=0

INTERVAL DAY TO SECOND型別可以用來儲存單位為天和秒的時間間隔。下面這條語句建立一個名為promotions的表，用來儲存促銷資訊。promotions表包含了一個INTERVAL DAY TO SECOND型別的列duration，該列用來記錄促銷有效的時間間隔：
CREATE TABLE promotions (
promotion_id INTEGER CONSTRAINT promotions_pk PRIMARY KEY,
name VARCHAR2(30) NOT NULL,
duration INTERVAL DAY(3) TO SECOND (4)
);
注意此處指定了duration列中天的精度為3，秒的小數部分精度為4。這就是說可以為該列的天儲存3位數字，而為該列的秒最多可以在小數點右邊儲存4位數字。
要向資料庫提供一個INTERVAL DAY TO SECOND字面值，可以使用下面的簡化語法：
INTERVAL '[+|-][ d] [ h[: m[: s]]]' [DAY[( days_precision)]])
[TO HOUR | MINUTE | SECOND[( seconds_precision)]]
其中
● + 或 - 是一個可選的指示符，用來說明時間間隔是正數還是負數(預設為正數)。
● d 是時間間隔的天數。
● h 是一個可選引數，表示時間間隔的小時數。如果指定了天和小時，必須在INTERVAL子句中包含TO HOUR。
● h 是一個可選引數，表示時間間隔的分鐘數。如果指定了天和分，必須在INTERVAL子句中包含TO MINUTES。
● s 是一個可選引數，表示時間間隔的秒數。如果指定了天和秒，必須在INTERVAL子句中包含TO SECOND。
● days_precision是一個可選引數，用來說明天數的精度(預設值為2)。
● seconds_precision是一個可選引數，用來說明秒的精度(預設值為6)。
表5-12給出了幾個INTERVAL DAY TO SECOND型別的時間間隔字面量的例子。
表5-12 時間間隔字面量的例子
時間間隔字面量說明
INTERVAL ‘3’ DAY 時間間隔為3天
INTERVAL ‘2’ HOUR 時間間隔為2小時
INTERVAL ‘25’ MINUTE 時間間隔為25分鐘
INTERVAL ‘45’ SECOND 時間間隔為45秒
INTERVAL ‘3 2’ DAY TO HOUR 時間間隔為3天零2小時
INTERVAL ‘3 2:25’ DAY TO MINUTE 時間間隔為3天零2小時25分
INTERVAL ‘3 2:25:45’ DAY TO SECOND 時間間隔為3天零2小時25分45秒
INTERVAL ‘123 2:25:45.12’ DAY(3)
TO SECOND(2) 時間間隔為123天零2小時25分45.12秒；天的精度是3位數字，秒的小數部分的精度是2位數字
INTERVAL ‘3 2:00:45’ DAY TO SECOND 時間間隔為3天2小時0分45秒
INTERVAL ‘-3 2:25:45’ DAY TO SECOND 時間間隔為負數，值為3天零2小時25分45秒
INTERVAL ‘1234 2:25:45’ DAY(3)
TO SECOND 時間間隔無效，因為天的位數超過了指定的精度3
INTERVAL ‘123 2:25:45.123’ DAY
TO SECOND(2) 時間間隔無效，因為秒的小數部分的位數超過了指定的精度2
SELECT INTERVAL '300' MONTH,
INTERVAL '3' MONTH,
INTERVAL '0-1' YEAR TO MONTH,
INTERVAL '54-2' YEAR TO MONTH,
INTERVAL '11:12:10.1234567' HOUR TO SECOND,
INTERVAL '6' DAY,
INTERVAL '120' HOUR(3),
INTERVAL '123 2 :25 :45.123' DAY(3) TO SECOND(3)
FROM dual;

下面這個INSERT語句向promotions表新增一行記錄：
INSERT INTO promotions (promotion_id, name, duration)
VALUES (1, '10% off Z Files', INTERVAL '3' DAY);
INSERT INTO promotions (promotion_id, name, duration)
VALUES (2, '20% off Pop 3', INTERVAL '2' HOUR);
INSERT INTO promotions (promotion_id, name, duration)
VALUES (3, '30% off Modern Science', INTERVAL '25' MINUTE);
INSERT INTO promotions (promotion_id, name, duration)
VALUES (4, '20% off Tank War', INTERVAL '45' SECOND);
INSERT INTO promotions (promotion_id, name, duration)
VALUES (5, '10% off Chemistry', INTERVAL '3 2:25' DAY TO MINUTE);
INSERT INTO promotions (promotion_id, name, duration)
VALUES (6, '20% off Creative Yell', INTERVAL '3 2:25:45' DAY TO SECOND);
INSERT INTO promotions (promotion_id, name, duration)
VALUES (7, '15% off My Front Line',
INTERVAL '123 2:25:45.12' DAY(3) TO SECOND(2));
下面這個查詢對promotions表進行檢索，注意duration列值的格式化：
SELECT *
FROM promotions;
PROMOTION_ID NAME DURATION
------------ ------------------------------ ------------------
1 10% off Z Files +003 00:00:00.0000
2 20% off Pop 3 +000 02:00:00.0000
3 30% off Modern Science +000 00:25:00.0000
4 20% off Tank War +000 00:00:45.0000
5 10% off Chemistry +003 02:25:00.0000
6 20% off Creative Yell +003 02:25:45.0000
7 15% off My Front Line +123 02:25:45.1200

四、 Datetime和Interval演算法
PL/SQL允許我們建立日期時間和間隔表示式。下面列表顯示了可以在表示式中使用的操作符：
運算元 1 操作符運算元 2 返回型別
日期時間 + 時間間隔日期時間
日期時間 - 時間間隔日期時間
時間間隔 + 日期時間日期時間
日期時間 - 日期時間時間間隔
時間間隔 + 時間間隔時間間隔
時間間隔 - 時間間隔時間間隔
時間間隔 * 數字時間間隔
數字 * 時間間隔時間間隔
時間間隔 / 數字時間間隔
我們還可以使用各種函式來操作日期時間型別，如EXTRACT等。
8、使用日期和時間子型別來避免"切斷"問題
對於某些日期和時間型別的預設精度都要小於它們對應的最大精度。例如，DAY TO SECOND的預設精度是DAY(2) TO SECOND(6)，而最大精度是DAY(9) TO SECOND(9)。為了避免在使用這些型別進行賦值和傳遞引數時丟失精度，我們可以宣告它們的子型別，這些型別都採用了最大的精度：
TIMESTAMP_UNCONSTRAINED

TIMESTAMP_TZ_UNCONSTRAINED

TIMESTAMP_LTZ_UNCONSTRAINED

YMINTERVAL_UNCONSTRAINED

DSINTERVAL_UNCONSTRAINED
1.4 LOB型別

LOB(large object)資料型別BFILE、BLOB、CLOB和NCLOB可以最大儲存4G的無結構資料(例如：文字、圖形、視訊剪輯和音訊等)塊。並且，它們允許高效地隨機地分段訪問資料。
LOB型別和LONG、LONG RAW型別相比有幾個不同的地方。比如，LOB(除了NCOLB)可以作為物件型別的一個屬性，但LONG型別不可以。LOB的最大值是4G，而LONG只有2G。LOB支援隨機訪問資料，但LONG只支援順序訪問。
LOB型別中可以儲存了LOB定位器，它能夠指向存放於外部檔案中的"大物件"，in-line (inside the row)或out-of-line (outside the row)的形式。BLOB、CLOB、NCLOB或BFILE型別的資料庫欄位儲存了定位器。其中BLOB、CLOB和NCLOB的資料存在資料庫中，in-line (inside the row)或out-of-line (outside the row)的形式，而BFILE資料存在資料庫之外的作業系統檔案中。
PL/SQL是通過定位器來操作LOB的。例如，當我們查詢出一個BLOB值，只有定位器被返回。如果在事務中得到定位器，LOB定位器就會包含事務的ID號，這樣我們就不能在另外一個事務中更新LOB內容了。同樣，我們也不能在一個會話中操作另外一個會話中的定位器。
從9i開始，我們也可以把CLOB型別轉成CHAR和VARCHAR2型別或是把BLOB轉成RAW，反之亦然，這樣，我們就能在大多數SQL和PL/SQL語句和函式中使用LOB型別了。要讀、寫和分段的操作LOB，我們可以使用Oracle系統包DBMS_LOB。
oracle中支援4種型別的LOB（large object）
CLOB：字元LOB。這種型別用於儲存大量的文字資訊，如xml或者只是純文字。這個資料型別需要進行字符集轉換，也就是說，在獲取時，這個欄位中的字元會從資料庫的字符集轉換為客戶的字符集，而在修改時會總客戶的字符集轉換為資料庫的字符集。
NCLOB：這是另一種型別的LOB。儲存在這一列中的資料所採用的字符集是資料庫的國家字符集，而不是資料庫的預設字符集。
BLOB：二進位制LOB。這種型別用於儲存大量的二進位制資訊，如處理文件、影像和你能想象的任何其他資料。它不會執行字符集轉換。應用向BLOB寫入什麼位和位元組，BLOB就會返回什麼位和位元組。
BFILE：二進位制檔案LOB。這與其說是一個資料庫儲存實體，不如說是一個指標。帶BFILE列的資料庫儲存的只是作業系統的某個檔案的一個指標。這個檔案在資料庫之外維護，根本不是資料庫的一部分。BFILE提供了檔案內容的只讀訪問。

因此可以將LOB分為內部LOB（CLOB、NCLOB、BLOB），外部LOB（BFILE內容儲存在資料庫之外，BFILE列只是一個指向作業系統中的檔案的指標）
內部LOB欄位在資料庫內部並不是在表中記錄資料（也可以設定為在表中記錄（in row（最多4000位元組，超過就會移出）））。LOB欄位內部儲存分為3個部分，如下圖所示：

table T的Txt欄位是CLOB型別的，LOB內部分為LOBINDEX、LOBSEGMENT，表T的Txt欄位儲存的為指向LOBINDEX的地址，LOBINDEX欄位儲存的為LOBSEGMENT的索引。所以查詢的順序是Txt->LOBINDEX->LOBSEGMENT。
如果是行內（IN ROW）儲存，那就和varchar2沒有區別了，行記憶體儲要比行外儲存塊。這裡只是淺顯的瞭解了LOB欄位的內部儲存，深入的引數不記錄了

一、 BFILE
BFILE資料型別用於儲存二進位制物件，它將儲存的內容放到作業系統的檔案中，而不是資料庫內。每個BFILE變數都儲存一個檔案定位器，它指向伺服器上的一個大的二進位制檔案。定位器包含目錄別名，該別名給出了檔案全路徑。
BFILE型別是隻讀的，而且它的大小要依賴於系統，不能超過4G。我們的DBA要確保給定的BFILE存在且Oracle有讀取它的許可權。
BFILE並不參與事務，是不可恢復，不能被複制。能夠被開啟的BFILE最大數是由Oracle初始化引數SESSION_MAX_OPEN_FILES決定的。
第一步:建立一個目錄物件用來儲存資料
CREATE OR REPLACE DIRECTORY record_bfile AS '/home/oracle/record_bfile/bfile.txt'
第二步:建立一個bfile表
CREATE TABLE articles
(
author_id NUMBER(4),
author_name VARCHAR2 (14) ,
release_date date,
article_content BFILE
);
第三步:插入記錄
INSERT INTO articles
VALUES(1111,'SMITH',to_date('2010-07-11','YYYY-MM-DD'),bfilename('record_bfile','bfile.txt'));

You want to access employee details contained in flat files as part of the EMPLOYEE table. You plan
to add a new column to the EMPLOYEE table to achieve this.
Which data type would you use for the new column?
A.CLOB
B.BLOB
C.BFILE
D.LONG RAW
Answer: C
題目解答：表的資料放到file中，要麼外部表，要麼bfile列儲存本題是列只能C
BFILE的用法
(1)、create or replace directory
BFILE_TEST
as
'/oracle/oradata/bfiles';
(2)、grant read on directory BFILE_TEST to SCOTT;
(3)、host ls -l /oracle/oradata/bfiles/1.TXT
(4)、connect SCOTT/TIGER
create table BFILES (ID number, TEXT bfile );
(5)、insert into BFILES values ( 1,
bfilename ( 'BFILE_TEST', '1.TXT' ) );

二、 BLOB、CLOB和NCLOB
BLOB資料型別可以在資料庫中存放不超過4G的大型二進位制物件；CLOB和NCLOB可以在資料庫中分別儲存大塊CHAR型別和NCHAR型別的字元資料，都支援定寬和變寬字符集。同BFILE一樣，這三個型別也都儲存定位器，指向各自型別的一個大資料塊。資料大小都不能超過4G。BLOB、CLOB和NCLOB都可以在事務中使用，能夠被恢復和複製。DBMS_LOB包可以對它們更改過的內容進行提交或回滾操作。BLOB、CLOB和NCLOB的定位器都可以跨事務使用，但不能跨會話使用。
1.4.2 clob和字串
Oracle中CLOB和BLOB欄位雖說在開發中滿足了存放超大內容的要求，但是在一些簡單使用中確頻頻帶來麻煩。CLOB中存放的是指標，並不能直接取到實際值。而SQLServer中的text欄位就很方便，可以直接拿來與需要的字串比對，象什麼等於呀小於呀Like呀不在話下。可是換成Oracle就麻煩死了，要開闢一個快取，把內容一段段讀取出來後轉換，難道寫個where條件都這麼複雜？經過多方尋求資料，終於發現一個方便簡單的方法：利用dbms_lob 包中的方法（放心，內建的）instr和substr 。具體的介紹如下：

instr函式與substr函式

instr函式用於從指定的位置開始，從大型物件中查詢第N個與模式匹配的字串。
用於查詢內部大物件中的字串的instr函式語法如下：

1: dbms_lob.instr(
2: lob_loc in clob character set any_cs,
3: pattern in varchar2 character set lob_loc%charset,
4: offset in integer:=1,
5: nth in integer := 1)
6: return integer;
7:

lob_loc為內部大物件的定位器
pattern是要匹配的模式
offset是要搜尋匹配檔案的開始位置
nth是要進行的第N次匹配
substr函式
substr函式用於從大物件中抽取指定數碼的位元組。當我們只需要大物件的一部分時，通常使用這個函式。
操作內部大物件的substr函式語法如下：

其中各個引數的含義如下：
lob_loc是substr函式要操作的大型物件定位器
amount是要從大型物件中抽取的位元組數
offset是指從大型物件的什麼位置開始抽取資料。

如果從大型物件中抽取資料成功，則這個函式返回一個 raw 值。如果有一下情況，則返回null：
1 任何輸入引數尾null
2 amount < 1
3 amount > 32767
4 offset < 1
5 offset > LOBMAXSIZE
1: dbms_lob.instr(
2: lob_loc in blob,
3: pattern in raw,
4: offset in integer := 1;
5: nth in integer := 1)
6: return integer;
7: dbms_lob.substr(
8: lob_loc in blob,
9: amount in integer := 32767,
10: offset in integer := 1)
11: return raw;
12: dbms_lob.substr(
13: lob_loc in clob character set any_cs,
14: amount in integer := 32767,
15: offset in integer := 1)
16: return varchar2 character set lob_loc%charset;

照下面示例，很容易看懂：
DECLARE
source_lob CLOB;
pattern VARCHAR2(6) := 'Oracle';
start_location INTEGER := 1;
nth_occurrence INTEGER := 1;
position INTEGER;
buffer VARCHAR2(100);
BEGIN
SELECT clob_locator INTO source_lob FROM mylobs WHERE lob_index = 4;
position := dbms_lob.instr(source_lob,
pattern,
start_location,
nth_occurrence);
dbms_output.put_line('The first occurrence starts at position:' ||
position);

nth_occurrence := 2;
SELECT clob_locator INTO source_lob FROM mylobs WHERE lob_index = 4;
position := dbms_lob.instr(source_lob,
pattern,
start_location,
nth_occurrence);
dbms_output.put_line('The first occurrence starts at position:' ||
position);

SELECT clob_locator INTO source_lob FROM mylobs WHERE lob_index = 5;
buffer := dbms_lob.substr(source_lob, 9, start_location);
dbms_output.put_line('The substring extracted is: ' || buffer);
END;

輸出結果為：
The first occurrence starts at position:8
The first occurrence starts at position:24
The substring extracted is: Oracle 9i

drop table t;
create table t(x int, y clob);
insert into t values(1, rpad('*', 5000, '*'));

往oracle函CLOB、LONG欄位型別的資料表插入值超過4000位元組時，在執行INSERT　INTO提示：ORA-01704:文字字串過長錯誤，這種情況下，如果想用SQL實現的話，可以通過ORACLE儲存過程實現。

--處理方法一：用EXECUTE IMMEDIATE 實現

--ASQL：帶引數的INSERT　INTO　語句
--ALongVar1 : 引數值，大於4000位元組
CREATEORREPLACEPROCEDUREEXEC_SQL1(ASQL varchar2, ALongVar1 clob)
AS
BEGIN
EXECUTEIMMEDIATE ASQL USING ALongValue;
ENDEXEC_SQL1;

--處理方法二：用Dbms_sql介面實現

--ASQL：帶引數的INSERT　INTO　語句
--ALongVar1 : 引數值，大於4000位元組
CREATEORREPLACEPROCEDUREEXEC_SQL(ASQL clob, ALongValue clob)
AS
The_c1 Integer;
The_result Integer;--dml_sql_result
BEGIN
The_C1 :=Dbms_sql.open_cursor;
Dbms_sql.parse(the_C1, ASQL, DBMS_SQL.NATIVE);
DBMS_SQL.BIND_VARIABLE
(The_c1, ':LongValue', ALongValue);
The_result:=Dbms_sql.execute(The_c1);
Dbms_sql.close_cursor(The_C1);
ENDEXEC_SQL;

1.4.3 oracle中可以用多種方法來檢索或操作lob資料
在oracle中，有4個大物件（lobs）型別可用，分別是blob,clob,bfile,nclob。
下面是對lob資料型別的簡單介紹。
blob:二進位制lob，為二進位制資料，最長可達4GB，存貯在資料庫中。
clob:字元lob,字元資料,最長可以達到4GB,存貯在資料庫中。

bfile:二進位制檔案;存貯在資料庫之外的只讀型二進位制資料，最大長度由作業系統限制。
nclob：支援對位元組字符集合(nultibyte characterset)的一個clob列。
對於如何檢索和操作這些lob資料一直是oracle資料庫開發者經常碰到的問題。下面我將在oracle對lob資料處理的一些方法和技巧，介紹給讀者，希望能夠對讀者以後的開發有所幫助。

oracle中可以用多種方法來檢索或操作lob資料。通常的處理方法是通過dbms_lob包。
其他的方法包括使用api(application programminginterfaces)應用程式介面和oci（oracle call interface）oracle呼叫介面程式。
一、在oracle開發環境中我們可以用dbms_lob包來處理！dbms_lob包功能強大，簡單應用。既可以用來讀取內部的lob物件，也可以用來處理bfile物件。但處理兩者之間，還有一點差別。處理內部lob物件（blob,clob）時，可以進行讀和寫，但處理外部lob物件bfile時，只能進行讀操作，寫的操作可以用pl/sql處理。另外用sql也可以處理lob，但要注意sql僅可以處理整個lob，不能操作lob的資料片。

在dbms_lob包中內建了read(),append,write()，erase()，copy(),getlength()，substr()等函式，可以很方便地操作lob物件。這裡不做深入討論，讀者可以參看相關的書籍。

對於pl/sql，下面介紹一種技巧，用動態的pl/sql語句處理clob物件來傳替表名！
example 1.
動態PL/SQL,對CLOB欄位操作可傳遞表名table_name，表的唯一標誌欄位名field_id，clob欄位名field_name記錄號v_id，開始處理字元的位置v_pos，傳入的字串變數v_clob

修改CLOB的PL/SQL過程：updateclob
create or replace procedure updateclob(
table_name in varchar2,
field_id in varchar2,
field_name in varchar2,v_id in number,
v_pos in number,
v_clob in varchar2)
is
lobloc clob;
c_clob varchar2(32767);
amt binary_integer;
pos binary_integer;
query_str varchar2(1000);
begin
pos:=v_pos*32766+1;
amt := length(v_clob);
c_clob:=v_clob;
query_str :=''select ''||field_name||''from ''||table_name||''
where ''||field_id||''= :id for update '';
--initialize buffer with data to be inserted or updated
EXECUTE IMMEDIATE query_str INTO lobloc USING v_id;
--from pos position, write 32766 varchar2 into lobloc
dbms_lob.write(lobloc, amt, pos, c_clob);
commit;
exception
when others then
rollback;
end;
l /用法說明：
在插入或修改以前，先把其它欄位插入或修改，CLOB欄位設定為空empty_clob()，
然後呼叫以上的過程插入大於2048到32766個字元。
如果需要插入大於32767個字元，編一個迴圈即可解決問題。
查詢CLOB的PL/SQL函式：getclob
create or replace function getclob(
table_name in varchar2,
field_id in varchar2,
field_name in varchar2,
v_id in number,
v_pos in number) return varchar2
is
lobloc clob;
buffer varchar2(32767);
amount number := 2000;
offset number := 1;
query_str varchar2(1000);
begin
query_str :=''select ''||field_name||'' from ''||table_name||''
where ''||field_id||''= :id '';
--initialize buffer with data to be found
EXECUTE IMMEDIATE query_str INTO lobloc USING v_id;
offset:=offset+(v_pos-1)*2000;
--read 2000 varchar2 from the buffer
dbms_lob.read(lobloc,amount,offset,buffer);
return buffer;
exception
when no_data_found then
return buffer;
end;
l 用法說明：
用select getclob(table_name,field_id,field_name,v_id,v_pos) as
partstr from dual;
可以從CLOB欄位中取2000個字元到partstr中，
編一個迴圈可以把partstr組合成dbms_lob.getlength(field_name)長度的目標字串。
二、對於在其他不同的開發環境，例如vc,vb,pb,java等環境下對lob的處理，處理方法不盡相同，在這裡將簡要舉幾個例子來說明不在oracle開發環境下對lob的處理。

（一）在pb中的處理
exampler 2.
string ls_path,ls_filename,ls_jhdh
long ll_num,ll_count,rtn
blob ole_blob
ll_num=dw_lb.getrow()
if ll_num>0 then ls_jhdh=dw_lb.object.ct_njhdh[ll_num]
select count(*) into :ll_count from sj_jh_jhfjb where
ct_jhdlxbh=''1'' and ct_jhdh=:ls_jhdh and ct_jdlxbh=:is_jdlx;
if ll_count>0 then
rtn=messagebox("提示","是否要修改此附件",question!,yesno!,1)
if rtn=1 then
SELECTBLOB ct_jhfjnr INTO le_blob from sj_jh_jhfjb where
ct_jhdlxbh=''1'' and ct_jhdh=:ls_jhdh and ct_jdlxbh=:is_jdlx;
ole_1.objectdata =ole_blob
If ole_1.activate(offsite!) <> 0 Then
Messagebox("OLE Activate","不能啟用")
Return -1
end If
end if
else
messagebox("提示","沒有附件")
end if
end if
（二）在vb中的處理
在vb中處理大物件，一般可以用OO4O(oracle objects for
ole)來處理大物件。這裡介紹一種不用0040處理大物件blob的方法。
下面這段程式可以將一個檔案（文字檔案，doc檔案，圖象檔案等）儲存到資料庫中，並可以將其從資料庫讀出
需要兩個commandbutton
cmd1 名稱 cmdsave caption 儲存
cmd2 名稱 cmdread caption 讀取
一個cmddialog控制元件
同時需要建立一張表t_demo(欄位id 型別 number,;欄位text 型別 blob;)
exmple 3.
Option Explicit
Dim rn As ADODB.Connection
Public Function CreateDataSource(DataSource As String, UserID
As String, Password As String) As Boolean
On Error GoTo DbConErr:
Set rn = New ADODB.Connection
With rn
.ConnectionString = "Provider=OraOledb.Oracle.1;" & _
"password=" & Password & ";" & _
"User ID =" & UserID & ";" & _
"Data Source=" & DataSource & ";" & _
"Locale Identifier=2052"
.Open
End With
CreateDataSource = True
Exit Function
DbConErr:
CreateDataSource = False
End Function

Private Sub cmdRead_Click()
Dim rs As New ADODB.Recordset
rs.ActiveConnection = rn
rs.LockType = adLockOptimistic
rs.CursorLocation = adUseClient
rs.Source = "select * from t_demo"
rs.Open
ComDlgDir.DialogTitle = "儲存檔案"
ComDlgDir.Filter = "*.*"
ComDlgDir.ShowSave
Call BlobToFile(rs.Fields("text"), ComDlgDir.filename)
Set rs = Nothing
Exit Sub
Set rs = Nothing
End Sub

Private Sub cmdsave_Click()
Dim rs As New ADODB.Recordset
rs.ActiveConnection = rn
rs.LockType = adLockOptimistic
rs.CursorLocation = adUseClient
rs.Source = "select * from t_demo"
rs.Open
rs.AddNew
ComDlgDir.DialogTitle = "選取檔案"
ComDlgDir.ShowOpen
rs.Fields("id").Value = 1
If ComDlgDir.filename <> "" Then
Call FileToBlob(rs.Fields("text"), ComDlgDir.filename)
rs.Update
End If
Set rs = Nothing
Exit Sub
Set rs = Nothing
End Sub

Private Sub Form_Load()
If Not CreateDataSource("sid", "systemp", "manager") Then
MsgBox "Connection failure!"
End If
End Sub

fld As ADODB.Field, filename As String, Optional ChunkSize As
Long = 8192)
Dim fnum As Integer, bytesleft As Long, bytes As Long
Dim tmp() As Byte
If (fld.Attributes And adFldLong) = 0 Then
Err.Raise 1001, , "field doesn''t support the GetChunk method."

End If
If Dir$(filename) = "" Then Err.Raise 53, , "File not found"
fnum = FreeFile
Open filename For Binary As fnum
bytesleft = LOF(fnum)
Do While bytesleft
bytes = bytesleft
If bytes > ChunkSize Then bytes = ChunkSize
ReDim tmp(1 To bytes) As Byte
Get fnum, , tmp
fld.AppendChunk tmp
bytesleft = bytesleft - bytes
Loop
Close #fnum
End Sub

Sub BlobToFile(fld As ADODB.Field, filename A

1.5 使用者自定義子型別
每個PL/SQL基型別都有對應的值集合和操作符集合。子類同樣會指定同其基型別相同的值集合和操作符集合的子集作為它自己的值集合和操作符集合。所以說子類並不是一個新型別，它只是在基類的基礎上新增了一個可選的約束。
子類可以增加可讀性和相容性。PL/SQL在STANDARD包裡預定義了一些子型別。如下例：
SUBTYPE CHARACTER IS CHAR;
SUBTYPE INTEGER IS NUMBER(38, 0); -- allows only whole numbers
子型別CHARACTER和基型別完全一樣，所以CHARACTER是一個未作約束的子型別。但是，子型別INTEGER將基類NUMBER的值集合的子集作為自己的值集合，所以INTEGER是一個約束的子型別。
1、定義子型別
我們可以在任何PL/SQL塊、子程式或包中定義自己的子型別，語法如下：
SUBTYPE subtype_name IS base_type[(constraint)] [NOT NULL];
subtype_name就是宣告的子型別的名稱，base_type可以是任何標量型別或使用者定義型別，約束只是用於限定基型別的精度和數值範圍，或是最大長度。下面舉幾個例子：
DECLARE
SUBTYPE birthdate IS DATE NOT NULL; -- based on DATE type

SUBTYPE counter IS NATURAL; -- based on NATURAL subtype

TYPE namelist IS TABLE OF VARCHAR2(10);

SUBTYPE dutyroster IS namelist; -- based on TABLE type

TYPE timerec IS RECORD(
minutes INTEGER,
hours INTEGER
);

SUBTYPE finishtime IS timerec; -- based on RECORD type

SUBTYPE id_num IS emp.empno%TYPE; -- based on column type
我們可以使用%TYPE或%ROWTYPE來指定基型別。當%TYPE提供資料庫欄位中的資料型別時，子型別繼承欄位的大小約束(如果有的話)。但是，子型別並不能繼承其他約束，如NOT NULL。
2、使用子型別
一旦我們定義了子型別，我們就可以宣告該型別的變數、常量等。下例中，我們宣告瞭Counter型別變數，子型別的名稱代表了變數的使用目的：
DECLARE
SUBTYPE counter IS NATURAL;

ROWS counter;
下面的例子演示瞭如何約束使用者自定義子型別：
DECLARE
SUBTYPE accumulator IS NUMBER;

total accumulator(7, 2);
子型別還可以檢查數值是否越界來提高可靠性。下例中我們把子型別Numeral的範圍限制在-9到9之間。如果程式把這個範圍之外的數值賦給Numeral型別變數，那麼PL/SQL就會丟擲一個異常。
DECLARE
SUBTYPE numeral IS NUMBER(1, 0);

x_axis numeral; -- magnitude range is -9 .. 9
y_axis numeral;
BEGIN
x_axis := 10; -- raises VALUE_ERROR
...
END;
? 型別相容
一個未作約束的子型別是可以和它的基型別互動使用。例如下面的宣告，amount值可以在不用轉換的情況下直接賦給total：
DECLARE
SUBTYPE accumulator IS NUMBER;

amount NUMBER(7, 2);
total accumulator;
BEGIN
...
total := amount;
...
END;
如果基型別相同，那麼不同的子型別也是可以互動使用。例如，下面的宣告中，finished的值就可以賦給debugging：
DECLARE
SUBTYPE sentinel IS BOOLEAN;

SUBTYPE SWITCH IS BOOLEAN;

finished sentinel;
debugging SWITCH;
BEGIN
...
debugging := finished;
...
END;
不同的子型別也是有可能互動使用，只要它們的基型別屬於同一個資料型別種類。例如下面的宣告中，verb值就能賦給sentence：
DECLARE
SUBTYPE word IS CHAR(15);

SUBTYPE text IS VARCHAR2(1500);

verb word;
sentence text(150);
BEGIN
...
sentence := verb;
...
END;

一、 timestamp with local time zone型別和timestamp with time zone
timestamp with loca time zone型別語法
tmestamp[(fractional_seconds_precisions)] with local zone
timestamp with local time zone 和timesatamp with time zone的最大區別就是，前者在使用者提交時間給資料庫的時，該型別會轉換成資料庫的時區來儲存資料，即資料庫儲存的時間是資料庫本地時區，當別的使用者訪問資料庫時oracle會自動將該時間轉換成當前客戶端的時間。
例子：
1、建立表
CREATE TABLE TIMESTAMP_TEST(
TIME DATE,
TIMESTP TIMESTAMP(3),
TIMESTP_TZ TIMESTAMP(3) WITH TIME ZONE,
TIMESTP_LTZ TIMESTAMP(3) WITH LOCAL TIME ZONE)
2、新增資料
INSERT INTO TIMESTAMP_TEST VALUES(SYSDATE,SYSDATE,SYSDATE,SYSDATE);
commit;
3、查詢dbtimezone和sessiontimezone的值
select dbtimezone ,sessiontimezone from dual;
DBTIME
------
SESSIONTIMEZONE
---------------------------------------------------------------------------
+00:00
+08:00
4、檢視資料的值

SQL> SELECT * FROM TIMESTAMP_TEST;
TIME
--------------
TIMESTP
--------------------------------------------------------------
TIMESTP_TZ
--------------------------------------------------------------
TIMESTP_LTZ
--------------------------------------------------------------
02-6月 -10
02-6月 -10 11.21.10.000 上午
02-6月 -10 11.21.10.000 上午 +08:00
02-6月 -10 11.21.10.000 上午
5、修改會話的time_zone值
alter session set time_zone='+10:00';
6、檢視結果
SQL> SELECT * FROM TIMESTAMP_TEST;
TIME
--------------
TIMESTP
-----------------------------------------------------
TIMESTP_TZ
-----------------------------------------------------
TIMESTP_LTZ
-----------------------------------------------------
02-6月 -10
02-6月 -10 11.21.10.000 上午
02-6月 -10 11.21.10.000 上午 +08:00
02-6月 -10 01.21.10.000 下午
7、從上面的實驗可以看出二者的去區別，當session的時區由8變為10是，時間增加兩個小時
再向表中新增一條記錄
insert into TIMESTAMP_TEST values(
TO_TIMESTAMP_TZ('2010-12-01 23:12:56.788 -12:44', 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM'),
TO_TIMESTAMP_TZ('2010-12-01 23:12:56.788-12:44', 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM'),
TO_TIMESTAMP_TZ('2010-12-01 23:12:56.788 -12:44', 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM'),
TO_TIMESTAMP_TZ('2010-12-0123:12:56.788 -12:44', 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM'));
（tzh：時區中的小時，tzm:時區中的分）
在這裡我指定了資料新增時的時區為-12:44，查詢結果為
TIME
--------------
TIMESTP
---------------------------------------------------------------------------
TIMESTP_TZ
---------------------------------------------------------------------------
TIMESTP_LTZ
---------------------------------------------------------------------------
01-12月-10
01-12月-10 11.12.56.788 下午
01-12月-10 11.12.56.788 下午 -12:44
02-12月-10 09.56.56.788 下午

TIME
--------------
TIMESTP
---------------------------------------------------------------------------
TIMESTP_TZ
---------------------------------------------------------------------------
TIMESTP_LTZ
---------------------------------------------------------------------------
02-6月 -10
02-6月 -10 11.21.10.000 上午
02-6月 -10 11.21.10.000 上午 +08:00
02-6月 -10 01.21.10.000 下午
由於當前使用者的時區是+10：00，新增資料時的指定時區死-12:44，二者時間相差22小時44分
二、毫秒
關於毫秒：
Oracle 毫秒的儲存必須欄位型別為 timestamp(6) –數字表示儲存的毫秒位數
--當前毫秒級時間
select to_char(current_timestamp,'yyyy-mm-dd hh24:mi:ss.ff6') from dual;
--字串轉為 timestamp型別
select to_timestamp('2012-02-03 10:29:46.453234','yyyy-mm-dd hh24:mi:ss.ff6') from dual;
--timestamp轉為字元型
select to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss.ff6') from dual;
PS: ff後面的數字表示獲得的毫秒位數，預設是6；一般ff3 獲得三位毫秒數。

如果你想把DATE型別轉換成TIMESTAMP型別，就使用CAST函式。
select cast(sysdate as timestamp) from dual;
但是值得注意的是:在轉換後的時間段尾部有了一段“.000000”。這是因為從date轉換過來的時候，沒有小數秒的資訊，預設為0。而且顯示格式是按照引數NLS_TIMESTAMP_FORMAT定的預設格式顯示。當你把一個表中date型別欄位的資料移到另一個表的timestamp型別欄位中去的時候，可以直接寫INSERT SELECT語句，oracle會自動為你做轉換的。

注意： to_char函式支援date和timestamp，但是trunc卻不支援TIMESTAMP資料型別。這已經清楚表明了在當兩個時間的差別極度重要的情況下，使用TIMESTAMP資料型別要比DATE資料型別更確切。

還值得一提的是：毫秒的顯示精度是6位，不過有效位是3位，即最大值達到999，滿1000ms就進為1s。當然你想儲存6位毫秒也是有辦法的：
insert 值指定六位：to_timestamp('2012-02-03 10:29:46.453234','yyyy-mm-dd hh24:mi:ss.ff6')

1.6 資料型別轉換
有時我們需要把一個值從一個型別轉換成另一個型別。例如，如果我們想檢查一個ROWID，我們就必須把它轉成一個字串。PL/SQL支援顯式和隱式(自動的)資料型別轉換。
1、顯式轉換
如果要進行型別間的轉換，我們可以使用內建函式。例如將一個CHAR型別的值轉換成一個DATE或NUMBER型別的值，我們就可以使用函式TO_DATE和TO_NUMBER。反過來，如果從DATE或NUMBER轉成CHAR的話，可以使用TO_CHAR函式。
2、隱式轉換
PL/SQL有時會幫助我們進行隱式地資料型別轉換。下面的例子中，CHAR型變數start_time和finish_time都有一個代表從午夜開始後所經過的秒數的值。這兩個變數的差值要放到elapsed_time中去。所以，PL/SQL會自動地把CHAR型別轉換成NUMBER型別。
DECLARE
start_time CHAR(5);
finish_time CHAR(5);
elapsed_time NUMBER(5);
BEGIN
/* Get system time as seconds past midnight. */
SELECT TO_CHAR(SYSDATE, 'SSSSS')
INTO start_time
FROM SYS.DUAL;

-- do something
/* Get system time again. */
SELECT TO_CHAR(SYSDATE, 'SSSSS')
INTO finish_time
FROM SYS.DUAL;

/* Compute elapsed time in seconds. */
elapsed_time := finish_time - start_time;

INSERT INTO results
VALUES (elapsed_time, ...);
END;
另外，在把查詢的結果賦給變數之前，如果有必要的話PL/SQL也會把原值型別轉成對應的變數型別，典型的例子就是把DATE型別放入VARCHAR2變數中。
同樣，在把變數值賦給資料庫欄位時，PL/SQL在必要的情況下，也會進行資料型別轉換。如果PL/SQL無法確定採用哪種轉換形式，就會發生變異錯誤。這種情況下，我們就必須使用型別轉換函式。下表是PL/SQL能夠做的隱式轉換。
BIN_INT BLOB CHAR CLOB DATE LONG NUMBER PLS_INT RAW UROWID VARCHAR2
BIN_INT X X X X X
BLOB X
CHAR X X X X X X X X X X
CLOB X X
DATE X X X
LONG X X X
NUMBER X X X X X
PLS_INT X X X X X
RAW X X X X
UROWID X X
VARCHAR2 X X X X X X X X X
5 注意
6 列表中只列出表現不同的型別，而那些表現相同的型別，如CLOB和NCLOB，CHAR和NCHAR，還有VARCHAR2和NVARCHAR2都是可以互相替換。
7 要在CLOB和NCLOB之間轉換，我們必須使用轉換函式TO_CLOB和TO_NCLOB。
8 TIMESTAMP，TIMESTAMP WITH TIME ZONE，TIMESTAMP WITH LOCAL TIME ZONE，INTERVAL DAY TO SECOND和INTERVAL YEAR TO MONTH都可以作為DATE型別用同樣的轉換規則進行轉換。但是由於它們的內部表現形式不同，這些型別不能相互轉換。
3、顯式轉換 VS 隱式轉換
通常，依賴隱式的資料轉換不是一個好習慣，因為這樣做會引起效能問題並可能隨著軟體版本變更而影響到後續的版本。隱式轉換是與環境相關的，它的轉換結果有時是無法預知的。所以，最好還是使用型別轉換函式。這樣的話，程式就有更好的可讀性和可維護性。
4、DATE值
當把一個查詢出來的DATE型別值放到CHAR或VARCHAR2變數中時，PL/SQL必須將內建的二進位制值轉成字元值，它就會呼叫TO_CHAR將日期按照預設的日期格式轉成字元值。同樣，把一個CHAR或VARCHAR2插入到DATE型別的欄位中，也是需要進行型別轉換的，PL/SQL會呼叫TO_DATE將字元按照預設的日期格式轉成日期型別的值。如果對轉換有特殊要求，我們就得顯式地指明轉換格式。
5、RAW和LONG RAW值
在把查詢出來的RAW或LONG RAW型別值賦給CHAR型別或VARCHAR2型別變數的時候，PL/SQL必須把內建的二進位制值轉成字元值。這種情況下，PL/SQL會把每個RAW或LONG RAW型別的二進位制位元組轉成等值的一對十六進位制字元值。比如PL/SQL會把二進位制11111111轉換成字元"FF"。函式RAWTOHEX也具有這樣的功能。把CHAR或VARCHAR2型別值插入RAW或LONG RAW欄位時也是需要型別轉換的。變數中的每對字元要轉成等值二進位制位元組，如果無法進行正常的轉換，PL/SQL就會丟擲異常。

除Oracle資料型別之外，Oracle9i/10g資料庫中表的列可以用ANSI、DB2和SQL/DS 數
據型別定義。不管用何種方法，Oracle伺服器內部將轉換這些資料型別為Oracle資料型別。
在某些情況下，Oracle伺服器使用一種資料型別的資料，而在另外一種情況下我們希望
使用一種不同資料型別的資料，如果這種情況發生，Oracle伺服器自動轉換資料為期望的數
據型別。這種資料型別的轉換可以被Oracle伺服器隱式進行，或由使用者顯式進行。
1.6.1 隱式資料型別轉換
隱式資料型別轉換工作依照下面的規則進行。
顯式資料型別轉換用轉換函式進行。轉換函式轉換從一種資料型別轉換值到另一種資料
型別。通常，函式名的構成遵循資料型別到資料型別的約定，第一個資料型別是輸入數
據型別；後一個資料型別是輸出資料型別。
儘管隱式資料型別轉換是可用的，但建議你做顯式資料型別轉換以確保SQL語句的可靠
性。
對於直接賦值，ORACLE SQL可以進行以下的隱式轉換：
從到
VARCHAR2 or CHAR NUMBER
VARCHAR2 or CHAR DATE
NUMBER VARCHAR2
DATE VARCHAR2

對於表示式賦值，ORACLE SQL可以進行以下的隱式轉換：

從到
VARCHAR2 or CHAR NUMBER
VARCHAR2 or CHAR DATE

1.6.2 顯式資料型別轉換
SQL 提供三種函式來從一種資料型別轉換值到另一種：TO_CHAR、TO_NUMBER、 TO_DATE。
TO_CHAR() 函式又可以分三小類，分別是
轉換字元->字元TO_CHAR(c)：將nchar,nvarchar2,clob,nclob型別轉換為char型別；
例如：
Select To_Char('AABBCC') From Dual;

轉換時間->字元TO_CHAR(d[,fmt])：將指定的時間(data, timestamp,
timestamp with time zone)按照指定格式轉換為varchar2型別；
必須加單引號，並且區分大小寫，能夠包含任一有效的日期格式元素，有一個fm元素
用來刪除填補的空，或者前導零，用一個逗號與日期值分開。
例如：
Select To_Char(Sysdate, 'yyyy-mm-dd hh24:mi:ss') From Dual;
轉換數值->字元TO_CHAR(n[,fmt])：將指定數值n按照指定格式fmt 轉換為
varchar2型別並返回；
例如：
Select To_Char(-100, 'L99G999D99MI') From Dual;

TO_DATE(c[,fmt[,nls]]) 將char,nchar,varchar2,nvarchar2轉換為日期型別，如果fmt引數
不為空，則按照fmt中指定格式進行轉換。注意這裡的fmt引數。如果ftm為'J'則表示按照
公元制(Julian day)轉換，c則必須為大於0並小於5373484的正整數。
例如：
Select To_Date(2454336, 'J') From Dual;

Select To_Date('2007-8-23 23:25:00', 'yyyy-mm-dd hh24:mi:ss')
From Dual;

TO_NUMBER(c[,fmt[,nls]]) 將char,nchar,varchar2,nvarchar2型字串按照fmt中指定格式
轉換為數值型別並返回。
例如：
Select To_Number('-100.00', '9G999D99') From Dual;
1.7 ORA-01704:文字字串過長 oracle CLOB 超過4000位元組

往oracle函CLOB、LONG欄位型別的資料表插入值超過4000位元組時，在執行INSERT　INTO提示：ORA-01704:文字字串過長錯誤，這種情況下，如果想用SQL實現的話，可以通過ORACLE儲存過程實現。
往oracle函CLOB、LONG欄位型別的資料表插入值超過4000位元組時，在執行INSERT　INTO提示：ORA-01704:文字字串過長錯誤，這種情況下，如果想用SQL實現的話，可以通過ORACLE儲存過程實現。

-- 處理方法一：用EXECUTE IMMEDIATE 實現

-- ASQL：帶引數的INSERT　INTO　語句
-- ALongVar1 : 引數值，大於4000位元組
CREATE OR REPLACE PROCEDURE EXEC_SQL1(ASQL VARCHAR2,
ALongVar1 CLOB) AS
BEGIN
EXECUTE IMMEDIATE ASQL
USING ALongVar1;
END EXEC_SQL1;

-- 處理方法二：用Dbms_sql介面實現

-- ASQL：帶引數的INSERT　INTO　語句
-- ALongVar1 : 引數值，大於4000位元組
CREATE OR REPLACE PROCEDURE EXEC_SQL(ASQL CLOB,
ALongValue CLOB) AS
The_c1 INTEGER;
The_result INTEGER; -- dml_sql_result
BEGIN
The_C1 := Dbms_sql.open_cursor;
Dbms_sql.parse(the_C1, ASQL, DBMS_SQL.NATIVE);
DBMS_SQL.BIND_VARIABLE(The_c1, ' :LongValue ', ALongValue);
The_result := Dbms_sql. EXECUTE(The_c1);
Dbms_sql.close_cursor(The_C1);
END EXEC_SQL;

CREATE OR REPLACE FUNCTION sumvarc RETURN CLOB IS
v_out CLOB;
BEGIN
FOR i IN 100..4000 LOOP
v_out := v_out || i;
END LOOP;
RETURN v_out;
END;

?
第2章 pl/sql 塊的資料型別
2.1 pl/sql 資料型別
定義並使用變數
declare
v_num number(5) := 123;
v_str varchar2(20) := 'abc';
v_date date := sysdate;
c_pi constant number(5,2) not null := 3.14;

begin
--c_pi := c_pi + 1; 常量不能修改其內容
v_num := v_num + 5;
dbms_output.put_line(v_num);
dbms_output.put_line(v_str);
--dbms_output.put_line('今天是' || v_date);
dbms_output.put_line('今天是' || to_char(v_date,'yyyy"年"mm"月"dd"日"'));

end;

在編寫 pl/sql 程式時，可以定義變數和常量；在 pl/sql 程式中包括有：
1.標量型別(scalar)
2.複合型別(composite)
3.參照型別(reference)
4.lob(large object)

變數宣告分標量型變數宣告和組合變數（複合變數）宣告。

2.1.1 標量型變數
標量型變數是指其內部沒有成員的變數。

標量(scalar)——常用型別
在編寫 pl/sql 塊時，如果要使用變數，需在定義部分定義變數。pl/sql 中定義變數和常量的語法如下：
identifier [constant] datatype [not null] [:=| default expr]
identifier : 名稱
constant ：指定常量。需要指定它的初始值，且其值是不能改變的
datatype ：資料型別
not null ：指定變數值不能為 null
:= 給變數或是常量指定初始值
default 用於指定初始值
expr ：指定初始值的 pl/sql 表示式，可以是文字值、其它變數、函式等。

2.1.1.1 標量定義的案例
①　定義一個變長字串
v_ename varchar2(10);

②　定義一個小數,範圍 -9999.99~9999.99
v_sal number(6,2);

③　定義一個小數並給一個初始值為 5.4 :=是 pl/sql 的賦值號
v_sal2 number(6,2):=5.4;

④　定義一個日期型別的資料
v_hiredate date;

⑤　定義一個布林變數，不能為空，初始值為 false
v_valid boolean not null default false;

例：
age number(5) not null:=25;
pi constant number(9):=3.1415926;
name char(10) not null:=‘fan’;
today date not null:=sysdate;
sex boolean:=true;

例：宣告一個變數Student_name,其型別基於另一個變數teacher_name。
Teacher_name char(10);
Student_name teacher_name%type;

例: 宣告一個變數No,使其與表emp中EMPNO的型別一致。
no emp.empno%type;

標量(scalar)——使用標量在定義好變數後，就可以使用這些變數。這裡需要說明的是 pl/sql 塊為變數賦值不同於其它的程式語言，需要在等號前面加冒號(:=) 下面以輸入員工號，顯示僱員姓名、工資、個人所得稅(稅率為 0.03)為例。說明變數的使用，看看如何編寫。

Sql 程式碼
1. declare
2. c_tax_rate number(3,2):=0.03;
3. --使用者名稱
4. v_ename varchar2(5);
5. v_sal number(7,2);
6. v_tax_sal number(7,2);
7. begin
8. --執行
9. select ename,sal into v_ename,v_sal from emp where empno=&no;
10.--計算所得稅
11. v_tax_sal := v_sal*c_tax_rate;
12.--輸出
13. dbms_output.put_line('姓名是：'||v_ename||'工資：
'||v_sal||' 交稅：'||v_tax_sal);
14.end;
15./

2.1.2 複合變數(composite)
介紹
用於存放多個值的變數。主要包括這幾種：
1. pl/sql 記錄
2. pl/sql 表
3. 巢狀表
4. varray即陣列

複合組合變數也叫做組合變數，在複合變數中包括多個內部元件，每個元件都可以單獨存放值,因此一個複合變數可以存放多個值。
複合變數型別不是資料庫中已經存在的資料型別，因此複合變數在宣告型別之前，首先要先建立複合型別，複合型別建立後可以多次使用，以便定義多個複合變數。
複合變數像標量變數一樣也有資料型別，複合資料型別（又稱為集合）有記錄（RECORD）、表（TABLE）、巢狀表（Nested TABLE）和陣列（VARRAY）四種型別。記錄型是由一組相關但又不同的資料型別組成的邏輯單元。表是資料的集合，可將表中的資料作為一個整體進行引用和處理。關於巢狀表和陣列本書不再作過多的介紹了。
記錄就是一組相關的資料項，每一個資料項都有自己的名字和資料型別。表由列和關鍵字組成，其中通過關鍵字可成組地訪問行。一經定義，記錄和表都可重複使用。

當使用標量變數處理oracle資料時，每個標量變數只能存放單個值，也就是說只能處理單行單列的資料。如果要使用標量變數處理單行多列資料，那麼必須要定義多個變數接收不同列的資料，為了簡化單行多列資料的處理，可以使用PL\SQL記錄，為了保留並處理多行單列的資料，可以使用索引表，巢狀表和varray，為了處理多行多列的資料，應該使用PL\SQL記錄表。

組合型變數內部包含若干個成員，每個成員由標量型變數或組合型變數組成
定義組合型變數的語法如下：
type <型別名> is record
(<域名1> {<標量型資料型別> | <record型別>}[not null],
<域名2> {<標量型資料型別> | <record型別>}[not null],
…….);
<識別符號> <型別名>;

例：定義一個變數，存放一個學生的有關資訊。
declare
type student is record /*定義組合型變數型別*/
(id number(4) not null:=0,
name char(10) not null:=' ',
sex boolean not null:=true);
stu student; /*定義組合型變數*/
begin
stu.id:=1;
stu.name:='sheng';
stu.sex:=true;
end;
例：宣告一個變數，其結構與表emp的資料結構相一致。
declare
emp_value emp%rowtype;
Begin
select * into empvalue from emp
where empno=7369;
dbms_output.put_line(‘姓名：’||‘ ’||emp_value.ename);
End;
注：在執行些PL/SQL塊前，應先執行

2.1.2.1 記錄(record)型別

PL/SQL 記錄
記錄是儲存在多個欄位中的一組相關的資料項，每個欄位都有自己的名字和資料型別。例如：描述一名員工的資訊需要一些不同的屬性，如姓名、工資、僱傭日期等，若把這些屬性（欄位）放在記錄裡，就可以組成了一個邏輯單元。當把這些欄位作為一個整體宣告為記錄型時，就可以作為一個單元來處理這些不同的資料了。

記錄型別：記錄型別是把邏輯相關的資料作為一個單元儲存起來
當使用PL\SQL記錄時，應用開發人員可以自己定義記錄型別和記錄變數，也可以使用%rowtype屬性直接定義記錄變數。
含有若干個標量元件，RECORD或PL/SQL表型別稱為資訊組
其結構類似於第三代語言中的記錄
不同於資料庫表中的行
把欄位的集合當作一個整體的邏輯單元
在資料處理時易於從表中取出行資料
概念：由不同的域組成。記錄也可以看成表中的資料行，域則相當於表中的列；

定義記錄型別語法如下：
TYPE record_type IS RECORD(
Field1 type1 [NOT NULL] [DEFAULT:= exp1 ],
Field2 type2 [NOT NULL] [DEFAULT:= exp2 ],
. . . . . .
Fieldn typen [NOT NULL] [DEFAULT:= expn ]
) ;

注意：
1. 類似於高階語言中的結構體，需要注意的是，當引用 pl/sql 記錄成員時，必須要加記錄變數作為字首(記錄變數.記錄成員)。
2. 記錄是儲存在多個欄位中的一組相關的資料項，每個欄位都有自己的名字和資料型別。
3. 把欄位的集合當作一個整體的邏輯單元
4. 主要用於從表中取出查詢到的行資料
5. 處理單行多列的資料

記錄的特點：
①　每個記錄內都可以有許多欄位。
②　可以為記錄賦初值，同時可用NOT NULL來限定記錄
③　無初始值的欄位定義為NULL
④　在定義欄位時也可以使用保留字DEFAULT
⑤　可以在任意一個塊、子程式或包的宣告部分定義RECORD型別並宣告使用者自定義的記錄
⑥　可以宣告並引用巢狀的記錄，一個記錄可以是其他記錄的元件

注意：
1. 記錄型別一次不能插入多條記錄，否則會報如下錯誤

例：
DECLARE
TYPE emp_record IS RECORD(
empno NUMBER(4),
ename emp.ename%TYPE,
edate emp.hiredate%TYPE);
v_emp emp_record;
BEGIN
SELECT empno,
ename,
hiredate
INTO v_emp
FROM emp
WHERE empno = 7934;
dbms_output.put_line(v_emp.empno || ',' || v_emp.ename || ',' ||
v_emp.edate);
END;

結果：7934,MILLER,23-1月 -82

定義PL\SQL記錄

自定義記錄

DECLARE
TYPE v_type IS RECORD(
ename VARCHAR2(20),
sal NUMBER(20),
hdate DATE);
v_mytype v_type;
BEGIN
SELECT ename,
empno,
hiredate
INTO v_mytype
FROM emp
WHERE mgr IS NULL;
dbms_output.put_line(v_mytype.ename || ' ' || v_mytype.sal || ' ' || v_mytype.hdate);
END;

-- %rowtype 型記錄
DECLARE
v_mytype emp%ROWTYPE;
BEGIN
SELECT * INTO v_mytype FROM emp WHERE mgr IS NULL;
dbms_output.put_line(v_mytype.ename || ' ' || v_mytype.sal || ' ' || v_mytype.hiredate);
END;

一、 %TYPE
定義一個變數，其資料型別與已經定義的某個資料變數的型別相同，或者與資料庫表的某個列的資料型別相同，這時可以使用%TYPE
例：DECLARE
--用%TYPE 型別定義變數
v_ename emp.ename%TYPE;
-- 用 %TYPE 型別定義與表相配的欄位
declare
v_sal number(7) ;
v_ename emp.ename%type;
v_hiredate emp.hiredate%type;

begin
select sal,ename,hiredate into v_sal,v_ename,v_hiredate from emp where empno = 7369;
insert into emp(empno,ename) values(1111,'xxxx');
update emp set sal = 2000 where empno= 1010;
delete emp where empno = 1012;
commit;
dbms_output.put_line(v_sal || ',' || v_ename || ',' || v_hiredate);
end;
TYPE t_Record IS RECORD(
t_no emp.empno%TYPE,
t_name emp.ename%TYPE,
t_sal emp.sal%TYPE );
-- 宣告接收資料的變數
v_emp t_Record;

二、 %ROWTYPE
1. 返回一個記錄型別, 其資料型別和資料庫表的資料結構相一致。
2. 宣告的變數對應於資料庫表或檢視中列的集合
3. 在%ROWTYPE 之前加上資料庫表名
4. 記錄內欄位的名字和資料型別參照表或檢視中的列

%ROWTYPE的優點:
①　? 可以不必知道資料庫中列的數量和型別。
②　? 在執行期間，資料庫中列的數量和型別可能發生變化。
③　? 在SELECT語句中使用該屬性可以有效地檢索表中的行。

例：
DECLARE
v_emp emp%ROWTYPE;
BEGIN
SELECT * INTO v_emp FROM emp WHERE empno = 7521;
dbms_output.put_line(v_emp.empno || ',' || v_emp.ename);
END;

declare
--定義一個 pl/sql 記錄型別 emp_record_type,型別包含 3 個資料 name,salary,title。說白了，就是一個型別可以存放 3 個資料，主要是為了好管理
type emp_record_type is record(
name emp.ename%type,
salary emp.sal%type,
title emp.job%type
);
--定義了一個 sp_record 變數，這個變數的型別是 emp_record_type
sp_record emp_record_type;
begin
select ename, sal, job into sp_record from emp where empno =7788;
dbms_output.put_line ('員工名:' || sp_record.name);
end;

declare
type emp_record is record(
empno number(4),
ename emp.ename%type,
edate emp.hiredate%type);

v_emp emp_record;

begin
select empno,ename,hiredate into v_emp from emp where empno = 7934;
dbms_output.put_line(v_emp.empno || ',' || v_emp.ename || ',' || v_emp.edate);

end;

/*
這既是Java註釋，也是Oracle PLSQL多行註釋
*/

------------------------------------------------------

declare
v_num number(5) := 123;
v_str varchar2(20) := 'abc';
v_date date := sysdate;
c_pi constant number(5,2) not null := 3.14;

begin
--c_pi := c_pi + 1; 常量不能修改其內容
v_num := v_num + 5;
dbms_output.put_line(v_num);
dbms_output.put_line(v_str);
--dbms_output.put_line('今天是' || v_date);
dbms_output.put_line('今天是' || to_char(v_date,'yyyy"年"mm"月"dd"日"'));


end;

---------------------------------------------

declare
v_num number(4) := 20;
v_num3 number(4) := 20;
v_num4 number(4) := 20;
begin
dbms_output.put_line('v_num :' || v_num);

declare
v_num2 number(4) := 30;
v_num3 number(4) := 50;
begin
v_num4 := 100;
dbms_output.put_line('inner v_num :' || v_num); --內部可以訪問外部的變數
dbms_output.put_line('inner v_num2 :' || v_num2);
dbms_output.put_line('inner v_num3 :' || v_num3);
end;

--dbms_output.put_line('outter v_num2 :' || v_num2); 錯誤
dbms_output.put_line('outter v_num3 :' || v_num3);
dbms_output.put_line('outter v_num3 :' || v_num4);
dbms_output.put_line('the end....');

end;

-----------------------------------------------------------

declare
v_sal number(7) ;
v_ename emp.ename%type;
v_hiredate emp.hiredate%type;

begin
select sal,ename,hiredate into v_sal,v_ename,v_hiredate from emp where empno = 7369;
insert into emp(empno,ename) values(1111,'xxxx');
update emp set sal = 2000 where empno= 1010;
delete emp where empno = 1012;
commit;
dbms_output.put_line(v_sal || ',' || v_ename || ',' || v_hiredate);
end;

三、記錄型別的定義
定義方式：顯示定義和隱式定義

1、隱式定義

隱式定義：是在基於表的結構或查詢上使用%TYPE 屬性，隱式宣告是一個更強有力的工具，這是因為這種資料變數是動態建立的。語法如下：

① 表記錄的隱式定義（student為表名）
record_type student%ROWTYPE;

② 遊標行記錄的隱式定義

cursor student_line(no_value student.no%type) is
select cuid,no from student where no =no_value;
stu_row student_line%rowtype;

2、顯示定義

顯示定義：在PL/SQL 程式塊中建立記錄變數之前在宣告部分定義。使用type 命令定義記錄，然後在建立該記錄的變數。建立語法如下：
TYPE record_type IS RECORD (field_definition_list);
注：
i、 field_definition_list是由逗號分隔的列表；
ii、通過type定義的是一種自定義的型別，需要再通過該型別來建立變數；
iii、整個定義過稱和SQL中定義表相似。
V、記錄中域的引用方式：rtt.testa 如下：
CREATE OR REPLACE FUNCTION VarDefined RETURN INT IS
--定義記錄型別record_test
TYPE record_test IS RECORD(
testa NUMBER,
testb NUMBER,
testc NUMBER);
--定義型別為record_test的變數rtt
rtt record_test;
BEGIN
--rtt中域testa的引用及賦值
rtt.testa := 10;
--輸出資訊
dbms_output.put_line('rtt.testa=' || rtt.testa);
--返回值
RETURN 0;
END VarDefined;

注意事項
顯示定義時，必須用type進行定義，然後才能宣告該記錄的變數；
隱式定義，靈活方便，但是可讀性差。

四、記錄的賦值方式
①　select into方式；
②　fetch方式

1、 SELECT INTO
DECLARE
v_mytype emp%ROWTYPE;
BEGIN
SELECT ename,
sal
INTO v_mytype.ename,
v_mytype.sal
FROM emp
WHERE mgr IS NULL;
dbms_output.put_line(v_mytype.ename || ' ' || v_mytype.sal || ' ' || v_mytype.hiredate);
END;

注意：

如果記錄型別的欄位是數字型別的，且有形如v_r_t.fttb_res_num := v_r_t.fttb_res_num + v_count2; 的，則必須進行初始化，不然變數的值一直為0

DECLARE

v_count2 NUMBER(18) := 0;

v_r_t sqm.SQM_FTTX_RES_COVER%ROWTYPE;

BEGIN
v_r_t.fttb_gpon_res_num := 0;--這裡必須進行初始化
v_r_t.fttb_res_num := 0;--這裡必須進行初始化
FOR REC1 IN (SELECT * FROM xt_fttx_res_num_02) LOOP
SELECT COUNT(1)
INTO v_count2
FROM XB_PORT P
WHERE P.MEID = REC1.meid
AND P.METACATEGORY IN
('com.gxlu.ngrm.equipment.PSTNPort',
'com.gxlu.ngrm.equipment.IPPort',
'com.gxlu.ngrm.equipment.XdslPort');
v_r_t.fttb_res_num := v_r_t.fttb_res_num + v_count2;
IF rec1.is_gpon = 1 THEN
v_r_t.fttb_gpon_res_num := v_r_t.fttb_gpon_res_num + v_count2;
END IF;
END LOOP;
END;

2、 fetch 。。。BULK COLLECT INTO

DECLARE
CURSOR c1 IS
SELECT t.OBJECT_ID,
t.OBJECT_NAME
FROM user_objects t
WHERE t.OBJECT_ID <= 272835
AND t.OBJECT_ID >= 272830;
--v_depart user_objects%ROWTYPE;
TYPE v_id_type IS TABLE OF user_objects.OBJECT_ID%TYPE;
v_id v_id_type;
TYPE v_name_type IS TABLE OF user_objects.OBJECT_NAME%TYPE;
v_name v_name_type;
BEGIN
OPEN c1;
FETCH c1 BULK COLLECT
INTO v_id,
v_name;
FOR i IN 1 .. v_id.count LOOP
dbms_output.put_line(v_id(i) || ',' || v_name(i));
END LOOP;
CLOSE c1;
END;

通過上面的這個例子，大家可以發現如果列很多的話，為每一列定義一個集合似乎有些繁瑣，可以把集合和%rowtype結合起來一起使用來簡化程式!
在輸出結果時，既可以使用集合的count屬性和可以使用first和last，在引用%rowtype型別的內容時，還有一個需要注意的地方是v_depart(i).depart_code，而不是v_depart.depart_code(i)，當然沒有這樣的寫法，即使有意義也並不一樣。

DECLARE
CURSOR c1 IS
SELECT t.*
FROM user_objects t
WHERE t.OBJECT_ID <= 272835
AND t.OBJECT_ID >= 272830;
TYPE v_depart_type IS TABLE OF user_objects%ROWTYPE;
v_depart v_depart_type;
BEGIN
OPEN c1;
FETCH c1 BULK COLLECT
INTO v_depart;
FOR i IN 1 .. v_depart.count LOOP
--或者 FOR i IN v_depart.first .. v_depart.last LOOP
dbms_output.put_line(v_depart(i)
.OBJECT_ID || ',' || v_depart(i).OBJECT_NAME);
END LOOP;
CLOSE c1;
END;

3、 SELECT 。。。 BULK COLLECT INTO

DECLARE
TYPE v_depart_type IS TABLE OF user_objects%ROWTYPE INDEX BY PLS_INTEGER;
v_depart v_depart_type;
BEGIN
SELECT t.* BULK COLLECT
INTO v_depart
FROM user_objects t
WHERE t.OBJECT_ID <= 272835
AND t.OBJECT_ID >= 272830;
FOR i IN 1 .. v_depart.count LOOP
--或者 FOR i IN v_depart.first .. v_depart.last LOOP
dbms_output.put_line(v_depart(i)
.OBJECT_ID || ',' || v_depart(i).OBJECT_NAME);
END LOOP;
END;

五、記錄型別的使用
1、在insert語句中使用記錄
--在values 中使用記錄變數
DECLARE
v_mytype emp%ROWTYPE;
BEGIN
v_mytype.ename := 'pengxiao';
v_mytype.sal := 500;
v_mytype.hiredate := SYSDATE;
INSERT INTO emp VALUES v_mytype;
END;
--在values 中使用記錄成員

DECLARE v_mytype emp%ROWTYPE;
BEGIN
v_mytype.ename := 'pengxiao';
v_mytype.sal := 500;
v_mytype.hiredate := SYSDATE;
INSERT INTO emp
(ename, sal, hiredate)
VALUES
(v_mytype.ename, v_mytype.sal, v_mytype.hiredate);
END;

2、 Update 中使用記錄
--UPDATE 中使用記錄變數
DECLARE
v_mytype emp%ROWTYPE;
BEGIN
v_mytype.ename := 'pengxiao';
v_mytype.sal := 500;
v_mytype.hiredate := SYSDATE;
UPDATE emp SET ROW = v_mytype WHERE mgr IS NULL;
END;
--UPDATE 中使用記錄成員
DECLARE
v_mytype emp%ROWTYPE;
BEGIN
v_mytype.ename := 'pengxiao';
v_mytype.sal := 500;
v_mytype.hiredate := SYSDATE;
UPDATE emp
SET ename = v_mytype.ename, sal = v_mytype.sal
WHERE mgr IS NULL;
END;

3、 Delete 中使用記錄
--DELETE 只能使用記錄成員不能使用記錄變數
DECLARE
v_mytype emp%ROWTYPE;
BEGIN
v_mytype.deptno := 30;
DELETE FROM emp WHERE deptno = v_mytype.deptno;
END;

2.1.2.2 PL\SQL集合
PL/SQL中沒有陣列的概念，他的集合資料型別和陣列是相似的。在7.3以前的版本中只有一種集合，稱為PL/SQL表，在這之後又有兩種集合資料型別:巢狀表和varray。其中varray集合中的元素是有數量限制的，index_by表和巢狀表是沒有這個限制的。index-by表是稀疏的，也就是說下標可以不連續，varray型別的集合則是緊密的，他的下標沒有間隔。index_by表不能儲存在資料庫中，但是巢狀表和varray可以被儲存在資料庫中。
集合在使用時必須先使用type進行定義方可使用。

1. 為了處理單行單列資料我們使用標量變數
2. 為了處理單行多列資料我們使用PL\SQL記錄
3. 為了處理單列多行資料，我們可以使用 PL\SQL集合
4. 集合型別包括索引表（PL\SQL表、index-by表，關聯陣列），巢狀表，和變長陣列varray三種型別,使用這三種型別的時候要仔細體會他們中的區別

PL/SQL集合：
1. 關聯陣列：也稱索引表，可以用任意數字和字串作為下標，類似於其他語言中的hash 表。
2. 巢狀表：可以儲存任意數量的元素，使用連續數字作為下標。可以定義等價的SQL型別，可以儲存在資料庫表中，用SQL進行檢索。
3. Varrays（可變陣列，可變大小陣列）：儲存固定數量的元素（在執行中，可以改變元素數量），使用順序數字作下標，可以定義等價的SQL型別，可以儲存在資料庫中。可以用SQL進行儲存和檢索，但比巢狀表缺乏靈活性。
一、表型別
相當於高階語言中的陣列，但是需要注意的是在高階語言中陣列的下標不能為負數，而 pl/sql 是可以為負數的，並且表元素的下標沒有限制。例項如下：
PL/SQL 表, 或者稱為索引表(index-table)或稱為關聯陣列，是可以在PL/SQL 程式中引用、能夠模仿陣列的非永久表。使用者可以定義一個表型別，然後宣告這種型別的變數。接下來，使用者就可以將記錄新增到使用者的PL/SQL 表中，並且採用與引用陣列元素大體相同的方法引用他們。

表包括兩個基本成分：
①　主鍵：資料型別為BINARY_INTEGER
②　標量或記錄資料型別

Note：此表非彼表
定義一個表：
TYPE type_name IS TABLE OF
{column_type | variable%TYPE | table.column%TYPE } [NOT NULL] | table%ROWTYPE
[ INDEX BY BINARY_INTEGER];
? 注意：
1． Index-by表中的元素不一定要按任何特定的順序排序
2．關鍵字唯一允許的型別是BINARY_INTERGER
3．使用主鍵值引用表中的欄位
4．Table型別沒有長度限制，可以動態增長
5．由於不儲存在資料庫中，column_type可以是任何合法的PL/SQL資料型別，包括：PLS/INTEGER、SIGNTYPE、和BOOLEAN。
6．使用的時候需要先賦值後讀取，至少也要先初始化一下，否則會出現異常：ORA-01403: no data found。
7．這種陣列不需要事先指定上限，下標可以不連續，可以是0或負數。

例：v1 TYPE1;

v1(-1) := '-1';
v1(0) := '0';
v1(1) := '1';
DBMS_OUTPUT.put_line(v1(-1)); --訪問合法
DBMS_OUTPUT.put_line(v1(2)); --訪問非法

Sql 程式碼

declare
--定義了一個 pl/sql 表型別 sp_table_type，該型別是用於存放emp.ename%type
--index by binary_integer 表示下標是整數
type emp_table_type is table of emp.ename%type
index by binary_integer;
--定義了一個 sp_table 變數，這個變數的型別是 sp_table_type
sp_table emp_table_type;
begin
select ename into sp_table(-1) from emp where empno = 7788;
dbms_output.put_line('員工名:' || sp_table(-1));
end;

說明：
sp_table_type 是 pl/sql 表型別
emp.ename%type 指定了表的元素的型別和長度
sp_table 為 pl/sql 表變數
sp_table(0) 則表示下標為 0 的元素
注意：如果把 select ename into sp_table(-1) from emp where empno = 7788;
變成 select ename into sp_table(-1) from emp;則執行時會出現錯誤，錯誤如下：
ORA-01422:實際返回的行數超出請求的行數
解決方法是：使用參照變數（這裡不講）

1、 PL/SQL 表方法
方法描述
EXISTS(n) 用於判斷PL/SQL表中指定的元素是否存在。
COUNT 返回一個PL/SQL表當前包含的元素的數量。
FIRST
LAST 在PL/SQL表中返回第一個和最後一個索引數字。
DELETE
DELETE把所有的元素從PL/SQL表中移開。
DELETE（n）把第n 個元素從PL/SQ表中移開。
DELETE(m,n)從PL/SQ表中移開在範圍（m,n）中的所有元素。

DECLARE
TYPE e_table_type IS TABLE OF emp.Ename%TYPE INDEX BY BINARY_INTEGER;
e_tab e_table_type;
v_var NUMBER(2);
BEGIN
e_tab(1) := 'SMITH';
UPDATE emp SET sal = 1.1 * sal WHERE Ename = e_tab(1);
v_var := e_tab.count;
dbms_output.put_line(v_var);
e_tab.delete(1);
v_var := e_tab.count;
dbms_output.put_line(v_var);
END;

DECLARE
TYPE dept_table_type IS TABLE OF dept%ROWTYPE INDEX BY BINARY_INTEGER;
my_dname_table dept_table_type;
v_count NUMBER(3);
BEGIN
v_count := -10;
my_dname_table(v_count).dname := 'searching';
dbms_output.put_line(my_dname_table(v_count).dname);
v_count := 0;
my_dname_table(v_count).dname := 'market';
dbms_output.put_line(my_dname_table(v_count).dname);
v_count := 10;
my_dname_table(v_count).dname := 'finance';
dbms_output.put_line(my_dname_table(v_count).dname);
END;

2、例一

drop table test ;
create table test(
id number(20),
value varchar2(50)
);

DECLARE
TYPE test_record IS RECORD(
id NUMBER(5),
VALUE VARCHAR(10));
TYPE test IS TABLE OF test_record INDEX BY BINARY_INTEGER;
v_test test;
v_starTime NUMBER(10);
v_endTime NUMBER(10);
BEGIN

--賦值
FOR i IN 1 .. 5000 LOOP
v_test(i).id := i;
v_test(i).value := to_char(floor(dbms_random.value(10, 101)));
END LOOP;
--檢視值
v_starTime := dbms_utility.get_time;
FOR i IN v_test.first .. v_test.last LOOP
INSERT INTO test VALUES (v_test(i).id, v_test(i).value);
END LOOP;
v_endTime := dbms_utility.get_time;
Dbms_Output.put_line('所用時間：' || (v_endTime - v_starTime) / 100);
END;

DECLARE
TYPE test_record IS RECORD(
id NUMBER(5),
VALUE VARCHAR(10));
TYPE test1 IS TABLE OF test_record INDEX BY BINARY_INTEGER;
v_test1 test1;
v_starTime NUMBER(10);
v_endTime NUMBER(10);
BEGIN
FOR i IN 1 .. 5000 LOOP
v_test1(i).id := i;
v_test1(i).value := to_char(floor(dbms_random.value(10, 101)));
END LOOP;
v_starTime := dbms_utility.get_time;
FORALL i IN v_test1.first .. v_test1.last
INSERT INTO test VALUES (v_test1(i).id, v_test1(i).value);
v_endTime := dbms_utility.get_time;
Dbms_Output.put_line('所用時間：' || (v_endTime - v_starTime) / 100);
END;

DECLARE
TYPE test1 IS TABLE OF dept%ROWTYPE;
v_test1 test1;
BEGIN
SELECT * BULK COLLECT INTO v_test1 FROM dept;
FOR i IN v_test1.first .. v_test1.last LOOP
Dbms_Output.put_line('部門名稱：' || v_test1(i).dname);
END LOOP;
END;

探討：有關PL/SQL表型別中的理解。
很容易認為PL/SQL表型別象c或Java中的陣列型別，因為其中有個關鍵字叫做“index”(索引)，而陣列的下標也叫做“index”。
但是這樣無法解釋PL/SQL表型別的索引可以不連續，甚至可以為負數的問題。
我認為，PL/SQL表型別更象是java中的map物件，索引就對應map中的鍵，多個值之間的鍵可以不連續，可以為負數，甚至可以是字串。兩者有異曲同工之妙。
示例：用字串做索引。
declare
--定義表結構
type myTabType is table of char(2) index by varchar2(5);
sexTab myTabType;

v_sex_code varchar2(5);
v_sex_name char(2);
begin
--往表中新增內容
sexTab('nan'):='男';
sexTab('nv'):='女';
--使用者輸入
v_sex_code:='&性別編號';
v_sex_name:=sexTab(v_sex_code);

dbms_output.put_line('您的性別是 = '||v_sex_name);
end;
輸入：nan
輸出: 男
聯想一下下：
·在PL/SQL表中，利用PL/SQL表型別，在某些場合也能夠避免減少表連線，從而大大提高連線的效率。
·在java中map物件用途非常廣泛，可以快取很多物件(如配置檔案物件，常見的查詢結果物件等)。兩者的用法真的是異曲同工之妙。
案例：
SQL> declare
2 type table_emp is table of emp%rowtype
3 index by binary_integer;
4 empt table_emp;
5 begin
6 empt(1).ename:='wangyi';
7 dbms_output.put_line(empt(1).ename);
8 end;
9 /

//返回總記錄
SQL> declare
2 type table_emp is table of emp%rowtype
3 index by binary_integer;
4 empt table_emp;
5 begin
6 dbms_output.put_line(empt.count);
7 end;
8 /

0 //沒有記錄

//刪除的操作
表名.Delete(記錄數);
//檢索記錄變數
First：獲取第一個的索引
Next：下一個的索引但是必須有引數
Last：最後一個的索引
SQL> declare
2 type table_emp is table of emp%rowtype
3 index by binary_integer;
4 empt table_emp;
5 i number(2):=1;
6 begin
7 while i<10
8 loop
9 empt(i).ename:='wangyi';
10 i:=i+1;
11 end loop;
12
13 dbms_output.put_line(empt.count);
14
15 empt.delete(2);
16
17 dbms_output.put_line(empt.count);
18
19 dbms_output.put_line(empt.first);
20 dbms_output.put_line(empt.next(2));
21 dbms_output.put_line(empt.last);
22 end;
23 /

oracle中type is table 型別刪除
最近寫儲存過程，遇到一個問題，使用批量插入資料老是出錯，說違反唯一性約束，最後檢查儲存過程，發現type table資料沒有刪除而引起的，儲存過程如下：
1 type type_char2 is table of NVARCHAR2(30) index by binary_integer; --定義一個字串的臨時表型別
2 v_card_id type_char2;
3 --下面是遊標
4 cursor cur_bt_data is
5 select * from test....;
6 --遍歷遊標
7 for bt_row in cur_bt_data loop
8 for i in 1 .. bt_row.confirm_quanlity loop
9 v_card_id(i) := to_number(bt_row.iccid_start)+i-1;
10 end loop;
11 forall i in 1 .. v_card_id.count
12 insert /*+ append */
13 into demo
14 (card_id,....)
15 values
16 (v_card_id(i),...);
17 commit;
18 end loop; -- [END]for 'cur_bt_data'

發現裡面的v_card_id(i)問題，如果查詢的 bt_row.confirm_quanlity 數量一樣，迴圈初始化，資料應該沒有問題，如果數量不一樣，如，前一次查詢的數量大，後一次的數量小，v_card_id沒有初始化後面的，就可能出現重複的結果，所有每次迴圈都必須清理臨時表
查了相關資料，只需要使用v_card_id.delete就刪除臨時表
修改如下：
19 for bt_row in cur_bt_data loop
20 v_card_id.delete;
21 for i in 1 .. bt_row.confirm_quanlity loop
22 ..........

二、巢狀表
巢狀表是表中之表。一個巢狀表是某些行的集合，它在主表中表示為其中的一列。對主表中的每一條記錄，巢狀表可以包含多個行。在某種意義上，它是在一個表中儲存一對多關係的一種方法。
Nested table is a data type in Oracle which is used to support columns containing multi valued attributes. It also hold entire sub table.
1.必須進行初始化，否則會出現異常：ORA-06531: Reference to uninitialized collection
2.初始化方法：
v1 TYPE2 := TYPE2(); --宣告時初始化陣列為空

v2 TYPE2 := TYPE2('1','2','3','4','5'); --宣告時初始化陣列為5個元素
v1 := TYPE2(); --初期化後陣列為空
v2 := TYPE2('1','2','3','4','5'); --初期化後陣列為5個元素
3.陣列元素的訪問：
下標從1開始，不能超過陣列所有元素的總和，當下標超出允許範圍時，出現異常：ORA-06532: Subscript outside of limit
因為不能訪問空陣列，所以空陣列的場合，必須進行陣列擴充套件。 54ne.com
例：v1.EXTEND;
V1(1):= ‘1’; --訪問合法
v1(2):= ‘2’; --訪問非法，之前必須再次執行v1.EXTEND; 網管網bitsCN_com
例：v2的下標範圍是1～5。
v2(5):= ‘Hello’; --訪問合法
DBMS_OUTPUT.put_line(v2(6)); --訪問非法

Oracle 資料庫建立巢狀表
步驟一：開啟SQLPLUS, 輸入使用者名稱與密碼（本例以Scott 使用者登入）
登入後顯示的介面如下：

圖 1-1
步驟二：建立元組型別TDate,在SQLPLUS中輸入
create type TDate as object(day integer, month char(3), year integer);
換行輸入“/”。建立成功：

圖 1-2
步驟三：建立Tcg型別
輸入：
create type TCg as object(Course varchar2(5), Grade integer, Cdate TDate);
建立成功，SQLPLUS介面顯示如下:

圖 1-3
步驟四：建立巢狀表型別 TCgs
在SQLPLUS中輸入：create type TCgs as table of TCg
建立成功後：

圖 1-4
步驟四：建立最外層表SC
在SQLPLUS中輸入：
create table SC(Sname varchar2(10),Cg TCgs)
nested table Cg store as Cg_tab
SQLPLUS 介面如下

圖 1-5
步驟五：插入資料
在SQLPLUS 中輸入：
insert into SC
values('picart',TCgs(TCg('DB',80,TDate(1, 'Oct',2009)),
TCg('OS',80,TDate(1, 'Nov',2009))));
insert into SC
values('kirk',TCgs(TCg('DB',85,TDate(1, 'Jul',2009)),
TCg('MATHS',80,TDate(1, 'Oct',2009))))
如下圖所示：

圖 1-6
步驟六：查詢資料
1）為了驗證資料是否插入表中，可輸入“select * from sc” 進行查詢，如圖所示：

圖 1-7
2）查詢每個同學選課的門數
在SQLPLUS 中輸入：
select sname, (select count(*) from table (x.Cg))
from SC x
結果如圖：

圖 1-8
3）查詢”kirk” 選擇課程的詳細資訊
在SQLPLUS 中輸入：
select * from (select cg from sc where sname=’kirk’);
結果下：

圖 1-9
4）查詢”kirk” 選擇的課程名
在SQLPLUS 中輸入：
select x.course from table(select cg from sc where sname=’kirk’) x;
結果如下：

圖 2-1
步驟七：修改資料
修改”kirk”的選課資訊，將“DB”課程修改為“orcl”
在SQLPLUS 中輸入：
Update table(select cg from sc where sname=’kirk’) x set x.course=’orcl’ where x.course=’DB’；
然後輸入：select x.course from table(select cg from sc where sname=’kirk’) x 查詢修改的結果
結果如下：

圖 2-2
修改”kirk”的選課時間，將其選擇課程‘orcl’的選課時間改為“21，Oct,2011”
在SQLPLUS 中輸入：
Update table(select cg from sc where sname=’kirl’) set cdate=tdate(21,’Oct’,2011) where course=’orcl’;
然後輸入：select * from table(select cg from sc where sname=’kirk’) where course=’orcl’
結果如圖：

圖 2-3
修改修改”kirk”的選課時間，將其選擇課程‘orcl’的選課時間的年份改為2010年
在SQLPLUS 中輸入：
update table(select cg from sc where sname=’kirk’) x set x.cdate.year=2010 where x.course=’orcl’;
然後輸入：select * from table(select cg from sc where sname=’kirk’) where course=’orcl’
結果如圖：

圖 2-4
步驟八：刪除SC表中的資料
刪除‘kirk‘所選的課程’orcl‘的資訊
在SQLPLUS 中輸入：
delete from table(select cg from sc where sname=’kirk’) where x.course=’orcl’;
然後輸入：select * from table(select cg from sc where sname=’kirk’)查詢
結果如圖：

2)刪除‘kirk‘的選課資訊
在SQLPLUS 中輸入：
delete from sc where sname=’kirk’
然後輸入：select * from sc 查詢

ORACLE巢狀表的使用

考查一個包含部門資訊的表，在任何時間內每個部門會有很多專案正在實施。在一個嚴格的關係模型中，將需要建立兩個獨立的表department和project。
巢狀表允許在department表中存放關於專案的資訊。勿需執行聯合操作，就可以通過department表直接訪問專案表中的記錄。這種不經聯合而直接選擇資料的能力使得使用者對資料訪問更加容易。甚至在並沒有定義方法來訪問巢狀表的情況下，也能夠很清楚地把部門和專案中的資料聯絡在一起。在嚴格的關係模型中，department和project兩個表的聯絡需要通過外部關鍵字（外來鍵）關係才能實現。
下面以電信賬單列印程式中的資料庫設計來舉例說明巢狀表的使用方法。
　1、建立型別ITEM_TYPE：此型別中，對於每個ITEM_TYPE型別都包含有一個記錄，記載了其名稱、型別、層次和數量資訊。

CREATE OR REPLACE TYPE "ITEM_TYPE" As Object
(
Item_Seq Number,
Item_Name Varchar2(100),
Item_Type Varchar2(100),
Item_Level Varchar2(100),
Item_Amt Number
)
2、建立ITEM_LIST_TYPE：此型別將用作一個巢狀表的基礎型別。

CREATE OR REPLACE TYPE "ITEM_LIST_TYPE" As Table Of item_type
3、建立表T_MAC_INFO：裝置資費資訊表

-- Create table
create table T_MAC_INFO
(
CHARGE_NO VARCHAR2(100),
MAC_TYPE VARCHAR2(100),
SUBTOTAL NUMBER,
ITEMS ITEM_LIST_TYPE,
MAC_SEQ NUMBER,
PRODUCT_SEQ NUMBER
)
nested table ITEMS store as ITEMS_TAB

4、向巢狀表中插入記錄

主要程式碼以下：

變數宣告：
Item_List1 Item_List_Type;
Item1 Item_Type;

For I in 1…10 to loop
Item_List1.Extend;
Item_List1(Item_List1.Count) := Item_Type(Item_List1.Count, Item1.Item_Name, Item1.Item_Type, Item1.Item_Level, Item1.Item_Amt);
End loop;

Insert Into t_Mac_Info
( --Cust_Id,
Product_Seq,
Mac_Seq,
Charge_No,
Mac_Type,
Subtotal,
Items)
Values
( --Mac_Info1.Cust_Id,
v_Seq_Product, -- Mac_Info1.Product_Seq,
Mac_Seq,
V_Charge_No,
V_Mac_Type,
V_Subtotal,
Item_List1);

commit;

其中ITEM_LIST1作為一巢狀表，被插入儲存在T_MAC_INFO這個表裡面。

5、查詢巢狀表

Select * From t_Mac_Info d, Table(d.Items) Emp

三、巢狀表的特點：
對於巢狀表，在ORACLE 的OOP程式設計裡面使用得多一點。在資料庫中使用關係-物件模型，具體表現為物件複用、標準支援、定義訪問路徑等，這些都是OOP程式設計的好處：
1、物件複用：如果編寫物件導向的程式碼，就提高了重用以前編寫的程式碼模組的機會。同樣，如果建立物件導向的資料庫物件，也就提高了資料庫物件能夠被重用的機會。
2、標準支援：如果建立標準的物件，那麼它們被重用的機會就會提高。如果有多個應用或多個表使用同一資料庫物件集合，那麼它就是既成事實的資料庫物件標準。
3、定義訪問路徑：對於每一個物件，使用者可定義在其上執行的過程和函式，從而可以使資料和訪問此資料的方法聯合起來。有了用這種方式定義的訪問路徑，就可以標準化資料訪問的方法並提高物件的可複用性。
以上舉例說明了巢狀表的簡單使用，對於巢狀表的詳細用法，可以參考相關書籍。
三、可變陣列
元素下標從0開始，陣列變數的宣告需要同時初始化
一般格式為：
TYPE type_name IS VARRAY (maximum_size) OF element_type

其中：
1. type_name是新可變長陣列型別的型別名,maximum_size是一個指定可變陣列中元素最大數目的整數。element_type是一個PL/SQL標量、記錄或物件型別。

一般格式為：
DECLARE
type numberlist is varray(10) of number(5);
type recordlist is varray(5) of dept%rowtype;

例： DECLARE
type strings is varray(5) of varchar2(10);
-- Declare a varray with four element
v_list strings := strings('scott','peter','smith','tom');
v_count number;
BEGIN
v_count := 1;
dbms_output.put_line(v_list(v_count));
v_list(v_count) := 'urman';
dbms_output.put_line(v_list(v_count));
v_count := 3;
dbms_output.put_line(v_list(v_count));
v_list(v_count) := 'jackson';
dbms_output.put_line(v_list(v_count));
v_list(4):='oracle';
dbms_output.put_line(v_list(4));
END;

TYPE TYPE3 IS ARRAY(5) OF VARCHAR2(10);

由於型別定義時的元素個數限制，所以TYPE3的變數在使用時最大的元素個數不能超過5個。與巢狀表基本相同（略）
四、表和陣列的屬性

例：DECLARE
type strings_table is table of varchar2(10) index by
binary_integer;
strings strings_table;
v_count number;
BEGIN
v_count := 1;
strings(v_count ) := 'element1';
if strings.exists(v_count ) then
dbms_output.put_line(strings(v_count ));
else
dbms_output.put_line('no data!');
end if;
--to be continued例：DECLARE
type strings_table is table of varchar2(10) index by
binary_integer;
strings strings_table;
v_count number;
BEGIN
v_count := 1;
strings(v_count ) := 'element1';
if strings.exists(v_count ) then
dbms_output.put_line(strings(v_count ));
else
dbms_output.put_line('no data!');
end if;
--to be continued

例：
strings(2) := 'element2';
strings(3) := 'element3';
strings(4) := 'element4';
strings(5) := 'element5';
strings(6) := 'element6';
dbms_output.put_line(strings.count);
dbms_output.put_line(strings.first);
dbms_output.put_line(strings.last);
dbms_output.put_line(strings.next(2));
dbms_output.put_line(strings.prior(4));
strings.delete(1,3);--delete from 1 to 3
--dbms_output.put_line(strings(2)); -- cann’t find data
END;

全面探討PL/SQL的複合資料型別
PL/SQL有兩種複合資料結構：記錄和集合。記錄由不同的域組成，集合由不同的元素組成。在本文中我們將討論記錄和集合的型別、怎樣定義和使用記錄和集合。
　　PL/SQL 記錄
　　記錄是PL/SQL的一種複合資料結構，scalar資料型別和其他資料型別只是簡單的在包一級進行預定義，但複合資料型別在使用前必須被定義，記錄之所以被稱為複合資料型別是因為他由域這種由資料元素的邏輯組所組成。域可以是scalar資料型別或其他記錄型別，它與c語言中的結構相似，記錄也可以看成表中的資料行，域則相當於表中的列，在表和虛擬表（檢視或查詢）中非常容易定義和使用，行或記錄中的每一列或域都可以被引用或單獨賦值，也可以通過一個單獨的語句引用記錄所有的域。在儲存過程或函式中記錄也可能有引數。
　　建立記錄
　　在PL/SQL中有兩種定義方式：顯式定義和隱式定義。一旦記錄被定義後，宣告或建立定義型別的記錄變數，然後才是使用該變數。隱式宣告是在基於表的結構或查詢上使用%TYPE屬性，隱式宣告是一個更強有力的工具，這是因為這種資料變數是動態建立的。
　　顯式定義記錄
　　顯式定義記錄是在PL/SQL程式塊中建立記錄變數之前在宣告部分定義。使用type命令定義記錄，然後在建立該記錄的變數。語法如下：
　　TYPE record_type IS RECORD (field_definition_list);
　　field_definition_list是由逗號分隔的列表。
　　域定義的語法如下：
　　field_name data_type_and_size [NOT NULL][{:=|DEFAULT} default_value]
　　域名必須服從與表或列的命名規則相同的命名規則。下面我們看一個例子：
　　DELCARE
　　TYPE stock_quote_rec IS RECORD
　　(symbol stock.symbol%TYPE
　　,bid NUMBER(10,4)
　　,ask NUMBER(10,4)
　　,volume NUMBER NOT NULL:=0
　　,exchange VARCHAR2(6) DEFAULT 'NASDAQ'
　　);
　　real_time_quote stock_quote_rec;
　　variable
　　域定義時的%TYPE屬性用於引用資料庫中的表或檢視的資料型別和大小,而在此之前程式不知道型別和大小。在上面的例子中記錄域在編譯時將被定義為與列SYMBOL相同的資料型別和大小,當程式碼中要使用來自資料庫中的資料時，在變數或域定義中最好使用%TYPE來定義。
　　隱式定義記錄
　　隱式定義記錄中，我們不用描述記錄的每一個域。這是因為我們不需要定義記錄的結構，不需要使用TYPE語句，相反在宣告記錄變數時使用%ROWTYPE命令定義與資料庫表，檢視，遊標有相同結構的記錄，與TYPE命令相同的是它是一種定義獲得資料庫資料記錄的好方法。
　　DECLARE
　　accounter_info accounts%ROWTYPR;
　　CURSOR xactions_cur(acct_no IN VARCHAR2) IS
　　SELECT action,timestamp,holding
　　FROM portfolios
　　WHERE account_nbr='acct_no'
　　;
　　xaction_info xactions_cur%ROWTYPE;
　　variable
　　有一些PL/SQL指令在使用隱式定義記錄時沒有使用%ROWTYPE屬性，比如遊標FOR迴圈或觸發器中的:old和:new記錄。
　　DELCARE
　　CURSOR xaction_cur IS
　　SELECT action,timeamp,holding
　　FROM portfolios
　　WHERE account_nbr='37'
　　;
　　BEGIN
　　FOR xaction_rec in xactions_cur
　　LOOP
　　IF xactions_rec.holding='ORCL'
　　THEN
　　notify_shareholder;
　　END IF;
　　END LOOP;
　　使用記錄
　　使用者可以給記錄賦值、將值傳遞給其他程式。記錄作為一種複合資料結構意味作他有兩個層次可用。使用者可以引用整個記錄，使用select into或fetch轉移所有域，也可以將整個記錄傳遞給一個程式或將所有域的值賦給另一個記錄。在更低的層次，使用者可以處理記錄內單獨的域，使用者可以給單獨的域賦值或者在單獨的域上執行布林表示式，也可以將一個或更多的域傳遞給另一個程式。
　　引用記錄
　　記錄由域組成，訪問記錄中的域使用點（.）符號。我們使用上面的例子看看
　　DELCARE
　　TYPE stock_quote_rec IS RECORD
　　(symbol stock.symbol%TYPE
　　,bid NUMBER(10,4)
　　,ask NUMBER(10,4)
　　,volume NUMBER NOT NULL:=0
　　,exchange VARCHAR2(6) DEFAULT 'NASDAQ'
　　);
　　TYPE detailed_quote_rec IS RECORD
　　(quote stock_quote_rec
　　,timestamp date
　　,bid_size NUMBER
　　,ask.size NUMBER
　　,last_tick VARCHAR2(4)
　　);
　　real_time_detail detail_quote_rec;
　　BEGIN
　　real_time_detail.bid_size:=1000;
　　real_time_detail.quote.volume:=156700;
　　log_quote(real_time_detail.quote);
　　給記錄賦值
　　給記錄或記錄中的域賦值的方法有幾種，可以使用SELECT INTO或FETCH給整個記錄或單獨的域賦值，可以將整個記錄的值賦給其他記錄，也可以通過給每個域賦值來得到記錄，以下我們通過例項講解每一種賦值方法。
　　1、使用SELECT INTO
　　使用SELECT INTO給記錄賦值要將記錄或域放在INTO子串中，INTO子串中的變數與SELECT中列的位置相對應。
　　例：
　　DECLARE
　　stock_info1 stocks%ROWTYPE;
　　stock_info2 stocks%ROWTYPE;
　　BEGIN
　　SELECT symbol,exchange
　　INTO stock_info1.symbol,stock_info1.exchange
　　FROM stocks
　　WHERE symbol='ORCL';
　　SELECT * INTO stock_info2 FROM stocks
　　WHERE symbol='ORCL';
　　2、使用FETCH
　　如果SQL語句返回多行資料或者希望使用帶引數的遊標，那麼就要使用遊標，這種情況下使用FETCH語句代替INSTEAD INTO是一個更簡單、更有效率的方法，但在安全性較高的包中FETCH的語法如下：
　　FETCH cursor_name INTO variable;
　　我們改寫上面的例子：
　　DECLARE
　　CURSOR stock_cur(symbol_in VARCHAR2) IS
　　SELECT symbol,exchange,begin_date
　　FROM stock
　　WHERE symbol=UPPER(symbol_in);
　　stock_info stock_cur%ROWTYPE
　　BEGIN
　　OPEN stock_cur('ORCL');
　　FETCH stock_cur INTO stock_info;
　　使用賦值語句將整個記錄複製給另一個記錄是一項非常有用的技術，不過記錄必須精確地被宣告為相同的型別，不能是基於兩個不同的TYPE語句來獲得相同的結構。
　　例：
　　DECLARE
　　TYPE stock_quote_rec IS RECORD
　　(symbol stocks.symbol%TYPE
　　,bid NUMBER(10,4)
　　,ask number(10,4)
　　,volume NUMBER
　　);
　　TYPE stock_quote_too IS RECORD
　　(symbol stocks.symbol%TYPE
　　,bid NUMBER(10,4)
　　,ask number(10,4)
　　,volume NUMBER
　　);
　　--這兩個記錄看上去是一樣的，但實際上是不一樣的
　　stock_one stocks_quote_rec;
　　stock_two stocks_quote_rec;
　　--這兩個域有相同的資料型別和大小
　　stock_also stock_rec_too；--與stock_quote_rec是不同的資料型別
　　BEGIN
　　stock_one.symbol:='orcl';
　　stock_one.volume:=1234500;
　　stock_two：=stock_one;--正確
　　syock_also：=stock_one;--錯誤，資料型別錯誤
　　stock_also.symbol:=stock_one.symbol;
　　stock_also.volume:=stock_one.volume;　
　　記錄不能用於INSERT語句和將記錄直接用於比較，下面兩種情況是錯誤的：
　　INSERT INTO stocks VALUES (stock_record);
　　和
　　IF stock_rec1>stock_rec2 THEN
　　要特別注意考試中試題中有可能用%ROWTYPE來欺騙你，但這是錯誤的，記住這一點。還有可能會出現用記錄排序的情況，ORACLE不支援記錄之間的直接比較。對於記錄比較，可以採用下面的兩個選擇：
　　. 設計一個函式，該函式返回scalar資料型別，使用這個函式比較記錄，如
　　IF sort_rec(stock_one)>sort_rec(stock_two) THEN
　　. 可以使用資料庫物件，資料庫物件可以使用order或map方法定義，允許oracle對複合資料型別進行比較。關於資料庫物件的討論已經超越了本文的範圍，要詳細瞭解資料庫物件，可以查閱oracle手冊。
　　PL/SQL集合
　　集合與其他語言中的陣列相似，在ORACLE7.3及以前的版本中只有一種集合稱為PL/SQL表，這種型別的集合依然保留，就是索引（INDEX_BY）表，與記錄相似，集合在定義的時候必須使用TYPE語句，然後才是建立和使用這種型別的變數。
　　集合的型別
　　PL/SQL有三種型別的集合
　　. Index_by表
　　. 巢狀表
　　. VARRAY
　　這三種型別的集合之間由許多差異，包括資料繫結、稀疏性(sparsity)、資料庫中的儲存能力都不相同。繫結涉及到集合中元素數量的限制，VARRAY集合中的元素的數量是有限，Index_by和巢狀表則是沒有限制的。稀疏性描述了集合的下標是否有間隔，Index_by表總是稀疏的，如果元素被刪除了巢狀表可以是稀疏的，但VARRAY型別的集合則是緊密的，它的下標之間沒有間隔。
　　Index_by表不能儲存在資料庫中，但巢狀表和VARRAY可以被儲存在資料庫中。
　　雖然這三種型別的集合有很多不同之處，但他們也由很多相似的地方：
　　. 都是一維的類似陣列的結構
　　. 都有內建的方法
　　. 訪問由點分隔
　　Index_by表
　　Index_by表集合的定義語法如下：
　　TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX
　　BY BINARY_INTERGET;
　　這裡面重要的關鍵字是INDEX BY BINARY_INTERGET，沒有這個關鍵字，那麼集合將是一個巢狀表，element_type可以是任何合法的PL/SQL資料型別，包括：PLS/INTEGER、SIGNTYPE、和BOOLEAN。其他的集合型別對資料庫的資料型別都有限制，但Ind

2.1.3 參照變數

參照變數是指用於存放數值指標的變數。通過使用參照變數，可以使得應用程式共享相同物件，從而降低佔用的空間。在編寫 pl/sql 程式時，可以使用遊標變數(ref cursor)和物件型別變數(ref obj_type)兩種參照變數型別。

一、遊標(cursor)
參考：Oracle_lhr_遊標.docx

2.1.4 PL/SQL中的變數賦值
在PL/SQL程式設計中，變數賦值是一個值得注意的地方，它的語法如下：
variable := expression ;
variable 是一個PL/SQL變數, expression 是一個PL/SQL 表示式.

BOOLEAN 型變數賦值：
布林值只有TRUE, FALSE及 NULL 三個值，其中空值在參加算數運算時，結果仍為空值。

例：DECLARE
done BOOLEAN;
/* the following statements are legal: */
BEGIN
done := FALSE;
WHILE NOT done LOOP
----do something
Dbms_output.put_line(‘hello!’);
Done := TRUE;
END LOOP;
END;

2.2 type定義資料型別
2.2.1 CREATE OR REPLACE TYPE
2種定義方式
2.2.1.1 已存在資料型別
create or replace type type_glid_lhr is table of number;
create or replace type type_glid_lhr is table of varchar2(255);

--只能在包宣告、塊宣告等地方定義,不能在方案級使用
-- 錯誤： create or replace type type_a is table of xb_location%rowtype；
DECLARE

TYPE type_a IS TABLE OF xb_location%ROWTYPE;
v_a_type type_a;
BEGIN
NULL;
END;

2.2.1.2 不存在原資料型別

-- 只能在方案級定義一種新型別
-- create or replace TYPE objemp AS OBJECT(maxsal NUMBER, minsal NUMBER);
-- 錯誤：
DECLARE
TYPE objemp AS OBJECT(maxsal NUMBER, minsal NUMBER);
v_a_type objemp;
BEGIN
NULL;
END;

-- 正確：
create or replace TYPE objemp as OBJECT(maxsal NUMBER, minsal NUMBER);
-- 定義表：正確,用as或is都可以：
CREATE OR REPLACE TYPE tabemp AS TABLE OF objemp;
CREATE OR REPLACE TYPE tabemp is TABLE OF objemp;
DECLARE
TYPE tabemp is TABLE OF objemp; --這裡只能用is
v_a_type tabemp;
BEGIN
NULL;
END;

2.2.2 例一
DECLARE
v_dept_row dept%ROWTYPE;
BEGIN
v_dept_row.deptno := 50;
v_dept_row.dname := 'repair';
v_dept_row.loc := 'BeiJing';
INSERT INTO dept VALUES v_dept_row;
END;

DECLARE
v_dept_row dept%ROWTYPE;
BEGIN
v_dept_row.deptno := &deptno;
v_dept_row.dname := 'procduct';
v_dept_row.loc := 'Sydeny';
UPDATE dept SET ROW = v_dept_row WHERE deptno = v_dept_row.deptno;
END;

----------------練習1----------------
create or replace type phone_type is Table of varchar2(50);
create table tb_info(
id number(6) primary key,
vid varchar2(20) not null,
vname varchar2(20) not null,
vphone phone_type
)nested table vphone Store as phone_table;

INSERT INTO tb_info
VALUES
(1, 'scce001', 'dog', phone_type('110', '119', '120', '911'));
DECLARE
v_phone_type phone_type;
BEGIN
SELECT vphone INTO v_phone_type FROM tb_info WHERE id = 1;
FOR i IN 1 .. v_phone_type.Count LOOP

Dbms_Output.put_line(v_phone_type(i));
END LOOP;
END;

------------------練習2-------------------------------------------
create or replace type obj_goods as Object
(
price number(5),
quantity varchar2(14),
name varchar2(50),
--定義方法
member Function getMustPay Return number,--實際付款
member Function getChange Return number--找零
);

CREATE OR REPLACE TYPE BODY obj_goods AS
MEMBER FUNCTION getMustPay RETURN NUMBER IS
BEGIN
RETURN Self.price * Self.quantity;
END;
MEMBER FUNCTION getChange RETURN NUMBER IS
BEGIN
RETURN Self.price;
END;
END;

DECLARE
v_obj_goods obj_goods;
BEGIN
v_obj_goods := obj_goods(40, '5', '香菸');
Dbms_Output.put_line('商品數量：' || v_obj_goods.quantity);
Dbms_Output.put_line('商品名稱：' || v_obj_goods.name);
Dbms_Output.put_line('商品總價：' || v_obj_goods.getMustPay);
Dbms_Output.put_line('商品單價：' || v_obj_goods.price);
END;

2.2.3 例二
create table parent(
id number(10),
name varchar2(100),
title varchar2(10) );

create table child(
id number(10),
parent_id number(10),
child_name varchar2(100),
child_title varchar2(10),
child_content varchar2(200),
child_time timestamp );

create sequence seq_p_c_id
minvalue 1 maxvalue 9999999999
start with 1 increment by 1
nocache;

drop type t_child_lst_map;
drop type t_child_lst;
drop type t_parent_lst;

CREATE OR REPLACE TYPE t_parent AS OBJECT
(
NAME VARCHAR2(100),
title VARCHAR2(10)
);

CREATE OR REPLACE TYPE t_child AS OBJECT
(
child_name VARCHAR2(100),
child_title VARCHAR2(10),
child_content VARCHAR2(200)
)
;

create or replace type t_parent_lst as table of t_parent;

create or replace type t_child_lst as table of t_child;

create or replace type t_child_lst_map as table of t_child_lst;
CREATE OR REPLACE PROCEDURE proc_ins_parent_child(i_parent_lst IN t_parent_lst, --parent列表
i_child_map_lst IN t_child_lst_map, --child列表集合，一個map元素對應一個child_lst，其下標與 parent列表的下標相同。
o_ret OUT NUMBER) AS
var_parent t_parent;
var_child_lst t_child_lst;
var_child t_child;
var_parent_id NUMBER;
var_child_id NUMBER;
BEGIN
FOR i IN 1 .. i_parent_lst.count LOOP
--取得parent各列的值
var_parent := i_parent_lst(i); --取得parent_id;
SELECT seq_p_c_id.nextVal INTO var_parent_id FROM dual; --插入parent表
INSERT INTO PARENT
(id, NAME, title)
VALUES
(var_parent_id, var_parent.name, var_parent.title); --取得該parent對應的child列表
var_child_lst := i_child_map_lst(i);
FOR j IN 1 .. var_child_lst.count LOOP
var_child := var_child_lst(j); --取得child_id;
SELECT seq_p_c_id.nextVal INTO var_child_id FROM dual; --插入child表
INSERT INTO child
(id,
parent_id,
child_name,
child_title,
child_content,
child_time)
VALUES
(var_child_id,
var_parent_id,
var_child.child_name,
var_child.child_title,
var_child.child_content,
systimestamp);
END LOOP;
END LOOP;
o_ret := 0;
EXCEPTION
WHEN OTHERS THEN
BEGIN
o_ret := -1;
RAISE;
END;
END proc_ins_parent_child;

2.2.4 例三
drop type t_sms_message_lst;
drop type t_sms_message;

--此型別中，新增了pk_id
CREATE OR REPLACE TYPE t_sms_message AS OBJECT
(
task_id NUMBER(32),
sender_num VARCHAR2(21),
mobile_no VARCHAR2(21),
param_value VARCHAR2(900),
pk_id NUMBER(32)
)
;

create or replace type t_sms_message_lst as table of t_sms_message;

drop procedure proc_sel_sms_message;

CREATE OR REPLACE PROCEDURE proc_sel_sms_message(in_task_id IN NUMBER,
in_count IN NUMBER,
out_sms_message_lst OUT t_sms_message_lst,
out_ret OUT NUMBER) AS
var_cr_sms_message SYS_REFCURSOR;
var_m_row_id VARCHAR2(1000);
var_m_task_id sms_message.task_id%TYPE;
var_m_sender_num sms_message.sender_num%TYPE;
var_m_mobile_no sms_message.mobile_no%TYPE;
var_m_param_value sms_message.param_value%TYPE;
var_m_last_send_time sms_message.last_send_time%TYPE;
var_t_sms_message t_sms_message;
var_total_send_amount INTEGER;

BEGIN

--查詢語句增加了pk_id搜尋項
OPEN var_cr_sms_message FOR
SELECT ROWID AS row_id,
task_id,
sender_num,
mobile_no,
param_value,
pk_id
FROM sms_message
WHERE task_id = in_task_id
AND (sms_message.last_send_time IS NULL OR
sms_message.last_send_time <>
(SELECT last_send_time
FROM sms_task
WHERE task_id = in_task_id))
AND rownum <= in_count
FOR UPDATE;

out_sms_message_lst := t_sms_message_lst();

SELECT last_send_time
INTO var_m_last_send_time
FROM sms_task
WHERE task_id = in_task_id;

LOOP
--fetch語句增加了pk_id項
FETCH var_cr_sms_message
INTO var_m_row_id,
var_m_task_id,
var_m_sender_num,
var_m_mobile_no,
var_m_param_value,
var_m_pk_id;
EXIT WHEN var_cr_sms_message%NOTFOUND;

--輸出物件型別中增加了pk_id項
var_t_sms_message := t_sms_message(var_m_task_id,
var_m_sender_num,
var_m_mobile_no,
var_m_param_value,
var_m_pk_id);
out_sms_message_lst.extend;
out_sms_message_lst(out_sms_message_lst.count) := var_t_sms_message;

UPDATE sms_message
SET last_send_time = var_m_last_send_time
WHERE ROWID = var_m_row_id;

END LOOP;

CLOSE var_cr_sms_message;

out_ret := 0;

EXCEPTION
WHEN OTHERS THEN
BEGIN
out_ret := -1;
RAISE;
END;
END proc_sel_sms_message;

ORACLE基本資料型別總結

2013-08-17 21:04 by 瀟湘隱者, 138943 閱讀, 5 評論, 收藏, 編輯

				     ORACLE基本資料型別（亦叫內建資料型別 built-in datatypes)可以按型別分為：字串型別、數字型別、日期型別、LOB型別、LONG RAW& RAW型別、ROWID & UROWID型別。 
			
				在講敘字串型別前，先要講一下編碼。字串型別的資料可依編碼方式分成資料庫字符集（CHAR/VARCHAR2/CLOB/LONG)和國際字符集(NCHAR/NVARCHAR2/NCLOB)兩種。資料庫中的字串資料都通過字符集將字元轉換為數字後(二進位制)，才儲存到資料塊中。通過不同的編碼集轉換，即便是相同的字元，也可能會轉換成不同的二進位制編碼。這也是產生亂碼的原因。資料庫的編碼格式一般是在建立資料庫時指定的。當然也可以修改資料庫的編碼。 
			
				檢視資料庫檢視所包含的資料型別:SELECT * FROM DBA_TYPES WHERE OWNER IS NULL.具體細節情況參見Oracle? Database SQL Language Quick Reference 10/11g 或官方文件 
			
				一 字串型別 
			
				    字串資料型別還可以依據儲存空間分為固定長度型別（CHAR/NCHAR) 和可變長度型別（VARCHAR2/NVARCHAR2)兩種. 
			
				    所謂固定長度：是指雖然輸入的欄位值小於該欄位的限制長度，但是實際儲存資料時，會先自動向右補足空格後，才將欄位值的內容儲存到資料塊中。這種方式雖然比較浪費空間，但是儲存效率較可變長度型別要好。同時還能減少資料行遷移情況發生。 
			
				所謂可變長度：是指當輸入的欄位值小於該欄位的限制長度時，直接將欄位值的內容儲存到資料塊中，而不會補上空白，這樣可以節省資料塊空間。 
			
				1.1：CHAR型別 CHAR(size [BYTE | CHAR]) 
			
				CHAR型別，定長字串，會用空格填充來達到其最大長度。非NULL的CHAR（12）總是包含12位元組資訊。CHAR欄位最多可以儲存2,000位元組的資訊。如果建立表時，不指定CHAR長度，則預設為1。另外你可以指定它儲存位元組或字元，例如 CHAR(12 BYTYE) CHAR(12 CHAR).一般來說預設是儲存位元組，你可以檢視資料庫引數 
			
				NLS_LENGTH_SEMANTICS的值。 
			
					SQL Code
				
							SQL> show parameter nls_length_semantics; 
						
							NAME                   TYPE           VALUE 
						
							------------------   ----------- ----------------- 
						
							nls_length_semantics   string          BYTE 
						
							eg: 
						
							CREATE TABLE TEST 
						
							( 
						
							NAME_OLD CHAR(10), 
						
							NAME_NEW CHAR(10 CHAR) 
						
							) 
						
							INSERT INTO TEST 
						
							( NAME_OLD, NAME_NEW) 
						
							SELECT 'ABCDEFGHIJ' , '你清除位元組與字元' FROM DUAL; 
						
							COMMIT; 
						
							INSERT INTO TEST 
						
							( NAME_OLD, NAME_NEW) 
						
							SELECT '你清除位元組與字元' , 'ABCDEFGHIJ' FROM DUAL; 
						
							ORA-12899: 列 "SYS"."TEST"."NAME_OLD" 的值太大 (實際值: 24, 最大值: 10)  
						
			注意：資料庫的NLS_CHARACTERSET 為AL32UTF8，即一個漢字佔用三到四個位元組。如果NLS_CHARACTERSET為ZHS16GBK，則一個字元佔用兩個位元組。 
		
			如果串的長度小於或等於250（0x01~0xFA）， Oracle 會使用1 個位元組來表示長度。對於所有長度超過250 的串，都會在一個標誌位元組0xFE 後跟有兩個位元組來表示長度。因此，如果有一個包含“Hello World”的VARCHAR2(80)，則在塊中可能如圖12.-1 所示 
		
			1.2： NCHAR型別 
		
			這是一個包含UNICODE格式資料的定長字串。NCHAR欄位最多可以儲存2,000位元組的資訊。它的最大長度取決於國家字符集。另外查詢時，如果欄位是NCHAR型別，則需要如下書寫 
		
			SELECT translated_description FROM product_descriptions 
		
			WHERE translated_name = N'LCD Monitor 11/PM'; 
		
			1.3 VARCHAR型別 
		
			不要使用VARCHAR資料型別。使用VARCHAR2資料型別。雖然VARCHAR資料型別目前是VARCHAR2的同義詞，VARCHAR資料型別將計劃被重新定義為一個單獨的資料型別用於可變長度的字串相比，具有不同的比較語義。 
		
			1.4： VARCHAR2型別 
		
			變長字串，與CHAR型別不同，它不會使用空格填充至最大長度。VARCHAR2最多可以儲存4,000位元組的資訊。 
		
			1.5： NVARCHAR2型別 
		
			這是一個包含UNICODE格式資料的變長字串。 NVARCHAR2最多可以儲存4,000位元組的資訊。 
		
			二. 數字型別 
		
			2.1 NUMBER型別 
		
			NUMBER(P,S)是最常見的數字型別，可以存放資料範圍為10^130~10^126（不包含此值)，需要1~22位元組(BYTE)不等的儲存空間。 
		
			P 是Precison的英文縮寫，即精度縮寫，表示有效數字的位數，最多不能超過38個有效數字 
		
			S是Scale的英文縮寫，可以使用的範圍為-84~127。Scale為正數時，表示從小數點到最低有效數字的位數，它為負數時，表示從最大有效數字到小數點的位數 
		
			下面是官方文件的示例 
		
							Actual Data 
						
							Specified As 
						
							Stored As 
						
							123.89 
						
							NUMBER 
						
							123.89 
						
							123.89 
						
							NUMBER(3) 
						
							124 
						
							123.89 
						
							NUMBER(6,2) 
						
							123.89 
						
							123.89 
						
							NUMBER(6,1) 
						
							123.9 
						
							123.89 
						
							NUMBER(3) 
						
							124 
						
							123.89 
						
							NUMBER(4,2) 
						
							exceeds precision 
						
							123.89 
						
							NUMBER(6,-2) 
						
							100 
						
							.01234 
						
							NUMBER(4,5) 
						
							.01234 
						
							.00012 
						
							NUMBER(4,5) 
						
							.00012 
						
							.000127 
						
							NUMBER(4,5) 
						
							.00013 
						
							.0000012 
						
							NUMBER(2,7) 
						
							.0000012 
						
							.00000123 
						
							NUMBER(2,7) 
						
							.0000012 
						
							1.2e-4 
						
							NUMBER(2,5) 
						
							0.00012 
						
							1.2e-5 
						
							NUMBER(2,5) 
						
							0.00001 
						
			2.2 INTEGER型別 
		
			INTEGER是NUMBER的子型別，它等同於NUMBER（38,0），用來儲存整數。若插入、更新的數值有小數，則會被四捨五入。 
		
			例如： 
		
			CREATE TABLE TEST 
		
			( 
		
			ID INTEGER 
		
			) 
		
			檢視錶TEST的DDL定義如下所示 
		
			CREATE TABLE "SYS"."TEST" 
		
			( "ID" NUMBER(*,0) 
		
			) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING 
		
			STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 
		
			PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) 
		
			TABLESPACE "SYSTEM" ; 
		
			INSERT INTO TEST 
		
			SELECT 12.34 FROM DUAL; 
		
			INSERT INTO TEST 
		
			SELECT 12.56 FROM DUAL; 
		
			SQL> SELECT * FROM TEST; 
		
			ID 
		
			---------- 
		
			12 
		
			13 
		
			2.3 浮點數 
		
			浮點數可以有一個十進位制數點任何地方從第一個到最後一個數字，或者可以在所有有沒有小數點。指數可能（可選） 用於以下數量增加的範圍 （例如， 1.777e-20)。刻度值不適用於浮點數字，因為可以顯示在小數點後的位數的數量不受限制。 
		
			二進位制浮點數不同數量的值由 Oracle 資料庫內部儲存的方式。使用小數精度數儲存值。完全相同號碼儲存範圍和數量由支援的精度內的所有文字。正是因為使用小數精度（數字 0 到 9） 表示文字儲存文字。使用二進位制精度 （數字 0 和 1） 儲存二進位制浮點數。這種儲存方案不能代表所有確切地使用小數精度的值。頻繁地，將值從十進位制轉換為二進位制的精度時出現的錯誤時撤消值回從二進位制轉換為十進位制精度。在字面 0.1 是一個這樣的例子。 
		
			Oracle 資料庫提供了專為浮點數的兩種數值資料型別： 
		
			BINARY_FLOAT 
		
			BINARY_FLOAT 是 32 位、 單精度浮點數字資料型別。可以支援至少6位精度,每個 BINARY_FLOAT 的值需要 5 個位元組，包括長度位元組。 
		
			BINARY_DOUBLE 
		
			BINARY_DOUBLE 是為 64 位，雙精度浮點數字資料型別。每個 BINARY_DOUBLE 的值需要 9 個位元組，包括長度位元組。 
		
			在數字的列中，浮點數有小數精度。在 BINARY_FLOAT 或 BINARY_DOUBLE 的列中，浮點數有二進位制的精度。二進位制浮點數支援的特殊值無窮大和 NaN （不是數字）。 
		
			您可以指定列在表 2-4 範圍內的浮點數。"數字文字"中定義了用於指定浮點數的格式。 
		
			Table 2-3 Floating Point Number Limits 
		
							Value 
						
							Binary-Float 
						
							Binary-Double 
						
							Maximum positive finite value 
						
							3.40282E+38F 
						
							1.79769313486231E+308 
						
							Minimum positive finite value 
						
							1.17549E-38F 
						
							2.22507485850720E-308 
						
			2.5 FLOAT型別 
		
			FLOAT型別也是NUMBER的子型別。 
		
			Float(n),數 n 指示位的精度，可以儲存的值的數目。N 值的範圍可以從 1 到 126。若要從二進位制轉換為十進位制的精度，請將 n 乘以 0.30103。要從十進位制轉換為二進位制的精度，請用 3.32193 乘小數精度。126 位二進位制精度的最大值是大約相當於 38 位小數精度。 
		
			三. 日期型別 
		
			日期型別用於儲存日期資料，但是並不是使用一般的格式（2012-08-08）直接儲存到資料庫的。 
		
			3.1 DATE型別 
		
			DATE是最常用的資料型別，日期資料型別儲存日期和時間資訊。雖然可以用字元或數字型別表示日期和時間資訊，但是日期資料型別具有特殊關聯的屬性。為每個日期值，Oracle 儲存以下資訊： 世紀、 年、 月、 日期、 小時、 分鐘和秒。一般佔用7個位元組的儲存空間。 
		
			3.2 TIMESTAMP型別 
		
			這是一個7位元組或12位元組的定寬日期/時間資料型別。它與DATE資料型別不同，因為TIMESTAMP可以包含小數秒，帶小數秒的TIMESTAMP在小數點右邊最多可以保留9位 
		
			3.3 TIMESTAMP WITH TIME ZONE型別 
		
			這是TIMESTAMP型別的變種，它包含了時區偏移量的值 
		
			3.4 TIMESTAMP WITH LOCAL TIME ZONE型別 
		
			3.5 INTERVAL YEAR TO MOTH 
		
			3.6 INTERVAL DAY TO SECOND 
		
			四. LOB型別 
		
			內建的LOB資料型別包括BLOB、CLOB、NCLOB、BFILE（外部儲存）的大型化和非結構化資料，如文字、影像、視屏、空間資料儲存。BLOB、CLOB、NCLOB型別 
		
			4.1 CLOB 資料型別 
		
			   它儲存單位元組和多位元組字元資料。支援固定寬度和可變寬度的字符集。CLOB物件可以儲存最多 (4 gigabytes-1) * (database block size) 大小的字元 
		
			4.2 NCLOB 資料型別 
		
			   它儲存UNICODE型別的資料，支援固定寬度和可變寬度的字符集，NCLOB物件可以儲存最多(4 gigabytes-1) * (database block size)大小的文字資料。 
		
			4.3 BLOB 資料型別 
		
			   它儲存非結構化的二進位制資料大物件，它可以被認為是沒有字符集語義的位元流，一般是影像、聲音、視訊等檔案。BLOB物件最多儲存(4 gigabytes-1) * (database block size)的二進位制資料。 
		
			4.4 BFILE 資料型別 
		
			   二進位制檔案，儲存在資料庫外的系統檔案，只讀的，資料庫會將該檔案當二進位制檔案處理 
		
			五. RAW & LONG RAW型別 
		
			5.1 LONG型別 
		
			它儲存變長字串，最多達2G的字元資料（2GB是指2千兆位元組， 而不是2千兆字元），與VARCHAR2 或CHAR 型別一樣，儲存在LONG 型別中的文字要進行字符集轉換。ORACLE建議開發中使用CLOB替代LONG型別。支援LONG 列只是為了保證向後相容性。CLOB型別比LONG型別的限制要少得多。 LONG型別的限制如下： 
		
			1.一個表中只有一列可以為LONG型。(Why?有些不明白) 
		
			2.LONG列不能定義為主鍵或唯一約束， 
		
			3.不能建立索引 
		
			4.LONG資料不能指定正規表示式。 
		
			5.函式或儲存過程不能接受LONG資料型別的引數。 
		
			6.LONG列不能出現在WHERE子句或完整性約束（除了可能會出現NULL和NOT NULL約束） 
		
			官方文件描敘如下： 
		
			The use of LONG values is subject to these restrictions: 
		
			A table can contain only one LONG column. 
		
			You cannot create an object type with a LONG attribute. 
		
			LONG columns cannot appear in WHERE clauses or in integrity constraints (except that they can appear in NULL and NOT NULL constraints). 
		
			LONG columns cannot be indexed. 
		
			LONG data cannot be specified in regular expressions. 
		
			A stored function cannot return a LONG value. 
		
			You can declare a variable or argument of a PL/SQL program unit using the LONG datatype. However, you cannot then call the program unit from SQL. 
		
			Within a single SQL statement, all LONG columns, updated tables, and locked tables must be located on the same database. 
		
			LONG and LONG RAW columns cannot be used in distributed SQL statements and cannot be replicated. 
		
			If a table has both LONG and LOB columns, then you cannot bind more than 4000 bytes of data to both the LONG and LOB columns in the same SQL statement. However, you can bind more than 4000 bytes of data to either the LONG or the LOB column. 
		
			In addition, LONG columns cannot appear in these parts of SQL statements: 
		
			GROUP BY clauses, ORDER BY clauses, or CONNECT BY clauses or with the DISTINCT operator in SELECT statements 
		
			The UNIQUE operator of a SELECT statement 
		
			The column list of a CREATE CLUSTER statement 
		
			The CLUSTER clause of a CREATE MATERIALIZED VIEW statement 
		
			SQL built-in functions, expressions, or conditions 
		
			SELECT lists of queries containing GROUP BY clauses 
		
			SELECT lists of subqueries or queries combined by the UNION, INTERSECT, or MINUS set operators 
		
			SELECT lists of CREATE TABLE ... AS SELECT statements 
		
			ALTER TABLE ... MOVE statements 
		
			SELECT lists in subqueries in INSERT statements 
		
			5.2 LONG RAW 型別，能儲存2GB 的原始二進位制資料（不用進行字符集轉換的資料） 
		
			5.3 RAW型別 
		
			用於儲存二進位制或字元型別資料，變長二進位制資料型別，這說明採用這種資料型別儲存的資料不會發生字符集轉換。這種型別最多可以儲存2,000位元組的資訊 
		
			六. ROWID & UROWID型別 
		
			在資料庫中的每一行都有一個地址。然而，一些錶行的地址不是物理或永久的，或者不是ORACLE資料庫生成的。 
		
			例如，索引組織錶行地址儲存在索引的葉子，可以移動。 
		
			例如,外部表的ROWID（如通過閘道器訪問DB2表）不是標準的ORACLE的rowid。 
		
			ORACLE使用通用的ROWID（UROWIDs）的儲存地址的索引組織表和外表。索引組織表有邏輯urowids的，和國外表的外urowids，。UROWID這兩種型別的儲存在ROWID偽（堆組織的表的物理行id）。 
		
			建立基於邏輯的rowid在表中的主鍵。邏輯的rowid不會改變，只要主鍵不改變。索引組織表的ROWID偽UROWID資料型別。你可以訪問這個偽列，你會堆組織表的ROWID偽（即使用一個SELECT ...ROWID語句）。如果你想儲存的rowid索引組織表，那麼你就可以定義一列的表型UROWID到列檢索值的ROWID偽。

Oracle 11g 資料型別

					1.     字元型別
				
									資料型別
								
									長度
								
									說明
								
									CHAR(n BYTE/CHAR)
								
									預設1位元組，n值最大為2000
								
									末尾填充空格以達到指定長度，超過最大長度報錯。預設指定長度為位元組數，字元長度可以從1位元組到四位元組。
								
									NCHAR(n)
								
									預設1字元，最大儲存內容2000位元組
								
									末尾填充空格以達到指定長度，n為Unicode字元數。預設為1位元組。
								
									NVARCHAR2(n)
								
									最大長度必須指定，最大儲存內容4000位元組
								
									變長型別。n為Unicode字元數
								
									VARCHAR2(n BYTE/CHAR)
								
									最大長度必須指定，至少為1位元組或者1字元，n值最大為4000
								
									變長型別。超過最大長度報錯。預設儲存的是長度為0的字串。
								
									VARCHAR
								
									同VARCHAR2
								
									不建議使用
								
					2.     數字型別
				
									資料型別
								
									長度
								
									說明
								
									NUMBER(p[,s])
								
									1-22位元組。
								
									P取值範圍1到38
								
									S取值範圍-84到127
								
									儲存定點數，值的絕對值範圍為1.0 x 10 -130至1.0 x 10 126。值大於等於1.0 x 10 126時報錯。p為有意義的10進位制位數，正值s為小數位數，負值s表示四捨五入到小數點左部多少位。
								
									BINARY_FLOAT
								
									5位元組，其中有一長度位元組。
								
									32位單精度浮點數型別。
								
									符號位1位，指數位8位，尾數位23位。
								
									BINARY_DOUBLE
								
									9位元組，其中有一長度位元組。
								
									64位雙精度浮點數型別。
								
					3.     時間、時間間隔型別
				
					時間欄位可取值範圍： 
				
									時間欄位
								
									時間型別有效值
								
									時間間隔型別有效值
								
									YEAR
								
									-4712至9999，包括0
								
									任何整數
								
									MONTH
								
									01至12
								
									0至11
								
									DAY
								
									01至31
								
									任何整數
								
									HOUR
								
									00 至 23
								
									0 至 23
								
									MINUTE
								
									00 至 59
								
									0至 59
								
									SECOND
								
									00 to 59.9(n)，9(n)不適用與DATE型別
								
									0 to 59.9(n)
								
									TIMEZONE_HOUR
								
									-1至14，不適用與DATE和TIMESTAMP型別
								
									不可用
								
									TIMEZONE_MINUTE
								
									00至59，不適用與DATE和TIMESTAMP型別
								
									不可用
								
									TIMEZONE_REGION
								
									不可用
								
									TIMEZONE_ABBR
								
									不可用
								
					時間、時間間隔型別： 
				
									資料型別
								
									長度
								
									說明
								
									DATE
								
									7位元組
								
									預設值為SYSDATE的年、月，日為01。包含一個時間欄位，若插入值沒有時間欄位，則預設值為：00:00:00 or 12:00:00 for 24-hour and 12-hour clock time。沒有分秒和時間區。
								
									TIMESTAMP [(fractional_seconds_precision)]
								
									7至11位元組
								
									fractional_seconds_precision為Oracle儲存秒值小數部分位數，預設為6，可選值為0到9。沒有時間區。
								
									TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE
								
									13位元組
								
									使用UTC，包含欄位YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_
								
									HOUR, TIMEZONE_MINUTE
								
									TIMESTAMP [(fractional_seconds_precision)] WITH LOCAL TIME ZONE
								
									7至11位元組 
								
									存時使用資料庫時區，取時使用回話的時區。
								
									INTERVAL YEAR [(year_precision)] TO MONTH
								
									5位元組
								
									包含年、月的時間間隔型別。year_precision是年欄位的數字位數，預設為2，可取0至9。
								
									INTERVAL DAY [(day_precision)]
								
									TO SECOND [(fractional_seconds_precision)]
								
									11位元組
								
									day_precision是月份欄位的數字位數，預設為2，可取0至9。
								
						TO_DATE()、DATE使用的時間欄位值都是午夜值。或者使用TRUNC()函式進行過濾，確保時間欄位為午夜值。
					
						時間和時間間隔型別操作規則：
					
					在DATE和TIMESTAMP（會被轉化為DATE型別值）型別上加、減NUMBER型別常量，該常量單位為天數。
				
					所有TIMESTAMP型別運算都以UTC時間為準。即對於TIMESTAMP WITH LOCAL TIME ZONE來說，先轉化為UTC時間，計算完成後再轉化回來。
				
						INTERVAL YEAR TO MONTH常量：
					
					INTERVAL‘year-month’YEAR/MONTH(precision) TO MONTH
				
					year位數超過precision時，返回一個錯誤。
				
					其中precision為最大的位數，預設為2，可取0到9。
				
					例子：INTERVAL '123-2' YEAR(3) TO MONTH 、
				
					INTERVAL '123' YEAR(3) 、
				
					INTERVAL '300' MONTH(3)。
				
						INTERVAL DAY TO SECOND常量：
					
					INTERVAL ‘n/time_expr/n time_expr’ DAY/HOUR/MINUTE(leading_precision)  TO  HOUR/MINUTE/SECOND(fractional_second_precision)
				
					INTERVAL ‘n/time_expr’ SECOND(leading_precision, fractional_second_precision) 
				
					time_expr格式：HH[:MI[:SS[.n]]] or MI[:SS[.n]] or SS[.n] 若n大於分秒精度，則四捨五入n。
				
					只有當第一個欄位是DAY時，才可以使用n time_expr。
				
					leading_precision預設為2，可取0至9。
				
					4.     大物件型別
				
									資料型別
								
									長度
								
									說明
								
									BLOB
								
									最大為(4GB-1)*資料庫塊大小
								
									儲存非結構化二進位制檔案。支援事務處理。
								
									CLOB
								
									最大為(4GB-1)*資料庫塊大小
								
									儲存單位元組或者多位元組字元資料。支援事務處理。
								
									NCLOB
								
									最大為(4GB-1)*資料庫塊大小
								
									儲存Unicode資料。支援事務處理。
								
									BFILE
								
									最大為2 32-1位元組
								
									LOB地址指向檔案系統上的一個二進位制檔案，維護目錄和檔名。不參與事務處理。只支援只讀操作。
								
						LOB列包含一個LOB地址，指向資料庫內或者資料庫外的LOB型別值。
					
					5.     其他型別
				
									資料型別
								
									長度
								
									說明
								
									LONG
								
									最大為2GB
								
									變長型別，儲存字串。建立表時不要使用該型別。
								
									RAW(n)
								
									最大2000位元組，n為位元組數，必須指定n
								
									變長型別，字符集發生變化時不會改變值。
								
									LONG RAW
								
									最大為2GB
								
									變長型別，不建議使用，建議轉化為BLOB型別，字符集發生變化時不會改變值。
								
									ROWID
								
									10位元組
								
									代表記錄的地址。顯示為18位的字串。用於定位資料庫中一條記錄的一個相對唯一地址值。通常情況下，該值在該行資料插入到資料庫表時即被確定且唯一。
								
									UROWID(n)
								
						ROWID：資料物件編號32位(6個字元)、檔案編號10位(3個字元)、塊編號22位(6個字元)、行編號16位(3個字元)
					
						使用dqms_rowid包獲得ROWID資訊：
					
					select dbms_rowid.rowid_object(rowid) object_id,                dbms_rowid.rowid_relative_fno(rowid) file_id,                                                                   dbms_rowid.rowid_block_number(rowid) block_id,                                                                  dbms_rowid.rowid_row_number(rowid) num

About Me

.............................................................................................................................................

● 本文作者：小麥苗，部分內容整理自網路，若有侵權請聯絡小麥苗刪除

● 本文在itpub（http://blog.itpub.net/26736162/abstract/1/）、部落格園（http://www.cnblogs.com/lhrbest）和個人微信公眾號（xiaomaimiaolhr）上有同步更新

● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/

● 本文部落格園地址：http://www.cnblogs.com/lhrbest

● 本文pdf版、個人簡介及小麥苗雲盤地址：http://blog.itpub.net/26736162/viewspace-1624453/

● 資料庫筆試面試題庫及解答：http://blog.itpub.net/26736162/viewspace-2134706/

● DBA寶典今日頭條號地址：http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826

.............................................................................................................................................

● QQ群號：230161599（滿）、618766405

● 微信群：可加我微信，我拉大家進群，非誠勿擾

● 聯絡我請加QQ好友（646634621），註明新增緣由

● 於 2018-01-01 06:00 ~ 2018-01-31 24:00 在魔都完成

● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解

● 版權所有，歡迎分享本文，轉載請保留出處

.............................................................................................................................................

● 小麥苗的微店：https://weidian.com/s/793741433?wfr=c&ifr=shopdetail

● 小麥苗出版的資料庫類叢書：http://blog.itpub.net/26736162/viewspace-2142121/

● 好訊息：小麥苗OCP、OCM開班啦，詳情請點選：http://blog.itpub.net/26736162/viewspace-2148098/

.............................................................................................................................................

使用微信客戶端掃描下面的二維碼來關注小麥苗的微信公眾號（xiaomaimiaolhr）及QQ群（DBA寶典），學習最實用的資料庫技術。

小麥苗的微信公眾號小麥苗的DBA寶典QQ群2 《DBA筆試面寶典》讀者群小麥苗的微店

.............................................................................................................................................

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2149685/，如需轉載，請註明出處，否則將追究法律責任。

Actual Data	Specified As	Stored As
123.89	NUMBER	123.89
123.89	NUMBER(3)	124
123.89	NUMBER(6,2)	123.89
123.89	NUMBER(6,1)	123.9
123.89	NUMBER(3)	124
123.89	NUMBER(4,2)	exceeds precision
123.89	NUMBER(6,-2)	100
.01234	NUMBER(4,5)	.01234
.00012	NUMBER(4,5)	.00012
.000127	NUMBER(4,5)	.00013
.0000012	NUMBER(2,7)	.0000012
.00000123	NUMBER(2,7)	.0000012
1.2e-4	NUMBER(2,5)	0.00012
1.2e-5	NUMBER(2,5)	0.00001

Value	Binary-Float	Binary-Double
Maximum positive finite value	3.40282E+38F	1.79769313486231E+308
Minimum positive finite value	1.17549E-38F	2.22507485850720E-308

資料型別	長度	說明
CHAR(n BYTE/CHAR)	預設1位元組，n值最大為2000	末尾填充空格以達到指定長度，超過最大長度報錯。預設指定長度為位元組數，字元長度可以從1位元組到四位元組。
NCHAR(n)	預設1字元，最大儲存內容2000位元組	末尾填充空格以達到指定長度，n為Unicode字元數。預設為1位元組。
NVARCHAR2(n)	最大長度必須指定，最大儲存內容4000位元組	變長型別。n為Unicode字元數
VARCHAR2(n BYTE/CHAR)	最大長度必須指定，至少為1位元組或者1字元，n值最大為4000	變長型別。超過最大長度報錯。預設儲存的是長度為0的字串。
VARCHAR	同VARCHAR2	不建議使用

時間欄位	時間型別有效值	時間間隔型別有效值
YEAR	-4712至9999，包括0	任何整數
MONTH	01至12	0至11
DAY	01至31	任何整數
HOUR	00 至 23	0 至 23
MINUTE	00 至 59	0至 59
SECOND	00 to 59.9(n)，9(n)不適用與DATE型別	0 to 59.9(n)
TIMEZONE_HOUR	-1至14，不適用與DATE和TIMESTAMP型別	不可用
TIMEZONE_MINUTE	00至59，不適用與DATE和TIMESTAMP型別	不可用
TIMEZONE_REGION		不可用
TIMEZONE_ABBR		不可用

Oracle資料型別介紹

Oracle資料型別介紹

Oracle的資料型別

ORACLE基本資料型別總結

Oracle 11g 資料型別

1. 字元型別

2. 數字型別

3. 時間、時間間隔型別

4. 大物件型別

5. 其他型別

相關文章