Oracle PL/SQL最佳实践指南

需积分: 50 179 浏览量更新于2024-07-24 收藏 503KB PDF 举报

"Oracle PL/SQL Best Practices by Steven Feuerstein, published by O'Reilly in April 2001, ISBN 0-596-00121-5." Oracle PL/SQL 是 Oracle 数据库系统中的过程式编程语言，用于扩展 SQL 的功能并创建复杂的业务逻辑。这本书《Oracle PL/SQL 最佳实践》提供了编写高效、可维护的 PL/SQL 代码的关键指导。作者 Steven Feuerstein 是 PL/SQL 领域的权威专家，他总结了程序开发过程中的最佳实践。本书涵盖以下几个主要方面： 1. **编码风格**：强调一致性和可读性，如命名规范、注释规则和代码格式化，以提高代码质量。 2. **SQL 在 PL/SQL 中的使用**：讲解如何有效地嵌入 SQL 语句，利用游标、子查询和事务管理来优化数据库操作。 3. **数据结构**：讨论如何选择适当的数据类型，如记录、表类型和嵌套表，以及如何有效管理内存中的数据。 4. **控制结构**：包括条件语句（IF-THEN-ELSIF）、循环（FOR, WHILE, GOTO）的使用，以及如何避免不必要的复杂性。 5. **异常处理**：介绍如何正确捕获和处理错误，实现健壮的异常处理机制，确保程序的稳定性。 6. **程序和包的构造**：讲解如何设计和组织 PL/SQL 程序单元，使用包来封装逻辑和数据，以及如何利用包的私有性和共享状态。 7. **内置包**：介绍了 Oracle 提供的预定义包，如 DBMS_OUTPUT 和 DBMS_DEBUG_JDWP，以及如何利用它们增强 PL/SQL 功能。此外，书中还包含互补的代码示例，可以在 O'Reilly 的网站上找到，以帮助读者更好地理解和应用这些最佳实践。附带的快速参考卡片提供了关键概念和语法的快速概览，方便日常开发时查阅。随着 PL/SQL 社区的发展，现在有更多的资源和工具可供开发者使用，比如集成开发环境 (IDEs)、知识库和专门的培训课程。然而，Steven Feuerstein 的这本著作仍然是学习和提升 PL/SQL 编程技能的重要参考资料，它可以帮助开发者遵循最佳实践，编写出高效且易于维护的 PL/SQL 代码。

CREATE OR REPLACE PACKAGE valstd

PROCEDURE progwith (str IN VARCHAR2);

PROCEDURE pw_rae;

END valstd;

You can now call valstd.pw_rae to show all the "programs with"

RAISE_APPLICATION_ERROR (as you can easily see from the valstd package body).

You can also call valstd.progwith to search for other strings. If, therefore, You've a

standard that developers should never hard-code -20,000 error numbers, issue this

command:

SQL> exec valstd.progwith ('-20')

and view what is likely to be a superset of all such instances.

Another kind of standard that might be set within an organization is that application

code should never reference a table or view directly but instead always go through

an encapsulation package ([SQL-01: Qualify PL/SQL variables with their scope names

when referenced inside SQL statements.]). Here is a query that identifies all program

units that violate this rule:

SELECT owner || '.' || name refs_table,

referenced_owner || '.' ||

referenced_name table_referenced

FROM all_dependencies

WHERE owner LIKE UPPER ('&1')

AND TYPE IN ('PACKAGE',

'PACKAGE BODY',

'PROCEDURE',

'FUNCTION')

AND referenced_type IN ('TABLE', 'VIEW')

ORDER BY owner,

name,

referenced_owner,

referenced_name;

Benefits

You don't have to rely solely on "manual" walkthroughs of code to validate

compliance with group standards.

Code analysis and code "mining" (extracting information from/about source code)

can be automated and tightly integrated into the development process.

Challenges

You need to design and build the analysis code and then integrate these checks into

your ongoing development effort.

Resources

Term Fly Presents http://www.flyheart.com

1. reftabs.sql : Query identifying direct references to tables and views.

2. valstd.pkg : Simple prototype package offering an interface to identify the

presence of unwanted text in source code.

DEV-05: Generate code whenever possible and

appropriate.

Life is short—and way too much of it is consumed by time spent in front of a

computer screen, moving digits with varying accuracy over the keyboard. Seems to

me that we should be aggressive about finding ways to build our applications with an

absolute minimum of time and effort while still producing quality goods. A key

component of such a strategy is code generation: rather than write the code yourself,

you let some other piece of software write the code for you.

Code generation is particularly useful when you have defined standards you want

everyone to follow. You can try to get developers to conform to those standards with

a "stick" approach: follow the standards, or else! But a more effective way to get the

often anarchistic, or at least highly individualistic, programmer to follow standards is

to make it easier to follow than not follow those guidelines. See Examples for specific

demonstrations of this "carrot" approach.

In addition to helping to implement standards, code generation comes in handy when

you have to write code that is repetitive in structure (i.e., it can be expressed

generally by a pattern). For example, the kind of code you write to determine if there

is at least one row in a table for a given primary key is the same regardless of the

table (and primary key). Wouldn't it be nice to be able to call a procedure that

queries the table structure and key from the data dictionary and generates the

function?

How do you generate code? You can pick from one of these three options:

• Write your own custom query or program to meet specific needs. Examples

steps you through a simple demonstration of how to go about this.

• Use a commercial tool that focuses on code generation. Resources offers a list

of known code-generation tools for PL/SQL developers.

• Run relatively constrained, functionally specific generation utilities that others

have written (noncommercial, freeware). Resources offers a list of generation

utilities available on the Oracle PL/SQL Best Practices web site.

Examples

Let's explore these three options for generation.

First, we have the classic "SQL generating SQL." Suppose that I want to drop all the

tables in my schema. There is no "drop all" command. Instead, I throw together a

Code-Generation Utilities

1. genlookup.pro : Generates a lookup procedure that returns a row in a table.

2. msginfo.pkg : Generates a package with definitions for all application-specific

exceptions.

3. genmods.pkg : Generates standard formatted functions.

DEV-06: Set up and use formal unit testing

procedures.

A unit test is a test a developer creates to ensure that his or her "unit," usually a

single program, works properly. A unit test is very different from a system or

functional test; these latter types of tests are oriented to application features or

overall testing of the system. You can't properly or effectively perform a system test

until you know that the individual programs behave as expected.

So, of course, you would therefore expect that programmers do lots of unit testing

and have a correspondingly high level of confidence in their programs. Ah, if only

that were the case! The reality is that programmers generally perform an inadequate

number of inadequate tests and figure that if the users don't find a bug, there is no

bug. Why does this happen? Let me count the ways:

The psychology of success and failure

We are so focused on getting our code to work correctly that we generally shy

away from bad news, from even wanting to take the chance of getting bad

news. Better to do some cursory testing, confirm that it seems to be working

OK, and then wait for others to find bugs, if there are any (as if there were

any doubt).

Deadline pressures

Hey, it's Internet time! Time to market determines all. We need everything

yesterday, so let's be just like Microsoft and Netscape: release pre-beta

software as production and let our users test/suffer through our applications.

Management's lack of understanding

IT management is notorious for not really understanding the software

development process. If we aren't given the time and authority to write (write,

that is, in the broadest sense, including testing, documentation, refinement,

etc.) our own code properly, we will always end up with buggy junk that no

one wants to admit ownership of.

Overhead of setting up and running tests

If it's a big deal to write and run tests, they won't get done. I don't have time,

and there is always something else to work on. One consequence of this point

剩余197页未读，继续阅读

姚义龙

粉丝: 0
资源: 12

Oracle PL/SQL最佳实践指南

最新资源