Oracle 数据库基本操作指南

66 views Posted on 2025-09-02 by 林坤源

Oracle 数据库是全球领先的关系型数据库管理系统（RDBMS），以其强大的性能、安全性和可扩展性而闻名。掌握其基本操作是每一位数据库开发和管理人员的必备技能。本文将引导您完成一系列核心操作，包括权限控制、数据操作和对象创建。

一、创建角色并授予权限

角色（Role）是权限的集合，用于简化用户权限管理。我们可以将一系列权限授予一个角色，然后将这个角色授予多个用户，而不是直接将权限授予每个用户。

示例：创建一个用于数据查询的角色

-- 1. 创建角色，名为 ‘data_query_role’
CREATE ROLE data_query_role NOT IDENTIFIED;

-- 2. 给角色授予权限
-- 授予连接到数据库的权限
GRANT CREATE SESSION TO data_query_role;
-- 授予对 scott 用户的 emp 表的查询权限
GRANT SELECT ON scott.emp TO data_query_role;
-- 授予对 scott 用户的 dept 表的查询权限
GRANT SELECT ON scott.dept TO data_query_role;

关键点：

  • NOT IDENTIFIED 表示启用该角色不需要密码。
  • 权限分为系统权限（如 CREATE SESSION）和对象权限（如 SELECT ON scott.emp）。

二、创建用户并授予角色

用户（User）是访问数据库的账户。创建用户后，通常需要授予其相应的角色以获取权限。

示例：创建一个新用户并授予上述角色

-- 1. 创建用户，名为 ‘dev_user’，密码为 ‘password123’
CREATE USER dev_user IDENTIFIED BY password123;

-- 2. 授予用户必要的系统权限（例如，允许更改会话，在某些客户端工具中是必须的）
GRANT ALTER SESSION TO dev_user;

-- 3. 将之前创建的角色授予新用户
GRANT data_query_role TO dev_user;

-- 4. 也可以直接授予用户额外的权限（可选）
GRANT CREATE VIEW TO dev_user;

现在，用户 dev_user 可以使用密码 password123 登录数据库，并拥有 data_query_role 角色中的所有权限。

三、回收角色或用户权限

当需要撤销用户的某些权限时，可以使用 REVOKE 命令。

示例：回收权限和角色

-- 1. 从用户 dev_user 回收 data_query_role 角色
REVOKE data_query_role FROM dev_user;

-- 2. 从角色 data_query_role 回收对 scott.emp 表的查询权限
REVOKE SELECT ON scott.emp FROM data_query_role;

-- 3. 直接回收用户 dev_user 的 CREATE VIEW 系统权限
REVOKE CREATE VIEW FROM dev_user;

关键点：

  • 回收权限的语法与授予权限类似，将 GRANT 替换为 REVOKE，将 TO 替换为 FROM。

四、基本增删改查（DML）操作

增删改查是操作表中数据的基础，对应 SQL 的 INSERT, DELETE, UPDATE, SELECT 语句。

假设我们有一个简单的 employees 表（id NUMBER, name VARCHAR2(100), salary NUMBER）。

-- 1. 增 (INSERT): 插入新数据
INSERT INTO employees (id, name, salary) VALUES (1, '张三', 5000);
INSERT INTO employees (id, name, salary) VALUES (2, '李四', 6000);
COMMIT; -- 提交事务，使更改永久化

-- 2. 查 (SELECT): 查询数据
SELECT * FROM employees; -- 查询所有数据和所有列
SELECT name, salary FROM employees WHERE salary > 5500; -- 条件查询

-- 3. 改 (UPDATE): 更新现有数据
UPDATE employees SET salary = 6500 WHERE name = '李四';
COMMIT;

-- 4. 删 (DELETE): 删除数据
DELETE FROM employees WHERE id = 1;
COMMIT;

五、创建视图（View）

视图是一个虚拟表，其内容由查询定义。它可以简化复杂查询、提供数据安全性（隐藏敏感数据）。

示例：创建一个视图，只显示员工姓名和薪水，隐藏 ID

CREATE OR REPLACE VIEW emp_view AS
SELECT name, salary
FROM employees;

现在，用户可以像查询普通表一样查询这个视图：

SELECT * FROM emp_view;

六、创建存储过程（Stored Procedure）

存储过程是存储在数据库中的一组为了完成特定功能的 SQL 语句集。它经过编译和优化，执行效率高，并可以减少网络传输。

示例：创建一个给指定员工加薪的存储过程

CREATE OR REPLACE PROCEDURE raise_salary (
    p_emp_name IN VARCHAR2,
    p_amount IN NUMBER
)
IS
BEGIN
    UPDATE employees
    SET salary = salary + p_amount
    WHERE name = p_emp_name;
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('已为 ' || p_emp_name || ' 加薪 ' || p_amount);
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('发生错误: ' || SQLERRM);
END raise_salary;

调用存储过程：

-- 方式一：在 PL/SQL 块中调用
BEGIN
   raise_salary('李四', 1000);
END;

-- 方式二：使用 EXEC 命令（在 SQL*Plus 或 SQLcl 中）
EXEC raise_salary('李四', 1000);

七、模式、实例等核心概念区别

理解这些概念对于掌握 Oracle 体系结构至关重要。

  1. 数据库 (Database)
    • 物理概念：指的是存储在磁盘上的物理文件集合，包括数据文件（.dbf）、控制文件（.ctl）和在线重做日志文件（.log）。数据库就是一堆文件。
  2. 实例 (Instance)
    • 内存和进程概念：实例是操作数据库的一种手段。它由一片共享内存区域（称为系统全局区 SGA）和一系列后台进程（如 PMON, SMON, DBWn, LGWR 等）组成。
    • 关系：一个实例在其生命周期内最多只能装载和打开一个数据库。而在 Oracle RAC（实时应用集群）环境中，多个实例可以同时装载和打开一个共享的数据库，这是实现高可用性和负载均衡的关键。
  3. 模式 (Schema)
    • 逻辑概念：模式是数据库对象的集合（如表、视图、索引、过程、包等），这些对象由一个特定的数据库用户拥有。模式名与用户名完全相同。
    • 当你创建一个用户时，系统会自动为其创建一个同名的模式。用户 scott 拥有的所有对象就构成了 scott 模式。

简单比喻：

  • 数据库就像是一个物理仓库，里面放着货物（数据）。
  • 实例就像是仓库的管理和运营团队（包括办公室、管理人员、装卸工人）。团队负责仓库的日常运作。
  • 模式就像是仓库里划分给不同租户（用户）的专属区域。租户 scott 的区域就叫 scott 区，里面的货架、箱子（表、视图）都是他的，别人不能随便动，除非他授予了权限。
概念本质包含内容关系
数据库物理的数据文件、控制文件、日志文件一个数据库可由一个或多个实例装载
实例内存/进程的内存结构（SGA）、后台进程一个实例一次只能装载一个数据库
模式逻辑的表、视图、索引等数据库对象与用户一一对应，是用户的所属对象集合

通过以上步骤和解释，您应该对 Oracle 数据库的基本操作和核心概念有了一个全面的了解。在实际工作中，熟练运用这些操作是进行有效数据库管理和开发的基础。

This entry was posted in 应用. Bookmark the permalink.