Previous: 1.5 Advice for
Oracle Programmers
Chapter 1
Introduction to PL/SQL
Next: 1.7 Best Practices for
PL/SQL Excellence
1.6 A Few of My Favorite (PL/SQL) Things
PL/SQL is a powerful, many-featured product. This is a lengthy book. I have gone to great lengths to
make all the information within the covers highly accessible. Still, I thought it would be helpful to
offer a quick review of some of my favorite aspects of the PL/SQL language.
It's all wonderful, of course, and I wouldn't trade PL/SQL for any other programming language in the
world. Yet certain features and techniques have stood out for me as ways to improve the efficiency of
my code and the productivity of my development effort.
The topics in the following sections offer just enough information to give you a sense of what is
possible. Go to the appropriate chapter for detailed information.
1.6.1 Anchored declarations
You can use the %TYPE and %ROWTYPE declaration attributes to anchor the datatype of one
variable to that of a previously existing variable or data structure. The anchoring data structure can be
a column in a database table, the entire table itself, a programmer-defined record, or a local PL/SQL
variable. In the following example, I declare a local variable with the same structure as the company
name:
my_company company.name%TYPE;
See
Chapter 4 for details.
1.6.2 Built-in functions
PL/SQL offers dozens of built-in functions to help you get your job done with the minimum amount
of code and fuss possible. Some of them are straightforward, such as the LENGTH function, which
returns the length of the specified string. Others offer subtle variations which will aid you greatly
but only when you are aware of those variations.
Two of my favorites in this category of hidden talents are SUBSTR and INSTR, both character
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
functions. SUBSTR returns a subportion of a string. INSTR returns the position in a string where a
substring is found. Most developers only use these functions to search forward through the strings.
By passing a negative starting location, however, SUBSTR will count from the end of the string. And
INSTR will actually scan in reverse through the string for the nth occurrence of a substring.
See the chapters in
Part 3 for details.
1.6.3 Built-in packages
In addition to the many built-in functions provided by PL/SQL, Oracle Corporation also offers many
built-in packages. These packages of functions, procedures, and data structures greatly expand the
scope of the PL/SQL language. With each new release of the Oracle Server, we get new packages to
improve our own programs.
It is no longer sufficient for a developer to become familiar simply with the basic PL/SQL functions
like TO_CHAR, ROUND, and so on. Those functions have now become only the innermost layer of
useful functionality. Oracle Corporation has built upon those functions, and you should do the same
thing.
See
Appendix C for a summary of the Application Programming Interfaces (APIs) of the built-in
packages.
1.6.4 The cursor FOR loop
The cursor FOR loop is one of my favorite PL/SQL constructs. It leverages fully the tight and
effective integration of the Ada-like programming language with the power of the SQL database
language. It reduces the volume of code you need to write to fetch data from a cursor. It greatly
lessens the chance of introducing loop errors in your programming and loops are one of the more
error-prone parts of a program. Does this loop sound too good to be true? Well, it isn't it's all true!
See
Chapter 7, Loops, for more information.
1.6.5 Scoping with nested blocks
The general advantage of and motivation for a nested block is that you create a scope for all the
declared objects and executable statements in that block. You can use this scope to improve your
control over activity in your program, particularly in the area of exception handling.
In the following procedure, I have placed BEGIN and END keywords around a sequence of DELETE
statements. This way, if any DELETE statement fails, I trap the exception, ignore the problem, and
move on to the next DELETE:
PROCEDURE delete_details
IS
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
BEGIN
BEGIN
DELETE FROM child1 WHERE ;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
BEGIN
DELETE FROM child2 WHERE ;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
END;
I can in this way use my nested blocks to allow my PL/SQL program to continue past exceptions.
See
Chapter 15, Procedures and Functions, for details.
1.6.6 Module overloading
Within a package and within the declaration section of a PL/SQL block, you can define more than
one module with the same name! The name is, in other words, overloaded. In the following example,
I have overloaded the value_ok function in the body of the check package:
PACKAGE BODY check
IS
/* First version takes a DATE parameter. */
FUNCTION value_ok (date_in IN DATE) RETURN BOOLEAN
IS
BEGIN
RETURN date_in <= SYSDATE;
END;
/* Second version takes a NUMBER parameter. */
FUNCTION value_ok (number_in IN NUMBER) RETURN BOOLEAN
IS
BEGIN
RETURN number_in > 0;
END;
END;
Overloading can greatly simplify your life and the lives of other developers. This technique
consolidates the call interfaces for many similar programs into a single module name. It transfers the
burden of knowledge from the developer to the software. You do not have to try to remember, for
example, the six different names for programs which all add values (dates, strings, Booleans,
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
numbers, etc.) to various PL/SQL tables.
Instead, you simply tell the compiler that you want to "add" and pass it the value you want added. PL/
SQL and your overloaded programs figure out what you want to do and they do it for you.
See
Chapter 15 for details.
1.6.7 Local modules
A local module is a procedure or function defined in the declaration section of a PL/SQL block
(anonymous or named). This module is considered local because it is only defined within the parent
PL/SQL block. It cannot be called by any other PL/SQL blocks defined outside of that enclosing
block.
See
Chapter 15 for details.
1.6.8 Packages
A package is a collection of related elements, including modules, variables, table and record TYPEs,
cursors, and exceptions. Packages are among the least understood and most underutilized features of
PL/SQL. That is a shame, because the package structure is also one of the most useful constructs for
building well-designed PL/SQL-based applications. Packages provide a structure in which you can
organize your modules and other PL/SQL elements. They encourage proper programming techniques
in an environment that often befuddles the implementation of good design.
With packages, you can:
● Create abstract datatypes and employ object-oriented design principles in your Oracle-based
applications.
● Use top-down design techniques comprehensively. You can build package specifications
devoid of any code and actually compile programs that call the modules in these "stub"
packages.
● Create and manipulate data that persist throughout a database session. You can use variables
that are declared in a package to create global data structures.
See
Chapter 16, Packages, for details. The disk that accompanies this book contains many examples
of packages. The frontend software gives you an easy-to-use interface to the code and explanations
for using it.
Previous: 1.5 Advice for
Oracle Programmers
Oracle PL/SQL
Programming, 2nd Edition
Next: 1.7 Best Practices for
PL/SQL Excellence
1.5 Advice for Oracle
Programmers
Book Index
1.7 Best Practices for PL/
SQL Excellence
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
The Oracle Library
Navigation
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Previous: 1.4 PL/SQL
Versions
Chapter 1
Introduction to PL/SQL
Next: 1.6 A Few of My
Favorite (PL/SQL) Things
1.5 Advice for Oracle Programmers
This whole book is full of advice about PL/SQL programming, but in this section I offer a few basic
principles. These principles guide my own use of PL/SQL and I'd like to encourage you to adopt
them as well.
1.5.1 Take a Creative, Even Radical Approach
We all tend to fall into ruts, in almost every aspect of our lives. People are creatures of habit: you
learn to write code in one way; you come to assume certain limitations about a product; you turn
aside possible solutions without serious examination because you just know it can't be done.
Developers become downright prejudiced about their own tools, and often not in positive ways. "It
can't run any faster than that; it's a pig." "I can't make it work the way the user wants; that'll have to
wait for the next version." "If I were using X or Y or Z product, it would be a breeze. But with this
stuff, everything is a struggle."
Sadly (or is it happily?), the reality is that your program could almost always run a little faster. The
screen could function just the way the user wants it to. Although each product has its limitations,
strengths, and weaknesses, you should never have to wait for the next version. Isn't it so much more
satisfying to be able to tell your therapist that you tackled the problem head-on, accepted no excuses,
and created a solution?
How do you do this? Break out of the confines of your hardened views and take a fresh look at the
world (or maybe just your cubicle). Reassess the programming habits you've developed, particularly
regarding fourth-generation language (4GL) development with the Oracle tools. Be creative step
away from the traditional methods, from the often limited and mechanical approaches constantly
reinforced in our places of business.
Try something new: experiment with what may seem to be a radical departure from the norm. You
will be surprised at how much you will learn, how you will grow as a programmer and problem-
solver. Over the years, I have surprised myself over and over with what is really achievable when I
stopped saying "You can't do that!" and instead simply nodded quietly and murmured "Now, if I do it
this way "
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
1.5.2 Get Ready to Establish New Habits
PL/SQL was initially a way to increase the flexibility of the ANSI-standard SQL, and soon a multi-
purpose "Swiss Army Knife" for many of Oracle's tools. Suddenly, developers found they could plop
function calls, IF-THEN-ELSE clauses, loops, and even GOTOs right into the midst of their
otherwise pristinely declarative screen modules and batch database processing routines.
Did the appearance of PL/SQL instantly transform Oracle-based applications into shining examples
for programmers of all faiths? Hardly. Sure, 4GLS are lots more productive than the older software
technology: now you can dig yourself into a pretty deep hole with a 4GL much more efficiently than
was ever possible with good old FORTRAN.
In fact, for a number of reasons, the level of sophistication of programming in PL/SQL has proven
very uneven. While many SQL*Forms developers came out of a 3GL programming environment
with a strong history in structured programming and strict guidelines, these principles have been
largely forgotten or considered inapplicable in the new 4GL world of SQL and SQL*Forms. What
does "structured code" mean when a screen is not composed of lines of code in a file, but rather as a
series of pictures and boxes of attributes in the Designer? How do you flowchart a SQL statement?
Many of us left our good programming manners behind when we sauntered into the world of
SQL*Forms. It's been hard to return to those habits, especially given some of the limitations of PL/
SQL Version 1. On the other hand, many Oracle developers are not seasoned programmers, but
relative newcomers who may have little or no formal training in computer sciences and
programming. They might, for example, have started out as end users who needed some ad hoc
queries and ended up building forms.
The first release of PL/SQL (and the later versions, for that matter) was not intended to be a
comprehensive procedural language in the same league as, say, C or COBOL. Officially, it existed
only to provide some programming constructs around the SQL language to facilitate batch database
procedures. PL/SQL did not interact with the operating system, had no debugger whatsoever, and
didn't support the normal 3GL concepts of link libraries and modularized code. As soon as Oracle
made PL/SQL available in SQL*Forms Version 3, however, thousands of Oracle developers moved
quickly to put it to work in their forms. Suddenly they could code all (well, almost all) the fancy
gizmos their users wanted.[
2]
[2] And they could do so without committing the most unnatural acts (for example,
user exits to provide pop-up windows or SQL*Forms Version 2.3 triggers that called
themselves recursively and were impossible to debug).
Damn the torpedoes and full speed ahead! But what about standards? What about modularization?
What about reusable code? Such concerns were often ignored as the volume of PL/SQL
programming exploded throughout the Oracle community. In their press to meet management
expectations of extraordinary productivity, developers did what they needed to do in what little time
they had. And few of us had time to take training in PL/SQL, even when it was offered. Few of us
had time to think about whether what we wrote could be maintained or enhanced easily. Instead we
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
just coded. And coded. And coded.
When it came time to debug a PL/SQL module, we discovered that there wasn't any debugger in PL/
SQL at all, and precious little to work with in SQL*Forms and SQL*Plus. Programmers with
backgrounds in established languages like COBOL or FORTRAN or C shook their heads and did
what they could. The many people introduced to software development through Oracle software
didn't know what they were missing. They just knew that it was very difficult to identify and then
repair problems in their code.
PL/SQL has come a long way from its first hesitant offering for the RDBMS in 1990. Developers
who have worked with the language since its first release must make sure to adapt to the changing
features and potential of PL/SQL.
1.5.3 Assume that PL/SQL Has What You Need
Programmers who are new to PL/SQL often make the mistake of starting their coding efforts before
they are sufficiently familiar with everything the language has to offer. I have seen and heard of
many instances where a developer spends valuable time writing procedures or functions that
duplicate built-in functionality provided by PL/SQL.
Please don't write a function that looks through each character in a string until it finds a match and
then returns the index of that match in the string. The INSTR function does this for you. Please don't
write a function to convert your string from uppercase to lowercase by performing ASCII code-table
shifting. Use the LOWER function instead.
With the PL/SQL of the 1990s, you also have to keep in mind much more than these basic functions.
Each new release of the database and the tools include packages that stretch the boundaries of the PL/
SQL language itself. These packages extend PL/SQL by providing additional datatypes, functions,
and procedures to handle more specialized situations. You can use DBMS_ JOB to schedule
processes from the database. You can use DBMS_PIPE to communicate information between
different Oracle sessions. The ideas and the list of prebuilt code goes on and on. Take some time to
stroll through
Part 3, Built-In Functions , and Appendix C, and get familiar with all the features that
are built into the PL/SQL language.
1.5.4 Share Your Ideas
Oracle Corporation, along with its flavor of SQL and the PL/SQL language, has been around for
close to 15 years. They have listened to user requests, kept up with the standards committees, and
generally sought to create a very robust environment for developers and users. As I've said, there is a
very good chance that what you need is already available in the language. If so, use it. If not, build it
yourself in the most general and reusable way possible. Then share it. Share your ideas and your
creations with others in your company, your Oracle User Group, even the worldwide Oracle
community through the International Oracle User's Group and User's Week convention.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Previous: 1.4 PL/SQL
Versions
Oracle PL/SQL
Programming, 2nd Edition
Next: 1.6 A Few of My
Favorite (PL/SQL) Things
1.4 PL/SQL Versions
Book Index
1.6 A Few of My Favorite
(PL/SQL) Things
The Oracle Library
Navigation
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Previous: 1.3 The Origins
of PL/SQL
Chapter 1
Introduction to PL/SQL
Next: 1.5 Advice for
Oracle Programmers
1.4 PL/SQL Versions
One thing that may complicate using PL/SQL is that it is not a single product. There are several
distinct, supported versions out there. Table 1.2 summarizes the various versions; the following
sections describe the main features available in each of the versions in use today.
Table 1.2: PL/SQL Versions
Version/Release Characteristics
Version 1.0 First available in SQL*Plus as a batch-processing script. Oracle Version 6.0 was
released at approximately the same time. PL/SQL was then implemented within
SQL*Forms Version 3, the predecessor of Oracle Forms.
Release 1.1 Available only in the Oracle Developer/2000 Release 1 tools. This upgrade
supports client-side packages and allows client-side programs to execute stored
code transparently.
Version 2.0 Available with Release 7.0 (Oracle Server). Major upgrade to Version 1. Adds
support for stored procedures, functions, packages, programmer-defined
records, PL/SQL tables, and many package extensions, including
DBMS_OUTPUT and DBMS_PIPE.
Release 2.1 Available with Release 7.1 of the Oracle Server Version. Supports programmer-
defined subtypes, enables the use of stored functions inside SQL statements, and
offers dynamic SQL with the DBMS_SQL package. With Version 2.1, you can
now execute SQL DDL statements from within PL/SQL programs.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Không có nhận xét nào:
Đăng nhận xét