CHAPTER 10



CHAPTER 1

INTRODUCTION TO PL/SQL

------------------------------------------------------------------------------------------------------------

PL/SQL is the Oracle's procedural language extension to SQL, the non-procedural relational database language. PL/SQL fully integrates modern software engineering features such as data encapsulation, information hiding, overloading, and exception handling, and so brings state-of-the-art programming to the ORACLE Server and a variety of ORACLE tools.

OVERVIEW OF PL/SQL

With PL/SQL, you can use SQL statements to manipulate ORACLE data and flow-of-control statements to process the data. Moreover, you can declare constants and variables, define subprograms (procedures and functions), and trap runtime errors. Thus, PL/SQL combines the data manipulating power of SQL with the data processing power of procedural languages.

PL/SQL is a block-structured language. That is, the basic units (procedures, functions, and anonymous blocks) that make up a PL/SQL program are logical blocks, which can contain any number of nested sub-blocks. Typically, each logical block corresponds to a problem or sub problem to be solved.

It is more efficient to use SQL statements within PL/SQL blocks because network traffic can be decreased significantly, and an application becomes more efficient as well.

When a SQL statement is issued on the client computer, the request is made to the database on the server, and the result set is sent back to the client. As a result, a single SQL statement causes two trips on the network. If multiple SELECT statements are issued, the network traffic can increase significantly very fast. For example, four SELECT statements cause eight network trips. If these statements are part of the PL/SQL block, there are still only two network trips made as in case of a single SELECT statement.

OBJECTIVES

This chapter will provide a basic understanding of some of the basics of PL/SQL language. We will briefly cover the following topics:

• PL/SQL Block Structure

• EXECUTING a PL./SQL program

• Generating Output

• Comments in PL/SQL

• PL/SQL Terminology

• PL/SQL Character set

o Lexical units

• Arithmetic and Relational operators

• Miscellaneous Symbols

• Variables in PL/SQL

• Reserve words

• Common Datatypes

• SELECT INTO command

• Substitution variables

PL/SQL BLOCK STRUCTURE

A block is the most basic unit in PL/SQL. All PL/SQL programs are combined into blocks. These blocks can also be nested within the other. Usually, PL/SQL blocks combine statements that represent a single logical task.

PL/SQL blocks can be divided into two groups: named and anonymous. Named blocks are used when creating subroutines. These subroutines are procedures, functions, and packages. Anonymous PL/SQL blocks, as you have probably guessed, do not have names. As a result, they cannot be stored in the database and referenced later.

PL/SQL blocks contain three sections: declaration section, executable section, and exception-handling section. The executable section is the only mandatory section of the block. Both the declaration and exception handling sections are optional. As a result, a PL/SQL block has the following structure:

DECLARE

Declaration statements

BEGIN

Executable statements

EXCEPTION

Exception-handling statements

END;

DECLARATION SECTION

The declaration section is the first section of the PL/SQL block. It contains definitions of PL/SQL identifiers such as variables, constants, cursors, and so on. PL/SQL identifiers are covered in detail throughout the book.

EXECUTABLE SECTION

The executable section is the next section of the PL/SQL block. This section contains some executable statements that allow you to manipulate the variables that have been declared in the declaration section.

EXCEPTION-HANDLING SECTION

The exception-handling section is the last section of the PL/SQL block. This section contains statements that are executed when a runtime error occurs within the block. Runtime errors occur while the program is running and cannot be detected by the PL/SQL compiler. Once a runtime error occurs, the control is passed to the exception-handling section of the block. The error is then evaluated, and a specific exception is raised or executed.

The following is an example of a PL/SQL program.

|-- filename: DisplayAge.sql |

|SET SERVEROUTPUT ON |

|DECLARE |

|num_age NUMBER(3) := 20; |

|BEGIN |

|num_age := 23; |

|DBMS_OUTPUT_PUT_LINE(‘My age is ‘ || num_age); |

|END; |

|/ |

The above example declares a variable named num_age in the declaration section. This variable has a type of NUMBER, and can store up to 3 digit long. Its default value is 20.

The executable section begins with num_age is assigned a new value 23, followed by an output statement that displays the value on the screen.

Notice that the program ends with a “/”, which is a required character used to execute the program.

EXECUTING A PL/SQL PROGRAM

PL/SQL can be executed directly in SQL*Plus. A PL/SQL program is normally saved with an .sql extension. To execute an anonymous PL/SQL program, simply type the following command at the SQL prompt:

SQL> @DisplayAge

Or

SQL> execute DisplayAge

in which a file named DisplayAge.sql is executed.

GENERATING OUTPUT

Like other programming languages, PL/SQL provides a procedure (i.e. PUT_LINE) to allow the user to display the output on the screen. For a user to able to view a result on the screen, two steps are required.

First, before executing any PL/SQL program, type the following command at the SQL prompt (Note: you need to type in this command only once for every SQL*PLUS session):

SQL> SET SERVEROUTPUT ON;

or put the command at the beginning of the program, right before the declaration section.

Second, use DBMS_OUTPUT.PUT_LINE in your executable section to display any message you want to the screen.

Syntax for displaying a message:

DBMS_OUTPUT.PUT_LINE();

in which PUT_LINE is the procedure to generate the output on the screen, and DBMS_OUTPUT is the package to which the PUT_LINE belongs.

If we look at the example discussed earlier, notice that the “SET SERVEROUTPUT ON” is located right before the DECLARE block. Doing so avoid the hassle to type in the command every time you log into SQL*PLUS.

In the executable section, the PUT_LINE function is used to display the age after it is changed.

The following shows the result of executing DisplayAge.sql:

|SQL> @DisplayAge |

| |

|My age is 23 |

Behind the scene, the DBMS_OUTPUT.PUT_LINE procedure writes the message to be displayed to the buffer for storage. Once a program has been completed, the information in the buffer is displayed on the screen. The size of the buffer is initialized at 2000 bytes, and can be set between 2000 and 1,000,000 bytes. To change the buffer size, use the following command instead of the default “SET SERVEROUTPUT ON”:

SQL> SET SERVEROUTPUT ON SIZE 10000;

Bear in mind that if the message to be displayed in a program exceeds the defined buffer size, a warning message will be prompted and the subsequent messages after the limited will not be displayed.

To turn off the feature of displaying a message on screen, you can type in the following command:

SQL> SET SERVEROUTPUT OFF;

COMMENTS

Single line comments begin with two hyphens.

-- This is a comment.

Multi-line comments begin with /* and end with */.

/*

This is a multi-line

comment.

*/

TERMINOLOGY

An executable is a file or the name of a program written using one of the computer programming languages. The program executes when you type the name of the executable at the command prompt. For example, when you enter sqlplus at the command prompt, you will invoke SQLPLUS.

A character set describes the range of characters that a computer language supports and displays in reports. Most programming languages, including PL/SQL, can display as text and almost any other character set.

Arithmetic operators are mathematical operations which are use to do calculations with data. Common operators are +, -, *, and /.

Relational operators are used for comparison with data, such as comparing two dates to see their relationship to one another. Common operators are >, |Greater than |

|< |Less than |

|= |Equal |

|>= |Greater than or equal to |

| ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download