Oracle 核心概念速查手冊：表空間、例外處理、安全管理、同義詞、DB Link、Cursor、Record、%ROWTYPE

本文彙整 Oracle 開發與管理必備的八大核心概念，提供標準語法範本與最佳實務註解，適合開發人員、DBA 快速查閱與複製套用。

1. 表空間

-- 建立表空間（含自動擴展）
CREATE TABLESPACE mytbs
DATAFILE 'C:\oracle\oradata\mydb\mytbs01.dbf' SIZE 100M
AUTOEXTEND ON NEXT 50M MAXSIZE 2G
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO;

-- 修改使用者預設表空間
ALTER USER zgl DEFAULT TABLESPACE mytbs;

-- 授予無限表空間配額
GRANT UNLIMITED TABLESPACE TO zgl;

重點：LOCAL 區塊管理取代字典管理；AUTO 區段空間管理減少自由清單爭用。

2. 例外處理

CREATE OR REPLACE PROCEDURE pro_test_exception (p_id IN VARCHAR2) IS
    v_name VARCHAR2(30);
BEGIN
    SELECT name INTO v_name FROM t_user WHERE id = p_id;
    DBMS_OUTPUT.PUT_LINE(v_name);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('查無資料');
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('回傳多筆資料');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('未預期錯誤: ' || SQLERRM);
        RAISE;  -- 重拋供上層處理
END;

重點：盡量捕捉具名例外；WHEN OTHERS 必配合 RAISE 或記錄日誌，避免靜默吞沒錯誤。

3. 安全管理

-- 帳號鎖定/解鎖/改密碼
ALTER USER zgl ACCOUNT LOCK;
ALTER USER zgl IDENTIFIED BY new_pwd;
ALTER USER zgl ACCOUNT UNLOCK;

-- 建立使用者並指定預設表空間
CREATE USER qqq IDENTIFIED BY qqq123 DEFAULT TABLESPACE tbs_data;

-- 系統權限與角色
GRANT CREATE SESSION TO qqq;           -- 基本連線
GRANT CREATE TABLE, CREATE PROCEDURE TO qqq;
GRANT RESOURCE TO qqq;                 -- 常用角色組合

-- 物件權限
GRANT SELECT, INSERT ON zgl.t_user TO qqq;
GRANT EXECUTE ON zgl.proc01 TO test;

-- 角色管理
CREATE ROLE app_readonly;
GRANT SELECT ON zgl.t_user TO app_readonly;
GRANT app_readonly TO test;

-- 設定檔：密碼複雜度、失敗鎖定、閒置逾時
CREATE PROFILE secure_profile LIMIT
    FAILED_LOGIN_ATTEMPTS 5
    PASSWORD_LIFE_TIME 90
    PASSWORD_REUSE_TIME 365
    PASSWORD_VERIFY_FUNCTION ora12c_verify_function;
ALTER USER zgl PROFILE secure_profile;

4. 同義詞

-- 公用同義詞（需 DBA 權限）
CREATE PUBLIC SYNONYM emp FOR hr.employees;

-- 私有同義詞
CREATE SYNONYM dept FOR hr.departments;

-- 查詢同義詞
SELECT * FROM dba_synonyms WHERE table_name = 'EMPLOYEES';

用途：隱藏結構描述名稱、簡化跨 Schema 存取、支援遷移透明度。

5. 資料庫連結

-- 建立 DB Link（固定使用者）
CREATE DATABASE LINK dblink_hr
CONNECT TO hr IDENTIFIED BY hr_pwd
USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.10)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL)))';

-- 使用 DB Link
SELECT * FROM employees@dblink_hr WHERE department_id = 10;

-- 當前使用者 DB Link（需雙向信任）
CREATE DATABASE LINK dblink_current USING 'ORCL';

注意：USING 字串為 tnsnames.ora 別名或完整 Easy Connect 字串；GLOBAL_NAMES=TRUE 時 DB Link 名稱必須等於遠端 GLOBAL_NAME。

6. Cursor（游標）

隱含游標（FOR 迴圈）

CREATE OR REPLACE PROCEDURE pro_test_cursor IS
BEGIN
    FOR r IN (SELECT * FROM t_user) LOOP
        DBMS_OUTPUT.PUT_LINE(r.id || ',' || r.name || ',' || SQL%ROWCOUNT);
    END LOOP;
END;

顯式游標

CREATE OR REPLACE PROCEDURE pro_test_cursor_one (p_id IN NUMBER) IS
    v_row t_user%ROWTYPE;
    CURSOR c_user IS SELECT * FROM t_user WHERE id = p_id;
BEGIN
    OPEN c_user;
    FETCH c_user INTO v_row;
    IF c_user%FOUND THEN
        DBMS_OUTPUT.PUT_LINE(v_row.id || ',' || v_row.name);
    END IF;
    CLOSE c_user;
END;

最佳實務：優先使用 FOR r IN (SELECT ...) 隱含游標，自動處理 OPEN/FETCH/CLOSE，減少錯誤。

7. Record（自訂記錄型別）

CREATE OR REPLACE PROCEDURE pro_test_record (p_id IN VARCHAR2) IS
    TYPE t_user_rec IS RECORD (
        id   t_user.id%TYPE,
        name t_user.name%TYPE
    );
    v_row t_user_rec;
BEGIN
    SELECT id, name INTO v_row FROM t_user WHERE id = p_id;
    DBMS_OUTPUT.PUT_LINE(v_row.id || ',' || v_row.name);
END;

8. %ROWTYPE（錨定資料列型別）

CREATE OR REPLACE PROCEDURE pro_test_rowtype (p_id IN VARCHAR2) IS
    v_row t_user%ROWTYPE;  -- 自動對應 t_user 所有欄位
BEGIN
    SELECT * INTO v_row FROM t_user WHERE id = p_id;
    DBMS_OUTPUT.PUT_LINE(v_row.id || ',' || v_row.name);
END;

優勢：資料表結構變更（增/減欄位）自動同步，減少維護負擔。

速查索引

將此手冊加入書籤，下次撰寫 PL/SQL 時即可快速複製、調整、套用，提升開發效率與程式碼品質。