Oracle数据库入门教程 - 从零开始学习Oracle数据库

目录

1. Oracle数据库简介
2. 环境搭建与安装
3. 数据库基础概念
4. SQL语言基础
5. 数据查询（SELECT）
6. 数据操作（DML）
7. 数据定义（DDL）
8. 数据控制（DCL）
9. PL/SQL编程
10. 存储过程和函数
11. 触发器
12. 索引和性能优化
13. 用户和权限管理
14. 备份与恢复
15. 实际应用案例
16. 总结与进阶

---

点击这里👇🏻获取：100万QPS短链系统、复杂的商城微服务系统、智能翻译助手AI Agent、SaaS点餐系统、刷题吧小程序、商城系统、秒杀系统、AI项目、代码生成神器、苏三demo项目等 8 个项目的：项目源代码、开发教程和技术答疑

1. Oracle数据库简介

Oracle数据库是由Oracle公司开发的关系型数据库管理系统（RDBMS），是目前世界上使用最广泛的企业级数据库之一。Oracle数据库具有以下特点：

• 高可靠性：提供数据保护、故障恢复和备份机制

• 高性能：优化的查询引擎和并行处理能力

• 可扩展性：支持从小型应用到大型企业级应用

• 安全性：完善的安全机制和权限管理

• 跨平台：支持多种操作系统

• Oracle Database 19c：当前长期支持版本（LTS）

• Oracle Database 21c：最新版本

• Oracle Database 23c：未来版本

实例（Instance）

Oracle实例是运行在内存中的数据库管理系统，包括：

• SGA（System Global Area）：系统全局区，共享内存区域
• 后台进程：执行数据库操作的进程

数据库（Database）

数据库是存储在磁盘上的物理文件集合，包括：

• 数据文件：存储实际数据

• 控制文件：记录数据库结构信息

• 日志文件：记录数据库变更

• 企业级应用：ERP、CRM等大型系统

• 金融行业：银行、证券、保险系统

• 电信行业：计费系统、客户管理系统

• 政府机构：政务系统、数据仓库

• 电商平台：大型电商网站

---

2. 环境搭建与安装

Windows系统

• 操作系统：Windows 10/11 或 Windows Server 2016+
• 内存：至少2GB（推荐4GB以上）
• 磁盘空间：至少10GB可用空间
• 处理器：x64架构

Linux系统

• 操作系统：Oracle Linux、Red Hat、CentOS、Ubuntu等
• 内存：至少2GB（推荐4GB以上）
• 磁盘空间：至少10GB可用空间

1. 访问Oracle官网：https://www.oracle.com/database/technologies/oracle-database-software-downloads.html
2. 选择适合的版本和操作系统
3. 注册Oracle账号（免费）
4. 下载安装文件

步骤1：解压安装文件

# 解压下载的zip文件到指定目录
# 例如：C:\oracle\database

步骤2：运行安装程序

1. 以管理员身份运行 setup.exe
2. 选择"创建和配置数据库"
3. 选择"桌面类"（学习用）或"服务器类"（生产用）

步骤3：配置安装选项

• 安装位置：选择Oracle主目录
• 数据库类型：选择"一般用途/事务处理"
• 数据库标识：
  • 全局数据库名：orcl（可自定义）
  • SID：orcl（可自定义）
• 数据库口令：设置SYS、SYSTEM等用户密码

步骤4：完成安装

等待安装完成，通常需要30-60分钟。

点击这里👇🏻获取：100万QPS短链系统、复杂的商城微服务系统、智能翻译助手AI Agent、SaaS点餐系统、刷题吧小程序、商城系统、秒杀系统、AI项目、代码生成神器、苏三demo项目等 8 个项目的：项目源代码、开发教程和技术答疑

2.4 Linux安装步骤

步骤1：准备环境

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

# 创建目录
mkdir -p /u01/app/oracle
chown -R oracle:oinstall /u01/app/oracle

步骤2：安装依赖

# CentOS/RHEL
yum install -y binutils compat-libcap1 compat-libstdc++ gcc gcc-c++ glibc glibc-devel ksh libaio libaio-devel libgcc libstdc++ libstdc++-devel libXi libXtst make sysstat

# Ubuntu/Debian
apt-get install -y binutils gcc g++ libaio1 libaio-dev libstdc++6 libstdc++6-4.8-dev libc6-dev make sysstat

步骤3：配置环境变量

编辑 ~/.bash_profile：

export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/19.3.0/dbhome_1
export ORACLE_SID=orcl
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH

步骤4：运行安装程序

# 切换到oracle用户
su - oracle

# 运行安装程序
./runInstaller

点击这里👇🏻获取：100万QPS短链系统、复杂的商城微服务系统、智能翻译助手AI Agent、SaaS点餐系统、刷题吧小程序、商城系统、秒杀系统、AI项目、代码生成神器、苏三demo项目等 8 个项目的：项目源代码、开发教程和技术答疑

2.5 验证安装

检查服务状态

Windows：

# 检查Oracle服务
services.msc
# 查找OracleServiceORCL和OracleOraDB19Home1TNSListener服务

Linux：

# 检查进程
ps -ef | grep ora_

# 检查监听器
lsnrctl status

连接测试

# 使用SQL*Plus连接
sqlplus sys/password@orcl as sysdba

# 如果连接成功，会显示SQL提示符
SQL>

SQL Developer是Oracle提供的图形化数据库管理工具：

1. 下载SQL Developer：https://www.oracle.com/database/sqldeveloper/
2. 解压并运行 sqldeveloper.exe
3. 创建新连接：
   • 连接名：任意名称
   • 用户名：system
   • 密码：安装时设置的密码
   • 主机名：localhost
   • 端口：1521
   • SID：orcl

---

3. 数据库基础概念

表（Table）

表是存储数据的基本单位，由行和列组成。

-- 创建表示例
CREATE TABLE employees (
    employee_id NUMBER(6) PRIMARY KEY,
    first_name VARCHAR2(20),
    last_name VARCHAR2(25),
    email VARCHAR2(25),
    hire_date DATE,
    salary NUMBER(8,2)
);

视图（View）

视图是基于一个或多个表的虚拟表。

-- 创建视图示例
CREATE VIEW emp_view AS
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > 5000;

索引（Index）

索引用于提高查询性能。

-- 创建索引示例
CREATE INDEX idx_emp_name ON employees(last_name);

序列（Sequence）

序列用于生成唯一的数字。

-- 创建序列示例
CREATE SEQUENCE emp_seq
START WITH 1
INCREMENT BY 1;

同义词（Synonym）

同义词是对象的别名。

-- 创建同义词示例
CREATE SYNONYM emp FOR employees;

字符类型

• CHAR(n)：固定长度字符串，最大4000字节
• VARCHAR2(n)：可变长度字符串，最大4000字节
• NCHAR(n)：固定长度Unicode字符串
• NVARCHAR2(n)：可变长度Unicode字符串
• CLOB：大文本对象，最大4GB

数字类型

• NUMBER(p,s)：数字类型，p为精度，s为小数位数
• INTEGER：整数类型
• FLOAT：浮点数类型

日期类型

• DATE：日期和时间
• TIMESTAMP：时间戳，精度更高
• TIMESTAMP WITH TIME ZONE：带时区的时间戳

大对象类型

• BLOB：二进制大对象
• CLOB：字符大对象
• BFILE：外部文件引用

主键约束（PRIMARY KEY）

CREATE TABLE students (
    student_id NUMBER PRIMARY KEY,
    name VARCHAR2(50)
);

外键约束（FOREIGN KEY）

CREATE TABLE orders (
    order_id NUMBER PRIMARY KEY,
    customer_id NUMBER,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

唯一约束（UNIQUE）

CREATE TABLE users (
    user_id NUMBER PRIMARY KEY,
    email VARCHAR2(100) UNIQUE
);

非空约束（NOT NULL）

CREATE TABLE products (
    product_id NUMBER PRIMARY KEY,
    product_name VARCHAR2(100) NOT NULL
);

检查约束（CHECK）

CREATE TABLE employees (
    employee_id NUMBER PRIMARY KEY,
    salary NUMBER CHECK (salary > 0),
    age NUMBER CHECK (age >= 18 AND age <= 65)
);

---

4. SQL语言基础

SQL（Structured Query Language）是结构化查询语言，用于管理关系型数据库。

• DDL（Data Definition Language）：数据定义语言
  • CREATE、ALTER、DROP、TRUNCATE
• DML（Data Manipulation Language）：数据操作语言
  • SELECT、INSERT、UPDATE、DELETE
• DCL（Data Control Language）：数据控制语言
  • GRANT、REVOKE
• TCL（Transaction Control Language）：事务控制语言
  • COMMIT、ROLLBACK、SAVEPOINT

-- SELECT语句结构
SELECT column1, column2, ...
FROM table_name
WHERE condition
GROUP BY column
HAVING condition
ORDER BY column;

---

5. 数据查询（SELECT）

查询所有列

-- 查询employees表的所有数据
SELECT * FROM employees;

查询指定列

-- 查询指定列
SELECT employee_id, first_name, last_name, salary
FROM employees;

使用别名

-- 列别名
SELECT employee_id AS id, 
       first_name AS "First Name",
       salary * 12 AS "Annual Salary"
FROM employees;

-- 表别名
SELECT e.employee_id, e.first_name
FROM employees e;

比较运算符

-- 等于
SELECT * FROM employees WHERE salary = 5000;

-- 不等于
SELECT * FROM employees WHERE salary != 5000;
SELECT * FROM employees WHERE salary <> 5000;

-- 大于、小于
SELECT * FROM employees WHERE salary > 5000;
SELECT * FROM employees WHERE salary < 5000;
SELECT * FROM employees WHERE salary >= 5000;
SELECT * FROM employees WHERE salary <= 5000;

逻辑运算符

-- AND
SELECT * FROM employees 
WHERE salary > 5000 AND department_id = 10;

-- OR
SELECT * FROM employees 
WHERE salary > 5000 OR department_id = 10;

-- NOT
SELECT * FROM employees 
WHERE NOT salary > 5000;

IN和NOT IN

-- IN
SELECT * FROM employees 
WHERE department_id IN (10, 20, 30);

-- NOT IN
SELECT * FROM employees 
WHERE department_id NOT IN (10, 20);

BETWEEN...AND

-- 范围查询
SELECT * FROM employees 
WHERE salary BETWEEN 3000 AND 8000;

LIKE模糊查询

-- 以S开头
SELECT * FROM employees 
WHERE first_name LIKE 'S%';

-- 包含en
SELECT * FROM employees 
WHERE first_name LIKE '%en%';

-- 第二个字符是a
SELECT * FROM employees 
WHERE first_name LIKE '_a%';

-- 转义特殊字符
SELECT * FROM employees 
WHERE job_id LIKE '%\_%' ESCAPE '\';

IS NULL和IS NOT NULL

-- 空值查询
SELECT * FROM employees 
WHERE commission_pct IS NULL;

-- 非空查询
SELECT * FROM employees 
WHERE commission_pct IS NOT NULL;

-- 升序（默认）
SELECT * FROM employees 
ORDER BY salary ASC;

-- 降序
SELECT * FROM employees 
ORDER BY salary DESC;

-- 多列排序
SELECT * FROM employees 
ORDER BY department_id ASC, salary DESC;

单行函数

字符函数：

-- UPPER：转大写
SELECT UPPER(first_name) FROM employees;

-- LOWER：转小写
SELECT LOWER(first_name) FROM employees;

-- SUBSTR：截取字符串
SELECT SUBSTR(first_name, 1, 3) FROM employees;

-- LENGTH：长度
SELECT LENGTH(first_name) FROM employees;

-- CONCAT：连接字符串
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;

-- TRIM：去除空格
SELECT TRIM('  Hello  ') FROM dual;

数字函数：

-- ROUND：四舍五入
SELECT ROUND(123.456, 2) FROM dual; -- 123.46

-- TRUNC：截断
SELECT TRUNC(123.456, 2) FROM dual; -- 123.45

-- MOD：取余
SELECT MOD(10, 3) FROM dual; -- 1

-- ABS：绝对值
SELECT ABS(-10) FROM dual; -- 10

-- CEIL：向上取整
SELECT CEIL(123.45) FROM dual; -- 124

-- FLOOR：向下取整
SELECT FLOOR(123.45) FROM dual; -- 123

日期函数：

-- SYSDATE：当前日期时间
SELECT SYSDATE FROM dual;

-- ADD_MONTHS：添加月份
SELECT ADD_MONTHS(SYSDATE, 3) FROM dual;

-- MONTHS_BETWEEN：月份差
SELECT MONTHS_BETWEEN(SYSDATE, hire_date) FROM employees;

-- NEXT_DAY：下一个指定星期几
SELECT NEXT_DAY(SYSDATE, 'MONDAY') FROM dual;

-- LAST_DAY：月份最后一天
SELECT LAST_DAY(SYSDATE) FROM dual;

-- EXTRACT：提取日期部分
SELECT EXTRACT(YEAR FROM SYSDATE) FROM dual;

转换函数：

-- TO_CHAR：转换为字符串
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM dual;
SELECT TO_CHAR(salary, '$999,999.99') FROM employees;

-- TO_NUMBER：转换为数字
SELECT TO_NUMBER('123.45') FROM dual;

-- TO_DATE：转换为日期
SELECT TO_DATE('2024-01-15', 'YYYY-MM-DD') FROM dual;

通用函数：

-- NVL：空值处理
SELECT NVL(commission_pct, 0) FROM employees;

-- NVL2：空值处理（三值）
SELECT NVL2(commission_pct, salary * commission_pct, 0) FROM employees;

-- COALESCE：返回第一个非空值
SELECT COALESCE(commission_pct, salary, 0) FROM employees;

-- DECODE：条件判断
SELECT DECODE(department_id, 
              10, 'IT',
              20, 'HR',
              'Other') AS dept_name
FROM employees;

-- CASE表达式
SELECT 
    CASE 
        WHEN salary > 10000 THEN 'High'
        WHEN salary > 5000 THEN 'Medium'
        ELSE 'Low'
    END AS salary_level
FROM employees;

聚合函数

-- COUNT：计数
SELECT COUNT(*) FROM employees;
SELECT COUNT(DISTINCT department_id) FROM employees;

-- SUM：求和
SELECT SUM(salary) FROM employees;

-- AVG：平均值
SELECT AVG(salary) FROM employees;

-- MAX：最大值
SELECT MAX(salary) FROM employees;

-- MIN：最小值
SELECT MIN(salary) FROM employees;

-- 按部门分组统计
SELECT department_id, COUNT(*), AVG(salary)
FROM employees
GROUP BY department_id;

-- 多列分组
SELECT department_id, job_id, COUNT(*)
FROM employees
GROUP BY department_id, job_id;

-- HAVING用于过滤分组结果
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 5000;

内连接（INNER JOIN）

-- 等值连接
SELECT e.first_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

-- 使用WHERE（旧式语法）
SELECT e.first_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;

外连接（OUTER JOIN）

-- 左外连接
SELECT e.first_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

-- 右外连接
SELECT e.first_name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id;

-- 全外连接
SELECT e.first_name, d.department_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.department_id;

自连接

-- 查询员工及其经理
SELECT e.first_name AS employee, m.first_name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;

交叉连接（CROSS JOIN）

-- 笛卡尔积
SELECT * FROM employees CROSS JOIN departments;

单行子查询

-- 返回单个值的子查询
SELECT * FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

多行子查询

-- IN子查询
SELECT * FROM employees
WHERE department_id IN (
    SELECT department_id FROM departments WHERE location_id = 1700
);

-- ANY子查询
SELECT * FROM employees
WHERE salary > ANY (
    SELECT salary FROM employees WHERE department_id = 10
);

-- ALL子查询
SELECT * FROM employees
WHERE salary > ALL (
    SELECT salary FROM employees WHERE department_id = 10
);

相关子查询

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

EXISTS子查询

-- EXISTS检查子查询是否有结果
SELECT * FROM employees e
WHERE EXISTS (
    SELECT 1 FROM departments d
    WHERE d.department_id = e.department_id
);

-- UNION：并集（去重）
SELECT employee_id FROM employees WHERE department_id = 10
UNION
SELECT employee_id FROM employees WHERE department_id = 20;

-- UNION ALL：并集（不去重）
SELECT employee_id FROM employees WHERE department_id = 10
UNION ALL
SELECT employee_id FROM employees WHERE department_id = 20;

-- INTERSECT：交集
SELECT employee_id FROM employees WHERE department_id = 10
INTERSECT
SELECT employee_id FROM employees WHERE department_id = 20;

-- MINUS：差集
SELECT employee_id FROM employees WHERE department_id = 10
MINUS
SELECT employee_id FROM employees WHERE department_id = 20;

---

6. 数据操作（DML）

插入单行

-- 插入所有列
INSERT INTO employees VALUES (
    1001, 'John', 'Doe', 'john.doe@example.com',
    SYSDATE, 'IT_PROG', 5000, NULL, 100, 60
);

-- 插入指定列
INSERT INTO employees (
    employee_id, first_name, last_name, email,
    hire_date, job_id, salary, department_id
) VALUES (
    1002, 'Jane', 'Smith', 'jane.smith@example.com',
    SYSDATE, 'IT_PROG', 6000, 60
);

插入多行

-- 使用INSERT ALL
INSERT ALL
    INTO employees VALUES (1003, 'Alice', 'Brown', 'alice@example.com', SYSDATE, 'IT_PROG', 5500, NULL, 100, 60)
    INTO employees VALUES (1004, 'Bob', 'White', 'bob@example.com', SYSDATE, 'IT_PROG', 5800, NULL, 100, 60)
SELECT * FROM dual;

从其他表插入

-- 从查询结果插入
INSERT INTO employees_backup
SELECT * FROM employees WHERE department_id = 60;

-- 更新单行
UPDATE employees
SET salary = 6000
WHERE employee_id = 1001;

-- 更新多列
UPDATE employees
SET salary = 6500, department_id = 70
WHERE employee_id = 1002;

-- 使用子查询更新
UPDATE employees
SET salary = (SELECT AVG(salary) FROM employees)
WHERE department_id = 60;

-- 删除指定行
DELETE FROM employees WHERE employee_id = 1001;

-- 删除所有数据（危险！）
DELETE FROM employees;

-- 使用子查询删除
DELETE FROM employees
WHERE department_id IN (
    SELECT department_id FROM departments WHERE location_id = 1700
);

-- MERGE：根据条件插入或更新
MERGE INTO employees_target t
USING employees_source s
ON (t.employee_id = s.employee_id)
WHEN MATCHED THEN
    UPDATE SET t.salary = s.salary
WHEN NOT MATCHED THEN
    INSERT (employee_id, first_name, last_name, salary)
    VALUES (s.employee_id, s.first_name, s.last_name, s.salary);

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

-- 保存点
SAVEPOINT sp1;
-- 一些操作
ROLLBACK TO sp1;

---

7. 数据定义（DDL）

创建表

-- 基本创建表
CREATE TABLE students (
    student_id NUMBER PRIMARY KEY,
    name VARCHAR2(50) NOT NULL,
    age NUMBER(3),
    email VARCHAR2(100) UNIQUE,
    enrollment_date DATE DEFAULT SYSDATE
);

-- 从现有表创建
CREATE TABLE employees_copy AS
SELECT * FROM employees WHERE 1=0; -- 只复制结构

CREATE TABLE employees_data AS
SELECT * FROM employees WHERE department_id = 60; -- 复制结构和数据

创建索引

-- 单列索引
CREATE INDEX idx_emp_name ON employees(last_name);

-- 复合索引
CREATE INDEX idx_emp_dept_sal ON employees(department_id, salary);

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

-- 函数索引
CREATE INDEX idx_emp_upper_name ON employees(UPPER(last_name));

创建视图

-- 简单视图
CREATE VIEW emp_view AS
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > 5000;

-- 复杂视图
CREATE VIEW dept_summary AS
SELECT d.department_name, COUNT(e.employee_id) AS emp_count, AVG(e.salary) AS avg_salary
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name;

创建序列

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

-- 使用序列
INSERT INTO employees (employee_id, first_name, last_name)
VALUES (emp_seq.NEXTVAL, 'John', 'Doe');

-- 获取当前值
SELECT emp_seq.CURRVAL FROM dual;

创建同义词

-- 创建同义词
CREATE SYNONYM emp FOR employees;

-- 使用同义词
SELECT * FROM emp;

修改表

-- 添加列
ALTER TABLE employees ADD (phone VARCHAR2(20));

-- 修改列
ALTER TABLE employees MODIFY (phone VARCHAR2(30));

-- 删除列
ALTER TABLE employees DROP COLUMN phone;

-- 重命名列
ALTER TABLE employees RENAME COLUMN phone TO phone_number;

-- 添加约束
ALTER TABLE employees ADD CONSTRAINT pk_emp PRIMARY KEY (employee_id);
ALTER TABLE employees ADD CONSTRAINT fk_emp_dept 
    FOREIGN KEY (department_id) REFERENCES departments(department_id);

-- 删除约束
ALTER TABLE employees DROP CONSTRAINT pk_emp;

修改索引

-- 重建索引
ALTER INDEX idx_emp_name REBUILD;

-- 重命名索引
ALTER INDEX idx_emp_name RENAME TO idx_employee_name;

-- 删除表
DROP TABLE employees;

-- 删除表（如果存在）
DROP TABLE employees CASCADE CONSTRAINTS;

-- 删除索引
DROP INDEX idx_emp_name;

-- 删除视图
DROP VIEW emp_view;

-- 删除序列
DROP SEQUENCE emp_seq;

-- 删除同义词
DROP SYNONYM emp;

-- 截断表（删除所有数据，保留结构）
TRUNCATE TABLE employees;

-- 截断表（级联）
TRUNCATE TABLE employees CASCADE;

---

8. 数据控制（DCL）

-- 授予系统权限
GRANT CREATE SESSION TO user1;
GRANT CREATE TABLE TO user1;
GRANT CREATE VIEW TO user1;

-- 授予对象权限
GRANT SELECT, INSERT, UPDATE ON employees TO user1;
GRANT ALL ON employees TO user2;

-- 授予角色
GRANT CONNECT, RESOURCE TO user1;

-- 撤销对象权限
REVOKE SELECT, INSERT ON employees FROM user1;

-- 撤销系统权限
REVOKE CREATE TABLE FROM user1;

---

9. PL/SQL编程

PL/SQL（Procedural Language/SQL）是Oracle的过程化SQL语言，扩展了SQL的功能。

DECLARE
    -- 声明部分
    v_name VARCHAR2(50);
    v_salary NUMBER;
BEGIN
    -- 执行部分
    v_name := 'John Doe';
    v_salary := 5000;
    DBMS_OUTPUT.PUT_LINE('Name: ' || v_name || ', Salary: ' || v_salary);
EXCEPTION
    -- 异常处理部分
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

DECLARE
    -- 变量声明
    v_count NUMBER := 0;
    v_name VARCHAR2(50);
    
    -- 常量声明
    c_pi CONSTANT NUMBER := 3.14159;
    
    -- 使用%TYPE
    v_emp_salary employees.salary%TYPE;
    
    -- 使用%ROWTYPE
    v_employee employees%ROWTYPE;
BEGIN
    v_count := 10;
    v_name := 'Test';
    DBMS_OUTPUT.PUT_LINE('Count: ' || v_count);
END;
/

IF语句

DECLARE
    v_salary NUMBER := 6000;
BEGIN
    IF v_salary > 10000 THEN
        DBMS_OUTPUT.PUT_LINE('High salary');
    ELSIF v_salary > 5000 THEN
        DBMS_OUTPUT.PUT_LINE('Medium salary');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Low salary');
    END IF;
END;
/

CASE语句

DECLARE
    v_grade CHAR(1) := 'A';
BEGIN
    CASE v_grade
        WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent');
        WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('Good');
        WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE('Fair');
        ELSE DBMS_OUTPUT.PUT_LINE('Poor');
    END CASE;
END;
/

循环

LOOP循环：

DECLARE
    v_count NUMBER := 1;
BEGIN
    LOOP
        DBMS_OUTPUT.PUT_LINE('Count: ' || v_count);
        v_count := v_count + 1;
        EXIT WHEN v_count > 5;
    END LOOP;
END;
/

WHILE循环：

DECLARE
    v_count NUMBER := 1;
BEGIN
    WHILE v_count <= 5 LOOP
        DBMS_OUTPUT.PUT_LINE('Count: ' || v_count);
        v_count := v_count + 1;
    END LOOP;
END;
/

FOR循环：

BEGIN
    FOR i IN 1..5 LOOP
        DBMS_OUTPUT.PUT_LINE('Count: ' || i);
    END LOOP;
    
    -- 反向循环
    FOR i IN REVERSE 1..5 LOOP
        DBMS_OUTPUT.PUT_LINE('Count: ' || i);
    END LOOP;
END;
/

隐式游标

BEGIN
    UPDATE employees SET salary = salary * 1.1 WHERE department_id = 60;
    DBMS_OUTPUT.PUT_LINE('Rows updated: ' || SQL%ROWCOUNT);
END;
/

显式游标

DECLARE
    CURSOR c_employees IS
        SELECT employee_id, first_name, last_name, salary
        FROM employees
        WHERE department_id = 60;
    
    v_emp c_employees%ROWTYPE;
BEGIN
    OPEN c_employees;
    LOOP
        FETCH c_employees INTO v_emp;
        EXIT WHEN c_employees%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_emp.first_name || ' ' || v_emp.last_name || ': ' || v_emp.salary);
    END LOOP;
    CLOSE c_employees;
END;
/

FOR循环游标

DECLARE
    CURSOR c_employees IS
        SELECT employee_id, first_name, last_name, salary
        FROM employees
        WHERE department_id = 60;
BEGIN
    FOR v_emp IN c_employees LOOP
        DBMS_OUTPUT.PUT_LINE(v_emp.first_name || ' ' || v_emp.last_name || ': ' || v_emp.salary);
    END LOOP;
END;
/

带参数的游标

DECLARE
    CURSOR c_employees(p_dept_id NUMBER) IS
        SELECT employee_id, first_name, last_name
        FROM employees
        WHERE department_id = p_dept_id;
BEGIN
    FOR v_emp IN c_employees(60) LOOP
        DBMS_OUTPUT.PUT_LINE(v_emp.first_name || ' ' || v_emp.last_name);
    END LOOP;
END;
/

DECLARE
    v_result NUMBER;
BEGIN
    v_result := 10 / 0;
EXCEPTION
    WHEN ZERO_DIVIDE THEN
        DBMS_OUTPUT.PUT_LINE('Division by zero error');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLCODE || ' - ' || SQLERRM);
END;
/

• 预定义异常：NO_DATA_FOUND、TOO_MANY_ROWS、ZERO_DIVIDE等
• 用户定义异常：自定义异常

DECLARE
    e_custom EXCEPTION;
    v_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_count FROM employees WHERE department_id = 999;
    IF v_count = 0 THEN
        RAISE e_custom;
    END IF;
EXCEPTION
    WHEN e_custom THEN
        DBMS_OUTPUT.PUT_LINE('Custom exception raised');
END;
/

---

10. 存储过程和函数

创建存储过程

-- 简单存储过程
CREATE OR REPLACE PROCEDURE update_salary (
    p_emp_id IN NUMBER,
    p_new_salary IN NUMBER
) AS
BEGIN
    UPDATE employees
    SET salary = p_new_salary
    WHERE employee_id = p_emp_id;
    
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Salary updated successfully');
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Employee not found');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
        ROLLBACK;
END;
/

-- 调用存储过程
EXEC update_salary(100, 6000);
-- 或
BEGIN
    update_salary(100, 6000);
END;
/

带OUT参数的存储过程

CREATE OR REPLACE PROCEDURE get_employee_info (
    p_emp_id IN NUMBER,
    p_name OUT VARCHAR2,
    p_salary OUT NUMBER
) AS
BEGIN
    SELECT first_name || ' ' || last_name, salary
    INTO p_name, p_salary
    FROM employees
    WHERE employee_id = p_emp_id;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        p_name := 'Not Found';
        p_salary := 0;
END;
/

-- 调用
DECLARE
    v_name VARCHAR2(100);
    v_salary NUMBER;
BEGIN
    get_employee_info(100, v_name, v_salary);
    DBMS_OUTPUT.PUT_LINE('Name: ' || v_name || ', Salary: ' || v_salary);
END;
/

创建函数

CREATE OR REPLACE FUNCTION calculate_bonus (
    p_salary IN NUMBER,
    p_percentage IN NUMBER DEFAULT 10
) RETURN NUMBER AS
    v_bonus NUMBER;
BEGIN
    v_bonus := p_salary * p_percentage / 100;
    RETURN v_bonus;
END;
/

-- 调用函数
SELECT employee_id, salary, calculate_bonus(salary, 15) AS bonus
FROM employees;

---

11. 触发器

触发器是当特定事件发生时自动执行的PL/SQL块。

BEFORE触发器

CREATE OR REPLACE TRIGGER trg_before_insert_emp
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    IF :NEW.salary < 0 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be negative');
    END IF;
    
    :NEW.hire_date := SYSDATE;
END;
/

AFTER触发器

CREATE OR REPLACE TRIGGER trg_after_update_emp
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employees_audit (
        employee_id, old_salary, new_salary, change_date
    ) VALUES (
        :OLD.employee_id, :OLD.salary, :NEW.salary, SYSDATE
    );
END;
/

INSTEAD OF触发器

CREATE OR REPLACE TRIGGER trg_instead_of_insert_view
INSTEAD OF INSERT ON emp_view
FOR EACH ROW
BEGIN
    INSERT INTO employees (
        employee_id, first_name, last_name, salary
    ) VALUES (
        :NEW.employee_id, :NEW.first_name, :NEW.last_name, :NEW.salary
    );
END;
/

CREATE OR REPLACE TRIGGER trg_ddl_log
AFTER CREATE OR DROP OR ALTER ON DATABASE
BEGIN
    INSERT INTO ddl_log (
        event_type, object_name, event_date
    ) VALUES (
        ORA_SYSEVENT, ORA_DICT_OBJ_NAME, SYSDATE
    );
END;
/

---

12. 索引和性能优化

• B-Tree索引：最常用的索引类型
• 位图索引：适用于低基数列
• 函数索引：基于函数的索引
• 复合索引：多列索引

-- 分析表
ANALYZE TABLE employees COMPUTE STATISTICS;

-- 重建索引
ALTER INDEX idx_emp_name REBUILD;

-- 查看执行计划
EXPLAIN PLAN FOR
SELECT * FROM employees WHERE last_name = 'Smith';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

1. 使用索引：在WHERE子句的列上创建索引
2. 避免全表扫描：使用索引列查询
3. 使用EXISTS代替IN：对于大表，EXISTS性能更好
4. **避免SELECT ***：只选择需要的列
5. 使用绑定变量：避免硬解析

---

13. 用户和权限管理

-- 创建用户
CREATE USER user1 IDENTIFIED BY password1;

-- 授予基本权限
GRANT CREATE SESSION TO user1;
GRANT CREATE TABLE TO user1;
GRANT UNLIMITED TABLESPACE TO user1;

-- 创建角色
CREATE ROLE manager_role;

-- 授予权限给角色
GRANT SELECT, INSERT, UPDATE ON employees TO manager_role;

-- 授予角色给用户
GRANT manager_role TO user1;

-- 修改用户密码
ALTER USER user1 IDENTIFIED BY newpassword;

-- 锁定用户
ALTER USER user1 ACCOUNT LOCK;

-- 解锁用户
ALTER USER user1 ACCOUNT UNLOCK;

-- 删除用户
DROP USER user1 CASCADE;

---

14. 备份与恢复

# 使用expdp导出
expdp system/password@orcl schemas=hr directory=backup_dir dumpfile=hr_backup.dmp

# 使用exp导出（旧方式）
exp system/password@orcl file=backup.dmp owner=hr

# 使用impdp导入
impdp system/password@orcl schemas=hr directory=backup_dir dumpfile=hr_backup.dmp

# 使用imp导入（旧方式）
imp system/password@orcl file=backup.dmp fromuser=hr touser=hr

-- 备份表空间
ALTER TABLESPACE users BEGIN BACKUP;
-- 复制数据文件
ALTER TABLESPACE users END BACKUP;

---

15. 实际应用案例

-- 创建员工表
CREATE TABLE employees (
    employee_id NUMBER PRIMARY KEY,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50),
    email VARCHAR2(100),
    phone VARCHAR2(20),
    hire_date DATE,
    job_id VARCHAR2(20),
    salary NUMBER(10,2),
    manager_id NUMBER,
    department_id NUMBER
);

-- 创建部门表
CREATE TABLE departments (
    department_id NUMBER PRIMARY KEY,
    department_name VARCHAR2(100),
    location VARCHAR2(100)
);

-- 插入数据
INSERT INTO departments VALUES (10, 'IT', 'Beijing');
INSERT INTO departments VALUES (20, 'HR', 'Shanghai');

INSERT INTO employees VALUES (
    1001, 'John', 'Doe', 'john@example.com', '1234567890',
    SYSDATE, 'IT_PROG', 5000, NULL, 10
);

-- 创建销售报表视图
CREATE VIEW sales_report AS
SELECT 
    d.department_name,
    COUNT(e.employee_id) AS employee_count,
    SUM(e.salary) AS total_salary,
    AVG(e.salary) AS avg_salary
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name;

-- 查询报表
SELECT * FROM sales_report;

CREATE OR REPLACE PROCEDURE migrate_employees AS
    CURSOR c_old_employees IS
        SELECT * FROM employees_old;
    v_count NUMBER := 0;
BEGIN
    FOR v_emp IN c_old_employees LOOP
        INSERT INTO employees (
            employee_id, first_name, last_name, email, hire_date, salary
        ) VALUES (
            v_emp.employee_id, v_emp.first_name, v_emp.last_name,
            v_emp.email, v_emp.hire_date, v_emp.salary
        );
        v_count := v_count + 1;
        
        IF v_count MOD 1000 = 0 THEN
            COMMIT;
        END IF;
    END LOOP;
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Migrated ' || v_count || ' employees');
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/

---

16. 总结与进阶

通过本教程，你已经掌握了：

• ✅ Oracle数据库的基本概念和架构
• ✅ SQL语言的基础和高级用法
• ✅ PL/SQL编程基础
• ✅ 存储过程、函数和触发器
• ✅ 数据库管理和优化
• ✅ 用户权限管理
• ✅ 备份与恢复

1. 高级SQL优化

   • 执行计划分析
   • 索引优化策略
   • 分区表设计

2. 高级PL/SQL

   • 包（Package）开发
   • 动态SQL
   • 批量处理

3. 数据库管理

   • 性能调优
   • 高可用性配置
   • 数据仓库设计

4. Oracle高级特性

   • 分区表
   • 物化视图
   • 高级复制

5. 设计规范：遵循命名规范和设计原则

6. 性能优化：合理使用索引，优化SQL语句

7. 安全控制：严格控制用户权限

8. 备份策略：定期备份，测试恢复流程

9. 文档管理：记录数据库变更和配置

• 官方文档：https://docs.oracle.com/en/database/
• Oracle Learning Library：免费学习资源
• Oracle社区：Oracle官方社区论坛
• 书籍推荐：
  • 《Oracle Database SQL语言参考手册》
  • 《Oracle PL/SQL编程》

---

结语

Oracle数据库是企业级应用的重要基础。通过本教程的学习，相信你已经掌握了Oracle数据库的基础知识和核心技能。

记住：

• 多实践：理论结合实践，多写SQL和PL/SQL代码
• 理解原理：理解数据库的工作原理和优化机制
• 关注性能：始终关注查询性能和系统性能
• 持续学习：Oracle功能强大，需要持续学习新特性

祝你学习愉快，数据库管理顺利！ 🚀

---

本教程由Java突击队学习社区编写，如有问题欢迎反馈。