oracle数据访问 Oracle Data Access

原网页：Oracle Data Access
结构化查询语言 (SQL)是高级声明性计算机语言，所有程序和用户都使用它来访问 Oracle 数据库中的数据。PL/SQL和Java是服务器端过程语言，使您能够将数据逻辑存储在数据库本身中。

SQL

内容包括结构化查询语言 (SQL)以及 Oracle 数据库如何处理 SQL 语句

sql简介 Introduction to SQL

SQL（读作 sequel）是一种基于集合的高级声明性计算机语言，所有程序和用户都使用它访问 Oracle 数据库中的数据。

尽管某些 Oracle 工具和应用程序掩盖了 SQL 的使用，但所有数据库任务都是使用 SQL 执行的。任何其他数据访问方法都会绕过 Oracle 数据库中内置的安全性，并可能危及数据安全性和完整性。

SQL 为关系数据库（如 Oracle 数据库）提供了一个接口。SQL 以一种一致的语言统一了以下任务：

创建、替换、更改和删除对象

插入、更新和删除表行

查询数据

控制对数据库及其对象的访问

保证数据库的一致性和完整性

SQL 可以交互使用，这意味着语句是手动输入到程序中的。SQL 语句也可以嵌入到用不同语言（如 C 或 Java）编写的程序中。
更多可以看：
“Introduction to Server-Side Programming”
Oracle Database Development Guide
Oracle Database SQL Language Reference

SQL 数据访问

有两大类计算机语言：非过程的声明性语言，描述应该做什么，以及过程语言，如 C++ 和 Java，描述应该如何做。

SQL 是声明性的，用户指定他们想要的结果，而不是如何派生它。例如，以下语句查询姓氏以 K开头的员工的记录：

数据库执行生成过程以导航数据并检索请求的结果的工作。SQL 的声明性特性使您能够在逻辑级别处理数据。只有在操作数据时才需要关注实现细节。

SELECT   last_name, first_name
FROM     hr.employees
WHERE    last_name LIKE 'K%'
ORDER BY last_name, first_name;

数据库在一个步骤中检索所有满足WHERE 条件的行，也称为谓词。数据库可以将这些行作为一个单元传递给用户、另一个 SQL 语句或应用程序。应用程序不需要逐一处理行，开发人员也不需要知道行是如何物理存储或检索的。

所有 SQL 语句都使用优化器，它是确定访问请求数据的最有效方式的数据库组件。Oracle 数据库还支持优化器更好地执行其工作。
也可以看看：《Oracle Database SQL Language Reference》了解有关 SQL 语句和 SQL 其他部分（例如运算符、函数和格式模型）的详细信息

SQL 标准

Oracle 努力遵循行业公认的标准并积极参与 SQL 标准委员会。

行业认可的委员会是美国国家标准协会 (ANSI) 和国际标准化组织 (ISO)。ANSI 和ISO/IEC 都接受 SQL 作为关系数据库的标准语言。

SQL 标准由十个部分组成。一个部分（SQL/RPR:2012）是2102年新增的。另外五个部分是在2011年修订的。另外四个部分，2008版本保持不变。

Oracle SQL包括对 ANSI/ISO 标准 SQL 语言的许多扩展，并且 Oracle 数据库工具和应用程序提供了额外的语句。SQLPlus、SQL Developer 和 Oracle Enterprise Manager 工具使您能够针对 Oracle 数据库运行任何 ANSI/ISO 标准 SQL 语句以及可用于这些工具的任何附加语句或函数。
更多可以看：
Oracle Database 2 Day Developer’s Guide
Oracle Database SQL Language Reference
SQLPlus User’s Guide and Reference

sql语句概述 Overview of SQL Statements

对 Oracle 数据库中的信息执行的所有操作都是使用 SQL语句运行的。SQL 语句是由标识符、参数、变量、名称、数据类型和 SQL保留字组成的计算机程序或指令。
注：SQL 保留字在 SQL 中具有特殊含义，不应用于任何其他目的。例如，SELECT、UPDATE是保留字，不应用作表名。
A SQL statement must be the equivalent of a complete SQL sentence, such as:

SELECT last_name, department_id FROM employees

Oracle 数据库只运行完整的 SQL 语句。如下所示的片段会生成一个错误，指示需要更多文本：

SELECT last_name;

Oracle SQL 语句分为以下几类：
Data Definition Language (DDL) Statements 数据定义语言 (DDL) 语句
Data Manipulation Language (DML) Statements 数据操作语言 (DML) 语句
Transaction Control Statements 事务控制语句
Session Control Statements 会话控制语句
System Control Statement 系统控制声明
Embedded SQL Statements 嵌入式 SQL 语句

数据定义语言 (DDL) 语句 Data Definition Language (DDL) Statements

数据定义语言 ( DLL ) 语句定义、结构更改和删除模式对象。

DDL 使您能够更改对象的属性，而无需更改访问该对象的应用程序。例如，您可以向人力资源应用程序访问的表中添加一列，而无需重写该应用程序。您还可以在数据库用户在数据库中执行工作时使用 DDL 来更改对象的结构。
更具体地说，DDL 语句使您能够：

• 创建、更改和删除模式对象和其他数据库结构，包括数据库本身和数据库用户。大多数 DDL 语句都以关键字CREATE、ALTER或开头DROP。
• 删除模式对象中的所有数据而不删除这些对象的结构 ( TRUNCATE)。
• 注意：Unlike DELETE, TRUNCATE generates no undo data, which makes it faster than DELETE. Also, TRUNCATE does not invoke delete triggers
• 授予和撤销权限和角色 ( GRANT, REVOKE)。
• 打开和关闭审核选项 ( AUDIT, NOAUDIT)。
• 向数据字典( COMMENT) 添加注释。
  示例：DDL 语句

CREATE TABLE plants
    ( plant_id    NUMBER PRIMARY KEY, 
      common_name VARCHAR2(15) );
INSERT INTO plants VALUES (1, 'African Violet'); # DML statement
INSERT INTO plants VALUES (2, 'Amaryllis'); # DML statement
ALTER TABLE plants ADD 
    ( latin_name VARCHAR2(40) );

GRANT READ ON plants TO scott;
REVOKE READ ON plants FROM scott;
DROP TABLE plants;

一个隐式COMMIT发生在数据库执行 DDL 语句之前立即发生，然后立即发生COMMITor ROLLBACK。在前面的示例中，两条INSERT语句后跟一条ALTER TABLE语句，因此数据库提交了两条INSERT语句。如果ALTER TABLE语句成功，则数据库提交该语句；否则，数据库回滚该语句。无论哪种情况，这两个INSERT语句都已提交。

数据操作语言 (DML) 语句

数据操作语言 ( DML ) 语句查询或操作现有模式对象中的数据。

DDL 语句更改数据库的结构，而 DML 语句查询或更改内容。例如，ALTER TABLE更改表的结构，同时向表中INSERT添加一行或多行。

DML 语句是最常用的 SQL 语句，使您能够：

• 从一个或多个表或视图中检索或获取数据 ( SELECT)。
• INSERT通过指定列值列表或使用子查询来选择和操作现有数据， 将新的数据行添加到表或视图 ( ) 中。
• 更改表或视图的现有行中的列值 ( UPDATE)。
• 有条件地将行更新或插入到表或视图中( MERGE)。
• 从表或视图中删除行 ( DELETE)。
• 查看 SQL 语句的执行计划( EXPLAIN PLAN)。
• 锁定表或视图，暂时限制其他用户的访问 ( LOCK TABLE)。
  以下示例使用 DML 查询employees表。该示例使用 DML 在 中插入一行employees，更新该行，然后将其删除：

SELECT * FROM employees;
INSERT INTO employees (employee_id, last_name, email, job_id, hire_date, salary)
  VALUES (1234, 'Mascis', 'JMASCIS', 'IT_PROG', '14-FEB-2008', 9000);
UPDATE employees SET salary=9100 WHERE employee_id=1234;
DELETE FROM employees WHERE employee_id=1234;

构成逻辑工作单元的 DML 语句的集合称为事务。例如，转账交易可能涉及三个离散操作：减少储蓄账户余额、增加支票账户余额以及在账户历史表中记录转账。与 DDL 语句不同，DML 语句不会隐式提交当前事务。

SELECT Statements

查询是从表或视图中检索数据的操作 。
SELECT是唯一可用于查询数据的 SQL 语句。从执行SELECT语句中检索到的数据集称为结果集。
SELECT下表显示了语句中常见的两个必需关键字和两个关键字。该表还将SELECT语句的功能与关键字相关联。

Keyword	Required?	Description	Capability
SELECT	Yes	指定应在结果中显示哪些列。投影生成表中列的子集。表达式是一个或多个值、运算符和解析为值的 SQL 函数的组合。出现在SELECT关键字之后和FROM子句之前的表达式列表称为选择列表。	Projection
FROM	Yes	指定应从中检索数据的表或视图。	Joining
WHERE	NO	指定过滤行的条件，生成表中行的子集。条件指定一个或多个表达式和逻辑（布尔）运算符的组合，并返回TRUE、FALSE或的值UNKNOWN。	Selection
ORDER	NO	指定显示行的顺序。

select语法

Joins

A join is a query that combines rows from two or more tables, views, or materialized views.

The following example joins the employees and departments tables (FROM clause), selects only rows that meet specified criteria (WHERE clause), and uses projection to retrieve data from two columns (SELECT). Sample output follows the SQL statement.

SELECT email, department_name
FROM   employees 
JOIN   departments
ON     employees.department_id = departments.department_id
WHERE  employee_id IN (100,103)
ORDER BY email;

EMAIL                     DEPARTMENT_NAME
------------------------- ------------------------------
AHUNOLD                   IT
SKING                     Executive

The following graphic represents the operations of projection and selection in the join shown in the preceding query.

大多数连接在子句或子句中至少有一个连接条件，用于比较两列，每列来自不同的表。数据库组合成对的行，每对包含来自每个表的一行，其连接条件的计算结果为TRUE。优化器根据连接条件、索引和表的任何可用统计信息来确定数据库连接表的顺序
联接类型包括以下内容：

• 内连接
• 内连接是两个或多个表的连接，它只返回满足连接条件的行。例如，如果连接条件为employees.department_id=departments.department_id，则不返回不满足此条件的行。
• 外连接
• 外连接返回满足连接条件的所有行，并返回一个表中没有另一表中的行满足条件的行。
• 表A和B的左外连接的结果始终包含左表A的所有记录，即使连接条件与右表B中的记录不匹配。如果不存在来自B的匹配行，则B列包含在B中不匹配的行的空值。例如，如果不是所有员工都在部门中，那么（左表）和（右表）的左外连接会检索所有行，即使没有满足连接条件的行（为空）。 employeesdepartmentsemployeesdepartmentsemployees.department_id
• 表A和B的右外连接的结果包含右表B的所有记录，即使连接条件与左表A中的行不匹配。如果不存在来自A 的匹配行，则A列包含 A 中不匹配的行的空值。例如，如果不是所有部门都有员工，则（左表）和（右表）的右外连接检索所有行，即使没有满足连接条件的行。 employeesdepartmentsdepartmentsemployees
• 全外连接是左外连接和右外连接的组合。
• 笛卡尔积
• 如果连接查询中的两个表没有连接条件，则数据库执行笛卡尔连接。一个表的每一行与另一个表的每一行结合。例如，如果employees有 107 行且departments有 27 行，则笛卡尔积包含 107*27 行。笛卡尔积很少有用。
  也可以看看：
  《Oracle 数据库 SQL 调优指南》了解联接
  《 Oracle 数据库 SQL 语言参考》中有关连接的详细说明和示例

Subqueries

子查询是SELECT嵌套在另一个 SQL 语句中的语句。当您必须执行多个查询来解决单个问题时，子查询很有用。

语句的每个查询部分称为查询块。在以下查询中，括号中的子查询是内部查询块：

SELECT first_name, last_name 
FROM   employees
WHERE  department_id 
IN     ( SELECT department_id 
         FROM departments 
         WHERE location_id = 1800 );

内部SELECT语句检索位置 ID 为 1800 的部门的 ID。外部查询块需要这些部门 ID，外部查询块检索其 ID 由子查询提供的部门中的员工姓名。

SQL 语句的结构并不强制数据库先执行内部查询。例如，数据库可以将整个查询重写为 and 的连接employees，departments这样子查询就不会自行执行。再比如，虚拟专用数据库（Virtual Private Database，VPD）特性可以通过WHERE子句限制员工的查询，使数据库先查询员工，再获取部门ID。优化器确定检索请求行的最佳步骤顺序。
也可以看看：

Oracle 数据库安全指南以了解有关 VPD 的更多信息

事务控制语句

事务控制语句管理 DML 语句所做的更改，并将 DML 语句分组为事务。
这些语句使您能够：

• 永久更改事务 ( COMMIT)。
• 撤消事务中的更改，因为事务开始 ( ROLLBACK) 或保存点 ( ROLLBACK TO SAVEPOINT)。保存点是事务上下文中用户声明的中间标记 。
  注：该ROLLBACK语句结束事务，但ROLLBACK TO SAVEPOINT没有结束。
  设置一个可以回滚的点 ( SAVEPOINT)。
• 为事务建立属性 ( SET TRANSACTION)。
• 指定是否在每个 DML 语句之后或提交事务时检查可延迟完整性约束SET CONSTRAINT( )。
  下面的示例启动一个名为 的事务Update salaries。该示例创建一个保存点，更新员工工资，然后将事务回滚到保存点。该示例将薪水更新为不同的值并提交。

SET TRANSACTION NAME 'Update salaries';
SAVEPOINT before_salary_update;
UPDATE employees SET salary=9100 WHERE employee_id=1234 # DML
ROLLBACK TO SAVEPOINT before_salary_update;
UPDATE employees SET salary=9200 WHERE employee_id=1234 # DML
COMMIT COMMENT 'Updated salaries';

也可以看看：
《交易简介》交易简介
《Oracle 数据库 SQL 语言参考》了解事务控制语句

会话控制语句

会话控制语句
会话控制语句动态地管理用户会话的属性。
会话是数据库实例内存中的一个逻辑实体，它表示当前用户登录到数据库的状态。会话从用户通过数据库认证开始一直持续到用户断开或退出数据库应用程序。
会话控制语句使您能够：

• 通过执行特殊功能更改当前会话，例如设置默认日期格式 ( ALTER SESSION)。
• 为当前会话启用和禁用角色，即权限组 ( SET ROLE)。
  以下语句将会话的默认日期格式动态更改为’YYYY MM DD-HH24:MI:SS’：

ALTER SESSION 
   SET NLS_DATE_FORMAT = 'YYYY MM DD HH24:MI:SS';

会话控制语句不会隐式提交当前事务。

系统控制语句 System Control Statement

系统控制语句更改数据库实例的属性。
唯一的系统控制语句是ALTER SYSTEM. 它使您能够更改设置，例如共享服务器的最小数量、终止会话以及执行其他系统级任务。
系统控制语句的示例包括：

ALTER SYSTEM SWITCH LOGFILE; 
ALTER SYSTEM KILL SESSION '39, 23';

该ALTER SYSTEM语句不会隐式提交当前事务。

嵌入式 SQL 语句

嵌入式 SQL 语句将 DDL、DML 和事务控制语句合并到一个过程语言程序中。
嵌入式语句与 Oracle 预编译器一起使用。嵌入式 SQL 是将 SQL 合并到过程语言应用程序中的一种方法。另一种方法是使用过程 API，例如开放式数据库连接 (ODBC) 或 Java 数据库连接 (JDBC)。
嵌入式 SQL 语句使您能够：
定义、分配和释放游标( DECLARE CURSOR, OPEN, CLOSE)。
指定一个数据库并连接到它 ( DECLARE DATABASE, CONNECT)。
分配变量名称 ( DECLARE STATEMENT)。
初始化描述符 ( DESCRIBE)。
指定如何处理错误和警告条件 ( WHENEVER)。
解析并运行 SQL 语句 ( PREPARE, EXECUTE, EXECUTE IMMEDIATE)。
从数据库中检索数据 ( FETCH)。
也可以看看：
《服务器端编程导论》
Oracle 数据库开发指南

优化器概述 Overview of the Optimizer

要了解 Oracle 数据库如何处理 SQL 语句，有必要了解数据库中称为优化器（也称为查询优化器或基于成本的优化器）的部分。所有 SQL 语句都使用优化器来确定访问指定数据的最有效方法。

优化器的使用 Use of the Optimizer

优化器生成描述可能的执行方法的执行计划。
优化器通过考虑多个信息源来确定哪个执行计划最有效。例如，优化器会考虑查询条件、可用访问路径、为系统收集的统计信息和提示。
要执行 DML 语句，Oracle 数据库可能必须执行许多步骤。每个步骤要么从数据库物理检索数据行，要么为发出语句的用户准备它们。数据库用于执行语句的步骤会极大地影响语句的运行速度。处理 DML 语句的许多不同方法通常是可能的。例如，访问表或索引的顺序可能会有所不同。
在确定一条 SQL 语句的最佳执行计划时，优化器会执行以下操作：

• 表达式和条件的评估 Evaluation of expressions and conditions
• 检查完整性约束以了解有关数据的更多信息并基于此元数据进行优化 Inspection of integrity constraints to learn more about the data and optimize based on this metadata
• 语句转换 Statement transformation
• 优化器目标的选择 Choice of optimizer goals
• 访问路径的选择 Choice of access paths
• 连接顺序的选择 Choice of join orders
  优化器生成处理查询的大多数可能方式，并为生成的执行计划中的每个步骤分配成本。选择成本最低的计划作为要执行 的查询计划。
  注：您可以在不执行的情况下获取 SQL 语句的执行计划。但是只有数据库实际用于执行查询的执行计划才被正确地称为查询计划。
  您可以通过设置优化器目标和收集优化器的代表性统计信息来影响优化器选择。例如，您可以将优化器目标设置为以下之一：
• Total throughput
  The ALL_ROWS hint instructs the optimizer to get the last row of the result to the client application as fast as possible.
• Initial response time
  The FIRST_ROWS hint instructs the optimizer to get the first row to the client as fast as possible
  典型的最终用户交互式应用程序将受益于初始响应时间优化，而批处理模式的非交互式应用程序将受益于总吞吐量优化。
  A typical end-user, interactive application would benefit from initial response time optimization, whereas a batch-mode, non-interactive application would benefit from total throughput optimization.

优化器组件

The optimizer contains three main components: the transformer, estimator, and plan generator.
优化器包含三个主要组件：转换器、估计器和计划生成器。

优化器的输入是一个解析查询。优化器执行以下操作：

• 优化器接收解析后的查询，并根据可用的访问路径和提示为 SQL 语句生成一组潜在的计划。
• 优化器根据数据字典中的统计信息估计每个计划的成本。成本是与使用特定计划执行语句所需的预期资源使用成比例的估计值。
• 优化器比较计划的成本并选择成本最低的计划（称为查询计划）传递给行源生成器。

Query Transformer

查询转换器确定更改查询的形式是否有帮助，以便优化器可以生成更好的执行计划。查询转换器的输入是经过解析的查询，优化器将其表示为一组查询块。

Estimator

估算器确定给定执行计划的总成本 。
估算器生成三种不同类型的度量来实现此目标：
选择性
此度量表示行集中的一小部分行。选择性与查询谓词（例如last_name=‘Smith’）或谓词组合相关联。
基数
此度量表示行集中的行数。
成本
该度量代表使用的工作单元或资源。查询优化器使用磁盘 I/O、CPU 使用率和内存使用率作为工作单元。
如果统计数据可用，则估计器使用它们来计算度量。统计数据提高了测量的准确程度。

Plan Generator

计划生成器为提交的查询尝试不同的计划。优化器选择成本最低的计划。

对于每个嵌套的子查询和未合并的视图，优化器都会生成一个子计划。优化器将每个子计划表示为一个单独的查询块。计划生成器通过尝试不同的访问路径、连接方法和连接顺序来探索查询块的各种计划。

自适应查询优化功能根据语句执行期间收集的统计信息更改计划。所有自适应机制都可以为不同于默认计划的语句执行最终计划。自适应优化使用动态计划（在语句执行期间在子计划中进行选择）或重新优化（在当前执行之后更改执行计划）。

访问路径

访问路径是查询用来检索行的技术 。

例如，使用索引的查询与不使用索引的查询具有不同的访问路径。通常，索引访问路径最适合检索表行的一小部分子集的语句。完全扫描对于访问大部分表更有效。

数据库可以使用几种不同的访问路径从表中检索数据。以下为代表名单：

全表扫描

这种类型的扫描从表中读取所有行并过滤掉那些不符合选择条件的行。数据库顺序扫描段中的所有数据块，包括高水位线（HWM）下的数据块，该高水位线将已用空间与未使用空间分开（参见“段空间和高水位线”）。

行扫描

行的rowid指定包含该行的数据文件和数据块以及该行在该块中的位置。数据库首先从语句WHERE子句或通过索引扫描获取所选行的rowid，然后根据其rowid定位每个所选行。

索引扫描

此扫描在索引中搜索 SQL 语句访问的索引列值（请参阅“索引扫描”）。如果语句只访问索引的列，则 Oracle 数据库直接从索引中读取索引列值。

集群扫描

集群扫描从存储在索引表集群中的表中检索数据，其中具有相同集群键值的所有行都存储在同一数据块中（请参阅“索引集群概述”）。数据库首先通过扫描簇索引来获取选中行的rowid。Oracle 数据库根据此 rowid 定位行。

哈希扫描

散列扫描定位散列簇中的行，其中具有相同散列值的所有行都存储在同一个数据块中（参见“散列簇概述”）。数据库首先通过对语句指定的集群键值应用散列函数来获得散列值。Oracle 数据库然后扫描包含具有此哈希值的行的数据块。

优化器根据语句的可用访问路径和使用每个访问路径或路径组合的估计成本来选择访问路径。

优化器统计

优化器统计信息是描述有关数据库和数据库中对象的详细信息的数据集合。统计数据提供了优化器在评估访问路径时可用的数据存储和分布的统计正确图。

优化器统计信息包括以下内容：

• 表统计
• 这些包括行数、块数和平均行长。
• 列统计
• 这些包括列中不同值和空值的数量以及数据的分布。
• 指数统计
• 这些包括叶块的数量和索引级别。
• 系统统计
• 其中包括 CPU 和 I/O 性能和利用率。

Oracle 数据库自动收集所有数据库对象的优化器统计信息，并将这些统计信息作为一项自动维护任务进行维护。您还可以使用该DBMS_STATS软件包手动收集统计信息。这个 PL/SQL 包可以修改、查看、导出、导入和删除统计信息。
Optimizer Statistics Advisor 是内置的诊断软件，可分析您当前收集统计信息的方式、现有统计信息收集作业的有效性以及所收集统计信息的质量。Optimizer Statistics Advisor 维护规则，这些规则体现了基于当前功能集的 Oracle 最佳实践。通过这种方式，顾问始终为统计数据收集提供最新的建议。
也可以看看：

Oracle Database 2 Day + Performance Tuning Guide和Oracle Database SQL Tuning Guide了解如何收集和管理统计信息 收集统计信息

Oracle Database PL/SQL Packages and Types Reference了解DBMS_STATS

优化器提示

提示是 SQL 语句中的注释，充当对优化器的指令。

有时，应用程序设计者拥有的关于特定应用程序数据的信息比优化器可用的信息多，他们可以选择一种更有效的方式来运行 SQL 语句。应用程序设计者可以使用 SQL 语句中的提示来指定语句应该如何运行。以下示例说明了提示的使用。
示例：带有 FIRST_ROWS 提示的 SELECT 执行计划
假设您的交互式应用程序运行一个返回 50 行的查询。此应用程序最初仅获取查询的前 25 行以呈现给最终用户。您希望优化器生成一个尽可能快地获取前 25 条记录的计划，这样用户就不会被迫等待。您可以使用提示将此指令传递给优化器，如以下示例中的SELECT语句和AUTOTRACE输出所示：

SELECT /*+ FIRST_ROWS(25) */ employee_id, department_id
FROM   hr.employees
WHERE  department_id > 50;

------------------------------------------------------------------------
| Id | Operation                    | Name              | Rows | Bytes
------------------------------------------------------------------------
|  0 | SELECT STATEMENT             |                   | 26   | 182
|  1 |  TABLE ACCESS BY INDEX ROWID | EMPLOYEES         | 26   | 182
|* 2 |   INDEX RANGE SCAN           | EMP_DEPARTMENT_IX |      |
------------------------------------------------------------------------

在这个例子中，执行计划显示优化器在employees.department_id列上选择一个索引来查找部门 ID 超过 50 的前 25 行employees。优化器使用从索引中检索到的 rowid 从employees表中检索记录并返回它给客户。第一条记录的检索通常几乎是瞬时的。
示例 无提示的 SELECT 执行计划
假设您执行相同的语句，但没有优化器提示：

SELECT employee_id, department_id
FROM   hr.employees
WHERE  department_id > 50;
 
------------------------------------------------------------------------
| Id | Operation              | Name              | Rows | Bytes | Cos
------------------------------------------------------------------------
|  0 | SELECT STATEMENT       |                   | 50   | 350   |
|* 1 |  VIEW                  | index$_join$_001  | 50   | 350   |
|* 2 |   HASH JOIN            |                   |      |       |
|* 3 |    INDEX RANGE SCAN    | EMP_DEPARTMENT_IX | 50   | 350   |
|  4 |    INDEX FAST FULL SCAN| EMP_EMP_ID_PK     | 50   | 350   |

在这种情况下，执行计划连接两个索引以尽可能快地返回请求的记录。优化器没有像例 9-2那样重复从索引到表，而是选择范围扫描EMP_DEPARTMENT_IX来查找部门 ID 超过 50 的所有行，并将这些行放在哈希表中。然后优化器选择读取EMP_EMP_ID_PK索引。对于此索引中的每一行，它会探测哈希表以查找部门 ID。

在这种情况下，数据库无法将第一行返回给客户端，直到索引范围扫描EMP_DEPARTMENT_IX完成。因此，这个生成的计划将花费更长的时间来返回第一条记录。与例 9-2中的计划不同，它通过索引 rowid 访问表，该计划使用多块 I/O，从而导致大量读取。读取使整个结果集的最后一行能够更快地返回。
也可以看看：

Oracle Database SQL Tuning Guide了解如何使用优化器提示
如何使用hint

sql处理概述 Overview of SQL Processing

本节介绍 Oracle 数据库如何处理 SQL 语句。具体来说，该部分解释了数据库处理 DDL 语句以创建对象、DML 修改数据以及查询以检索数据的方式。

SQL 处理的阶段

SQL 处理的一般阶段是解析、优化、行源生成和执行。根据语句，数据库可能会省略其中一些步骤。

SQL Parsing

SQL 处理的第一阶段是SQL 解析。此阶段涉及将 SQL 语句的各个部分分离为可由其他例程处理的数据结构。

当应用程序发出 SQL 语句时，应用程序对数据库进行解析调用以准备语句以供执行。解析调用打开或创建一个游标，它是特定于会话的私有 SQL 区域的句柄，该区域包含已解析的 SQL 语句和其他处理信息。游标和私有 SQL 区域位于PGA中。

在解析调用期间，数据库执行以下检查：

语法检查

语义检查

共享池检查

前面的检查确定了在语句执行之前可以发现的错误。有些错误无法通过解析捕获。例如，数据库只能在语句执行期间遇到死锁或数据转换错误。

SQL Optimization

查询优化是选择执行 SQL 语句的最有效方法的过程。

数据库根据收集到的有关正在访问的实际数据的统计信息来优化查询。优化器使用行数、数据集的大小和其他因素来生成可能的执行计划，为每个计划分配一个数字成本。数据库使用成本最低的计划。

数据库必须为每个唯一的 DML 语句至少执行一次硬解析，并在此解析期间执行优化。DDL 永远不会被优化，除非它包含一个 DML 组件，例如需要优化的子查询。

SQL Row Source Generation

行源生成器是从优化器接收最佳执行计划并生成迭代计划（称为查询计划）的软件，该计划可供数据库的其余部分使用。

查询计划采用步骤组合的形式。每一步都返回一个行集。该集合中的行要么供下一步使用，要么在最后一步返回给发出 SQL 语句的应用程序。

行源是由执行计划中的一个步骤返回的行集以及可以迭代处理行的控制结构。行源可以是表、视图或联接或分组操作的结果。

SQL Execution

在执行期间，SQL 引擎执行行源生成器生成的树中的每个行源。这是 DML 处理中唯一的强制性步骤。

在执行过程中，如果数据不在内存中，则数据库将数据从磁盘读取到内存中。数据库还取出确保数据完整性所需的任何锁和闩锁，并记录在 SQL 执行期间所做的任何更改。处理 SQL 语句的最后阶段是关闭游标。

如果数据库配置为使用内存中列存储（IM 列存储），那么数据库会在可能的情况下将查询透明地路由到 IM 列存储，否则会路由到磁盘和数据库缓冲区缓存。单个查询还可以使用 IM 列存储、磁盘和缓冲区缓存。例如，一个查询可能连接两个表，其中只有一个缓存在 IM 列存储中。

DML 和 DDL 处理之间的差异

Oracle 数据库处理 DDL 与 DML 不同。

例如，当您创建表时，数据库不会优化该CREATE TABLE语句。相反，Oracle 数据库解析 DDL 语句并执行命令。

与 DDL 相比，大多数 DML 语句都有查询组件。在查询中，游标的执行会将查询生成的行放入结果集中。

数据库可以一次获取一行或分组获取结果集行。在 fetch 中，数据库选择行，如果查询请求，则对行进行排序。每次连续的提取都会检索另一行结果，直到最后一行被提取。

Server-Side Programming: PL/SQL and Java

SQL解释了结构化查询语言 (SQL) 语言以及数据库如何处理 SQL 语句。本章说明存储在数据库中的过程语言/SQL (PL/SQL) 或 Java 程序如何使用 SQL。

服务器端编程简介

在 SQL 等非过程语言中，指定了要操作的数据集，但没有指定要执行的操作或执行它们的方式。

在过程语言程序中，大多数语句的执行依赖于前面或后面的语句以及控制结构，例如循环或条件分支，这些在 SQL 中是不可用的。为了说明过程语言和非过程语言之间的区别，假设以下 SQL 语句查询employees表：

SELECT employee_id, department_id, last_name, salary FROM employees;

前面的语句请求数据，但不对数据应用逻辑。但是，假设您希望应用程序根据薪水和部门绩效来确定数据集中的每个员工是否应该加薪。加薪的一个必要条件是该员工在过去五年内没有收到超过三次加薪。如果要求加薪，则申请必须调整薪水并向经理发送电子邮件；否则，应用程序必须更新报告。
问题是需要条件逻辑和程序流控制的过程数据库应用程序如何使用 SQL。基本的开发方法如下：

使用客户端编程将 SQL 语句嵌入以 C、C++ 或 Java 等过程语言编写的应用程序中

您可以将 SQL 语句放在源代码中，并在编译之前将其提交给预编译器或 Java 翻译器。或者，您可以消除预编译步骤，并使用诸如 Java 数据库连接 (JDBC) 或 Oracle 调用接口 (OCI) 之类的 API 来使应用程序能够与数据库进行交互。

使用服务器端编程开发驻留在数据库中的数据逻辑

应用程序可以显式调用以 PL/SQL（发音为PL sequel）或 Java编写的存储子程序（过程和函数） 。您还可以创建一个触发器，它被命名为程序单元，存储在数据库中并为响应指定的事件而调用。

本章介绍第二种方法。服务器端编程的主要好处是内置在数据库中的功能可以部署在任何地方。数据库而不是应用程序决定了在给定操作系统上执行任务的最佳方式。此外，子程序通过将应用程序处理集中在服务器上来提高可伸缩性，使客户端能够重用代码。由于子程序调用快速高效，一次调用即可启动计算密集型存储子程序，从而减少网络流量。

您可以使用以下语言在 Oracle 数据库中存储数据逻辑：

PL/SQL

PL/SQL 是 Oracle 数据库对 SQL 的过程扩展。PL/SQL 与数据库集成，支持所有 Oracle SQL 语句、函数和数据类型。用数据库 API 编写的应用程序可以调用 PL/SQL 存储的子程序，并将 PL/SQL 代码块发送到数据库执行。

JAVA

Oracle 数据库还支持开发、存储和部署 Java 应用程序。Java 存储的子程序在数据库中运行，并且独立于在中间层运行的程序。Java 存储的子程序使用与 PL/SQL 类似的执行模型与 SQL 交互。

PL/SQL 概述

PL/SQL 提供了一种服务器端的存储过程语言，该语言易于使用、与 SQL 无缝连接、健壮、可移植且安全。您可以使用称为PL/SQL 单元的过程对象访问和操作数据库数据。
PL/SQL 单元一般分为以下几类：

• PL/SQL 子程序是存储在数据库中的 PL/SQL 块，可以从应用程序中按名称调用。创建子程序时，数据库会解析子程序并将其解析后的表示形式存储在数据库中。您可以将子程序声明为过程或函数。
• PL/SQL 匿名块是出现在您的应用程序中且未命名或存储在数据库中的 PL/SQL 块。在许多应用程序中，PL/SQL 块可以出现在 SQL 语句出现的任何地方。
  PL/SQL 编译器和解释器嵌入在 Oracle SQL Developer 中，为开发人员提供了在客户端和服务器上一致且有效的开发模型。此外，PL/SQL 存储过程可以从多个数据库客户端（如 Pro*C、JDBC、ODBC 或 OCI）以及 Oracle Reports 和 Oracle Forms 调用。

PL/SQL Subprograms

PL/SQL 子程序是一个命名的 PL/SQL 块，它允许调用者提供只能输入、只能输出或输入和输出值的参数。

子程序解决特定问题或执行相关任务，并充当模块化、可维护的数据库应用程序的构建块。子程序是PL/SQL 过程或PL/SQL 函数。过程和函数是相同的，只是函数总是向调用者返回单个值，而过程则不然。本章中的术语PL/SQL 过程是指过程或函数。

PL/SQL Packages

PL/SQL 包为应用程序开发人员提供了许多优势。

优点包括：

封装

包使您能够将存储过程、变量、数据类型等封装或分组在一个命名的存储单元中。封装在开发过程中提供了更好的组织，也提供了更大的灵活性。您可以创建规范和引用公共过程，而无需实际创建包体。封装简化了权限管理。授予包的特权使被授权者可以访问包结构。

数据安全

包定义的方法使您能够指定哪些变量、游标和过程是公共的和私有的。公共意味着包的用户可以直接访问它。私有意味着它对包的用户是隐藏的。

例如，一个包可以包含 10 个过程。您可以定义包，以便只有三个过程是公共的，因此可供包的用户执行。其余过程是私有的，只能由包内的过程访问。不要将公共和私有包变量与授予PUBLIC.

更好的性能

当第一次调用包中的过程时，整个包会以小块的形式加载到内存中。此加载在一个操作中完成，与独立过程所需的单独加载相反。当调用相关的封装过程时，不需要磁盘 I/O 来运行内存中的编译代码。

可以替换和重新编译包体而不影响规范。因此，引用包的结构（总是通过规范）的模式对象不需要重新编译，除非包规范也被替换。通过使用包，可以最大限度地减少不必要的重新编译，从而减少对整体数据库性能的影响。
创建 PL/SQL 包
您创建一个包分为两部分：规范和主体。包规范声明了包的所有公共结构，而包体定义了包的所有结构（公共和私有）。

下面的示例显示了创建包规范的语句的一部分，该规范employees_management封装了几个用于管理员工数据库的子程序。包的每个部分都是用不同的语句创建的。

CREATE PACKAGE employees_management AS 
  FUNCTION hire_employees (last_name VARCHAR2, job_id VARCHAR2, manager_id NUMBER, 
    salary NUMBER, commission_pct NUMBER, department_id NUMBER) RETURN NUMBER; 
  PROCEDURE fire_employees(employee_id NUMBER); 
  PROCEDURE salary_raise(employee_id NUMBER, salary_incr NUMBER); 
.
.
.
  no_sal EXCEPTION;
END employees_management;

PL/SQL Anonymous Blocks

PL/SQL 匿名块是未命名的、非持久性的 PL/SQL 单元。

匿名块的典型用途包括：

启动对子程序和包结构的调用

隔离异常处理

通过在其他 PL/SQL 块中嵌套代码来管理控制

匿名块不具有存储子程序的代码重用优势。

PL/SQL Language Constructs

PL/SQL 块可以包括各种不同的 PL/SQL 语言结构。

这些构造包括以下内容：

变量和常量

您可以在过程、函数或包中声明这些构造。您可以在 SQL 或 PL/SQL 语句中使用变量或常量，以便在需要时捕获或提供值。

光标

您可以在过程、函数或包中显式声明游标，以促进 Oracle 数据库数据的面向记录的处理。PL/SQL 引擎也可以隐式声明游标。

例外

PL/SQL 允许您显式处理在处理 PL/SQL 代码期间出现的 内部和用户定义的错误条件（称为异常）。

PL/SQL 可以运行动态 SQL语句，其完整文本直到运行时才知道。动态 SQL 语句存储在字符串中，这些字符串在运行时输入或由程序构建。此技术使您能够创建通用过程。例如，您可以创建一个在运行时才知道名称的表上操作的过程。

PL/SQL Collections and Records

许多编程技术使用集合类型，例如数组、包、列表、嵌套表、集合和树。

为了支持数据库应用程序中的收集技术，PL/SQL 提供了数据类型TABLE和VARRAY. 这些类型使您能够声明关联数组、嵌套表和可变大小数组。

收藏品
PL/SQL 集合是一组有序的元素，它们都是相同类型的。

每个元素都有一个唯一的下标，它决定了它在集合中的位置。要创建一个集合，首先要定义一个集合类型，然后声明一个该类型的变量。

集合的工作方式类似于大多数第三代编程语言中的数组。此外，集合可以作为参数传递。因此，您可以使用它们将数据列移入和移出数据库表，或在客户端应用程序和存储的子程序之间移动。

记录
PL/SQL 记录是一个复合变量，可以存储不同类型的数据值，类似于 C、C++ 或 Java 中的结构类型。记录对于保存表行中的数据或表行中的某些列很有用。

假设您有有关员工的数据，例如姓名、薪水和雇用日期。这些项目在类型上不同，但在逻辑上相关。包含每个项目的字段的记录使您可以将数据视为逻辑单元。

您可以使用该%ROWTYPE属性来声明表示表行或从游标中获取的行的记录。使用用户定义的记录，您可以声明自己的字段。

How PL/SQL Runs

PL/SQL 支持解释执行和本地执行。

在解释执行中，PL/SQL 源代码被编译成所谓的字节码表示。作为 Oracle 数据库的一部分实现的便携式虚拟计算机运行此字节码。

本机执行在计算密集型单元上提供最佳性能。在这种情况下，PL/SQL 单元的源代码直接编译为给定平台的目标代码。此目标代码链接到 Oracle 数据库。

PL/SQL 引擎定义、编译和运行 PL/SQL 单元。该引擎是许多 Oracle 产品（包括 Oracle 数据库）的特殊组件。虽然许多 Oracle 产品都有 PL/SQL 组件，但本主题专门介绍了可以存储在 Oracle 数据库中并使用 Oracle 数据库 PL/SQL 引擎进行处理的 PL/SQL 单元。每个 Oracle 工具的文档都描述了它的 PL/SQL 功能。

下图说明了 Oracle 数据库中包含的 PL/SQL 引擎。

PL/SQL 单元存储在数据库中。当应用程序调用存储过程时，数据库将编译后的 PL/SQL 单元加载到系统全局区域 (SGA)中的共享池中。PL/SQL 和 SQL 语句执行器一起工作来处理过程中的语句。

您可以从另一个 PL/SQL 块调用存储过程，该块可以是匿名块或另一个存储过程。例如，您可以从 Oracle Forms 调用存储过程。

在 Oracle 数据库上执行的 PL/SQL 过程可以调用用 C 编程语言编写并存储在共享库中的外部过程或函数。C 例程在与 Oracle 数据库不同的地址空间中运行。

Oracle 数据库中的 Java 概述

Java 已成为首选的面向对象编程语言。
Java 包括以下功能：

• Java 虚拟机 (JVM)，它提供了平台独立性的基础
• 自动存储管理技术，例如垃圾收集
• 从 C 中借用并强制执行强类型的语言语法
  该数据库为 Java 程序提供了一个支持复杂查询和多个数据视图的动态数据处理引擎。客户端请求被组装为数据查询以便立即处理。查询结果是动态生成的。

Java 和 Oracle 数据库的结合可帮助您创建基于组件、以网络为中心的应用程序，这些应用程序可以随着业务需求的变化而轻松更新。此外，您可以将应用程序和数据存储从桌面移动到智能网络和以网络为中心的服务器上。更重要的是，您可以从任何客户端设备访问这些应用程序和数据存储。

下图显示了传统的两层客户端/服务器配置，其中客户端调用 Java 存储过程的方式与调用 PL/SQL 子程序的方式相同。

Overview of the Java Virtual Machine (JVM)

Oracle JVM是一个标准的、与 Java 兼容的环境，可以运行任何纯 Java 应用程序。它与 JLS 和 JVM 规范兼容。

Oracle JVM 支持标准的 Java 二进制格式和 API。此外，Oracle 数据库遵循标准 Java 语言语义，包括运行时的动态类加载。

下图说明了 Oracle Java 应用程序如何驻留在 Java 核心类库之上，这些类库驻留在 Oracle JVM 之上。因为 Oracle Java 支持系统位于数据库内部，所以 JVM 与数据库库交互，而不是直接与操作系统交互。

与其他 Java 环境不同，Oracle JVM 嵌入在 Oracle 数据库中。Oracle JVM 和典型的客户端 JVM 之间存在一些重要差异。例如，在标准 Java 环境中，通过在命令行中发出以下命令，通过解释器运行 Java 应用程序，其中classname是 JVM 首先解释的类的名称：

java classname

Java Programming Environment

Oracle 为企业应用程序开发人员提供用于创建、部署和管理 Java 应用程序的端到端 Java 解决方案。

该解决方案由客户端和服务器端编程接口、支持 Java 开发的工具以及与 Oracle 数据库集成的 Java 虚拟机组成。所有这些产品都与 Java 标准兼容。

Java 编程环境包含以下附加功能：

• Java 存储过程作为 PL/SQL 的 Java 等价物和伴侣。Java 存储过程与 PL/SQL 紧密集成。您可以从 PL/SQL 包调用 Java 存储过程，也可以从 Java 存储过程调用过程。
• 用于访问 SQL 数据的 JDBC 和 SQLJ 编程接口。
• 帮助开发、加载和管理类的工具和脚本。

触发器概述

数据库触发器是一个编译的存储程序单元，用 PL/SQL 或 Java 编写，Oracle 数据库在某些情况下会自动调用（“触发”）。

只要发生以下操作之一，就会触发触发器：

任何用户发布的针对特定表或视图的 DML语句

DML 语句修改模式对象中的数据。例如，插入和删除行是 DML 操作。

由特定用户或任何用户发出的 DDL语句

DDL 语句定义模式对象。例如，创建表和添加列是 DDL 操作。

数据库事件

用户登录或注销、错误以及数据库启动或关闭都是可以调用触发器的事件。

触发器是类似于子程序但调用方式不同的模式对象。子程序由用户、应用程序或触发器显式运行。当触发事件发生时，数据库会隐式调用触发器。

Advantages of Triggers

正确使用触发器使您能够构建和部署更健壮且更有效地使用数据库的应用程序。

• 您可以使用触发器来：
• 自动生成派生列值
• 防止无效交易
• 提供审计和事件记录
• 记录有关表访问的信息
  您可以使用触发器来强制执行所有客户端应用程序通用的低级业务规则。例如，几个应用程序可能会访问该employees表。如果这个表上的触发器保证了插入数据的格式，那么这个业务逻辑就不需要在每个客户端都复现。因为应用程序无法绕过触发器，所以会自动使用触发器中的业务逻辑。

您可以使用触发器和完整性约束来定义和实施任何类型的完整性规则。但是，Oracle 强烈建议您只使用触发器来强制执行无法使用完整性约束定义的复杂业务规则。

过度使用触发器会导致复杂的相互依赖关系，这在大型应用程序中可能难以维护。例如，当调用触发器时，其触发器操作中的 SQL 语句可能会触发其他触发器，从而导致级联触发器产生意想不到的效果。

Types of Triggers

触发器可以根据它们的调用方式和它们执行的操作类型进行分类。

Oracle 数据库支持以下类型的触发器：

• 行触发器

每次表受到触发语句的影响时，都会触发行触发器。例如，如果一条语句更新了多行，则行触发器会为受UPDATE. 如果触发语句不影响任何行，则不会运行行触发器。如果触发器操作中的代码取决于触发语句提供的数据或受影响的行，则行触发器很有用。

• 语句触发器

语句触发器代表触发语句触发一次，无论触发语句影响的行数如何。例如，如果一条语句从表中删除 100 行，则语句级DELETE触发器仅触发一次。如果触发器操作中的代码不依赖于触发语句提供的数据或受影响的行，则语句触发器很有用。

• INSTEAD OF触发器

Oracle 数据库触发 INSTEAD OF 触发器，而不是执行触发语句。这些触发器对于透明地修改无法通过 DML 语句直接修改的视图很有用。

• 事件触发器

您可以使用触发器向订阅者发布有关数据库事件的信息。事件触发器分为以下几类：

系统事件触发器可能由数据库实例启动和关闭或错误消息等事件引起 。

由于与用户登录和注销、DDL 语句和 DML 语句相关的 事件而触发用户事件触发器。

Timing for Triggers

You can define the trigger timing—whether the trigger action is to be run before or after the triggering statement.

A simple trigger is a single trigger on a table that enables you to specify actions for exactly one of the following timing points:

Before the firing statement

Before each row affected by the firing statement

After each row affected by the firing statement

After the firing statement

For statement and row triggers, a BEFORE trigger can enhance security and enable business rules before making changes to the database. The AFTER trigger is ideal for logging actions.

A compound trigger can fire at multiple timing points. Compound triggers help program an approach in which the actions that you implement for various timing points share common data.

Creation of Triggers

The CREATE TRIGGER statement creates or replaces a database trigger.

A PL/SQL trigger has the following general syntactic form:

CREATE TRIGGER trigger_name
  triggering_statement
  [trigger_restriction]
BEGIN
 triggered_action;
END;

A PL/SQL trigger has the following basic components:

• Trigger name

The name must be unique among other trigger names in the same schema. For example, the name may be part_reorder_trigger.

• The trigger event or statement

A triggering event or statement is the SQL statement, database event, or user event that causes a trigger to be invoked. For example, a user updates a table.

• Trigger restriction

A trigger restriction specifies a Boolean expression that must be true for the trigger to fire. For example, the trigger is not invoked unless the number of available parts is less than a present reorder amount.

• Triggered action

A triggered action is the procedure that contains the SQL statements and code to be run when a triggering statement is issued and the trigger restriction evaluates to true. For example, a user inserts a row into a pending orders table.
Example: CREATE TRIGGER Statement
This example creates a trigger that fires when an INSERT, UPDATE, or DELETE statement executes on a line items table.

Suppose that you create the orders and lineitems tables with the following statements. The orders table contains a row for each unique order, whereas the lineitems table contains a row for each item in an order.

CREATE TABLE orders
( order_id NUMBER PRIMARY KEY,
  /* other attributes */
  line_items_count NUMBER DEFAULT 0 );

CREATE TABLE lineitems
( order_id REFERENCES orders,
  seq_no   NUMBER,
  /* other attributes */
  CONSTRAINT lineitems PRIMARY KEY(order_id,seq_no) );

CREATE OR REPLACE TRIGGER lineitems_trigger
  AFTER INSERT OR UPDATE OR DELETE ON lineitems 
  FOR EACH ROW
BEGIN
  IF (INSERTING OR UPDATING)
  THEN
    UPDATE orders SET line_items_count = NVL(line_items_count,0)+1
    WHERE order_id = :new.order_id;
  END IF;
  IF (DELETING OR UPDATING)
  THEN
    UPDATE orders SET line_items_count = NVL(line_items_count,0)-1
    WHERE order_id = :old.order_id;
  END IF;
END;
/

Execution of Triggers

Oracle 数据库使用与子程序执行相同的步骤在内部执行触发器。

唯一细微的区别是，如果用户帐户有权运行触发语句，则它有权触发触发器。除了这个例外，数据库验证和运行触发器的方式与存储的子程序相同。

Storage of Triggers

Oracle 数据库在数据库模式中以编译形式存储 PL/SQL 触发器，就像 PL/SQL 存储过程一样。

当一条CREATE TRIGGER语句提交时，已编译的 PL/SQL 代码将存储在数据库中。共享池删除了 PL/SQL 触发器的源代码。

下图显示了一个带有隐式调用 PL/SQL 触发器的 SQL 语句的数据库应用程序。触发器与其关联的表分开存储。

Java triggers are stored in the same manner as PL/SQL triggers. However, a Java trigger references Java code that was separately compiled with a CALL statement. Thus, creating a Java trigger involves creating Java code and creating the trigger that references this Java code.