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_name
    START WITH n
    INCREMENT BY n
    MINVALUE n | NOMINVALUE
    MAXVALUE n | NOMAXVALUE
    CYCLE | NOCYCLE
    CACHE n | NOCACHE;

-- 示例：创建员工ID序列
CREATE SEQUENCE hr.emp_seq
    START WITH 1001
    INCREMENT BY 1
    MINVALUE 1
    NOMAXVALUE
    NOCYCLE
    CACHE 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 ALL
    INTO 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 password
    DEFAULT TABLESPACE tablespace_name
    QUOTA nG ON tablespace_name;
CREATE USER hr_user IDENTIFIED BY hr123
    DEFAULT TABLESPACE users
    QUOTA 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
BEGIN
    SELECT AVG(salary) INTO p_avg_salary
    FROM hr.employees
    WHERE dept_id = p_dept_id;
    IF p_avg_salary IS NULL THEN
        p_avg_salary := 0;
    END IF;
END;
/

-- 调用存储过程
DECLARE
    v_avg_salary NUMBER;
BEGIN
    hr.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 AS
    v_emp_name VARCHAR2(50);
BEGIN
    SELECT emp_name INTO v_emp_name
    FROM hr.employees
    WHERE emp_id = p_emp_id;
    RETURN v_emp_name;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN '员工不存在';
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
BEGIN
    INSERT 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;

总结：Oracle 基础语句核心要点

• DDL：掌握表、索引、序列的创建与修改，注意 Oracle 特有数据类型（如 VARCHAR2、NUMBER）和约束机制。
• DML：查询语句是核心，多表连接、子查询和 ROWNUM 分页是高频场景。
• TCL：事务控制是数据一致性的关键，SAVEPOINT 可实现部分回滚。
• PL/SQL：存储过程和函数用于封装业务逻辑，触发器实现数据变更自动化。
• 特有功能：序列、分区表、物化视图等是 Oracle 企业级能力的体现，需结合业务场景使用。