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，從而彌補了浮點數性能的問題。 

variable_name number[([P][, S])] = 0;
  • NUMBER可選指定precision (P)與scale (S)。精度表示十進制有效數字的個數，最多不能超過38個有效數字（實際支持39-40位十進制數字）[注 1]。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)標誌字節數組的結束。[1]
      • 兩個字節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格式數據的變長字符串
--  字段translated_name是NCHAR类型，则需要如下书写：
SELECT translated_description FROM product_descriptions 
WHERE translated_name = N'LCD Monitor 11/PM';  

variable_name varchar2(20) = 'Text';

-- e.g.: 
address varchar2(20) := 'lake view road';

日期類型

variable_name date = to_date('01-01-2005 14:20:23', 'DD-MM-YYYY hh24:mi:ss');
  • 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_date('31-12-2004', 'dd-mm-yyyy')

 to_date ('31-Dec-2004', 'dd-mon-yyyy', 'NLS_DATE_LANGUAGE = American')

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

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

示例： 

WHERE dateField BETWEEN DATE '2004-12-30' - INTERVAL '1-6' YEAR TO MONTH
    AND DATE '2004-12-30'
create table T
(
   C1 DATE,
   C2 TIMESTAMP(9)
);
 
insert into t(c1,c2) values(date'2010-2-12',timestamp'2010-2-12 13:24:52.234123211');
insert into t(c1,c2) values(
        to_date('2010-2-12 10:20:30','YYYY-MM-DD HH24:MI:SS'),
        to_timestamp('2010-2-12 13:24:52.123456','YYYY-MM-DD HH24:MI:SS.FF6')
);
 
SQL> select c1,dump(c1) c1_d,c2,dump(c2) c2_d from t;
  • 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;

自定義類型

程式設計師自定義類型： 

type data_type is record (field_1 type_1 = xyz, field_2 type_2 := xyz, ..., field_n type_n = xyz);

例如： 

declare
    type t_address is record (
        name address.name%type,
        street address.street%type,
        street_number address.street_number%type,
        postcode address.postcode%type);
    v_address t_address;
begin
    select name, street, street_number, postcode into v_address from address where rownum = 1;
end;

可以使用點表示（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結構。 

IF x = 1 THEN
   sequence_of_statements_1;
ELSIF x = 2 THEN
   sequence_of_statements_2;
ELSIF x = 3 THEN
   sequence_of_statements_3;
ELSIF x = 4 THEN
   sequence_of_statements_4;
ELSIF x = 5 THEN
   sequence_of_statements_5;
ELSE
   sequence_of_statements_N;
END IF;

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

CASE
   WHEN x = 1 THEN sequence_of_statements_1;
   WHEN x = 2 THEN sequence_of_statements_2;
   WHEN x = 3 THEN sequence_of_statements_3;
   WHEN x = 4 THEN sequence_of_statements_4;
   WHEN x = 5 THEN sequence_of_statements_5;
   ELSE sequence_of_statements_N;
END CASE;

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

CASE x
   WHEN 1 THEN sequence_of_statements_1;
   WHEN 2 THEN sequence_of_statements_2;
   WHEN 3 THEN sequence_of_statements_3;
   WHEN 4 THEN sequence_of_statements_4;
   WHEN 5 THEN sequence_of_statements_5;
   ELSE sequence_of_statements_N;
END 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的程序語言和其他關係型資料庫：

註釋

  1. ↑ p是精度，即十進制數字的有效位數，其中最重要有效數字是在最左邊的非零數字，最不重要有效數字是最右邊的數字。Oracle保證數字可移植性，其精度可達基於100進制的20個數字（centesimal digit），這等效於依賴小數點位置的39位或40位十進制數字。原文：p is the precision, or the total number of significant decimal digits, where the most significant digit is the left-most nonzero digit, and the least significant digit is the right-most known digit. Oracle guarantees the portability of numbers with precision of up to 20 base-100 digits, which is equivalent to 39 or 40 decimal digits depending on the position of the decimal point.

參考文獻

  1. ↑ Oracle Ducuments about Data Type. [2017-08-14]. 
  2. ↑ Literals. Oracle Database SQL Reference 10g Release 2 (10.2). Oracle. [2009-03-20]. 
  3. ↑ SQL PL

參見

數據庫管理系統（DBMS）
概念
數據庫組件
SQL
分類
指令
安全
實現
類型
數據庫產品
數據庫組件
取自 "https://www.qiuwenbaike.cn/index.php?title=PL-SQL&oldid=6462706"