Oracle 基礎語句大全：從數據定義到復雜查詢

一、DDL（數據定義語言）：定義數據庫結構

1. 創建表（CREATE TABLE）

-- 語法格式
CREATE TABLE [schema.]table_name (column1 datatype [CONSTRAINT constraint1],column2 datatype [DEFAULT default_value],-- 表級約束[CONSTRAINT primary_key PRIMARY KEY (column1)],[CONSTRAINT foreign_key FOREIGN KEY (column2) REFERENCES ref_table(ref_col)]
);-- 示例：創建員工表
CREATE TABLE hr.employees (emp_id NUMBER(6) PRIMARY KEY,emp_name VARCHAR2(50) NOT NULL,salary NUMBER(10,2) CHECK (salary > 0),hire_date DATE DEFAULT SYSDATE,dept_id NUMBER(4) REFERENCES hr.departments(dept_id)
);-- Oracle 特有數據類型
-- VARCHAR2(n)：可變長度字符串，n為最大長度
-- NUMBER(p,s)：數值型，p總位數，s小數位
-- DATE：日期時間類型，包含年月日時分秒
-- CLOB：大文本類型（最大4GB）
-- BLOB：二進制大對象

2. 修改表（ALTER TABLE）

-- 添加列
ALTER TABLE table_name ADD (new_column datatype [CONSTRAINT]);
ALTER TABLE hr.employees ADD (email VARCHAR2(100) UNIQUE);-- 修改列定義
ALTER TABLE table_name MODIFY (column_name datatype [NULL|NOT NULL]);
ALTER TABLE hr.employees MODIFY (salary NUMBER(12,2));-- 刪除列
ALTER TABLE table_name DROP COLUMN column_name;
ALTER TABLE hr.employees DROP COLUMN email;-- 添加約束
ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY/UNIQUE/CHECK/Foreign KEY (...);
ALTER TABLE hr.employees ADD CONSTRAINT uk_emp_name UNIQUE (emp_name);

3. 刪除表（DROP TABLE）

-- 普通刪除
DROP TABLE table_name [CASCADE CONSTRAINTS]; -- 級聯刪除外鍵
DROP TABLE hr.temp_employees;-- 清空表數據但保留結構
TRUNCATE TABLE table_name; -- 比DELETE更快，不記錄日志
TRUNCATE TABLE hr.employees;

4. 創建索引（CREATE INDEX）

-- 普通索引
CREATE INDEX idx_table_column ON table_name(column1, column2);
CREATE INDEX idx_emp_salary ON hr.employees(salary);-- 唯一索引
CREATE UNIQUE INDEX idx_unique ON table_name(unique_column);-- 函數索引（Oracle 特有）
CREATE INDEX idx_emp_upper ON hr.employees(UPPER(emp_name));

5. 創建序列（CREATE SEQUENCE）

-- 語法
CREATE SEQUENCE [schema.]sequence_nameSTART WITH nINCREMENT BY nMINVALUE n | NOMINVALUEMAXVALUE n | NOMAXVALUECYCLE | NOCYCLECACHE n | NOCACHE;-- 示例：創建員工ID序列
CREATE SEQUENCE hr.emp_seqSTART WITH 1001INCREMENT BY 1MINVALUE 1NOMAXVALUENOCYCLECACHE 20;-- 使用序列
INSERT INTO hr.employees(emp_id, emp_name) 
VALUES (hr.emp_seq.NEXTVAL, '張三');
SELECT hr.emp_seq.CURRVAL FROM DUAL; -- 查詢當前值

二、DML（數據操作語言）：操作表數據

1. 插入數據（INSERT）

-- 標準插入
INSERT INTO table_name (column1, column2) VALUES (value1, value2);
INSERT INTO hr.employees(emp_id, emp_name, salary) 
VALUES (1001, '張三', 8000);-- 插入查詢結果
INSERT INTO target_table SELECT * FROM source_table;
INSERT INTO hr.emp_backup SELECT * FROM hr.employees;-- 批量插入（Oracle 特有）
INSERT ALLINTO dept_emp VALUES (1001, 10)INTO dept_emp VALUES (1002, 20)
SELECT * FROM DUAL;

2. 更新數據（UPDATE）

-- 單表更新
UPDATE table_name SET column1 = value1, column2 = value2
WHERE condition;
UPDATE hr.employees SET salary = salary * 1.1
WHERE dept_id = 10 AND hire_date < '2020-01-01';-- 多表更新（Oracle 特有）
UPDATE hr.employees e
SET e.salary = (SELECT avg(salary) FROM hr.employees WHERE dept_id = e.dept_id)
WHERE e.salary < (SELECT avg(salary) FROM hr.employees WHERE dept_id = e.dept_id);

3. 刪除數據（DELETE）

-- 刪除符合條件的記錄
DELETE FROM table_name WHERE condition;
DELETE FROM hr.employees WHERE hire_date < '2015-01-01';-- 清空表（與TRUNCATE區別：DELETE可回滾，TRUNCATE不可回滾）
DELETE FROM hr.employees;

4. 查詢數據（SELECT）

-- 基礎查詢
SELECT column1, column2 FROM table_name WHERE condition;
SELECT emp_name, salary FROM hr.employees WHERE dept_id = 10;-- 去重與別名
SELECT DISTINCT dept_id FROM hr.employees;
SELECT emp_name AS "員工姓名", salary * 12 AS "年薪" FROM hr.employees;-- 排序與限制（Oracle 12c+支持LIMIT，傳統用ROWNUM）
SELECT * FROM hr.employees ORDER BY salary DESC, emp_name ASC;
SELECT * FROM hr.employees WHERE ROWNUM <= 10; -- 前10條-- 多表連接
-- 內連接
SELECT e.emp_name, d.dept_name
FROM hr.employees e INNER JOIN hr.departments d
ON e.dept_id = d.dept_id;-- 外連接
SELECT e.emp_name, d.dept_name
FROM hr.employees e LEFT JOIN hr.departments d
ON e.dept_id = d.dept_id;-- 子查詢
SELECT emp_name, salary
FROM hr.employees
WHERE salary > (SELECT avg(salary) FROM hr.employees);

三、TCL（事務控制語言）：管理事務

-- 開始事務（隱式開始，無需命令）
BEGIN TRANSACTION; -- 非Oracle語法，Oracle自動開始事務-- 提交事務
COMMIT; -- 永久保存數據變更-- 回滾事務
ROLLBACK; -- 撤銷未提交的變更-- 設置保存點
SAVEPOINT savepoint_name;
UPDATE hr.employees SET salary = salary * 1.1 WHERE dept_id = 10;
SAVEPOINT dept10_updated;
UPDATE hr.employees SET salary = salary * 1.2 WHERE dept_id = 20;
ROLLBACK TO dept10_updated; -- 回滾到dept10_updated點，保留dept10的更新-- 自動提交（會話級設置）
SET AUTOCOMMIT ON; -- 每條DML后自動提交

四、DCL（數據控制語言）：管理權限

1. 用戶管理

-- 創建用戶
CREATE USER username IDENTIFIED BY passwordDEFAULT TABLESPACE tablespace_nameQUOTA nG ON tablespace_name;
CREATE USER hr_user IDENTIFIED BY hr123DEFAULT TABLESPACE usersQUOTA 500M ON users;-- 修改密碼
ALTER USER username IDENTIFIED BY new_password;
ALTER USER hr_user IDENTIFIED BY hr456;-- 刪除用戶
DROP USER username [CASCADE]; -- CASCADE刪除用戶所有對象
DROP USER hr_user CASCADE;

2. 權限管理

-- 授予權限
-- 系統權限（如創建表、刪除用戶）
GRANT CREATE TABLE, ALTER USER TO username;
GRANT CREATE SESSION TO hr_user; -- 允許登錄數據庫-- 對象權限（表、視圖等）
GRANT SELECT, INSERT, UPDATE ON hr.employees TO hr_user;
GRANT ALL PRIVILEGES ON hr.departments TO hr_user;-- 授予角色
GRANT DBA, CONNECT TO username; -- DBA角色擁有全部權限-- 回收權限
REVOKE CREATE TABLE FROM username;
REVOKE UPDATE ON hr.employees FROM hr_user;

五、常用函數與表達式

1. 字符串函數

-- 拼接：CONCAT或||
SELECT CONCAT(emp_name, ' works in ') || dept_name
FROM hr.employees e, hr.departments d
WHERE e.dept_id = d.dept_id;-- 長度：LENGTH
SELECT emp_name, LENGTH(emp_name) FROM hr.employees;-- 大小寫轉換：UPPER, LOWER, INITCAP
SELECT UPPER(emp_name) FROM hr.employees;
SELECT INITCAP(emp_name) FROM hr.employees; -- 首字母大寫-- 截取：SUBSTR(start, length)
SELECT SUBSTR(emp_name, 1, 3) FROM hr.employees; -- 前3個字符-- 替換：REPLACE
SELECT REPLACE(emp_name, '張', '王') FROM hr.employees;

2. 數值函數

-- 四舍五入：ROUND(n, decimal)
SELECT ROUND(salary, -3) FROM hr.employees; -- 四舍五入到千位-- 取整：FLOOR, CEIL
SELECT FLOOR(3.7), CEIL(3.1) FROM DUAL; -- 3, 4-- 絕對值：ABS
SELECT ABS(-100) FROM DUAL; -- 100-- 冪運算：POWER(n, m)
SELECT POWER(2, 3) FROM DUAL; -- 8

3. 日期函數

-- 獲取當前日期：SYSDATE
SELECT SYSDATE FROM DUAL; -- 格式：2025-06-18 15:30:00-- 日期運算：加減天數
SELECT SYSDATE + 7 FROM DUAL; -- 一周后
SELECT hire_date, SYSDATE - hire_date AS "入職天數"
FROM hr.employees;-- 提取部分日期：EXTRACT
SELECT EXTRACT(YEAR FROM hire_date) AS "入職年份"
FROM hr.employees;-- 日期格式化：TO_CHAR(date, '格式')
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL; -- 2025-06-18 15:30:00

4. 轉換函數

-- 字符串轉日期：TO_DATE
SELECT TO_DATE('2025-01-01', 'YYYY-MM-DD') FROM DUAL;-- 數字轉字符串：TO_CHAR
SELECT TO_CHAR(salary, 'L999,999.00') FROM hr.employees; -- 帶貨幣符號格式化-- 日期轉數字：TO_NUMBER
SELECT TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY')) FROM DUAL; -- 獲取年份數字

六、PL/SQL 基礎語句

1. 存儲過程（PROCEDURE）

-- 創建存儲過程：計算員工平均工資
CREATE OR REPLACE PROCEDURE hr.get_avg_salary(p_dept_id IN NUMBER,p_avg_salary OUT NUMBER
) AS
BEGINSELECT AVG(salary) INTO p_avg_salaryFROM hr.employeesWHERE dept_id = p_dept_id;IF p_avg_salary IS NULL THENp_avg_salary := 0;END IF;
END;
/-- 調用存儲過程
DECLAREv_avg_salary NUMBER;
BEGINhr.get_avg_salary(10, v_avg_salary);DBMS_OUTPUT.PUT_LINE('部門10平均工資：' || v_avg_salary);
END;
/

2. 函數（FUNCTION）

-- 創建函數：返回員工姓名
CREATE OR REPLACE FUNCTION hr.get_emp_name(p_emp_id IN NUMBER)
RETURN VARCHAR2 ASv_emp_name VARCHAR2(50);
BEGINSELECT emp_name INTO v_emp_nameFROM hr.employeesWHERE emp_id = p_emp_id;RETURN v_emp_name;
EXCEPTIONWHEN NO_DATA_FOUND THENRETURN '員工不存在';
END;
/-- 調用函數
SELECT hr.get_emp_name(1001) FROM DUAL;

3. 觸發器（TRIGGER）

-- 創建觸發器：記錄員工工資變更日志
CREATE OR REPLACE TRIGGER hr.log_salary_change
AFTER UPDATE OF salary ON hr.employees
FOR EACH ROW
BEGININSERT INTO hr.salary_log (emp_id, old_salary, new_salary, update_time)VALUES (:OLD.emp_id, :OLD.salary, :NEW.salary, SYSDATE);
END;
/

七、Oracle 特有功能語句

1. 分區表操作

-- 創建分區表（范圍分區）
CREATE TABLE hr.sales_history (sale_id NUMBER,sale_date DATE,amount NUMBER
)
PARTITION BY RANGE (sale_date) (PARTITION p_2024 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD')),PARTITION p_2025 VALUES LESS THAN (TO_DATE('2026-01-01', 'YYYY-MM-DD')),PARTITION p_other VALUES LESS THAN (MAXVALUE)
);-- 新增分區
ALTER TABLE hr.sales_history ADD PARTITION p_2026 VALUES LESS THAN (TO_DATE('2027-01-01', 'YYYY-MM-DD'));

2. 物化視圖（Materialized View）

-- 創建物化視圖（自動刷新）
CREATE MATERIALIZED VIEW hr.dept_salary_summary
REFRESH COMPLETE ON DEMAND
AS
SELECT dept_id, AVG(salary) avg_sal, COUNT(*) emp_count
FROM hr.employees
GROUP BY dept_id;-- 手動刷新物化視圖
REFRESH MATERIALIZED VIEW hr.dept_salary_summary;

3. 閃回查詢（Flashback Query）

-- 查詢過去時間點的數據
SELECT * FROM hr.employees
AS OF TIMESTAMP SYSDATE - 1 -- 24小時前的數據-- 閃回刪除的表（回收站功能）
FLASHBACK TABLE hr.employees TO BEFORE DROP;

八、常用系統視圖與查詢

-- 查看用戶所有表
SELECT table_name FROM user_tables;-- 查看表結構
DESCRIBE hr.employees;
SELECT column_name, data_type, nullable
FROM user_tab_columns
WHERE table_name = 'EMPLOYEES';-- 查看用戶權限
SELECT * FROM user_sys_privs;
SELECT * FROM user_tab_privs;-- 查看數據庫性能視圖（需要DBA權限）
SELECT * FROM v$session;
SELECT * FROM v$instance;