Oracle从入门到精通

一、Oracle 基础认知

Oracle 数据库是甲骨文公司的一款关系数据库管理体系，在各领域广泛应用，如金融、保险、银行等行业。其具有高可用性、强大的性能、广泛的兼容性、丰富的功能和安全性等特点。
在高可用性方面，Oracle 通过数据保卫（Data Guard）、实时应用集群（RAC）和闪回技术（Flashback Technology）等本领，确保数据库体系在出现故障时仍能一连运行，保障业务一连性。例如，数据保卫技术允许在主数据库出现故障时，立刻切换到备用数据库。
性能方面，Oracle 数据库的查询优化器（Query Optimizer）能根据查询条件和数据分布情况主动选择最优执行计划，提高查询性能。主动存储管理（ASM）通过主动化的存储管理机制，优化 I/O 性能。内存管理机制如主动内存管理（AMM）和主动共享内存管理（ASMM）能主动调整内存分配，提高体系性能和稳固性。
兼容性上，Oracle 数据库支持多种操纵体系、硬件平台和编程语言。它能在 Windows、Linux、UNIX 等多种操纵体系上运行，支持 x86、x64、SPARC 等多种硬件架构。同时，支持多种编程语言，如 SQL、PL/SQL、Java、C++、Python 等。
Oracle 数据库的版本历史丰富，从 1979 年的 Oracle 1.0 到 2013 年的 Oracle 12c，不断推出新特性和功能。如 Oracle 2.0 引入了 PL/SQL 语言；Oracle 3.0 引入了分布式数据库概念；Oracle 7.0 引入了分布式事务处置处罚、多线程并发处置处罚等重要新特性。
总之，Oracle 数据库以其卓越的性能、广泛的兼容性和强大的功能，在数据库领域占据重要地位。
二、安装与设置

（一）下载与准备

Oracle 数据库可以从 Oracle 官方网站进行下载。下载地址为：https://www.oracle.com/database/technologies/oracle-database-software-downloads.html。在下载前须要注册 Oracle 账户，并接受许可协议。
安装前的环境准备工作至关重要。在 Linux 体系上，起首须要查察体系信息，如服务器 ip、体系版本、主机名等。接着查抄磁盘空间情况，确保有足够的空间安装 Oracle。然后进行挂载光驱，设置 yum 设置，安装 Oracle 须要的软件包，设置 hosts 文件，添加组与用户，创建文件体系，修改操纵体系参数等操纵。例如，安装软件包时可以使用以下下令：yum install binutils -y、yum install compat-libcap1 -y等一系列下令安装所需软件包。在 Windows 体系上，安装前也须要确保体系更新到最新版本，关闭防火墙、杀毒软件等，确保有足够的磁盘空间。
（二）安装步调

在 Linux 上安装 Oracle 数据库：

• 更新体系并安装必备软件包，如：sudo yum update -y、sudo yum install -y bc binutils compat-libcap1 gcc gcc-c++ glibc glibc-devel ksh libaio libaio-devel libXrender libXrender-devel libX11 libXau libXi libXtst libXext make sysstat。
  
• 创建 Oracle 用户和组：sudo groupadd oinstall、sudo groupadd dba、sudo useradd -g oinstall -G dba oracle、passwd oracle。
  
• 创建 Oracle 安装目录：sudo mkdir -p /u01/app/oracle、sudo chown -R oracleinstall /u01/app/oracle、sudo chmod -R 775 /u01/app/oracle。
  
• 设置内核参数，编辑 /etc/sysctl.conf 文件添加相应参数后执行 sudo sysctl -p。
  
• 解压下载的安装文件，并运行安装步调：unzip LINUX.X64_193000_db_home.zip -d /u01/app/oracle、cd /u01/app/oracle、./runInstaller。按照安装向导的提示，选择安装类型和设置选项。完成后，使用 root 用户运行提示的脚本来完成安装。最后启动 Oracle 数据库：su - oracle、sqlplus / as sysdba、startup。
  

在 Windows 上安装 Oracle 数据库：

• 下载并解压安装包，从 Oracle 官网下载实用于 Windows 平台的 Oracle Database 19c ZIP 文件，将文件解压缩到一个目录。
  
• 启动安装步调，双击解压目录中的 setup.exe，按照安装向导的步调进行设置，选择 “创建并设置单实例数据库”，指定 Oracle 基础目录和数据库文件位置，设置数据库管理员暗码。
  
• 安装完成后，Oracle Database 会主动启动，并为您设置基本的数据库服务。
  

（三）基本操纵

启动与克制数据库：

• 启动数据库：在 SQL*Plus 中，以管理员身份毗连后启动数据库，如在 Linux 上，su - oracle、sqlplus / as sysdba、startup；在 Windows 上类似操纵。
  
• 克制数据库：可以使用 shutdown immediate 下令正常关闭数据库。例如在 Linux 上，su - oracle、sqlplus / as sysdba、shutdown immediate。
  

创建用户与赋权：

• 登录到 system，如在 Windows 下，打开下令提示符，输入 sqlplus /nolog，然后 connect /as sysdba 毗连。
  
• 创建新用户，例如创建用户名 root，暗码 root，可以使用 create user root identified by root;。
  
• 给新用户赋权，如 grant connect,resource to root;，可以赋予 connect（毗连）、resource（资源）、dba（数据库管理）等权限。
  

创建表与插入数据：

• 创建表，例如 CREATE TABLE employees (employee_id NUMBER PRIMARY KEY, first_name VARCHAR2(50), last_name VARCHAR2(50), hire_date DATE);。
  
• 插入数据，如 INSERT INTO employees (employee_id, first_name, last_name, hire_date)VALUES(1, ‘John’, ‘Doe’, SYSDATE);。
  

三、数据库查询与管理

（一）查询数据

Oracle 的查询功能非常强大，可以满意各种复杂的查询需求。
基本查询：可以使用简朴的 SELECT 语句查询表中的数据。例如，SELECT * FROM employees; 可以查询 employees 表中的全部数据。还可以指定特定的列进行查询，如 SELECT employee_id, first_name FROM employees;。可以使用别名来重命名列名，方便阅读和理解查询效果，例如 SELECT employee_id AS id, first_name AS fname FROM employees;。使用 DISTINCT 关键字可以去除重复值，如 SELECT DISTINCT department_id FROM employees;。
带条件查询：可以使用 WHERE 子句来添加查询条件。例如，查询工资大于 5000 的员工信息，可以使用 SELECT * FROM employees WHERE salary > 5000;。条件判定运算符包括 =、<、>、<=、>=、!= 等。还可以使用条件毗连符 AND、OR、NOT 来组合多个条件。例如，查询工资大于 5000 且部门为销售部的员工信息，可以使用 SELECT * FROM employees WHERE salary > 5000 AND department = ‘销售部’;。区间判定可以使用 BETWEEN…AND 操纵符，例如查询工资在 4000 到 6000 之间的员工信息，可以使用 SELECT * FROM employees WHERE salary BETWEEN 4000 AND 6000;。还可以使用 IN 操纵符来查询列值在指定列表中的数据，例如查询部门为销售部、市场部、研发部的员工信息，可以使用 SELECT * FROM employees WHERE department IN (‘销售部’, ‘市场部’, ‘研发部’);。暗昧查询可以使用 LIKE 关键字，通配符 % 表示零个或多个恣意字符，_ 表示一个恣意字符。例如，查询姓王且名字长度为两个字的员工信息，可以使用 SELECT * FROM employees WHERE first_name LIKE ‘王_’;。
排序与分组查询：可以使用 ORDER BY 子句对查询效果进行排序。默认是升序排序，可以使用 ASC 关键字明白指定升序，使用 DESC 关键字指定降序。例如，查询员工信息并按照工资降序排序，可以使用 SELECT * FROM employees ORDER BY salary DESC;。分组查询可以使用 GROUP BY 子句，把具有相同值的多条记录当成一组进行处置处罚。例如，查询每个部门的员工人数，可以使用 SELECT department_id, COUNT() FROM employees GROUP BY department_id;。在分组查询中，可以使用 HAVING 子句进行进一步的过滤，例如查询员工人数大于 10 的部门信息，可以使用 SELECT department_id, COUNT() FROM employees GROUP BY department_id HAVING COUNT(*) > 10;。
（二）备份与恢复

Oracle 数据库的备份与恢复是确保数据安全的重要本领。使用 RMAN（Recovery Manager）可以方便地进行数据库备份与恢复。
备份数据库：

• 全备份：使用 BACKUP DATABASE; 下令可以对整个数据库进行完全备份。默认情况下，数据库处在 “非归档模式” 下，可能会出现错误，须要先将数据库修改为 “归档模式”。可以通过以管理员身份毗连数据库，执行一系列下令来实现，如 shutdown immediate;（关闭数据库）、startup mount;（启动数据库到 “装载” 状态）、alter database archivelog;（修改数据库为 “归档模式”）、alter database open;（打开数据库）。然后再执行全备份下令。
  
• 增量备份：使用 BACKUP INCREMENTAL LEVEL 1 DATABASE; 下令可以进行增量备份，只备份自上次备份以来更改的数据块。
  
• 备份归档日志：使用 BACKUP ARCHIVELOG ALL; 下令可以备份全部的归档日志文件。
  
• 备份压缩：可以选择在备份下令里表现指定压缩选项，如 BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;（仅对 1,5 文件压缩备份）、BACKUP AS COMPRESSED BACKUPSET DATAFILE 1,5;。也可以设置 RMAN 指定压缩选项，如 CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO COMPRESSED BACKUPSET;（备份压缩到指定路径）、backup as Compressed backupset database format 'd:\oracle\backup\%U.dbf’plus archivelog for。
  

恢复数据库：

• 恢复整个数据库：在须要恢复数据库时，可以使用以下步调。起首关闭数据库 SHUTDOWN IMMEDIATE;，然后启动数据库到装载模式 STARTUP MOUNT;，接着进行恢复操纵 RESTORE DATABASE; 和 RECOVER DATABASE;，最后打开数据库 ALTER DATABASE OPEN;。
  
• 恢复特定命据文件：假如只须要恢复某个特定的数据文件，可以使用以下下令。先关闭数据库 SHUTDOWN IMMEDIATE;，然后启动到装载模式 STARTUP MOUNT;，分配通道 ALLOCATE CHANNEL c1 DEVICE TYPE DISK;，恢复特定命据文件 RESTORE DATAFILE ‘/path/to/datafile’;，恢复数据文件 RECOVER DATAFILE ‘/path/to/datafile’;，最后打开数据库 ALTER DATABASE OPEN;。
  
• 恢复表空间：假如只须要恢复某个特定的表空间，可以使用类似的步调。先关闭数据库，启动到装载模式，分配通道，然后恢复表空间 RESTORE TABLESPACE tablespace_name;，恢复表空间 RECOVER TABLESPACE tablespace_name;，最后打开数据库。
  
• 恢复时点恢复（PITR）：将数据库恢复到某个特定的时间点。先关闭数据库，启动到装载模式，分配通道，设置恢复时间点 SET UNTIL TIME “TO_DATE(‘2023-10-01 12:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)”;，恢复数据库 RESTORE DATABASE;，恢复数据库 RECOVER DATABASE;，最后打开数据库并重置日志 ALTER DATABASE OPEN RESETLOGS;。
  

四、高级功能探索

（一）数据库优化

数据库优化对于提高 Oracle 数据库的性能至关重要。执行计划分析和索引管理是两个重要的优化方法。
执行计划分析：
执行计划分析可以帮助我们了解 SQL 语句的执行方式和性能瓶颈。Oracle 数据库提供了多种方式来查察执行计划，例如使用 EXPLAIN PLAN 语句或者在 SQL Developer 等工具中查察执行计划。执行计划中包含了多个重要的字段，如基数（Rows）、字节（Bytes）和泯灭（COST）等。基数表示当前操纵的返回效果集行数，字节表示执行该步调后返回的字节数，泯灭则是 CPU 泯灭，用于说明 SQL 执行的代价，理论上越小越好。常见的执行计划类型包括 TABLE ACCESS FULL（全表扫描）、TABLE ACCESS BY ROWID（通过 ROWID 的表存取）、TABLE ACCESS BY INDEX SCAN（索引扫描）等。例如，假设我们有一个较大的表 LARGE_TABLE，且 username 列上没有索引，运行 SELECT * FROM LARGE_TABLE where USERNAME = ‘TEST’; 语句时，执行计划可能表现为 TABLE ACCESS FULL LARGE_TABLE [65001] [ANALYZED]，这表明进行了全表扫描。
索引管理：
索引是提高数据库查询性能的重要本领，但不准确使用或计划不当可能会导致性能下降。

• 合理选择和创建索引：
  
• 列选择：选择常常被查询的列作为索引列。例如对于常常用于过滤、毗连或排序的列，创建索引可以显著提高查询性能。但不宜过度索引，由于每个索引都会增加数据维护的开销。
  
• 索引类型：根据查询模式选择合适的索引类型。Oracle 数据库提供了多种类型的索引，包括 B 树索引、位图索引和函数索引等。例如，B-Tree 索引是 Oracle 中最常用的索引类型，实用于大多数查询场景，尤其是等值查询和范围查询。假设我们有一个员工表 CREATE TABLE employees (emp_id NUMBER PRIMARY KEY, name VARCHAR2(100), department_id NUMBER);，在 department_id 上创建 B-Tree 索引可以使用 CREATE INDEX idx_department ON employees(department_id);。位图索引特别实用于那些有限且重复值多的列（低基数），比如性别、部门等。例如，继续使用 employees 表，在性别字段上创建位图索引可以使用 CREATE BITMAP INDEX idx_gender ON employees(gender);。当常常须要对某个列进行函数操纵后查询时，可以考虑创建函数索引。比如假设常常须要对员工的入职日期进行年份查询，可以使用 CREATE INDEX idx_hire_year ON employees(EXTRACT(YEAR FROM hire_date));。
  
• 复合索引：对于常常同时查询多个列的查询，可以创建复合索引。复合索引是基于多个列的索引，可以镌汰查询时须要访问的数据块数量，从而提高查询性能。例如创建一个联合索引可以使用 CREATE INDEX idx_dept_id_name ON employees(department_id, name);。
  
• 索引统计信息：定期网络和更新索引的统计信息是保持索引性能的关键。通过网络统计信息，Oracle 数据库可以优化查询计划，选择更合适的索引进行查询操纵。
  
• 索引优化技巧：
  
• 覆盖索引：当一个索引包含了查询中全部须要的列时，这个索引就是覆盖索引。使用覆盖索引可以制止访问表数据，从而提高查询效率。例如假设查询通常须要员工的 ID 和姓名，可以创建索引 CREATE INDEX idx_emp_id_name ON employees(emp_id, name);。
  
• 联合索引的列顺序：在创建联合索引时，列的顺序非常关键。Oracle 会从左到右使用索引中的列。例如创建联合索引 CREATE INDEX idx_dept_id_name ON employees(department_id, name);，假如查询条件包含这两个字段，那么此索引非常有效。
  
• 索引压缩：在有重复值较多的列上，使用索引压缩可以节流存储空间。例如对联合索引使用压缩可以使用 CREATE INDEX idx_dept_id_name_compress ON employees(department_id, name) COMPRESS 1;。
  
• 不可见索引：不可见索引对优化器是不可见的，可以用于测试索引对查询性能的影响，而不影响现有的查询。例如创建一个不可见索引可以使用 CREATE INDEX idx_emp_email ON employees(email) INVISIBLE;。
  
• 分区索引：当表非常大时，使用分区和分区索引可以显著提高性能。例如创建一个分区表和分区索引可以使用 CREATE TABLE sales (sale_id NUMBER, sale_date DATE) PARTITION BY RANGE (sale_date) (PARTITION p2019 VALUES LESS THAN (TO_DATE(‘2020-01-01’, ‘YYYY-MM-DD’)), PARTITION p2020 VALUES LESS THAN (TO_DATE(‘2021-01-01’, ‘YYYY-MM-DD’))); CREATE INDEX idx_sale_date ON sales(sale_date) LOCAL;。
  
• 索引跳跃扫描：当查询条件中包含联合索引的一部分列时，Oracle 可以执行索引跳跃扫描来提高效率。例如假设有联合索引 CREATE INDEX idx_dept_job ON employees(department_id, job_id);，即使查询只包含 job_id，Oracle 也能有效地使用这个索引，如 SELECT * FROM employees WHERE job_id = ‘IT_PROG’;。
  
• 监控和优化索引：使用定期监控索引的使用情况，并根据实际情况对索引进行优化，是维持数据库性能的关键。可以通过 Oracle 的各种性能视图（如 V$INDEX_USAGE_INFO）来监控索引的使用情况。
  

（二）存储过程与触发器

存储过程和触发器是 Oracle 数据库中的强大功能，可以提高数据库的主动化和灵活性。
存储过程：
存储过程是一组针对数据库操纵的预界说的 SQL 语句，它可以存储在数据库中，供以后调用使用。在 Oracle 中，存储过程用 PL/SQL 语言编写，它是一种结合了 SQL 和步调计划的语言。PL/SQL 具有很强的数据操纵能力和过程控制能力，可以方便地编写出高效的存储过程来。

• 存储过程的好处：
  
• 增加数据库的执行效率，镌汰网络通讯的开销。由于存储过程已经被预先编译和优化，所以在执行时不须要反复进行解析和优化，可以直接调用执行。
  
• 通过参数来实现动态化的操纵，不但可以简化代码，还可以制止 SQL 注入等风险。
  
• 存储过程的创建和执行：
  
• 创建存储过程：在 Oracle 中，创建存储过程须要使用 CREATE PROCEDURE 语句。例如创建一个简朴的存储过程，它接受两个参数并输出它们的和，可以使用 CREATE OR REPLACE PROCEDURE add_nums( num1 IN NUMBER, num2 IN NUMBER, sum OUT NUMBER) IS BEGIN sum := num1 + num2; END add_nums;。
  
• 执行存储过程：在 Oracle 中，执行存储过程须要使用 EXECUTE 或 EXECUTE IMMEDIATE 语句。例如，执行上述示例步调，可以使用如下的语句：DECLARE result NUMBER; BEGIN add_nums(10, 20, result); DBMS_OUTPUT.PUT_LINE('The sum is: ’ || result); END ;。
  
• 参数类型：在存储过程中，参数可以是输入参数、输出参数或双向参数。输入参数指定存储过程的输入，输出参数指定存储过程的输出，双向参数既可以进行输入，也可以进行输出。声明参数类型的方法如下：(param_name [IN | OUT | IN OUT] param_type [,…])。例如创建一个包含三个参数的存储过程 my_proc，第一个参数 num 是输入参数，第二个参数 str 是双向参数，第三个参数 cur 是输出参数，可以使用 CREATE OR REPLACE PROCEDURE my_proc ( num IN NUMBER, str IN OUT VARCHAR2, cur OUT SYS_REFCURSOR) IS BEGIN – 逻辑实现 END my_proc;。
  
• 记录集处置处罚：用存储过程来操纵数据时常常须要返回查询效果列表。Oracle 提供了两种类型的记录集：游标和 PL/SQL 表。
  
• 游标：游标是一种返回效果集的数据布局，它可以遍历查询效果。游标可以是显式或隐式的，显式游标须要声明一个游标变量，并在代码中打开和关闭它，隐式游标则由 Oracle 主动创建和管理。例如创建一个演示如何使用游标的存储过程，可以使用 CREATE OR REPLACE PROCEDURE get_employee( id_list IN VARCHAR2, emp_cur OUT SYS_REFCURSOR) IS BEGIN OPEN emp_cur FOR ‘SELECT * FROM employees WHERE id IN (’ || id_list || ‘)’; END get_employee;。
  
• PL/SQL 表：PL/SQL 表是一种类似于数组的数据布局，它可以存储一组值。在 Oracle 中，可以在存储过程中声明和使用 PL/SQL 表。例如创建一个名为 my_package 的包，其中声明白一个名为 num_list 的 PL/SQL 表类型和一个使用该类型的存储过程 sum_nums，可以使用 CREATE OR REPLACE PACKAGE my_package IS TYPE num_list IS TABLE OF NUMBER INDEX BY PLS_INTEGER; PROCEDURE sum_nums(nums IN num_list, sum OUT NUMBER); END my_package; CREATE OR REPLACE PACKAGE BODY my_package IS PROCEDURE sum_nums(nums IN num_list, sum OUT NUMBER) IS total NUMBER := 0; BEGIN FOR indx IN 1…nums.COUNT LOOP total := total + nums(indx); END LOOP; sum := total; END sum_nums; END my_package;。
  

触发器：
触发器类似与存储过程，都是为了实现特别功能而执行的代码块。触发器不允许用户表现通报参数，不能够返回参数值，不允许用户调用触发器。触发器只是在 Oracle 合适的时间主动调用，非常类似于面向编程中的拦截器。

• 触发器的类型：
  
• 按照触发事件类型、对象差别分为：语句触发器，行触发器，Instead of 触发器，体系事件触发器，用户触发器。
  
• 前三者针对一般数据表有 insert、update、delet、DML 操纵，后两者为体系事件，drop、alter、DDL 操纵。DML - DML (Data Manipulation Language) 数据操纵语言下令使用户能够查询数据库以及操纵已有数据库中的数据。DDL (Data Definition Language)，是用于描述数据库中要存储的实际世界实体的语言。
  
• Oracle 中的触发器共有语句触发器、行触发器、instead of 触发器、体系事件触发器和用户事件触发器五类。
  
• 语句触发器和行级触发器的区别：
  
• 语句触发器：针对一次 DML 操纵，无论影响到表多少条记录，只触发一次。
  
• 行级触发器：针对 DML 操纵影响到的全部记录，均会触发一次。
  
• instead of 触发器的重要特点：
  
• instead of 触发器，则完全代替了 DML 操纵，也就是说，执行触发器动作，而本来的 DML 操纵不会执行。
  
• 替换触发器只能建立在视图上不能建立在表上。用户在视图上执行的 DML 操纵将被替换触发器中的操纵代替。替换触发器重要解决对不可更新视图执行更新操纵时带来的问题。在界说视图时，假如视图中没有选择基础表的主键咧，或者视图中的数据来自多个基础表，那么用户将无法对如许的视图直接执行插入、修改、删除操纵。这种情况下，用户可以针对是视图创建一个替换触发器，将对视图的更新操纵转换为对基础表的操纵。
  
• 触发器应用场景：
  
• 对于视图的更新操纵可以利用 instead of 触发器进行，从而自界说如何更新源数据表。
  
• 信息汇总：当明细数据更新时，可以利用触发器实现汇总数据的更新。
  
• 数据备份：当数据更新时，可以利用触发器将原数据记录到历史数据表中，从而实现数据跟踪。
  
• 用户事件触发器：
  
• 用户事件触发器创建时不再针对单个数据库对象，而是建立在用户或用户的模式之上。
  
• 触发器的创建和使用示例：
  
• 利用触发器对在 scott.emp 表上执行的 DML 操纵进行安全性查抄，只有 scott 用户登录数据库后才能向该表中执行 DML 操纵：
  
• 在 scott 用户下创建触发器语句：create or replace trigger tri_dm1 before insert or update or delete on scott.emp begin if user <>‘SCOTT’ then raise_application_error(-20001,‘You don’‘t have access to modify this table.’); end if; end;/。
  
• 以 system 用户毗连，并对 emp 表执行 DML 操纵，会触发错误。以 scott 用户毗连，并对 emp 表执行 DML 操纵，可以正常执行。
  
• 利用触发器进行表和备份表之间的同步复制：
  
• 在 scott 用户下创建 scott.emp 表的复本 employee：conn scott/tiger; create table employee as select * from scott.emp;。
  
• 在 scott 用户下创建能实现 scott.emp 和 employee 两表之间同步复制的 DML 触发器：create or replace trigger duplicate_empafter update or insert or delete on scott.emp for each row begin if inserting then insert into employee values (:new.empno,:new.ename,:new.job,:new.mgr,:new.hiredate,:new.sal,:new.comm,:new.deptno); elsif deleting then delete from employee where empno=ld.empno; else update employee set empno=:new.empno,ename=:new.ename,job=:new.job,mgr=:new.mgr,hiredate=:new.hiredate,sal=:new.sal,comm=:new.comm,deptno=:new.deptno where empno=ld.empno; end if; end;/。
  
• 对 scott.emp 表进行插入、删除和更新操纵，查询 scott.emp 表和 employee 表中插入、删除和更新的记录，可以看到两表同步更新。
  
• 建立触发器，对 scott.emp 表进行 DML 操纵时的时间、用户进行日志记录：
  
• 建立日志表 emp_log：create table emp_log(who varchar2(30), when date, oper varchar2(10));。
  
• 在 emp 表上建立语句级触发器，将对 emp 表执行的操纵记录到 emp_log 表中：create or replace trigger dm1_log after insert or update or delete on scott.emp declare oper emp_log.oper%type; begin if inserting then oper:=‘insert’; elsif deleting then oper:=‘delete’; else oper:=‘update’; end if; insert into emp_log values(user,sysdate,oper); end;/。
  
• 对 scott.emp 执行 DML 操纵，查察 emp_log 表中的数据，可以看到操纵记录。
  

五、Java 与 Oracle 集成

（一）毗连 Oracle 数据库

在 Java 中，使用 JDBC（Java Database Connectivity）毗连 Oracle 数据库是一种常见的方法。以下是一个示例代码展示如何在 Java 中使用 JDBC 毗连 Oracle 数据库：

1. import java.sql.Connection;
2. import java.sql.DriverManager;
3. import java.sql.SQLException;
5. public class OracleJDBCConnection {
6.     public static void main(String[] args) {
7.         String url = "jdbc:oracle:thin:@your_host:1521:your_service_name";
8.         String username = "your_username";
9.         String password = "your_password";
10.         try {
11.             Connection connection = DriverManager.getConnection(url, username, password);
12.             System.out.println("Connected to Oracle database successfully!");
13.             connection.close();
14.         } catch (SQLException e) {
15.             e.printStackTrace();
16.         }
17.     }
18. }

复制代码

在上述代码中，须要将your_host、your_service_name、your_username和your_password更换为实际的数据库毗连信息。
（二）使用 JPA

Java 长期化 API（JPA）提供了一种对象关系映射（ORM）的解决方案，使得在 Java 应用步调中进行数据库操纵更加便捷。以下是使用 JPA 操纵 Oracle 数据库的方法：
1. 添加依赖
在 Maven 项目中，须要添加 JPA 和 Oracle 数据库驱动的依赖。例如：

1. <dependency>
2.     <groupId>org.springframework.boot</groupId>
3.     <artifactId>spring-boot-starter-data-jpa</artifactId>
4. </dependency>
5. <dependency>
6.     <groupId>com.oracle</groupId>
7.     <artifactId>ojdbc6</artifactId>
8.     <version>11.2.0.2.0</version>
9. </dependency>

复制代码

2. 设置属性
在application.properties文件中设置数据库毗连信息和 JPA 属性：

1. spring.datasource.url=jdbc:oracle:thin:@your_host:1521:your_service_name
2. spring.datasource.username=your_username
3. spring.datasource.password=your_password
4. spring.jpa.show-sql=true

复制代码

3. 实体类界说
界说一个实体类，对应数据库中的表：

1. import javax.persistence.Entity;
2. import javax.persistence.GeneratedValue;
3. import javax.persistence.Id;
5. @Entity
6. public class User {
7.     @Id
8.     @GeneratedValue
9.     private Long id;
10.     private String name;
11.     private int age;
13.     // Getters and setters
14. }

复制代码

4. 仓库接口
创建一个仓库接口，继承自 JpaRepository，以便进行数据库操纵：

1. import org.springframework.data.jpa.repository.JpaRepository;
3. public interface UserRepository extends JpaRepository<User, Long> {
4. }

复制代码

5. 服务类和控制器
可以创建一个服务类来封装业务逻辑，并在控制器中调用服务类的方法进行数据库操纵。例如：

1. import org.springframework.stereotype.Service;
3. @Service
4. public class UserService {
5.     private final UserRepository userRepository;
7.     public UserService(UserRepository userRepository) {
8.         this.userRepository = userRepository;
9.     }
11.     public User saveUser(User user) {
12.         return userRepository.save(user);
13.     }
14. }
15. import org.springframework.web.bind.annotation.PostMapping;
16. import org.springframework.web.bind.annotation.RequestBody;
17. import org.springframework.web.bind.annotation.RestController;
19. @RestController
20. public class UserController {
21.     private final UserService userService;
23.     public UserController(UserService userService) {
24.         this.userService = userService;
25.     }
27.     @PostMapping("/users")
28.     public User createUser(@RequestBody User user) {
29.         return userService.saveUser(user);
30.     }
31. }

复制代码

六、进阶学习

（一）分页

Oracle 数据库提供了多种分页方法，以下是对分页实现方法的介绍：

• 使用 rowid 实现分页：
  

• 代码示例：
  

1.      SELECT *FROM (SELECT RIDFROM (SELECT R.RID, ROWNUM LINENUMFROM (SELECT ROWID RIDFROM TABLE1WHERE TABLE1_ID = XXORDER BY order_date DESC) RWHERE ROWNUM <=20)WHERE LINENUM >=10) T1,TABLE1 T2WHERE T1.RID = T2.ROWID;

复制代码

• 原理分析：起首通过 ROWNUM 查询到分页之后的 10 条实际返回记录的 ROWID，最后通过 ROWID 将最终返回字段值查询出来并返回。此方式的实现更加繁琐，通用性不是非常好，但在特定场景下有上风，比如常常要翻页到很反面的数据时，效率可能比其他方法高。
  

• 使用 rownum 实现分页：
  

• 代码示例：
  
• 方法一：
  

1.        SELECT * FROM (SELECT a.*, ROWNUM rFROM my_table aWHERE ROWNUM <=30)WHERE r >=20;

复制代码

• 方法二：结合 BETWEEN AND
  

1.        SELECT * FROM (SELECT A.*, ROWNUM RNFROM (SELECT *FROM TABLE1WHERE TABLE1_ID = XXORDER BY GMT_CREATE DESC) A)WHERE RN BETWEEN 10 AND 20;

复制代码

• 方法三：结合 MINUS
  

1.        SELECT * FROM TABLE1WHERE ROWNUM <= 20MINUSSELECT * FROM TABLE1 WHERE ROWNUM <= 10;

复制代码

• 长处是简朴易懂，缺点是在查询大量数据时性能较差。
  

• 使用分页模板：
  

• Oracle 的三层分页指的是在进行分页查询时，使用三种差别的方式来实现分页效果，分别是使用 ROWNUM、使用 OFFSET 和 FETCH、使用 ROW_NUMBER () OVER ()。
  
• 使用 OFFSET 和 FETCH：
  

1.      SELECT * FROM my_tableORDER BY my_columnOFFSET20ROWSFETCH NEXT 10ROWSONLY;

复制代码

• 使用 ROW_NUMBER () OVER ()：
  

1.      SELECT * FROM (SELECT a.*, ROW_NUMBER() OVER (ORDERBY my_column) rFROM my_table a)WHERE r BETWEEN21AND30;

复制代码

（二）视图

• 创建视图：
  

• 在 CREATE VIEW 语句中嵌入子查询，子查询可以是复杂的 SELECT 语句。例如：
  

1.      create or replace view empviewasselect employee_id emp_id,last_name name,department_namefrom employees e,departments dWhere e.department_id = d.department_id

复制代码

• 在选择视图中的列时应使用别名，如：
  

1.      CREATE VIEW salvu50ASSELECT employee_id ID_NUMBER,last_name NAME,salary*12 ANN_SALARYFROM employeesWHERE department_id = 50;

复制代码

• 查询视图：
  

• 使用 SELECT 语句查询视图，例如：
  

1.      SELECT * FROM salvu50;

复制代码

• 删除视图：
  

• 使用 DROP VIEW 语句删除视图，例如：
  

1.      DROP VIEW empvu80;

复制代码

（三）存储过程

• 界说：存储过程是一组用于完成特定命据库功能的 SQL 语句集，经过编译后存储在数据库体系中。在使用时候，用户通过指定已经界说的存储过程名字并给出相应的存储过程参数来调用并执行它，从而完成一个或一系列的数据库操纵。
  
• 调用：
  

• 执行语法有多种，例如：
  
• call sample_proc();
  
• exec sample_proc;
  
• set serveroutput onbeginsample_proc;end;
  

• 带返回值的存储过程：
  

• 代码示例：
  

1.      create or replace procedure count_proc(detpcode in varchar,num out number)ascot number(5);beginselect count(*) into cot from pat_visit_user where dept_code=detpcode;num:=cot;end;

复制代码

• 参数 IN 表示输入参数，是参数的默认模式。OUT 表示返回值参数，也就是输出参数。类型可以使用恣意 Oracle 中的正当类型。
  

• 各种条件下的存储过程：
  

• 参数的数据类型只须要指明类型名即可，不须要指定宽度。参数的宽度由外部调用者决定。过程可以有参数，也可以没有参数。
  
• 变量声明块用于声明该存储过程须要用到的变量，它的作用域为该存储过程。另外这里声明的变量必须指定宽度。遵照 PL/SQL 的变量声明规范。
  

（四）约束

Oracle 数据库的约束包括主键约束、唯一约束、外键约束、查抄约束、非空约束、默认约束。

• 主键约束：确保每行数据的唯一性，不允许空值。例如：
  

1.    CREATE TABLE employees (employee_id NUMBER PRIMARY KEY,first_name VARCHAR2(50),last_name VARCHAR2(50));

复制代码

• 唯一约束：确保列中的每个值都是唯一的，但允许空值。例如：
  

1.    CREATE TABLE employees (employee_id NUMBER,email VARCHAR2(100) UNIQUE);

复制代码

• 外键约束：用于维护表与表之间的关系，确保一个表中的值必须在另一个表中存在。例如：
  

1.    CREATE TABLE departments (department_id NUMBER PRIMARY KEY,department_name VARCHAR2(50));CREATE TABLE employees (employee_id NUMBER PRIMARY KEY,department_id NUMBER,CONSTRAINT fk_department FOREIGN KEY (department_id)REFERENCES departments (department_id));

复制代码

• 查抄约束：用于强制列中的数据满意特定的条件。例如：
  

1.    CREATE TABLE employees (employee_id NUMBER PRIMARY KEY,salary NUMBER,CONSTRAINT chk_salary CHECK (salary >0));

复制代码

• 非空约束：用于确保列中的数据不能为空。例如：
  

1.    CREATE TABLE employees (employee_id NUMBER PRIMARY KEY,first_name VARCHAR2(50) NOT NULL,last_name VARCHAR2(50) NOT NULL);

复制代码

• 默认约束：用于在列中没有提供值时，为其赋予默认值。例如：
  

1.    CREATE TABLE employees (employee_id NUMBER PRIMARY KEY,hire_date DATE DEFAULT SYSDATE,status VARCHAR2(20) DEFAULT 'ACTIVE');

复制代码

（五）序列

• 创建序列：
  

• 语法：create sequence 序列名 [可选参数]。
  
• 可选参数说明：
  
• increment by：序列每次增加的值，负数表示递减，缺省值为 1。
  
• start with：序列的初始值，缺省值为 1。
  
• maxvalue：序列可生成的最大值，缺省值为 nomaxvalue，即不设置最大值。
  
• minvalue：序列可生成的最小值，缺省值为 nominvalue，即不设置最小值。
  
• cycle：界说当序列达到最大 / 小值后是否循环，缺省值为不循环。
  
• cache：界说缓存序列的个数，缺省值为 20，nocache 表示不设置缓存。
  
• 例如：create sequence REQ_NOincrement by1startwith1maxvalue 99999cyclenocache;
  

• 使用序列：
  

• 语法：序列名.[currval/nextval]。
  
• currval：表示当前值，新序列若没使用过nextval不能直接使用当前值，会报错。
  
• nextval：表示序列的下一个值。
  
• 例如：select seq_name.nextval from dual;查询序列seq_name的下一个值。
  

（六）同义词

• 创建同义词：
  

• 利用企业管理器创建企业表同义词，名为 “qyb”。grant create public synonym to scott;（授权乐成）。
  
• 利用 SQLPlus 或 iSQLPlus 创建医保卡表的同义词，名为 “ybk”。create synonym ybk for card;（同义词已创建）。
  

• 使用限制：对同义词的操纵与对原数据库对象的操纵基本一致，但须要注意同义词只是一个别名，不能改变原数据库对象的本质属性。在某些情况下，可能须要考虑同义词所指向的对象是否存在、权限是否足够等问题。
  

（七）索引

• 作用：索引是提高数据库查询性能的重要本领。它可以加快数据的检索速度，镌汰磁盘 I/O 操纵，提高数据库的响应时间。
  
• 创建方法：
  

• 选择常常被查询的列作为索引列。例如对于常常用于过滤、毗连或排序的列，创建索引可以显著提高查询性能。但不宜过度索引，由于每个索引都会增加数据维护的开销。
  
• 根据查询模式选择合适的索引类型。Oracle 数据库提供了多种类型的索引，包括 B 树索引、位图索引和函数索引等。
  
• 对于常常同时查询多个列的查询，可以创建复合索引。复合索引是基于多个列的索引，可以镌汰查询时须要访问的数据块数量，从而提高查询性能。
  
• 定期网络和更新索引的统计信息是保持索引性能的关键。通过网络统计信息，Oracle 数据库可以优化查询计划，选择更合适的索引进行查询操纵。
  

• 使用注意事项：
  

• 不准确使用或计划不当的索引可能会导致性能下降。例如，在频繁更新的列上创建索引可能会增加数据维护的开销，从而低沉数据库的性能。
  
• 覆盖索引可以制止访问表数据，从而提高查询效率，但须要合理计划索引以确保包含查询中全部须要的列。
  
• 在创建联合索引时，列的顺序非常关键。Oracle 会从左到右使用索引中的列。
  
• 在有重复值较多的列上，使用索引压缩可以节流存储空间。
  
• 不可见索引对优化器是不可见的，可以用于测试索引对查询性能的影响，而不影响现有的查询。
  
• 当表非常大时，使用分区和分区索引可以显著提高性能。
  
• 索引跳跃扫描可以在查询条件中包含联合索引的一部分列时提高效率。
  
• 使用定期监控索引的使用情况，并根据实际情况对索引进行优化，是维持数据库性能的关键。可以通过 Oracle 的各种性能视图（如V$INDEX_USAGE_INFO）来监控索引的使用情况。
  

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。