Introduction
- PL/SQL is a procedural language extension to
SQL, which is the standard data access language for relational databases.
- PL/SQL is a powerful transaction processing
language. It allows the data manipulation and query statements of SQL to
be included in block-structured and procedural units of code.
- It combines SQL with the design capabilities of
procedural programming languages such as C and FORTRAN.
- PL/SQL offers modern software engineering
features such as data encapsulation and exception handling.
- In object-oriented programming, encapsulation
is the inclusion in a program object of all the resources needed for the
object to function.
- PL/SQL offers features that enable information
hiding and object orientation.
- PL/SQL uses SQL statements to manipulate
Oracle data and flow-of-control statements to process the data.
- PL/SQL fully supports SQL datatypes. This
reduces the need to convert data passed between your applications and
the database.
Benefits of PL/SQL
- PL/SQL provides several advantages that are
available in the procedural programming languages.
- PL/SQL is a block-structured language. This
means that the basic units that make up a PL/SQL program, such as
procedures, functions, and anonymous blocks, are logical blocks, which
can contain any number of nested subblocks.
- One of the benefits of using PL/SQL is that
you can modularize program development.
- This means that you can group logically
related statements within blocks.
- Modularity lets you break an application down
into manageable, well-defined logic modules. PL/SQL meets this need with
program units.
- The modular structure of PL/SQL programs
enables you to break down a complex problem into a set of manageable,
well-defined, logical modules.
- Besides blocks and subprograms, PL/SQL
provides packages, which allow you to group related program items into
larger units.
- Modularized program development enables you to
place the reusable PL/SQL code in libraries.
- PL/SQL code in libraries can be shared among
Oracle Developer applications.
- You can also store this code in an Oracle
server to make it accessible to any application that can interact with
an Oracle database.
- Typically, each logical block corresponds to a
problem or subproblem to be solved. PL/SQL supports the
divide-and-conquer approach to problem solving called stepwise
refinement.
- Applications written in PL/SQL are portable to
any operating system and platform on which Oracle runs.
- Therefore PL/SQL programs can run anywhere
that Oracle can run. You can also write portable program libraries,
which can be reused in different environments.
- Another benefit of PL/SQL is that you can
declare identifiers such as variables and constants. You can use these
identifiers in SQL and procedural statements.
- An additional benefit of PL/SQL is that it
enables you to declare variables of various datatypes.
- PL/SQL enables you to declare variables
dynamically, based on the data structure of tables and columns in the
database.
- Control structures are an important PL/SQL
extension to SQL. Not only does PL/SQL let you manipulate Oracle data, it
also lets you process the data using conditional, iterative, and
sequential flow-of-control statements. Collectively, these statements
can handle any situation.
- The procedural language control provided by
PL/SQL enables you to process the rows returned by a multiple-row query
individually with an explicit cursor.
- PL/SQL adds functionality to nonprocedural
tools such as Oracle Forms and Oracle Reports.
- With PL/SQL in these tools, you can use
familiar procedural constructs to build applications. Moreover, PL/SQL
is the same in all environments.
- For example, scripts written with one tool can
be used by other tools.
- A good programming language provides
capabilities for handling errors and recovering from them if possible.
- PL/SQL implements error handling via
exceptions and exception handlers. In PL/SQL, a warning or error
condition is called an exception.
- You can declare user-defined error conditions
and process them with an exception handling routine.
- PL/SQL plays a central role to both the Oracle
server and Oracle development tools. This is done through stored
procedures, stored functions, database triggers, and Oracle Developer
component triggers.
- PL/SQL also improves performance by adding
procedural processing power to Oracle tools, such as Oracle Forms and
Reports.
- Using PL/SQL, a tool can do any computation
quickly and efficiently without calling on the Oracle server. This saves
time and reduces network traffic.
- The PL/SQL and SQL languages are tightly
integrated. PL/SQL supports all of the SQL datatypes and the non value
NULL. This allows you to manipulate Oracle data easily and efficiently.
It also helps you to write high-performance code.
- Without PL/SQL, Oracle processes SQL
statements one at a time. Each SQL statement results in another call to
Oracle and higher performance overhead.
- With PL/SQL, an entire block of statements can
be sent to Oracle at one time. This can drastically reduce the network
and performance overhead.
PL/SQL Engine
- PL/SQL is a technology that is used by the
Oracle server and by certain Oracle tools. Blocks of PL/SQL are passed
to and processed by a PL/SQL engine, which may reside in the tool or in
the Oracle server.
- Many Oracle tools, including Oracle Developer,
have their own PL/SQL engine. This PL/SQL engine is independent of the
engine present in the Oracle server.
- The engine that is used depends on the source
from where the PL/SQL block of code is invoked.
- When you submit PL/SQL blocks from a Pro*
program, user-exit, SQL*PLUS, or Server Manager, the PL/SQL engine in the
Oracle server processes them.
- It separates out the SQL statements and sends
them individually to the SQL statement executor.
- The PL/SQL engine executes procedural
statements but sends SQL statements to the SQL Statement Executor in the
Oracle server.
- By filtering out the SQL statements, only a
single transfer is required from the application to the Oracle Server.
This reduces network traffic and greatly enhances performance,
especially in a client/server network.
- The PL/SQL engine in Oracle tools executes
only the procedural statements in the PL/SQL block at the application
site. It uses the Procedural Statement Executor in the local PL/SQL
engine for this purpose.
PL/SQL
block
- PL/SQL is a block-structured language. A set of
logically related statements forms a block.
- A basic block of PL/SQL code can contain three
sections: Declarative, Executable, and Exception Handling.
- These sections are used according to the
requirements of an application being developed.
- The Declarative section is the first part of a
PL/SQL block. It is used for declaring items, such as variables,
constants, cursors, and definitions of error situations called
exceptions. Declaring an item allocates storage space for the item.
- You use the DECLARE keyword to signal the
start of the Declarative section in a PL/SQL block. The Declarative
section is optional.
- The second section of a PL/SQL block is the
Executable section. It contains SQL and PL/SQL statements for querying
and for manipulating data.
- You use the BEGIN keyword to signal the start
of the Executable section in a PL/SQL block.
- The END keyword signifies the end of the
Executable section.
- There needs to be at least one executable
statement within a set of BEGIN and END statements. As part of the
syntax, a semicolon is placed after the END keyword.
- The final section in the PL/SQL block is the
Exception Handling section. It specifies the actions to be performed
when errors and abnormal conditions arise in the Executable section.
- In a PL/SQL block, conditions that lead to
errors are known as exceptions. You can take care of exceptions by
defining the handlers for them.
- Exceptions are placed in a separate section of
a PL/SQL block called the Exception Handling section. The Exception
Handling section is optional and is embedded in the Executable section
of a block and is placed at the end of the Executable section.
PL/SQL Program Constructs
- You can build an application to contain
various program constructs. Program constructs are PL/SQL blocks or
modules that execute different functions, depending on the execution
environment.
- A program construct can be a single PL/SQL
block, a nested block, or a group of blocks.
- These blocks can be either entirely separate
or nested one within another. By using the basic PL/SQL block, you can
create a variety of program constructs.
Anonymous Block
- An anonymous block is an unnamed PL/SQL block
embedded in an application or issued interactively.
- An anonymous block is declared at the point in
an application where it is to be executed. It is then passed to the
PL/SQL engine for execution at run time.
- You can embed an anonymous block in a
precompiler program and in SQL*Plus or Server Manager.
Stored
Procedure Or Function
- Another program construct is the stored
procedure or function. A stored procedure or function is a PL/SQL
program unit that has a name and can take parameters.
- A stored procedure or function is stored in
the data dictionary and can be called by many users.
- The term stored procedure is sometimes used
generically to cover both stored procedures and stored functions.
- The only difference between procedures and
functions is that functions always return a single value to the caller,
while procedures do not return a value to the caller.
Application Procedure
Or Function
- The application procedure or function is a
named PL/SQL block stored in an Oracle Developer application or shared
library.
- This construct can use input and output
parameters, and it can be invoked repeatedly.
- Procedures and functions declared as part of a
Developer application are distinct from those stored in the database,
although their general structure is the same.
- Stored subprograms are database objects and
are stored in the data dictionary. They can be accessed by any number of
applications, including Developer applications.
- Some of the application procedures or
functions are available in the components of Oracle Developer, such as
Forms.
Package
- A is an encapsulated collection of related
program objects, such as procedures, functions, variables, constants,
cursors, and exceptions, stored together in the database or in Oracle
Tools like Oracle Developer components.
Triggers
- Triggers are procedures that are stored in the
database and are implicitly run, or fired, when a DML event occurs in
the database.
- You can define triggers to fire before or
after an INSERT, UPDATE, or DELETE occurs on a table or view.
Application Trigger
- The application trigger is associated with an application
event and is executed automatically when a specified event occurs.
Syntax
Rules
- Before you use PL/SQL blocks to create an
application, it is essential to understand the general rules of syntax
that apply to them. This knowledge enables you to write error-free code
and to simplify the compilation and debugging of code.
- Because PL/SQL is an extension of SQL, the
general syntax rules that are applicable to SQL are also applicable to
the PL/SQL language.
- Identifiers are used to name PL/SQL program
items and units, which include constants, variables, exceptions,
cursors, cursor variables, subprograms, and packages.
- An identifier name can have a maximum of 30
characters. An identifier name begins with an alphabetical character.
- Reserved words cannot be used as identifier
names unless they are enclosed in double quotation marks.
- Reserved words are written in uppercase
letters to promote readability.
- The identifier name needs to be different from
the column names in a table used in the block.
- If an identifier name in a SQL statement is
the same as a column name, then the engine interprets it as a reference
to the column and not the identifier.
- You enclose the characters and date literals
in a PL/SQL statement in single quotation marks. Character literals
include all the printable characters in the PL/SQL character set:
letters, numerals, spaces, and special symbols.
- Numeric literals in a PL/SQL block are
represented by either a simple value such as -32.5 or a scientific
notation such as 2E5. The 2E5 means 2*(10 to the power of 5) = 200000.
- You use two hyphens (--) to indicate a single
line comment. Comments are strictly informational and do not enforce any
condition or behavioral logic, or data.
- Multiple line comments, if incorporated, are
enclosed within the symbols /* and */. You comment the code to document
each phase and to assist with debugging.
- The lexical units, such as identifiers and
literals, can be separated by one or more spaces. You cannot embed
spaces in lexical units except for string literals and comments.
- Statements can be split across lines, but
keywords cannot be split.
- A PL/SQL block is terminated by a slash on a
line by itself.
|
|
Practice
Test & Answers
1.
You develop a PL/SQL named program and compile it. Your PL/SQL program is now
stored in which of the following areas?
A. In an executable program B. In a flat
file C. In
the Oracle database D. In SQL*Plus
2. You are using PL/SQL to support code centralization in
your organization. Which of the following choices does not identify a feature
offered by PL/SQL's centralization?
A. More manageable upgrade
process
B. Program that
takes advantage of client PC power
C. Single copy
of program available for all users
D. Native
communication with the database
3. You want to use the feature in PL/SQL that allows
different versions of the same procedure to accept information of different
datatypes. Which of the following terms best
describes this feature?
A. Encapsulation
B. Packages
C. Modularization
D. Overloading
4. Use the following code block to answer
this question:
SQL> create or replace package my_pkg is
2 /*********/
3
function f_no_op
4 ( myvar in number)
5
return number;
6 /********/
7
procedure no_op
8 ( myvar in number) ;
9 end;
10 /
Package created.
Which of the following choices identifies the type of code
block shown here?
A. Package specification
B. Package body
C. Procedure
specification
D. Procedure body
5. You are creating a function in PL/SQL. Which two of the following components must appear in every function
in PL/SQL (without them, Oracle returns an error)?
A. Declared variables
B. A return
statement
C. A begin-end section
D. An exception section
6. You are developing PL/SQL programs. Which of the following
choices best describes a possible definition for
a trigger?
A. A code block containing
procedure and function specifications that has an associated body containing
the actual code
B. A code block containing
procedure and function bodies that has an associated specification containing
the procedure and function definitions
C. A code block associated
with a table that Oracle executes whenever a specified event occurs
D. An unnamed code block parsed and executed at the same
time
Answer
1.C. 2. B. 3-D.
4. A. 5. B and C. 6. C.
|
