Oracle 第5章：表与数据操作

时间：2024-10-29 07:28:29

在Oracle数据库中，创建表、插入数据、更新数据以及删除数据是基本的数据管理任务。下面我将逐一介绍这些操作，并提供相应的SQL语句示例。

创建表

创建一个表需要使用CREATE TABLE语句来定义表的结构，包括列名、数据类型等。以下是一个创建名为employees的表的例子，该表包含员工的基本信息：

CREATE TABLE employees (
    employee_id NUMBER(6) PRIMARY KEY,
    first_name VARCHAR2(20),
    last_name VARCHAR2(25),
    email VARCHAR2(25),
    phone_number VARCHAR2(20),
    hire_date DATE,
    job_id VARCHAR2(10),
    salary NUMBER(8,2),
    commission_pct NUMBER(2,2),
    manager_id NUMBER(6),
    department_id NUMBER(4)
);

在这个例子中，NUMBER(6)表示整数类型，最多可以有6位数字；VARCHAR2(20)表示可变长度的字符串，最大长度为20个字符；DATE类型用来存储日期和时间；PRIMARY KEY表示主键，用于唯一标识表中的每一行记录。

插入数据

一旦表创建好了，就可以使用INSERT语句来向表中添加新的记录。例如，向employees表中插入一条新员工的记录：

INSERT INTO employees (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
VALUES (100, 'John', 'Doe', 'jdoe@example.com', '555.0100', TO_DATE('2024-10-26', 'YYYY-MM-DD'), 'AD_PRES', 24000, null, 101, 90);

这里TO_DATE函数用来转换日期格式。

更新数据

如果需要修改已有的记录，可以使用UPDATE语句。例如，要给某个员工涨薪：

UPDATE employees
SET salary = salary * 1.1
WHERE employee_id = 100;

这个命令将把员工ID为100的员工薪水提高10%。

删除数据

要从表中删除记录，可以使用DELETE语句。例如，删除一个员工的信息：

DELETE FROM employees
WHERE employee_id = 100;

以上就是创建表、插入数据、更新数据以及删除数据的基本操作。实际使用时，请根据具体情况调整SQL语句，并确保在执行任何更改之前备份重要数据。

数据查询

除了创建、插入、更新和删除数据外，查询数据也是常见的需求。SELECT语句可以用来从表中检索数据。例如，列出所有员工的名字和薪水：

SELECT first_name, last_name, salary
FROM employees;

复杂查询

你可以使用JOIN来连接多个表，从而获取更复杂的数据组合。比如，连接employees表和departments表来显示每个员工的名字及其所在的部门名称：

SELECT e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

索引优化

为了提高查询性能，可以在表上创建索引。索引类似于书的目录，帮助数据库快速定位数据。例如，为employees表的last_name字段创建一个索引：

CREATE INDEX idx_lastname ON employees(last_name);

这样，在查询涉及last_name字段时，性能可能会得到提升。

触发器

触发器是一种特殊类型的存储过程，它定义了当特定的事件（如INSERT、UPDATE或DELETE）发生时应自动执行的动作。例如，可以创建一个触发器，在每次向employees表中添加新记录时自动记录到另一个表中：

CREATE OR REPLACE TRIGGER log_new_employee
AFTER INSERT ON employees
FOR EACH ROW
DECLARE
BEGIN
    INSERT INTO audit_log (action, table_name, row_id, change_date)
    VALUES ('INSERT', 'employees', :NEW.employee_id, SYSDATE);
END;
/

这个触发器会在每次插入新员工记录后，在audit_log表中记录一条审计日志。

存储过程

存储过程是一组预先编写的SQL语句和控制流语句，封装在一个命名单元中，可以通过调用其名字来执行。例如，可以编写一个存储过程来计算员工的年终奖金：

CREATE OR REPLACE PROCEDURE calculate_bonus(employee_id IN employees.employee_id%TYPE)
IS
    emp_salary employees.salary%TYPE;
BEGIN
    SELECT salary INTO emp_salary FROM employees WHERE employee_id = employee_id;
    DBMS_OUTPUT.PUT_LINE('Employee ' || employee_id || ' bonus: ' || (emp_salary * 0.1));
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('No such employee');
END;
/

然后可以通过如下方式调用此存储过程：

EXECUTE calculate_bonus(100);

以上就是一些关于Oracle数据库中表的操作及其扩展功能的介绍。实际应用中，还需要考虑安全性、事务处理、并发控制等问题。

好的，我们可以继续探讨Oracle数据库中的高级特性，特别是那些可以帮助开发者更好地管理和维护数据的功能。

事务处理

事务是作为一个工作单元的一系列操作。在Oracle中，事务处理通常涉及到开始事务、提交或回滚事务。例如，如果你想要确保一系列操作要么全部成功要么都不执行，你可以这样做：

-- 开始一个新事务
START TRANSACTION;

-- 执行一系列操作...
INSERT INTO employees VALUES (101, 'Jane', 'Doe', 'jdoe2@example.com', '555.0200', SYSDATE, 'AD_VP', 17000, NULL, 100, 90);

-- 如果一切顺利，提交事务
COMMIT;

-- 如果出错，回滚事务
ROLLBACK;

并发控制

在多用户环境中，多个用户可能同时访问同一份数据。Oracle提供了几种并发控制机制，如乐观锁定和悲观锁定，以防止数据竞争和不一致。乐观锁定通常通过版本号或时间戳来实现：

-- 更新操作
UPDATE employees SET salary = salary + 1000 WHERE employee_id = 100 AND version = 1;

-- 假设有人已经修改了这条记录，上面的更新不会成功

错误处理

在PL/SQL块中，可以使用异常处理来捕获并处理错误情况。例如，如果尝试插入重复的主键值，可以捕获ORA-00001异常：

DECLARE
    e_employee_id employees.employee_id%TYPE := 100;
    e_first_name employees.first_name%TYPE := 'Test';
BEGIN
    INSERT INTO employees (employee_id, first_name)
    VALUES (e_employee_id, e_first_name);
EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
        DBMS_OUTPUT.PUT_LINE('Duplicate value for employee_id: ' || e_employee_id);
END;
/

视图

视图是基于一个或多个表或其他视图之上的虚拟表。它可以简化复杂的查询，并且隐藏基础表的实际结构。例如，创建一个只展示员工姓名和薪水的视图：

CREATE VIEW employee_summary AS
SELECT first_name, last_name, salary
FROM employees;

然后可以直接查询这个视图：

SELECT * FROM employee_summary;

分区

对于非常大的表，分区技术可以提高查询性能。分区将大表分成更小、更易于管理的部分。例如，按雇员的入职年份对employees表进行范围分区：

CREATE TABLE employees (
    ...
)
PARTITION BY RANGE (hire_date) (
    PARTITION emp_2020 VALUES LESS THAN (TO_DATE('2021-01-01', 'YYYY-MM-DD')),
    PARTITION emp_2021 VALUES LESS THAN (TO_DATE('2022-01-01', 'YYYY-MM-DD')),
    PARTITION emp_2022 VALUES LESS THAN (TO_DATE('2023-01-01', 'YYYY-MM-DD')),
    PARTITION emp_2023 VALUES LESS THAN (MAXVALUE)
);

分区可以帮助减少查询时扫描的数据量，从而提高效率。

以上这些技术可以帮助你在Oracle数据库中更有效地管理和操作数据。当然，还有很多其他高级特性和最佳实践，可以根据具体的应用场景选择性地使用。

相关文章