Introduction to PL/SQL

This chapter introduces PL/SQL Basics,PL SQL Block structure,PL SQL Architecture,Advantage of PL/SQL etc

What Is PL/SQL?

PL/SQL stands for “Procedural Language extensions to SQL.” PL/SQL is available primarily as an “enabling technology” within other software products; it does not exist as a standalone language. You can use PL/SQL in the Oracle relational database, in the Oracle Server, and in client-side application development tools, such as Oracle Forms. PL/SQL is closely integrated into the SQL language, yet it adds programming constructs that are not native to this standard relational database language. PL/SQL allows you to combine SQL statements with “standard” procedural constructs.

PL/SQL Block Structure

PL/SQL is a block-structured language. That is, the basic units  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 subproblem to be solved.

A block (or sub-block) lets you group logically related declarations and statements. That way, you can place declarations close to where they are used. The declarations are local to the block and cease to exist when the block completes.

A  PL/SQL block has three parts: a declarative part, an executable part, and an exception-handling part. (In PL/SQL, a warning or error condition is called an exception.) Only the executable part is required.

Declaration Section:
The Declaration section of a PL/SQL Block starts with the keyword DECLARE. Declaration section is optional. it is used to declare any variables, constants, records and cursors, which are used to manipulate data in the execution section.

Begin-End(Execution) Section:
This is a mandatory section.The Execution section of a PL/SQL Block starts with the reserved keyword BEGIN and ends with END. It is the section where the program logic is written to perform any task. The programmatic constructs like loops, conditional statement and SQL statements form the part of execution section.

Exception Section:
This section is optional..The Exception section of a PL/SQL Block starts with the reserved keyword EXCEPTION. Any errors in the program can be handled in this section. If the PL/SQL Block contains exceptions that cannot be handled, the Block terminates with errors.

The PL/SQL Block structure is given below

DECLARE
–Declarations

BEGIN

–Statements

EXCEPTION

–Exception Handlers

END

An example of PL/SQL program is given below

DECLARE

emp_nam varchar2(30);

BEGIN

SELECT employee_name into emp_nam FROM EMPLOYEES

WHERE emp_sal >100000;

EXCEPTION

—optional

WHEN NO_DATA_FOUND THEN

null;

END;

PL/SQL Architecture

The PL/SQL compilation and run-time system is an engine that compiles and executes PL/SQL blocks and subprograms. The engine can be installed in an Oracle server or in an application development tool such as Oracle Forms.

In either environment, the PL/SQL engine accepts as input any valid PL/SQL block or subprogram. Figure shows the PL/SQL engine processing an anonymous block. The PL/SQL engine executes procedural statements but sends SQL statements to the SQL engine in the Oracle database.

PL/SQL Engine

PL/SQL is an unusual — and an unusually powerful — programming language. You can write programs that look just like traditional 3GL modules, but you can also include calls to SQL statements, manipulate data through cursors, and take advantage of some of the newest developments in programming languages. PL/SQL supports packages which allow you to perform object-oriented design. PL/SQL provides a powerful mechanism for trapping and, with exception handlers, resolving errors. The tight integration of PL/SQL with SQL provides developers with the best of both worlds — declarative and procedural logic.

Advantages of PL/SQL

PL/SQL is a completely portable, high-performance transaction processing language that offers the following advantages:


Fundamentals of PL/SQL

Comments disabled

Comments on this article have been disabled.