http://www.developer.com/tech/article.php/777761/Day-1-Learning-the-Basics-of-PLSQL.htm
Congratulations on your decision to read this book, Sams Teach Yourself PL/SQL in 21 Days, Second Edition!
If you are new to the Oracle
environment, this book will help you learn and master Oracle's built-in
procedural language quickly. 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: Learn what PL/SQL is and why you should master it Learn how PL/SQL relates to other Oracle products Learn what resources you need to finish this book Write your first PL/SQL function Over the remaining 20 days, you'll delve 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 writing some batch jobs that run on the server. PL/SQL is a procedural language that Oracle developed as an extension to
standard SQL 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 an
application 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: The procedural logic, that is, the definition of the process, resides on
client machines. The need to look at the data from one query and use it as the basis for
the next query results in an increased amount of network traffic. 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 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. If you are developing with Oracle products, Developer/2000 for example, 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? Yes! 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. PL/SQL 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 Oracle8i Enterprise on UNIX. 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 have that knowledge.
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. PL/SQL is Oracle's Procedural Language extension to SQL. It, too,
usually runs on the database server, but some Oracle products such as
Developer/2000 also contain a PL/SQL engine that resides on the client. Thus,
you can run your PL/SQL code on either the client or the server depending on
which is more appropriate for the task at hand. Unlike SQL, PL/SQL is
procedural, not declarative. This means that your code specifies exactly
how things get done. As in SQL, however, you need some way to send your PL/SQL
code up to the server for execution. PL/SQL also enables you to embed SQL
statements within its procedural code. This tight-knit relationship between
PL/SQL, SQL, and SQL*Plus is the cause for some of the confusion between the
products. SQL*Plus is an interactive program that allows you to type in and execute SQL
statements. It also enables you to type in PL/SQL code and send it to the server
to be executed. SQL*Plus is one of the most common front ends used to develop
and create stored PL/SQL procedures and functions. What happens when you run SQL*Plus and type in a SQL statement? Where does
the processing take place? What exactly does SQL*Plus do, and what does the
database do? If you are in a Windows environment and you have a database server
somewhere on the network, the following things happen: SQL*Plus transmits your SQL query over the network to the database
server. SQL*Plus waits for a reply from the database server. The database server executes the query and transmits the results back to
SQL*Plus. SQL*Plus displays the query results on your computer
screen. Even if you're not running in a networked Windows environment, the same
things happen. The only difference might be that the database server and
SQL*Plus are running on the same physical machine. This would be true, for
example, if you were running Personal Oracle on a single PC. PL/SQL is executed in much the same manner. Type a PL/SQL block into
SQL*Plus, and it is transmitted to the database server for execution. If there
are any SQL statements in the PL/SQL code, they are sent to the server's
SQL engine for execution, and the results are returned back to the PL/SQL
program. The important thing is that SQL*Plus does not execute your SQL queries. SQL*Plus
also does not execute your PL/SQL code. SQL*Plus simply serves as your window
into the Oracle database, which is where the real action takes place. Figure
1.1 illustrates this relationship. Relationship of SQL*Plus, PL/SQL, and Oracle. Several other tools besides SQL*Plus can serve as your window to the
database. Server Manager, which has an interface similar to SQL*Plus, is
one such tool, although Oracle plans to stop supporting it sometime in the
future. If you have Oracle Enterprise Manager installed, you should take a look
at SQLPlus Worksheet. SQLPlus Worksheet is a GUI tool that is fully compatible
with SQL*Plus but is much easier to use. If you are a Developer 2000 programmer,
you'll have access to Oracle's Procedure Builder—a tool designed
for developing and debugging PL/SQL code. You'll read more about SQLPlus
Worksheet and Procedure Builder later in this chapter. SQL*Plus is used for most of the examples in this book because of its
universal availability to developers. It is perhaps still the most widely used
tool to develop, test, and create PL/SQL stored subprograms and SQL queries. Note - In addition to Oracle's tools, several third-party vendors
also have tools that can be used to develop PL/SQL code. Some of the major
products in this space are SQL-Programmer by Sylvain Faust Inc. Web address: http://www.bmc.com/ SQL-Station by Platinum Technology Inc. Web address:
http://www.cai.com/ SQL-Navigator by Quest Software. Web address:
http://www.quest.com/ Tool for Oracle Application Developers (TOAD) by Quest Software. Web
address:
http://www.quest.com/ In order to try the examples and complete the exercises in this book, you
will need access to An Oracle8i database (the Personal Edition will work) SQL*Plus or SQLPlus worksheet Note - Where possible, the exercises and
examples in this book have been designed to run equally well under both Oracle8
and Oracle8i. Many, especially those in the first nine days, will even run under
Oracle7. However, Oracle8i contains many new features that are not available in
previous releases. Days 10, 11, 12, 20, and 21, in particular, are heavily
focused on the new 8i features. If you do not currently have access to an Oracle database, there are at least
two ways to get your hands on one. For a nominal cost, you can visit
Oracle's online store and purchase a 30-day evaluation version of almost
any Oracle product, including the database. You can get to the online Oracle
Store from Oracle's home page,
http://www.oracle.com.
Another option is to join the Oracle Technology Network (OTN). OTN members can
download developer-licensed copies of Oracle's database software at no
charge. OTN members also have the option of subscribing to various technology
tracks in order to get regular shipments of Oracle software CDs. You can
register as an OTN member at no cost. The URL to visit is
http://technet.oracle.com. You will need these database privileges roles: CREATE PROCEDURE CREATE SEQUENCE CREATE SESSION CREATE TABLE CREATE TRIGGER CREATE VIEW CREATE TYPE The following Oracle-supplied packages should be available: DBMS_OUTPUT DBMS_SQL UTL_FILE DBMS_PIPE DBMS_ALERT Your database administrator can help you verify that these packages are
available to you. If you are using Oracle8i Personal Edition, you can verify the existence of
these packages by logging on as the user SYSTEM and issuing the following
query: The resulting list will show you all packages in the database owned by the
user SYS. The packages named in this chapter should be in that list. Of those,
the DBMS_OUTPUT is the most essential and is used throughout most of
the exercises and examples to display results. The other packages are discussed
only in specific chapters. Caution - I recommend that you do not use a
production database and that you create the sample tables in a schema that is
not shared with other users. If you are using Personal Oracle on your own PC,
you won't have a problem with this. If you are using an employer's
facilities, you might want to discuss use of the database with your
employer's database administrator, or DBA, as they are often called. There
is nothing inherently dangerous in any of the exercises or examples, but there
is always the risk that a coding mistake, such as an infinite loop, might tie up
CPU or I/O resources. It's always good etiquette to minimize the potential
impact of your mistakes on other developers and end users. By now you should have a basic understanding of what PL/SQL is and how it
relates to other Oracle products. You should have access to an Oracle database
environment either at work or at home. During the rest of this chapter, you will
learn some of the basics of PL/SQL, and you will write your first Oracle stored
function. New Term - PL/SQL is referred to as a block
structured language A PL/SQL block is a syntactical unit that might contain
program code, variable declarations, error handlers, procedures, functions, and
even other PL/SQL blocks. In this syntax, variable_declarations are any variables that
you might want to define. Cursor definitions and nested PL/SQL procedures and
functions are also defined here. program_code refers to the
PL/SQL statements that make up the block. exception_handlers
refers to program code that gets triggered in the event of a runtime error or
exception. The declaration section of a PL/SQL block is optional, although in practice
it is unusual not to have any declarations at all. The exception handler portion
of a PL/SQL block is also optional, and you won't see much of it until Day
7, "Procedures, Packages, Errors, and Exceptions." Note - When you're defining PL/SQL
functions, procedures, and triggers, the keyword DECLARE is not used.
When defining a function, the function specification, or function header as it
is sometimes called, begins the block. Similarly, procedure and trigger
specifications begin procedure and trigger blocks. Function, procedure, and
trigger blocks are covered in more detail on Day 2, "Writing Declarations
and Blocks." New Term - Any variable declarations must
immediately follow DECLARE and come before BEGIN. The
BEGIN and END keywords delimit the procedural portion of the
block. This is where the code goes. The EXCEPTION keyword signifies the
end of the main body of code, and begins the section containing exception
handling code. The semicolon at the end of the block, and at the end of each
statement, is the PL/SQL statement terminator, and signifies the end of
the block. Tip - Omitting the semicolon at the end of a
block is a common oversight. Leave it off, and you'll get a syntax error.
Remember to include it and you will save yourself lots of aggravation. Blocks such as the one shown in "The Syntax for a PL/SQL Block"
form the basis for all PL/SQL programming. An Oracle stored procedure consists
of one PL/SQL block. An Oracle stored function consists of one PL/SQL block. An
Oracle database trigger consists of one PL/SQL block. It is not possible to
execute PL/SQL code except as part of a block. PL/SQL blocks can be nested. One block can contain another block as in
the following example: Nesting of blocks is often done for error-handling purposes. You will read
more about error handling on Day 7. Are you ready to try writing your first PL/SQL code? Good. Remember that for
this and all other examples in this book, you will be using SQL*Plus to send the
PL/SQL code to the Oracle database for execution. Begin by running SQL*Plus and connecting to your Oracle database. Your
initial SQL*Plus screen should look like the one shown in Figure
1.2. Next, type in the following lines of code from Listing 1.1
exactly as shown. Notice the slash at the end. It must be typed in as well,
exactly as shown. Initial SQL*Plus screen. Tip - The slash at the end tells SQL*Plus that you are done typing
PL/SQL code. SQL*Plus will then transmit that code to the Oracle database
for execution. The slash has meaning to SQL*Plus only, not to PL/SQL. Tip - The slash character must be typed on a line by itself, and it
must be the first character on that line; otherwise, it will get sent to the
database and generate an error message. After you type the slash, SQL*Plus transmits your code to Oracle for
execution. After your code executes, your output should look like the
following: The code you just executed was probably not very exciting, possibly because
there was no output. PL/SQL does have some limited output facilities, and next
you will learn how to produce some simple screen output. When it was originally designed, PL/SQL had no output facilities at all.
Remember that PL/SQL is not a standalone language. It is almost always used in
conjunction with some other program or tool that handles the input, output, and
other user interaction. Oracle now includes the DBMS_OUTPUT package with PL/SQL, which
provides you with some limited output capabilities. You will learn more about
packages during Day 8, "Using SQL," but for now it's enough to
know that you can use the dbms_output.put_line procedure as shown in
Listing 1.2. The dbms_output.put_line() procedure takes exactly one argument and
generates a line of text as output from the database server. In order for you to
see that line of text, you must tell SQL*Plus to display it. This is done with
the SQL*Plus command: Type the preceding command now. It needs to be executed only once per session,
so you won't need to reissue it unless you exit SQL*Plus and get back
in again. Next, type in the PL/SQL code from Listing 1.2. The resulting output from
SQL*Plus should look like that shown below. Note - It is SQL*Plus that prints the server output on the screen for you to see.
You must remember to execute the SET SERVEROUTPUT ON command, or
you won't see any output. You also can use the SET SERVEROUTPUT OFF
command to turn off output when you don't want to see it. Until now, you have been retyping each PL/SQL block as you tried it. If you
made a mistake, you had to type the code all over again. There are some
alternatives to typing PL/SQL straight into SQL*Plus. Depending on your personal
preferences, and on what you are trying to do, there are three basic ways to go
about this: Cut and paste from Notepad. Execute a text file using the SQL*Plus @ command. Use the SQL*Plus EDIT command. The first method involves running Windows Notepad, typing your PL/SQL code
(or SQL queries) into it, and then copying and pasting from Notepad into
SQL*Plus to execute the desired code. This method is ideal for experimenting
with short snippets of PL/SQL code and SQL queries. You can keep several related
items in the same text file where you can easily call them up when you want to
work on them. The second method makes use of a SQL*Plus command to execute a file. For
example, if you have a text file named test.sql with the code from
Listing 1.2, you could execute that file by typing this command: The resulting output would look like: Note - When you're executing a file, the default file extension is .SQL.
SQL*Plus looks for the file first in the default directory and then follows
a search path that you can define. How you define this path is operating system–specific
and outside the scope of this book. For details, you should consult the SQL*Plus
User's Guide and also your operating system documentation. Executing commands from a file like this is most useful in cases where you
are re-creating a stored procedure, function, or database trigger and you have
the definition already stored in its own text file. The third option involves using the SQL*Plus EDIT command to invoke
your system's text editor. Under Windows, this will be Notepad unless you
have specifically defined a different editor. When you issue the EDIT
command, SQL*Plus will launch Notepad and automatically place in it the text
of the most recently executed PL/SQL block or SQL statement. See Figure
1.3 for an example of this. Using the SQL*Plus EDIT command. After you've brought up Notepad, you can edit the PL/SQL block to your
satisfaction and then exit from Notepad, being sure to save the file. When you
save your file, SQL*Plus will not immediately reexecute it. It is merely placed
in an internal buffer. You must use the / command, by typing /
on a line by itself, in order to execute the code you just edited. Using the EDIT command works well as long as you keep in mind one
important thing. SQL*Plus remembers only the most recent SQL statement or PL/SQL
block. If you have been working on a PL/SQL block, and you execute just one SQL
statement, that statement will replace the PL/SQL block you have been
editing. Caution - Do not allow the SQL*Plus buffer to
contain your only copy of a long procedure. It's too easy to enter a SQL
command without thinking and wipe out the much longer PL/SQL procedure you have
been developing. Which of these three methods you choose is up to you, and depends in part on
your personal preferences. You are likely to find the first method, copying and
pasting between Notepad and SQL*Plus, most useful during the first few chapters
of this book. As you write larger PL/SQL functions and procedures, you will find
yourself gravitating toward keeping each in its own file. Perhaps one of the most useful things you can do with your knowledge of
PL/SQL is to use it to write stored functions and stored procedures.
Encapsulating the code you wrote earlier into a stored function enables you to
compile it once and store it in the database for future use. The next time you
want to run that PL/SQL block, all you need to do is invoke the function. Using
SQL*Plus, type in the input code shown in Listing 1.3, which will create a
PL/SQL function to return the value that was output by Listing 1.2. Compare the code in Listing 1.3 to that in Listing 1.2. Notice that the
keyword DECLARE has been replaced in lines 1 and 2 by the words
CREATE OR REPLACE FUNCTION ss_thresh RETURN NUMBER AS. This will be
explained further in Day 3. Also notice that the calls to
dbms_output.put_line() have been replaced by the RETURN
command (line 6), which returns the value of the variable X to the
caller. The only output from Listing 1.3 is a confirmation that the function has
been successfully created, which is shown in line 9. Notice that Oracle has created the function. SQL*Plus indicates this by
displaying the words Function created. You probably were able to type in the code from Listing 1.3 and create the
SS_THRESH function with no errors. However, that might not have been
the case. To show you how to deal with an error, Listing 1.4 contains the same
code as Listing 1.3, but with one small error. Unlike most compilers, which will display a listing of errors found in source
code, Oracle stores any errors it finds in a database table named
USER_ERRORS. If you want to see the specific details, and you may well,
you need to retrieve the error listing yourself. Use the SQL*Plus command
SHOW ERRORS, as shown in Listing 1.5, to do this. As you can see, the error listing has two columns of output. The first column
contains the line number where the error occurred and also the character
position within that line. The second column contains the specific error
message. In this example, the error occurred in line 5 at the fifth character
position. The error message tells you that Oracle encountered an equal sign when
it was really expecting something else. That "something else," in this
case, is the assignment operator, represented by :=. Figure 1.4 shows the SQL*Plus screen as it would look after executing Listings 1.4 and 1.5. Error listing for SS_THRESH. Tip - Typing = instead of :=
is a common mistake to make, especially if you also program in other languages
that do use = for assignment. Now that you have written and compiled the function, it's time to
execute it and see the results. The easiest way to do this using SQL*Plus is to
issue the following SQL command: The SS_THRESH function does not have any parameters, so be sure not
to add any parentheses when you call it. In other words, don't use
SS_THRESH() because Oracle will return an error. The table
DUAL is a special Oracle table that always exists, always has exactly
one row, and always has exactly one column. It's the perfect table to use
when experimenting with functions. Selecting the function from the DUAL
table causes the function result to be displayed. The SS_THRESH function is a very simple function, and you might
rightly wonder if something so absurdly simple can be useful. The value this
function returns is the Social Security Contribution and Benefit Base, a value
that changes from year to year. If you were a programmer working on a payroll
system and needed to write several queries using this value, you could use a
function like this to encapsulate this information. To encapsulate
information means to embed it within a function so that values like this
don't need to be replicated all through your code, and so that any changes
can be made in one central place. There's another benefit to this approach.
Your queries become more self-documenting. It's a bit easier to remember
six months later what you meant when you see than if you had simply hard-coded the value Procedure Builder is part of Oracle's Developer 2000 development
environment. It allows you to develop and debug PL/SQL program units for use in
Developer 2000 applications. With Developer 2000, PL/SQL is used on the client
to program the behavior behind the forms, reports, and menus that you develop.
You end up with a PL/SQL engine on the client as well as on the server. A nice
benefit of this is that Procedure Builder can be used to execute PL/SQL code
without having to be connected to a database. Note - Many of the advanced features discussed
later in this book are available only when executing PL/SQL code in the
database. If you have Developer 2000 installed, you start Procedure Builder by selecting
Start, Programs, Developer 2000 R2.0, Procedure Builder. The opening screen
is shown in Figure 1.5 and is divided into
three sections. Procedure Builder's opening screen. As you can see, the Procedure Builder window is divided into three major
parts. The Object Navigator window allows you to navigate through the various
program units, PL/SQL libraries, and database objects to which you have access.
The other two parts of the display combine to make up the PL/SQL Interpreter
window. The top pane is used when debugging PL/SQL code and shows the code being
debugged. The bottom pane is where you can type in and execute ad-hoc PL/SQL
blocks. New Term - PL/SQL may be used to write
procedures, functions, package bodies, package types, and triggers. These
constructs are referred to as program units. The PL/SQL interpreter allows you to enter a PL/SQL anonymous block and have
it executed. The small block in Listing 1.2, that you typed in earlier, is one
such anonymous block. You can type that block into Procedure Builder and execute
it, but first you need to make one small change. The code shown in Listing 1.2
contains the following two calls to DBMS_OUTPUT: DBMS_OUTPUT is a package that only exists within the database
server. Procedure Builder will return errors if you try to execute the code as
it stands now. Fortunately, Oracle has a package similar to DBMS_OUTPUT
that can be used in its place when you are executing code on a client. The name
of that package is TEXT_IO, and it also contains an entry point named
PUT_LINE. Take the code shown in Listing 1.2, replace the calls to
DBMS_OUTPUT.PUT_LINE with TEXT_IO.PUT_LINE, and you have the
code shown in Listing 1.6. This code will run from Procedure Builder. Now, you can take this code and type it into Procedure Builder's PL/SQL
Interpreter. The interpreter will automatically execute the block when you
finish entering the last line. The results will look like the following: Procedure Builder has been written specifically to work with PL/SQL. Unlike
SQL*Plus, you do not need to enter a forward-slash to tell Procedure Builder
that you are done entering a block of PL/SQL. Creating a function (or any other program unit such as a procedure or package)
using Procedure Builder requires a bit more than just typing the CREATE
FUNCTION statement into the interpreter. To create a function, you need
to tell Procedure Builder that you want to create a new program unit. Do this
by selecting the File, New, Program Unit menu option. You will see the dialog
box shown in Figure 1.6. Creating a New Program Unit. This dialog contains radio buttons allowing you to choose the type of program
unit that you are creating and also contains a textbox for the program unit's
name. Choose Function, type the name SS_THRESH into the textbox, and
click OK. You will see a screen similar to that shown in Figure
1.7. Entering the code for SS_THRESH. Figure 1.7 shows the function with the code
already written. Of course, Procedure Builder does not write the code for you.
When Procedure Builder opens this window, it places a skeleton function in the
textbox. You have to fill in the details. When you get the code entered the
way that you want it, click the Compile button to compile it, and then click
the Close button to close the window. To execute the function that you just created, type the following statement
into the PL/SQL interpreter: When you execute this statement, Procedure Builder will execute the function
and display the following results: In addition to creating PL/SQL program units on the client, Procedure Builder
can also be used to create and execute program units in a database. To do this,
you first need to connect to a database. Use the File, Connect menu option to
connect to a database. Once you've logged in, you will be able to browse
database program units using the Object Navigator. Figure
1.8 shows the program units owned by the user named JEFF. Program units in the JEFF schema. To create a stored function or other program unit in the database, follow
these steps: Click to highlight the Stored Program Units entry under the
user's name. Click the Create Toolbar button. Proceed as you would when
creating a local program unit. Except for having to choose the schema, the process for creating a PL/SQL
function in the database is the same as for creating one locally. If you have Enterprise Manager available, consider using SQLPlus Worksheet
for the examples in this book. SQLPlus Worksheet is completely compatible with
SQL*Plus, and can be used for all the examples in this book. The advantage that
SQL*Plus worksheet has over SQL*Plus is in the interface. Rather than type in
large blocks of code one line at a time, you can use a text editor-like
interface. After you get the code entered the way that you want it, you can
click a toolbar button to execute it. Figure 1.9 shows the SQLPlus Worksheet. The SQLPlus Worksheet. In this chapter you learned a little about PL/SQL, what it is, and why it is
used. You know that PL/SQL is Oracle's procedural language extension to
SQL, and that you can use it to write procedures and functions that execute on
the server. This chapter also explains the relationship between PL/SQL, SQL, and
SQL*Plus. This should give you a good grasp of how PL/SQL fits into the larger
Oracle picture. You wrote your first PL/SQL stored function, which should give you a good
feel for the mechanics of programming with PL/SQL. SQL*Plus is the tool used throughout this book for PL/SQL code examples.
SQLPlus Worksheet and Procedure Builder are two other tools that may also be
used to write and execute PL/SQL code. Q&A As you can see, the SQLPlus Worksheet screen is divided into two
halves. The upper half is used for the entry and editing of SQL statements and
PL/SQL blocks. The lower half is used to display output. The execute toolbar
button, the one with the lightning bolt, is used to execute the statements that
you have entered in the upper pane. There are two ways to use SQLPlus Worksheet to execute commands from a file.
One way is to use the File, Open menu option to load the contents of a file into
the upper pane, and then click the lightning bolt button. The other way is to
use the Worksheet, Run Local Script menu option. Where does PL/SQL code execution take place? Usually, execution takes place at the server level. For the
examples in this book, that will always be the case. Some Oracle products, such
as Developer/2000, also have the capability to execute PL/SQL blocks locally on
the client machine. Can I write a complete application with PL/SQL? Generally speaking you cannot, at least not as most people
envision an application. For an end-user application, you would still need a
tool, such as PowerBuilder or Developer/2000, in order to design screens and
generate reports. I executed some PL/SQL code which used
dbms_output.put_line() to print some data, but I didn't see
anything. How come? You probably forgot to enable the server output option. Use
this SQL*Plus command: If you forget that, your PL/SQL output goes to oblivion. I am using Procedure Builder, and I get errors when I try to
execute code that contains calls to dbms_output.put_line().
Why? When you use Procedure Builder to execute code
locally, you must use text_io.put_line rather than
dbms_output.put_line(). If you are using Procedure Builder, and you
have connected to a database, you will be able to execute calls to
dbms_output.put_line(), but you won't see the results. Use the following workshop to test your comprehension of this chapter and put
what you've learned into practice. You'll find the answers to the quiz
and exercises in Appendix A, "Answers." What tells SQL*Plus to send your PL/SQL code to the Oracle database for
execution? What is the fundamental basis of all PL/SQL code? List an advantage of pushing program logic up to the server
level. Name three Oracle products that use PL/SQL. What command tells SQL*Plus to display PL/SQL output? Name
at least two options for managing your PL/SQL source code. If you didn't encounter any errors when compiling your first
function, try putting some in on purpose. Then try out the SHOW ERRORS
command. Try each of the three ways mentioned in the chapter for
managing your source code. Become familiar with the SQL*Plus EDIT
command. Try using the @ command or the START command to
execute your PL/SQL code from a text file. This article is brought to you by Sams Publishing, publisher of Sams
Teach Yourself PL/SQL in 21 Days, Second Edition.
Day 1: Learning the Basics of PL/SQL
June 4, 2001
What Is PL/SQL?
Why Learn PL/SQL?
SQL, SQL*Plus, PL/SQL: What's the
Difference?
SQL
PL/SQL
SQL*Plus
What You Need to Finish This Book
SELECT object_name
FROM dba_objects
WHERE owner='SYS'
AND object_type = 'PACKAGE';
Getting Started with PL/SQL
PL/SQL Is Block Structured
The Syntax for a PL/SQL Block
DECLARE
variable_declarations
BEGIN
program_code
EXCEPTION
exception_handlers
END;
DECLARE
variable declarations go here
BEGIN
some program code
BEGIN
code in a nested block
EXCEPTION
exception_handling_code
END;
more program code
END;
Compiling and Executing a Simple Block
Relationship of SQL*Plus, PL/SQL, and Oracle.
Listing 1.1 Your First PL/SQL Block
DECLARE
x NUMBER;
BEGIN
x := 72600;
END;
/
declare
x integer;
begin
x := 65400;
end;
/
PL/SQL procedure successfully completed
What About Some Output?
Listing 1.2 PL/SQL Block Showing the Use of the dbms_output.put_line
Procedure
DECLARE
x NUMBER;
BEGIN
x := 72600;
dbms_output.put_line('The variable X = ');
dbms_output.put_line(x);
END;
/
SQL> SET SERVEROUTPUT ON
The variable x=
72600
Alternatives to Retyping
SQL> @c:\a\test
The variable X =
65400
Writing Your First Function
Listing 1.3 The SS_THRESH Function
1: CREATE OR REPLACE FUNCTION ss_thresh
2: RETURN NUMBER AS
3: x NUMBER;
4: BEGIN
5: x := 72600;
6: RETURN x;
7: END;
8: /
Function created
Finding Compilation Errors
Listing 1.4 The SS_THRESH Function with an Error
1: CREATE OR REPLACE FUNCTION ss_thresh
2: RETURN NUMBER AS
3: x NUMBER;
4: BEGIN
5: x = 72600;
6: RETURN x;
7: END;
8: /
Warning: Function created with compilation errors.
Listing 1.5 The SHOW ERRORS Command
1: SHOW ERRORS
Errors for FUNCTION SS_THRESH:
LINE/COL ERROR
-------- ---------------------------------------------------------------
5/5 PLS-00103: Encountered the symbol "=" when expecting one of the
following:
:= . ( @ % ;
The symbol ":= was inserted before "=" to continue.
Displaying the Function's Return Value
SELECT SS_THRESH FROM DUAL;
SS_THRESH
---------
72600
Can Even This Simple Function Be Useful?
SELECT * FROM employee_table
WHERE emp_salary > SS_THRESH;
SELECT * FROM employee_table
WHERE emp_salary > 72600;
Executing PL/SQL Using Developer 2000's
Procedure Builder
Starting Procedure Builder
Using Interactive PL/SQL
dbms_output.put_line('The variable X = ');
dbms_output.put_line(x);
Listing 1.6 A PL/SQL Block Using TEXT_IO That Will Run from Procedure
Builder
DECLARE
x INTEGER;
BEGIN
x := 72600;
text_io.put_line('The variable X = ');
text_io.put_line(x);
END;
The variable X =
72600
Creating the SS_THRESH Function
TEXT_IO.PUT_LINE(SS_THRESH);
72600
Connecting to a Database
Using SQLPlus Worksheet
Executing a PL/SQL Block Using SQLPlus
Worksheet
Summary
Q & A
SET SERVEROUTPUT ON
Workshop
Quiz
Exercises
){kind=link}