Basic Loop Example:

set serveroutput on
declare
a number(3);
begin
a:=1;
dbms_output.put_line('--------------');
loop
dbms_output.put_line(a||' Today is Wonderfull Day');
exit when a>10;
a:=a+1;
end loop;
dbms_output.put_line('--------------');
end;
/
------------------------------------------------
Case Statement Example:
set serveroutput on
declare
a number(3);
begin
a:=&a;
dbms_output.put_line('--------------');
case a
when 1 then dbms_output.put_line(' Today is Monday ');
when 2 then dbms_output.put_line(' Today is Tuesday');
when 3 then dbms_output.put_line(' Today is Wednesday');
when 4 then dbms_output.put_line(' Today is Thursday ');
when 5 then dbms_output.put_line(' Today is friday ');
when 6 then dbms_output.put_line(' Today is Saturday ');
when 7 then dbms_output.put_line(' Today is Sunday ');
else
dbms_output.put_line(' Plese Enter value between 1 to 7 ');
end case;
dbms_output.put_line('--------------');
end;
/
------------------------------------------------
Pre-Deined Exception (zero Divide) Example:
set serveroutput on
declare
a number(3);
b number(3);
d number(3);
begin
a:=&a;
b:=&b;
d:=a/b;
dbms_output.put_line('----------------');
dbms_output.put_line('Division is:: '||d);
dbms_output.put_line('----------------');
EXCEPTION
when zero_divide then
dbms_output.put_line(b||' Enter Non Zero Values:: ');
when others then
dbms_output.put_line(' Unknown Error ');
end;
/
------------------------------------------------
drop table student;
create table student
(
id number(3),
name varchar2(10),
adr varchar2(10)
);
desc student;
insert into student(id,name,adr)values(1,'akash','Indapur');
insert into student(id,name,adr)values(2,'suraj','Baramati');
insert into student(id,name,adr)values(3,'ramesh','Indapur');
insert into student(id,name,adr)values(4,'asif','Baramati');
-----------------------------------------------------------
Pre-Deined Exception (no data and too many rows) Example:
set serveroutput on
declare
i student.id%type;
n student.name%type;
a student.adr%type;
begin
i:=&i;
select name,adr into n,a from student where id=i;
dbms_output.put_line('----------------');
dbms_output.put_line('Id:'||i||' Name:'||n||' Adr:'||a);
dbms_output.put_line('----------------');
EXCEPTION
when no_data_found then
dbms_output.put_line(' Data Not Available ');
when too_many_rows then
dbms_output.put_line(' Lot of Data ');
when others then
dbms_output.put_line(' Unknown Error ');
end;
/
-----------------------------------------------------------
User-Deined Exception Example 1:
set serveroutput on
declare
i student.id%type;
n student.name%type;
a student.adr%type;
invalid exception;
begin
i:=&i;
if(i<=0) then
raise invalid;
end if;
select name,adr into n,a from student where id=i;
dbms_output.put_line('----------------');
dbms_output.put_line('Id:'||i||' Name:'||n||' Adr:'||a);
dbms_output.put_line('----------------');
EXCEPTION
when invalid then
dbms_output.put_line('Id value must be greater than zero');
when others then
dbms_output.put_line(' Unknown Error ');
end;
/
-----------------------------------------------------------
User-Deined Exception Example 2:
set serveroutput on
declare
b number(7);
insufϐicient exception;
begin
b:=&b;
if(b<500)then
raise insufϐicient;
end if;
dbms_output.put_line('----------------');
dbms_output.put_line('You can Withdraw Money');
dbms_output.put_line('----------------');
EXCEPTION
when insufϐicient then
dbms_output.put_line('Insufϐicient Balance');
when others then
dbms_output.put_line('Unknown Error');
end;
/
-----------------------------------------------------------
drop table student;
create table student
(
id number(3),
name varchar2(10),
adr varchar2(10)
);
desc student;
insert into student(id,name,adr)values(1,'akash','Indapur');
insert into student(id,name,adr)values(2,'suraj','Baramati');
insert into student(id,name,adr)values(3,'ramesh','Indapur');
insert into student(id,name,adr)values(4,'asif','Baramati');
-----------------------------------------------------------
Explicit Cursor Example: ( basic loop)
set serveroutput on
declare
cursor c is select * from student;
r student%rowtype;
begin
open c;
dbms_output.put_line('----------------');
loop
fetch c into r;
dbms_output.put_line(r.id||' '||r.name||' '||r.adr);
exit when c%notfound;
end loop;
dbms_output.put_line('No of Rows ::'||c%rowcount);
close c;
dbms_output.put_line('----------------');
end;
/
-----------------------------------------------------------
Explicit Cursor Example: ( for loop)
set serveroutput on
declare
cursor c is select * from student;
begin
dbms_output.put_line('--------------');

for r in c
loop
dbms_output.put_line(r.id||' '||r.name||' '||r.adr);
end loop;

dbms_output.put_line('--------------');
end;
/
-----------------------------------------------------------
Parametrized Cursor Example: (basic loop)
set serveroutput on
declare
cursor c(a varchar2) is select * from student where adr=a;
r student%rowtype;
begin
open c('&a');
dbms_output.put_line('----------------');
loop
fetch c into r;
dbms_output.put_line(r.id||' '||r.name||' '||r.adr);
exit when c%notfound;
end loop;
dbms_output.put_line('No of Rows ::'||c%rowcount);
close c;
dbms_output.put_line('----------------');
end;
/
-----------------------------------------------------------
Parametrized Cursor Example: (for loop)
set serveroutput on
declare
cursor c(a varchar2) is select * from student where adr=a;
begin
dbms_output.put_line('--------------');

for r in c('&a')
loop
dbms_output.put_line(r.id||' '||r.name||' '||r.adr);
end loop;

dbms_output.put_line('--------------');
end;
/
-----------------------------------------------------------
For Loop Example:( factorial of number)
set serveroutput on
declare
a number(3);
fact number(7):=1;
begin
a:=&a;
dbms_output.put_line('--------------');
for i in reverse 1..a
loop
fact:=fact*i;
end loop;
dbms_output.put_line(a||' factorial ='||fact);
dbms_output.put_line('--------------');
end;
/
-----------------------------------------------------------
For Loop Example:
set serveroutput on
begin
dbms_output.put_line('--------------');
for a in 1..10
loop
dbms_output.put_line(a||' Today is Wonderfull Day');
end loop;
dbms_output.put_line('--------------');
end;
/
-----------------------------------------------------------
Exit Example:
set serveroutput on
declare
begin
dbms_output.put_line('----------------');
for i in 1..3
loop
for j in 1..3
loop
if j=2 then
exit;
end if;
dbms_output.put_line(i ||'::::'||j);
end loop;
end loop;
dbms_output.put_line('----------------');
end;
/
-----------------------------------------------------------
Function Example: (in out mode)
set serveroutput on
create or replace function fun1(a in out number)
return number
as
begin
a:=a*a;
return a;
end;
/
-----------------------------------------------------------
Function Execute:
Method 1:
SQL>Select fun1(5) from dual;
Method 2:
set serveroutput on
declare
a number(3);
begin
dbms_output.put_line('----------------');
a:=fun1(7);
dbms_output.put_line(': Square of 7 is :::'||a);
dbms_output.put_line('----------------');
end;
/
-----------------------------------------------------------
drop table student;
create table student
(
id number(3),
name varchar2(10),
adr varchar2(10)
);
desc student;
insert into student(id,name,adr)values(1,'akash','Indapur');
insert into student(id,name,adr)values(2,'suraj','Baramati');
insert into student(id,name,adr)values(3,'ramesh','Indapur');
insert into student(id,name,adr)values(4,'asif','Baramati');
-----------------------------------------------------------
Function Example: (in mode)
set serveroutput on
create or replace function fun1(i in number)
return varchar2
as
n student.name%type;
a student.adr%type;
begin
select name,adr into n,a from student where id=i;
return concat(n,' lives in '||a);
dbms_output.put_line('--------------');
dbms_output.put_line('--------------');
end;
/
-----------------------------------------------------------
Function Execute:
Method 1:
SQL>Select fun1(1) from dual;
Method 2:
set serveroutput on
declare
a varchar2(30)
begin
dbms_output.put_line('----------------');
a:=fun1(1);
dbms_output.put_line(a);
dbms_output.put_line('----------------');
end;
/
-----------------------------------------------------------
If ELSE Example: (greatest number between three)
set serveroutput on
declare
a number(3);
b number(3);
c number(3);
begin
a:=&a;
b:=&b;
c:=&c;
dbms_output.put_line('----------------');
if(a>b and a>c) then
dbms_output.put_line(a ||': is Greatest Number :');
elsif(b>c) then
dbms_output.put_line(b ||': is Greatest Number :');
else
dbms_output.put_line(c ||': is Greatest Number :');
end if;
dbms_output.put_line('----------------');
end;
/
-----------------------------------------------------------
If Example: (Even Number)
set serveroutput on
declare
i number(3);
begin
i:=&i;
if(mod(i,2)=0) then
dbms_output.put_line('----------------');
dbms_output.put_line(i ||': is Even Number :');
dbms_output.put_line('----------------');
end if;
end;
/
-----------------------------------------------------------
If Example: (Even or Odd Number)
set serveroutput on
declare
i number(3);
begin
i:=&i;
dbms_output.put_line('----------------');
if(mod(i,2)=0) then
dbms_output.put_line(i||' : is Even Number.');
else
dbms_output.put_line(i||' : is Odd Number.');
end if;
dbms_output.put_line('----------------');
end;
/
-----------------------------------------------------------
If Else Ladder Example: ( Grade)
set serveroutput on
declare
i number(3);
begin
i:=&i;
dbms_output.put_line('----------------');
if(i<=100 and i>=75) then
dbms_output.put_line(i||' Distinction.');
elsif(i<=74 and i>=60) then
dbms_output.put_line(i||' First Class.');
elsif(i<=59 and i>=50) then
dbms_output.put_line(i||' Second Class.');
elsif(i<=49 and i>=40) then
dbms_output.put_line(i||' Pass Class.');
else
dbms_output.put_line(' Need of Improvement ');
end if;
dbms_output.put_line('----------------');
end;
/
-----------------------------------------------------------
drop table student;
create table student
(
id number(3),
name varchar2(10),
adr varchar2(10)
);
desc student;
insert into student(id,name,adr)values(1,'akash','Indapur');
insert into student(id,name,adr)values(2,'suraj','Baramati');
insert into student(id,name,adr)values(3,'ramesh','Indapur');
insert into student(id,name,adr)values(4,'asif','Baramati');
-----------------------------------------------------------
Implicit Cursor Example:
set serveroutput on
declare
a number(3);
begin
a:=10;
dbms_output.put_line('--------------');
insert into student(id,name,adr)values(7,'G','Indapur');

dbms_output.put_line('No.rows Effected::'||SQL%FOUND);

dbms_output.put_line('--------------');
end;
/
-----------------------------------------------------------
Display values from Example:
set serveroutput on
declare
a student.name%type;
b student.adr%type;
i student.id%type;
begin
i:='&i';
select name,adr into a,b from student where id=i;
dbms_output.put_line('--------------');
dbms_output.put_line('WElcome --'||a||' Address '||b);
dbms_output.put_line('--------------');
end;
/
-----------------------------------------------------------
Goto Statement Example:
set serveroutput on
declare
begin
dbms_output.put_line('----------------');
goto Seven;
dbms_output.put_line('01:::Its Wonderfull Day:::');
dbms_output.put_line('02:::Its Wonderfull Day:::');
<<Three>>
dbms_output.put_line('03:::Its Wonderfull Day:::');
goto Ten;
dbms_output.put_line('04:::Its Wonderfull Day:::');
dbms_output.put_line('04:::Its Wonderfull Day:::');
dbms_output.put_line('06:::Its Wonderfull Day:::');
<<Seven>>
dbms_output.put_line('07:::Its Wonderfull Day:::');
goto Three;
dbms_output.put_line('08:::Its Wonderfull Day:::');
dbms_output.put_line('09:::Its Wonderfull Day:::');
<<Ten>>
dbms_output.put_line('10:::Its Wonderfull Day:::');

dbms_output.put_line('----------------');
end;
/
-----------------------------------------------------------
Prime Number Example:
set serveroutput on
declare
a number(3);
begin
dbms_output.put_line('--------------');
for i in 1..10
loop
a:=0;
for j in 2..i/2
loop
if(mod(i,j)=0) then
a:=1;
end if;
end loop;
if(a=0) then
dbms_output.put_line(i ||' is Prime');
end if;
end loop;
dbms_output.put_line('--------------');
end;
/
-----------------------------------------------------------
Procedure Example: (in mode)
set serveroutput on
create or replace procedure prg1(n in number)
as
b number(6);
begin
b:=n*n;
dbms_output.put_line('--------------');
dbms_output.put_line(n||'***:Square is :***'||b);
dbms_output.put_line('--------------');
end;
/
-----------------------------------------------------------
Procedure Execute:
Method 1:
SQL>Execute prg1(7);
Method 2:
set serveroutput on
begin
dbms_output.put_line('----------------');
prg1(7);
dbms_output.put_line('----------------');
end;
/
-----------------------------------------------------------
Procedure Example: (in mode and out mode)
set serveroutput on
create or replace procedure prg1(n in number,b out number)
as
begin
b:=n*n;
dbms_output.put_line('--------------');
dbms_output.put_line('--------------');
end;
/
-----------------------------------------------------------
Procedure Execute:
Method 2:
set serveroutput on
declare
d number(4);
begin
dbms_output.put_line('----------------');
prg1(7,d);
dbms_output.put_line(7||'***:Square is :***'||d);
dbms_output.put_line('----------------');
end;
/
-----------------------------------------------------------
Procedure Example: (in out mode)
set serveroutput on
create or replace procedure prg1(n in out number)
as
begin
n:=n*n;
end;
/
-----------------------------------------------------------
Procedure Execute:
Method 2:
set serveroutput on
declare
d number(4);
begin
dbms_output.put_line('----------------');
d:=7;
prg1(d);
dbms_output.put_line(7||'***:Square is :***'||d);
dbms_output.put_line('----------------');
end;
/
-----------------------------------------------------------
drop table emp;
create table emp
(
id number(3),
name varchar2(10),
salary number(7)
);
desc emp;
insert into emp(id,name,salary)values(1,'akash',5000);
insert into emp(id,name,salary)values(2,'Suraj',7000);
insert into emp(id,name,salary)values(3,'Rohit',9000);
-----------------------------------------------------------
Trigger Example: (Salary difference)
set serveroutput on
create or replace trigger trg1
after
update
on emp
for each row
when (new.id>0)
declare
diff number(7);
begin
diff:=:new.salary-:old.salary;
dbms_output.put_line('-------------------------');
dbms_output.put_line('Old Salary '||:old.salary);
dbms_output.put_line('New Salary '||:new.salary);
dbms_output.put_line('Salary Difference'||diff);
dbms_output.put_line('-------------------------');
end;
/
-----------------------------------------------------------
drop table left;
create table left
(
id number(3),
name varchar2(10),
salary number(7),
Date_of_left date
);
-----------------------------------------------------------
Trigger Example: (delete)
set serveroutput on
create or replace trigger trg1
after
delete
on emp
for each row
begin
dbms_output.put_line('-------------------------');
insert into left(id,name,salary,Date_of_left)
values(:old.id,:old.name,:old.salary,sysdate);
dbms_output.put_line('-------------------------');
end;
/
-----------------------------------------------------------
User Deined Exception
set serveroutput on
declare
i student.id%type;
b student.name%type;
a student.adr%type;
invalid EXCEPTION;
begin
i:=&i;
if(i<=0) then
raise invalid;
end if;
select name,adr into b,a from student where id=i;
dbms_output.put_line('--------------');
dbms_output.put_line('ID:'||i||' Name:'||b||' Adr:'||a);
dbms_output.put_line('--------------');
exception
when invalid then
dbms_output.put_line('-Enter Value Greater than Zero-');
end;
/
-----------------------------------------------------------
Simple Example: (value from user)
set serveroutput on
declare
a varchar2(20);
begin
a:='&a';
dbms_output.put_line('--------------');
dbms_output.put_line('WElcome --'||a);
dbms_output.put_line('--------------');
end;
/
-----------------------------------------------------------
While Loop Example:
set serveroutput on
declare
a number(3);
begin
a:=10;
dbms_output.put_line('--------------');
while a>=1
loop
dbms_output.put_line(a||' Today is Wonderfull Day');
a:=a-1;
end loop;
dbms_output.put_line('--------------');
end;
/
-----------------------------------------------------------
While Loop Example: Reverse Number
set serveroutput on
declare
a number(3);
begin
a:=10;
dbms_output.put_line('----------------');
while a>=1
loop
dbms_output.put_line(a||' Today is Good Day ! ');
a:=a-1;
end loop;
dbms_output.put_line('----------------');
end;
/
-----------------------------------------------------------
Self-Join Table
drop table emp;
create table emp
(
e_id number(3),
name varchar2(10),
m_id number(3)
);
desc emp;
insert into emp(e_id,name,m_id)values(1,'akash',3);
insert into emp(e_id,name,m_id)values(2,'suraj',4);
insert into emp(e_id,name,m_id)values(3,'rohit',3);
insert into emp(e_id,name,m_id)values(4,'rahul',1);
select * from emp;
-----------------------------------------------------------
Self-Join Example
Select a.name,b,name from emp a, emp b where a,m_id=b.e_id;
-----------------------------------------------------------
Join Table
drop table student;
create table student
(
id number(3),
name varchar2(10),
adr varchar2(10)
);
desc student;
insert into student(id,name,adr)values(1,'akash','Indapur');
insert into student(id,name,adr)values(2,'suraj','Baramati');
insert into student(id,name,adr)values(3,'ramesh','Indapur');
insert into student(id,name,adr)values(4,'asif','Baramati');
drop table lib;
create table lib
(
id number(3),
book varchar2(10)
);
insert into lib(id,book)values(1,'DMS');
insert into lib(id,book)values(2,'DSU');
insert into lib(id,book)values(5,'CGR');
insert into lib(id,book)values(6,'OOP');
-----------------------------------------------------------
Inner Join Example
Select student.name,lib.book from student inner join lib on student.id=lib.id;
-----------------------------------------------------------
Left Outer Join Example
Select student.name,lib.book from student left outer join lib on student.id=lib.id;
-----------------------------------------------------------
Right Outer Join Example
Select student.name,lib.book from student right outer join lib on student.id=lib.id;
-----------------------------------------------------------
Full Outer Join Example
Select student.name,lib.book from student full outer join lib on student.id=lib.id;
-----------------------------------------------------------
View Table
drop table college;
create table college
(
id number(3),
name varchar2(10),
adr varchar2(10),
per number(3),
account number(5),
fees number(5)
);
insert into college(id,name,adr,per,account,fees)
values(1,'asif','Bara',67,68664,20000);
insert into college(id,name,adr,per,account,fees)
values(2,'rohan','Ind',78,77654,35000);
insert into college(id,name,adr,per,account,fees)
values(3,'ganesh','Tem',87,44557,25000);
insert into college(id,name,adr,per,account,fees)
values(4,'raj','Bara',95,99886,15000);
-----------------------------------------------------------
View Example
Create view v1
As
Select name,fees from college;
-----------------------------------------------------------
Select * from v1;
-----------------------------------------------------------
View Example
Create view v2
As
Select student.name,lib.book from student full outer join lib on student.id=lib.id;
-----------------------------------------------------------
Select * from v2;
-----------------------------------------------------------
Group By Table
drop table student;
create table student
(
id number(3),
name varchar2(10),
adr varchar2(10)
);
desc student;
insert into student(id,name,adr)values(1,'akash','Indapur');
insert into student(id,name,adr)values(2,'suraj','Baramati');
insert into student(id,name,adr)values(3,'ramesh','Indapur');
insert into student(id,name,adr)values(4,'asif','Baramati');
insert into student(id,name,adr)values(5,'Rohit','Indapur');
insert into student(id,name,adr)values(6,'Virat','Pune');
-----------------------------------------------------------
Group By Example
Select adr,count(adr) from student group by adr;
-----------------------------------------------------------
Group By – having Example
Select adr,count(adr) from student group by adr having adr=’Indapur’;
-----------------------------------------------------------
Sequence Table
drop table student;
create table student
(
id number(3) primary key,
name varchar2(10),
adr varchar2(10)
);
-----------------------------------------------------------
Sequence Example
Create sequence seq1
Start with 1
Increment by 1
Maxvalue 5
Minvalue 1
Nocycle;
-----------------------------------------------------------
Sequence Run
insert into student(id,name,adr)values(seq1.nextval,'akash','Indapur');
insert into student(id,name,adr)values(seq1.nextval,'suraj','Baramati');
insert into student(id,name,adr)values(seq1.nextval,'ramesh','Indapur');
insert into student(id,name,adr)values(seq1.nextval,'asif','Baramati');
select * from student;
-----------------------------------------------------------
Create User
Create user vpp identiϐied by pass1234;
Grant create session, connect, resource to vpp;
Revoke connect from vpp;
---------------------------------------------------------------