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;

陣列[編輯]

PL/SQL 將數組稱為「集合」（collection）。 該語言提供三種類型的集合：

  • 關聯數組（索引表）
  • 嵌套表
  • Varray（可變大小數組）

程式設計師必須為變量指定上限，但不需要為索引表或嵌套表指定上限。 該語言包含多種用於操作集合元素的集合方法：例如 FIRST、LAST、NEXT、PRIOR、EXTEND、TRIM、DELETE 等。索引表可用於模擬關聯數組。

關聯數組（索引表）[編輯]

對於索引表，可以通過數字或字符串對數組進行索引。 它與Java映射類似，由鍵值對組成。 只有一維且無界。

嵌套表[編輯]

對於嵌套表（英語：Nested SQL），程式設計師需要了解嵌套的內容。 在這裡，創建了一個可能由多個組件組成的新類型。 然後可以使用該類型在表中創建一列，並在該列中嵌套這些組件。 With nested tables the programmer needs to understand what is nested. Here, a new type is created that may be composed of a number of components. That type can then be used to make a column in a table, and nested within that column are those components.

Varray（可變大小數組）[編輯]

對於 Varray，需要了解「可變大小數組」短語中的「變量」一詞並不像想像的那樣適用於數組的大小。 聲明數組的大小實際上是固定的。 數組中元素的數量是可變的，最多可達聲明的大小。 可以說，可變大小的數組的大小並不是那麼可變。

迴圈[編輯]

從過程語言的，PL/SQL提供了多種迭代結構，包括基本的LOOP語句、WHILE 循環、FOR循環和Cursor FOR循環。 從 Oracle 7.3 開始，引入了REF CURSOR 類型，以允許從存儲過程和函數返回記錄集。 Oracle 9i引入了預定義的SYS_REFCURSOR類型，這意味著我們不再需要定義自己的 REF CURSOR 類型。

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

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

例1： 

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

例2： 

loop
   exit when；
end loop；

LOOP語句[編輯]

<<parent_loop>>
LOOP
	statements

	<<child_loop>>
	loop
		statements
		exit parent_loop when <condition>; -- Terminates both loops
		exit when <condition>; -- Returns control to parent_loop
	end loop child_loop;
        if <condition> then
           continue; -- continue to next iteration
        end if;

	exit when <condition>;
END LOOP parent_loop;

[3]

Loop可用EXIT關鍵字或拋出異常來終止。

FOR循環[編輯]

DECLARE
    var NUMBER;
BEGIN
    /* N.B. for loop variables in PL/SQL are new declarations, with scope only inside the loop */
    FOR var IN 0 .. 10 LOOP
        DBMS_OUTPUT.PUT_LINE(var);
    END LOOP;

    IF var IS NULL THEN
        DBMS_OUTPUT.PUT_LINE('var is null');
    ELSE
        DBMS_OUTPUT.PUT_LINE('var is not null');
    END IF;
END;

輸出: 

 0
 1
 2
 3
 4
 5
 6
 7
 8
 9
 10
 var is null


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;

Cursor FOR循環[編輯]

FOR RecordIndex IN (SELECT person_code FROM people_table)
LOOP
  DBMS_OUTPUT.PUT_LINE(RecordIndex.person_code);
END LOOP;

Cursor-for循環自動打開一個cursor，讀入其數據並再次關閉游標。

作為替代方案，PL/SQL 程式設計師可以提前預定義游標的 SELECT 語句，以（例如）允許重用或使代碼更易於理解（在長查詢或複雜查詢的情況下特別有用）。 

DECLARE
  CURSOR cursor_person IS
    SELECT person_code FROM people_table;
BEGIN
  FOR RecordIndex IN cursor_person
  LOOP
    DBMS_OUTPUT.PUT_LINE(recordIndex.person_code);
  END LOOP;
END;

FOR 循環中 person_code 的概念用點符號（「.」）表示: 

RecordIndex.person_code

while循環[編輯]

while...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;

游標[編輯]

游標是指向私有SQL區域的指針，該區域存儲來自SELECT或數據操作語言(DML)語句（INSERT、UPDATE、DELETE 或 MERGE）的信息。游標保存SQL語句返回的行（一行或多行）。游標所保存的行集稱為活動集（active set）。[4]

游標可以是顯式的或隱式的。在FOR循環中，如果要重用查詢，則應使用顯式游標，否則首選隱式游標。如果在循環內使用游標，則在需要批量收集或需要動態SQL時，建議使用 FETCH。 

create or replace procedure test() IS 
cusor_1 Cursor is select std_name from student where ...; --Cursor 的使用方式1 
cursor_2 Cursor; 
begin 
select class_name into cursor_2 from class where ...; --Cursor 的使用方式2 

-- 可使用For x in cursor LOOP .... end LOOP; 来实现对Cursor 的遍历 

end test; 


-- SYS_REFCURSOR 型游标，该游标是Oracle 以预先定义的游标，可作出参数进行传递 
create or replace procedure test1(rsCursor out SYS_REFCURSOR) IS 
cursor SYS_REFCURSOR; 
name varhcar(20); 
begin 
OPEN cursor FOR select name from student where ... --SYS_REFCURSOR 只能通过OPEN 方法来打开和赋值 
LOOP 
    fetch cursor into name -- SYS_REFCURSOR 只能通过fetch into 来打开和遍历 
    exit when cursor%NOTFOUND; 
    --SYS_REFCURSOR 中可使用三个状态属性： 
         ---%NOTFOUND( 未找到记录信息) %FOUND( 找到记录信息) 
         ---%ROWCOUNT( 然后当前游标所指向的行位置) 
    dbms_output.putline(name); 
end LOOP; 
rsCursor := cursor; 
end test1;

存儲過程[編輯]

查看存儲過程： 

select text
from USER_SOURCE
where name='My_Store_Precedure_Name'
order by LINE;

類似的語言[編輯]

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

PL/SQL 的工作方式類似於與其他關係資料庫關聯的嵌入式過程語言。 例如，Sybase ASE 和 Microsoft SQL Server 具有 Transact-SQL，PostgreSQL 具有 PL/pgSQL（在一定程度上模擬 PL/SQL），MariaDB 包括 PL/SQL 兼容性解析器，而 IBM Db2 包括 SQL 過程語言，符合ISO SQL的SQL/PSM標準。

PL/SQL的設計者將其語法建模為Ada的語法。 Ada 和 PL/SQL都以Pascal語言作為共同的祖先，因此 PL/SQL 在大多數方面也類似於 Pascal。 然而，PL/SQL 包的結構與 Borland Delphi 或Free Pascal 單元實現的基本 Object Pascal 程序結構並不相似。 程式設計師可以在 PL/SQL 包中定義公共和私有全局數據類型、常量和靜態變量。

PL/SQL 還允許定義類並將它們實例化為 PL/SQL 代碼中的對象。 這類似於物件導向程式語言（如 Object Pascal、C++ 和 Java）中的用法。 PL/SQL 將類稱為「抽象數據類型」(ADT) 或「用戶定義類型」(UDT)，並將其定義為 Oracle SQL 數據類型而不是 PL/SQL 用戶定義類型，從而允許 它在 Oracle SQL 引擎和 Oracle PL/SQL 引擎中的使用。 抽象數據類型的構造函數和方法是用 PL/SQL 編寫的。 生成的抽象數據類型可以作為 PL/SQL 中的對象類進行操作。 此類對象還可以作為 Oracle 資料庫表中的列值保留。

儘管表面上相似，但 PL/SQL 與 Transact-SQL 本質上是不同的。 將代碼從一種語言移植到另一種語言通常涉及不平凡的工作，這不僅是因為兩種語言的功能集存在差異，還因為 Oracle 和 SQL Server 處理並發的方式存在非常顯著的差異 和鎖定。

注釋[編輯]

  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]. （原始內容存檔於2017-07-10）. 
  2. ^ Literals. Oracle Database SQL Reference 10g Release 2 (10.2). Oracle. [2009-03-20]. （原始內容存檔於2009-03-18）. 
  3. ^ Database PL/SQL User's Guide and Reference. download.oracle.com. [2023-10-17]. （原始內容存檔於2011-10-28）. 
  4. ^ Feuerstein, Steven. Working with Cursors. oracle.com. [2023-10-17]. （原始內容存檔於2018-03-30）. 
  5. ^ SQL PL

外部連結[編輯]

參見[編輯]

概念
資料庫組件
SQL
分類
指令
安全
實現
類型
資料庫產品
資料庫組件
取自「https://zh.wikipedia.org/w/index.php?title=PL-SQL&oldid=80352160」