Bachelor of science in information technology notes

PL/SQL
INTRODUCTION
PL/SQL means PL/SQL (Procedural Language/Structured Query Language). In Oracle
database management, PL/SQL is a procedural language extension to Structured Query
Language (SQL). The purpose of PL/SQL is to combine database language and
procedural programming language. The basic unit in PL/SQL is called a block, which is
made up of three parts: a declarative part, an executable part, and an exception-
building part.
Because PL/SQL allows you to mix SQL statements with procedural constructs, it is
possible to use PL/SQL blocks and subprograms to group SQL statements before sending
them to Oracle for execution. Without PL/SQL, Oracle must process SQL statements one
at a time and, in a network environment, this can affect traffic flow and slow down
response time. PL/SQL blocks can be compiled once and stored in executable form to
improve response time.


 It’s a procedural programming language.
 It’s an extension to SQL.
PURPOSE OF PL/SQL
It is a technology used by the oracle server to manipulate the database.
BENEFITS OF PL/SQL
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

Tight Integration with SQL
The PL/SQL language is tightly integrated with SQL. You do not have to translate
between SQL and PL/SQL data types: a NUMBER or VARCHAR2 column in the
database is stored in a NUMBER or VARCHAR2 variable in PL/SQL. This integration
saves you both learning time and processing time. Special PL/SQL language features let
you work with table columns and rows without specifying the data types, saving on
maintenance work when the table definitions change. Running a SQL query and
processing the result set is as easy in PL/SQL as opening a text file and processing each
line in popular scripting languages.
Using PL/SQL to access metadata about database objects and handle database error
conditions, you can write utility programs for database administration that are reliable
and produce readable output about the success of each operation.
Many database features, such as triggers and object types, make use of PL/SQL. You can
write the bodies of triggers and methods for object types in PL/SQL.

,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.
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 pseudo columns.
This extensive SQL support lets you manipulate Oracle data flexibly and safely. Also,
PL/SQL fully supports SQL data types, reducing the need to convert data passed between
your applications and the database.
PL/SQL also supports dynamic SQL, a 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 at run time, without knowing
details such as table names and WHERE clauses in advance.

Better Performance
Without PL/SQL, Oracle must process SQL statements one at a time. Programs that issue
many SQL statements require multiple calls to the database, resulting in significant
network and performance overhead.
With PL/SQL, an entire block of statements can be sent to Oracle at one time. This can
drastically reduce network traffic between the database and an application. As
PL/SQL even has language features to further speed up SQL statements that are issued
inside a loop.
PL/SQL stored procedures are compiled once and stored in executable form, so
procedure calls are efficient. Because stored procedures execute in the database server, a
single call over the network can start a large job. This division of work reduces network
traffic and improves response times. Stored procedures are cached and shared among
users, which lowers memory requirements and invocation overhead.

Higher Productivity
PL/SQL extends tools such as Oracle Forms and Oracle Reports. With PL/SQL in these
tools, you can use familiar language constructs to build applications. For example, you
can use an entire PL/SQL block in an Oracle Forms trigger, instead of multiple trigger
steps, macros, or user exits.
PL/SQL is the same in all environments. Once you learn PL/SQL with one Oracle tool,
you can transfer your knowledge to other tools.

Full Portability
Applications written in PL/SQL can run on any operating system and platform where the
Oracle database runs. With PL/SQL, you can write portable program libraries and reuse
them in different environments.

Tight Security
PL/SQL stored procedures move application code from the client to the server, where you
can protect it from tampering, hide the internal details, and restrict who has access. For

,example, you can grant users access to a procedure that updates a table, but not grant
them access to the table itself or to the text of the UPDATE statement.
Triggers written in PL/SQL can control or record changes to data, making sure that all
changes obey your business rules.


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. This direct mapping helps your programs
better reflect the world they are trying to simulate.

PLSQL BLOCKS
The basic units that make up a PL/SQL program are logical blocks, which can be nested inside
one another.
A PL/SQL block has three parts: a declarative part, an executable part, and an exception-handling
part that deals with error conditions. Only the executable part is required.
First comes the declarative part, where you define types, variables, and similar items. These items
found in the declarative part are manipulated in the executable part. Exceptions raised during
execution can be dealt with in the exception-handling part.
They are of two types:
a) Anonymous blocks
b) Named blocks
ANONYMOUS BLOCKS
 Are blocks without names
 Not stored in the database
 Executes each time compiled
NAMED BLOCKS
 They have names
 Stored in the database
 Compiled only once

ANONYMOUS BLOCKS
These are plsql blocks that do not have names.
Syntax
Declare (options)
Begin (mandatory)
Sql
Plsql
Exception (optional)

, End (mandatory)

VARIABLE
It is a container that is used to hold and manipulate data.
TYPES OF VARIABLES
 PL/SQL variables.
 Non PL/SQL variables.
1. PL/SQL
They include:-
A. Scalar datatypes:-They hold single values and include
varchar2,char,number etc.
B. Composite datatypes. They include:-
 Records:- A record is a collection of related fields
treated as a one logic unit.
 Plsql collections.

C. Reference datatypes:-They create a pointer to the data being
accessed.
D. Large objects:-used to display character larger objects. Eg
image.
2. NON-PLSQL VARIABLES
 Host variables:-Are variables declared within the sql prompt
environment .Eg SQL > variable g_salary number.
 Bind variables:- references previously declared host variable using a
full colon(:) eg. :g_salary.
 Is used within a pl/sql code.
OTHERS(NON_VARIABLES)
a) %type attributes:-
 enables a variable to inherit a previously declared
variable in a database column .
 Used to automatically pick the datatype.
 Prefix the tablename with column_name.eg

First_name of datatype varchar2 but can just use:-
First_name employees.first.first_name%type;
b) %rowtype:-
 Enables a variable to inherit the whole
structure of a table. Eg
Myemp employees%rowtype.
OUTPUT MANAGEMENT
To produce output from a plsql block the following package is used
‘DBMS_OUTPUT.PUT_LINE’
Enabled by
‘SET SERVEROUTPUT ON’