您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

Oracle數據庫新手開發指南：核心語法與實務技巧全解析

編輯：Oracle 資料庫基礎

前言

Oracle 資料庫以其強大的功能與企業級可靠性，成為眾多關鍵任務系統的首選。然而，對於從 SQL Server 或其他資料庫轉型的開發者而言，Oracle 獨特的 PL/SQL 語法、存儲過程結構及異常處理機制往往構成初期學習障礙。本文系統整理 Oracle 開發的核心知識點，涵蓋過程調試、存儲過程編寫、流程控制、游標操作、變量賦值及異常處理等實戰場景，並輔以與 SQL Server 的對比，助力新手快速構建穩健的資料庫應用。

一、過程調試：匿名區塊的標準範式

在 Oracle 中進行邏輯驗證或片段測試，核心在於編寫匿名 PL/SQL 區塊。其標準結構包含宣告區、執行區與異常處理區（可選）。

DECLARE
  v_param_int NUMBER; -- 變量宣告
BEGIN
  NULL; -- 佔位符，實際開發中替換為業務邏輯
  -- Operate the data
END;
/

對比 SQL Server： SQL Server 採用 T-SQL 批次處理模式，變量宣告需加 `@` 前綴，無需 `BEGIN...END` 包裹簡單邏輯，語法更接近腳本風格：

DECLARE @param_int INT;
-- Operate the data

二、存儲過程：從結構到細節的規範構建

2.1 標準語法骨架與關鍵規範

存儲過程是封裝業務邏輯的核心單元。`CREATE OR REPLACE` 確保冪等部署，參數模式（IN/OUT/IN OUT）明確數據流向。

CREATE OR REPLACE PROCEDURE proc_name (
  p_input_num   IN  NUMBER,
  p_input_str   IN  VARCHAR2,
  p_output_num  OUT VARCHAR2
) AS
  v_temp NUMBER; -- 區域變量宣告於 AS/IS 與 BEGIN 之間
BEGIN
  -- 業務邏輯
  NULL;
END proc_name;
/

三大語法鐵律：

參數分隔： 多參數間必須以逗號 `,` 分隔，最後一個參數後無逗號。
變量終結： 宣告區每行變量定義必須以分號 `;` 結尾。
區塊閉合： 執行區結束前需以 `END [procedure_name];` 閉合，且整個腳本通常以單獨一行的 `/` 執行。

2.2 流程控制：IF-THEN-ELSIF 的嚴格結構

Oracle 無 `ELSE IF` 關鍵字，僅有 `ELSIF`，且每個分支若包含多條語句，必須用 `BEGIN...END` 包裹，結尾以 `END IF;` 收束。

IF condition1 THEN
  BEGIN
    -- statements
  END;
ELSIF condition2 THEN
  BEGIN
    -- statements
  END;
ELSE
  BEGIN
    -- statements
  END;
END IF;

對比 SQL Server： T-SQL 使用 `IF...BEGIN...END ELSE BEGIN...END`，無 `ELSIF`，巢狀層級較深時可讀性較弱。

2.3 結果集返回：SYS_REFCURSOR 的標準化實踐

Oracle 不支援像 SQL Server 那樣直接 `SELECT` 返回結果集給調用者（如 ADO.NET、Java JDBC）。標準做法是定義 `OUT` 模式的 `SYS_REFCURSOR`（或弱類型游標），在過程中 `OPEN` 游標綁定查詢。

PROCEDURE get_data(p_cursor OUT SYS_REFCURSOR) AS
BEGIN
  OPEN p_cursor FOR SELECT * FROM employees WHERE department_id = 10;
END;

2.4 變量賦值：`:=` 操作符的獨佔地位

Oracle 使用 `:=` 進行賦值，而非 `=`（SQL Server 使用 `SET @var = val` 或 `SELECT @var = val`）。

v_count NUMBER := 0; -- 宣告時初始化
v_count := v_count + 1; -- 執行區賦值

2.5 SELECT INTO 的「無數據」陷阱與應對

這是新手最易踩的坑。Oracle 若 `SELECT INTO` 查無記錄，會拋出 `NO_DATA_FOUND` 異常（SQL Server 則變量保持 NULL）。務必包裹在 BEGIN...EXCEPTION 區塊中處理：

BEGIN
  SELECT max(sal) INTO v_max_sal FROM emp WHERE deptno = 99;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    v_max_sal := 0; -- 預設值或業務邏輯
  WHEN TOO_MANY_ROWS THEN
    -- 處理多行情況
    RAISE;
END;

2.6 異常處理：構建彈性的錯誤防火牆

Oracle 異常區塊支援預定義異常、非預定義異常（`PRAGMA EXCEPTION_INIT`）及用戶自定義異常。生產環境建議採用「記錄日誌 + 拋出業務異常」模式：

EXCEPTION
  WHEN OTHERS THEN
    -- 1. 記錄詳細錯誤上下文至日誌表
    INSERT INTO error_log (err_time, err_code, err_msg, call_stack)
    VALUES (SYSDATE, SQLCODE, SUBSTR(SQLERRM, 1, 200), DBMS_UTILITY.FORMAT_ERROR_STACK);
    -- 2. 拋出統一業務異常碼給上層應用
    RAISE_APPLICATION_ERROR(-20001, '業務處理失敗: ' || SQLERRM);
END;

文中提到的 `GOTO` 標籤跳轉雖合法，但現代開發強烈建議避免使用 GOTO，改用模組化子程序或標誌變量控制流程，提升代碼可維護性。

2.7 DML 影響行數檢測：SQL%ROWCOUNT 的正確姿勢

隱式游標屬性 `SQL%ROWCOUNT` 返回最近一次 DML（INSERT/UPDATE/DELETE/MERGE）影響的行數。常用於驗證更新是否生效：

UPDATE cc_manual SET status = 'CLOSED' WHERE id = p_id;
IF SQL%ROWCOUNT = 0 THEN
  -- 無數據更新，可能 ID 不存在或狀態已是目標值
  p_ret_code := -9;
  p_ret_msg  := '更新失敗，目標記錄不存在或無變化';
  -- 可選：OPEN 游標返回空集合供前端統一處理
  OPEN p_cursor FOR SELECT * FROM cc_manual WHERE 1=0;
END IF;