Overview of PL/SQL
The limits of my language mean the limits of my world. --Ludwig Wittgenstein
This chapter surveys the main features of PL/SQL and
points out the advantages they offer. It also acquaints you with the
basic concepts behind PL/SQL and the general appearance of PL/SQL
programs. You see how PL/SQL bridges the gap between database technology
and procedural programming languages.
This chapter discusses the following topics:
- Understanding the Main Features of PL/SQL
- PL/SQL Architecture
- Advantages of PL/SQL
-
See Also: Access additional information and code samples for PL/SQL on the Oracle Technology Network, athttp://otn.oracle.com/tech/pl_sql/
.
Understanding the Main Features of PL/SQL
A good way to get acquainted with PL/SQL is to look at a
sample program. The program below processes an order for a tennis
racket. First, it declares a variable of type
NUMBER
to store the quantity of tennis rackets on hand. Then, it retrieves the quantity on hand from a database table named inventory
. If the quantity is greater than zero, the program updates the table and inserts a purchase record into another table named purchase_record
. Otherwise, the program inserts an out-of-stock record into the purchase_record
table.-- available online in file 'examp1' DECLARE qty_on_hand NUMBER(5); BEGIN SELECT quantity INTO qty_on_hand FROM inventory WHERE product = 'TENNIS RACKET' FOR UPDATE OF quantity; IF qty_on_hand > 0 THEN -- check quantity UPDATE inventory SET quantity = quantity - 1 WHERE product = 'TENNIS RACKET'; INSERT INTO purchase_record VALUES ('Tennis racket purchased', SYSDATE); ELSE INSERT INTO purchase_record VALUES ('Out of tennis rackets', SYSDATE); END IF; COMMIT; END;
With PL/SQL, you can use SQL statements to manipulate
Oracle data and flow-of-control statements to process the data. You can
also declare constants and variables, define 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.
Block Structure
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 subproblem to be solved. Thus, PL/SQL
supports the divide-and-conquer approach to problem solving called stepwise refinement.
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.
As Figure 1-1
shows, 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.
The order of the parts is logical. First comes the
declarative part, in which items can be declared. Once declared, items
can be manipulated in the executable part. Exceptions raised during
execution can be dealt with in the exception-handling part.
Figure 1-1 Block Structure
Text description of the illustration pls81001_block_structure.gif
You can nest sub-blocks in the executable and
exception-handling parts of a PL/SQL block or subprogram but not in the
declarative part. Also, you can define local subprograms in the
declarative part of any block. However, you can call local subprograms
only from the block in which they are defined.
Variables and Constants
PL/SQL lets you declare constants and variables, then use
them in SQL and procedural statements anywhere an expression can be
used. However, forward references are not allowed. So, you must declare a
constant or variable before referencing it in other statements, including other declarative statements.
Declaring Variables
Variables can have any SQL datatype, such as
CHAR
, DATE
, or NUMBER
, or any PL/SQL datatype, such as BOOLEAN
or BINARY_INTEGER
. For example, assume that you want to declare a variable named part_no
to hold 4-digit numbers and a variable named in_stock
to hold the Boolean value TRUE
or FALSE
. You declare these variables as follows:part_no NUMBER(4); in_stock BOOLEAN;
You can also declare nested tables, variable-size arrays (varrays for short), and records using the
TABLE
, VARRAY
, and RECORD
composite datatypes.Assigning Values to a Variable
You can assign values to a variable in three ways. The first way uses the assignment operator (
:=
),
a colon followed by an equal sign. You place the variable to the left
of the operator and an expression (which can include function calls) to
the right. A few examples follow:tax := price * tax_rate; valid_id := FALSE; bonus := current_salary * 0.10; wages := gross_pay(emp_id, st_hrs, ot_hrs) - deductions;
The second way to assign values to a variable is by
selecting (or fetching) database values into it. In the example below,
you have Oracle compute a 10% bonus when you select the salary of an
employee. Now, you can use the variable
bonus
in another computation or insert its value into a database table.SELECT sal * 0.10 INTO bonus FROM emp WHERE empno = emp_id;
The third way to assign values to a variable is by passing it as an
OUT
or IN
OUT
parameter to a subprogram. As the following example shows, an IN
OUT
parameter lets you pass initial values to the subprogram being called and return updated values to the caller:DECLARE my_sal REAL(7,2); PROCEDURE adjust_salary (emp_id INT, salary IN OUT REAL) IS ... BEGIN SELECT AVG(sal) INTO my_sal FROM emp; adjust_salary(7788, my_sal); -- assigns a new value to my_sal
Declaring Constants
Declaring a constant is like declaring a variable except that you must add the keyword
CONSTANT
and immediately assign a value to the constant. Thereafter, no more
assignments to the constant are allowed. In the following example, you
declare a constant named credit_limit
:credit_limit CONSTANT REAL := 5000.00;
Cursors
Oracle uses work areas to execute SQL statements and store processing information. A PL/SQL construct called a cursor lets you name a work area and access its stored information. There are two kinds of cursors: implicit and explicit.
PL/SQL implicitly declares a cursor for all SQL data manipulation
statements, including queries that return only one row. For queries that
return more than one row, you can explicitly declare a cursor to
process the rows individually. An example follows:
DECLARE CURSOR c1 IS SELECT empno, ename, job FROM emp WHERE deptno = 20;
The set of rows returned by a multi-row query is called the result set. Its size is the number of rows that meet your search criteria. As Figure 1-2 shows, an explicit cursor "points" to the current row in the result set. This allows your program to process the rows one at a time.
Figure 1-2 Query Processing
Text description of the illustration pls81003_query_processing.gif
Multi-row query processing is somewhat like file
processing. For example, a COBOL program opens a file, processes
records, then closes the file. Likewise, a PL/SQL program opens a
cursor, processes rows returned by a query, then closes the cursor. Just
as a file pointer marks the current position in an open file, a cursor
marks the current position in a result set.
You use the
OPEN
, FETCH
, and CLOSE
statements to control a cursor. The OPEN
statement executes the query associated with the cursor, identifies the
result set, and positions the cursor before the first row. The FETCH
statement retrieves the current row and advances the cursor to the next row. When the last row has been processed, the CLOSE
statement disables the cursor.Cursor FOR Loops
In most situations that require an explicit cursor, you can simplify coding by using a cursor
FOR
loop instead of the OPEN
, FETCH
, and CLOSE
statements. A cursor FOR
loop implicitly declares its loop index as a record that represents a
row fetched from the database. Next, it opens a cursor, repeatedly
fetches rows of values from the result set into fields in the record,
then closes the cursor when all rows have been processed. In the
following example, the cursor FOR
loop implicitly declares emp_rec
as a record:DECLARE CURSOR c1 IS SELECT ename, sal, hiredate, deptno FROM emp; ... BEGIN FOR emp_rec IN c1 LOOP ... salary_total := salary_total + emp_rec.sal; END LOOP;
To reference individual fields in the record, you use dot notation, in which a dot (
.
) serves as the component selector.Cursor Variables
Like a cursor, a cursor variable points to the current row
in the result set of a multi-row query. But, unlike a cursor, a cursor
variable can be opened for any type-compatible query. It is not tied to a
specific query. Cursor variables are true PL/SQL variables, to which
you can assign new values and which you can pass to subprograms stored
in an Oracle database. This gives you more flexibility and a convenient
way to centralize data retrieval.
Typically, you open a cursor variable by passing it to a
stored procedure that declares a cursor variable as one of its formal
parameters. The following procedure opens the cursor variable
generic_cv
for the chosen query:PROCEDURE open_cv (generic_cv IN OUT GenericCurTyp,choice NUMBER) IS BEGIN IF choice = 1 THEN OPEN generic_cv FOR SELECT * FROM emp; ELSIF choice = 2 THEN OPEN generic_cv FOR SELECT * FROM dept; ELSIF choice = 3 THEN OPEN generic_cv FOR SELECT * FROM salgrade; END IF; ... END;
Attributes
PL/SQL variables and cursors have attributes,
which are properties that let you reference the datatype and structure
of an item without repeating its definition. Database columns and tables
have similar attributes, which you can use to ease maintenance. A
percent sign (
%
) serves as the attribute indicator.%TYPE
The
%TYPE
attribute provides the datatype of a
variable or database column. This is particularly useful when declaring
variables that will hold database values. For example, assume there is a
column named title
in a table named books
. To declare a variable named my_title
that has the same datatype as column title
, use dot notation and the %TYPE
attribute, as follows:my_title books.title%TYPE;
Declaring
my_title
with %TYPE
has two advantages. First, you need not know the exact datatype of title
. Second, if you change the database definition of title
(make it a longer character string for example), the datatype of my_title
changes accordingly at run time.%ROWTYPE
In PL/SQL, records are used to group data. A record
consists of a number of related fields in which data values can be
stored. The
%ROWTYPE
attribute provides a record type that
represents a row in a table. The record can store an entire row of data
selected from the table or fetched from a cursor or cursor variable.
Columns in a row and corresponding fields in a record have
the same names and datatypes. In the example below, you declare a
record named
dept_rec
. Its fields have the same names and datatypes as the columns in the dept
table.DECLARE dept_rec dept%ROWTYPE; -- declare record variable
You use dot notation to reference fields, as the following example shows:
my_deptno := dept_rec.deptno;
If you declare a cursor that retrieves the last name, salary, hire date, and job title of an employee, you can use
%ROWTYPE
to declare a record that stores the same information, as follows:DECLARE CURSOR c1 IS SELECT ename, sal, hiredate, job FROM emp; emp_rec c1%ROWTYPE; -- declare record variable that represents -- a row fetched from the emp table
When you execute the statement
FETCH c1 INTO emp_rec;
the value in the
ename
column of the emp
table is assigned to the ename
field of emp_rec
, the value in the sal
column is assigned to the sal
field, and so on. Figure 1-3 shows how the result might appear.Figure 1-3 %ROWTYPE Record
Text description of the illustration pls81002_rowtype_record.gif
Control Structures
Control structures are the most important PL/SQL extension
to SQL. Not only does PL/SQL let you manipulate Oracle data, it lets
you process the data using conditional, iterative, and sequential
flow-of-control statements such as
IF-THEN-ELSE
, CASE
, FOR-LOOP
, WHILE-LOOP
, EXIT-WHEN
, and GOTO
. Collectively, these statements can handle any situation.Conditional Control
Often, it is necessary to take alternative actions depending on circumstances. The
IF-THEN-ELSE
statement lets you execute a sequence of statements conditionally. The IF
clause checks a condition; the THEN
clause defines what to do if the condition is true; the ELSE
clause defines what to do if the condition is false or null.
Consider the program below, which processes a bank
transaction. Before allowing you to withdraw $500 from account 3, it
makes sure the account has sufficient funds to cover the withdrawal. If
the funds are available, the program debits the account. Otherwise, the
program inserts a record into an audit table.
-- available online in file 'examp2' DECLARE acct_balance NUMBER(11,2); acct CONSTANT NUMBER(4) := 3; debit_amt CONSTANT NUMBER(5,2) := 500.00; BEGIN SELECT bal INTO acct_balance FROM accounts WHERE account_id = acct FOR UPDATE OF bal; IF acct_balance >= debit_amt THEN UPDATE accounts SET bal = bal - debit_amt WHERE account_id = acct; ELSE INSERT INTO temp VALUES (acct, acct_balance, 'Insufficient funds'); -- insert account, current balance, and message END IF; COMMIT; END;
To choose among several values or courses of action, you can use
CASE
constructs. The CASE expression evaluates a condition and returns a
value for each case. The case statement evaluates a condition and
performs an action (which might be an entire PL/SQL block) for each
case.-- This CASE statement performs different actions based -- on a set of conditional tests. CASE WHEN shape = 'square' THEN area := side * side; WHEN shape = 'circle' THEN BEGIN area := pi * (radius * radius); DBMS_OUTPUT.PUT_LINE('Value is not exact because pi is irrational.'); END; WHEN shape = 'rectangle' THEN area := length * width; ELSE BEGIN DBMS_OUTPUT.PUT_LINE('No formula to calculate area of a' || shape); RAISE PROGRAM_ERROR; END; END CASE;
A sequence of statements that uses query results to select
alternative actions is common in database applications. Another common
sequence inserts or deletes a row only if an associated entry is found
in another table. You can bundle these common sequences into a PL/SQL
block using conditional logic.
Iterative Control
LOOP
statements let you execute a sequence of statements multiple times. You place the keyword LOOP
before the first statement in the sequence and the keywords END
LOOP
after the last statement in the sequence. The following example shows
the simplest kind of loop, which repeats a sequence of statements
continually:LOOP -- sequence of statements END LOOP;
The
FOR-LOOP
statement lets you specify a
range of integers, then execute a sequence of statements once for each
integer in the range. For example, the following loop inserts 500
numbers and their square roots into a database table:FOR num IN 1..500 LOOP INSERT INTO roots VALUES (num, SQRT(num)); END LOOP;
The
WHILE-LOOP
statement associates a
condition with a sequence of statements. Before each iteration of the
loop, the condition is evaluated. If the condition is true, the sequence
of statements is executed, then control resumes at the top of the loop.
If the condition is false or null, the loop is bypassed and control
passes to the next statement.
In the following example, you find the first employee who
has a salary over $2500 and is higher in the chain of command than
employee 7499:
-- available online in file 'examp3' DECLARE salary emp.sal%TYPE := 0; mgr_num emp.mgr%TYPE; last_name emp.ename%TYPE; starting_empno emp.empno%TYPE := 7499; BEGIN SELECT mgr INTO mgr_num FROM emp WHERE empno = starting_empno; WHILE salary <= 2500 LOOP SELECT sal, mgr, ename INTO salary, mgr_num, last_name FROM emp WHERE empno = mgr_num; END LOOP; INSERT INTO temp VALUES (NULL, salary, last_name); COMMIT; EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO temp VALUES (NULL, NULL, 'Not found'); COMMIT; END;
The
EXIT-WHEN
statement lets you complete a loop if further processing is impossible or undesirable. When the EXIT
statement is encountered, the condition in the WHEN
clause is evaluated. If the condition is true, the loop completes and
control passes to the next statement. In the following example, the loop
completes when the value of total
exceeds 25,000:LOOP ... total := total + salary; EXIT WHEN total > 25000; -- exit loop if condition is true END LOOP; -- control resumes here
Sequential Control
The
GOTO
statement lets you branch to a label
unconditionally. The label, an undeclared identifier enclosed by double
angle brackets, must precede an executable statement or a PL/SQL block.
When executed, the GOTO
statement transfers control to the labeled statement or block, as the following example shows:IF rating > 90 THEN GOTO calc_raise; -- branch to label END IF; ... <<calc_raise>> IF job_title = 'SALESMAN' THEN -- control resumes here amount := commission * 0.25; ELSE amount := salary * 0.10; END IF;
Modularity
Modularity lets you break an application down into
manageable, well-defined modules. Through successive refinement, you can
reduce a complex problem to a set of simple problems that have
easy-to-implement solutions. PL/SQL meets this need with program units, which include blocks, subprograms, and packages.
Subprograms
PL/SQL has two types of subprograms called procedures and functions,
which can take parameters and be invoked (called). As the following
example shows, a subprogram is like a miniature program, beginning with a
header followed by an optional declarative part, an executable part,
and an optional exception-handling part:
PROCEDURE award_bonus (emp_id NUMBER) IS bonus REAL; comm_missing EXCEPTION; BEGIN -- executable part starts here SELECT comm * 0.15 INTO bonus FROM emp WHERE empno = emp_id; IF bonus IS NULL THEN RAISE comm_missing; ELSE UPDATE payroll SET pay = pay + bonus WHERE empno = emp_id; END IF; EXCEPTION -- exception-handling part starts here WHEN comm_missing THEN ... END award_bonus;
When called, this procedure accepts an employee number. It
uses the number to select the employee's commission from a database
table and, at the same time, compute a 15% bonus. Then, it checks the
bonus amount. If the bonus is null, an exception is raised; otherwise,
the employee's payroll record is updated.
Packages
PL/SQL lets you bundle logically related types, variables, cursors, and subprograms into a package.
Each package is easy to understand and the interfaces between packages
are simple, clear, and well defined. This aids application development.
Packages usually have two parts: a specification and a body. The specification
is the interface to your applications; it declares the types,
constants, variables, exceptions, cursors, and subprograms available for
use. The body defines cursors and subprograms and so implements the specification.
In the following example, you package two employment procedures:
CREATE PACKAGE emp_actions AS -- package specification PROCEDURE hire_employee (empno NUMBER, ename CHAR, ...); PROCEDURE fire_employee (emp_id NUMBER); END emp_actions; CREATE PACKAGE BODY emp_actions AS -- package body PROCEDURE hire_employee (empno NUMBER, ename CHAR, ...) IS BEGIN INSERT INTO emp VALUES (empno, ename, ...); END hire_employee; PROCEDURE fire_employee (emp_id NUMBER) IS BEGIN DELETE FROM emp WHERE empno = emp_id; END fire_employee; END emp_actions;
Only the declarations in the package specification are
visible and accessible to applications. Implementation details in the
package body are hidden and inaccessible.
Packages can be compiled and stored in an Oracle database,
where their contents can be shared by many applications. When you call a
packaged subprogram for the first time, the whole package is loaded
into memory. So, subsequent calls to related subprograms in the package
require no disk I/O. Thus, packages can enhance productivity and improve
performance.
Data Abstraction
Data abstraction lets you extract the essential properties
of data while ignoring unnecessary details. Once you design a data
structure, you can forget the details and focus on designing algorithms
that manipulate the data structure.
Collections
The collection types
TABLE
and VARRAY
allow you to declare index-by tables, nested tables, and variable-size
arrays (varrays for short). A collection is an ordered group of
elements, all of the same type. Each element has a unique subscript that
determines its position in the collection.
To reference an element, use standard subscripting syntax.
For example, the following call references the fifth element in the
nested table (of type
Staff
) returned by function new_hires
:DECLARE TYPE Staff IS TABLE OF Employee; staffer Employee; FUNCTION new_hires (hiredate DATE) RETURN Staff IS BEGIN ... END; BEGIN staffer := new_hires('10-NOV-98')(5); ... END;
Collections work like the arrays found in most
third-generation programming languages. Also, collections can be passed
as parameters. So, you can use them to move columns of data into and out
of database tables or between client-side applications and stored
subprograms.
Records
You can use the
%ROWTYPE
attribute to declare
a record that represents a row in a table or a row fetched from a
cursor. But, with a user-defined record, you can declare fields of your
own.
Records contain uniquely named fields, which can have
different datatypes. Suppose you have various data about an employee
such as name, salary, and hire date. These items are dissimilar in type
but logically related. A record containing a field for each item lets
you treat the data as a logical unit.
Consider the following example:
DECLARE TYPE TimeRec IS RECORD (hours SMALLINT, minutes SMALLINT); TYPE MeetingTyp IS RECORD ( date_held DATE, duration TimeRec, -- nested record location VARCHAR2(20), purpose VARCHAR2(50));
Notice that you can nest records. That is, a record can be a component of another record.
Object Types
In PL/SQL, object-oriented programming is based on object types. An object type
encapsulates a data structure along with the functions and procedures
needed to manipulate the data. The variables that form the data
structure are called attributes. The functions and procedures that characterize the behavior of the object type are called methods.
Object types reduce complexity by breaking down a large
system into logical entities. This lets you create software components
that are modular, maintainable, and reusable.
When you define an object type using the
CREATE
TYPE
statement (in SQL*Plus for example), you create an abstract template
for some real-world object. As the following example of a bank account
shows, the template specifies only those attributes and behaviors the
object will need in the application environment:CREATE TYPE Bank_Account AS OBJECT ( acct_number INTEGER(5), balance REAL, status VARCHAR2(10), MEMBER PROCEDURE open (amount IN REAL), MEMBER PROCEDURE verify_acct (num IN INTEGER), MEMBER PROCEDURE close (num IN INTEGER, amount OUT REAL), MEMBER PROCEDURE deposit (num IN INTEGER, amount IN REAL), MEMBER PROCEDURE withdraw (num IN INTEGER, amount IN REAL), MEMBER FUNCTION curr_bal (num IN INTEGER) RETURN REAL );
At run time, when the data structure is filled with
values, you have created an instance of an abstract bank account. You
can create as many instances (called objects) as you need. Each object has the number, balance, and status of an actual bank account.
Information Hiding
With information hiding, you see only the details that are
relevant at a given level of algorithm and data structure design.
Information hiding keeps high-level design decisions separate from
low-level design details, which are more likely to change.
Algorithms
You implement information hiding for algorithms through top-down design.
Once you define the purpose and interface specifications of a low-level
procedure, you can ignore the implementation details. They are hidden
at higher levels. For example, the implementation of a procedure named
raise_salary
is hidden. All you need to know is that the procedure will increase a
specific employee's salary by a given amount. Any changes to the
definition of raise_salary
are transparent to calling applications.Data Structures
You implement information hiding for data structures though data encapsulation.
By developing a set of utility subprograms for a data structure, you
insulate it from users and other developers. That way, other developers
know how to use the subprograms that operate on the data structure but
not how the structure is represented.
With PL/SQL packages, you can specify whether subprograms
are public or private. Thus, packages enforce data encapsulation by
letting you put subprogram definitions in a black box. A private
definition is hidden and inaccessible. Only the package, not your
application, is affected if the definition changes. This simplifies
maintenance and enhancement.
Error Handling
PL/SQL makes it easy to detect and process predefined and user-defined error conditions called exceptions. When an error occurs, an exception is raised.
That is, normal execution stops and control transfers to the
exception-handling part of your PL/SQL block or subprogram. To handle
raised exceptions, you write separate routines called exception handlers.
Predefined exceptions are raised implicitly by the runtime
system. For example, if you try to divide a number by zero, PL/SQL
raises the predefined exception
ZERO_DIVIDE
automatically. You must raise user-defined exceptions explicitly with the RAISE
statement.
You can define exceptions of your own in the declarative
part of any PL/SQL block or subprogram. In the executable part, you
check for the condition that needs special attention. If you find that
the condition exists, you execute a
RAISE
statement. In the
example below, you compute the bonus earned by a salesperson. The bonus
is based on salary and commission. So, if the commission is null, you
raise the exception comm_missing
.DECLARE ... comm_missing EXCEPTION; -- declare exception BEGIN ... IF commission IS NULL THEN RAISE comm_missing; -- raise exception END IF; bonus := (salary * 0.10) + (commission * 0.15); EXCEPTION WHEN comm_missing THEN ... -- process the exception
PL/SQL Architecture
The PL/SQL compilation and run-time system is a
technology, not an independent product. Think of this technology as 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 or Oracle Reports. So, PL/SQL can
reside in two environments:
These two environments are independent. PL/SQL is bundled
with the Oracle server but might be unavailable in some tools. In either
environment, the PL/SQL engine accepts as input any valid PL/SQL block
or subprogram. Figure 1-4
shows the PL/SQL engine processing an anonymous block. The engine
executes procedural statements but sends SQL statements to the SQL
Statement Executor in the Oracle server.
Figure 1-4 PL/SQL Engine
Text description of the illustration pls81004_plsql_engine.gif
In the Oracle Database Server
Application development tools that lack a local PL/SQL
engine must rely on Oracle to process PL/SQL blocks and subprograms.
When it contains the PL/SQL engine, an Oracle server can process PL/SQL
blocks and subprograms as well as single SQL statements. The Oracle
server passes the blocks and subprograms to its local PL/SQL engine.
Anonymous Blocks
Anonymous PL/SQL blocks can be embedded in an Oracle
Precompiler or OCI program. At run time, the program, lacking a local
PL/SQL engine, sends these blocks to the Oracle server, where they are
compiled and executed. Likewise, interactive tools such as SQL*Plus and
Enterprise Manager, lacking a local PL/SQL engine, must send anonymous
blocks to Oracle.
Stored Subprograms
Subprograms can be compiled separately and stored
permanently in an Oracle database, ready to be executed. A subprogram
explicitly
CREATE
d using an Oracle tool is called a stored
subprogram. Once compiled and stored in the data dictionary, it is a
schema object, which can be referenced by any number of applications
connected to that database.
Stored subprograms defined within a package are called packaged subprograms. Those defined independently are called standalone subprograms. Those defined within another subprogram or within a PL/SQL block are called local subprograms, which cannot be referenced by other applications and exist only for the convenience of the enclosing block.
Stored subprograms offer higher productivity, better
performance, memory savings, application integrity, and tighter
security. For example, by designing applications around a library of
stored procedures and functions, you can avoid redundant coding and
increase your productivity.
You can call stored subprograms from a database trigger,
another stored subprogram, an Oracle Precompiler application, an OCI
application, or interactively from SQL*Plus or Enterprise Manager. For
example, you might call the standalone procedure
create_dept
from SQL*Plus as follows:SQL> CALL create_dept('FINANCE', 'NEW YORK');
Subprograms are stored in parsed, compiled form. So, when
called, they are loaded and passed to the PL/SQL engine immediately.
Also, they take advantage of shared memory. So, only one copy of a
subprogram need be loaded into memory for execution by multiple users.
Database Triggers
A database trigger is a stored subprogram associated with a
database table, view, or event. For instance, you can have Oracle fire a
trigger automatically before or after an
INSERT
, UPDATE
, or DELETE
statement affects a table. One of the many uses for database triggers
is to audit data modifications. For example, the following table-level trigger fires whenever salaries in the emp
table are updated:CREATE TRIGGER audit_sal AFTER UPDATE OF sal ON emp FOR EACH ROW BEGIN INSERT INTO emp_audit VALUES ... END;
The executable part of a trigger can contain procedural
statements as well as SQL data manipulation statements. Besides
table-level triggers, there are instead-of triggers for views and system-event triggers for schemas. For more information, see Oracle9i Application Developer's Guide - Fundamentals.
In Oracle Tools
When it contains the PL/SQL engine, an application
development tool can process PL/SQL blocks and subprograms. The tool
passes the blocks to its local PL/SQL engine. The engine executes all
procedural statements at the application site and sends only SQL
statements to Oracle. Thus, most of the work is done at the application
site, not at the server site.
Furthermore, if the block contains no SQL statements, the
engine executes the entire block at the application site. This is useful
if your application can benefit from conditional and iterative control.
Frequently, Oracle Forms applications use SQL statements
merely to test the value of field entries or to do simple computations.
By using PL/SQL instead, you can avoid calls to the Oracle server.
Moreover, you can use PL/SQL functions to manipulate field entries.
Advantages of PL/SQL
PL/SQL is a completely portable, high-performance transaction processing language that offers the following advantages:
- Support for SQL
- Support for object-oriented programming
- Better performance
- Higher productivity
- Full portability
- Tight integration with Oracle
- Tight security
Support for SQL
SQL has become the standard database language because it
is flexible, powerful, and easy to learn. A few English-like commands
such as
SELECT
, INSERT
, UPDATE
, and DELETE
make it easy to manipulate the data stored in a relational database.
SQL is non-procedural, meaning that you can state what you
want done without stating how to do it. Oracle determines the best way
to carry out your request. There is no necessary connection between
consecutive statements because Oracle executes SQL statements one at a
time.
PL/SQL lets you use all the SQL data manipulation, cursor
control, and transaction control commands, as well as all the SQL
functions, operators, and pseudocolumns. So, you can manipulate Oracle
data flexibly and safely. Also, PL/SQL fully supports SQL datatypes.
That reduces the need to convert data passed between your applications
and the database.
PL/SQL also supports dynamic SQL, an advanced programming
technique that makes your applications more flexible and versatile. Your
programs can build and process SQL data definition, data control, and
session control statements "on the fly" at run time.
Support for Object-Oriented Programming
Object types are an ideal object-oriented modeling tool,
which you can use to reduce the cost and time required to build complex
applications. Besides allowing you to create software components that
are modular, maintainable, and reusable, object types allow different
teams of programmers to develop software components concurrently.
By encapsulating operations with data, object types let
you move data-maintenance code out of SQL scripts and PL/SQL blocks into
methods. Also, object types hide implementation details, so that you
can change the details without affecting client programs.
In addition, object types allow for realistic data
modeling. Complex real-world entities and relationships map directly
into object types. That helps your programs better reflect the world
they are trying to simulate.
Better Performance
Without PL/SQL, Oracle must process SQL statements one at a
time. Each SQL statement results in another call to Oracle and higher
performance overhead. In a networked environment, the overhead can
become significant. Every time a SQL statement is issued, it must be
sent over the network, creating more traffic.
However, with PL/SQL, an entire block of statements can be
sent to Oracle at one time. This can drastically reduce communication
between your application and Oracle. As Figure 1-5
shows, if your application is database intensive, you can use PL/SQL
blocks and subprograms to group SQL statements before sending them to
Oracle for execution.
PL/SQL stored procedures are compiled once and stored in
executable form, so procedure calls are quick and efficient. Also,
stored procedures, which execute in the server, can be invoked over slow
network connections with a single call. That reduces network traffic
and improves round-trip response times. Executable code is automatically
cached and shared among users. That lowers memory requirements and
invocation overhead.
Figure 1-5 PL/SQL Boosts Performance
Text description of the illustration pls81005_plsql_boosts_performance.gif
PL/SQL also improves performance by adding procedural
processing power to Oracle tools. 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.
Higher Productivity
PL/SQL adds functionality to non-procedural tools such as
Oracle Forms and Oracle Reports. With PL/SQL in these tools, you can use
familiar procedural constructs to build applications. For example, you
can use an entire PL/SQL block in an Oracle Forms trigger. You need not
use multiple trigger steps, macros, or user exits. Thus, PL/SQL
increases productivity by putting better tools in your hands.
Also, PL/SQL is the same in all environments. As soon as
you master PL/SQL with one Oracle tool, you can transfer your knowledge
to other tools, and so multiply the productivity gains. For example,
scripts written with one tool can be used by other tools.
Full Portability
Applications written in PL/SQL are portable to any
operating system and platform on which Oracle runs. In other words,
PL/SQL programs can run anywhere Oracle can run; you need not tailor
them to each new environment. That means you can write portable program
libraries, which can be reused in different environments.
Tight Integration with SQL
The PL/SQL and SQL languages are tightly integrated. PL/SQL supports all the SQL datatypes and the non-value
NULL
. That allows you manipulate Oracle data easily and efficiently. It also helps you to write high-performance code.
The
%TYPE
and %ROWTYPE
attributes further integrate PL/SQL with SQL. For example, you can use the %TYPE
attribute to declare variables, basing the declarations on the
definitions of database columns. If a definition changes, the variable
declaration changes accordingly the next time you compile or run your
program. The new definition takes effect without any effort on your
part. This provides data independence, reduces maintenance costs, and
allows programs to adapt as the database changes to meet new business
needs.Tight Security
PL/SQL stored procedures enable you to partition
application logic between the client and server. That way, you can
prevent client applications from manipulating sensitive Oracle data.
Database triggers written in PL/SQL can disable application updates
selectively and do content-based auditing of user inserts.
Furthermore, you can restrict access to Oracle data by
allowing users to manipulate it only through stored procedures that
execute with their definer's privileges. For example, you can grant
users access to a procedure that updates a table, but not grant them
access to the table itself.
No comments:
Post a Comment