24 SQL, PL/SQL, and Java

001 This chapter provides an overview of SQL, PL/SQL, and Java.
　		 本章概要地介绍 SQL，PL/SQL，及 Java。
　
002 This chapter contains the following topics: 本章包含以下主题：
003

See Also:

另见：
004

Overview of SQL

24.1 SQL 概述
005 SQL is a database access, nonprocedural language. Users describe in SQL what they want done, and the SQL language compiler automatically generates a procedure to navigate the database and perform the desired task.
　		 SQL 是一种用于数据库访问的非过程化语言。用户通过 SQL 描述其目标，之后 SQL 语言编译器自动地生成执行过程，控制数据库执行用户所期望的操作。
　
006 IBM Research developed and defined SQL, and ANSI/ISO has refined SQL as the standard language for relational database management systems. The minimal conformance level for SQL-99 is known as Core. Core SQL-99 is a superset of SQL-92 Entry Level specification. Oracle Database is broadly compatible with the SQL-99 Core specification.
　		 IBM 的研究机构开发并定义了 SQL，之后 ANSI/ISO 选择了改进后的 SQL 作为关系型数据库管理系统的标准语言。SQL-99 标准的最小子集被称为核心[Core]。而 SQL-99 核心是 SQL-92 入门级规范的超集。Oracle 数据库与 SQL-99 核心规范广泛地兼容[broadly compatible]。

　
007 Oracle SQL includes many extensions to the ANSI/ISO standard SQL language, and Oracle tools and applications provide additional statements. The Oracle tools SQL*Plus and Oracle Enterprise Manager let you run any ANSI/ISO standard SQL statement against an Oracle database, as well as additional statements or functions that are available for those tools.
　		 Oracle SQL 包括许多对 ANSI/ISO 标准 SQL 语言的扩展，Oracle 工具及应用程序也增加了额外的语句。用户可以使用 Oracle 工具 SQL*Plus 或 Oracle 企业管理器[Oracle Enterprise Manager]对 Oracle 数据库执行任意的 ANSI/ISO 标准 SQL 语句，以及这些工具提供的额外的语句或函数。
　
008 Although some Oracle tools and applications simplify or mask SQL use, all database operations are performed using SQL. Any other data access method circumvents the security built into Oracle and potentially compromise data security and integrity.
　		 所有的数据库操作都是通过 SQL 提交给数据库的，但 Oracle 工具及应用程序能够简化或隐藏实际的 SQL。SQL 之外的任何数据访问方式均会绕过 Oracle 内置的安全特性，有可能对数据的安全性及完整性造成破坏。
　
009

See Also:

另见：
010

SQL Statements

24.1.1 SQL 语句
011 All operations performed on the information in an Oracle database are run using SQL statements. A statement consists partially of SQL reserved words, which have special meaning in SQL and cannot be used for any other purpose. For example, SELECT and UPDATE are reserved words and cannot be used as table names.
　		 对 Oracle 数据库内存储的信息所执行的所有操作都是通过 SQL 语句[statement]执行的。语句中包含大量 SQL 保留字[reserved word]，保留字在 SQL 中有特殊的含义，用户不能将其用作其他用途。例如，SELECT 与 UPDATE 都是保留字，不能用做数据表名。
　
012 A SQL statement is a computer program or instruction. The statement must be the equivalent of a complete SQL sentence, as in:
　		 一条 SQL 语句相当于一条计算机程序或指令。因此 SQL 语句必须包含一段完整的 SQL 语法，例如：
　
013

SELECT last_name, department_id FROM employees;
SELECT last_name, department_id FROM employees;
014 Only a complete SQL statement can be run. A fragment such as the following generates an error indicating that more text is required before a SQL statement can run:
　		 只有完整的 SQL 语句才能被执行。执行下面所示的 SQL 语句片断将产生错误，表明此 SQL 语句必须书写完整才能执行：
　
015

SELECT last_name
SELECT last_name
016 Oracle SQL statements are divided into the following categories: Oracle SQL 语句可以分为以下几类：
017

See Also:

Chapter 22, "Triggers" for more information about using SQL statements in PL/SQL program units
另见：

第 22 章，“触发器”了解如何在 PL/SQL 程序结构中使用 SQL 语句
018

Data Manipulation Language Statements

24.1.1.1 数据操作语言语句
019 Data manipulation language (DML) statements query or manipulate data in existing schema objects. They enable you to:
  • Retrieve data from one or more tables or views (SELECT); fetches can be scrollable (see "Scrollable Cursors")
  • Add new rows of data into a table or view (INSERT)
  • Change column values in existing rows of a table or view (UPDATE)
  • Update or insert rows conditionally into a table or view (MERGE)
  • Remove rows from tables or views (DELETE)
  • See the execution plan for a SQL statement (EXPLAIN PLAN)
  • Lock a table or view, temporarily limiting other users' access (LOCK TABLE)
 数据操作语言[Data manipulation language，DML]语句的作用是查询或操作已有方案对象内的数据。用户利用 DML 语句可以完成以下工作：
  • 从一个或多个表或视图中查询数据（SELECT）；获取操作[fetch]是可滚动 的[scrollable]（见“可滚动游标”）
  • 向表或视图中加入新数据行（INSERT）
  • 修改表或视图中已有数据行的列值（UPDATE）
  • 根据判断条件为表及视图插入或更新数据行（MERGE）
  • 从表或视图中删除数据行（DELETE）
  • 查询 SQL 语句的执行计划[execution plan]（EXPLAIN PLAN）
  • 对表或视图加锁[lock]，临时地限制其他用户访问此对象（LOCK TABLE）
020 DML statements are the most frequently used SQL statements. Some examples of DML statements are:
　		 DML 语句是使用频率最高的 SQL 语句。以下是一些 DML 语句的示例：
　
021

SELECT last_name, manager_id, commission_pct + salary FROM employees;
SELECT last_name, manager_id, commission_pct + salary FROM employees;
022

INSERT INTO employees VALUES
(1234, 'DAVIS', 'SALESMAN', 7698, '14-FEB-1988', 1600, 500, 30);

INSERT INTO employees VALUES
(1234, 'DAVIS', 'SALESMAN', 7698, '14-FEB-1988', 1600, 500, 30);
023

DELETE FROM employees WHERE last_name IN ('WARD','JONES');
DELETE FROM employees WHERE last_name IN ('WARD','JONES');
024

DML Error Logging

24.1.1.1.1 DML 错误日志
025 When a DML statement encounters an error, the statement can continue processing while the error code and the associated error message text is logged to an error logging table. This is particularly helpful to long-running, bulk DML statements. After the DML operation completes, you can check the error logging table to correct rows with errors.
　		 如果 DML 语句在执行过程中出现错误，语句可以继续执行，同时将错误代码及错误消息内容记录到一个错误日志表[error logging table]中。此特性适合与长时间运行且处理大量数据的 DML 语句配合使用。当 DML 操作结束后，用户可以检查错误日志表以修正发生错误的数据行。
　
026 New syntax is added to the DML statements to provide the name of the error logging table, a statement tag, and a reject limit. The reject limit determines whether the statement should be aborted. For parallel DML operations, the reject limit is applied for each slave. The only values for the reject limit that are precisely enforced on parallel operations are zero and unlimited.
　		 现在 DML 语句中加入了新的语法，用户可以设定错误日志表名，语句标签[statement tag]，及放弃执行条件[reject limit]。放弃执行条件用于判断是否应该中断语句的执行。对于并行 DML 语句，放弃执行条件对每个子进程[slave]均适用。对于并行操作，可以准确设置的放弃执行条件值只有 0 和无限制。
　
027 With data conversion errors, Oracle tries to provide a meaningful value to log for the column. For example, it could log the value of the first operand to the conversion operator that failed. If a value cannot be derived, then NULL is logged for the column.
　		 当发生数据类型转换[data conversion]错误时，Oracle 将找出一个有含义的列值记录到日志中。例如，Oracle 有可能记录出现错误的类型转换操作符的第一个操作数[operand]。如果无法获得有意义的数据值，Oracle 将记录 NULL。
　
028

See Also:

另见：
029

Data Definition Language Statements

24.1.1.2 数据定义语言语句
030 Data definition language (DDL) statements define, alter the structure of, and drop schema objects. DDL statements enable you to:
  • Create, alter, and drop schema objects and other database structures, including the database itself and database users (CREATE, ALTER, DROP)
  • Change the names of schema objects (RENAME)
  • Delete all the data in schema objects without removing the objects' structure (TRUNCATE)
  • Grant and revoke privileges and roles (GRANT, REVOKE)
  • Turn auditing options on and off (AUDIT, NOAUDIT)
  • Add a comment to the data dictionary (COMMENT)
 数据定义语言[Data definition language，DDL]语句的作用是定义或修改方案对象[schema object]的结构，以及移除方案对象。用户利用 DDL 语句可以完成以下工作：
  • 创建，修改，移除方案对象及其他数据库结构，包括数据库自身及数据库用户（CREATE，ALTER，DROP）
  • 修改方案对象名称（RENAME）
  • 删除方案对象的所有数据，但不移除对象结构（TRUNCATE）
  • 授予或收回权限及角色（GRANT，REVOKE）
  • 打开或关闭审计选项（AUDIT，NOAUDIT）
  • 向数据字典中添加注释（COMMENT）
031 DDL statements implicitly commit the preceding and start a new transaction. Some examples of DDL statements are:
　		 DDL 语句将隐式地提交之前的操作并开始一个新事务。以下是一些 DDL 语句的示例：
　
032

CREATE TABLE plants
(COMMON_NAME VARCHAR2 (15), LATIN_NAME VARCHAR2 (40));
CREATE TABLE plants
(COMMON_NAME VARCHAR2 (15), LATIN_NAME VARCHAR2 (40));
033

DROP TABLE plants;
DROP TABLE plants;
034

GRANT SELECT ON employees TO scott;
GRANT SELECT ON employees TO scott;
035

REVOKE DELETE ON employees FROM scott;
REVOKE DELETE ON employees FROM scott;
036

See Also:

Chapter 20, "Database Security"
另见：

第 20 章，“数据库安全”
037

Transaction Control Statements

24.1.1.3 事务控制语句
038 Transaction control statements manage the changes made by DML statements and group DML statements into transactions. They enable you to:
  • Make a transaction's changes permanent (COMMIT)
  • Undo the changes in a transaction, either since the transaction started or since a savepoint (ROLLBACK)
  • Set a point to which you can roll back (SAVEPOINT)
  • Establish properties for a transaction (SET TRANSACTION)
 事务控制语句[transaction control statement]的作用是管理 DML 语句对数据的修改，以及将逻辑上相关的 DML 语句组织为事务。用户利用事务控制语句可以完成以下工作：
  • 将事务对数据的修改永久地保存到数据库（COMMIT）
  • 还原事务对数据的修改，可还原到事务开始处或任意保存点[savepoint]（ROLLBACK）
  • 设置保存点以标识回滚位置（SAVEPOINT）
  • 设置事务的属性（SET TRANSACTION）
039

Session Control Statements

24.1.1.4 会话控制语句
040 Session control statements manage the properties of a particular user's session. For example, they enable you to:
  • Alter the current session by performing a specialized function, such as enabling and disabling the SQL trace facility (ALTER SESSION)
  • Enable and disable roles (groups of privileges) for the current session (SET ROLE)
 会话控制语句[session control statement]用于管理用户会话的属性。用户利用会话控制语句可以完成以下工作：
  • 执行特定操作，修改当前会话，例如启用或禁用 SQL 跟踪功能[SQL trace facility]（ALTER SESSION）
  • 为当前会话启用或禁用角色[role]（即一组权限的集合）（SET ROLE）
041

System Control Statements

24.1.1.5 系统控制语句
042 System control statements change the properties of the Oracle database server instance. The only system control statement is ALTER SYSTEM. It enables you to change settings (such as the minimum number of shared servers), kill a session, and perform other tasks.
　		 系统控制语句[system control statement]用于修改 Oracle 数据库实例的属性。ALTER SYSTEM 是唯一的系统控制语句。用户可以使用此语句修改实例设置（例如共享服务进程的最小数量），终止进程[kill session]，或执行其他操作。
　
043

Embedded SQL Statements

24.1.1.6 嵌入 SQL 语句
044 Embedded SQL statements incorporate DDL, DML, and transaction control statements within a procedural language program. They are used with the Oracle precompilers. Embedded SQL statements enable you to:
  • Define, allocate, and release cursors (DECLARE CURSOR, OPEN, CLOSE)
  • Specify a database and connect to Oracle (DECLARE DATABASE, CONNECT)
  • Assign variable names (DECLARE STATEMENT)
  • Initialize descriptors (DESCRIBE)
  • Specify how error and warning conditions are handled (WHENEVER)
  • Parse and run SQL statements (PREPARE, EXECUTE, EXECUTE IMMEDIATE)
  • Retrieve data from the database (FETCH)
 用户可以使用嵌入 SQL 语句[embedded SQL statement]将 DDL，DML，及事务控制语句加入到以过程化语言编写的程序中。Oracle 预编译器[precompiler]能够处理这样的代码。用户利用嵌入 SQL 语句可以完成以下工作：
  • 定义，分配，及释放游标[cursor]（DECLARE CURSOR，OPEN，CLOSE）
  • 选择一个 Oracle 数据库并进行连接（DECLARE DATABASE，CONNECT）
  • 分配变量名（DECLARE STATEMENT）
  • 初始化描述符[descriptor]（DESCRIBE）
  • 设定如何处理错误及警告（WHENEVER）
  • 解析并执行 SQL 语句（PREPARE，EXECUTE，EXECUTE IMMEDIATE）
  • 从数据库中取回数据（FETCH）
045

Cursors

24.1.2 游标
046 A cursor is a handle or name for a private SQL area—an area in memory in which a parsed statement and other information for processing the statement are kept.
　		 游标[cursor]是私有 SQL 区[private SQL area]的名称（或称为句柄），私有 SQL 区是一种内存结构，用于存储被解析的语句，以及处理语句所需的其他信息。
　
047 Although most Oracle users rely on the automatic cursor handling of the Oracle utilities, the programmatic interfaces offer application designers more control over cursors. In application development, a cursor is a named resource available to a program and can be used specifically to parse SQL statements embedded within the application.
　		 大多数 Oracle 用户的操作只需依靠 Oracle 工具提供的自动游标处理功能就可以实现，Oracle 也提供了编程接口供应用程序开发者进一步控制游标。在应用程序开发中，游标是一种命名的可供程序使用的资源，可用于解析嵌入到应用程序内的 SQL 语句。
　
048 Each user session can open multiple cursors up to the limit set by the initialization parameter OPEN_CURSORS. However, applications should close unneeded cursors to conserve system memory. If a cursor cannot be opened due to a limit on the number of cursors, then the database administrator can alter the OPEN_CURSORS initialization parameter.
　		 每个用户会话都能够打开多个游标，上限由 OPEN_CURSORS 初始化参数决定。应用程序应该负责关闭不再使用的游标以节约系统内存。如果因为游标数量达到上限而无法打开新游标，数据库管理员可以修改 OPEN_CURSORS 初始化参数。
　
049 Some statements (primarily DDL statements) require Oracle to implicitly issue recursive SQL statements, which also require recursive cursors. For example, a CREATE TABLE statement causes many updates to various data dictionary tables to record the new table and columns. Recursive calls are made for those recursive cursors; one cursor can run several recursive calls. These recursive cursors also use shared SQL areas.
　		 有些语句（主要是 DDL 语句）会导致 Oracle 隐式地提交递归 SQL 语句[recursive SQL statement]，进而导致递归游标[recursive cursor]。例如，执行 CREATE TABLE 语句时有可能更新多个数据字典表以记录新表及其各列的定义。在处理递归游标时需要进行递归调用[recursive call]；一个游标可以同时执行多个递归调用。递归游标也使用私有 SQL 区存储其信息。
　
050

Scrollable Cursors

24.1.2.1 可滚动游标
051 Execution of a cursor puts the results of the query into a set of rows called the result set, which can be fetched sequentially or nonsequentially. Scrollable cursors are cursors in which fetches and DML operations do not need to be forward sequential only. Interfaces exist to fetch previously fetched rows, to fetch the nth row in the result set, and to fetch the nth row from the current position in the result set.
　		 执行游标时，查询结果将被放入一个数据行集内，这个数据行集被称为结果集[result set]，结果集可以被顺序[sequentially]或非顺序[nonsequentially]地获取[fetch]。如果使用可滚动游标[scrollable cursor]，则获取数据或执行 DML 操作时就不一定按照向前的顺序进行。用户可以通过 Oracle 提供的接口获取之前已经获取过的数据行，获取结果集内位于第 n 位的数据行，或获取结果集内从游标当前位置起第 n 位的数据行。
　
052

See Also:

Oracle Call Interface Programmer's Guide for more information about using scrollable cursors in OCI
另见：

Oracle Call Interface Programmer's Guide 了解如何在 OCI 中使用可滚动游标
053

Shared SQL

24.1.3 共享 SQL
054 Oracle automatically notices when applications send similar SQL statements to the database. The SQL area used to process the first occurrence of the statement is shared—that is, used for processing subsequent occurrences of that same statement. Therefore, only one shared SQL area exists for a unique statement. Because shared SQL areas are shared memory areas, any Oracle process can use a shared SQL area. The sharing of SQL areas reduces memory use on the database server, thereby increasing system throughput.
　		 Oracle 能够自动地侦测到应用程序向数据库提交相同的相同的 SQL 语句。用于处理第一次出现的语句的 SQL 区[SQL area]将被共享，之后提交的相同 SQL 语句也使用此 SQL 区。因此对于完全相同的 SQL 语句来说，系统中只存在一个共享 SQL 区[shared SQL area]。共享 SQL 区是一种共享的内存空间，任何 Oracle 进程都可以使用此区域。将 SQL 区共享能够减少数据库服务器的内存使用，提升系统的处理能力。
　
055 In evaluating whether statements are similar or identical, Oracle considers SQL statements issued directly by users and applications as well as recursive SQL statements issued internally by a DDL statement.
　		 在评估新提交语句与已创建 SQL 区的语句是否相同时，Oracle 既考虑由用户及应用程序直接提交的 SQL 语句，同时也考虑由 DDL 语句隐式提交的递归 SQL 语句[recursive SQL statement]。
　
056

See Also:

Oracle Database Application Developer's Guide - Fundamentals and Oracle Database Performance Tuning Guide for more information about shared SQL
另见：

Oracle Database Application Developer's Guide - Fundamentals 及 Oracle Database Performance Tuning Guide 了解关于共享 SQL 的更多信息
057

Parsing

24.1.4 解析
058 Parsing is one stage in the processing of a SQL statement. When an application issues a SQL statement, the application makes a parse call to Oracle. During the parse call, Oracle:
  • Checks the statement for syntactic and semantic validity
  • Determines whether the process issuing the statement has privileges to run it
  • Allocates a private SQL area for the statement
 解析[parsing]是处理 SQL 语句过程中的一个环节。当应用程序提交 SQL 语句时，意味着向 Oracle 发起了一个解析调用[parse call]。Oracle 的解析调用完成如下工作：
  • 检查语句语法[syntactic]及语义[semantic]上的正确性
  • 检查提交语句的进程是否有权限执行此语句
  • 为语句分配一个私有 SQL 区[private SQL area]
059 Oracle also determines whether there is an existing shared SQL area containing the parsed representation of the statement in the library cache. If so, the user process uses this parsed representation and runs the statement immediately. If not, Oracle generates the parsed representation of the statement, and the user process allocates a shared SQL area for the statement in the library cache and stores its parsed representation there.
　		 Oracle 还需判断库缓存[library cache]中是否已经存在包含经过解析的与新提交语句相同语句的共享 SQL 区[shared SQL area]。如果存在，则用户进程[user process]使用解析结果立即运行语句。如果不存在，Oracle 将生成语句解析结果，用户进程在库缓存中为语句分配共享 SQL 区存储解析结果。
　
060 Note the difference between an application making a parse call for a SQL statement and Oracle actually parsing the statement. A parse call by the application associates a SQL statement with a private SQL area. After a statement has been associated with a private SQL area, it can be run repeatedly without your application making a parse call. A parse operation by Oracle allocates a shared SQL area for a SQL statement. Once a shared SQL area has been allocated for a statement, it can be run repeatedly without being reparsed.
　		 应用程序为语句提交解析调用与 Oracle 对语句进行解析是有区别的。应用程序提交解析调用时将 SQL 语句与私用 SQL 区关联。当 SQL 语句与私用 SQL 区关联后，此 SQL 语句就可以重复运行而无需应用程序再次提交解析调用。而 Oracle 进行解析操作时将为 SQL 语句分配共享 SQL 区。当语句已获得共享 SQL 区后，就可以重复运行而无需再次解析。
　
061 Both parse calls and parsing can be expensive relative to execution, so perform them as seldom as possible.
　		 与语句执行[execution]相比，解析调用与解析操作是系统开销较高的操作，所以应尽可能地减少此类操作。
　
062

See Also:

"Overview of PL/SQL"
另见：

“PL/SQL 概述”
063

SQL Processing

24.1.5 SQL 处理过程
064 This section introduces the basics of SQL processing. Topics include: 本节介绍 SQL 处理过程。具体内容包括：
065

SQL Statement Execution

24.1.5.1 SQL 语句执行
066 Figure 24-1 outlines the stages commonly used to process and run a SQL statement. In some cases, Oracle can run these stages in a slightly different order. For example, the DEFINE stage could occur just before the FETCH stage, depending on how you wrote your code.
　		 图 24-1 显示了处理及运行 SQL 语句的各个步骤。 在某些情况下 Oracle 执行这些步骤的顺序可能略有不同。例如，由于用户代码编写方式不同，DEFINE 步骤可能会出现在 FETCH 步骤之前。
　
067 For many Oracle tools, several of the stages are performed automatically. Most users need not be concerned with or aware of this level of detail. However, this information could be useful when writing Oracle applications.
　		 对于许多 Oracle 工具，图中有些步骤是自动执行的。绝大多数数据库用户不必了解这些细节。但是，应用开发者在工作中应该参考这些信息。
　
068 Figure 24-1 The Stages in Processing a SQL Statement
　		 图 24-1 处理 SQL 语句的步骤
　
069


　


　
070

Figure 24-1 is a flow chart depicting the paths in processing a SQL statement. It shows the path from OPEN through PARSE, EXECUTE, PARALLELIZE, and CLOSE. It also shows the optional and iterative DESCRIBE and DEFINE loops, BIND loop, and FETCH loop. Before CLOSE, the process can repeat by going back to the bind decision or all the way back to PARSE.
图 24-1 是表示 SQL 语句处理过程路径的流程图。图中的主线自打开[OPEN]起，经过解析[PARSE]， 执行[EXECUTE]，并行化[PARALLELIZE]，到关闭[CLOSE]止。图中还显示了可选的循环路径，包括描述[DESCRIBE]循环， 定义[DEFINE]循环，绑定[BIND]循环，及获取[FETCH]循环。在关闭之前，流程可以回到绑定判断[bind decision]或解析步骤重复进行。
071

DML Statement Processing

24.1.5.2 DML 语句处理过程
072 This section provides an example of what happens during the execution of a SQL statement in each stage of DML statement processing.
　		 本节以一个 DML 语句的执行过程为例说明 Oracle 在 SQL 语句执行过程各步骤所做的工作。
　
073 Assume that you are using a Pro*C program to increase the salary for all employees in a department. The program you are using has connected to Oracle and you are connected to the proper schema to update the employees table. You can embed the following SQL statement in your program:
　		 假设用户使用 Pro*C 程序增加属于某一部门的所有员工的薪水。用户使用的程序已经连接到 Oracle 数据库并选择了相应的方案[schema]来更新 employees 表。用户可以将以下 SQL 语句嵌入程序：
　
074

EXEC SQL UPDATE employees SET salary = 1.10 * salary
WHERE department_id = :department_id;
EXEC SQL UPDATE employees SET salary = 1.10 * salary
WHERE department_id = :department_id;
075 Department_id is a program variable containing a value for department number. When the SQL statement is run, the value of department_id is used, as provided by the application program.
　		 Department_id 是一个程序变量，其中包含部门编码值。当 SQL 语句运行时，应用程序应提供 department_id 值供 SQL 语句使用。
　
076 The following stages are necessary for each type of statement processing: 各类 SQL 语句处理过程均包含以下步骤：
077 Optionally, you can include another stage: 此外，用户可以选择以下步骤：
078 Queries (SELECTs) require several additional stages, as shown in Figure 24-1: 处理查询（SELECTs）时还需要执行以下步骤，如 图 24-1 所示：
079

See Also:

"Query Processing"
另见：

“查询处理过程”
080

Stage 1: Create a Cursor

24.1.5.2.1 第一步：创建游标
081 A program interface call creates a cursor. The cursor is created independent of any SQL statement: it is created in expectation of any SQL statement. In most applications, cursor creation is automatic. However, in precompiler programs, cursor creation can either occur implicitly or be explicitly declared.
　		 应用程序可以通过调用接口创建游标。游标的创建与 SQL 语句是相互独立的：任何类型的 SQL 语句执行时都需要创建游标。在大多数应用程序中游标是自动创建的。但在预编译程序[precompiler program]中，游标也可以被显式地声明。
　
082

Stage 2: Parse the Statement

24.1.5.2.2 第二步：解析语句
083 During parsing, the SQL statement is passed from the user process to Oracle, and a parsed representation of the SQL statement is loaded into a shared SQL area. Many errors can be caught during this stage of statement processing.
　		 在解析过程中，用户进程[user process]将 SQL 语句传递给 Oracle，之后解析结果被存储到共享 SQL 区[shared SQL area]中。语句处理过程在此步骤可能会出现多种错误。
　
084 Parsing is the process of:
  • Translating a SQL statement, verifying it to be a valid statement
  • Performing data dictionary lookups to check table and column definitions
  • Acquiring parse locks on required objects so that their definitions do not change during the statement's parsing (however, parse locks can be broken to allow conflicting DDL operations)
  • Checking privileges to access referenced schema objects
  • Determining the optimal execution plan for the statement
  • Loading it into a shared SQL area
  • Routing all or part of distributed statements to remote nodes that contain referenced data
 解析包含以下工作：
  • 翻译 SQL 语句，并验证语句的有效性
  • 查询数据字典表，检查相关表及数据列的定义
  • 获取相关对象上的解析锁[parse lock]，确保对象定义不会在语句解析过程中改变（与解析有冲突的 DDL 操作可以打破解析锁）
  • 检查访问相关方案对象的权限
  • 确定最优的语句执行方案[execution plan]
  • 将解析结果存储到共享 SQL 区
  • 将分布式语句[distributed statement]的部分或全部传递到包含相关数据的远程节点
085 Oracle parses a SQL statement only if a shared SQL area for an similar SQL statement does not exist in the shared pool. In this case, a new shared SQL area is allocated, and the statement is parsed.
　		 只有共享池[shared pool]中不存在相同 SQL 语句的共享 SQL 区时 Oracle 才需解析语句。在这种情况下，Oracle 将创建新的共享 SQL 区并解析语句。
　
086 The parse stage includes processing requirements that need to be done only once no matter how many times the statement is run. Oracle translates each SQL statement only once, rerunning that parsed statement during subsequent references to the statement.
　		 无论 SQL 语句运行多少次，解析步骤所做的工作只需处理一次。Oracle 只要解析 SQL 语句一次，再次运行同样的语句时直接使用之前的解析结果。
　
087 Although parsing a SQL statement validates that statement, parsing only identifies errors that can be found before statement execution. Thus, some errors cannot be caught by parsing. For example, errors in data conversion or errors in data (such as an attempt to enter duplicate values in a primary key) and deadlocks are all errors or situations that can be encountered and reported only during the execution stage.
　		 在解析步骤可以验证 SQL 语句的正确性，但此步骤只能找出语句执行前可能出现的错误。即解析只能发现部分错误。例如，数据转换错误，数据逻辑错误（例如向主键中插入了重复值）及死锁等情况只有在语句的执行阶段才 会发生并被捕获。
　
088

See Also:

"Shared SQL"
另见：

“共享 SQL”
089

Query Processing

24.1.5.2.3 查询处理过程
090 Queries are different from other types of SQL statements because, if successful, they return data as results. Whereas other statements simply return success or failure, a query can return one row or thousands of rows. The results of a query are always in tabular format, and the rows of the result are fetched (retrieved), either a row at a time or in groups.
　		 查询与其他类型的 SQL 语句有所不同，如果查询能够成功执行将返回结果数据。而其他类型的 SQL 语句只需返回执行成功或失败消息。查询可以返回一行乃至数千行数据。查询结果永远采取表形式[tabular format]，获取[fetch]数据行可以采取一次获取一行或一次获取一个行集的方式。
　
091 Several issues relate only to query processing. Queries include not only explicit SELECT statements but also the implicit queries (subqueries) in other SQL statements. For example, each of the following statements requires a query as a part of its execution:
　		 查询处理过程还面临一些特殊的问题。查询包括显式地 SELECT 语句，还有其他 SQL 语句中的隐式查询（子查询）。例如以下语句在执行过程中都需进行查询：
　
092

INSERT INTO table SELECT...

UPDATE table SET x = y WHERE...

DELETE FROM table WHERE...

CREATE table AS SELECT...
INSERT INTO table SELECT...

UPDATE table SET x = y WHERE...

DELETE FROM table WHERE...

CREATE table AS SELECT...
093 In particular, queries:
  • Require read consistency
  • Can use temporary segments for intermediate processing
  • Can require the describe, define, and fetch stages of SQL statement processing.
 除此之外，查询还具有以下特点：
  • 需要保证读一致性[read consistency]
  • 可以使用临时段[temporary segment]进行中间处理
  • 可能需要经过 SQL 语句处理过程中的描述[describe]，定义[define]，及获取步骤
094

Stage 3: Describe Results of a Query

24.1.5.2.4 第三步：描述查询结果
095 The describe stage is necessary only if the characteristics of a query's result are not known; for example, when a query is entered interactively by a user. In this case, the describe stage determines the characteristics (datatypes, lengths, and names) of a query's result.
　		 当查询结果的特性[characteristic]不确定时，语句处理需要经过描述[describe]步骤。例如，如果查询是由用户交互式地输入的，则需要经过描述步骤确定查询结果的特性（包括各结果字段的数据类型，长度，及名称）。
　
096

Stage 4: Define Output of a Query

24.1.5.2.5 第四步：定义查询的输出
097 In the define stage for queries, you specify the location, size, and datatype of variables defined to receive each fetched value. These variables are called define variables. Oracle performs datatype conversion if necessary. (See DEFINE on Figure 24-1, "The Stages in Processing a SQL Statement".)
　		 在查询处理的定义步骤中，用户需要设定用于接收获取值的变量的位置，数据类型，及容量。这样的变量被称为定义变量[define variable]。在需要时 Oracle 能够自动执行数据类型转换。（见 图 24-1 所示的 DEFINE 步骤。）
　
098

Stage 5: Bind Any Variables

24.1.5.2.6 第五步：绑定变量
099 At this point, Oracle knows the meaning of the SQL statement but still does not have enough information to run the statement. Oracle needs values for any variables listed in the statement; in the example, Oracle needs a value for department_id. The process of obtaining these values is called binding variables.
　		 在此阶段，Oracle 已经解释了 SQL 语句的含义，但还缺乏足够的信息运行此语句。Oracle 还需要语句中包含的变量的值。在示例中，Oracle 需要 department_id 的值。获得这些变量值的过程被称为绑定变量[binding variable]。
　
100 A program must specify the location (memory address) where the value can be found. End users of applications may be unaware that they are specifying bind variables, because the Oracle utility can simply prompt them for a new value.
　		 应用程序必须指定变量值的存储位置（即内存地址）。Oracle 工具能够提示应用程序的用户输入变量值，但用户无需了解绑定变量的过程。
　
101 Because you specify the location (binding by reference), you need not rebind the variable before reexecution. You can change its value and Oracle looks up the value on each execution, using the memory address.
　		 当应用程序指定了变量值的存储位置后，再次执行语句时无需重复绑定变量。用户可以修改变量值，Oracle 能够在每次执行时通过内存地址获得变量值。
　
102 You must also specify a datatype and length for each value (unless they are implied or defaulted) if Oracle needs to perform datatype conversion.
　		 如果 Oracle 需要执行数据类型转换，应用程序必须指定变量值的数据类型和长度（除非变量值有隐含或默认的数据类型）。
　
103
See Also:

for more information about specifying a datatype and length for a value

另见：

了解如何指定变量值的数据类型和长度
104

Stage 6: Parallelize the Statement

24.1.5.2.7 第六步：语句并行化
105 Oracle can parallelize queries (SELECTs, INSERTs, UPDATEs, MERGEs, DELETEs), and some DDL operations such as index creation, creating a table with a subquery, and operations on partitions. Parallelization causes multiple server processes to perform the work of the SQL statement so it can complete faster.
　		 Oracle 能够并行化地执行查询（SELECT，INSERT，UPDATE，MERGE，DELETE），还能够并行化地执行某些 DDL 操作，例如创建索引，通过子查询创建表，及分区操作等。并行执行使用多个进程执行 SQL 语句，以提高执行速度。
　
106
See Also:

Chapter 16, "Business Intelligence"
另见：

第 16 章，“业务智能”
107

Stage 7: Run the Statement

24.1.5.2.8 第七步：运行语句
108 At this point, Oracle has all necessary information and resources, so the statement is run. If the statement is a query or an INSERT statement, no rows need to be locked because no data is being changed. If the statement is an UPDATE or DELETE statement, however, all rows that the statement affects are locked from use by other users of the database until the next COMMIT, ROLLBACK, or SAVEPOINT for the transaction. This ensures data integrity.
　		 语句处理进行到此步骤时，Oracle 已获得了足够的信息及资源，此时语句可以开始执行。如果语句为查询或 INSERT 语句，不会对已有数据进行修改，因此不必对任何数据行加锁。如果语句为 UPDATE 或 DELETE 语句，则所有受影响的数据行都将被加锁，直至事务提交了 COMMIT，ROLLBACK，或 SAVEPOINT 命令锁才能被释放，锁能够避免其他数据库用户使用这些数据行。锁机制用于确保数据一致性。
　
109 For some statements you can specify a number of executions to be performed. This is called array processing. Given n number of executions, the bind and define locations are assumed to be the beginning of an array of size n.
　		 用户可以设定一条 SQL 语句的执行次数。这被称为批量执行[array processing]。如果用户设定的执行次数为 n，则在定义及绑定步骤需要使用长度为 n 的数组保存相关的设定。
　
110

Stage 8: Fetch Rows of a Query

24.1.5.2.9 第八步：获取查询的数据行
111 In the fetch stage, rows are selected and ordered (if requested by the query), and each successive fetch retrieves another row of the result until the last row has been fetched.
　		 在数据获取[fetch]步骤，数据行将被选择并排序（如果查询要求排序），每个获取操作取回结果集中的一条数据行，取回最后一行后获取步骤结束。
　
112

Stage 9: Close the Cursor

24.1.5.2.10 第九步：关闭游标
113 The final stage of processing a SQL statement is closing the cursor.
　		 处理 SQL 语句的最后一个步骤是关闭游标。
　
114

DDL Statement Processing

24.1.5.3 DDL 语句处理过程
115 The execution of DDL statements differs from the execution of DML statements and queries, because the success of a DDL statement requires write access to the data dictionary. For these statements, parsing (Stage 2) actually includes parsing, data dictionary lookup, and execution.
　		 DDL 语句的执行与 DML 语句及查询的执行有所区别，因为 DDL 语句执行时需要向数据字典中写入数据。对于 DDL 语句，在解析步骤（第二步）实际包含了解析，数据字典查询，及数据字典修改语句的执行。
　
116 Transaction management, session management, and system management SQL statements are processed using the parse and run stages. To rerun them, simply perform another execute.
　		 用于进行事务管理，会话管理，及系统管理的 SQL 语句是在解析及运行步骤执行的。再次提交这些语句时就能够重复执行。
　
117

Control of Transactions

24.1.5.4 事务控制
118 In general, only application designers using the programming interfaces to Oracle are concerned with the types of actions that should be grouped together as one transaction. Transactions must be defined so that work is accomplished in logical units and data is kept consistent. A transaction should consist of all of the necessary parts for one logical unit of work, no more and no less.
  • Data in all referenced tables should be in a consistent state before the transaction begins and after it ends.
  • Transactions should consist of only the SQL statements that make one consistent change to the data.
 一般情况下，只有通过编程接口使用 Oracle 的应用程序开发者才需要关心如何将不同类型的多个操作归为一个事务。在实际应用中需要通过事务确保逻辑上属于同一组的操作同时执行，从而确保数据一致性。一个事务应由逻辑上必须同时执行的一组操作构成。
  • 与操作相关的所有数据表中的数据在事务开始前及事务执行后均应具备数据一致性。
  • 事务内的 SQL 语句只能对数据进行一次具备一致性的修改。
119 For example, a transfer of funds between two accounts (the transaction or logical unit of work) should include the debit to one account (one SQL statement) and the credit to another account (one SQL statement). Both actions should either fail or succeed together as a unit of work; the credit should not be committed without the debit. Other unrelated actions, such as a new deposit to one account, should not be included in the transfer of funds transaction.
　		 例如，在两个账户间转帐（逻辑上相关的一组操作，即一个事务）包括对一个账户进行借方操作（一条 SQL 语句）及对另一个账户进行贷方操作（一条 SQL 语句）。两个操作在逻辑上属于一组，必须同时执行或同时撤销，即只有借方操作成功才能提交贷方操作。其他不相关的操作，例如对其中一个账户进行存款操作，不应包含在转帐事务中。
　
120

Overview of the Optimizer

24.1.6 优化器概述
121 All SQL statements use the optimizer, a part of Oracle that determines the most efficient means of accessing the specified data. Oracle also provides techniques that you can use to make the optimizer perform its job better.
　		 所有 SQL 语句都可以使用 Oracle 内置的优化器[optimizer]选择访问其所需数据的最优方式。用户还可以使用 Oracle 提供的工具配置优化器使其更好的工作。
　
122 There are often many different ways to process a SQL DML (SELECT, INSERT, UPDATE, MERGE, or DELETE) statement; for example, by varying the order in which tables or indexes are accessed. The procedure Oracle uses to run a statement can greatly affect how quickly the statement runs. The optimizer considers many factors among alternative access paths.
　		 Oracle 完成一个 SQL DML 语句（SELECT，INSERT，UPDATE，MERGE，或 DELETE）的方式有多种 。例如，访问语句内各个表及索引的顺序可以不同。Oracle 运行语句的方式可能会显著地影响语句运行的速度。优化器能够考虑多种因素从可选择的访问路径 中确定最优方案。
　
123
Note:

The optimizer might not make the same decisions from one version of Oracle to the next. In recent versions, the optimizer might make decisions based on better information available to it.
提示：

不同版本的 Oracle 在相同条件下作出的决定可能不同。在最新版本的 Oracle 中，优化器可以利用提供给他的更多信息做出更好的执行决策。
124 You can influence the optimizer's choices by setting the optimizer approach and goal. Objects with stale or no statistics are automatically analyzed. You can also gather statistics for the optimizer using the PL/SQL package DBMS_STATS.
　		 用户可以设置优化器决策方式及目标来影响优化器的选择。对于没有统计信息[statistic]及信息陈旧的对象 Oracle 能够自动地进行分析。用户也可以使用 PL/SQL 包 DBMS_STATS 为优化器收集统计信息。
　
125 Sometimes the application designer, who has more information about a particular application's data than is available to the optimizer, can choose a more effective way to run a SQL statement. The application designer can use hints in SQL statements to specify how the statement should be run.
　		 有时，应用程序开发者所掌握的关于数据的信息可能多于优化器，此时开发者可以选择更高效的方式执行 SQL 语句。开发者可以在 SQL 语句中使用执行指示[hint]来说明语句应该如何执行。
　
126

See Also:

另见：
127

Execution Plans

24.1.6.1 执行计划
128 To run a DML statement, Oracle might need to perform many steps. Each of these steps either retrieves rows of data physically from the database or prepares them in some way for the user issuing the statement. The combination of the steps Oracle uses to run a statement is called an execution plan. An execution plan includes an access method for each table that the statement accesses and an ordering of the tables (the join order). The steps of the execution plan are not performed in the order in which they are numbered.
　		 在运行一条 DML 语句时，Oracle 可能需要执行多个步骤。例如，从数据库物理文件中获取数据行，或将数据整理为提交语句的用户所需的形式。Oracle 运行一条语句的所有步骤被称为语句的执行计划[execution plan]。执行计划中包括语句所需数据所在数据表的访问方式，以及数据表的访问顺序（即关联顺序）。需要注意的是，执行计划各步骤的执行顺序不是通过编号表示的。
　
129

Stored Outlines

24.1.6.1.1 存储执行概要
130 Stored outlines are abstractions of an execution plan generated by the optimizer at the time the outline was created and are represented primarily as a set of hints. When the outline is subsequently used, these hints are applied at various stages of compilation. Outline data is stored in the OUTLN schema. You can tune execution plans by editing stored outlines.
　		 存储执行概要[stored outline]是执行计划[execution plan]的概要，由优化器[optimizer]创建概要的同时对其进行存储，此概要主要由一组执行提示[hint]构成。当一个执行概要被使用时，其中的执行提示将被应用到语句各执行步骤的编译过程中。执行概要数据存储在 OUTLN 方案中。用户可以编辑存储执行概要以调整执行计划。
　
131

Editing Stored O