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;
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;
Cursor in Oracle:
create table mytest
(id int,
name varchar(200));
then insert values and use following code to learn.
for example:
insert into mytest values(1,'om');
then use following to learn cursor...
(1)simple way select statement in pl-sql :
declare
enoint;
enamevarchar(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
enomytest.id%type;
enamemytest.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
enmytest.id%type;
enamemytest.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 taking input from user(command line) :
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);
exitwhen c1%notfound;
end loop;
closec1;
end;
