INTRODUCTION
TO PL/SQL :
• Procedural
Language/Structured Query Language
• 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.
• Reduces
Network Traffic .
• Error
Handling .
• Declare
Variable.
• Intermediate
Calculation.
• Portable
Application.
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;
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;
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 in PL_SQL 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;
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;
Example:
declare
i number;
begin
for i in 1..10
Loop
dbms_output.put_line(i);
end loop;
end;
Cursor in PL-SQL
:
(1)Simple way select statement in pl-sql :
declare
eno int;
ename varchar(200);
begin
select id,name into
eno,ename from mytest where id='1';
dbms_output.put_line(eno||ename);
end;
(2) SELECT
USING %TYPE:
declare
eno mytest.id%type;
ename mytest.name%type;
begin
select id,name into eno , ename
from mytest where id='1';
dbms_output.put_line(eno||ename);
end;
(3)cursor example
with %rowtype :
declare
en mytest%rowtype;
cursor c1 is select
* from mytest;
begin
open c1;
loop
fetch c1 into en;
dbms_output.put_line(en.id||en.name);
exit when c1%notfound;
end loop;
close c1;
end;
(4)cursor with %type
example:
declare
en mytest.id%type;
ename mytest.name%type;
cursor c1 is select
id,name from mytest
where id=1;
begin
open c1;
loop
fetch c1 into en,ename;
dbms_output.put_line(en||ename);
exit when c1%notfound;
end loop;
close c1;
end;
(5) parameterized Cursor
:
declare
myno number;
cursor c1(en number)
is select *
from mytest where id=myno;
res c1%rowtype;
begin
myno:=&en;
open c1(myno);
loop
fetch c1 into res;
dbms_output.put_line(res.id||res.name);
exit when c1%notfound;
close c1;
end;
