Mastering SQL for Data Science: A Comprehensive Guide
Day-9
PL/SQL
vPL/SQL Programming language was developed by Oracle Corporation in the late of 1980s as procedural extension language for SQL and the Oracle relational database.
PL/SQL stands for
“Procedural Language extensions to the Structured Query Language”.
SQL is a popular languagefor both querying and updating data in the relational database management
systems (RDBMS). PL/SQL is a standard and portable language for Oracle Database
Development. PL/SQL is a highly structured and readable language. Besides,
PL/SQL provides a more comprehensive programming language solution for building
mission –critical applications on Oracle Database.
PL/SQL is an embedded
language. PL/SQL only can execute in an Oracle Database. It was not designed to
use as a standalone language like java, C++ or C# etc ,which means we cannot
develop PL/SQL program that runs on a system that does not have an Oracle
Database.
v Advantages of PL/SQL:
ü SQL is the standard database language and PL/SQL is strongly integrated with SQL.
ü PL/SQL allows sending an entire block of statements to the database at one time. This, in turn, reduces network traffic and provides higher performance for the applications.
ü PL/SQL gives high productivity to programmers as it can query, transform, and update data in a database.
ü PL/SQL saves time on design and debugging by strong features, such as exception handling, data hiding and object- oriented data types.
ü Applications written in PL/SQL are fully portable with high security level.
ü PL/SQL provides support for developing Web applications and server pages.
ü PL/SQL provides access to predefined SQL packages.
v PL/SQL block structure:
PL/SQL code is grouped into
structures called blocks. Every Programming environment allows the creation of
structured, logical blocks of code that processes, which have to be applied to
data. A single PL/SQL code block consists of a set of SQL statements clubbed
together, and passed to the oracle engine entirely.
Declare
<Declaration of local variables,
constants, functions, procedures, cursors, exceptions …etc>
Begin
<SQL statements>;
<Other executable statements of PL>;
Exception
< Exception
Handling Messages>;
End;
/
E.g:
Declare
declare
a number;
begin
a:=&a;
dbms_output.put_line(a);
end;
/
v Data types in PL/SQL:
PL/SQL
has two kinds of data types: Scalar and Composite.
·
Scalar data types:
The Scalar types are types that
store a single value such as number, Boolean, character, and datetime whereas
the composite types are types that store multiple values, for example, record
and collection.
ü Boolean:
Boolean
data type can be used to store the values true, false or Null.
ü Binary_integer:
Binary_integer
us used to store signed integer. The range of the binary integer value is .
ü Number:
It
is same as SQL number data types. In addition to this it also includes ANSI
standard types with which includes following data types:
Dec|Decimal
Int|Integer
Real
A
company may decide that some comments about each of its various vendors must be
stored along with their details. The lob types are used to store objects. A
Large Object data types refer to large data items such as text, graphic images,
video clips and sound waveforms.
BLOB
used to store large binary objects in the database. (8 to 128 TB)
CLOB
used to store large blocks of character data in the database. (8 to 128 TB)
·
Composite Data type:
ü %type:
The %type attribute provides for
further integration. PL/SQL can use the %type attribute to declare the
variables based on the definitions of columns in a table. Hence, if a column’s
attributes change, the variable’s attributes will change as well. This provides
for data independence, reduces maintenance costs and allows program to adapt
the changes made into the table.
Syntax:
<variable_name> <table_name>.<column_name>%type;
E.g:
n emp.emp_no%type;
E.g:
declare
n student.no%type;
nm student.name%type;
begin
n:=&n;
select no,name
into n,nm
from student
where no=n;
end;
/
ü %rowtype:
%type attribute is used to declare a
variable’s data type as being equivalent to the specified column’s data type,
while %rowtype attribute is used to declare composite variable that is
equivalent to a row in the specified data. The composite variable is consists
of column names and data types in the referenced table. The declaration of
%rowtype attribute with variable, which means variable inherit column and its
data type information for all the columns of a table.
Syntax:
<variable_name> <table_name>%rowtype;
E.g:
emp_rec emp%rowtype;
E.g:
declare
trec temp%rowtype;
n temp.no%type;
begin
n:=&n;
select no,name
into trec
from temp
where no=n;
end;
/
v Control Structure:
ü Conditional
Statements
ü Iterative Statements
· Conditional Statements:
Within
Pl/SQL block, selection/conditional control is used when the execution of a particular
set of statement is based on specific condition. Sequence of statements can be
executed based on some condition using if statement.
Syntax:
If
<condition> then
<action/statements>;
Elsif
<action/statements>;
End
if;
declare
n number;
begin
n:=&n;
if n<0 then
dbms_output.put_line('your no. is negative');
end if;
if n>0 then
dbms_output.put_line('Your no. is positive');
end if;
if n=0 then
dbms_output.put_line('your no. is zero');
end if;
end;
/
PL/SQL
provides a facility for a executing statements repeatedly via loops. In PL/SQL
we have three loops as follows to execute statements repetitively.
ü Simple Loop
ü While Loop
ü For Loop
Let’s
discuss every loop in detail;
In
simple loop, the keyword loop should be placed before the first statement in
the sequence and the keyword end loop should be written at the end of the
sequence to end the loop.
Syntax:
Loop
<sequence of statements>;
End
loop;
E.g:
declare
i number;
n number;
begin
n:=&n;
i:=1;
loop
dbms_output.put_line(i);
i:=i+1;
exit when i>n;
end loop;
end;
/
Syntax:
While <condition>
Loop
<sequence of
statements>;
End loop;
Before
each iteration of the loop, condition is evaluated. If it equivalents to true,
sequence of statements are executed. If
condition evaluates to false or null, the loop is finished and control resumes
after the end loop statement.
The
only difference between simple loop and while loop is simple execute first and then
it will check the condition. So, simple loop execute at least once and in while
loop first it will check condition and then execute the rest of the statements.
E.g:
declare
n number;
i number;
begin
n:=&n;
i:=1;
while i<=n
loop
dbms_output.put_line(i);
i:=i+1;
end loop;
end;
/
ü For Loop:
For
loop statement is best suitable when you want to execute a code for a known
number of times rather than based on some other conditions.
In
this loop, the lower limit and upper limit will be specified and as long as the
loop variable is in between this range, the loop will be executed.
The
loop variable is self incremental, so no explicit increment operation is needed
in this loop. The sequence of statement contains all the code that needs to be
executed. If the loop variable came out of the range, then control will exit
from the loop.
The
loop can be made to work in the reverse order by adding the keyword ‘Reverse’
before lower limit.
Syntax:
For <loop_variable> in [reverse]
<lower_limit>..<upper_limit>
Loop
<sequence of
statements>;
End loop;
E.g:
declare
i number;
n number;
begin
n:=&n;
for i in 1..n
loop
dbms_output.put_line(i);
end loop;
end;
/
i number;
n number;
begin
n:=&n;
for i in reverse 1..n
loop
dbms_output.put_line(i);
end loop;
end;
/
0 Comments