Oracle 核心知識八大模組速成：表空間、例外處理、安全管理、同義詞、DB Link、游標、Record 與 %ROWTYPE

模組一：表空間管理

-- 建立表空間
CREATE TABLESPACE mytbs 
  DATAFILE '/u01/oradata/mytbs01.dbf' SIZE 100M
  AUTOEXTEND ON NEXT 10M MAXSIZE 2G
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE
  SEGMENT SPACE MANAGEMENT AUTO;

-- 修改用戶預設表空間
ALTER USER zgl DEFAULT TABLESPACE mytbs;
GRANT UNLIMITED TABLESPACE TO zgl;

模組二：例外處理

CREATE OR REPLACE PROCEDURE proc_test_exc (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;
/
-- 自訂例外
DECLARE
  e_invalid_id EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_invalid_id, -20001);
BEGIN
  IF p_id IS NULL THEN RAISE e_invalid_id; END IF;
EXCEPTION
  WHEN e_invalid_id THEN DBMS_OUTPUT.PUT_LINE('ID 不可為空');
END;

模組三：安全管理 (用戶/角色/Profile)

-- 用戶生命週期
CREATE USER app_user IDENTIFIED BY pass#123
  DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp
  PROFILE app_profile
  QUOTA 100M ON users;

ALTER USER app_user ACCOUNT LOCK/UNLOCK;
ALTER USER app_user IDENTIFIED BY new_pass;
ALTER USER app_user DEFAULT TABLESPACE new_tbs;
DROP USER app_user CASCADE;

-- 角色
CREATE ROLE app_read;
GRANT SELECT ON schema.table TO app_read;
GRANT app_read TO app_user;

-- Profile (密碼/資源限制)
CREATE PROFILE app_profile LIMIT
  FAILED_LOGIN_ATTEMPTS 5
  PASSWORD_LIFE_TIME 90
  PASSWORD_REUSE_TIME 365
  PASSWORD_VERIFY_FUNCTION ora12c_verify_function
  SESSIONS_PER_USER 10
  CPU_PER_SESSION UNLIMITED
  CONNECT_TIME 480;

模組四：同義詞

-- 私有同義詞 (僅擁有者可見)
CREATE SYNONYM emp FOR hr.employees;

-- 公用同義詞 (所有用戶可見，需 CREATE PUBLIC SYNONYM 權限)
CREATE PUBLIC SYNONYM employees FOR hr.employees;

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

模組五：Database Link (跨資料庫查詢)

CREATE DATABASE LINK remote_db
  CONNECT TO remote_user IDENTIFIED BY pass
  USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.1.10)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL)))';

-- 查詢
SELECT * FROM employees@remote_db WHERE department_id = 10;

-- 分散式交易 (Two-Phase Commit)
INSERT INTO local_table SELECT * FROM remote_table@remote_db;
COMMIT;  -- 自動協調

模組六：游標

隱性游標

BEGIN
  UPDATE emp SET sal = sal * 1.1 WHERE deptno = 10;
  IF SQL%ROWCOUNT > 0 THEN
    DBMS_OUTPUT.PUT_LINE('更新 ' || SQL%ROWCOUNT || ' 筆');
  END IF;
END;

顯性游標 - FOR LOOP (最常用)

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

顯性游標 - OPEN/FETCH/CLOSE

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

游標變數 (REF CURSOR) - 介面傳遞

CREATE OR REPLACE PACKAGE pkg_cur AS
  TYPE t_refcur IS REF CURSOR;
  PROCEDURE get_emp (p_dept IN NUMBER, p_rc OUT t_refcur);
END;
/
CREATE OR REPLACE PACKAGE BODY pkg_cur AS
  PROCEDURE get_emp (p_dept IN NUMBER, p_rc OUT t_refcur) IS
  BEGIN
    OPEN p_rc FOR SELECT * FROM emp WHERE deptno = p_dept;
  END;
END;

模組七：Record 型別

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

模組八：%ROWTYPE

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

進階技巧總結