PLSQL Developer 14 使用教程：编程与测试优化指南

5星 · 超过95%的资源需积分: 48 74 浏览量更新于2024-07-02 收藏 17.81MB PDF 举报

"PLSQL Developer 14 用户指南是一份详细介绍如何使用该工具与 Oracle 数据库进行交互的文档。这份指南涵盖了从安装、登录数据库、编写、测试到优化 PL/SQL 程序的全过程。" PL/SQL Developer 是一个集成开发环境（IDE），专门用于开发和管理 Oracle 数据库中的 PL/SQL 代码。以下是该用户指南中的关键知识点： 1. **介绍**: 提供了对 PL/SQL Developer 的概述，解释了它的主要功能和用途，帮助新用户了解工具的基本概念。 2. **安装**: - **系统要求**: 介绍了运行 PL/SQL Developer 所需的硬件和软件配置。 - **交互式安装**: 指导用户如何通过图形界面完成安装过程。 - **静默安装**: 适用于自动化部署，说明了如何在无用户交互的情况下进行安装。 - **卸载 PL/SQL Developer**: 描述了如何从计算机中移除该应用程序。 3. **登录到 Oracle 数据库**: 详述了如何配置连接参数，连接到 Oracle 数据库实例。 4. **编写程序**: - **创建程序**: 教用户如何新建 PL/SQL 代码文件。 - **保存程序**: 解释了保存代码的步骤和位置。 - **修改程序**: 讲解如何编辑已有的 PL/SQL 代码。 - **编译程序**: 包括如何检查语法错误和编译代码。 - **比较程序**: 介绍了如何对比不同版本的代码差异。 5. **测试程序**: - **创建测试脚本**: 教授如何定义测试用例。 - **执行测试脚本**: 如何运行测试并查看结果。 - **变量类型**: 介绍支持的变量类型及其用法。 - **保存测试脚本**: 说明如何保存测试脚本以便重复使用。 - **跟踪运行时错误**: 如何监控和调试错误。 - **包状态与 Java 会话状态**: 有关包的状态信息和 Java 代码的执行状态。 - **查看结果集**: 查看 SQL 查询返回的数据。 - **查看 DBMS_OUTPUT**: 显示在 DBMS_OUTPUT 表中的调试信息。 - **查看 HTTP 输出**: 当涉及到 Web 应用程序时，显示 HTTP 响应。 - **调试**: 使用调试器进行单步执行、设置断点等操作。 - **追踪执行**: 追踪代码执行路径，分析性能。 - **回归测试** 和 **创建标准测试**: 自动化测试策略，确保代码质量。 6. **优化**: - **使用 EXPLAIN PLAN 窗口**: 分析 SQL 查询的执行计划，以提高效率。 - **自动统计信息**: 关于自动收集数据库对象的统计信息以优化查询。 - **PL/SQL 分析器**: 用于分析 PL/SQL 代码的性能。 - **SQL TRACE**: 提供 SQL 语句的详细执行信息，用于性能诊断。 7. **adhoc SQL**: - **使用 SQL 窗口**: 直接在 IDE 内编写和执行 SQL 查询。 - **结果网格操作**: 如何处理查询结果，包括排序、过滤和导出数据。 - **其他未列出的功能可能包括更多 SQL 相关的操作和管理工具，例如视图、存储过程、触发器的创建和管理等。这份指南为用户提供了全面的指导，无论是初学者还是有经验的开发人员，都能从中受益，有效地使用 PLSQL Developer 14 进行 PL/SQL 开发和管理。

16 PL/SQL Developer 14.0 User’s Guide

4.3 Modifying a program

You can open a previously saved program file by pressing the Open button on File ribbon and selecting

the Program file item. If the file was recently used, you can also open it by selecting Reopen from the

File ribbon. When you start editing, a blue colored indicator at the bottom of the window lights up. This

indicates that the file is modified, but not yet saved. There is also a yellow colored indicator, which

indicates that the file is modified, but not yet compiled.

4.4 Compiling a program

You can compile a program file by pressing the Execute button on the Session ribbon or on the Quick

Access Toolbar. All program units in the program file are compiled, starting with the first one, regardless

of the program unit that is currently selected. When an error occurs, compilation is terminated and the

editor is positioned at the source line that caused the error:

If there is more than one error and you wish to correct them, you can click on the next error message at

the bottom of the Program Editor to go to the error location. If you have configured PL/SQL Developer

to use the HTML manuals as described in chapter 36.2, you can double-click on a compilation error to

display the corresponding paragraph in the Oracle Error Messages manual.

The compilation error list can also contain hints. Hints do not cause the compilation to fail, but they may

indicate an issue that could cause a run-time error (such as a comparison with null, a function without a

return value, an unused parameter, and so on). For more information about hints, see chapter 22.19.

If an error message is displayed in a message box after compilation, this means that the create statement

failed without actually compiling the source. The error message should explain the cause of the error. If

for example you get the 'create or replace' syntax wrong, it will say "ORA-00900: Invalid SQL

statement" in a message box.

Note: If you are using Oracle Server 7.2 or earlier, a trigger compilation error is always reported in a

message box. Only since Oracle Server 7.3, trigger compilation errors are reported in the same way as

procedures, functions, packages and types.

4.5 Comparing a program

To compare a program source to another version in the database or on the file system, you can rightclick

on the editor and click on the Compare to submenu-item:

18 PL/SQL Developer 14.0 User’s Guide

5. Testing programs

After successfully compiling a program, you'll need to test it. To achieve this, you can use PL/SQL

Developer's Test Scripts. A Test Script allows you to execute one or more program units, define input,

output and input/output variables and view and assign values to variables. If a run-time error occurs

during execution of the Test Script, you can view the sources of the program units that were involved in

the error.

If you are using Oracle 7.3.4 or later you can use PL/SQL Developer’s integrated debugger. You can step

through your code, set breakpoints, view and set variables, view the call stack, and so on.

5.1 Creating a Test Script

There are two ways to create a Test Script. You can simply right-click on a program unit (a standalone

function or procedure, a packaged function or procedure, or an object type method) in the Object

Browser and select the Test item. This will create a new Test Script with variables for the parameters and

result. For the purpose of this manual, we are going to create a Test Script manually from scratch though.

To create a new empty Test Script, press the New button on the File ribbon or on the Quick Access

Toolbar and select the Test Window item. You can now type a PL/SQL block with the familiar

declare...begin...end syntax in the body of the Test Script. As an example we're going to test a package

employee, which implements some employee functions of the employee/department demo tables.

Function deptname returns the name of the department of an employee, and can be tested using the

following PL/SQL block:

The PL/SQL block contains a simple call to the function that we want to test, and uses variables

deptname and empno. We'll have to assign a value to empno, and after execution, check the value of

deptname to determine if the function returned the correct value.

By prefixing these variables with a colon, they can be declared at the bottom of the Test Script:

PL/SQL Developer 14.0 User’s Guide 19

The Scan source for variables button () at the upper-left of the variables list can be used to quickly

copy the variables from the source. After this, the integer data type and value 7369 (Mr. Smith from the

research department) is assigned to empno. Now we're ready to execute the script.

You can enable a preference (see chapter 22.21) so that the Test Window will automatically search for

bind variables in the source before executing the script.

You can also declare variables locally in a declare part of the PL/SQL block, but you will not be able to

modify or view the values of these variables. An advantage of these local variables is the fact that you

can use record types, PL/SQL tables, types and so on.

5.2 Executing a Test Script

To execute the Test Script, press the Execute button on the Session ribbon or on the Quick Access

Toolbar. After execution, the values of the variables are updated. Any variable whose value has changed

is displayed with a yellow background:

The result of the function is 'RESEARCH', so it obviously functions correctly. At the bottom of the

window, the execution time is displayed. This information can be used to optimize your code for

performance. For optimization purposes you can also view the statistics of the execution of the PL/SQL

block by selecting the Statistics tab. You can also create a profile report for each executed line of

PL/SQL code by pressing the Create Profiler report button before executing the script. After execution

you can switch to the Profiler page to view the report. Statistics and Profiler reports are explained in

chapter 6.2 and 6.3 respectively.

You can abort a running Test Script by pressing the Break button, which is particularly useful when a

program is stuck in an endless loop, or execution is taking longer than expected. Note that pressing the

Break button will not always be successful. If for example the program is waiting for a lock, it will not

respond to a break signal.

After executing a script, a transaction may have been started by the program units that were executed.

The Commit and Rollback button on the Session ribbon and on the Quick Access Toolbar will be enabled

if this is the case. For more information about transactions, see chapter 19.

5.3 Variable types

The type list in the variables section of the Test Window contains all types that can be used. The

characteristics of these types are explained in this chapter.

Type

Description

Integer

Can be used for numeric values in the range of -2^32 to 2^32 - 1.

Float

Can be used for other numeric values.

20 PL/SQL Developer 14.0 User’s Guide

String

Equivalent of a varchar2 data type in a table. Can be up to 2000 (Oracle7) or 4000

(Oracle8) characters. The value cell of this data type has a cell button that invokes a

text editor, so that you can view and modify multi line strings easily.

Date

The date and time data type.

Long

The Long data type does not display the value in the grid, but just displays <Long>.

Press the cell button to invoke the text editor.

Long Raw

Pressing the cell button of a Long Raw value will invoke an import/export file

dialog, allowing you to load or save the contents of this variable.

Cursor

This data type can be used where you can use a cursor variable in PL/SQL. After

executing the Test Script, you can press the cell button of the value to display the

result set in a SQL Window.

CLOB, BLOB

& BFile

These LOB Locator variables must be initialized on the server before you can view

the LOB data. The CLOB will invoke a text editor, the BLOB and BFile will invoke

an import/export file dialog.

PL/SQL String

Use this data type for PL/SQL varchar2 values of up to 32000 characters.

Char

A fixed length (space padded) string.

Substitution

Substitution variables can be used without the restriction of bind variables. The

variable name in the PL/SQL block is substituted by its value in the text before it is

sent to the server.

Temporary

CLOB &

BLOB

These LOB Locators can already hold data before they are passed to the server

through the Test Script.

Boolean variables

When you select the variable type list-box, you will notice that the Boolean data type is missing. This is

because SQL*Net does not support this data type. To use a Boolean variable, you can declare it as an

integer and use the sys.diutil.bool_to_int and sys.diutil.int_to_bool functions provided by Oracle. These

functions convert between null/true/false and null/0/1. If you use the Test function in the Object Browser,

this conversion is automatically generated for you.

5.4 Saving Test Scripts

To save the Test Script, press the Save button on the File ribbon or on the Quick Access Toolbar. The

save dialog uses .tst as the default extension for Test Scripts, though you can change the default

extension with a preference described in chapter 22.29. The PL/SQL block, all variables (name, type and

value), and the debugger watches (see chapter 5.10) are saved. By saving a Test Script you can easily re-

test a program unit if you modify it later.

You can open a previously saved Test Script by pressing the Open button on the File ribbon or on the

Quick Access Toolbar and selecting Test Script, which will create a new Test Window. You can

alternatively right-click on a previously created Test Window and select the Load item, which will open

a Test Script in the existing Test Window.

Saving as SQL*Plus script

You can alternatively save a Test Script in SQL*Plus compatible format. This allows you to run the script

in an environment where PL/SQL Developer is not available (e.g. on a Unix server). To do so, select

SQL*Plus script (*.sql) as file type in the file selector dialog. The example script from the previous

chapter would be saved like this:

剩余262页未读，继续阅读

awsbamboo115

粉丝: 38
资源: 163

PLSQL Developer 14 使用教程：编程与测试优化指南

PLSQL Developer基础操作指南

PLSQL Developer 7.0 用户指南：编程与测试实践

PLSQL Developer 14与instantclient的安装教程

PLSQL Developer14用户指南.pdf

PLSQL Developer 13 用户指南 （PLSQL Developer 13.0 User's Guide)

PLSQL Developer 8.0使用指南

PLSQL Developer 7.0用户指南

PLSQLDeveloper10.0用户指南

PLSQL Developer8用户指南

PLSQL Developer 7.0 用户指南

最新资源

PLSQL Developer 13 用户指南（PLSQL Developer 13.0 User's Guide)