Overview of PL/SQL Programs(PL/SQL) 1 :

Overview of PL/SQL Programs :

1) Describe a PL/SQL program construct
2) List the components of a PL/SQL block
3) List of benefits of subprograms
4) Describe how a stored procedure/function is invoked

==> Describe a PL/SQL program construct:


The basic unit of PL/SQL is block.



Block is divided into Declarative,
Executable and Exception Sections.

Executable section is required and all
other sections are optional.
You can nest blocks in the executable
and exception handling sections of a
PL/SQL block.

Declarative Section :

In declarative section, we define and
initialize the variables ,local
subprograms and cursors used in the block.

Declarations are local to blocks.

Executable Section :

In this section , we manipulate the
variables and cursors declared in the
Declarative section of PL/SQL block.

Executable section must also contain
at least one executable statement.

Exception Handling Section :

In this section, user can detect and
process the errors. This block is used to
respond runtime errors encountered by the
program.

When user defined or system related
exceptions are encountered the control
transfers to exception handling ( error
handling) section.

User defined exceptions are those which
are declared by the user.
Predefined exceptions are raised
automatically for common errors by PL/SQL.

Example of PL/SQL Block :





==> List the components of a PL/SQL block :

Block Structure ::







There are two kinds of blocks : Anonymous and Named Blocks.
Anonymous blocks are generally constructed dynamically and executed only once.

Eg :


Named Blocks are blocks that have a name associated with them. Named blocks can be divided into three types : Labeled

Label blocks are anonymous blocks with a label that gives the block a name. Label allows you to refer to variables that otherwise would not be visible.

Subprograms consist of procedures and functions. They can be stored in the database as stand alone objects , as part of a package , or as methods of an object type.

Triggers consist of a PL/SQL block that is associated with an event that occurs in the database. Triggers are executed implicitly whenever the triggering event occurs. The event can be DDL,DML or DCL.

==> List the benefits of subprograms :

Subprograms are named PL/SQL blocks that can be called with a set of parameters. Procedures and Functions collectively known as subprograms.


=>Subprograms let you break a program down into manageable, well defined modules.


=>Subprograms promote reusability. Once created, it can be reused in any number of applications.


=>Subprograms promote maintainability. You can change the internals of a subprogram without changing other subprograms that call it.


=>Subprograms can make your code even more reusable and maintained by grouping the subprograms into a PL/SQL package.

Many database applications are built using either a client/server or 3-tier model. In client/server model , the program sends requests to a database server for information. If this request done using SQL, it result in many network trips, one for each SQL Statement. Several SQL statements can be bundled together into one PL/SQL block and sent to the server as a single unit. This results in less network traffic and a faster application.


==> Describe how a stored procedure/function is invoked :

A procedure call is a PL/SQL statement itself.
Functions are called as part of an expressions.
VAR_NAME := Func_Name(PAR1,PAR2…);


To execute a procedure the user should have EXECUTE privilege on that object.

Grant Execute on proc_name to user_name;

If the user has execute privilege , he can execute the procedure even if the user does not have privileges on any of the tables that procedure uses.

The syntax used to execute a procedure depends on the environment from which the procedure is called. In SQLPLUS, a procedure can be called using the execute command, followed by procedure name. Any arguments to be passed to the procedure must be enclosed in parentheses following the procedure name.

Execute PROC_NAME(2,’BABU’);

The above command will execute proc_name procedure with
2 and ’BABU’ as parameters.
Within a subprogram the procedure can be called without the execute command.
To execute a procedure owned by another user, you must either create a synonym for that procedure or reference the owner’s name during the execution,

Execute owner_name.proc_name(2,’BABU’);

When executing remote procedures, the name of a database link must be specified.

Execute PROC_NAME@rem_db_link(2,’BABU’);

CALL Statement :

This can be used to call both PL/SQL and java subprograms. CALL is a SQL statement. It is not valid inside a PL/SQL block, but is valid when executed using dynamic SQL.

CALL SUB_PROG_NAME;

Calling Functions from SQL :


We can call function using an SQL Statement.
Select Func_totmarks(2) from students;




Sreekanth Babu V ( sreekanthbabu.vakiti@gmail.com)

References :

Oracle University books,

Oracle: The Complete References ,

Oracle 9i PL/SQL Programming