Oracle PL/SQL詳解

一、基本語法

PL/SQL是Oracle資料庫中的一種編程語言，基於SQL語言擴展而來，既擴展了SQL的語句操作，也提供了一些面向過程的編程功能。以一個簡單的Hello World程序為例：

DECLARE
  message varchar2(20) := 'Hello, World!';
BEGIN
  dbms_output.put_line(message);
END;

關鍵字DECLARE標識了變數的聲明，可以在其中聲明一些變數。BEGIN標識程序的開始，END標識程序的結束。dbms_output.put_line()函數可以將信息列印至控制台。

除此之外，PL/SQL還支持分支語句、循環語句、異常處理等常見編程特性。

二、處理CURSOR

將SQL查詢結果集的指針存儲在游標對象中，以便於在PL/SQL程序中進行操作。

--以下是一份處理游標的代碼
DECLARE
  CURSOR emp_cursor IS
    SELECT first_name, last_name, salary FROM employees WHERE salary > 10000 ORDER BY salary DESC;
BEGIN
  FOR emp IN emp_cursor LOOP
    dbms_output.put_line(emp.first_name || ' ' || emp.last_name || ', ' || emp.salary);
  END LOOP;
END;

在以上代碼中，我們先定義一個名為emp_cursor的游標對象，存儲的是查詢結果集 SELECT first_name, last_name, salary from employees WHERE salary > 10000 ORDER BY salary DESC。FOR-IN循環遍歷游標的結果集，將每一條記錄的first_name、last_name和salary值輸出出來。如果需要在查詢結果中增加或修改記錄，需要使用游標和UPDATE或INSERT語句連接使用。

三、存儲過程

存儲過程是PL/SQL程序的一種，類似於函數，但不會返回值。在存儲過程中我們可以使用變數和條件控制來編寫複雜的業務邏輯。

--以下是一份簡單的存儲過程代碼
CREATE OR REPLACE PROCEDURE increase_salary (p_employee_id NUMBER, p_increase_amount NUMBER) AS
  v_salary employees.salary%TYPE;
BEGIN
  SELECT salary INTO v_salary FROM employees WHERE employee_id = p_employee_id;
  UPDATE employees SET salary = v_salary + p_increase_amount WHERE employee_id = p_employee_id;
  COMMIT;
END increase_salary;

這是一個名為increase_salary的存儲過程，接受兩個輸入參數，一個員工ID和一個增加工資的數值。SELECT語句獲取員工ID對應的工資，然後使用UPDATE命令將工資更新至原來的基礎上增加p_increase_amount，並使用COMMIT將之前的修改進行提交。

四、觸發器

觸發器是資料庫對象，它響應資料庫表上的事件。當滿足指定的條件時，觸發器會自動啟動。

--以下是一份使用觸發器來記錄員工薪水變化的代碼
CREATE OR REPLACE TRIGGER salary_change_tracking
AFTER UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
  INSERT INTO salary_changes (employee_id, old_salary, new_salary)
  VALUES (:old.employee_id, :old.salary, :new.salary);
END;

這是一個名為salary_change_tracking的觸發器，在employees表的salary欄位更新時執行。針對每條更新記錄，觸發器會將更新前和更新後的salary和employee_id信息記錄到salary_changes表中。

五、包

包是一種將過程、函數、類型、變數和常量進行組織的機制。我們可以在包中定義一個公共的介面，用於集中訪問和管理組件邏輯。

--以下是一個簡要的包示例
CREATE OR REPLACE PACKAGE my_package IS
  PROCEDURE procedure1;
  PROCEDURE procedure2;
END my_package;

CREATE OR REPLACE PACKAGE BODY my_package IS
  PROCEDURE procedure1 IS
  BEGIN
    dbms_output.put_line('Procedure 1');
  END;

  PROCEDURE procedure2 IS
  BEGIN
    dbms_output.put_line('Procedure 2');
  END;
END my_package;

這是一個名為my_package的包，包含了兩種過程procedure1和procedure2。在實現部分中，我們可以分別定義每個過程的具體實現。當需要使用時，只需要引用包名即可。