PL / SQL
Pl/SQL
state Loops in PL/SQL
Built in Function
Cursor Management
Exception
INTRODUCTION
PL/SQL stands for Procedural Language/SQL. PL/SQL
extends SQL by adding constructs found in procedural languages,
resulting in a structural language that is more powerful than
SQL.PL/SQL is not case sensitive. ‘C’ style comments (/* ……… */)
may be used in PL/SQL programs whenever required.
All PL/SQL programs are made up of blocks, each block performs
a logical action in the program. A PL/SQL block consists of three parts
1. Declaration section
2. Executable section
3. Exception handling section
Only the executable section is required. The other sections are
optional.
A PL/SQL block has the following structure :
DECLARE
/* Declaration section */
BEGIN
/* Executable section */
EXCEPTION
/* Exception handling section */
END;
1. Declaration section :
This is first section which is start with word Declare. All the
identifiers (constants and variables) are declared in this section before
they are used in SELECT command.
2. Executable section :
This section contain procedural and SQL statements. This is the
only section of the block which is required. This section starts with
‘Begin’ word.
• The only SQL statements allowed in a PL/SQL program are
SELECT, INSERT, UPDATE, DELETE and several other
data manipulation statements.
• Data definition statements like CREATE, DROP or ALTER
are not allowed.
• The executable section also contains constructs such as
assignments, branches, loops, procedure calls and trigger .
3. Exception handling section :
This section is used to handle errors that occurs during execution
of PL/SQL program. This section starts with ‘exception’ word .
The ‘End’ indicate end of PL/SQL block.
Oracle PL/SQL programs, can be invoke either by typing it in
sqlplus or by putting the code in a file and invoking the file. To execute
it use ‘/’ on SQL prompt or use ‘.’ and run.
ARCHITECUTRE OF PL/SQL
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 :
1. The Oracle server
2. Oracle tools.
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. Fig. 4.1 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.
FUNDAMENTALS OF PL/SQL
PL/SQL DATA TYPES
PL/SQL and Oracle have their foundations in SQL. Most PL/SQL
data types are native to Oracle’s data dictionary, there is a very easy
integration of PL/SQL code with the Oracle Engine.
The default data types that we can declare in PL/SQL are number
(for storing numeric data), char (for storing character data), date (for
storing date and time data) boolean (for storing TRUE, FALSE or
NULL). number, char and date data types can have NULL values.
Here, we explain two data types,
1. Variable, 2. Constant.
1. Variables and types of declaration in PL/SQL :
The SELECT statement has a special form in PL/SQL in which a
single tuple is placed in variables. The information from the database
is transferred into variables which is used in PL/SQL programs. Every
variable has a specific type associated with it.
That type can be :
1. A generic type used in PL/SQL
2. A type same as used by SQL for database columns.
The most commonly used generic type is NUMBER. Variables of
type NUMBER can hold either an integer or a real number.
PL SQL Expressions
Expressions are a composite of operators and operands . In the case
of a mathematical expression ,the operand is the number and operator
is the symbol such as + or – that acts on the operand. The expression
value is the evaluated total of the operands using the operators.
Operators are divided into categories that describe the way that act
upon operands.
-Comparison operators are binary, meaning they work with two
operands. Examples of comparison operators are the greater than (>)
,less than(<) and equal(=) signs ,among others.
-Logical operators include AND,OR and NOT
-Arithmetic operators include
addition/positive(+),subtraction/negative(-),multiplication(*),and
division(/).
-The assignment operator is specific to PL/SQL and is written as
colon-equal (:=)
-The lone character operator is a double pipe(||) that joins two strings
together, concatenating the operands.
-Other basic SQL operators include IS NULL, IN and BETWEEN.
CURSOR MANAGEMENT IN PL/SQL
Whenever, a SQL statement is issued the Database server opens
an area of memory is called Private SQL area in which the command
is processed and executed. An identifier for this area is called a
cursor.
When PL/SQL block uses a select command that returns more
than one row, Oracle displays an error message and invokes the
TOO_MANY_ROWS exception. To resolve this problem, Oracle uses
a mechanism called cursor.
There are two types of cursors.
1. Implicit cursors
2. Explicit cursors
PL/SQL provides some attributes which allows to evaluate what
happened when the cursor was last used. You can use these
attributes in PL/SQL statements like functions but you cannot use
them within SQL statements.
The SQL cursor attributes are :
1. %ROWCOUNT : The number of rows processed by a SQL
statement.
2. %FOUND : TRUE if at least one row was processed.
3. %NOTFOUND : TRUE if no rows were processed.
4. %ISOPEN : TRUE if cursor is open or FALSE if cursor has not
been opened or has been closed. Only used with explicit cursors.
EXCEPTION (ERROR) HANDLING
The Exception section in PL/SQL block is used to handle an error
that occurs during the execution of PL/SQL program. If an error occurs
within a block PL/SQL passes control to the EXCEPTION section of
the block. If no EXCEPTION section exists within the block or the
EXCEPTION section does not handle the error that's occurred then
the error is passed out to the host environment.
Exceptions occur when either an Oracle error occurs (this
automatically raises an exception) or you explicitly raise an error or a
routine that executes corrective action when detecting an error. Thus
Exceptions are identifiers in PL/SQL that are raised during the
execution of a block to terminate its action.
There are two classes of exceptions, these are :
1. Predefined exception :
Oracle predefined errors which are associated with specific error
codes.
2. User-defined exception :
Declared by the user and raised when specifically requested
within a block. You can associate a user-defined exception with an
error code if you wish.
Comments
Post a Comment