PL-SQL

**PL/SQL**（Procedural Language/SQL）是甲骨文公司專有的 SQL 擴展語言，應用在甲骨文公司的 Oracle 資料庫系統。一些的 SQL 資料庫管理系統也提供了類似的擴展 SQL 語言。PL/SQL 的的語法非常類似於 Ada，而且像 1980 年代的 Ada 編譯器一樣，PL/SQL 的運作系統使用 Diana 作為中介語言。

重要的是 PL/SQL 緊密的結合在 Oracle 資料庫裡面。

PL/SQL 是 Oracle 資料庫使用的三種語言的其中之一，另外兩個是 SQL 和 Java。

## 歷史

## 特性

### SQL 連接操作

Oracle 連接操作 (left join ,right join,full join) 的語法確與 SQL 標準完全不同，沒有左連接與右連接的概念，也不支持全外連接。Oracle 語法如下所示：

```
 select * from t1,t2 where t1.id=t2.id(+)
```

採用 (+) 來表示外連接，在 Oracle 中它相當於左連接。Oracle9i 中增加了標準外連接的語法支持，但使用不廣。

### 物化視圖

物化視圖（materialized view）或譯為實體化視圖。與普通視圖關係不同，物化視圖更像是一個表，保存了實實在在的數據，並且可以與表一樣定義存儲參數，可以與表一樣使用 select,insert,update,delete。在其它資料庫中也有和物化視圖相似的解決方案，DB2 叫物化查詢表 (materialized query table)，sqlserver 有索引視圖，但是索引視圖僅是起優化作用，與 oracle 的物化視圖還不太一樣。

## 數據類型

### 數值類型

採用本地的 number 類型做指數或對數運算，與標準的浮點數性能可能會相差 50 倍。好在 Oracle10g 中增加了高效的浮點類型 binary\_float,binary\_double，從而彌補了浮點數性能的問題。

* NUMBER 可選指定 precision (P) 與 scale (S)。精度表示十進位有效數字的個數，最多不能超過 38 個有效數字（實際支持 39-40 位十進位數字）。Scale 的範圍為 [-84，127]。Scale 為正數時，表示從小數點到最不重要的十進位有效數字的個數；為負數時，其絕對值表示從最不重要的十進位有效數字到小數點的位數。如果沒有指定精度，precision 與 scale 默認為最大的取值區間。如果指定了精度，沒有指定 scale，scale 默認為 0。內部存儲格式是變長阿拉伯數字的字節數組:
  * 首字節為長度值，最大 22；如果為 NULL，則該字節值為 255 (0xFF)
  * 第二字節是符號和指數字節 (sign bit/exponent)，其最高比特為符號位，1 表示正數，0 表示負數；其餘 7 比特構成基為 100 的指數值，取值範圍 [-65，62]，NUMBER 數據類型的取值範圍為 [10-130,10126)；
    * 第二字節值大於 128，則：指數值 = 字節值 - 128 - 64= 字節值 - 192，即去除符號比特後偏移了 64。字節值最大為 254
    * 第二字節值等於 128，則 NUMBER 數據類型表示值 0
    * 第二字節值小於 128，則：指數值 =(255 - 字節值)-128-64=63-ZV，即取反後去除符號比特再偏移 64
  * 其餘字節保存了基數為 100 的數值 00-99
    * 對於正數：實際值＝存儲值－1
    * 對於負數：實際值＝存儲值 - 101；字節值 102 (0x66) 標誌字節數組的結束。
    * 兩個字節 255 與 101 表示正無窮
    * 單字節 0 表示負無窮
* INTEGER 是 NUMBER 的子類型，它等同於 NUMBER（38,0），用來存儲整數。若插入、更新的數值有小數，則會被四捨五入。
* FLOAT 類型也是 NUMBER 的子類型。Float (n), 數 n 指示位的精度，可以存儲的值的數目。n 值的範圍可以從 1 到 126。若要從二進位轉換為十進位的精度，請將 n 乘以 0.30103。要從十進位轉換為二進位的精度，請用 3.32193 乘小數精度。126 位二進位精度的最大值是大約相當於 38 位小數精度。
* BINARY\_FLOAT 是 32 位、 單精度浮點數字數據類型。可以支持至少 6 位精度，每個 BINARY\_FLOAT 的值需要 5 個字節，包括長度字節。
* BINARY\_DOUBLE 是為 64 位，雙精度浮點數字數據類型。每個 BINARY\_DOUBLE 的值需要 9 個字節，包括長度字節。

其它數值類型：
dec, decimal, double precision, int, numeric, real, smallint, binary\_integer.

### 字符類型

字符串數據類型依據存儲空間分為兩種：

* 固定長度類型：CHAR/NCHAR，自動補足空格，最多可以存儲 2,000 位元組
* 可變長度類型：VARCHAR2/NVARCHAR2，最大字節數都是 4000，自動刪除首尾的空格

串的開頭存儲了串的長度。如果串的長度小於或等於 250（0x01~0xFA）， Oracle 會使用 1 個字節來表示長度。對於所有長度超過 250 的串，都會在一個標誌字節 0xFE 後跟有兩個字節來表示長度。

chr (0) 表示的不可見字符，即我們通常所說的 \0

* CHAR 類型： CHAR (size [BYTE | CHAR]) 固定長度字符串；
* NCHAR 類型： 根據字符集而定的 UNICODE 格式固定長度字符串 最大長度 2000 bytes。
* VARCHAR 類型：不建議使用。雖然 VARCHAR 數據類型目前是 VARCHAR2 的同義詞，VARCHAR 數據類型將被重新定義為一個單獨的數據類型用於可變長度的字符串相比，與 VARCHAR2 具有不同的比較語義
* varchar2 類型：變長字符串
* nvarchar2 () 類型：包含 UNICODE 格式數據的變長字符串

### 日期類型

* Date 類型可以表示日期與時間。精度到秒。日期範圍可以是公元前 4712 年 1 月 1 日至公元 9999 年 12 月 31 日。占用 7 個字節的存儲空間。第 1 位元組：世紀 + 100；第 2 位元組：年； 第 3 位元組：月； 第 4 位元組：天； 第 5 位元組：小時 + 1； 第 6 位元組：分 + 1；第 7 位元組：秒 + 1。其中時間可以忽略。但無法只表示時間而忽略日期。Oracle Datatypes（頁面存檔備份，存於網際網路檔案館）
* TIMESTAMP 類型：7 位元組或 11 位元組的定寬日期 / 時間數據類型。可以包含小數秒，小數位數可以指定為 0-9，默認為 6 位，所以最高精度可以到 ns (奈秒). 如果精度為 0，則用 7 位元組存儲，與 date 類型功能相同，如果精度大於 0 則用 11 位元組存儲。
* TIMESTAMP WITH TIME ZONE 類型：TIMESTAMP 類型的變種，它包含了時區偏移量的值
* TIMESTAMP WITH LOCAL TIME ZONE 類型：
* INTERVAL YEAR TO MOTH：
* INTERVAL DAY TO SECOND：

函數 `TO_DATE` 把字符串轉換為日期值。

函數 `TO_CHAR (date_string, format_string)` 把日期值轉換為字符串。

PL/SQL 支持使用 ANSI 日期與時間間隔值 The following clause gives an 18-month range:

示例：

* sysdate-- 返回當前系統日期和時間，精確到秒
* systimestamp-- 返回當前系統日期和時間，精確到毫秒

日期型數據可以與數值加減得到新的日期，加減數值單位為天

* sysdate+1-- 取明天的當前時間
* sysdate-1/24-- 取當前時間的前一個小時

### LOB 類型

內置的 LOB 數據類型包括 BLOB、CLOB、NCLOB、BFILE（外部存儲）的大型化和非結構化數據，如文本、圖像、視屏、空間數據存儲。

* BLOB 數據類型：存儲非結構化的二進位數據大對象，它可以被認為是沒有字符集語義的比特流，一般是圖像、聲音、視頻等文件。BLOB 對象最多存儲 (4 gigabytes-1) \* (database block size) 的二進位數據。
* CLOB 數據類型：存儲單字節和多字節字符數據。支持固定寬度和可變寬度的字符集。CLOB 對象可以存儲最多 (4 gigabytes-1) \* (database block size) 大小的字符
* NCLOB 數據類型：存儲 UNICODE 類型的字符數據，支持固定寬度和可變寬度的字符集，NCLOB 對象可以存儲最多 (4 gigabytes-1) \* (database block size) 大小的文本數據。
* BFILE 數據類型：存儲在資料庫外的二進位文件，只讀，最大長度 4G

### LONG 類型，RAW 類型，LONG RAW 類型

均為較老的數據類型，Oracle 不建議使用。

* LONG 類型存儲變長字符串，最多達 2G 的字節數據。存儲在 LONG 類型中的文本要進行字符集轉換。支持 LONG 列只是為了保證向後兼容性。LONG 類型的限制如下：
  * 一個表中只有一列可以為 LONG 型。
  * LONG 列不能定義為主鍵或唯一約束
  * 不能建立索引
  * LONG 數據不能指定正則表達式。
  * 函數或存儲過程不能接受 LONG 數據類型的參數。
  * LONG 列不能出現在 WHERE 子句或完整性約束（除了可能會出現 NULL 和 NOT NULL 約束）
* LONG RAW 類型，能存儲 2GB 的原始二進位數據（不用進行字符集轉換的數據）
* RAW 類型用於存儲二進位或字符類型數據，變長二進位數據類型，這說明採用這種數據類型存儲的數據不會發生字符集轉換。這種類型最多可以存儲 2,000 位元組的信息

### ROWID & UROWID 類型

在資料庫中的每一行都有一個地址。然而，一些表行的地址不是物理或永久的，或者不是 ORACLE 資料庫生成的。例如，索引組織表行地址存儲在索引的葉子，可以移動。例如，外部表的 ROWID（如通過網關訪問 DB2 表）不是標準的 ORACLE 的 rowid。

ORACLE 使用通用的 ROWID（UROWIDs）的存儲地址的索引組織表和外表。10 個字節，格式為： \*\*\*\*\*\*\*\*.\*\*\*\*.\*\*\*\*，\* 為 0 或 1。NROWID 類型為二進位數據表中記錄的唯一行號，最大長度 4000 位元組

索引組織表有邏輯 urowids 的，和外表的外 urowids。UROWID 這兩種類型的存儲在 ROWID（堆組織的表的物理行 id）。

創建基於邏輯的 rowid 在表中的主鍵。邏輯的 rowid 不會改變，只要主鍵不改變。索引組織表的 ROWID 偽 UROWID 數據類型。你可以訪問這個偽列，你會堆組織表的 ROWID 偽（即使用一個 SELECT ...ROWID 語句）。如果你想存儲的 rowid 索引組織表，那麼你就可以定義一列的表型 UROWID 到列檢索值的 ROWID 偽。

### 指定列的數據類型

定義一個變量，其類型是指定表的指定列的數據類型：

```
Variable_name Table\_name.Column_name%type;
```

### 自定義類型

程式設計師自定義類型：

例如：

可以使用點表示（dot-notation）獲取結構中的域：

```
v_address.street = 'High Street';"
```

### 自增長數據類型

Oracle 的數據類型里沒有自增長（auto-incremental）欄位類型，Oracle 的官方解決方案是採用 sequence 實現。insert 的時候需要用 sequence.nextval。需要增加一張專用表來保存自增長欄位的表和欄位名，每次新增記錄時都把這個記錄值加 1 再取出使用。

## 基本程式

## 條件語句

以下的代碼展示了 IF-THEN-ELSIF 結構。ELSIF 和 ELSE 部分是可選的，從而可以創建更簡單的 IF-THEN 或者 IF-THEN-ELSE 結構。

CASE 語句簡化了一些大的 IF-THEN-ELSE 結構。

CASE 語句可以使用預定義的選擇符：

## 陣列

## 迴圈

比較常用的有三種形式。

```
For ... Loop
   ...
   End Loop;
```

loop 循環是最簡單的循環語句，用於無限制的循環執行語句：

```
Loop
   ...
   End Loop;
```

```
while...loop;
```

如果退出循環，則必須使用 exit 語句終止循環。exit 語句分兩種格式：

```
exit：该格式的语句用于无条件强迫终止循环。
exit...when：该格式用于有条件终止循环，首先检测when的条件是否满足。
```

例 1：

```
loop
    if...then exit；
    end if；
end loop；
```

例 2：

```
loop
   exit when；
end loop；
```

while...loop 循環在執行語句時，首先檢測條件的值。例如：

```
declare
  div_name varchar2(20);
  div_num integer=1;
begin
  while div_num<10 loop
    select name into div_name from div_tab where div_author ='A000'||to_char(div_num);
    div_num=div_num+1;
  end loop;
end;
```

for...loop 循環可以限定循環的次數例如：

```
declare
  div_name varchar2(20);
  div_num integer:=1;
begin
  for div_num in 1..9 loop
      select name into div_name from div_tab where div_author='A000'||to_char(div_num);
  end loop;
end;
```

例如：

```
declare
   div_name varchar2(20);
   div_num integer=1;
begin
   for div_num in 1..9 loop
        EXIT WHEN div_num >7;
        DBMS_OUTPUT.put_line(div_num);
   end loop;
end;
```

## 類似的語言

功能近似 PL/SQL 的程序語言和其他關係型資料庫：

* Sybase ASE
* Microsoft SQL Server 的 Transact-SQL
* PostgreSQL 資料庫的 PL/pgSQL（模仿 PL/SQL）
* IBM DB2 的 SQL PL
* 符合 ISO SQL 的 SQL/PSM 標準。

## 注釋

## 參考文獻

## 外部連結

## 參見

* Oracle 資料庫
* SQL
* Transact-SQL
* 關聯式資料庫管理系統