Oracle 基礎入門完整教材：DDL、DML、查詢、PL/SQL 與管理操作

資料定義語言 (DDL)

建立資料表

CREATE TABLE employees (
  emp_id     NUMBER(6) PRIMARY KEY,
  emp_name   VARCHAR2(50) NOT NULL,
  dept_id    NUMBER(4),
  hire_date  DATE DEFAULT SYSDATE,
  salary     NUMBER(8,2) CHECK (salary > 0),
  email      VARCHAR2(100) UNIQUE,
  CONSTRAINT fk_dept FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
) TABLESPACE users;

修改表結構

ALTER TABLE employees ADD (phone VARCHAR2(20));
ALTER TABLE employees MODIFY (emp_name VARCHAR2(80));
ALTER TABLE employees DROP COLUMN phone;
ALTER TABLE employees RENAME COLUMN emp_name TO full_name;

刪除與截斷

DROP TABLE employees PURGE;          -- 連同索引/約束/權限一併刪除，不可回滾
TRUNCATE TABLE employees;            -- 保留結構、釋放空間、高水位歸零、不可回滾

重命名

RENAME employees TO staff;

資料操作語言 (DML)

插入資料

-- 指定欄位
INSERT INTO employees (emp_id, emp_name, dept_id, salary)
VALUES (1001, 'Alice', 10, 50000);

-- 全欄位 (須符合建表順序)
INSERT INTO employees VALUES (1002, 'Bob', 20, SYSDATE, 60000, 'bob@corp.com');

-- 子查詢插入
INSERT INTO emp_archive SELECT * FROM employees WHERE hire_date < DATE '2020-01-01';

更新資料

UPDATE employees
SET salary = salary * 1.05
WHERE dept_id = 10;

-- 子查詢更新
UPDATE employees e
SET (salary, dept_id) = (
  SELECT AVG(salary), 30 FROM employees WHERE dept_id = e.dept_id
)
WHERE emp_id = 1001;

刪除資料

DELETE FROM employees WHERE emp_id = 1001;  -- 可回滾
-- 配合 TRUNCATE 見上

查詢與進階 SQL

基本 SELECT

SELECT DISTINCT dept_id FROM employees;
SELECT emp_name, salary * 12 AS annual_sal FROM employees;

條件與排序

SELECT * FROM employees
WHERE salary BETWEEN 40000 AND 80000
  AND emp_name LIKE 'A%'
ORDER BY salary DESC, emp_id;

分組與聚合

SELECT dept_id, COUNT(*) AS cnt, AVG(salary) AS avg_sal
FROM employees
GROUP BY dept_id
HAVING COUNT(*) > 5;

集合運算

SELECT emp_id FROM employees WHERE dept_id = 10
UNION
SELECT emp_id FROM employees WHERE dept_id = 20;

SELECT emp_id FROM employees
MINUS
SELECT emp_id FROM terminated_emps;

階層查詢

SELECT LPAD(' ', 2*(LEVEL-1)) || emp_name AS org_chart
FROM employees
START WITH mgr_id IS NULL
CONNECT BY PRIOR emp_id = mgr_id
ORDER SIBLINGS BY emp_name;

索引、視圖、同義詞、序列

-- 索引
CREATE INDEX idx_emp_dept ON employees(dept_id) TABLESPACE idx_ts;
CREATE UNIQUE INDEX uk_emp_email ON employees(email);

-- 視圖
CREATE OR REPLACE VIEW v_dept_summary AS
SELECT d.dept_name, COUNT(e.emp_id) AS headcount, AVG(e.salary) AS avg_sal
FROM departments d LEFT JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_name;

-- 同義詞
CREATE PUBLIC SYNONYM emp FOR hr.employees;

-- 序列
CREATE SEQUENCE seq_emp_id START WITH 1000 INCREMENT BY 1 CACHE 20 NOCYCLE;

PL/SQL 程式設計核心

區塊結構

DECLARE
  v_cnt NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_cnt FROM employees;
  DBMS_OUTPUT.PUT_LINE('Total: ' || v_cnt);
EXCEPTION
  WHEN NO_DATA_FOUND THEN NULL;
  WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/
-- 常用迴圈
FOR i IN 1..10 LOOP ... END LOOP;
WHILE v_flag LOOP ... END LOOP;
LOOP EXIT WHEN v_done; ... END LOOP;

游標

-- 顯式游標
CURSOR c_high_paid IS SELECT * FROM employees WHERE salary > 80000;
OPEN c_high_paid;
FETCH c_high_paid INTO v_rec;
CLOSE c_high_paid;

-- 遊標 FOR 迴圈 (自動開啟/關閉/擷取)
FOR r IN (SELECT * FROM employees WHERE salary > 80000) LOOP
  DBMS_OUTPUT.PUT_LINE(r.emp_name);
END LOOP;

存儲程序/函數/套件

CREATE OR REPLACE PROCEDURE adjust_salary (
  p_emp_id IN employees.emp_id%TYPE,
  p_pct    IN NUMBER
) AS
BEGIN
  UPDATE employees SET salary = salary * (1 + p_pct/100) WHERE emp_id = p_emp_id;
  COMMIT;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR(-20001, 'Employee not found');
END;
/

CREATE OR REPLACE FUNCTION get_annual_sal (p_emp_id NUMBER) RETURN NUMBER AS
  v_sal NUMBER;
BEGIN
  SELECT salary * 12 INTO v_sal FROM employees WHERE emp_id = p_emp_id;
  RETURN v_sal;
END;
/

CREATE OR REPLACE PACKAGE pkg_emp AS
  PROCEDURE hire_new (p_name VARCHAR2, p_dept NUMBER, p_sal NUMBER);
  FUNCTION headcount (p_dept NUMBER) RETURN NUMBER;
END pkg_emp;
/

CREATE OR REPLACE PACKAGE BODY pkg_emp AS
  PROCEDURE hire_new (p_name VARCHAR2, p_dept NUMBER, p_sal NUMBER) IS
  BEGIN
    INSERT INTO employees (emp_id, emp_name, dept_id, salary)
    VALUES (seq_emp_id.NEXTVAL, p_name, p_dept, p_sal);
  END;
  FUNCTION headcount (p_dept NUMBER) RETURN NUMBER IS
    v_cnt NUMBER;
  BEGIN
    SELECT COUNT(*) INTO v_cnt FROM employees WHERE dept_id = p_dept;
    RETURN v_cnt;
  END;
END pkg_emp;
/
-- 呼叫
EXEC pkg_emp.hire_new('Carol', 10, 55000);

交易控制

COMMIT;                    -- 確認交易
ROLLBACK;                  -- 回滾
SAVEPOINT sp1;             -- 設定存檔點
ROLLBACK TO SAVEPOINT sp1; -- 回滾至存檔點
SET AUTOCOMMIT ON;         -- SQL*Plus 環境自動提交

使用者與權限管理

CREATE USER app_user IDENTIFIED BY pwd;
GRANT CONNECT, RESOURCE TO app_user;
GRANT CREATE VIEW, CREATE SEQUENCE TO app_user;
GRANT SELECT ON hr.employees TO app_user WITH GRANT OPTION;

CREATE ROLE app_readonly;
GRANT SELECT ON hr.employees TO app_readonly;
GRANT app_readonly TO app_user;

REVOKE SELECT ON hr.employees FROM app_user;

常用資料字典視圖

常用函數速查

• 字串：SUBSTR, INSTR, LPAD/RPAD, TRIM, REPLACE, UPPER/LOWER/INITCAP
• 數值：ROUND, TRUNC, MOD, CEIL/FLOOR, POWER, SQRT
• 日期：SYSDATE, ADD_MONTHS, MONTHS_BETWEEN, LAST_DAY, NEXT_DAY, ROUND/TRUNC(date), TO_CHAR, TO_DATE
• 轉換：TO_NUMBER, NVL, NVL2, COALESCE, DECODE, CASE
• 聚合：COUNT, SUM, AVG, MAX/MIN, STDDEV, VARIANCE