Oracle PL/SQL最佳实践指南

需积分: 3 48 浏览量更新于2024-07-29 收藏 1.96MB PDF 举报

"Oracle PL/SQL Best Practices" Oracle PL/SQL是Oracle数据库系统中的一种强大力量，它结合了SQL（结构化查询语言）用于数据管理和操纵，以及PL（过程语言）用于编写存储过程、函数、触发器等复杂业务逻辑。本资源可能是关于优化和最佳实践的指南，旨在帮助开发者提升在Oracle环境中编写PL/SQL代码的效率和质量。在Oracle PL/SQL编程中，遵循最佳实践至关重要，以下是一些关键点： 1. **模块化编程**：将复杂的逻辑分解成小的、可重用的函数和过程，提高代码可读性和维护性。避免编写过长的程序块，每个程序块应有明确的责任。 2. **异常处理**：使用BEGIN...EXCEPTION...END结构来捕获和处理错误。正确的异常处理可以防止程序意外终止，并提供有用的错误信息。 3. **性能优化**：尽量减少对数据库的访问，例如通过批量处理、缓存结果或使用索引来提升查询速度。避免在循环中执行DML操作，考虑使用SQL的集合操作。 4. **变量和游标管理**：声明变量时指定数据类型，避免使用默认类型。合理使用显式游标，尽量避免隐式游标，因为它们可能导致性能问题。 5. **代码注释**：添加清晰的注释，说明代码的目的和工作原理。良好的注释有助于团队协作和未来的代码维护。 6. **测试与调试**：编写单元测试来验证PL/SQL代码的功能，使用DBMS_OUTPUT或其他调试工具进行调试。确保所有关键路径都经过测试。 7. **安全性**：限制对敏感数据的访问，使用角色和权限来管理用户访问。避免在PL/SQL中硬编码敏感信息，如密码或连接字符串。 8. **代码规范**：遵循统一的编码风格，如命名规则、缩进和空格，这有助于提高代码的可读性。使用PL/SQL的内置编译器警告来检查代码质量。 9. **错误处理策略**：避免使用UNDO表空间来回滚事务，而应显式地提交或回滚事务。正确处理程序中的错误，以确保数据库一致性。 10. **并发控制**：在多用户环境中，了解并使用锁、事务隔离级别和乐观/悲观锁定策略来防止数据不一致。除了上述实践，还可以参考O'Reilly出版的相关书籍，如"Learning Oracle PL/SQL"、"Oracle Essentials"、"Oracle in a Nutshell"等，这些资源能深入学习Oracle PL/SQL的各个方面。此外，访问oreilly.com或safari.oreilly.com可以获得更多的在线参考材料和最新的技术资讯。O'Reilly还组织各种技术会议，为开发者提供了学习和交流的平台。掌握Oracle PL/SQL的最佳实践能够帮助开发者编写出高效、可靠且易于维护的代码，进一步提升数据库应用的整体性能。

xiv

Preface

Lizbeth

The anchor of the team. She used to write in Cobol and is continually

shocked at the lack of strong process in the PL/SQL world. She can’t under-

stand why programmers today make fun of the Cobol programmers of the

past. Didn’t they write the software that made the first phase of the Informa-

tion Revolution a broad success? Lizbeth is methodical and careful, but not,

on the whole, the best problem solver—she too easily falls into the trap of

seeing things only from her own perspective.

Jasper

The junior member of the team. He is new to PL/SQL and new to MFE.

Jasper is eager to learn from anyone and everyone and has nice thick skin, but

he is not very creative (i.e., is not ready to take risks in his code). He always

wears jeans, preferably of the distressed variety, complemented by polo shirts

with the logos of animals on them. Lizbeth thinks of him as the son she never

had, and Delaware treats him like the mascot of the team.

As you can see, each person has her or his own strengths and weaknesses. We will

learn from both of these characteristics as we make our way through this book.

Structure of This Book

As I mentioned earlier, I decided to loosen up a bit structure-wise in the second

edition. That does not mean, however, that you are about to start reading a

chaotic and hard-to-navigate book. This section summarizes the organization.

Oracle PL/SQL Best Practices is composed of nine chapters and two appendixes.

Each chapter contains a set of best practices for a particular area of functionality

in the PL/SQL language or for broad programming principles (the latter are

concentrated mostly in Chapter 1).

Best practices are used to overcome problems in our everyday programming life

and to encourage the writing of high-quality code. To reinforce this purpose, most

of the best practices in the book are presented first as a problem in the MFE envi-

ronment: Sunita’s team or a member of it confronts a challenge or has followed a

“worst practice” into a very uncomfortable spot. We will then see how the MFE

development team applies the best practice at hand to meet the challenge or repair

the nasty code.

In many of the best practices, I also offer references to files available for down-

load from the book’s web site, as well as to publicly available tools (freeware,

open source, and commercial) that will help you apply the best practices in a prac-

tical and efficient manner. You will find a comprehensive list of all such references

in Appendix B.

Most best practices in this book are introduced by two titles: the first title is often

a bit tongue-in-cheek (less than totally serious) and the second title is a more

serious recommendation.

Preface

Chapter 1, The Big Picture, offers advice about how to improve the overall process

by which you write code. The emphasis is on the application as a whole and the

processes used to write that application, rather than on concrete suggestions for

specific aspects of programming in PL/SQL. Please don’t skip Chapter 1: if you

do, it will be difficult to fully utilize the best practices in the rest of this book.

Chapter 2, Real Developers Follow Standards, is all about, well, standards: setting

rules for how you and everyone on your team should write code. These standards

range from naming conventions to standards templates from which code should

be built. By the time you have finished reading this chapter, I hope that you will

have overcome your ingrained resistance to standards, and instead see that rules

free up more of your time to write the more interesting parts of your application.

Chapter 3, Life After Compilation, explores the steps that should take place after

you get your program to compile, because, let’s face it, that’s just the beginning.

You will learn about testing, tracing, and debugging—and the differences among

these activities.

Chapter 4, What’s Code Without Variables?, takes a close look at how you can

best declare and manage data structures and variables within your PL/SQL

programs. PL/SQL is a strongly-typed language, which means that you need to

make lots of decisions yourself in this area.

Chapter 5, Developer As Traffic Cop, is a “back to basics” chapter that talks about

the best way to write IF statements, loops, and the new (in Oracle Database 11g)

CONTINUE statement. Sure, these aren’t terribly complicated constructs, but

there are still right and wrong ways to work with them.

Chapter 6, Doing the Right Thing When Stuff Goes Wrong, covers exception

management (“stuff going wrong”). We will explore defining standards for

raising, handling, logging, and communicating errors back to the user. It is impos-

sible to build a high-quality application with inconsistent error management, so

be sure to read this chapter before you start your next project!

Chapter 7, Break Your Addiction to SQL, focuses on a crucial aspect of PL/SQL

development: how you should write the SQL statements in your program to

improve programmer productivity, increase performance, and make your code

more maintainable. My feeling is that most PL/SQL developers are totally

addicted to writing SQL—and that addiction leads to some of the biggest

mistakes in how we write our code.

Chapter 8, Playing with Blocks (of Code), offers advice on specifying parameters

and building maintainable, readable, reusable procedures and functions—the

program units that contain your business logic. It also presents recommendations

for packages, the building blocks of well-designed PL/SQL-based applications, and

triggers, which allow you to associate business rules with specific database objects.

Chapter 9, My Code Runs Faster Than Your Code, provides guidelines for

improving the performance of your PL/SQL programs. These best practices range

from techniques that will have an amazing impact on execution time (reducing

xvi

Preface

program elapsed time from hours or days to mere minutes) to more granular

recommendations whose impact will vary depending on the type of code you are

writing.

Appendix A, Best Practices Quick Reference, compiles the best practices across all

the chapters into a concise resource. Once you have studied the individual best

practices, you can use this appendix as a checklist, to be reviewed both before you

begin coding a new application and while you are implementing your programs.

Appendix B, Resources for PL/SQL Developers, offers descriptions of the files (exam-

ples and reusable code) that are referenced in the book, plus a list of the most useful

books and online resources to help you take full advantage of the PL/SQL language.

How to Use This Book

My primary goal in writing this book was to create a resource that would make a

concrete, noticeable difference in the quality of the PL/SQL code you write. To

accomplish this, the book needs to be useful and usable not just for general study,

but also for day-to-day, program-to-program tasks. It also needs to be concise and

to the point. A 1,000-page text on best practices would be overwhelming, intimi-

dating, and hard to use. The result is this relatively brief (I consider any publication

under 300 pages a major personal accomplishment!) and entertaining book.

You can certainly read and apply the best practices in this book selectively. The

best way to leverage best practices, however, is to start from a solid under-

standing of both your challenges and the high-level flows needed to break out of

the current programming ruts in which you may find yourself today. So start with

Chapter 1 (what a concept!) and see what you think of my “big picture” ideas. If

you find yourself disagreeing with any of these ideas, think through what bothers

you about them and what you would do instead to achieve the same results. If you

like the ideas, spend some time planning how to apply them within your team.

The higher level the best practice is, the more challenging it can be to get it started

and ingrained in a group of developers.

Once you have absorbed (or rejected) the advice in Chapter 1, it will be easier to

take advantage of the more specific best practices in the other eight chapters. I

suggest that you proceed from start to end (don’t panic—it’s not that long a

book!) with a light reading of each chapter, saving a full exploration of all the

details until later on. Try to picture the best practices as a whole, reinforcing the

following themes:

• I want to write code that I—and others—can easily understand and change

as needed.

• The world is terribly complex, so I should strive to keep my code simple. I

can then meet that complexity through carefully designed interaction among

elements of my code.

剩余293页未读，继续阅读

FuYuDong007

粉丝: 0
资源: 1

Oracle PL/SQL最佳实践指南

Oracle PL/SQL best practice

Oracle PL/SQL Best Practices

Oracle.PL.SQL最佳实践

Oracle PL/SQL Programming

Advanced Oracle PL/SQL Programming with Packages

Oracle PL/SQL最佳实践指南

Oracle PL/SQL：SQL最佳实践与陷阱指南

Oracle PL/SQL编程最佳实践指南

Oracle PL SQL Best Practices(2nd) 无水印pdf

Quest Toad for Oracle 中文手册：提升 PL/SQL 开发与数据库管理效率

最新资源