Page 1
Get ready! You are about to embark on a three-week journey to
learn Oracle's PL/SQL programming language. PL/SQL is at the core
of several Oracle products, including Developer/2000 and the
Oracle database itself. During the first week, you will cover the basics of the
PL/SQL language. During the second week, you will learn some of
the more advanced features, and you will also learn how to use PL/SQL
in conjunction with the database. Week 2 also explores some of
Oracle8's new object-oriented features. The third week takes you into the
world of Oracle's built-in packages.
Where You Are Going
Day 1 starts the week off with an introduction to PL/SQL, a discussion of how it relates to other Oracle products, and an example of a stored function written in PL/SQL. Day 2 discusses selecting a development
Page 2
tool and reviews some of the choices available to you in this area. On Days 3 and 4, you will move on to learn the basics of the PL/SQL language. You will learn about the datatypes available to you in PL/SQL and what they can be used for; how to write PL/SQL expressions; and about the block structure used to write PL/SQL code and what that means to you as a developer. Days 5 and 6 teach you about the various control structures, such as loops and IF statements, that are available. The week finishes with Day 7 and a discussion of PL/SQL's built-in functions. Day 7 discusses the major categories of functions that are available, gives you some tips for experimenting with them, and provides examples showing how you can use the more commonly used functions. Good luck and have fun!
Page 3
Week 1
by Jonathan Gennick
Congratulations on your decision to read this book, Teach Yourself PL/SQL in 21 Days! If you are new to the Oracle environment, this book will help you quickly learn and master Oracle's built-in procedural language. Knowledge of PL/SQL (Procedural Language/Structured Query Language) is becoming a fundamental necessity no matter which of Oracle's many products you use.
Today, on your first day, you will accomplish these tasks:
Page 4
Over the remaining 20 days, you'll delve ever deeper into the power and capabilities of this language and learn how to leverage its power in your applications regardless of whether you are doing client/server programming with Oracle's tools (such as Developer/2000), using other front-end tools (such as PowerBuilder), or simply need to write some batch jobs that run on the server.
PL/SQL is a procedural language that Oracle developed as an extension to standard SQL in order to provide a way to execute procedural logic on the database.
NEW TERM
If you have worked with relational databases in the past, you are no doubt
familiar with SQL, which stands for Structured Query
Language. SQL itself is a powerful declarative language. It is
declarative in the sense that you describe the results that you
want but not how they are obtained. This is good because you can insulate your applications
from the specifics of how the data is physically stored. A competent SQL programmer can also
push a great deal of processing work back to the server level through the creative use of SQL.
There are limits, though, to what you can accomplish with a single declarative query. The real world is seldom as neat and clean as we would like it to be. Developers often find themselves needing to execute several queries in succession and process the specific results of one query before going on to the next. This leads to two problems in a client/server environment:
Why are these problems? The procedural logic on client machines can quickly become out of sync if the software is upgraded. It can also be implemented incorrectly, resulting in a loss of database integrity. The need to pull down large amounts of intermediate data to a client results in a long wait for the end users who must sit there staring at the hourglass while the data is transferred to their machines. The cumulative effects of a number of clients pulling large amounts of data across the network add to the slowdown and further decrease performance.
PL/SQL provides a mechanism for developers to add a procedural component at the server level. It has been enhanced to the point where developers now have access to all the features of a full-featured procedural language at the server level. It also forms the basis for programming in Oracle's continually evolving set of client/server development tools, most notably Developer/2000.
Page 5
If you are developing with Oracle products, such as Developer/2000, Oracle Forms, or Oracle Reports, the answer to this question is simple. You need to know PL/SQL because those products use PL/SQL for any procedural code. But what if you don't develop with Oracle's products? What if all you use is Oracle's database engine? Is PL/SQL of any use to you? Absolutely, it is!
Regardless of the front-end tool that you are using, you can use PL/SQL to perform processing on the server rather than the client. You can use PL/SQL to encapsulate business rules and other complicated logic. It provides for modularity and abstraction. You can use it in database triggers to code complex constraints, which enforce database integrity; to log changes; and to replicate data. It can also be used with stored procedures and functions to provide enhanced database security. Finally, it provides you with a level of platform independence. Oracle is implemented on many hardware platforms, but PL/SQL is the same on all of them. It makes no difference whether you are running Personal Oracle on a laptop or Oracle7 Workgroup Server on Windows NT.
Regardless of what development tools you use, if you are developing in an Oracle environment, your knowledge of PL/SQL and your ability to apply it will give you a competitive advantage against those who do not. With PL/SQL you have the power to make your applications more robust, more efficient, and more secure.
This question has bedeviled many people new to Oracle. There are several products with the letters "SQL" in the title, and these three, SQL*Plus, SQL, and PL/SQL, are often used together. Because of this, it's easy to become confused as to which product is doing the work and where the work is being done. This section briefly describes each of these three products.
SQL stands for Structured Query Language. This has become the lingua franca of database access languages. It has been adopted by the International Standards Organization (ISO) and has also been adopted by the American National Standards Institute (ANSI). When you code statements such as SELECT, INSERT, UPDATE, and DELETE, SQL is the language you are using. It is a declarative language and is always executed on the database server. Often you will find yourself coding SQL statements in a development tool, such as PowerBuilder or Visual Basic, but at runtime those statements are sent to the server for execution.