Oracle数据库作为全球领先的企业级关系型数据库管理系统，凭借其卓越的性能、高可用性、强大的安全机制和完善的企业级功能，成为金融、电信、政府等关键行业的核心数据平台。本文将系统全面地梳理Oracle数据库的核心知识点，从基础概念到高级特性，帮助初学者建立完整的知识体系，也为有经验的DBA和开发者提供深入的技术参考。

一、Oracle数据库基础

1.1 Oracle数据库概述
Oracle数据库是由Oracle公司开发的关系型数据库管理系统（RDBMS），以其高可靠性、高性能、可扩展性和完善的企业级功能著称。

核心特性：

高可用性（RAC、Data Guard）

强大的并发控制机制

完善的安全体系

丰富的内置功能（分区、物化视图、高级队列等）

PL/SQL过程化编程语言

跨平台支持（Linux、Unix、Windows）

1.2 数据库安装与配置
Linux安装（Oracle Linux 7+）：

# 安装依赖包
yum install -y binutils compat-libcap1 compat-libstdc++-33 gcc gcc-c++ glibc glibc-devel ksh libaio libaio-devel libgcc libstdc++ libstdc++-devel libXext libXtst libX11 libXau libXi make sysstat

# 创建Oracle用户和组
groupadd oinstall
groupadd dba
useradd -g oinstall -G dba oracle
passwd oracle

# 配置内核参数
cat >> /etc/sysctl.conf << EOF
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 4294967295
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
EOF
sysctl -p

# 配置用户限制
cat >> /etc/security/limits.conf << EOF
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
oracle soft stack 10240
oracle hard stack 32768
EOF

# 创建安装目录
mkdir -p /u01/app/oracle/product/19.0.0/dbhome_1
mkdir -p /u01/app/oracle/oradata
chown -R oracle:oinstall /u01/app/oracle
chmod -R 775 /u01/app/oracle

# 设置环境变量
su - oracle
cat >> ~/.bash_profile << EOF
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=\$ORACLE_BASE/product/19.0.0/dbhome_1
export ORACLE_SID=orcl
export PATH=\$PATH:\$ORACLE_HOME/bin
export LD_LIBRARY_PATH=\$ORACLE_HOME/lib:/usr/lib
EOF
source ~/.bash_profile

Windows安装：
下载Oracle Database安装程序
运行安装向导，选择“创建和配置数据库”
选择“桌面类”或“服务器类”安装
设置全局数据库名、管理员密码
完成安装
1.3 Oracle体系结构
物理结构：

-- 查看数据文件
SELECT file_name, tablespace_name, bytes/1024/1024 AS size_mb 
FROM dba_data_files;

-- 查看控制文件
SELECT name FROM v$controlfile;

-- 查看重做日志文件
SELECT member FROM v$logfile;

-- 查看归档日志
ARCHIVE LOG LIST;

内存结构（SGA - 系统全局区）：
Buffer Cache：缓存数据块
Shared Pool：缓存SQL、执行计划、数据字典
Redo Log Buffer：缓存重做记录
Large Pool：用于并行查询、RMAN备份
Java Pool：用于Java存储过程

进程结构：
PMON（进程监控）：监控用户进程，清理异常进程
SMON（系统监控）：实例恢复，回收临时段
DBWn（数据库写入）：将Buffer Cache写入数据文件
LGWR（日志写入）：将Redo Log Buffer写入日志文件
CKPT（检查点）：更新数据文件头，触发DBWn
ARCn（归档进程）：归档重做日志

-- 查看内存结构
SHOW SGA;
SELECT * FROM v$sgainfo;

-- 查看后台进程
SELECT pname, description FROM v$bgprocess WHERE pname IS NOT NULL;

二、SQL基础

2.1 数据类型

-- 创建表示例
CREATE TABLE employees (
    emp_id NUMBER(10) PRIMARY KEY,
    emp_name VARCHAR2(50) NOT NULL,
    hire_date DATE DEFAULT SYSDATE,
    salary NUMBER(10,2),
    resume CLOB,
    photo BLOB,
    status CHAR(1) DEFAULT 'A'
);

2.2 数据库对象
表（TABLE）：

-- 创建表
CREATE TABLE dept (
    dept_id NUMBER(4) PRIMARY KEY,
    dept_name VARCHAR2(50) NOT NULL UNIQUE,
    location VARCHAR2(100),
    created_date DATE DEFAULT SYSDATE
);

-- 创建临时表（会话级）
CREATE GLOBAL TEMPORARY TABLE temp_sales (
    sale_id NUMBER,
    sale_date DATE,
    amount NUMBER
) ON COMMIT PRESERVE ROWS;

-- 创建临时表（事务级）
CREATE GLOBAL TEMPORARY TABLE temp_data (
    col1 NUMBER,
    col2 VARCHAR2(100)
) ON COMMIT DELETE ROWS;

索引（INDEX）：

-- B-Tree索引（默认）
CREATE INDEX idx_emp_name ON employees(emp_name);

-- 唯一索引
CREATE UNIQUE INDEX idx_emp_email ON employees(email);

-- 位图索引（适合低基数列）
CREATE BITMAP INDEX idx_emp_status ON employees(status);

-- 函数索引
CREATE INDEX idx_emp_name_upper ON employees(UPPER(emp_name));

-- 反向键索引（减少热点块竞争）
CREATE INDEX idx_emp_id_rev ON employees(emp_id) REVERSE;

-- 分区索引
CREATE INDEX idx_sales_date ON sales(sale_date) LOCAL;

视图（VIEW）：

-- 创建普通视图
CREATE VIEW emp_dept_view AS
SELECT e.emp_id, e.emp_name, e.salary, d.dept_name
FROM employees e
INNER JOIN dept d ON e.dept_id = d.dept_id;

-- 创建物化视图
CREATE MATERIALIZED VIEW mv_sales_summary
REFRESH COMPLETE ON DEMAND
AS
SELECT dept_id, SUM(amount) total_sales, COUNT(*) sale_count
FROM sales
GROUP BY dept_id;

-- 刷新物化视图
EXEC DBMS_MVIEW.REFRESH('MV_SALES_SUMMARY', 'C');

序列（SEQUENCE）：

-- 创建序列
CREATE SEQUENCE emp_seq
    START WITH 1000
    INCREMENT BY 1
    MAXVALUE 9999999
    NOCYCLE
    CACHE 20;

-- 使用序列
INSERT INTO employees (emp_id, emp_name) 
VALUES (emp_seq.NEXTVAL, '张三');

SELECT emp_seq.CURRVAL FROM dual;

同义词（SYNONYM）：

-- 创建公共同义词
CREATE PUBLIC SYNONYM emp FOR scott.employees;

-- 创建私有同义词
CREATE SYNONYM dept FOR scott.departments;

-- 删除同义词
DROP PUBLIC SYNONYM emp;

2.3 SQL语句
SELECT查询：

-- 基础查询
SELECT * FROM employees;
SELECT emp_name, salary FROM employees WHERE dept_id = 10;

-- 条件查询
SELECT * FROM employees 
WHERE salary BETWEEN 5000 AND 10000
AND emp_name LIKE '张%'
AND dept_id IN (10, 20, 30)
AND hire_date >= DATE '2023-01-01';

-- 排序
SELECT * FROM employees 
ORDER BY salary DESC, hire_date ASC;

-- 分页查询（ROWNUM）
SELECT * FROM (
    SELECT e.*, ROWNUM rn 
    FROM employees e 
    WHERE ROWNUM <= 20
) WHERE rn >= 11;

-- 分页查询（ROW_NUMBER，Oracle 12c+）
SELECT * FROM employees
ORDER BY emp_id
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

-- 聚合查询
SELECT 
    dept_id,
    COUNT(*) emp_count,
    AVG(salary) avg_salary,
    SUM(salary) total_salary,
    MAX(salary) max_salary,
    MIN(salary) min_salary
FROM employees
GROUP BY dept_id
HAVING COUNT(*) > 5;

-- 多表连接
SELECT e.emp_name, d.dept_name, s.sale_amount
FROM employees e
INNER JOIN dept d ON e.dept_id = d.dept_id
LEFT JOIN sales s ON e.emp_id = s.emp_id;

-- 子查询
SELECT * FROM employees 
WHERE salary > (SELECT AVG(salary) FROM employees);

-- 相关子查询
SELECT * FROM employees e1
WHERE salary > (
    SELECT AVG(salary) 
    FROM employees e2 
    WHERE e2.dept_id = e1.dept_id
);

INSERT/UPDATE/DELETE：

-- 插入单行
INSERT INTO employees (emp_id, emp_name, dept_id, salary)
VALUES (1001, '李四', 10, 8000);

-- 插入多行
INSERT INTO employees (emp_id, emp_name, dept_id, salary)
SELECT emp_id + 2000, emp_name, dept_id, salary
FROM employees WHERE dept_id = 10;

-- 多表插入
INSERT ALL
    WHEN salary > 8000 THEN INTO emp_high VALUES (emp_id, emp_name, salary)
    WHEN salary <= 5000 THEN INTO emp_low VALUES (emp_id, emp_name, salary)
    ELSE INTO emp_mid VALUES (emp_id, emp_name, salary)
SELECT emp_id, emp_name, salary FROM employees;

-- 更新
UPDATE employees 
SET salary = salary * 1.1
WHERE dept_id = 10;

-- 删除
DELETE FROM employees 
WHERE hire_date < SYSDATE - 365*3;

-- MERGE（合并操作）
MERGE INTO employees e
USING new_employees n ON (e.emp_id = n.emp_id)
WHEN MATCHED THEN
    UPDATE SET e.salary = n.salary, e.emp_name = n.emp_name
WHEN NOT MATCHED THEN
    INSERT (emp_id, emp_name, salary) VALUES (n.emp_id, n.emp_name, n.salary);

三、PL/SQL编程

3.1 PL/SQL基础
块结构：

DECLARE
    -- 声明部分
    v_emp_name VARCHAR2(50);
    v_salary NUMBER(10,2);
    CURSOR emp_cursor IS SELECT emp_name, salary FROM employees;
BEGIN
    -- 执行部分
    SELECT emp_name, salary INTO v_emp_name, v_salary
    FROM employees WHERE emp_id = 1001;

    DBMS_OUTPUT.PUT_LINE('员工：' || v_emp_name || '，工资：' || v_salary);
EXCEPTION
    -- 异常处理部分
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('未找到员工');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('错误：' || SQLERRM);
END;
/

变量与常量：

DECLARE
    -- 基本变量
    v_name VARCHAR2(50) := '张三';
    v_age NUMBER(3) DEFAULT 25;
    v_today DATE := SYSDATE;

    -- 常量
    c_pi CONSTANT NUMBER := 3.14159;

    -- 锚定类型
    v_emp_name employees.emp_name%TYPE;
    v_emp_row employees%ROWTYPE;

    -- 记录类型
    TYPE emp_record_type IS RECORD (
        emp_id NUMBER,
        emp_name VARCHAR2(50),
        salary NUMBER
    );
    v_emp_record emp_record_type;
BEGIN
    v_emp_name := '李四';
    v_emp_record.emp_id := 1001;
    v_emp_record.emp_name := '王五';
END;
/

3.2 控制结构
条件控制：

-- IF语句
IF v_salary > 10000 THEN
    v_bonus := v_salary * 0.2;
ELSIF v_salary > 5000 THEN
    v_bonus := v_salary * 0.1;
ELSE
    v_bonus := v_salary * 0.05;
END IF;

-- CASE语句
CASE v_status
    WHEN 'A' THEN
        v_level := '优秀';
    WHEN 'B' THEN
        v_level := '良好';
    WHEN 'C' THEN
        v_level := '合格';
    ELSE
        v_level := '待改进';
END CASE;

-- CASE表达式
v_level := CASE v_status
            WHEN 'A' THEN '优秀'
            WHEN 'B' THEN '良好'
            ELSE '普通'
           END;

循环控制：

-- 基本LOOP
DECLARE
    v_counter NUMBER := 1;
BEGIN
    LOOP
        DBMS_OUTPUT.PUT_LINE('计数：' || v_counter);
        v_counter := v_counter + 1;
        EXIT WHEN v_counter > 10;
    END LOOP;
END;
/

-- WHILE LOOP
DECLARE
    v_counter NUMBER := 1;
BEGIN
    WHILE v_counter <= 10 LOOP
        DBMS_OUTPUT.PUT_LINE('计数：' || v_counter);
        v_counter := v_counter + 1;
    END LOOP;
END;
/

-- FOR LOOP
BEGIN
    FOR i IN 1..10 LOOP
        DBMS_OUTPUT.PUT_LINE('计数：' || i);
    END LOOP;

    -- 反向循环
    FOR i IN REVERSE 1..10 LOOP
        DBMS_OUTPUT.PUT_LINE('反向：' || i);
    END LOOP;
END;
/

3.3 游标
显式游标：

DECLARE
    CURSOR emp_cursor IS
        SELECT emp_id, emp_name, salary 
        FROM employees 
        WHERE dept_id = 10;

    v_emp_id employees.emp_id%TYPE;
    v_emp_name employees.emp_name%TYPE;
    v_salary employees.salary%TYPE;
BEGIN
    OPEN emp_cursor;
    LOOP
        FETCH emp_cursor INTO v_emp_id, v_emp_name, v_salary;
        EXIT WHEN emp_cursor%NOTFOUND;

        DBMS_OUTPUT.PUT_LINE(v_emp_id || ' - ' || v_emp_name);

        -- 更新当前行
        UPDATE employees SET salary = v_salary * 1.1 
        WHERE CURRENT OF emp_cursor;
    END LOOP;
    CLOSE emp_cursor;

    COMMIT;
END;
/

-- 游标FOR循环（自动打开、关闭）
DECLARE
    CURSOR emp_cursor IS
        SELECT emp_id, emp_name, salary 
        FROM employees;
BEGIN
    FOR emp_rec IN emp_cursor LOOP
        DBMS_OUTPUT.PUT_LINE(emp_rec.emp_name);
    END LOOP;
END;
/

-- 游标变量（REF CURSOR）
DECLARE
    TYPE ref_cursor_type IS REF CURSOR;
    v_cursor ref_cursor_type;
    v_emp_name employees.emp_name%TYPE;
    v_dept_name dept.dept_name%TYPE;
BEGIN
    OPEN v_cursor FOR 
        SELECT e.emp_name, d.dept_name
        FROM employees e
        INNER JOIN dept d ON e.dept_id = d.dept_id;

    LOOP
        FETCH v_cursor INTO v_emp_name, v_dept_name;
        EXIT WHEN v_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_emp_name || ' - ' || v_dept_name);
    END LOOP;

    CLOSE v_cursor;
END;
/

3.4 异常处理

DECLARE
    v_emp_id NUMBER := 9999;
    v_emp_name VARCHAR2(50);
BEGIN
    SELECT emp_name INTO v_emp_name 
    FROM employees 
    WHERE emp_id = v_emp_id;

    DBMS_OUTPUT.PUT_LINE('员工：' || v_emp_name);

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('员工ID ' || v_emp_id || ' 不存在');
        -- 插入错误日志
        INSERT INTO error_log (error_date, error_message)
        VALUES (SYSDATE, '员工不存在: ' || v_emp_id);

    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('返回多条记录');

    WHEN DUP_VAL_ON_INDEX THEN
        DBMS_OUTPUT.PUT_LINE('违反唯一约束');

    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('错误代码: ' || SQLCODE);
        DBMS_OUTPUT.PUT_LINE('错误信息: ' || SQLERRM);

        -- 记录详细错误信息
        INSERT INTO error_log (error_date, error_code, error_message, error_stack)
        VALUES (SYSDATE, SQLCODE, SQLERRM, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);

        -- 重新抛出异常
        RAISE;
END;
/

-- 自定义异常
DECLARE
    e_invalid_salary EXCEPTION;
    PRAGMA EXCEPTION_INIT(e_invalid_salary, -20001);

    v_salary NUMBER := -100;
BEGIN
    IF v_salary < 0 THEN
        RAISE_APPLICATION_ERROR(-20001, '工资不能为负数');
    END IF;
EXCEPTION
    WHEN e_invalid_salary THEN
        DBMS_OUTPUT.PUT_LINE('无效工资');
END;
/

来源：
https://app-ah8jla8z2m81.appmiaoda.com

Oracle数据库学习知识点（一）

一、Oracle数据库基础

二、SQL基础

三、PL/SQL编程

热门文章

最新文章

相关电子书

探索云世界

热门

云计算

大数据

云原生

人工智能

数据库

开发与运维

活动广场

任务中心

训练营

直播

乘风者计划

下载

镜像站

技术资料

Oracle数据库学习知识点（一）

一、Oracle数据库基础

二、SQL基础

三、PL/SQL编程

热门文章

最新文章

相关电子书