Monday 3 August 2015

       

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;


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;






No comments:

Post a Comment