PL/SQL

PL/SQL is Oracle's procedural language (PL) superset of the Structured Query Language (SQL). You can use PL/SQL to do such things as codify your business rules through the creation of stored procedures and packages, trigger database events to occur or add programming logic to the execution of SQL commands.

PL/SQL Overview
PL/SQL code is grouped into structures called blocks. If the block of PL/SQL code is not given a name, then it is called an anonymous block.

A block of PL/SQL code contains three sections. In Declaration section, you defines the variables and cursors that the block will use. The declaration section will start with the keyword declare and ends when the Executable Command section starts (keyword begin). The Executable Commands section is followed by the exception Handling Section. The PL/SQL block is terminated by the end keyword. Syntax:

declare

begin

exception

end;


Declaration Section
Example, the result is stored in a table named AREAS. The AREAS table has two columns, to store radius and area values.

declare
pi constant NUMBER(9,7) := 3.1415926 ;
radius INTEGER (5) ;
area NUMBER (14,2) ;
begin
radius := 3;
area := pi*power( radius, 2 );
insert into AREAS values (radius, area);
end;
/

The end; signals the end of the PL/SQL block and the / executes the PL/SQL block PL/SQL block is executed, you will receive the following response from Oracle:

PL/SQL procedure successfully completed.

In the following example, a cursor is declared to retrieve a record from the RADIUS_VALS table. RADIUS_VALS is a table with one column, named Radius, that holds radius value to be used in these examples. The cursor is declared in the Declaration section and a variable named rad_val is declared with a datatype based on the cursor's results.

declare
pi constant NUMBER(9,7) := 3.1415926 ;
area NUMBER (14,2) ;
cursor rad_cursor is select * from RADIUS_VALS;
rad_val rad_cursor%ROWTYPE;
begin
open rad_cursor;
fetch rad_cursor into rad_val;
area := pi*power( rad_val.radius, 2 );
insert into AREAS values (rad_val.radius, area);
close rad_cursor;
end;
/

The cursor definition consists of a cursor name (rad_cursor) and a query (select * from RADIUS_VALS;). A cursor holds the results of a query for processing by the other commands within the PL/SQL blocks.

rad_val rad_cursor%ROWTYPE;

The rad_val variable will be able to reference each column of the query's result set. In this example, the query only returns a single column, but if the table contained multiple columns, you would be able to reference all of them via the rad_val variable.

In addition to the %ROWTYPE declaration, you can use the %TYPE declaration to inherit datatype information. If you use the %ROWTYPE declaration, the variable inherits he column and datatype information for all the columns in the cursor's result set. If you use the %TYPE declaration, then the variable only inherits the definition of the column used to define it.


Executable Commands Section
In the executable Command section, you manipulate the variables and cursors declared in the Declarations section of your PL/SQL block. The Executable Commands section always starts with the keyword begin.

declare
pi constant NUMBER(9,7) := 3.1415926 ;
area NUMBER (14,2) ;
cursor rad_cursor is select * from RADIUS_VALS;
rad_val rad_cursor%ROWTYPE;
begin
open rad_cursor;
fetch rad_cursor into rad_val;
area := pi*power( rad_val.radius, 2 );
insert into AREAS values (rad_val.radius, area);
close rad_cursor;
end;
/

In the first command involving the cursor, the open command is used:

open rad_cursor;

ð When the rad_cursor cursor is opened, the query declared for that cursor is executed and the records to be retuned are identified.

fetch rad_cursor into rad_val;

ð In the Declarations section, the rad_var variable was declared to anchor its datatypes to the rad_cursor cursor.

cursor rad_cursor is select * from RADIUS_VALS;
rad_val rad_cursor%ROWTYPE;

ð When you fetch a record from the cursor into the rad_val variable, you can still address each column value selected via the cursor's querery. When the cursor's data is no longer needed, you can close the cursor as shown below,

close rad_cursor;


Conditional Logic

if, else and elsif syntax:

if
then
elsif
then
else
end if;

Nested if-else syntax:

if
then
if
then
end if;
else
end if;

Example,

declare
pi constant NUMBER(9,7) := 3.1415926 ;
area NUMBER (14,2) ;
cursor rad_cursor is select * from RADIUS_VALS;
rad_val rad_cursor%ROWTYPE;
begin
open rad_cursor;
fetch rad_cursor into rad_val;
area := pi*power( rad_val.radius, 2 );
if area > 30
then
insert into AREAS values (rad_val.radius , area);
end if;
close rad_cursor;
end;
/


Simple Loops
In the following listing, a simple loop is used to generate multiple rows in the AREAS table. The loop is started by the loop keyword and the exit when clause determines when the loop should be exited. An end loop clause signals the end of the loop.

Syntax,

loop
;
exit when ;
end loop;

Example,

declare
pi constant NUMBER(9,7) := 3.1415926 ;
radius INTEGER (5) ;
area NUMBER (14,2) ;
begin
radius := 3;
loop
area := pi*power( radius, 2 );
insert into AREAS values (radius, area);
radius := radius + 1 ;
exit when area > 100 ;
end loop;
end;
/



Simple Cursor Loops
You can use the attributes of a cursor - such as whether or not any rows are left to be fetched - as the exit criteria for a loop. In the following example, a cursor is executed until no more rows are returned by the query. To determine the status of the cursor, the cursor's attributes are checked. Cursor have four attributes you can use in your program:

Attribute Performance
%FOUND A record can be fetched from the cursor.
%NOTFOUND No more records can be fetched from the cursor.
%ISOPEN The cursor has been opened.
%ROWCOUNT The number of rows fetched from the cursor os far.

These attributes are Booleans; they are set to either TRUE or FALSE.

declare
pi constant NUMBER(9,7) := 3.1415926 ;
area NUMBER (14,2) ;
cursor rad_cursor is select * from RADIUS_VALS;
rad_val rad_cursor%ROWTYPE;
begin
open rad_cursor;
loop
fetch rad_cursor into rad_val;
exit when rad_cursor%NOTFOUND;
area := pi*power( rad_val.radius, 2 );
insert into AREAS values (rad_val.radius , area);
end loop;
close rad_cursor;
end;
/


FOR Loops
In a for loop, the loop executes a specified number of times. The for loop's start is indicated by the keyword for, followed by the criteria used to determine when the processing should exit the loop.

declare
pi constant NUMBER(9,7) := 3.1415926 ;
radius INTEGER (5) ;
area NUMBER (14,2) ;
begin
for radius in 1 . . 7 loop
area : = pi * power (radius , 2);
insert into AREAS values (radius, area);
end loop;
end;
/

SQL> SELECT * FROM AREAS ORDER BY radius;
RADIUS AREA
1 3.14
2 12.57
3 28.27
4 50.27
5 78.54
6 113.1
7 153.94

7 rows selected.



Cursor FOR Loop
In FOR loop, the loop executes a specified number of times. In a Cursor FOR loop, the results of query are used to dynamically determine the number of times the loop is executed.

declare
pi constant NUMBER(9,7) := 3.1415926 ;
area NUMBER (14,2) ;
cursor rad_cursor is select * from RADIUS_VALS;
rad_val rad_cursor%ROWTYPE;
begin
for rad_val in rad_cursor
loop
area := pi * power( rad_val.radius, 2 );
insert into AREAS values (rad_val.radius , area);
end loop;
end;
/


While Loops
In a WHILE loop, the loop is processed until an exit condition is met. Instead of specifying the exit condition via an exit command within the loop, the exit condition is specified in the while command that initiates the loop.

declare
pi constant NUMBER(9,7) := 3.1415926 ;
radius INTEGER (5) ;
area NUMBER (14,2) ;
begin
radius := 3;
while radius <= 7
loop
area : = pi * power (radius , 2);
insert into AREAS values (radius, area);
radius : = radius + 1;
end loop;
end;
/


Exception Handling Section
When user-defined or system-related exceptions (errors) are encountered, the control of the PL/SQL block shifts to the Exception Handling Section. If an exception is raised within the Exception Commands section of your PL/SQL block, the flow of commands immediately leaves the Executable Commands section and searches the Exception Handling section for an exception matching the error encountered.

Syntax:

declare

begin

exception

end;

In the following listing, the simple loop for calculating the area of a circle is shown, with two modifications (shown in bold). A new variable named some_variable is declared in the Declaration section and a calculation to determine the variable's value is created in the Executable Command section.

declare
pi constant NUMBER(9,7) := 3.1415926 ;
radius INTEGER (5) ;
area NUMBER (14,2) ;
some_variable NUMBER(14,2);
begin
radius := 3;
loop
some_variable := 1/ (radius - 4);
area : = pi * power (radius , 2);
insert into AREAS values (radius, area);
radius : = radius + 1;
exit when area > 100;
end loop;
end;
/

ð Because the calculation for some_variable involves division, you may encounter a situation in which the calculation attempts to divide by zero - an error condition.

declare
*
ERROR at line 1:
ORA-01476 : divisor is equal to zero
ORA-06512 : at line 9

Therefore you can add an Exception Section on your PL/SQL.

declare
pi constant NUMBER(9,7) := 3.1415926 ;
radius INTEGER (5) ;
area NUMBER (14,2) ;
some_variable NUMBER(14,2);
begin
radius := 3;
loop
some_variable := 1/ (radius - 4);
area : = pi * power (radius , 2);
insert into AREAS values (radius, area);
radius : = radius + 1;
exit when area > 100;
end loop;
exception
when ZERO_DIVIDE
then
insert into AREAS values (0, 0);
end;
/

ð When the PL/SQL block encounters an error, it scans the Exception Handling section for the defines exceptions. In this case, it finds the ZERO_DIVIDE exception, which is one of the system-defined exceptions available in PL/SQL. In addition to the system-defined exceptions and user-defined exceptions, you can use the when others clause to address all exception not defined within your Exception Handling section.