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: 本章包含以下主题:

See Also:



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 内置的安全特性,有可能对数据的安全性及完整性造成破坏。

See Also:



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 中有特殊的含义,用户不能将其用作其他用途。例如,SELECTUPDATE 都是保留字,不能用做数据表名。
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 语法,例如:

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 语句必须书写完整才能执行:

SELECT last_name

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

See Also:

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


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

Data Manipulation Language Statements 数据操作语言语句

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 语句的示例:

SELECT last_name, manager_id, commission_pct + salary FROM employees;

SELECT last_name, manager_id, commission_pct + salary FROM employees;

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

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

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

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

DML Error Logging 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

See Also:



Data Definition Language Statements 数据定义语言语句

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 语句可以完成以下工作:
  • 创建,修改,移除方案对象及其他数据库结构,包括数据库自身及数据库用户(CREATEALTERDROP
  • 修改方案对象名称(RENAME
  • 删除方案对象的所有数据,但不移除对象结构(TRUNCATE
  • 授予或收回权限及角色(GRANTREVOKE
  • 打开或关闭审计选项(AUDITNOAUDIT
  • 向数据字典中添加注释(COMMENT
031 DDL statements implicitly commit the preceding and start a new transaction. Some examples of DDL statements are:
DDL 语句将隐式地提交之前的操作并开始一个新事务。以下是一些 DDL 语句的示例:



DROP TABLE plants;

DROP TABLE plants;

GRANT SELECT ON employees TO scott;

GRANT SELECT ON employees TO scott;

REVOKE DELETE ON employees FROM scott;

REVOKE DELETE ON employees FROM scott;

See Also:

Chapter 20, "Database Security"


第 20 章,“数据库安全

Transaction Control Statements 事务控制语句

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

Session Control Statements 会话控制语句

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

System Control Statements 系统控制语句

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],或执行其他操作。

Embedded SQL Statements 嵌入 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 CURSOROPENCLOSE
  • 选择一个 Oracle 数据库并进行连接(DECLARE DATABASECONNECT
  • 初始化描述符[descriptor](DESCRIBE
  • 设定如何处理错误及警告(WHENEVER
  • 从数据库中取回数据(FETCH


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 区存储其信息。

Scrollable Cursors 可滚动游标

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 位的数据行。

See Also:

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


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

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]。

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 - FundamentalsOracle Database Performance Tuning Guide 了解关于共享 SQL 的更多信息


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.

See Also:

"Overview of PL/SQL"



SQL Processing

24.1.5 SQL 处理过程

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

SQL Statement Execution 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 语句的步骤




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]或解析步骤重复进行。

DML Statement Processing 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 语句嵌入程序:

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 所示:

See Also:

"Query Processing"



Stage 1: Create a Cursor 第一步:创建游标

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]中,游标也可以被显式地声明。

Stage 2: Parse the Statement 第二步:解析语句

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 语句的正确性,但此步骤只能找出语句执行前可能出现的错误。即解析只能发现部分错误。例如,数据转换错误,数据逻辑错误(例如向主键中插入了重复值)及死锁等情况只有在语句的执行阶段才 会发生并被捕获。

See Also:

"Shared SQL"


共享 SQL

Query Processing 查询处理过程

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 语句中的隐式查询(子查询)。例如以下语句在执行过程中都需进行查询:


UPDATE table SET x = y WHERE...




UPDATE table SET x = y WHERE...


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],及获取步骤

Stage 3: Describe Results of a Query 第三步:描述查询结果

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.

Stage 4: Define Output of a Query 第四步:定义查询的输出

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 步骤。)

Stage 5: Bind Any Variables 第五步:绑定变量

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 需要执行数据类型转换,应用程序必须指定变量值的数据类型和长度(除非变量值有隐含或默认的数据类型)。
See Also:

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




Stage 6: Parallelize the Statement 第六步:语句并行化

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 能够并行化地执行查询(SELECTINSERTUPDATEMERGEDELETE),还能够并行化地执行某些 DDL 操作,例如创建索引,通过子查询创建表,及分区操作等。并行执行使用多个进程执行 SQL 语句,以提高执行速度。
See Also:

Chapter 16, "Business Intelligence"

第 16 章,“业务智能

Stage 7: Run the Statement 第七步:运行语句

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 语句,不会对已有数据进行修改,因此不必对任何数据行加锁。如果语句为 UPDATEDELETE 语句,则所有受影响的数据行都将被加锁,直至事务提交了 COMMITROLLBACK,或 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 的数组保存相关的设定。

Stage 8: Fetch Rows of a Query 第八步:获取查询的数据行

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.

Stage 9: Close the Cursor 第九步:关闭游标

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

DDL Statement Processing 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 语句是在解析及运行步骤执行的。再次提交这些语句时就能够重复执行。

Control of Transactions 事务控制

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 语句)。两个操作在逻辑上属于一组,必须同时执行或同时撤销,即只有借方操作成功才能提交贷方操作。其他不相关的操作,例如对其中一个账户进行存款操作,不应包含在转帐事务中。

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 语句(SELECTINSERTUPDATEMERGE,或 DELETE)的方式有多种 。例如,访问语句内各个表及索引的顺序可以不同。Oracle 运行语句的方式可能会显著地影响语句运行的速度。优化器能够考虑多种因素从可选择的访问路径 中确定最优方案。

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]来说明语句应该如何执行。

See Also:



Execution Plans 执行计划

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]。执行计划中包括语句所需数据所在数据表的访问方式,以及数据表的访问顺序(即关联顺序)。需要注意的是,执行计划各步骤的执行顺序不是通过编号表示的。

Stored Outlines 存储执行概要

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 方案中。用户可以编辑存储执行概要以调整执行计划。

Editing Stored Outlines 编辑存储执行概要

132 The outline is cloned into the user's schema at the onset of the outline editing session. All subsequent editing operations are performed on that clone until the user is satisfied with the edits and chooses to publicize them. In this way, any editing done by the user does not impact the rest of the user community, which would continue to use the public version of the outline until the edits are explicitly saved.
当编辑存储执行概要[stored outline]的会话开始时,被编辑的执行概要将被复制到相应的用户方案[schema]中。之后所有的编辑操作均针对此副本执行,直至用户决定发布编辑好的执行概要。采取此种方式,一个用户修改存储执行概要时不会影响数据库的其他用户,其他用户将继续使用已发布的存储执行概要,直到用户编辑结果被显式地保存。
See Also:

Oracle Database Performance Tuning Guide for details about execution plans and using stored outlines

Oracle Database Performance Tuning Guide 了解执行计划的细节以及如何使用存储执行概要

Overview of Procedural Languages

24.2 过程化语言概述

135 In Oracle, SQL, PL/SQL, XML, and Java all interoperate seamlessly in a way that allows developers to mix-and-match the most relevant features of each language. SQL and PL/SQL form the core of Oracle's application development stack. Not only do most enterprise back-ends run SQL, but Web applications accessing databases do so using SQL (wrappered by Java classes as JDBC), Enterprise Application Integration applications generate XML from SQL queries, and content-repositories are built on top of SQL tables. It is a simple, widely understood, unified data model. It is used standalone in many applications, but it is also invoked indirectly from Java (JDBC), Oracle Call Interface (dynamic SQL), and XML (XML SQL Utility).
在 Oracle 系统内,SQL,PL/SQL,XML,及 Java 间可以进行无缝地相互操作,开发者可以搭配使用各种语言中的相关特性。SQL 及 PL/SQL 构成了 Oracle 应用开发框架的核心。绝大多数的企业后台应用程序需要运行 SQL,Web 应程序访问数据库时也需要使用 SQL(此时 SQL 被封装为 JDBC Java 类),企业应用集成系统需要使用 SQL 查询生成 XML,各种内容资料库[content-repository]也构建于 SQL 数据表之上。SQL 是一种简单,被广泛理解,且统一的数据访问模型。SQL 可以在应用程序中独立使用,也可以由 Java(JDBC),Oracle Call Interface(动态 SQL),及 XML(XML SQL 工具)间接地使用。
136 This section includes the following: 本节包含以下内容:

Overview of PL/SQL

24.2.1 PL/SQL 概述

138 PL/SQL is Oracle's procedural language extension to SQL. It provides a server-side, stored procedural language that is easy-to-use, seamless with SQL, robust, portable, and secure. The PL/SQL compiler and interpreter are embedded in Oracle Developer, providing developers with a consistent and leveraged development model on both the client and the server side. In addition, PL/SQL stored procedures can be called from a number of Oracle clients, such as Pro*C or Oracle Call Interface, and from Oracle Reports and Oracle Forms.
PL/SQL 是 Oracle 在 SQL 的基础上扩展而成的过程化语言。PL/SQL 是一种存储并运行于服务端的过程化语言,具有安全,健壮,可移植,易于使用,且与 SQL 无缝集成等特点。在 Oracle Developer 中包含了 PL/SQL 编译器及解释器,开发者可以采用相同的方式开发客户端及服务端程序。此外,PL/SQL 存储过程可以由 Pro*C 或 Oracle Call Interface 等多种 Oracle 客户端调用,也可以由 Oracle Reports 及 Oracle Forms 调用。
139 PL/SQL enables you to mix SQL statements with procedural constructs. With PL/SQL, you can define and run PL/SQL program units such as procedures, functions, and packages. PL/SQL program units generally are categorized as anonymous blocks and stored procedures.
用户可以在 PL/SQL 中结合使用 SQL 语句及过程控制语句。用户可以定义并运行过程,函数,及包等 PL/SQL 程序单元[program unit]。PL/SQL 程序单元一般可分为两类:匿名块[anonymous block]与存储过程[stored procedure]。
140 An anonymous block is a PL/SQL block that appears in your application and is not named or stored in the database. In many applications, PL/SQL blocks can appear wherever SQL statements can appear.
匿名块是一种嵌入到应用程序中的 PL/SQL 程序单元,没有命名且不存储在数据库中。在大多数应用程序中,能使用 SQL 语句的位置就可以使用匿名块。
141 A stored procedure is a PL/SQL block that Oracle stores in the database and can be called by name from an application. When you create a stored procedure, Oracle parses the procedure and stores its parsed representation in the database. Oracle also lets you create and store functions (which are similar to procedures) and packages (which are groups of procedures and functions).
存储过程是一种存储于 Oracle 数据库内且可以由应用程序根据其名称进行调用的 PL/SQL 程序单元。当用户创建存储过程时,Oracle 对过程进行解析并将解析后的结果存储到数据库中。用户还可以创建存储函数[store function](与过程类似)及包[package](一组相关的过程及函数)。

See Also:



How PL/SQL Runs PL/SQL 是如何运行的

144 PL/SQL can run with either interpreted execution or native execution.
PL/SQL 的执行方式包括解释执行[interpreted execution]与本地执行[native execution]。

Interpreted Execution 解释执行

146 In versions earlier than Oracle9i, PL/SQL source code was always compiled into a so-called bytecode representation, which is run by a portable virtual computer implemented as part of the Oracle database server, and also in products such as Oracle Forms. Starting with Oracle9i, you can choose between native execution and interpreted execution.
在 Oracle9i 之前的版本中,PL/SQL 源代码只能被编译为字节码[bytecode],此种代码由 Oracle 数据库服务器提供的可移植虚拟机运行,也可以由 Oracle Forms 等产品执行。从 Oracle9i 开始,用户应可以选择解释执行或本地执行。

Native Execution 本地执行

148 For best performance on computationally intensive program units, compile the source code of PL/SQL program units stored in the database directly to object code for the given platform. (This object code is linked into the Oracle database server.)
为了使包含大量计算工作的程序获得最佳的性能,可以将存储于数据库内的 PL/SQL 程序单元的源代码直接编译为数据库所在平台的目标码[object code]。(目标码将被链接[link]到 Oracle 数据库服务器。)
See Also:

Oracle Database PL/SQL User's Guide and Reference

Oracle Database PL/SQL User's Guide and Reference
150 The PL/SQL engine is the tool you use to define, compile, and run PL/SQL program units. This engine is a special component of many Oracle products, including the Oracle database server.
PL/SQL 引擎[PL/SQL engine]是用户定义,编译,及运行 PL/SQL 程序单元的工具。许多 Oracle 产品中都包含此组件,例如 Oracle 数据服务器。
151 While many Oracle products have PL/SQL components, this section specifically covers the program units that can be stored in an Oracle database and processed using the Oracle database server PL/SQL engine. The PL/SQL capabilities of each Oracle tool are described in the appropriate tool's documentation.
虽然许多 Oracle 产品中都包含 PL/SQL 组件,但本节主要讲述可以存储于 Oracle 数据库且可以由 Oracle 数据库服务器 PL/SQL 引擎处理的 PL/SQL 程序单元。各种 Oracle 工具的 PL/SQL 功能将在相应的工具文档中介绍。
152 Figure 24-2 illustrates the PL/SQL engine contained in Oracle database server.
图 24-2 显示了 Oracle 数据库服务器包含的 PL/SQL 引擎。
153 Figure 24-2 The PL/SQL Engine and the Oracle Database Server
图 24-2 PL/SQL 引擎与 Oracle 数据库服务器


Figure 24-2 shows applications sending procedure calls to the Oracle Database. The PL/SQL procedure is run in the SGA, which sends it to the PL/SQL engine. The PL/SQL engine sends SQL to the SQL statement executor.
图 24-2 显示了应用程序向 Oracle 数据库发送了一个过程调用。PL/SQL 过程在 SGA 中运行,SGA 将过程发送给 PL/SQL 引擎,而引擎则将 SQL 发送给 SQL 语句执行器。
156 The program unit is stored in a database. When an application calls a procedure stored in the database, Oracle loads the compiled program unit into the shared pool in the system global area (SGA). The PL/SQL and SQL statement executors work together to process the statements within the procedure.
PL/SQL 程序单元存储于数据库中。当应用程序调用存储于数据库内的存储过程时,Oracle 将经过编译的程序单元加载到位于系统全局区[system global area,SGA]的共享池[shared pool]内。PL/SQL 语句执行器[PL/SQL statement executor]和 SQL 语句执行器[SQL statement executor]协同工作,共同处理程序单元内的所有语句。
157 The following Oracle products contain a PL/SQL engine:
  • Oracle database server
  • Oracle Forms (version 3 and later)
  • SQL*Menu (version 5 and later)
  • Oracle Reports (version 2 and later)
  • Oracle Graphics (version 2 and later)
以下 Oracle 产品均包含 PL/SQL 引擎:
  • Oracle 数据库服务器
  • Oracle Forms(3 及之后的版本)
  • SQL*Menu(5 及之后的版本)
  • Oracle Reports(2 及之后的版本)
  • Oracle Graphics(2 及之后的版本)
158 You can call a stored procedure from another PL/SQL block, which can be either an anonymous block or another stored procedure. For example, you can call a stored procedure from Oracle Forms (version 3 or later).
用户可以在 PL/SQL 块中调用存储过程,此处的 PL/SQL 块可以是匿名块[anonymous block]也可以是其他存储过程。例如,用户可以在 Oracle Forms(3 及之后的版本)中调用存储过程。
159 Also, you can pass anonymous blocks to Oracle from applications developed with these tools:
  • Oracle precompilers (including user exits)
  • Oracle Call Interfaces (OCIs)
  • SQL*Plus
  • Oracle Enterprise Manager
用户还可以在使用以下工具开发的应用程序中将匿名块传递给 Oracle:
  • Oracle 预编译器[precompilers](包括用户出口)
  • Oracle Call Interfaces(OCI)
  • SQL*Plus
  • Oracle 企业管理器[Oracle Enterprise Manager]

Language Constructs for PL/SQL PL/SQL 语言构件

161 PL/SQL blocks can include the following PL/SQL language constructs:
  • Variables and constants
  • Cursors
  • Exceptions
PL/SQL 块中可以包含以下 PL/SQL 语言构件:
  • 变量[variable]与常量[constant]
  • 游标[cursor]
  • 异常[exception]
162 This section gives a general description of each construct.
See Also:

Oracle Database PL/SQL User's Guide and Reference

Oracle Database PL/SQL User's Guide and Reference

Variables and Constants 变量与常量

165 Variables and constants can be declared within a procedure, function, or package. A variable or constant can be used in a SQL or PL/SQL statement to capture or provide a value when one is needed.
在过程,函数,及包中均可声明变量与常量。变量与常量可以在 SQL 语句或 PL/SQL 语句中使用,用于存储(读取或写入)数据值。
166 Some interactive tools, such as SQL*Plus, let you define variables in your current session. You can use such variables just as you would variables declared within procedures or packages.
在 SQL*Plus 等交互式的工具中,用户可以在当前会话内定义变量。此种变量的使用方式与过程或包中的变量相同。

Cursors 游标

168 Cursors can be declared explicitly within a procedure, function, or package to facilitate record-oriented processing of Oracle data. Cursors also can be declared implicitly (to support other data manipulation actions) by the PL/SQL engine.
游标[cursor]可以在过程,函数,及包内显式地声明,实现以记录为单位处理 Oracle 数据。游标也可以由 PL/SQL 引擎隐式地声明(用于支持其他类型的数据操作工作)。
See Also:

"Scrollable Cursors"


Exceptions 异常

171 PL/SQL lets you explicitly handle internal and user-defined error conditions, called exceptions, that arise during processing of PL/SQL code. Internal exceptions are caused by illegal operations, such as division by zero, or Oracle errors returned to the PL/SQL code. User-defined exceptions are explicitly defined and signaled within the PL/SQL block to control processing of errors specific to the application (for example, debiting an account and leaving a negative balance).
用户可以显式地处理 PL/SQL 中出现的内部错误及用户定义错误,在 PL/SQL 代码执行过程中发生错误的情况被称为异常[exception]。内部异常[internal exception]是由非法操作造成的,例如运算除数为零,或 PL/SQL 代码返回了 Oracle 错误。用户定义异常[user-defined exception]是用户在 PL/SQL 代码内显式定义并显式抛出的,用于处理应用程序中可能发生的特定错误(例如,对一个账户做借方操作后账户余额变为负值)。
172 When an exception is raised, the execution of the PL/SQL code stops, and a routine called an exception handler is invoked. Specific exception handlers can be written for any internal or user-defined exception.
当发生异常时,PL/SQL 代码的执行将被停止,同时引发异常处理过程。开发者可以为不同的内部异常及用户定义异常编写相应的异常处理过程。

Dynamic SQL in PL/SQL PL/SQL 中的动态 SQL

174 PL/SQL can run dynamic SQL statements whose complete text is not known until runtime. Dynamic SQL statements are stored in character strings that are entered into, or built by, the program at runtime. This enables you to create general purpose procedures. For example, dynamic SQL lets you create a procedure that operates on a table whose name is not known until runtime.
PL/SQL 中可以运行动态 SQL[dynamic SQL],动态 SQL 语句的文本是在运行时才被完全确定的。动态 SQL 语句通常存储在字符串中,此字符串是在程序运行时被输入或构建的。用户可以使用动态 SQL 开发通用过程。例如,用户可以创建过程针对数据表进行某种操作,而数据表的名称可以在运行时才提供。
175 You can write stored procedures and anonymous PL/SQL blocks that include dynamic SQL in two ways:
  • By embedding dynamic SQL statements in the PL/SQL block
  • By using the DBMS_SQL package
用户在存储过程及匿名 PL/SQL 块中均可使用动态 SQL,且使用时可以采用两种方式:
  • 将动态 SQL 语句嵌入到 PL/SQL 程序块中
  • 使用 DBMS_SQL
176 Additionally, you can issue DML or DDL statements using dynamic SQL. This helps solve the problem of not being able to statically embed DDL statements in PL/SQL. For example, you can choose to issue a DROP TABLE statement from within a stored procedure by using the EXECUTE IMMEDIATE statement or the PARSE procedure supplied with the DBMS_SQL package.
用户可以使用动态 SQL 提交 DML 或 DDL 语句。在 PL/SQL 中无法嵌入静态 DDL 语句,动态 SQL为此问题提供了一个解决方法。例如,用户可以在存储过程中使用 EXECUTE IMMEDIATE 语句或 DBMS_SQL 包提供的 PARSE 方法提交 DROP TABLE 语句。

See Also:



PL/SQL Program Units PL/SQL 程序单元

179 Oracle lets you access and manipulate database information using procedural schema objects called PL/SQL program units. Procedures, functions, and packages are all examples of PL/SQL program units.
Oracle 提供了被称为 PL/SQL 程序单元[PL/SQL program units]的过程型方案对象[procedural schema object],用于访问及操作数据库信息。过程,函数,及包都属于 PL/SQL 程序单元。

Stored Procedures and Functions 存储过程与存储函数

181 A procedure or function is a schema object that consists of a set of SQL statements and other PL/SQL constructs, grouped together, stored in the database, and run as a unit to solve a specific problem or perform a set of related tasks. Procedures and functions permit the caller to provide parameters that can be input only, output only, or input and output values. Procedures and functions let you combine the ease and flexibility of SQL with the procedural functionality of a structured programming language.
过程[procedure]及函数[function]这两种方案对象均由 SQL 语句及 PL/SQL 语言构件组合而成,存储于数据库中,运行时作为一个整体,用于解决某个问题,或完成一组相关的操作。调用者可以在调用过程及函数时为其提供参数,参数可以专用于输入值,专用于输出值,或同时用于输入及输出值。在过程与函数中,用户既可以发挥 SQL 的灵活性与易用性,也能够发挥结构化编成语言的过程控制能力。
182 Procedures and functions are identical except that functions always return a single value to the caller, while procedures do not. For simplicity, procedure as used in the remainder of this chapter means procedure or function.
183 You can run a procedure or function interactively by:
  • Using an Oracle tool, such as SQL*Plus
  • Calling it explicitly in the code of a database application, such as an Oracle Forms or precompiler application
  • Calling it explicitly in the code of another procedure or trigger
  • 使用 SQL*Plus 等 Oracle 工具
  • 在数据库应用程序代码中显式地调用,例如在 Oracle Forms 或其他预编译应用程序中[precompiler application]
  • 在其他过程或触发器的代码中显式地调用

See Also:


185 Figure 24-3 illustrates a simple procedure that is stored in the database and called by several different database applications.
图 24-3 显示了数据库中的一个简单存储过程,被不同的数据库应用程序调用。
186 Figure 24-3 Stored Procedure
图 24-3 存储过程


Figure 24-3 is described in the text preceding the image.
图 24-3 将在下文中描述。
189 The following stored procedure example inserts an employee record into the employees table:
以下存储过程示例向 employees 表插入一条员工信息:
Procedure hire_employees (last_name VARCHAR2, job_id VARCHAR2, manager_id NUMBER, hire_date DATE, salary NUMBER, commission_pct NUMBER, department_id NUMBER)

INSERT INTO employees VALUES (emp_sequence.NEXTVAL, last_name, job_id, manager_id, hire_date, salary, commission_pct, department_id);
Procedure hire_employees (last_name VARCHAR2, job_id VARCHAR2, manager_id NUMBER, hire_date DATE, salary NUMBER, commission_pct NUMBER, department_id NUMBER)

INSERT INTO employees VALUES (emp_sequence.NEXTVAL, last_name, job_id, manager_id, hire_date, salary, commission_pct, department_id);
191 All of the database applications in this example call the hire_employees procedure. Alternatively, a privileged user can use Oracle Enterprise Manager or SQL*Plus to run the hire_employees procedure using the following statement:
此例中的所有数据库应用程序均调用 hire_employees 过程。授权用户也可以在 Oracle 企业管理器[Enterprise Manager] 或 SQL*Plus 中使用以下语句运行 hire_employees 过程:
EXECUTE hire_employees ('TSMITH', 'CLERK', 1037, SYSDATE, 500, NULL, 20);
EXECUTE hire_employees ('TSMITH', 'CLERK', 1037, SYSDATE, 500, NULL, 20);
193 This statement places a new employee record for TSMITH in the employees table
此语句在 employees 表中为 TSMITH 插入一条记录。
See Also:

Oracle Database PL/SQL User's Guide and Reference

Oracle Database PL/SQL User's Guide and Reference

Benefits of Procedures 存储过程的优势

196 Stored procedures provide advantages in the following areas:
  • Security with definer's rights procedures

    Stored procedures can help enforce data security. You can restrict the database operations that users can perform by allowing them to access data only through procedures and functions that run with the definer's privileges.

    For example, you can grant users access to a procedure that updates a table but not grant them access to the table itself. When a user invokes the procedure, the procedure runs with the privileges of the procedure's owner. Users who have only the privilege to run the procedure (but not the privileges to query, update, or delete from the underlying tables) can invoke the procedure, but they cannot manipulate table data in any other way.
  • 利用定义者权限过程[definer's rights procedure]确保数据安全


See Also:

"Dependency Tracking for Stored Procedures"

  • Inherited privileges and schema context with invoker's rights procedures

    An invoker's rights procedure inherits privileges and schema context from the procedure that calls it. In other words, an invoker's rights procedure is not tied to a particular user or schema, and each invocation of an invoker's rights procedure operates in the current user's schema with the current user's privileges. Invoker's rights procedures make it easy for application developers to centralize application logic, even when the underlying data is divided among user schemas.

    For example, a user who runs an update procedure on the employees table as a manager can update salary, whereas a user who runs the same procedure as a clerk can be restricted to updating address data.
  • Improved performance

    • The amount of information that must be sent over a network is small compared with issuing individual SQL statements or sending the text of an entire PL/SQL block to Oracle, because the information is sent only once and thereafter invoked when it is used.

    • A procedure's compiled form is readily available in the database, so no compilation is required at execution time.

    • If the procedure is already present in the shared pool of the system global area (SGA), then retrieval from disk is not required, and execution can begin immediately.
  • Memory allocation

  • Because stored procedures take advantage the shared memory capabilities of Oracle, only a single copy of the procedure needs to be loaded into memory for execution by multiple users. Sharing the same code among many users results in a substantial reduction in Oracle memory requirements for applications.
  • Improved productivity

    Stored procedures increase development productivity. By designing applications around a common set of procedures, you can avoid redundant coding and increase your productivity.

    For example, procedures can be written to insert, update, or delete employee records from the employees table. These procedures can then be called by any application without rewriting the SQL statements necessary to accomplish these tasks. If the methods of data management change, only the procedures need to be modified, not all of the applications that use the procedures.
  • Integrity

    Stored procedures improve the integrity and consistency of your applications. By developing all of your applications around a common group of procedures, you can reduce the likelihood of committing coding errors.

    For example, you can test a procedure or function to guarantee that it returns an accurate result and, once it is verified, reuse it in any number of applications without testing it again. If the data structures referenced by the procedure are altered in any way, then only the procedure needs to be recompiled. Applications that call the procedure do not necessarily require any modifications.
  • 通过调用者权限过程[invoker's rights procedure]使用调用者的权限及方案上下文


    例如,以经理身份登录的用户运行针对 employees 表的更新过程时可以修改薪水数据,而以职员身份登录的用户运行同样的的更新过程时只能修改地址数据。
  • 提升性能

    • 与向 Oracle 提交 SQL 语句或 PL/SQL 块代码相比,使用存储过程时通过网络传输的数据量较小,因为存储过程只有在定义时需要被传输到服务器,而使用时只需进行调用。

    • 数据库以编译后的形式保存过程,因此执行期间无需进行编译。

    • 如果过程已经被加载到系统全局区[system global area,SGA]的共享池[shared pool]内,则过程可以直接执行,而不必从磁盘获取。
  • 内存分配

    存储过程可以利用 Oracle 的共享内存特性,多个用户执行同一个过程时只需将一份过程副本加载到内存中。通过在多个用户间共享相同的代码,能够显著地减少应用程序所需的 Oracle 内存。
  • 提高开发生产率


    例如,开发者可以编写存储过程,分别对 employees 表内的员工数据进行插入,更新及删除操作。任何应用程序都可以调用这些过程,而无需重写 SQL 语句就可以完成相应的工作。如果数据管理的方式发生变化,只需修改存储过程,而不必修改调用过程的应用程序。
  • 完整性

    存储过程能够提高应用程序所处理数据时的一致性与完整性。由于应用程序是在一组 公用存储过程的基础上开发的,因此能够减少提交代码发生错误的可能性。


Procedure Guidelines 过程开发指南

200 Use the following guidelines when designing stored procedures:
  • Define procedures to complete a single, focused task. Do not define long procedures with several distinct subtasks, because subtasks common to many procedures can be duplicated unnecessarily in the code of several procedures.
  • Do not define procedures that duplicate the functionality already provided by other features of Oracle. For example, do not define procedures to enforce simple data integrity rules that you could easily enforce using declarative integrity constraints.
  • 在一个存储过程中应该只实现一个单一的任务。不要定义执行多个子任务的大型存储过程,如果在多个过程中实现了相同的子任务将造成不必要的代码重复。
  • 不要定义过程来实现 Oracle 已经提供的功能。例如,不要定义过程来强制实现简单的数据完整性规则,此类功能只需要声明数据完整性约束即可实现。

Anonymous PL/SQL Blocks Compared with Stored Procedures 匿名 PL/SQL 块与存储过程的比较

202 A stored procedure is created and stored in the database as a schema object. Once created and compiled, it is a named object that can be run without recompiling. Additionally, dependency information is stored in the data dictionary to guarantee the validity of each stored procedure.
存储过程在创建后作为方案对象存储在数据库中。一旦存储过程被创建并编译后,就成为一个命名的对象,可以重复执行而无需再次编译。此外,与存储过程相关的依赖性信息也将被保存在数据字典中,以确保 过程的有效性。
203 As an alternative to a stored procedure, you can create an anonymous PL/SQL block by sending an unnamed PL/SQL block to the Oracle database server from an Oracle tool or an application. Oracle compiles the PL/SQL block and places the compiled version in the shared pool of the SGA, but it does not store the source code or compiled version in the database for reuse beyond the current instance. Shared SQL allows anonymous PL/SQL blocks in the shared pool to be reused and shared until they are flushed out of the shared pool.
除存储过程之外,用户还可以通过 Oracle 工具或应用程序向 Oracle 数据库服务器发送无命名的 PL/SQL 块来创建匿名 PL/SQL 块。Oracle 将编译 PL/SQL 块,并将编译结果存储在 SGA 的共享池内[shared pool],但匿名 PL/SQL 块不会以编译后的形式存储于数据库内,当实例关闭后则无法重用。利用 Oracle 的共享 SQL 技术[Shared SQL],共享池内的匿名 PL/SQL 块在被清除出共享池之前可以被重用。
204 In either case, moving PL/SQL blocks out of a database application and into database procedures stored either in the database or in memory, you avoid unnecessary procedure recompilations by Oracle at runtime, improving the overall performance of the application and Oracle.
将数据库应用程序中的 PL/SQL 块改写为存储过程,存储到数据库或内存中,可以避免过程运行时 Oracle 进行不必要的重复编译工作,从而提升应用程序及 Oracle 的整体性能。

Standalone Procedures 独立存储过程

206 Stored procedures not defined within the context of a package are called standalone procedures. Procedures defined within a package are considered a part of the package.
没有在包[package]中定义的存储过程被称为独立存储过程[standalone procedure]。而在包内定义的过程被看作包的一部分。
See Also:

"PL/SQL Packages" for information about the advantages of packages

PL/SQL 包”了解使用包有哪些优势

Dependency Tracking for Stored Procedures 存储过程的依赖性跟踪

209 A stored procedure depends on the objects referenced in its body. Oracle automatically tracks and manages such dependencies. For example, if you alter the definition of a table referenced by a procedure, then the procedure must be recompiled to validate that it will still work as designed. Usually, Oracle automatically administers such dependency management.
存储过程依赖其定义中所引用的对象。Oracle 能够自动地跟踪及管理相关的依赖性。例如,用户修改了过程所引用的表的定义,过程必须被重新编译以验证其是否能正常工作。通常,Oracle 能够自动地对依赖性问题进行管理。
See Also:

Chapter 6, "Dependencies Among Schema Objects" for more information about dependency tracking

第 6 章,“方案对象间的依赖关系” 了解关于依赖性跟踪的更多信息

External Procedures 外部过程

212 A PL/SQL procedure executing on an Oracle database server can call an external procedure or function that is written in the C programming language and stored in a shared library. The C routine runs in a separate address space from that of the Oracle database server.
在 Oracle 数据库服务器上执行的 PL/SQL 过程可以调用以 C 语言编写的外部过程[external procedure]或外部函数[external function],这些外部过程及函数存储于共享库[shared library]中。外部 C 程序与 Oracle 数据库服务器运行于不同的地址空间[address space]中。
See Also:

Oracle Database Application Developer's Guide - Fundamentals for more information about external procedures

Oracle Database Application Developer's Guide - Fundamentals 了解更多关于外部过程的信息

Table Functions 表函数

215 Table functions are functions that can produce a set of rows as output. In other words, table functions return a collection type instance (nested table and VARRAY datatypes). You can use a table function in place of a regular table in the FROM clause of a SQL statement.
表函数[table function]指能够产生一个数据行集作为输出的函数。即表函数返回的是一个集合类型的实例[collection type instance](嵌套表[nested table]或 VARRAY 数据类型)。用户可以在 SQL 语句的 FROM 字句中以使用常规表的方式使用表函数 的结果集。
216 Oracle allows table functions to pipeline results (act like an Oracle rowsource) out of the functions. This can be achieved by either providing an implementation of the ODCITable interface, or using native PL/SQL instructions.
表函数能够以管道[pipeline]的形式输出结果(如同一个 Oracle 数据行数据源[rowsource])。此特性可以通过 ODCITable 接口实现,也可以通过本地 PL/SQL 指令[native PL/SQL instruction]实现。
217 Pipelining helps to improve the performance of a number of applications, such as Oracle Warehouse Builder (OWB) and cartridges groups.
管道方式能够提高 Oracle Warehouse Builder(OWB)及 cartridges groups 等多种应用程序的性能。
218 The ETL (Extraction-Transformation-Load) process in data warehouse building extracts data from an OLTP system. The extracted data passes through a sequence of transformations (written in procedural languages, such as PL/SQL) before it is loaded into a data warehouse.
数据仓库建设的 ETL(Extraction-Transformation-Load,抽取-转换-加载)过程需要从 OLTP 系统中抽取数据。被抽取的数据在加载到数据仓库前需要经过一系列的转换(以过程语言实现,例如 PL/SQL)。
219 Oracle also allows parallel execution of table and non-table functions. Parallel execution provides the following extensions:
  • Functions can directly accept a set of rows corresponding to a subquery operand.
  • A set of input rows can be partitioned among multiple instances of a parallel function. The function developer specifies how the input rows should be partitioned between parallel instances of the function.
Oracle 支持并行执行表函数及非表函数。并行执行具有以下特点:
  • 函数可以直接使用子查询操作返回的结果集。
  • 一个输入数据集合可以被分配到多个并行运行的函数实例中。开发者在编写函数时需要设定如何在函数实例间分配输入数据。
220 Thus, table functions are similar to views. However, instead of defining the transform declaratively in SQL, you define it procedurally in PL/SQL. This is especially valuable for the arbitrarily complex transformations typically required in ETL.
表函数的作用于视图类似。但视图使用 SQL 语句定义数据转换,而表函数则使用过程化的 PL/SQL。此特性适合实现复杂的数据转换,例如完成 ETL 中的转换工作。

See Also:



PL/SQL Packages PL/SQL 包

223 A package is a group of related procedures and functions, along with the cursors and variables they use, stored together in the database for continued use as a unit. Similar to standalone procedures and functions, packaged procedures and functions can be called explicitly by applications or users.
224 Oracle supplies many PL/SQL packages with the Oracle database server to extend database functionality and provide PL/SQL access to SQL features. For example, the ULT_HTTP supplied package enables HTTP callouts from PL/SQL and SQL to access data on the Internet or to call Oracle Web Server Cartridges. You can use the supplied packages when creating your applications or for ideas in creating your own stored procedures.
Oracle 数据库服务器提供了多个 PL/SQL 包,用于扩展数据库功能。例如,用户可以在 PL/SQL 及 SQL 中使用 ULT_HTTP 包发送 HTTP 请求访问 Internet 上的数据,或调用 Oracle Web Server 模块[cartridge]。用户开发应用程序时,可以在自定义的存储过程中使用 Oracle 提供的 PL/SQL 包。
225 You create a package in two parts: the specification and the body. The package specification declares all public constructs of the package and the body defines all constructs (public and private) of the package. This separation of the two parts provides the following advantages:
  • You have more flexibility in the development cycle. You can create specifications and reference public procedures without actually creating the package body.

    You can alter procedure bodies contained within the package body separately from their publicly declared specifications in the package specification. As long as the procedure specification does not change, objects that reference the altered procedures of the package are never marked invalid. That is, they are never marked as needing recompilation.
包在创建时分为两部分:包规范与包体。包规范[package specification]用于声明所有公有程序结构,而包体则用于对所有程序结构进行定义(包括公有及私有)。将包分为两部分具有以下优势:
  • 可与使开发周期更灵活。用户可以首先声明包规范而不定义包体,此时已声明的公有过程已经可以被其他过程引用。
  • 用户可以分别修改一个过程在包体内的定义及在包规范中的声明。只要在包规范中的过程声明没有发生变化,引用了包中被修改过程的对象就不会被标志为无效状态[invalid]。因此避免了不必要的重新编译工作。
226 Figure 24-4 illustrates a package that encapsulates a number of procedures used to manage an employee database.
图 24-4 显示了一个用于管理员工数据库的包,其中包含了数个过程。
227 Figure 24-4 A Stored Package
图 24-4 存储包


Figure 24-4 is described in the text.
图 24-4 将在下文中描述。
230 Database applications explicitly call packaged procedures as necessary. After being granted the privileges for the employees_management package, a user can explicitly run any of the procedures contained in it. For example, Oracle Enterprise Manager or SQL*Plus can issue the following statement to run the hire_employees package procedure:
数据库应用程序可以显式地调用包内的过程。当用户被授予了访问 employees_management 包的权限后,就可以显式地执行其中所有的过程。例如,在 Oracle 企业管理器或 SQL*Plus 中可以执行以下语句来运行 employees_management 包的过程:
EXECUTE employees_management.hire_employees ('TSMITH', 'CLERK', 1037, SYSDATE, 500, NULL, 20);
EXECUTE employees_management.hire_employees ('TSMITH', 'CLERK', 1037, SYSDATE, 500, NULL, 20);

See Also:



Benefits of Packages 包的优势

234 Packages provide advantages in the following areas:
  • Encapsulation of related procedures and variables

    Stored packages allow you to encapsulate or group stored procedures, variables, datatypes, and so on in a single named, stored unit in the database. This provides better organization during the development process. Encapsulation of procedural constructs also makes privilege management easier. Granting the privilege to use a package makes all constructs of the package accessible to the grantee.
  • Declaration of public and private procedures, variables, constants, and cursors

    The methods of package definition allow you to specify which variables, cursors, and procedures are public and private. Public means that it is directly accessible to the user of a package. Private means that it is hidden from the user of a package.

    For example, a package can contain 10 procedures. You can define the package so that only three procedures are public and therefore available for execution by a user of the package. The remainder of the procedures are private and can only be accessed by the procedures within the package. Do not confuse public and private package variables with grants to PUBLIC.
  • 封装相关的过程与变量

    使用存储包[stored package]用户可以将存储过程,变量,数据类型等程序结构封装[encapsulate](或称为组合)为一个命名的程序单元并存储在数据库中。这使开发过程更易管理。此外,将过程封装还能使权限管理更简单。当授予用户访问包的权限后,用户就可以访问包内的所有程序结构。
  • 声明公有或私有的过程,变量,常量及游标


    例如,一个包中包含 10 个过程。用户可以将其中的 3 个声明为公有的,包用户可以直接使用。其余过程声明为私有,只能被包内的过程访问。注意,不要将公有及私有包程序结构与授权给 PUBLIC 相混淆。
See Also:

Chapter 20, "Database Security" for more information about grants to PUBLIC

第 20 章,“数据库安全”了解授权给 PUBLIC 的含义
  • Better performance

    An entire package is loaded into memory when a procedure within the package is called for the first time. This load is completed in one operation, as opposed to the separate loads required for standalone procedures. Therefore, when calls to related packaged procedures occur, no disk I/O is necessary to run the compiled code already in memory.

    A package body can be replaced and recompiled without affecting the specification. As a result, schema objects that reference a package's constructs (always through the specification) need not be recompiled unless the package specification is also replaced. By using packages, unnecessary recompilations can be minimized, resulting in less impact on overall database performance.
  • 性能更佳

    当包内的某个过程第一次被调用时,整个包将被加载到内存中。这个加载操作只需执行一次,而对于分散的独立过程[standalone procedure]则需要执行多次操作。因此,当包内的其他过程被调用时其编译代码已经存在于内存中,而无需进行磁盘 I/O 操作。


PL/SQL Collections and Records PL/SQL 集合与记录

238 Many programming techniques use collection types such as arrays, bags, lists, nested tables, sets, and trees. To support these techniques in database applications, PL/SQL provides the datatypes TABLE and VARRAY, which allow you to declare index-by tables, nested tables, and variable-size arrays.
很多编程方法都需要使用数组[array],包[bag],列表[list],嵌套表[nested table],集[set],树[tree]等集合类型[collection type]。为了在数据库应用程序中支持这些编程方法,PL/SQL 提供了 TABLEVARRAY 数据类型,开发者使用这些数据类型能够定义索引表[index-by table],嵌套表,及变长数组[ariable-size array]。

Collections 集合

240 A collection is an ordered group of elements, all of the same type. Each element has a unique subscript that determines its position in the collection.
241 Collections work like the arrays found in most third-generation programming languages. Also, collections can be passed as parameters. So, you can use them to move columns of data into and out of database tables or between client-side applications and stored subprograms.
集合与大多数第三代编程语言中的数组类似。此外集合可以被作为参数传递。因此,开发者可以使用集合数据类型在数据库表间或客户端应用程序与服务端存储过程间传递多项数据[columns of data]。

Records 记录

243 You can use the %ROWTYPE attribute to declare a record that represents a row in a table or a row fetched from a cursor. But, with a user-defined record, you can declare fields of your own.
用户可以使用 %ROWTYPE 属性声明记录[record],此种数据类型可以表示从数据表或游标中获取的数据行。而使用用户定义的记录类型时,还可以定义额外的字段。
244 Records contain uniquely named fields, which can have different datatypes. Suppose you have various data about an employee such as name, salary, and hire date. These items are dissimilar in type but logically related. A record containing a field for each item lets you treat the data as a logical unit.
See Also:

Oracle Database PL/SQL User's Guide and Reference for detailed information on using collections and records

Oracle Database PL/SQL User's Guide and Reference 了解关于使用集合与记录的详细信息

PL/SQL Server Pages PL/SQL Server Pages

247 PL/SQL Server Pages (PSP) are server-side Web pages (in HTML or XML) with embedded PL/SQL scripts marked with special tags. To produce dynamic Web pages, developers have usually written CGI programs in C or Perl that fetch data and produce the entire Web page within the same program. The development and maintenance of such dynamic pages is costly and time-consuming.
PL/SQL Server Pages(PSP)是运行在服务端的 Web 页面,其中可以嵌入以特殊标签[tag]标记的 PL/SQL 脚本。以往开发者在生成动态页面时,需要使用 C 或 Perl 语言编写 CGI 程序,获取数据并生成页面。这样的动态页面的开发及维护成本高昂,且耗费时间。
248 Scripting fulfills the demand for rapid development of dynamic Web pages. Small scripts can be embedded in HTML pages without changing their basic HTML identity. The scripts contain the logic to produce the dynamic portions of HTML pages and are run when the pages are requested by the users.
采用脚本能够满足快速开发动态 Web 页面的需求。脚本可以被嵌入到 HTML 页面中,而不会对页面上的 HTML 对象产生影响。脚本中可以包含生成 HTML 页面动态内容的逻辑,在用户请求页面时执行。
249 The separation of HTML content from application logic makes script pages easier to develop, debug, and maintain. The simpler development model, along the fact that scripting languages usually demand less programming skill, enables Web page writers to develop dynamic Web pages.
由于脚本能够将 HTML 内容与应用程序逻辑分离,因此采用脚本的页面更易于开发,调试,及维护。采用脚本后开发模型更为简单,且脚本语言对编程技术的要求更低,使得只熟悉 Web 页面的人员也能够开发动态 Web 页面。
250 There are two kinds of embedded scripts in HTML pages: client-side scripts and server-side scripts. Client-side scripts are returned as part of the HTML page and are run in the browser. They are mainly used for client-side navigation of HTML pages or data validation. Server-side scripts, while also embedded in the HTML pages, are run on the server side. They fetch and manipulate data and produce HTML content that is returned as part of the page. PSP scripts are server-side scripts.
HTML 页面中可以嵌入两类脚本:客户端脚本[client-side script]与服务端脚本[server-side script]。客户端脚本作为 HTML 页面的一部分返回到浏览器并在其中运行。客户端脚本的作用包括客户端 HTML 页面导航,数据验证等。服务端脚本也嵌入在 HTML 页面中,但运行于服务端。服务端脚本能够获取及操作数据,并生成 HTML 内容作为页面的一部分返回到浏览器。PSP 脚本属于服务端脚本。
251 A PL/SQL gateway receives HTTP requests from an HTTP client, invokes a PL/SQL stored procedure as specified in the URL, and returns the HTTP output to the client. A PL/SQL Server Page is processed by a PSP compiler, which compiles the page into a PL/SQL stored procedure. When the procedure is run by the gateway, it generates the Web page with dynamic content. PSP is built on one of two existing PL/SQL gateways:
  • PL/SQL cartridge of Oracle Application Server
  • WebDB
PL/SQL 网关[gateway ]的作用是从 HTTP 客户端接收 HTTP 请求,调用 URL 中指定的存储过程,并向客户端返回 HTTP 输出结果。PL/SQL Server Page 由 PSP 编译器处理,编译器将 PSP 页面编译为存储过程。当网关运行 PSP 页面对应的存储过程,就能够生成包含动态内容的 Web 页面。Oracle 提供了两种处理 PSP 的 PL/SQL 网关:
  • Oracle Application Server 中的 PL/SQL cartridge
  • WebDB
See Also:

Oracle Database Application Developer's Guide - Fundamentals for more information about PL/SQL Server Pages

Oracle Database Application Developer's Guide - Fundamentals 了解更多关于 PL/SQL Server Pages 的信息

Overview of Java

24.2.2 Java 概述

254 Java is an object-oriented programming language efficient for application-level programs. It includes the following features:
  • A Java Virtual Machine (JVM), which provides the fundamental basis for platform independence
  • Automatic storage management techniques, such as gathering scattered memory into contiguous memory space
  • Language syntax that borrows from C and enforces strong typing
Java 是一种面向对象的编程语言,适于开发应用型的程序[application-level program]。Java 包含以下特性:
  • Java 虚拟机[Java Virtual Machine,JVM],提供了 实现平台独立性的基础
  • 自动存储管理技术,例如将分散的可用内存收集为连续的存储空间
  • 从 C 语言中继承的语法,并在强类型语言[strong typing]方面进行了加强
255 This section contains the following topics: 本节包含以下主题:

Java and Object-Oriented Programming Terminology Java 及面向对象编程技术术语

257 This section covers some basic terminology of Java application development in the Oracle environment.
本节介绍在 Oracle 环境下进行 Java 应用程序开发的基础术语。

Classes 类

259 All object-oriented programming languages support the concept of a class. As with a table definition, a class provides a template for objects that share common characteristics. Each class can contain the following:
  • Attributes—static or instance variables that each object of a particular class possesses
  • Methods—you can invoke methods defined by the class or inherited by any classes extended from the class
所有面向对象语言中都包含类[class]的概念。与表定义[table definition]类似,类是具有共同特性的对象的模版。类中包含以下元素:
  • 属性[attribute]——类的实例[instance]所拥有的静态变量[static variable]或实例变量[instance variable]
  • 方法[method]——类中可以定义方法,也可以继承其他类中的方法,这些方法可供用户调用
260 When you create an object from a class, you are creating an instance of that class. The instance contains the fields of an object, which are known as its data, or state.
261 Figure 24-5 shows an example of an Employee class defined with two attributes: last name (lastName) and employee identifier (ID).
图 24-5 是 Employee 类的示例,此类中包含两个属性:姓氏(lastName)和员工编号(ID)。
262 Figure 24-5 Classes and Instances
图 24-5 类与实例


Figure 24-5 shows an example of an Employee class defined with two attributes: last name (lastName) and employee identifier (ID). The Employee class defines the fields that instances hold (state) and methods you can invoke on instances of Employee (behavior). Each instance of Employee holds its own state. You can access that state only if the creator of the class defines it in a way that provides access to you.
图 24-5 为 Employee 类的示例,此类中定义了两个属性:姓氏(lastName)和员工编号(ID)。Employee 类定义了其实例应包含的字段(状态),以及可供用户调用的方法(行为)。Employee 类的每个实例都具有属于自己的状态。只有类的设计者提供了访问实例属性的方法后,用户才能存取这些属性。
265 When you create an instance, the attributes store individual and private information relevant only to the employee. That is, the information contained within an employee instance is known only for that single employee. The example in Figure 24-5 shows two instances of employee—Smith and Jones. Each instance contains information relevant to the individual employee.
当用户创建实例时,实例的属性中将存储特定员工的私有信息。即一个员工实例所包含的是关于特定员工的信息。图 24-5 所示的例子中有两个 Employee 类的实例 — Smith 和 Jones。这两个实例分别包含两个员工各自的信息。

Attributes 属性

267 Attributes within an instance are known as fields. Instance fields are analogous to the fields of a relational table row. The class defines the fields, as well as the type of each field. You can declare fields in Java to be static, public, private, protected, or default access.
  • Public, private, protected, or default access fields are created within each instance.
  • Static fields are like global variables in that the information is available to all instances of the employee class.
实例中的属性[attributes]也被称为字段[field]。实例字段与关系型数据表中每行的字段类似。在类中需要定义字段及字段的数据类型。在 Java 中,用户声明字段时可以指定其属性为静态[static],公有[public],私有[private],保护[protected],或默认访问[default access]。
  • 属性为公有,私有,保护,或默认访问的字段将在类的每个实例中创建。
  • 静态字段与全局变量[global variable]类似,其中存储的信息可供 Employee 类的所有实例访问。
268 The language specification defines the rules of visibility of data for all fields. Rules of visibility define under what circumstances you can access the data in these fields.
Java 语言规范定义了类字段数据的可见性规则。可见性规则定义了在何种情况下用户可以访问字段的数据。

Methods 方法

270 The class also defines the methods you can invoke on an instance of that class. Methods are written in Java and define the behavior of an object. This bundling of state and behavior is the essence of encapsulation, which is a feature of all object-oriented programming languages. If you define an Employee class, declaring that each employee's id is a private field, other objects can access that private field only if a method returns the field. In this example, an object could retrieve the employee's identifier by invoking the Employee.getId method.
在类中还需定义方法[method],用户通过调用方法来操作类的实例。方法使用 Java 代码定义,说明了对象的行为。封装[encapsulation]的实质就是将状态[state]与行为[behavior]捆绑在一起,这也是所有面向对象编程语言共有的特性。例如,用户在定义 Employee 类时将 id 声明为私有字段,其他对象只能通过 Employee 类提供的方法访问私有字段的数据。在前面的示例中,用户需要调用 Employee.getId 方法才能获得员工标识数据。
271 In addition, with encapsulation, you can declare that the Employee.getId method is private, or you can decide not to write an Employee.getId method. Encapsulation helps you write programs that are reusable and not misused. Encapsulation makes public only those features of an object that are declared public; all other fields and methods are private. Private fields and methods can be used for internal object processing.
此外,用户在封装 Employee 类时可以将 Employee.getId 方法声明为私有的,或不编写 Employee.getId 方法。用户通过封装技术能够编写出可重用且不会被误用的程序。经过封装后,只有被声明为公有的对象元素才能供所有用户使用;剩余的对象元素为私有的。私有字段及方法只能在对象内部使用。

Class Hierarchy 类的层次关系

273 Java defines classes within a large hierarchy of classes. At the top of the hierarchy is the Object class. All classes in Java inherit from the Object class at some level, as you walk up through the inheritance chain of superclasses. When we say Class B inherits from Class A, each instance of Class B contains all the fields defined in class B, as well as all the fields defined in Class A. For example, in Figure 24-6, the FullTimeEmployee class contains the id and lastName fields defined in the Employee class, because it inherits from the Employee class. In addition, the FullTimeEmployee class adds another field, bonus, which is contained only within FullTimeEmployee.
在 Java 中定义的类都属于一个巨大的类层次关系中的一环。在这个层次关系的顶端是 Object 类。沿着任意一个 Java 类的超类继承链向上追溯,就会发现她们均继承自 Object 类。如果我们说 B 类继承自 A 类,那么 B 类的每个实例既包含 B 类中定义的字段,也包含在 A 类中定义的字段。例如 图 24-6 所示,FullTimeEmployee 类中包含了在 Employee 类中定义的 idlastName 字段,因为前者继承自后者。此外 FullTimeEmployee 类中还定义了新字段 bonus,此字段只包含于 FullTimeEmployee 类中。
274 You can invoke any method on an instance of Class B that was defined in either Class A or B. In our employee example, the FullTimeEmployee instance can invoke methods defined only within its own class, or methods defined within the Employee class.
用户可以对 B 类的实例调用定义在 B 类及 A 类上的方法。在上述示例中,对于 FullTimeEmployee 类的实例,既可以调用 FullTimeEmployee 类中定义的方法,也可以调用 Employee 类中定义的方法。
275 Figure 24-6 Using Inheritance to Localize Behavior and State
图 24-6 根据继承关系定位行为和状态



Figure 24-6 shows the Employee class and its subclasses.

The Employee class has two subclasses, PartTimeEmployee and FullTimeEmployee, rather than using attributes of Employee to differentiate between different Employee types. Part-time employees have to track their schedules, while full-time employees are eligible for bonuses. Each full time employee is considered "exempt" if he works for a monthly salary, or "non-exempt" if he works at an hourly rate. Each one computes salaryToDate differently.
图 24-6 显示了 Employee 类和其子类。

Employee 类有两个子类,PartTimeEmployee 类和 FullTimeEmployee 类,因此不必使用 Employee 类的属性来区分不同类型的员工。对于兼职员工需要记录其工作时间,而对于全职员工则需要判断是否符合发放奖金的条件。对于全职员工,如果领取月薪则被视为可豁免员工,如按小时计薪则被视为不可豁免员工。可豁免员工和不可豁免员工均使用 salaryToDate 方法计薪,但算法不同。
278 Instances of Class B are substitutable for instances of Class A, which makes inheritance another powerful construct of object-oriented languages for improving code reuse. You can create new classes that define behavior and state where it makes sense in the hierarchy, yet make use of pre-existing functionality in class libraries.
B 类的实例可以代替[substitutable] A 类的实例,因此继承是面向对象语言实现代码重用的重要方式。用户在定义新类时可以定义与之相关的行为和状态,同时充分发挥其超类中已定义的功能。

Interfaces 接口

280 Java supports only single inheritance; that is, each class has one and only one class from which it inherits. If you must inherit from more than one source, Java provides the equivalent of multiple inheritance, without the complications and confusion that usually accompany it, through interfaces. Interfaces are similar to classes; however, interfaces define method signatures, not implementations. The methods are implemented in classes declared to implement an interface. Multiple inheritance occurs when a single class simultaneously supports many interfaces.
Java 只支持单一继承[single inheritance],即每个类有且只有一个父类。如果用户定义的类必须继承自多个源,Java 提供了接口[interface]作为多重继承[multiple inheritance]的替代解决方法,此方法避免了多重继承造成的复杂性和易混淆性。接口与类相似,但接口只定义方法,而不提供具体实现[implementation]。方法是在实现了接口的类中实现的。如果一个类同时支持多个接口,则相当于进行了多重继承。

Polymorphism 多态性

282 Assume in our Employee example that the different types of employees must be able to respond with their compensation to date. Compensation is computed differently for different kinds of employees.
  • FullTimeEmployees are eligible for a bonus
  • NonExemptEmployees get overtime pay
在上述 Employee 类的例子中,不同类型的员工都需要根据其工作时间计算报酬。而不同类型员工的报酬计算方式均有所不同。
  • FullTimeEmployees 需要判断是否符合发放奖金的条件
  • NonExemptEmployees 需要发放加班薪金
283 In traditional procedural languages, you would write a long switch statement, with the different possible cases defined.
在传统的过程化语言中,开发者需要使用 switch ... case ... 语句处理各种可能的情况。
switch (employee.type) {
case: Employee
return employee.salaryToDate;
case: FullTimeEmployee
return employee.salaryToDate + employee.bonusToDate;
switch (employee.type) {
case: Employee
return employee.salaryToDate;
case: FullTimeEmployee
return employee.salaryToDate + employee.bonusToDate;
285 If you add a new kind of employee, then you must update your switch statement. If you modify your data structure, then you must modify all switch statements that use it.
如果出现了新的员工类型,开发者必须修改 switch 语句。如果修改了数据结构,则必须修改所有 switch 语句以适应新的数据结构。
286 In an object-oriented language such as Java, you implement a method, compensationToDate, for each subclass of Employee class that requires any special treatment beyond what is already defined in Employee class. For example, you could implement the compensationToDate method of NonExemptEmployee, as follows:
在 Java 等面向对象语言中,用户需要在 Employee 类的基础上为其每个子类实现 compensationToDate 方法,以处理每个子类的特殊情况。例如,开发者应该为 NonExemptEmployee 按以下方式实现 compensationToDate 方法:
private float compensationToDate() {
  return super.compensationToDate() + this.overtimeToDate();
private float compensationToDate() {
  return super.compensationToDate() + this.overtimeToDate();
288 Implement FullTimeEmployee's method as follows:
按以下方式实现 FullTimeEmployee 类的 compensationToDate 方法:
private float compensationToDate() {
  return super.compensationToDate() + this.bonusToDate();
private float compensationToDate() {
  return super.compensationToDate() + this.bonusToDate();
290 The common usage of the method name compensationToDate lets you invoke the identical method on different classes and receive different results, without knowing the type of employee you are using. You do not have to write a special method to handle FullTimeEmployees and PartTimeEmployees. This ability for the different objects to respond to the identical message in different ways is known as polymorphism.
由于所有子类都具有 compensationToDate 方法,因此开发者可以调用不同子类的同名方法,得到不同的结果,且无需了解正在处理何种类型的员工。开发者无需编写特殊代码来分别处理 FullTimeEmployees 类和 PartTimeEmployees 类。这种令不同对象响应相同消息而获得不同结果的功能被称为多态性[polymorphism]。
291 In addition, you could create an entirely new class that does not inherit from Employee at all—Contractor—and implement a compensationToDate method in it. A program that calculates total payroll to date would iterate over all people on payroll, regardless of whether they were full-time, part-time, or contractors, and add up the values returned from invoking the compensationToDate method on each. You can safely make changes to the individual compensationToDate methods with the knowledge that callers of the methods will work correctly. For example, you can safely add new fields to existing classes.
此外,开发者可以创建一个新类 Contractor(不继承自 Employee 类),并实现一个 compensationToDate 方法。根据日期计算总薪水的应用程序可以遍历薪水册中的所有人员,分别调用其 compensationToDate 方法并对返回的结果求和,而无需关心此人为全职,兼职,或承包人。开发者可以修改任意类的 compensationToDate 方法的实现,而不会对方法的调用者产生影响。例如,开发者可以向已有类中添加字段。

Overview of the Java Virtual Machine (JVM) Java 虚拟机[JVM]概述

293 As with other high-level computer languages, Java source compiles to low-level instructions. In Java, these instructions are known as bytecodes (because their size is uniformly one byte of storage). Most other languages—such as C—compile to computer-specific instructions, such as instructions specific to an Intel or HP processor. Java source compiles to a standard, platform-independent set of bytecodes, which interacts with a Java Virtual Machine (JVM). The JVM is a separate program that is optimized for the specific platform on which you run your Java code.
与其他高阶(high-level)计算机语言不同,Java 源代码将被编译为低阶(low-level)指令[instruction]。在 Java 中,这种指令被称为字节码[bytecode](因为这种指令的存储容量均为一字节)。C 之类的编程语言语言在不同体系结构的计算机上将被编译为不同的指令,例如 Intel 或 HP 处理器特有的指令。而 Java 源代码将被编译为标准的,与平台无关的字节码,这种字节码运行在 Java 虚拟机[Java Virtual Machine,JVM]之上。JVM 是一种特殊的程序,对特定平台进行了优化,供用户执行 Java 代码。
294 Figure 24-7 illustrates how Java can maintain platform independence. Java source is compiled into bytecodes, which are platform independent. Each platform has installed a JVM that is specific to its operating system. The Java bytecodes from your source get interpreted through the JVM into appropriate platform dependent actions.
图 24-7 显示了 Java 如何保证平台独立性。Java 源代码被编译为与平台独立的字节码。不同的平台上均安装与之操作系统对应的 JVM。JVM 将 Java 源代码编译成的字节码解释为特定平台上的操作。
295 Figure 24-7 Java Component Structure
图 24-7 Java 组件结构



Figure 24-7 shows three boxes in a vertical line. Java Applications is in the top box, Java Virtual Machine is in the middle box, and Operating System is in the bottom box. There is a double arrow pointing between each box.
图 24-7 显示了三个垂直排列的长方形。Java 应用程序位于顶端,Java 虚拟机在中间,操作系统位于底端。相邻长方形间由双向箭头连接。
298 When you develop a Java program, you use predefined core class libraries written in the Java language. The Java core class libraries are logically divided into packages that provide commonly-used functionality, such as basic language support (java.lang), I/O (java.io), and network access (java.net). Together, the JVM and core class libraries provide a platform on which Java programmers can develop with the confidence that any hardware and operating system that supports Java will execute their program. This concept is what drives the "write once, run anywhere" idea of Java.
当用户开发 Java 程序时,需要使用以 Java 语言编写的核心类库[core class library]。Java 核心类库在逻辑上被分为多个包,每个包提供一组通用的功能,例如基本语言支持(java.lang),I/O(java.io),及网络访问等(java.net)。JVM 与核心类库共同构成一个平台,Java 程序员在此基础上开发的程序可以运行于任何支持 Java 的硬件及操作系统上。这体现了 Java“一次编写,随处运行”的理念。
299 Figure 24-8 illustrates how Oracle's Java applications sit on top of the Java core class libraries, which in turn sit on top of the JVM. Because Oracle's Java support system is located within the database, the JVM interacts with the Oracle database libraries, instead of directly with the operating system.
图 24-8 显示了 Oracle Java 应用程序构建于 Java 核心类库之上,而后者又运行在 JVM 上。由于 Oracle 的 Java 支持组件内建于数据库中,因此 JVM 与 Oracle 数据库程序库[Oracle database library]交互,而非直接与操作系统交互。
300 Figure 24-8 Java Component Structure
图 24-8 Java 组件结构



Figure 24-8 shows six boxes in a vertical line: Data/Persistence Logic, JDBC, Java Core Class Libraries, Oracle Database JVM, Oracle Database Libraries, and Operating System. JDBC and Java Core Class Libraries are boxed together.
图 24-8 显示了六个垂直排列的长方形,由上至下依次为:数据/持久性逻辑,JDBC,Java 核心类库,Oracle 数据库 JVM,Oracle 数据库程序库,及操作系统。JDBC 与 Java 核心类库被一个长方形同时包含。
303 Sun Microsystems furnishes publicly available specifications for both the Java language and the JVM. The Java Language Specification (JLS) defines things such as syntax and semantics; the JVM specification defines the necessary low-level behavior for the computer that runs the bytecodes. In addition, Sun Microsystems provides a compatibility test suite for JVM implementors to determine if they have complied with the specifications. This test suite is known as the Java Compatibility Kit (JCK). Oracle's JVM implementation complies fully with JCK. Part of the overall Java strategy is that an openly specified standard, together with a simple way to verify compliance with that standard, allows vendors to offer uniform support for Java across all platforms.
Sun 微系统公司[Sun Microsystems]已经公开发布了 Java 语言及 JVM 的规范。Java 语言规范[Java Language Specification,JLS]定义了 Java 的语法及语义;而 JVM 规范则定义了运行字节码的计算机必须具备的低阶行为[low-level behavior]。此外,Sun 微系统公司还提供了兼容性测试套件,供 JVM 开发者测试其产品是否符合规范要求。这个测试套件被称为 Java 兼容性工具包[Java Compatibility Kit,JCK]。Oracle 的 JVM 实现完全通过了 JCK 的测试。Java 的一个基本理念就是提供一个公开的标准,并提供一个验证是否满足标准的简单方法,确保第三方厂商在各种平台上均能实现符合标准的 Java 环境。

Why Use Java in Oracle? 为何在 Oracle 中使用 Java?

305 You can write and load Java applications within the database, because it is a safe language. Java prevents anyone from tampering with the operating system that the Java code resides in. Some languages, such as C, can introduce security problems within the database; Java, because of its design, is a safe language to allow within the database.
用户可以在数据库中开发并加载 Java 应用程序,因为 Java 是一种安全的语言。Java 程序代码运行时不会影响其所在的操作系统。而有些语言,例如 C 语言,可能会给数据库带来安全问题。Java 的设计思想确保了她是一种能够保证数据库安全的语言。
306 Although Java presents many advantages to developers, providing an implementation of a JVM that supports Java server applications in a scalable manner is a challenge. This section discusses some of these challenges. Java 能够为开发者提供很多帮助,但如何提供一个以可伸缩方式支持 Java 服务端应用程序的 JVM 实现是一个难点。本节将讨论与此相关的问题。

Multithreading 多线程

308 Multithreading support is often cited as one of the key scalability features of Java. Certainly, the Java language and class libraries make it simpler to write shared server applications in Java than many other languages, but it is still a daunting task in any language to write reliable, scalable shared server code.
多线程[multithreading]支持是 Java 中一项重要的可伸缩特性。Java 与其他语言相比,开发者利用其语言特性和类库更易于编写共享服务应用程序[shared server application],但是以任何语言实现一个可伸缩,且可靠的共享服务应用程序都是一个困难的任务。
309 As a database server, Oracle efficiently schedules work for thousands of users. The Oracle JVM uses the facilities of the RDBMS server to concurrently schedule Java execution for thousands of users. Although Oracle supports Java language level threads required by the JLS and JCK, using threads within the scope of the database does not increase scalability. Using the embedded scalability of the database eliminates the need for writing shared server Java servers. You should use the database's facilities for scheduling users by writing single-threaded Java applications. The database takes care of the scheduling between each application; thus, you achieve scalability without having to manage threads. You can still write shared server Java applications, but multiple Java threads does not increase your server's performance.
作为一个数据库服务器,Oracle 能够高效地调度大量用户的操作。Oracle JVM 能够利用 RDBMS 服务器的功能并发地调度大量用户的 Java 执行操作。Oracle 支持 Java 语言规范中的多线程特性,且满足 JLS 及 JCK 的要求,但在数据库内进行多线程执行并不能增加应用程序的可伸缩性。开发者应该使用数据库提供的可伸缩特性,避免编写共享服务的 Java 服务端应用程序。开发者应该编写单线程的 Java 应用程序,再利用数据库的可伸缩特性调度大量用户对单线程 Java 应用程序的并行执行。此时数据库负责应用程序的调度,即开发者无需通过多线程就能实现可伸缩性。虽然开发者可以编写共享服务的 Java 服务端应用程序,但这样的程序无法提高系统的整体性能。
310 One difficulty multithreading imposes on Java is the interaction of threads and automated storage management, or garbage collection. The garbage collector executing in a generic JVM has no knowledge of which Java language threads are executing or how the underlying operating system schedules them.
  • Non-Oracle model—A single user maps to a single Java language level thread; the same single garbage collector manages all garbage from all users. Different techniques typically deal with allocation and collection of objects of varying lifetimes and sizes. The result in a heavily shared server application is, at best, dependent upon operating system support for native threads, which can be unreliable and limited in scalability. High levels of scalability for such implementations have not been convincingly demonstrated.
  • Oracle JVM model—Even when thousands of users connect to the server and run the same Java code, each user experiences it as if he is executing his own Java code on his own Java Virtual Machine. The responsibility of the Oracle JVM is to make use of operating system processes and threads, using the scalable approach of the Oracle RDBMS. As a result of this approach, the JVM's garbage collector is more reliable and efficient because it never collects garbage from more than one user at any time.
在 Java 中实现多线程的困难包括线程间的交互以及自动存储管理[automated storage management](或称为资源回收[garbage collection])。在 JVM 中执行的资源回收器[garbage collector]无法确定当前哪些 Java 线程正在执行,以及底层操作系统是如何调度这些线程的。
  • 非 Oracle 模型——每个用户均与一个 Java 线程对应,一个资源回收器管理所有的用户资源。资源回收器需要依据对象的生命周期及大小采用不同的技术分配及回收对象资源。在一个共享服务的应用程序中,能否获得最佳性能依赖于底层操作系统对线程的支持,而操作系统的原生线程特性可能存在不可靠或缺乏可伸缩性的问题。采用这种方式实现的共享服务应用程序的可伸缩性无法被保证。
  • Oracle JVM 模型——即使大量用户连接到数据库服务器并同时执行相同的 Java 代码,在效果上与每个用户使用独立的 JVM 独立执行相同。Oracle JVM 利用 Oracle RDBMS 实现可伸缩性的方法管理操作系统进程及线程。采用此模型时,JVM 的资源回收器工作更可高也更为高效,此时她只需对一个用户的资源进行管理。

Automated Storage Management 自动存储管理

312 Garbage collection is a major feature of Java's automated storage management, eliminating the need for Java developers to allocate and free memory explicitly. Consequently, this eliminates a large source of memory leaks that commonly plague C and C++ programs. There is a price for such a benefit: garbage collection contributes to the overhead of program execution speed and footprint. Although many papers have been written qualifying and quantifying the trade-off, the overall cost is reasonable, considering the alternatives.
资源回收[garbage collection]是 Java 中一项重要的自动存储管理[automated storage management]特性,此特性使 Java 开发者不必显式地分配及释放内存。这消除了 C 及 C++ 程序中常见的内存泄漏[memory leak]问题。但此特性也带来了额外的问题:资源回收将影响程序的执行速度及空间使用。尽管有许多文章在争论资源回收机制的优劣,但与其他机制相比资源回收 造成的开销还是可接受的。
313 Garbage collection imposes a challenge to the JVM developer seeking to supply a highly scalable and fast Java platform. The Oracle JVM meets these challenges in the following ways:
  • The Oracle JVM uses the Oracle scheduling facilities, which can manage multiple users efficiently.
  • Garbage collection is performs consistently for multiple users because garbage collection is focused on a single user within a single session. The Oracle JVM enjoys a huge advantage because the burden and complexity of the memory manager's job does not increase as the number of users increases. The memory manager performs the allocation and collection of objects within a single session—which typically translates to the activity of a single user.
  • The Oracle JVM uses different garbage collection techniques depending on the type of memory used. These techniques provide high efficiency and low overhead.
对于在 JVM 上开发具备高可伸缩性及高性能应用的开发者来说,必须解决资源回收带来的问题。Oracle JVM 通过以下方式帮助开发者解决资源回收问题:
  • Oracle JVM 利用 Oracle 提供的调度功能,可以高效地管理大量用户的并发操作。
  • 在多用户环境下资源回收算法没有复杂化,因为资源回收只针对单一会话内的单一用户。当用户数量增加时,Oracle JVM 的内存管理工作的强度及复杂性不会增加。内存管理器[memory manager]所执行的对象资源分配及回收工作只针对单一会话(通常可以理解为单一用户)。
  • Oracle JVM 根据内存使用类型来选择不同的资源回收技术。这些技术能够确保高效及低开销。

Footprint 空间使用

315 The footprint of an executing Java program is affected by many factors:
  • Size of the program itself—how many classes and methods and how much code they contain.
  • Complexity of the program—the amount of core class libraries that the Oracle JVM uses as the program runs, as opposed to the program itself.
  • Amount of state the JVM uses—how many objects the JVM allocates, how large they are, and how many must be retained across calls.
  • Ability of the garbage collector and memory manager to deal with the demands of the executing program, which is often non-deterministic. The speed with which objects are allocated and the way they are held on to by other objects influences the importance of this factor.
执行 Java 程序时的空间使用情况受多种因素影响:
  • 程序自身的大小——程序定义了多少各类,类中定义的方法以及代码量.
  • 程序的复杂程度——除程序自身外,程序运行时 Oracle JVM 需要使用的核心类库。
  • JVM 需要保存的状态信息——JVM 内需要分配的对象数,对象的大小,对象间相互调用时需要保存的信息
  • 资源回收器及内存管理器应对程序执行需求的能力,此因素通常难以确定。对象的分配速度及对象的使用情况都将对此因素产生影响。
316 From a scalability perspective, the key to supporting many concurrent clients is a minimum user session footprint. The Oracle JVM keeps the user session footprint to a minimum by placing all read-only data for users, such as Java bytecodes, in shared memory. Appropriate garbage collection algorithms are applied against call and session memories to maintain a small footprint for the user's session. The Oracle JVM uses three types of garbage collection algorithms to maintain the user's session memory:
  • Generational scavenging for short-lived objects
  • Mark and lazy sweep collection for objects that exist for the life of a single call
  • Copying collector for long-lived objects—objects that live across calls within a session
从可伸缩性的角度看,支持大量并发客户端的关键是减小每个用户会话的空间使用。Oracle JVM 通过将所有只读数据(例如 Java 字节码)存储在共享内存中来实现用户会话空间使用最小化。此外,对调用及会话使用的内存空间采用适当的资源回收算法也有助于减小用户会话内存使用。Oracle JVM 提供了三种资源回收算法管理用户会话内存:
  • Generational scavenging——针对生命周期较短的对象
  • Mark and lazy sweep collection——针对单次调用期间使用的对象
  • Copying collector——针对生命周期较长的对象,例如在整个会话期间使用的对象

Performance 性能

318 Oracle JVM performance is enhanced by implementing a native compiler. Java runs platform-independent bytecodes on top of a JVM, which in turn interacts with the specific hardware platform. Any time you add levels within software, your performance is degraded. Because Java requires going through an intermediary to interpret platform-independent bytecodes, a degree of inefficiency exists for Java applications that does not exists within a platform-dependent language, such as C. To address this issue, several JVM suppliers create native compilers. Native compilers translate Java bytecodes into platform-dependent native code, which eliminates the interpreter step and improves performance.
Oracle 为不同平台分别实现了本地编译器[native compiler],从而提升了 Oracle JVM 的性能。Java 在 JVM 中运行与平台独立的字节码,而 JVM 则需要与不同的硬件平台交互。对软件划分的层次越多,对软件性能的影响就越大。Java 与平台独立的字节码需要经过中间层的解释,因此和其他与平台相关的语言(例如 C)相比有一定的性能损失。为了解决此问题,众多 JVM 提供商开发了多种本地编译器。本地编译器能够将 Java 字节码转换为与平台相关的本地代码[native code],消除了解释过程从而提升了程序的性能。
319 The following table describes two methods for native compilation.
下表描述本地编译[native compilation]的两种方式。

Native Compilation Method

Just-In-Time (JIT) Compilation
JIT compilers quickly compile Java bytecodes to native (platform-specific) computer code during runtime. This does not produce an executable to be run on the platform; instead, it provides platform-dependent code from Java bytecodes that is run directly after it is translated. This should be used for Java code that is run frequently, which will be run at speeds closer to languages such as C.
Static Compilation
Static Compilation Static compilation translates Java bytecodes to platform-independent C code before runtime. Then a standard C compiler compiles the C code into an executable for the target platform. This approach is more suitable for Java applications that are modified infrequently. This approach takes advantage of the mature and efficient platform-specific compilation technology found in modern C compilers.


JIT 编译器在运行时将 Java 字节码迅速编译为(与平台相关的)本地代码。JIT 编译器不会在平台上生成可执行文件,而是在运行前将 Java 字节码转换为与平台相关的本地代码,之后立即执行。此种编译器适用于运行频率较高的 Java 代码,经 JIT 编译器编译后的 Java 程序的运行速度能够接近 C 语言程序。
静态编译[Static Compilation]
静态编译指在运行前将 Java 字节码转换为与平台相关的 C 代码。之后使用标准的 C 编译器将 C 代码编译为目标平台上的可执行代码。此方式更适合不会被频繁修改的 Java 应用程序。此方式充分利用了现代 C 编译器的成熟性及与平台相关的高效的编译技术。
321 Oracle uses static compilation to deliver its core Java class libraries: the ORB and JDBC code in natively compiled form. It is applicable across all the platforms Oracle supports, whereas a JIT approach requires low-level, processor-dependent code to be written and maintained for each platform. You can use this native compilation technology with your own Java code.
Oracle 的核心 Java 类库采用的是静态编译的方式:ORB 及 JDBC 代码均被编译为本地形式。在所有 Oracle 支持的平台上均可以采用静态编译方式,而 JIT 方式则要求编写目标平台上的与处理器相关的低阶代码。开发者可以在自己的 Java 代码中采用 JIT 方式。

Dynamic Class Loading 动态类加载

323 Another strong feature of Java is dynamic class loading. The class loader loads classes from the disk (and places them in the JVM-specific memory structures necessary for interpretation) only as they are used during program execution. The class loader locates the classes in the CLASSPATH and loads them during program execution. This approach, which works well for applets, poses the following problems in a server environment:
Java 的另一项重要特性是动态类加载[dynamic class loading]。Java 程序运行时,当一个类被用到时类加载器[class loader]才从磁盘读取该类(并将其放入 JVM 的内存结构中进行解释操作)。类加载器根据 CLASSPATH 定位类并在程序运行时进行加载。这种方式对于 applet 来说工作正常,但在服务端系统中将带来以下问题:


The class loading operation places a severe penalty on first-time execution. A simple program can cause the Oracle JVM to load many core classes to support its needs. A programmer cannot easily predict or determine the number of classes loaded.
The Oracle JVM loads classes dynamically, just as with any other Java Virtual Machine. The same one-time class loading speed hit is encountered. However, because the classes are loaded into shared memory, no other users of those classes will cause the classes to load again—they will simply use the same pre-loaded classes.
A benefit of dynamic class loading is that it supports program updating. For example, you would update classes on a server, and clients who download the program and load it dynamically see the update whenever they next use the program. Server programs tend to emphasize reliability. As a developer, you must know that every client runs a specific program configuration. You do not want clients to inadvertently load some classes that you did not intend them to load.
Oracle separates the upload and resolve operation from the class loading operation at runtime. You upload Java code you developed to the server using the loadjava utility. Instead of using CLASSPATH, you specify a resolver at installation time. The resolver is analogous to CLASSPATH, but lets you specify the schemas in which the classes reside. This separation of resolution from class loading means you always know what program users run.


服务端程序第一次执行时将面对大量的类加载工作。一个简单的程序就需要 Oracle JVM 加载大量其所需要的核心类。开发者难以预测或确定被加载的类的数量。

Oracle JVM 与其他 JVM 一样采用动态类加载,因此同样面临一次加载大量类的问题。但在 Oracle JVM 中,类被加载到共享内存中,使用相同类的其他用户不必再次加载类,而只需使用已加载的类即可。

Oracle 将上传[upload]和解析[resolve]两种操作从运行时的类加载工作中独立出来。开发者使用 loadjava 工具将开发好的 Java 代码上传。而在安装时使用解析器而非传统的 CLASSPATH。解析器与 CLASSPATH 类似,但用户可以利用解析器设定类所在的方案。将类解析与类加载分离使开发者能够明确用户所加载的类。

Oracle's Java Application Strategy Oracle 的 Java 应用程序开发策略

326 One appeal of Java is its ubiquity and the growing number of programmers capable of developing applications using it. Oracle furnishes enterprise application developers with an end-to-end Java solution for creating, deploying, and managing Java applications. The total solution consists of client-side and server-side programmatic interfaces, tools to support Java development, and a Java Virtual Machine integrated with the Oracle database server. All these products are compatible with Java standards.
Java 的重要吸引力在于其广泛的应用面以及与日俱增的 Java 程序员群体。Oracle 为企业应用开发者提供了一个端到端 Java 解决方案,从创建,部署,到管理。这个全面的解决方案由客户端及服务端编程接口,Java 开发工具,与集成在 Oracle 数据库服务器内的 JVM 组成。上述所有产品均与 Java 标准兼容。
327 In addition to the Oracle JVM, the Java programming environment consists of the following:
  • Java stored procedures as the Java equivalent and companion for PL/SQL. Java stored procedures are tightly integrated with PL/SQL. You can call a Java stored procedure from a PL/SQL package; you can call PL/SQL procedures from a Java stored procedure.
  • SQL data can be accessed through the JDBC programming interface.
  • Tools and scripts used in assisting in development, class loading, and class management.
除 Oracle JVM 之外,一个 Java 开发环境含包含以下内容:
  • Java 存储过程与 PL/SQL 存储过程相似。Java 存储过程与 PL/SQL 紧密集成。用户可以在 PL/SQL 包中调用 Java 存储过程,也可以在 Java 存储过程中调用 PL/SQL 过程。
  • 用于访问 SQL 数据的 JDBC 编程接口。
  • 用于辅助开发,类加载,及类管理的工具与脚本。
328 This section contains the following topics: 本节包含以下主题:

Java Stored Procedures Java 存储过程

330 A Java stored procedure is a program you write in Java to run in the server, exactly as a PL/SQL stored procedure. You invoke it directly with products like SQL*Plus, or indirectly with a trigger. You can access it from any Oracle Net client—OCI, precompiler, or JDBC.
Java 存储过程[Java stored procedure]是以 Java 编写并运行于服务端的程序,与 PL/SQL 存储过程完全相同。用户可以在 SQL*Plus 之类的工具中直接调用 Java 存储过程,也可以在触发器中调用。用户可以通过任意的 Oracle Net 客户端(OCI,预编译器,或 JDBC)调用 Java 存储过程。
331 In addition, you can use Java to develop powerful programs independently of PL/SQL. Oracle provides a fully-compliant implementation of the Java programming language and JVM.
此外,开发者也可以使用 Java 开发与 PL/SQL 独立的程序。Oracle 提供了与标准完全兼容的 Java 编程语言及 JVM 实现。
See Also:

Oracle Database Java Developer's Guide explains how to write stored procedures in Java, how to access them from PL/SQL, and how to access PL/SQL functionality from Java.

Oracle Database Java Developer's Guide 阐释了如何使用 Java 编写存储过程,如何在 PL/SQL 中访问 Java 存储过程,及如何在 Java 中访问 PL/SQL 功能。

PL/SQL Integration and Oracle Functionality 与 PL/SQL 及 Oracle 功能集成

334 You can invoke existing PL/SQL programs from Java and invoke Java programs from PL/SQL. This solution protects and leverages your existing investment while opening up the advantages and opportunities of Java-based Internet computing.
用户可以在 Java 中调用已有的 PL/SQL 程序,同样也可以在 PL/SQL 中调用 Java 程序。此特性能够保护并利用已有投资,同时发挥基于 Java 的 Internet 计算的优势。


336 Java database connectivity (JDBC) is an application programming interface (API) for Java developers to access SQL data. It is available on client and server, so you can deploy the same code in either place.
JDBC(Java database connectivity)是一套供 Java 开发者访问 SQL 数据的 API。JDBC 在客户端及服务端均可使用,因此开发者在客户端及服务端可部署相同的代码。
337 Oracle's JDBC allows access to objects and collection types defined in the database from Java programs through dynamic SQL. Dynamic SQL means that the embedded SQL statement to be run is not known before the application is run, and requires input to build the statement. It provides for translation of types defined in the database into Java classes through default or customizable mappings, and it also enables you to monitor, trace, and correlate resource consumption of Java and J2EE applications down to the database operation level.
开发者利用 Oracle JDBC 可以在 Java 程序中通过动态 SQL[dynamic SQL]存取对象类型[object type]及集合类型[collection type]的数据。动态 SQL 指嵌入到应用程序中的 SQL 语句,只有在运行时才能根据用户输入形成完整的可执行语句。利用 JDBC,开发者可以将数据库中定义的类型通过默认或自定义的映射转换为 Java 类。此外,开发者还可以利用 JDBC 在数据库操作级监控,跟踪,并分析 Java 及 J2EE 应用程序。
338 Core Java class libraries provide only one JDBC API. JDBC is designed, however, to allow vendors to supply drivers that offer the necessary specialization for a particular database. Oracle delivers the following three distinct JDBC drivers.
核心 Java 类库中只包含一套 JDBC API。但第三方厂商可以依据 JDBC 规范开发驱动程序,为特定数据库提供特殊支持。Oracle 提供了以下三种不同的 JDBC 驱动程序。


JDBC Thin Driver
You can use the JDBC Thin driver to write 100% pure Java applications and applets that access Oracle SQL data. The JDBC Thin driver is especially well-suited to Web browser-based applications and applets, because you can dynamically download it from a Web page just like any other Java applet.
JDBC Oracle Call Interface Driver
The JDBC Oracle Call Interface (OCI) driver accesses Oracle-specific native code (that is, non-Java) libraries on the client or middle tier, providing a richer set of functionality and some performance boost compared to the JDBC Thin driver, at the cost of significantly larger size and client-side installation.
JDBC Server-side Internal Driver
Oracle uses the server-side internal driver when Java code runs on the server. It allows Java applications running in the server's JVM to access locally defined data (that is, on the same computer and in the same process) with JDBC. It provides a further performance boost because of its ability to use underlying Oracle RDBMS libraries directly, without the overhead of an intervening network connection between your Java code and SQL data. By supporting the same Java-SQL interface on the server, Oracle does not require you to rework code when deploying it.


JDBC 瘦客户驱动程序[JDBC Thin driver]
开发者可以使用 JDBC 瘦客户驱动程序编写能够访问 Oracle SQL 数据的 100% 纯 Java 应用程序或 applet。JDBC 瘦客户驱动程序尤其适用于开发基于 Web 浏览器的应用程序及 applet,因为用户可以从 Web 页面动态地下载程序,就如同下载其他 Java applet一样。
JDBC OCI 驱动程序[JDBC Oracle Call Interface Driver]
JDBC OCI 驱动程序使用位于客户端或中间件的以本地(即非 Java)代码形式存在的库函数,与 JDBC 瘦客户驱动程序相比提供了更丰富的功能及更高的性能,但代价是容量庞大且需要在客户端进行安装。
JDBC 服务端内部驱动程序[JDBC  Server-side Internal Driver]
如果 Java 代码运行于服务端的话,Oracle 则使用 JDBC 服务端内部驱动程序。运行于服务端 JVM 内的 Java 应用程序可以通过此种驱动访问本地定义的(即同一计算机的同一进程内的)数据。此种驱动程序可以直接使用 Oracle RDBMS 的类库,且无需在应用程序代码和 SQL 数据间建立网络连接,因此能够提供更好的性能。Oracle 提供的服务端及客户端 Java-SQL 接口是相同的,因此开发者在不同位置部署程序时无需修改任何代码。

See Also:




342 SQLJ allows developers to use object datatypes in Java programs. Developers can use JPublisher to map Oracle object and collection types into Java classes to be used in the application.
开发者通过 SQLJ 可以在 Java 程序中使用对象数据类型[object datatype]。开发者可以使用 JPublisher 将 Oracle 对象类型及集合类型映射为 Java 类,并在 Java 应用程序中使用。
343 SQLJ provides access to server objects using SQL statements embedded in the Java code. SQLJ provides compile-time type checking of object types and collections in the SQL statements. The syntax is based on an ANSI standard (SQLJ Consortium).
通过 SQLJ,开发者可以使用嵌入于 Java 代码的 SQL 语句访问服务器中的对象。SQLJ 提能够对 SQL 语句中的对象类型及集合类型进行编译时类型检查[compile-time type checking]。SQLJ 的语法基于 ANSI 标准(SQLJ 协定[SQLJ Consortium])。
344 You can specify Java classes as SQL user-defined object types. You can define columns or rows of this SQLJ type. You can also query and manipulate the objects of this type as if they were SQL primitive types. Additionally, you can do the following:
  • Make the static fields of a class visible in SQL
  • Allow the user to call a Java constructor
  • Maintain the dependency between the Java class and its corresponding type
开发者可以将一个 Java 类作为一个用户定义的 SQL 对象类型。开发者可以定义这个 SQLJ 类型的行及列。开发者开可以操作这个 SQLJ 类型的对象,操作方式与 SQL 对象类型完全相同。此外,SQLJ 还具有以下功能:
  • 使一个类的静态字段[static field]在 SQL 中可见
  • 允许用户调用 Java 类的构造体[constructor]
  • 维护 Java 类及其对应的对象类型间的依赖关系

JPublisher JPublisher

346 Java Publisher (JPublisher) is a utility, written entirely in Java, that generates Java classes to represent the following user-defined database entities in your Java program:
  • SQL object types
  • Object reference types ("REF types")
  • SQL collection types (VARRAY types or nested table types)
  • PL/SQL packages
JPublisher(Java Publisher)是一个以 Java 编写的工具,用于在 Java 程序中生成 Java 类来代表以下用户定义的数据库对象:
  • SQL 对象类型[SQL object type]
  • 对象引用类型[object reference type](即“REF”类型 )
  • SQL 集合类型[SQL collection type](VARRAY 类型或嵌套表[nested table]类型)
  • PL/SQL 包
347 JPublisher lets you to specify and customize the mapping of these entities to Java classes in a strongly typed paradigm.
开发者可以使用 JPublisher 设定及自定义上述数据库对象与 Java 类间的映射关系。
See Also:

Oracle Database JPublisher User's Guide

Oracle Database JPublisher User's Guide

Java Messaging Service Java 消息服务

350 Java Messaging Service (JMS) is a messaging standard developed by Sun Microsystems along with Oracle, IBM, and other vendors. It defines a set of interfaces for JMS applications and specifies the behavior implemented by JMS providers. JMS provides a standard-based API to enable asynchronous exchange of business events within the enterprise, as well as with customers and partners. JMS facilitates reliable communication between loosely coupled components in a distributed environment, significantly simplifying the effort required for enterprise integration. The combination of Java technology with enterprise messaging enables development of portable applications.
Java 消息服务[Java Messaging Service,JMS]是由 SUN 微系统和 Oracle,IBM,及其他厂商制定的消息系统标准。此标准中定义了 JMS 应用程序的接口,及 JMS 提供者应具备的行为。JMS 提供了基于标准的 API,用户可以利用此套 API 在企业内部或企业与客户及合作伙伴间异步地交换业务事件。JMS 能够为分布式环境中的各个松耦合组件提供可靠的通信能力,简化企业信息集成的工作。采用 Java 技术的企业消息系统具备良好的可移植性。
351 Oracle Java Messaging Service is a Java API for Oracle Streams, based on the JMS standard. Multiple client applications can send and receive messages of any type through a central JMS provider (Oracle Streams). The JMS client consists of the Java application as well as a messaging client runtime library that implements the JMS interface and communicates with Oracle Streams.
Oracle Java 消息服务[Oracle Java Messaging Service]是一套基于 Oracle 数据流[Oracle Streams]且符合 JMS 标准的 Java API。多个客户端应用程序可以通过中央 JMS 提供者(即 Oracle 数据流)发送或接收消息。JMS 客户端由 Java 应用程序和实现了 JMS 接口并与 Oracle 数据流通信的消息服务客户端运行时类库构成。
352 Java Messaging Oracle JMS supports the standard JMS interfaces and has extensions to support other Streams features that are not a part of the standard. It can be used to enqueue and dequeue messages in the queue available with Oracle Streams. Oracle JMS includes the standard JMS features:
  • Point-to-point communication using queues
  • Publish-subscribe communication using topics
  • Synchronous and asynchronous message exchange
  • Subject-based routing
Oracle JMS 支持标准的 JMS 接口,并进行了扩展以支持其他不包含于 JMS 标准的 Oracle 数据流特性。用户可以使用 JMS 对 Oracle 数据流队列中的消息进行入队及出对操作。Oracle JMS 包含以下标准 JMS 特性:
  • 通过队列进行点对点的通信
  • 根据主题进行发布-订阅式通信[publish-subscribe communication]
  • 同步或异步地消息交换
  • 根据主题对消息进行路由
353 Oracle Streams also provides extensions to the standard JMS features:
  • Point-to-multipoint communication using a recipient list for specifying the applications to receive the messages
  • Administrative API to create the queue tables, queues and subjects
  • Automatic propagation of messages between queues on different databases, enabling the application to define remote subscribers
  • Transacted session support, allowing both JMS and SQL operations in one transaction
  • Message retention after message is consumed
  • Exception handling
  • Delay specification before a message is visible
Oracle 数据流还在标准的 JMS 之上进行了扩展:
  • 使用接收者列表[recipient list]指定接收消息的应用程序,实现一点对多点的通信[point-to-multipoint communication]
  • 管理性 API,用于创建对列表[queue table],队列[queue],及主题[subject]
  • 自动地在不同数据库的队列间传播消息,使应用程序可以使用远程订阅者[subscriber]
  • 支持事务性会话[transacted session],JMS 及 SQL 操作可以位于同一事务内
  • 当消息被使用[consume]后可以设定消息保留周期[message retention]
  • 异常处理
  • 可以设定消息可见延迟

A 翻译不确定的词汇[格式:黄色背景 ]


B 翻译不确定的Oracle/数据库词汇[格式:
黄色背景 ]

[159] (including user exits)
[238] index-by tables
[293] high-level
[293] low-level
[303] low-level behavior
[316] Generational scavenging
[316] Mark and lazy sweep collection
[316] Copying collector

C 翻译不确定的句子[格式:
黄色背景 ]

[044] They are used with the Oracle precompilers.
[081] The cursor is created independent of any SQL statement: it is created in expectation of any SQL statement.
[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.
[108] This ensures data integrity.
[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.
[116] To rerun them, simply perform another execute.
[118] Transactions should consist of only the SQL statements that make one consistent change to the data.
[198] By developing all of your applications around a common group of procedures, you can reduce the likelihood of committing coding errors.
[224] Oracle supplies many PL/SQL packages with the Oracle database server to extend database functionality and provide PL/SQL access to SQL features.
[241] So, you can use them to move columns of data into and out of database tables or between client-side applications and stored subprograms.
[254] Language syntax that borrows from C and enforces strong typing
[260] The instance contains the fields of an object, which are known as its data, or state.
[278] Instances of Class B are substitutable for instances of Class A, which makes inheritance another powerful construct of object-oriented languages for improving code reuse.
[291] For example, you can safely add new fields to existing classes.
[321] It is applicable across all the platforms Oracle supports, whereas a JIT approach requires low-level, processor-dependent code to be written and maintained for each platform.
[347] JPublisher lets you to specify and customize the mapping of these entities to Java classes in a strongly typed paradigm.

D 注释性的文字[格式:


E 未完成的链接


F Oracle学习问题[格式:
1、descriptor 是什么?
[044] Initialize descriptors (DESCRIBE)

2、precompiler 是什么?
[044] They are used with the Oracle precompilers.

3、link 步骤做了什么工作?
[148] (This object code is linked into the Oracle database server.)

4、SQL*Menu 是什么?
[157] SQL*Menu (version 5 and later)

5、想想在 ETL 中如何使用?
[024] DML Error Logging

[094] Describe Results of a Query

[096] Define Output of a Query

[198] Inherited privileges and schema context with invoker's rights procedures

9、shared library 是什么咚咚?
[212] shared library

translator: zw1840@hotmail.com