INTRODUCTION TO PL/SQL:
• PL/SQL
is Very Usefully Language and Tools of Oracle to Manipulate, Control, Validate,
and Restricted the Unauthorized Access of Data from the Database.
• PL/SQL
can improve the Performance of an Application and it is dealing with Error and
return User Friendly Error Message.
Advantages
PL/SQL :
• Procedural
Language Supported: PL/SQL is a development tools that not only
supported data Manipulation but also Provide the Condition, Checking, Looping
or Branching Operation.
• Reduces
Network Traffic: PL/SQL is same entire block of SQL statement execute
to the oracle engine at all at once so it's benefit to reduce the Network
Traffic.
• Error
Handling: PL/SQL also permits during with Error Handling as required
facility to Display User Friendly Error Message where errors are encounter.
• Declare
Variable: PL/SQL allow to declaration and use of variable in a block
of code which variable will use to store intermediate result of query for later
processing.
• Intermediate
Calculation: PL/SQL calculations done quickly and efficient without
the use of oracle engines and improve the transaction.
• Portable
Application: Application are written in PL/SQL is portable in any
computer or hardware for any system means Application independence to run any computer.
PL/SQL
Block:
PL/SQL Block consists of three sections:
• The
Declaration section (optional).
• The
Execution section (mandatory).
• The
Exception (or Error) Handling section (optional).
PL/SQL
Block:
DECLARE
Variable declaration
BEGIN
Program Execution
EXCEPTION
Exception handling
END;
Basic example :
DECLARE
message
varchar2(20):= 'Hello, World!';
BEGIN
dbms_output.put_line(message);
END;
PL/SQL DATA
TYPE:
•
Number type
Example :
no number:=10;
(The NUMBER datatype is used to store
fixed-point or floating-point numbers)
•
Character type:
(CHAR data type is used to store
fixed-length character data)
Example:
grade CHAR(1);
.VARCHAR2
(The
VARCHAR2 data type is used to store variable-length character data)
.Datetime type
(The Datetime data types lets us store
and manipulate dates, times, and intervals (periods of time). )
.Boolean type:
(BOOLEAN
data type is used to store the logical values TRUE, FALSE and NULL (which stand
for a missing, unknown, or inapplicable value).)
IF-THEN syntax:
IF condition
THEN
{
...statements
to execute when condition is TRUE...
}
END IF;
IF-THEN EXAMPLE:
declare
no
number:=&n;
begin
if no>0
then
dbms_output.put_line('is
positive');
end if;
end;
if-then-else
syntax:
IF condition
THEN
{
...statements
to execute when condition is TRUE...
}
ELSE
{
...statements
to execute when condition is FALSE...
}
END IF;
IF-THEN ELSE EXAMPLE:
declare
no
number:=&n;
begin
if no>0
then
dbms_output.put_line('is
positive');
else
dbms_output.put_line('is
negative');
end if;
end
IF-THEN-ELSIF-ELSE
syntax:
IF condition1
THEN
{
...statements
to execute when condition1 is TRUE...
}
ELSIF condition2
THEN
{
...statements
to execute when condition2 is TRUE...
}
ELSE
{
...statements
to execute when both condition1 and condition2 are FALSE...
}
END IF;
IF-THEN-ELSIF-ELSE example:
DECLARE
grade CHAR(1);
BEGIN
grade := 'B';
IF grade = 'A' THEN
DBMS_OUTPUT.PUT_LINE('Excellent');
ELSIF grade = 'B' THEN
DBMS_OUTPUT.PUT_LINE('Very Good');
ELSIF grade = 'C' THEN
DBMS_OUTPUT.PUT_LINE('Good');
ELSIF grade = 'D' THEN
DBMS_OUTPUT. PUT_LINE('Fair');
ELSIF grade = 'F' THEN
DBMS_OUTPUT.PUT_LINE('Poor');
ELSE
DBMS_OUTPUT.PUT_LINE('No such grade');
END IF;
END;
Case example:
DECLARE
grade CHAR(1);
BEGIN
grade := 'B';
CASE
WHEN grade = 'A' THEN
DBMS_OUTPUT.PUT_LINE('Excellent');
WHEN grade = 'B' THEN
DBMS_OUTPUT.PUT_LINE('Very Good');
WHEN grade = 'C' THEN
DBMS_OUTPUT.PUT_LINE('Good');
WHEN grade = 'D' THEN
DBMS_OUTPUT.PUT_LINE('Fair');
WHEN grade = 'F' THEN
DBMS_OUTPUT.PUT_LINE('Poor');
END CASE;
EXCEPTION
WHEN CASE_NOT_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No such grade');
END;
WHILE Loop syntax:
WHILE condition
LOOP
{
...statements...
}
END LOOP;
WHILE Loop example:
declare
n number:=1;
begin
while
n<10
loop
dbms_output.put_line(n);
n:=n+1;
end loop;
end;
For loop syntax:
FOR loop_counter IN [REVERSE] lowest_number..highest_number
LOOP
{
...statements
...}
END LOOP;
For loop example:
declare
i number;
begin
for i in
1..10
loop
dbms_output.put_line(i);
end loop;
end;
