跳到內容

Oracle 資料庫/PL/SQL

來自華夏公益教科書

PL/SQL

[編輯 | 編輯原始碼]

簡介

[編輯 | 編輯原始碼]

PL/SQL 代表 SQL 的過程語言擴充套件。它是 SQL 與程式語言的過程功能的結合，它透過將過程功能（如條件或迴圈語句）注入面向集合的 SQL 結構來增強 SQL 的功能。

PL/SQL 的優勢

[編輯 | 編輯原始碼]
  • 過程語言功能：PL/SQL 包含過程語言結構，如條件語句（if else 語句）和迴圈，如（FOR 迴圈）。
  • 塊結構：PL/SQL 由程式碼塊組成，這些程式碼塊可以相互巢狀。每個塊構成一個任務或邏輯模組的單元。PL/SQL 塊可以儲存在資料庫中並重復使用。
  • 更好的效能：PL/SQL 引擎將多個 SQL 語句同時處理為單個塊，從而減少網路流量。
  • 異常處理：PL/SQL 在 PL/SQL 程式執行期間有效地處理異常（或錯誤）。一旦捕獲到異常，可以根據異常型別採取特定操作，也可以向用戶顯示錯誤訊息。

限制

[編輯 | 編輯原始碼]

PL/SQL 只能使用 SELECT、DML（INSERT、UPDATE、DELETE）和 TC（COMMIT、ROLLBACK、SAVEPOINT）語句。DDL（CREATE、ALTER、DROP）和 DCL（GRANT、REVOKE）不能直接使用。但是，任何 DDL/DCL 都可以從 PL/SQL 中執行，前提是它們嵌入在 EXECUTE IMMEDIATE 語句中。

基本結構

[編輯 | 編輯原始碼]

每個 PL/SQL 程式都包含構成 PL/SQL 塊的 SQL 和 PL/SQL 語句。PL/SQL 塊包含三個部分

宣告部分：此部分是可選的，它以保留關鍵字 DECLARE 開始。此部分用於宣告任何佔位符，如變數、常量、記錄和遊標，這些佔位符用於在執行部分中操作資料。

執行部分：此部分是必需的，它以保留關鍵字 BEGIN 開始，並以 END 結束。此部分是編寫程式邏輯以執行任何任務的地方。迴圈、條件語句和 SQL 語句等程式設計結構構成執行部分的一部分。

異常部分：此部分是可選的，它以保留關鍵字 EXCEPTION 開始。程式中的任何異常都可以在此部分處理，以便 PL/SQL 塊正常終止。如果 PL/SQL 塊包含無法處理的異常，則該塊將以錯誤突然終止。

以上三個部分中的每個語句都必須以 ;（分號）結束。PL/SQL 塊可以巢狀在其他 PL/SQL 塊中。可以使用註釋來記錄程式碼。

PL/SQL 的外觀如下。

/* multi-lines comments */
-- single line comments
DECLARE 
    Variable declaration
BEGIN 
    Program Execution 
EXCEPTION 
    Exception handling
END;

PL/SQL 佔位符

[編輯 | 編輯原始碼]

佔位符是臨時儲存區域。佔位符可以是變數、常量和記錄中的任何一個。Oracle 定義佔位符來臨時儲存資料，這些資料用於在 PL SQL 塊執行期間操作資料。

根據要儲存的資料型別，可以使用名稱和資料型別來定義佔位符。下面列出了一些用於定義佔位符的資料型別。

Number(n,m), Char(n), Varchar2(n), Date, Long, Long Raw, Raw, Blob, Clob, Nclob, Bfile

儲存值的佔位符可以在 PL/SQL 塊中更改。

PL/SQL 變數

[編輯 | 編輯原始碼]

宣告變數的通用語法為

variable_name datatype [NOT NULL := value ];
  • variable_name 是變數的名稱。
  • datatype 是有效的 PL/SQL 資料型別。
  • NOT NULL 是變數上的可選規範。如果指定了 NOT NULL，則必須提供初始值。
  • value 或 DEFAULT value 也是可選規範，您可以在其中初始化變數。
  • 每個變數宣告都是一個單獨的語句，必須以分號結束。

以下示例聲明瞭兩個變數，其中一個是非空的。

DECLARE 
  emp_id varchar2(10);
  salary number(9,2) NOT NULL := 1000.00;

變數的值可以在 PL/SQL 塊的執行或異常部分中更改。我們可以透過以下兩種方式之一將值分配給變數。

1) 直接將值分配給變數。

variable_name:=  value;

2) 直接從資料庫列中將值分配給變數。

SELECT column_name
INTO variable_name 
FROM table_name
[WHERE condition];

以下示例將獲取 ID 為 '12345' 的員工的薪水，並將其顯示在螢幕上。

DECLARE
  var_emp_id varchar2(10) = 'A12345'; 
  var_salary number(9,2);  
BEGIN 
  SELECT salary
  INTO var_salary
  FROM employee
  WHERE emp_id = var_emp_id;
  dbms_output.put_line(var_salary);
  dbms_output.put_line('Employee ' || var_emp_id || ' earns salary ' || var_salary); 
END; 
/

注意：斜槓 '/' 表示執行上面的 PL/SQL 塊。

PL/SQL 記錄

[編輯 | 編輯原始碼]

記錄是複合資料型別，它包含不同標量資料型別的組合，如 char、varchar、number 等。記錄中的每個標量資料型別都儲存一個值。記錄可以儲存表中一行的值。

TYPE record_name IS RECORD 
  (col_name_1 datatype, 
   col_name_2 table_name.column_name%type);

資料型別可以在宣告表的過程中進行宣告，例如 col_name_1。如果欄位基於資料庫表中的列，則可以將資料型別定義為 col_name_2。還可以使用 %type 方法宣告變數和常量的型別。與 %type 類似，如果記錄的所有欄位都基於表的列，則可以使用 %rowtype 方法來宣告它們。

record_name table_name%ROWTYPE;

例如

DECLARE 
  TYPE rec_employee IS RECORD 
   (emp_id          varchar2(10), 
    emp_last_name   employees.last_name%type, 
    emp_dept        employees.dept%type,
    salary          number(9,2)
   );

DECLARE 
  rec_employee employees%ROWTYPE;

將記錄宣告為 ROWTYPE 的優點：1) 無需為表中的所有列顯式宣告變數。2) 如果資料庫表中的列規範發生了更改，則程式碼無需更新。

缺點：1) 當記錄建立為 ROWTYPE 時，將為表中的所有列建立欄位，並將使用記憶體為所有欄位建立資料型別。

將值分配給記錄與變數類似，您可以透過直接分配或透過 SELECT 語句將值分配給記錄

record_name.col_name := value;

SELECT col_1, col_2 
INTO record_name.col_name_1, record_name.col_name_2 
FROM table_name 
[WHERE condition];

如果記錄宣告為 ROWTYPE，則可以使用 SELECT * 來分配值。

SELECT * INTO record_name
FROM table_name 
[WHERE condition];

可以按照以下語法檢索記錄的列值

var_name := record_name.col_name;

變數和記錄的範圍

[編輯 | 編輯原始碼]

PL/SQL 允許在塊中巢狀塊，即外部塊的執行部分可以包含內部塊。外部塊可訪問的變數也可以被所有巢狀的內部塊訪問；但是，在內部塊中宣告的變數無法被外部塊訪問。

根據其宣告，我們可以將變數分為兩種型別。

  • 區域性變數 - 這些變數是在內部塊中宣告的，外部塊無法引用它們。
  • 全域性變數 - 這些變數是在外部塊中宣告的，可以被它自身及其內部塊引用。

在下面的示例中，在外部塊中建立了兩個變數，並將它們的乘積分配給在內部塊中建立的第三個變數。變數 'var_num1' 和 'var_num2' 可以被塊中的任何地方訪問；但是，變數 'var_result' 是在內部塊中宣告的，因此無法在外部塊中訪問。

DECLARE
  var_num1 number;
  var_num2 number;
BEGIN 
  var_num1 := 100; 
  var_num2 := 200; 
  DECLARE 
    var_result number;
  BEGIN
    var_result := var_num1 * var_num2; 
  END;
  /* var_result is not accessible to here */ 
END; 
/

PL/SQL 常量

[編輯 | 編輯原始碼]

顧名思義，常量是在 PL/SQL 塊中使用的值，在整個程式中保持不變。常量是使用者定義的文字值。您可以宣告一個常量並使用它來代替實際值。

constant_name CONSTANT datatype := VALUE;

例如

DECLARE 
  comm_pct CONSTANT number(3) := 10;

您必須在宣告常量時為其分配一個值。如果您稍後為常量分配一個值，Oracle 將提示異常。

PL/SQL 條件語句

[編輯 | 編輯原始碼]

PL/SQL 支援程式語言特性，如條件語句、迭代語句。

條件語句的語法

IF condition_1 THEN 
 statement_1; 
 statement_2; 
[ELSIF condition_2 THEN 
 statement_3;] 
[ELSE 
 statement_4;] 
END IF;

注意：請注意關鍵字 ELSIF，它沒有 'E' 在 'IF' 之前。

PL/SQL 迭代語句

[編輯 | 編輯原始碼]

當您希望多次重複執行一個或多個語句時，使用迭代語句。PL/SQL 中有三種類型的迴圈

1. 簡單迴圈 簡單迴圈用於當一組語句至少要執行一次才能終止迴圈時。迴圈中必須指定 EXIT 條件，否則迴圈將進入無限次迭代。當 EXIT 條件滿足時，程序將退出迴圈。

LOOP 
  statements; 
  EXIT; 
  {or EXIT WHEN condition;}
END LOOP;

注意：a) 在迴圈體之前初始化一個變數。b) 在迴圈中遞增變數。c) 使用 EXIT WHEN 語句退出迴圈。如果您使用沒有 WHEN 條件的 EXIT 語句，則迴圈中的語句只執行一次。

2. WHILE 迴圈 當一組語句必須在條件為真時執行時，使用 WHILE 迴圈。條件在每次迭代開始時評估。迭代將一直持續，直到條件變為假。

WHILE <condition> 
 LOOP statements; 
END LOOP;

注意：a) 在迴圈體之前初始化一個變數。b) 在迴圈中遞增變數。c) EXIT WHEN 語句和 EXIT 語句可以在 while 迴圈中使用，但很少使用。

3. FOR 迴圈 FOR 迴圈用於執行一組語句預定的次數。迭代發生在給定的開始和結束整數值之間。計數器始終遞增 1。當計數器達到結束整數值時，迴圈退出。

FOR counter IN start_val..end_val 
  LOOP statements; 
END LOOP;

注意：a) 計數器變數在宣告部分隱式宣告，因此不需要顯式宣告它。b) 計數器變數遞增 1，不需要顯式遞增。c) EXIT WHEN 語句和 EXIT 語句可以在 FOR 迴圈中使用，但很少使用。

PL/SQL 遊標

[編輯 | 編輯原始碼]

遊標包含有關 select 語句和它訪問的資料行的資訊。這個臨時工作區用於儲存從資料庫檢索的資料，並操作這些資料。一個遊標可以包含多行，但一次只能處理一行。遊標包含的一組行稱為活動集。

PL/SQL 中有兩種型別的遊標

隱式遊標

[編輯 | 編輯原始碼]

當您執行 DML 語句（如 DELETE、INSERT、UPDATE 和 SELECT..INTO 語句）時，會建立隱式語句來處理這些語句。

Oracle 提供了一些稱為隱式遊標屬性的屬性，用於檢查 DML 操作的狀態。可用的遊標屬性有 %FOUND、%NOTFOUND、%ROWCOUNT 和 %ISOPEN。

例如，當您執行 INSERT、UPDATE 或 DELETE 語句時，遊標屬性會告訴我們是否影響了任何行以及影響了多少行。當在 PL/SQL 塊中執行 SELECT... INTO 語句時，可以使用隱式遊標屬性來確定 SELECT 語句是否返回了任何行。當沒有選擇資料時，PL/SQL 會返回錯誤。

下表定義了每個屬性的遊標狀態。

屬性 返回值
SQL%FOUND 如果 DML 語句（如 INSERT、DELETE 和 UPDATE）影響了至少一行，並且如果 SELECT ….INTO 語句返回了至少一行，則返回值為 TRUE。

如果 DML 語句（如 INSERT、DELETE 和 UPDATE）未影響任何行，並且如果 SELECT ….INTO 語句未返回任何行，則返回值為 FALSE。

SQL%NOTFOUND 如果 DML 語句（如 INSERT、DELETE 和 UPDATE）影響了至少一行，並且如果 SELECT ….INTO 語句返回了至少一行，則返回值為 FALSE。

如果 DML 語句（如 INSERT、DELETE 和 UPDATE）未影響任何一行，並且如果 SELECT ….INTO 語句未返回任何行，則返回值為 TRUE。

SQL%ROWCOUNT 返回 DML 操作 INSERT、DELETE、UPDATE、SELECT 影響的行數
SQL%ISOPEN 始終返回 FALSE

使用隱式遊標屬性

DECLARE  var_rows number(5);
BEGIN
  UPDATE employee 
  SET salary = salary + 2000;
  IF SQL%NOTFOUND THEN
    dbms_output.put_line('None of the salaries where updated');
  ELSIF SQL%FOUND THEN
    var_rows := SQL%ROWCOUNT;
    dbms_output.put_line('Salaries for ' || var_rows || 'employees are updated');
  END IF; 
END;

在上面的 PL/SQL 塊中，更新了 'employee' 表中所有員工的薪水。如果沒有任何員工的薪水被更新，我們將得到一條訊息 'None of the salaries where updated'。否則，我們將得到一條訊息，例如，'Salaries for 100 employees are updated'，如果 'employee' 表中有 100 行。

顯式遊標

[編輯 | 編輯原始碼]

顯式遊標在 PL/SQL 塊的宣告部分定義。當您執行返回多行的 SELECT 語句時，必須建立它。即使遊標儲存多個記錄，但一次只能處理一條記錄，稱為當前行。當您獲取一行時，當前行位置會移動到下一行。

使用顯式遊標有四個步驟。

  • 在宣告部分宣告遊標。
  • 在執行部分開啟遊標。
  • 在執行部分從遊標中獲取資料到 PL/SQL 變數或記錄中。
  • 在結束 PL/SQL 塊之前，在執行部分關閉遊標。

宣告

CURSOR cursor_name IS select_statement;

例如

DECLARE
   CURSOR cur_emp IS 
   SELECT * 
   FROM employees
   WHERE salary > 10000;

使用遊標 當遊標開啟時，第一行成為當前行。當資料被獲取時，它被複制到記錄或變數中，邏輯指標移動到下一行，它成為當前行。在每個 fetch 語句上，指標都會移動到下一行。如果您希望在最後一行之後獲取，程式將丟擲一個錯誤。當遊標中有多行時，我們可以使用迴圈和顯式遊標屬性來獲取所有記錄。

OPEN cursor_name;
FETCH cursor_name INTO record_name|variable_list;
CLOSE cursor_name;

注意

  • 我們可以將遊標中的行獲取到 PL/SQL 記錄或在 PL/SQL 塊中建立的變數列表中。
  • 如果您將遊標獲取到 PL/SQL 記錄中，則記錄應該與遊標具有相同的結構。
  • 如果您將遊標獲取到變數列表中，則變數應該在 fetch 語句中按照與遊標中存在的列相同的順序排列。
  • 當我們嘗試開啟一個在先前操作中沒有關閉的遊標時，它會丟擲異常。
  • 當我們嘗試在最後一次操作後獲取遊標時，它會丟擲異常。

例如

DECLARE 
  rec_emp employees%rowtype;
  CURSOR cur_emp IS 
  SELECT *
  FROM employees
  WHERE salary > 10000; 
BEGIN 
  OPEN cur_emp; 
  FETCH cur_emp INTO rec_emp; 
    dbms_output.put_line (rec_emp.first_name || '  ' 
                       || rec_emp.last_name); 
  CLOSE emp_cur; 
END;

Oracle 提供了一些稱為顯式遊標屬性的屬性，用於控制使用遊標時的資料處理。我們使用這些屬性來避免透過 OPEN、FETCH 和 CLOSE 語句訪問遊標時出現錯誤。

屬性 返回值
Cursor_name%FOUND TRUE，如果 fetch 語句返回了至少一行。

FALSE，如果 fetch 語句沒有返回任何行。

Cursor_name%NOTFOUND TRUE，如果 fetch 語句沒有返回任何行。

FALSE，如果 fetch 語句返回了至少一行。

Cursor_name%ROWCOUNT fetch 語句獲取的行數。

如果沒有返回任何行，PL/SQL 語句將返回一個錯誤。

Cursor_name%ISOPEN TRUE，如果遊標已在程式中開啟。

FALSE，如果遊標未在程式中開啟。

帶簡單迴圈的遊標

DECLARE 
  CURSOR cur_emp IS 
  SELECT first_name, last_name, salary FROM employees; 
  rec_emp cur_emp%rowtype; 
BEGIN 
  IF NOT cur_emp%ISOPEN THEN 
    OPEN cur_emp; 
  END IF; 
  LOOP 
    FETCH cur_emp INTO rec_emp; 
    EXIT WHEN cur_emp%NOTFOUND; 
    dbms_output.put_line(cur_emp.first_name || ' ' ||cur_emp.last_name 
      || ' ' ||cur_emp.salary); 
  END LOOP; 
END; 
/

遊標屬性 %ISOPEN 用於檢查遊標是否開啟，如果條件為真，則程式不會再次開啟遊標。遊標屬性 %NOTFOUND 用於檢查 fetch 是否返回了任何行。如果沒有找到任何行，程式將退出。通常，當遊標到達最後一行時，就不能再獲取任何行了。

帶 WHILE 迴圈的遊標

DECLARE 
  CURSOR cur_emp IS 
  SELECT first_name, last_name, salary FROM employees; 
  rec_emp cur_emp%rowtype; 
BEGIN 
  IF NOT cur_emp%ISOPEN THEN 
    OPEN cur_emp; 
  END IF; 
  FETCH cur_emp INTO sales_rec;  
 WHILE cur_emp%FOUND THEN  
 LOOP 
   dbms_output.put_line(cur_emp.first_name || ' ' ||cur_emp.last_name 
   || ' ' ||cur_emp.salary); 
   FETCH cur_emp INTO sales_rec; 
 END LOOP; 
END; 
/

使用 %FOUND 評估第一個 fetch 語句是否返回了一行，如果為 TRUE，則程式將進入 while 迴圈。在迴圈內部，再次使用 fetch 語句處理下一行。如果 fetch 語句在 while 迴圈之前沒有執行一次，則 while 條件將在第一次例項中返回 false，而 while 迴圈將被跳過。

帶 FOR 迴圈的遊標：使用 FOR 迴圈時，您不需要宣告記錄或變數來儲存遊標值，也不需要開啟、獲取和關閉遊標。這些功能由 FOR 迴圈自動完成。

DECLARE 
 CURSOR cur_emp IS 
   SELECT first_name, last_name, salary FROM employees; 
 rec_emp cur_emp%rowtype; 
BEGIN 
  FOR rec_emp in cur_emp 
  LOOP  
    dbms_output.put_line(cur_emp.first_name || ' ' ||cur_emp.last_name 
    || ' ' ||cur_emp.salary);  
  END LOOP; 
END;
/

當 FOR 迴圈被處理時，將建立一個結構為 'cur_emp' 的記錄 'rec_emp'，遊標被開啟，行被獲取到記錄 'rec_emp' 中，並且在處理完最後一行後遊標被關閉。透過使用 FOR 迴圈，您可以減少程式中的行數。

PL/SQL 異常處理

[編輯 | 編輯原始碼]

PL/SQL 過程

[編輯 | 編輯原始碼]

從 PL/SQL 塊呼叫儲存過程時，只需使用儲存過程名稱進行呼叫。如果在儲存過程名稱前加上 “EXECUTE” 關鍵字，則會收到錯誤資訊。

my_sproc;

EXECUTE my_sproc;

PLS-00103: Encountered the symbol "my_sproc" when expecting one of the following:
:= . ( @ % ; immediate
The symbol ":=" was substituted for "my_sproc" to continue.

EXECUTE IMMEDIATE my_sproc;

PLS-00222: no function with name exists in this scope

PL/SQL 函式

[編輯 | 編輯原始碼]
 一位 Wikibookian 建議將本書或本章與 Oracle 程式設計/SQL 速查表 合併。
請在 討論頁面 上討論是否應該進行合併。
本節內容比較簡短。
您可以透過 擴充套件 來幫助 Wikibooks。

引數 - 過程，函式

[編輯 | 編輯原始碼]
 本節內容比較簡短。
您可以透過 擴充套件 來幫助 Wikibooks。

PL/SQL 觸發器

[編輯 | 編輯原始碼]
 本節內容比較簡短。
您可以透過 擴充套件 來幫助 Wikibooks。
檢索自 "https://wikibook.tw/w/index.php?title=Oracle_Database/PL/SQL&oldid=4429039"
華夏公益教科書