Oracle 数据库基本操作指南

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.