印刻数据库

外观

Oracle 数据库对象开发 ​

数据库对象开发概述 ​

数据库对象是数据库的基本组成部分，包括表、索引、视图、序列、同义词、触发器、存储过程和包等。Oracle 数据库对象开发是指创建、管理和维护这些对象的过程，是 Oracle 数据库开发的重要组成部分。

数据库对象开发的重要性 ​

  • 组织和管理数据：将数据组织成结构化的形式，便于管理和访问
  • 提高数据访问效率：通过索引等对象提高数据查询和修改的效率
  • 增强数据安全性：通过视图、同义词等对象控制数据访问权限
  • 实现复杂业务逻辑：通过触发器、存储过程和包等对象实现复杂的业务规则
  • 提高开发效率：复用数据库对象，减少重复代码

数据库对象的主要类型 ​

对象类型描述主要用途
表（Table）存储数据的基本单位存储结构化数据
索引（Index）提高数据访问效率的数据结构加速数据查询和修改
视图（View）基于表或其他视图的逻辑表简化数据访问，控制数据访问权限
序列（Sequence）生成唯一数字的对象生成主键值，确保数据唯一性
同义词（Synonym）数据库对象的别名简化对象访问，提供对象访问的透明性
触发器（Trigger）在特定事件发生时自动执行的存储过程实现数据完整性约束，自动维护数据
存储过程（Stored Procedure）存储在数据库中的可执行代码块实现复杂业务逻辑，提高代码复用性
函数（Function）存储在数据库中的可执行代码块，返回一个值实现数据转换和计算，提高代码复用性
包（Package）相关存储过程、函数、变量和常量的集合模块化管理代码，提高代码复用性和安全性
物化视图（Materialized View）存储查询结果的数据库对象提高查询性能，支持数据分析和报表生成
数据库链接（Database Link）连接到远程数据库的对象访问远程数据库中的数据

表的开发 ​

表是Oracle数据库中存储数据的基本单位，是数据库对象开发的基础。

表的创建 ​

sql
-- 创建基本表
CREATE TABLE employees (
  employee_id NUMBER(6) PRIMARY KEY,
  first_name VARCHAR2(20),
  last_name VARCHAR2(25) NOT NULL,
  email VARCHAR2(25) UNIQUE NOT NULL,
  phone_number VARCHAR2(20),
  hire_date DATE NOT NULL,
  job_id VARCHAR2(10) NOT NULL,
  salary NUMBER(8,2),
  commission_pct NUMBER(2,2),
  manager_id NUMBER(6),
  department_id NUMBER(4),
  FOREIGN KEY (job_id) REFERENCES jobs(job_id),
  FOREIGN KEY (department_id) REFERENCES departments(department_id),
  FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
);

-- 创建带约束的表
CREATE TABLE departments (
  department_id NUMBER(4) PRIMARY KEY,
  department_name VARCHAR2(30) NOT NULL,
  manager_id NUMBER(6),
  location_id NUMBER(4),
  CONSTRAINT dept_name_uq UNIQUE (department_name),
  CONSTRAINT dept_loc_fk FOREIGN KEY (location_id) REFERENCES locations(location_id)
);

表的修改 ​

sql
-- 添加列
ALTER TABLE employees ADD (age NUMBER(3));

-- 修改列
ALTER TABLE employees MODIFY (age NUMBER(2));

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

-- 添加约束
ALTER TABLE employees ADD CONSTRAINT emp_salary_min CHECK (salary > 0);

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

表的删除 ​

sql
-- 删除表
DROP TABLE employees CASCADE CONSTRAINTS;

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

索引的开发 ​

索引是提高数据访问效率的数据结构，通过创建合适的索引可以显著提高查询性能。

索引的创建 ​

sql
-- 创建 B-tree 索引
CREATE INDEX emp_last_name_idx ON employees(last_name);

-- 创建唯一索引
CREATE UNIQUE INDEX emp_email_idx ON employees(email);

-- 创建复合索引
CREATE INDEX emp_dept_id_salary_idx ON employees(department_id, salary);

-- 创建位图索引（适用于低基数列）
CREATE BITMAP INDEX emp_job_id_bidx ON employees(job_id);

-- 创建函数索引
CREATE INDEX emp_upper_last_name_idx ON employees(UPPER(last_name));

索引的修改 ​

sql
-- 重命名索引
ALTER INDEX emp_last_name_idx RENAME TO employees_last_name_idx;

-- 重建索引
ALTER INDEX employees_last_name_idx REBUILD;

-- 合并索引碎片
ALTER INDEX employees_last_name_idx COALESCE;

索引的删除 ​

sql
-- 删除索引
DROP INDEX employees_last_name_idx;

视图的开发 ​

视图是基于表或其他视图的逻辑表，用于简化数据访问和控制数据访问权限。

视图的创建 ​

sql
-- 创建简单视图
CREATE VIEW emp_dept_view AS
SELECT e.employee_id, e.first_name, e.last_name, e.salary, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

-- 创建带检查选项的视图
CREATE VIEW emp_salary_view AS
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > 5000
WITH CHECK OPTION;

-- 创建带只读选项的视图
CREATE VIEW emp_readonly_view AS
SELECT employee_id, first_name, last_name
FROM employees
WITH READ ONLY;

视图的修改 ​

sql
-- 修改视图
CREATE OR REPLACE VIEW emp_dept_view AS
SELECT e.employee_id, e.first_name, e.last_name, e.salary, d.department_name, d.location_id
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

-- 重命名视图
ALTER VIEW emp_dept_view RENAME TO employee_department_view;

视图的删除 ​

sql
-- 删除视图
DROP VIEW employee_department_view;

序列的开发 ​

序列是生成唯一数字的数据库对象，常用于生成主键值。

序列的创建 ​

sql
-- 创建基本序列
CREATE SEQUENCE emp_seq
START WITH 100
INCREMENT BY 1
NOCACHE
NOCYCLE;

-- 创建带缓存的序列
CREATE SEQUENCE dept_seq
START WITH 280
INCREMENT BY 10
CACHE 20
CYCLE;

序列的使用 ​

sql
-- 使用序列生成主键值
INSERT INTO departments (department_id, department_name)
VALUES (dept_seq.NEXTVAL, 'New Department');

-- 获取序列的当前值
SELECT dept_seq.CURRVAL FROM DUAL;

序列的修改 ​

sql
-- 修改序列
ALTER SEQUENCE emp_seq
INCREMENT BY 2
CACHE 10;

序列的删除 ​

sql
-- 删除序列
DROP SEQUENCE emp_seq;

同义词的开发 ​

同义词是数据库对象的别名，用于简化对象访问和提供对象访问的透明性。

同义词的创建 ​

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

-- 创建公有同义词
CREATE PUBLIC SYNONYM dept FOR departments;

同义词的使用 ​

sql
-- 使用私有同义词访问表
SELECT * FROM emp WHERE department_id = 90;

-- 使用公有同义词访问表
SELECT * FROM dept WHERE department_id = 90;

同义词的删除 ​

sql
-- 删除私有同义词
DROP SYNONYM emp;

-- 删除公有同义词
DROP PUBLIC SYNONYM dept;

触发器的开发 ​

触发器是在特定事件发生时自动执行的存储过程，用于实现数据完整性约束和自动维护数据。

触发器的创建 ​

sql
-- 创建行级触发器
CREATE OR REPLACE TRIGGER emp_salary_trg
BEFORE INSERT OR UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
  IF :NEW.salary < 0 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Salary must be greater than 0');
  END IF;
END emp_salary_trg;

-- 创建语句级触发器
CREATE OR REPLACE TRIGGER emp_count_trg
AFTER INSERT OR DELETE ON employees
BEGIN
  DBMS_OUTPUT.PUT_LINE('Employees count changed');
END emp_count_trg;

-- 创建替代触发器（用于视图）
CREATE OR REPLACE TRIGGER emp_view_trg
INSTEAD OF INSERT ON emp_dept_view
FOR EACH ROW
BEGIN
  INSERT INTO employees (employee_id, first_name, last_name, department_id)
  VALUES (:NEW.employee_id, :NEW.first_name, :NEW.last_name, :NEW.department_id);
END emp_view_trg;

触发器的启用和禁用 ​

sql
-- 禁用触发器
ALTER TRIGGER emp_salary_trg DISABLE;

-- 启用触发器
ALTER TRIGGER emp_salary_trg ENABLE;

-- 禁用表上的所有触发器
ALTER TABLE employees DISABLE ALL TRIGGERS;

-- 启用表上的所有触发器
ALTER TABLE employees ENABLE ALL TRIGGERS;

触发器的删除 ​

sql
-- 删除触发器
DROP TRIGGER emp_salary_trg;

存储过程和函数的开发 ​

存储过程和函数是存储在数据库中的可执行代码块，用于实现复杂业务逻辑和提高代码复用性。

存储过程的创建和使用 ​

sql
-- 创建存储过程
CREATE OR REPLACE PROCEDURE get_employee_details (
  p_employee_id IN NUMBER,
  p_employee_name OUT VARCHAR2,
  p_employee_salary OUT NUMBER
) IS
BEGIN
  SELECT first_name || ' ' || last_name, salary
  INTO p_employee_name, p_employee_salary
  FROM employees
  WHERE employee_id = p_employee_id;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    p_employee_name := 'Not Found';
    p_employee_salary := 0;
  WHEN OTHERS THEN
    RAISE;
END get_employee_details;

-- 调用存储过程
DECLARE
  v_employee_name VARCHAR2(100);
  v_employee_salary NUMBER;
BEGIN
  get_employee_details(100, v_employee_name, v_employee_salary);
  DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name || ', Salary: ' || v_employee_salary);
END;

函数的创建和使用 ​

sql
-- 创建函数
CREATE OR REPLACE FUNCTION calculate_bonus (
  p_salary IN NUMBER,
  p_percentage IN NUMBER DEFAULT 10
) RETURN NUMBER IS
BEGIN
  RETURN p_salary * p_percentage / 100;
EXCEPTION
  WHEN OTHERS THEN
    RAISE;
END calculate_bonus;

-- 调用函数
SELECT employee_id, first_name, last_name, salary, calculate_bonus(salary, 15) AS bonus
FROM employees
WHERE department_id = 90;

包的开发 ​

包是相关存储过程、函数、变量和常量的集合，用于模块化管理代码，提高代码复用性和安全性。

包的创建 ​

sql
-- 创建包规范
CREATE OR REPLACE PACKAGE emp_pkg IS
  -- 常量
  c_max_salary CONSTANT NUMBER := 100000;
  
  -- 变量
  v_total_employees NUMBER;
  
  -- 类型定义
  TYPE emp_rec_type IS RECORD (
    employee_id NUMBER,
    employee_name VARCHAR2(100),
    salary NUMBER
  );
  
  TYPE emp_tab_type IS TABLE OF emp_rec_type INDEX BY PLS_INTEGER;
  
  -- 存储过程声明
  PROCEDURE get_employee (p_employee_id IN NUMBER);
  PROCEDURE update_salary (p_employee_id IN NUMBER, p_percentage IN NUMBER);
  
  -- 函数声明
  FUNCTION get_employee_count (p_department_id IN NUMBER) RETURN NUMBER;
  FUNCTION get_highest_salary (p_department_id IN NUMBER) RETURN NUMBER;
END emp_pkg;
/

-- 创建包体
CREATE OR REPLACE PACKAGE BODY emp_pkg IS
  -- 包初始化
  BEGIN
    SELECT COUNT(*) INTO v_total_employees FROM employees;
  END;
  
  -- 存储过程实现
  PROCEDURE get_employee (p_employee_id IN NUMBER) IS
    v_employee emp_rec_type;
  BEGIN
    SELECT employee_id, first_name || ' ' || last_name, salary
    INTO v_employee
    FROM employees
    WHERE employee_id = p_employee_id;
    
    DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_employee.employee_id || ', Name: ' || v_employee.employee_name || ', Salary: ' || v_employee.salary);
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('Employee not found');
    WHEN OTHERS THEN
      RAISE;
  END get_employee;
  
  PROCEDURE update_salary (p_employee_id IN NUMBER, p_percentage IN NUMBER) IS
  BEGIN
    UPDATE employees
    SET salary = salary * (1 + p_percentage / 100)
    WHERE employee_id = p_employee_id;
    
    DBMS_OUTPUT.PUT_LINE('Updated salary for employee ' || p_employee_id);
  EXCEPTION
    WHEN OTHERS THEN
      RAISE;
  END update_salary;
  
  -- 函数实现
  FUNCTION get_employee_count (p_department_id IN NUMBER) RETURN NUMBER IS
    v_count NUMBER;
  BEGIN
    SELECT COUNT(*)
    INTO v_count
    FROM employees
    WHERE department_id = p_department_id;
    
    RETURN v_count;
  EXCEPTION
    WHEN OTHERS THEN
      RAISE;
  END get_employee_count;
  
  FUNCTION get_highest_salary (p_department_id IN NUMBER) RETURN NUMBER IS
    v_highest_salary NUMBER;
  BEGIN
    SELECT MAX(salary)
    INTO v_highest_salary
    FROM employees
    WHERE department_id = p_department_id;
    
    RETURN v_highest_salary;
  EXCEPTION
    WHEN OTHERS THEN
      RAISE;
  END get_highest_salary;
END emp_pkg;

包的使用 ​

sql
-- 调用包中的存储过程
BEGIN
  emp_pkg.get_employee(100);
  emp_pkg.update_salary(100, 10);
END;

-- 调用包中的函数
SELECT emp_pkg.get_employee_count(90) AS dept_90_count,
       emp_pkg.get_highest_salary(90) AS dept_90_highest_salary
FROM DUAL;

-- 访问包中的变量
BEGIN
  DBMS_OUTPUT.PUT_LINE('Total Employees: ' || emp_pkg.v_total_employees);
END;

包的删除 ​

sql
-- 删除包体
DROP PACKAGE BODY emp_pkg;

-- 删除包规范
DROP PACKAGE emp_pkg;

数据库对象开发最佳实践 ​

1. 设计阶段最佳实践 ​

  • 明确业务需求：理解业务需求，设计合适的数据库对象
  • 规范化设计：遵循数据库设计规范，确保数据完整性和一致性
  • 考虑性能因素：在设计阶段考虑索引、分区等性能因素
  • 模块化设计：将复杂业务逻辑分解为多个模块，提高代码复用性
  • 考虑安全性：设计合适的访问控制机制，确保数据安全

2. 开发阶段最佳实践 ​

  • 使用清晰的命名规范：为数据库对象使用清晰、一致的命名规范
  • 编写详细的注释：为复杂的数据库对象编写详细的注释，便于理解和维护
  • 测试数据库对象：编写测试用例，验证数据库对象的正确性
  • 优化SQL语句：使用合适的SQL语句，提高执行效率
  • 使用绑定变量：减少硬解析，提高SQL执行效率

3. 部署阶段最佳实践 ​

  • 编译数据库对象：确保数据库对象编译成功，没有语法错误
  • 授权访问权限：授予合适的访问权限，确保数据安全
  • 监控数据库对象性能：监控数据库对象的性能，及时发现和解决问题
  • 备份数据库对象：定期备份数据库对象，防止对象丢失
  • 文档化数据库对象：编写数据库对象的文档，便于维护和使用

4. 不同Oracle版本的数据库对象特性 ​

版本数据库对象特性
11g引入复合触发器，增强动态SQL支持
12c支持多租户环境下的数据库对象，增强PL/SQL功能
19c引入自动索引，增强JSON支持
21c支持区块链表，增强安全特性

常见问题（FAQ） ​

Q1: 如何选择合适的索引类型？ ​

A: 选择合适的索引类型需要考虑以下因素：

  • 列的基数：高基数列适合使用B-tree索引，低基数列适合使用位图索引
  • 查询类型：频繁用于WHERE子句的列适合创建索引
  • 数据修改频率：频繁修改的列不适合创建索引，因为会增加维护开销
  • 查询条件：如果查询条件中使用了函数，适合创建函数索引

Q2: 如何优化存储过程和函数的性能？ ​

A: 优化存储过程和函数的性能可以从以下几个方面入手：

  • 使用绑定变量：减少硬解析，提高SQL执行效率
  • 优化SQL语句：使用合适的SQL语句，提高执行效率
  • 减少网络开销：减少存储过程和函数与客户端之间的数据传输
  • 优化游标使用：使用BULK COLLECT减少上下文切换
  • 避免不必要的计算和函数调用：减少存储过程和函数中的计算和函数调用

Q3: 如何管理数据库对象的依赖关系？ ​

A: 管理数据库对象的依赖关系可以使用以下方法：

  • 使用Oracle提供的依赖视图：如dba_dependencies、user_dependencies等
  • 编译无效对象：使用ALTER ... COMPILE语句编译无效的数据库对象
  • 使用工具：如Oracle SQL Developer、Toad等工具管理数据库对象的依赖关系
  • 文档化依赖关系：编写数据库对象的依赖关系文档，便于维护和管理

Q4: 如何确保数据库对象的安全性？ ​

A: 确保数据库对象的安全性可以从以下几个方面入手：

  • 授予最小权限：只为用户授予必要的访问权限
  • 使用视图和同义词：限制用户对底层表的直接访问
  • 加密敏感数据：对敏感数据进行加密，保护数据安全
  • 审计数据库对象访问：审计数据库对象的访问，便于监控和追踪
  • 使用存储过程和函数：将业务逻辑封装在存储过程和函数中，减少直接访问表的机会

Q5: 如何迁移数据库对象？ ​

A: 迁移数据库对象可以使用以下方法：

  • 使用Oracle Data Pump：导出和导入数据库对象
  • 使用SQL脚本：生成数据库对象的SQL脚本，然后在目标数据库中执行
  • 使用第三方工具：如Oracle SQL Developer、Toad等工具迁移数据库对象
  • 使用Oracle GoldenGate：实时迁移数据库对象和数据

Q6: 如何监控数据库对象的性能？ ​

A: 监控数据库对象的性能可以使用以下方法：

  • 使用Oracle Enterprise Manager：监控数据库对象的性能，生成性能报告
  • 使用动态性能视图：如v$sql、v$session、v$lock等视图监控数据库对象的性能
  • 使用AWR和ASH报告：分析数据库对象的性能，识别瓶颈
  • 使用SQL Trace和TKPROF：跟踪SQL语句的执行，分析执行计划

Q7: 如何处理数据库对象的变更？ ​

A: 处理数据库对象的变更可以使用以下方法：

  • 制定变更管理流程：建立数据库对象变更的管理流程，确保变更的安全性和可追溯性
  • 测试变更：在测试环境中测试数据库对象的变更，验证变更的正确性
  • 备份数据库对象：在变更前备份数据库对象，以便在变更失败时恢复
  • 文档化变更：记录数据库对象的变更，便于追踪和管理
  • 监控变更影响：监控数据库对象变更对系统性能和功能的影响

Q8: 如何优化数据库对象的存储空间？ ​

A: 优化数据库对象的存储空间可以使用以下方法：

  • 压缩数据库对象：使用Oracle提供的压缩功能，减少数据库对象的存储空间
  • 分区表：将大表分成多个分区，提高查询性能，便于管理
  • 定期清理数据：定期清理不再需要的数据，减少存储空间占用
  • 使用合适的数据类型：选择合适的数据类型，减少存储空间占用
  • 优化索引：删除不必要的索引，减少存储空间占用