Scribd
Upload
12 views25 pages

PL SQL Program

The document provides a comprehensive overview of PL/SQL programming syntax and examples, including variable declarations, conditional statements, and calculations for various scenarios such as student results, employee salaries, and electricity bills. It demonstrates the use of `dbms_output.put_line` for displaying output and includes multiple examples of using `if...else` statements for decision-making in code. The document is structured chronologically, detailing different programming exercises and outputs for specific dates.

Uploaded by

Pranjali
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
12 views25 pages

PL SQL Program

The document provides a comprehensive overview of PL/SQL programming syntax and examples, including variable declarations, conditional statements, and calculations for various scenarios such as student results, employee salaries, and electricity bills. It demonstrates the use of `dbms_output.put_line` for displaying output and includes multiple examples of using `if...else` statements for decision-making in code. The document is structured chronologically, detailing different programming exercises and outputs for specific dates.

Uploaded by

Pranjali
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 25

DATE:- 06-Feb-2023 PL SQL PRGRAMMING

syntax is

begin

end;

in this programming variables declare separetly in declare section

syntax;

declare
// declare all variable
begin
assign value to that varibale
then display or print the masg use dbms_output.put_line(' print whatever msg u want
to print ');

begin
dbms_output.put_line('happy');
dbms_output.put_line('new');
dbms_output.put_line('year');
dbms_output.put_line('2023');
end;

declare
x number(5);
begin
x := 10;
dbms_output.put_line('Roll no: ' || x);
end;

declare
first_num number(5);
second_num number(5);
nm varchar2(20);
begin
first_num := 1000;
second_num := 5000;
nm := 'Prajali';
dbms_output.put_line('1st num : ' || first_num);
dbms_output.put_line('2nd num: ' || second_num);
dbms_output.put_line('Nmae: ' || nm);
end;

declare
rollnum number(5);
sname varchar2(20);
marks number(5);
begin
rollnum :=46;
sname := 'tinu';
marks := 90;
dbms_output.put_line('Rno : ' || rollnum || ' studname: ' || sname || ' Total Marks
: ' || marks);
end;
Date := 7-feb-2023

use hr

set serveroutput on

declare
numA number(5);
numB number(5);
numC number(8);
begin
numA := 500;
numB := 200;
numC := numA + numB;
dbms_output.put_line('total Addition :- ' || numC );
end;

// student result
declare
rolno number(5);
sname varchar2(20);
total_marks number(5);
avg_marks number(5);
percentage number(5);
hindi number(5);
eng number(5);
marathi number(5);
begin
rolno := 22;
sname := 'malti';
hindi := 80;
eng := 70;
marathi := 60;
total_marks := hindi + eng + marathi ;
avg_marks := (hindi+eng+marathi)/3;
percentage := (hindi+eng+marathi)*0.34;
dbms_output.put_line('Roll number of student :- ' || rolno);
dbms_output.put_line('Name of student :- ' || sname);
dbms_output.put_line('Total marks of student :- ' || total_marks);
dbms_output.put_line('Average marks of student :- ' || avg_marks);
dbms_output.put_line('percentage of student :- ' || percentage);
end;

//student percentage
declare
rolno number(5);
sname varchar2(20);
total_marks number(5);
avg_marks number(5);
percentage number(5);
hindi number(5);
eng number(5);
marathi number(5);
begin
rolno := 25;
sname := 'Maya';
hindi := 95;
eng := 85;
marathi := 75;
total_marks := hindi + eng + marathi ;
avg_marks := total_marks/3;
percentage := total_marks/300*100;
dbms_output.put_line('Roll number of student :- ' || rolno);
dbms_output.put_line('Name of student :- ' || sname);
dbms_output.put_line('Total marks of student :- ' || total_marks);
dbms_output.put_line('Average marks of student :- ' || avg_marks);
dbms_output.put_line('percentage of student :- ' || percentage);
end;

// employee salary
declare
employer varchar2(30);
empnum number(8);
empname varchar2(20);
bsal number(10);
gross_sal number(10);
net_sal number(10);
pf number(10);
da number(10);
hra number(10);
begin
employer:= 'SHCIL';
empnum:=4432;
empname:='pranjali';
bsal:=30000;
pf:=bsal*.12;
da:=bsal*.20;
hra:=bsal*.60;
gross_sal:=bsal+da+hra;
net_sal:=gross_sal-pf;
dbms_output.put_line('employer name :- ' || employer);
dbms_output.put_line('employee number:- ' || empnum);
dbms_output.put_line('employee name :- '|| empname);
dbms_output.put_line('employee PF :- '|| pf);
dbms_output.put_line('employee DA :- '|| da);
dbms_output.put_line('employee HRA :- '|| hra);
dbms_output.put_line('employee basic salary :-' || bsal);
dbms_output.put_line('employee gross salary :- '|| gross_sal);
dbms_output.put_line('employee net salary :- ' || net_sal);
end;

Date: 8-feb-20213

use hr
set serveroutput on

// value taken from user use &


declare
x number(4);
begin
x:=&x;
dbms_output.put_line('x='||x);
end;

// employee salary structure


declare
employer varchar2(30);
empnum number(8);
empname varchar2(20);
bsal number(10);
gross_sal number(10);
net_sal number(10);
pf number(10);
da number(10);
hra number(10);
begin
employer:= 'SHCIL';
empnum:=&empnum;
empname:='Pranjali';
bsal:=&bsal;
pf:=bsal*.12;
da:=bsal*.20;
hra:=bsal*.60;
gross_sal:=bsal+da+hra;
net_sal:=gross_sal-pf;
dbms_output.put_line('employer name :- ' || employer);
dbms_output.put_line('employee number:- ' || empnum);
dbms_output.put_line('employee name :- '|| empname);
dbms_output.put_line('employee PF :- '|| pf);
dbms_output.put_line('employee DA :- '|| da);
dbms_output.put_line('employee HRA :- '|| hra);
dbms_output.put_line('employee basic salary :-' || bsal);
dbms_output.put_line('employee gross salary :- '|| gross_sal);
dbms_output.put_line('employee net salary :- ' || net_sal);
end;

declare
rollnum number(5);
sname varchar2(20);
marks number(5);
begin
rollnum :=&rollnum;
sname := 'tinu';
marks :=&marks;
dbms_output.put_line('Rno : ' || rollnum || ' studname: ' || sname || ' Total Marks
: ' || marks);
end;

//electricity bill

declare
mno number(10); cname varchar2(20); ltype varchar2(2); cr number(10); pr
number(10); nu number(10); fbill number(10);
begin
mno:=&mno; cname:='Hiwale'; ltype:='C'; cr:=&cr; pr:=≺ nu:=(cr-pr);
fbill:=nu*4.5;
dbms_output.put_line('meter no - ' || mno);
dbms_output.put_line('consumer name - ' || cname);
dbms_output.put_line('line type - '|| ltype);
dbms_output.put_line('current reading - '|| cr);
dbms_output.put_line('previous reading - '|| pr);
dbms_output.put_line('number of unit - '|| nu);
dbms_output.put_line('final bill - '|| fbill);
end;

// item purchasing bill


declare
ino number(10); iname varchar2(10); poi number(10);qoi number(10);tbill
number(10);da number(10);fbill number(10);
begin
ino:=&ino; iname:='Pranali'; poi:=&poi; qoi:=&qoi; tbill:=poi*qoi; da:=tbill*0.1;
fbill:=tbill-da;
dbms_output.put_line('Item no = ' || ino);
dbms_output.put_line('Name of item = ' || iname);
dbms_output.put_line('price of item = ' || poi);
dbms_output.put_line('quantity of item = ' || qoi);
dbms_output.put_line('total bill of item = ' || tbill);
dbms_output.put_line('Discount amount of item = ' || da);
dbms_output.put_line('final bill of item after 10% discount = ' || fbill);
end;

Date:= 13-feb-2023
use hr
set serveroutput on

// using multiple if ....if condition then


// elsif then
// else
// end if;

declare
fno number(10); sno number(10);
begin
fno:=&fno;
sno:=&sno;
dbms_output.put_line(' number1 ' || fno || ' number1 ' || sno);
if fno>sno then
dbms_output.put_line('first number is greater than second');
elsif sno>fno then
dbms_output.put_line('second number is greater than first');
else
dbms_output.put_line('both are equal');
end if;
end;

// multipl if else .......Salesman and commission

declare
sno number(5); sname varchar2(20); salary number(10); samt number(20); comm
number(20); income number(20);
begin
sno:=442;
sname:='Bosco';
samt:=&samt;
salary:=30000;
dbms_output.put_line('Number of salesman ' || sno);
dbms_output.put_line('Name of salesman ' || sname);
dbms_output.put_line('Sales amount ' || samt);
dbms_output.put_line('Salary of salesman ' || salary);
if samt<=1500 then
comm:=samt*0.05;
dbms_output.put_line('Sales amount range 1-1500 then commission :- ' || comm);
elsif samt<=3000 then
comm:=samt*0.07;
dbms_output.put_line('Sales amount range 1501-3000 then commission :- ' || comm);
elsif samt<=4500 then
comm:=samt*0.10;
dbms_output.put_line('Sales amount range 3001-4500 then commission :- ' || comm);
else
comm:=samt*0.12;
dbms_output.put_line('Sales amount range 4501 and above then commission :- ' ||
comm);
end if;
income:=salary+comm;
dbms_output.put_line('Total income :- ' || income);
end;

// multipl if else .....Item purchasing


declare
ino number(10); iname varchar2(20); poi number(20); qoi number(20); tbill
number(20); dbill number(20);
begin
ino:=11;
iname:='Shoes';
poi:=&poi; qoi:=&qoi;
dbms_output.put_line('Number of item :- ' || ino);
dbms_output.put_line('Name of item :- ' || iname);
dbms_output.put_line('Price of item :- ' || poi);
dbms_output.put_line('Quantity of item :- ' || qoi);
tbill:=poi*qoi;
dbms_output.put_line('Total bill ' || tbill);
if tbill<=3000 then
dbill:=tbill-(0.02*tbill);
dbms_output.put_line('Discounted bill ' || dbill);
elsif tbill<=6000 then
dbill:=tbill-(0.03*tbill);
dbms_output.put_line('Discounted bill ' || dbill);
else
dbill:=tbill-(0.05*tbill);
dbms_output.put_line('Discounted bill ' || dbill);
end if;
end;
Date: 9-feb-2023

use hr
set serveroutput on

// if ...else...learning

//if comes with condition if its true then print the given msg other wise print
reming one or we use else then remaing msg which not satisfied condition
// else always comes with if
// syntax if condition
//end if;

declare
no number;
begin
no:=&no;
dbms_output.put_line('print number - ' || no);
if no>=18 then
dbms_output.put_line('Eligible');
else
dbms_output.put_line('Not Eligible');
end if;
dbms_output.put_line('Finish');
end;

declare
pname varchar2(20);
ppage number(10);
begin
pname:='pranjali';
ppage:=&ppage;
dbms_output.put_line('age of person - ' || ppage);
dbms_output.put_line('name of person - ' || pname);
if ppage>=21 then
dbms_output.put_line('Eligible for marriage ');
else
dbms_output.put_line('Not eligible for marriage ');
end if;
end;

// if ..else salesamt and commision


declare
sno number(5); sname varchar2(20); samt number(10);comm number(10);
begin
sno:=&sno; sname:='geeta'; samt:=&samt;
dbms_output.put_line('Name of sales person ' || sname);
dbms_output.put_line('Number of sales person ' || sno);
dbms_output.put_line('sales amount ' || samt);
if samt>=1000 then comm:=samt*0.10;
dbms_output.put_line('commision amount ' || comm);
else
comm:=samt*0.05;
dbms_output.put_line('commision amount ' || comm);
end if;
end;

// if ....else item purchasing


declare
ino number(5); iname varchar2(20); iprice number(20); iqty number(10); tbill
number(20); dbill number(20); nbill number(20);discount number(20);
begin
ino:=&ino;
iname:='Watch';
iprice:=&iprice; iqty:=&iqty;
dbms_output.put_line('Number of item '|| ino);
dbms_output.put_line('Price of item '|| iprice);
dbms_output.put_line('quantity of item '|| iqty);
dbms_output.put_line('Name of item '|| iname);
tbill:=iprice*iqty;
dbms_output.put_line('Total bill of item '|| tbill);
if tbill>=5000 then dbill:=tbill*0.10;
dbms_output.put_line('Discount'|| dbill);
else
dbill:=tbill*0.08;
dbms_output.put_line('Discount'|| dbill);
end if;
nbill:=tbill-dbill;
dbms_output.put_line('Net bill'|| nbill);
end;

// if .......else Wages
declare
wno number(10);wname varchar2(20);wh number(10);wages number(10);
begin
wno:=&wno;
wname:='John';
wh:=&wh;
dbms_output.put_line('Worker number ' || wno);
dbms_output.put_line('Worker name ' ||wname);
if wh<=8 then wages:=wh*50;
dbms_output.put_line('Wages for first 8 hours ' ||wages);
else
wages:=(8*50)+(wh-8)*90;
end if;
dbms_output.put_line('Wages for next all hours ' ||wages);
end;

//if........else electricity bill


declare
mno number(3);cname varchar2(10); cr number(10); pr number(10); nou number(10);
charges number(10);
begin
mno:=105;
cname:='Lizy';
dbms_output.put_line('Meter no. - '|| mno);
dbms_output.put_line('consumer name - '|| cname);
cr:=&cr; pr:=&pr;
nou:=(cr-pr);
dbms_output.put_line('Number of units - '|| nou);
if nou<=100 then charges:=nou*3.5;
dbms_output.put_line('charges for fisrt 100 units - '|| charges);
else
charges:=(100*3.5)+(nou-100)*7.5;
end if;
dbms_output.put_line('charges for next all units - '|| charges);
end;

Date:- 10-feb-2023
use hr
set serveroutput on

// using multiple if ....if condition then


// elsif then
// else
// end if;

declare
fno number(10); sno number(10);
begin
fno:=&fno;
sno:=&sno;
dbms_output.put_line(' number1 ' || fno || ' number1 ' || sno);
if fno>sno then
dbms_output.put_line('first number is greater than second');
elsif sno>fno then
dbms_output.put_line('second number is greater than first');
else
dbms_output.put_line('both are equal');
end if;
end;

// multipl if else .......Salesman and commission

declare
sno number(5); sname varchar2(20); salary number(10); samt number(20); comm
number(20); income number(20);
begin
sno:=442;
sname:='Bosco';
samt:=&samt;
salary:=30000;
dbms_output.put_line('Number of salesman ' || sno);
dbms_output.put_line('Name of salesman ' || sname);
dbms_output.put_line('Sales amount ' || samt);
dbms_output.put_line('Salary of salesman ' || salary);
if samt<=1500 then
comm:=samt*0.05;
dbms_output.put_line('Sales amount range 1-1500 then commission :- ' || comm);
elsif samt<=3000 then
comm:=samt*0.07;
dbms_output.put_line('Sales amount range 1501-3000 then commission :- ' || comm);
elsif samt<=4500 then
comm:=samt*0.10;
dbms_output.put_line('Sales amount range 3001-4500 then commission :- ' || comm);
else
comm:=samt*0.12;
dbms_output.put_line('Sales amount range 4501 and above then commission :- ' ||
comm);
end if;
income:=salary+comm;
dbms_output.put_line('Total income :- ' || income);
end;

// multiple if else .....Item purchasing


declare
ino number(10); iname varchar2(20); poi number(20); qoi number(20); tbill
number(20); dbill number(20);
begin
ino:=11;
iname:='';
poi:=&poi; qoi:=&qoi;
dbms_output.put_line('Number of item :- ' || ino);
dbms_output.put_line('Name of item :- ' || iname);
dbms_output.put_line('Price of item :- ' || poi);
dbms_output.put_line('Quantity of item :- ' || qoi);
tbill:=poi*qoi;
dbms_output.put_line('Total bill ' || tbill);
if tbill<=3000 then
dbill:=tbill-(0.02*tbill);
dbms_output.put_line('2% Discounted bill ' || dbill);
elsif tbill<=6000 then
dbill:=tbill-(0.03*tbill);
dbms_output.put_line('3% Discounted bill ' || dbill);
else
dbill:=tbill-(0.05*tbill);
dbms_output.put_line('5% Discounted bill ' || dbill);
end if;
end;

// multiple if else ....wages

declare
wno number(3); wname varchar2(20); wh number(10); wages number(20);
begin
wno:=89;
wname:='Ramakant';
wh:=&wh;
dbms_output.put_line('Worker number ' || wno);
dbms_output.put_line('Worker name ' ||wname);
if wh<=8 then wages:=wh*50;
dbms_output.put_line('Wages for first 8 hours ' ||wages);
elsif wh<=10 then wages:=(8*50)+((wh-8)*70);
dbms_output.put_line('Wages for next 2 hours ' ||wages);
else
wages:=(8*50)+(2*70)+((wh-10)*90);
dbms_output.put_line('Wages for next all hours ' ||wages);
end if;
end;

// multiple if.............. electricity bill


declare
mno number(3);cname varchar2(10); cr number(10); pr number(10); nou number(10);
charges number(10);
begin
mno:=11;
cname:='Debolina';
dbms_output.put_line('Meter no. - '|| mno);
dbms_output.put_line('consumer name - '|| cname);
cr:=&cr; pr:=&pr;
nou:=(cr-pr);
dbms_output.put_line('Number of units - '|| nou);
if nou<=100 then charges:=nou*3.5;
elsif nou<=350 then charges:=(100*3.5)+(nou-100)*5;
else
charges:=(100*3.5)+(250*5)+(nou-350)*7.5;
end if;
dbms_output.put_line('charges for units - '|| charges);
end;

Date:-14-feb-2023
set serveroutput on

// use of case insted of use else if many time

declare
rno number(2); sname varchar(20);
begin
rno:=&rno;
sname:= case rno
when 1 then 'One'
when 2 then 'Two'
when 3 then 'Three'
else
'Out of range'
end;
dbms_output.put_line('Spelling ' || sname);
end;

// calender case check program

declare
mno number(2); mname varchar2(20);
begin
mno:=&mno;
mname:= case mno
when 1 then 'Thiry one'
when 4 then 'Thity'
when 2 then 'Twenty eight'
else
' Out of range'
end;
dbms_output.put_line('Number of days in given month :-' || mname);
end;

// To check Vowels using case

declare
val varchar(10); alpha varchar(5);
begin
alpha:='&alpha';
val:= case alpha
when 'a' then 'Vovels..'
when 'e' then 'Vovels..'
when 'i' then 'Vovels..'
when 'o' then 'Vovels..'
when 'u' then 'Vovels..'
else
'Consonants....'
end;
dbms_output.put_line(' Enter Charater is Vowels :' || val);
end;

Date:=15-feb-2023
set serveroutput on

// mod function use to find reminder in division


//floor function use to find round up value from intiger part on lower side
// i.e. if 8/3=2.6666667 so round up value is 3 but using floor we get value 2
only i.e lowwrside
//

declare
nos number(2);
div number(2);
r number(4);
q number(2);
begin
nos:=&nos;
div:=&div;
q:=floor(nos/div);
r:=mod(nos,div);
dbms_output.put_line(' Quotient ....' || q);
dbms_output.put_line(' Remainder ....' || r);
end;

Date:= 21-feb-2023

set serveroutput on

// mod function use to find reminder in division


//floor function use to find round up value from intiger part on lower side
// i.e. if 8/3=2.6666667 so round up value is 3 but using floor we get value 2
only i.e lowwrside
//

declare
nos number(2);
div number(2);
r number(4);
q number(2);
begin
nos:=&nos;
div:=&div;
q:=floor(nos/div);
r:=mod(nos,div);
dbms_output.put_line(' Quotient ....' || q);
dbms_output.put_line(' Remainder ....' || r);
end;

declare
rno number(4); hindi number(4); eng number(4); maths number(4);
begin
rno:=&rno;
hindi:=&hindi; eng:=&eng; maths:=&maths;
if hindi>=35 and eng>=35 and maths>=35 then
dbms_output.put_line( ' Stident is pass ');
else
dbms_output.put_line( ' Fail ... ');
end if;
end;

declare
rno number(4); hindi number(4); eng number(4); maths number(4);
begin
rno:=&rno;
hindi:=&hindi; eng:=&eng; maths:=&maths;
if hindi<35 or eng<35 or maths<35 then
dbms_output.put_line( ' Stident is fail ');
else
dbms_output.put_line( ' pass ... ');
end if;
end;

// 3 number in acending

declare
a number(3); b number(3); c number(3);
begin
a:=&a; b:=&b; c:=&c;
if a<b and b<=c then
dbms_output.put_line(' a<b<c......' || a || ' ....'|| b || '........' || c );
elsif a<c and c<=b then
dbms_output.put_line(' a<c<b......' || a || ' ....'|| c|| '........' || b );
elsif b<a and a<=c then
dbms_output.put_line(' b<a<c......' || b || ' ....'|| a || '........' ||c );
elsif b<c and c<=a then
dbms_output.put_line(' b<c<a......' || b || ' ....'|| c || '........' ||a );
elsif c<a and a<=b then
dbms_output.put_line(' c<a<b......' || c || ' ....'|| a || '........' ||b );
else
dbms_output.put_line(' c<b<a......' || c || ' ....'|| b || '........' ||a );
end if;
end;

// practical and theory marks

declare
rno number(2); sname varchar2(10); mt number(4); mp number(4); tot number(5);
begin
rno:=&rno;
sname:='&sname';
dbms_output.put_line(' Roll no. ...' || rno);
dbms_output.put_line(' Student name ...' || sname);
mt:=&mt; mp:=&mp; tot:=mt+mp;
if mt>=15 and mp>=15 and tot>=40 then
dbms_output.put_line(' student is pass ..');
elsif mt>=15 and mp>=15 and tot=39 then
dbms_output.put_line('student is promoted ..');
else
dbms_output.put_line(' Fail ..');
end if;
end;

Date - 22 feb 2023

set serveroutput on

// or operator in if else

// To check Vowel or not

declare
alpha varchar2(3);
begin
alpha:='&alpha';
if alpha = 'a' or alpha = 'e' or alpha = 'i' or alpha = 'o' or alpha = 'u' then
dbms_output.put_line(' It is Vowel ... ');
else
dbms_output.put_line(' It is Consonant ... ');
end if;
end;

// To check even and odd

declare
n number(3);
begin
n:=&n;
if n = 1 or n = 3 or n = 5 or n = 7 or n = 9 then
dbms_output.put_line(' Given no is odd number ....');
elsif n = 2 or n = 4 or n = 6 or n = 8 or n = 10 then
dbms_output.put_line(' Given no is even number ....');
else
dbms_output.put_line(' Given no out of range ....');
end if;
end;

// To accept month no. and year and display no. of days in that month
// leap year logic if year divisible by 4 or divisible by 400 but not divisible by
100 then its leap year

declare
mno number(2); yr number(5); nod number(3);
begin
mno:=&mno;
yr:=&yr;
if mno=2 then
if mod(yr,4)=0 and mod(yr,100)<>0 or mod(yr,400)=0 then
dbms_output.put_line('Number of days is 29 (leap year ).....');
else
dbms_output.put_line('Number of days is 28.....');
end if;
elsif mno=4 or mno=6 or mno=9 or mno=11 then
dbms_output.put_line('Number of days is 30......');
elsif mno=1 or mno=3 or mno=5 or mno=7 or mno=8 or mno=10 or mno=12 then
dbms_output.put_line('Number of days is 31..');
else
dbms_output.put_line(' Given no out of range ....');
end if;
end;

// To check divisibility using mod function and not equal to show by this <>
symbol
declare
mno number(2); nod number(3);
begin
mno:=&mno;
if mod(mno,2)=0 then
dbms_output.put_line('its divisible by 2 ');
elsif mod(mno,2)<>0 then
dbms_output.put_line('its not divisible by 2 ');
end if;
end;

Date- 23-feb-2023
set serveroutput on

// Nested if ....worker and wages

// wh= working hours // wt = worker type


declare
wno number(2);
wname varchar2(10);
wh number(3);
wt varchar2(10);
wages number(5);
begin
wno:=11;
wname:='John';
wt:='&wt';
wh:=&wh;
if wt='t' then
if wh<=8 then wages:=wh*20;
dbms_output.put_line(' Wages for 8 hours ....'|| wages);
elsif wh<=10 then wages:=(8*20)+((wh-8)*30);
dbms_output.put_line(' Wages for for next 2 hours ....'|| wages);
else
wages:=(8*20)+(2*30)+((wh-10)*50);
dbms_output.put_line(' Wages for next all hours ....'|| wages);
end if;
elsif wt='p' then
if wh<=8 then wages:=wh*70;
dbms_output.put_line(' Wages for 8 hours ....'|| wages);
elsif wh<=10 then wages:=(8*70)+((wh-8)*80);
dbms_output.put_line(' Wages for next 2 hours ....'|| wages);
else
wages:=(8*70)+(2*80)+((wh-10)*90);
dbms_output.put_line(' Wages for next all hours ....'|| wages);
end if;
else
dbms_output.put_line(' Wrong working type ....'|| wt);
end if;
end;

// Nested if Meter reading


// lt = line type, nu = number of unit ,cr = current reading ,pr = previous
reading,

declare
mn number(3); lt varchar2(4); cr number(10); pr number(10); nu number(10); chrg
number(10);
begin
mn:=71;
lt:='&lt';
cr:=&cr; pr:=&pr;
nu:=(cr-pr);
dbms_output.put_line('Net units.....' || nu);
if lt='d' then
if nu<=100 then chrg:=nu*2.5;
dbms_output.put_line('Charges for first 100 units .....' || chrg);
elsif nu<=350 then chrg:=(100*2.5)+((nu-100)*4);
dbms_output.put_line('Charges for next 250 units .....' || chrg);
else
chrg:=(100*2.5)+(250*4)+((nu-350)*5.5);
dbms_output.put_line('Charges for next all units .....' || chrg);
end if;
elsif lt='c' then
if nu<100 then chrg:=nu*3.5;
dbms_output.put_line('CCharges for first 100 units .....' || chrg);
elsif nu<=350 then chrg:=(100*3.5)+(nu-100)*5;
dbms_output.put_line('CCharges for next 250 units .....' || chrg);
else
chrg:=(100*3.5)+(250*5)+((nu-350)*7.5);
dbms_output.put_line('Charges for next all units .....' || chrg);
end if;
elsif lt='i' then
if nu<=100 then chrg:=100*4;
dbms_output.put_line('Charges for first 100 units .....' || chrg);
elsif nu<=350 then chrg:=(100*4)+((nu-100)*6);
dbms_output.put_line('Charges for next 250 units .....' || chrg);
else
chrg:=(100*4)+(250*6)+((nu-350)*8);
dbms_output.put_line('Charges for next all units .....' || chrg);
end if;
else
dbms_output.put_line('Invalid Line Type .....' || lt);
end if;
end;

===================================================================================
====================

// Date:=1-march-2023
set serveroutput on

// Various type of loops


1] for loop
2] basic loop or exit loop

1] for loop

// for and loop


// syntax for variable 1..5 (range)loop
end loop;
here .. (two dots)indicate range
for a in 1..5 loop indicates how many times you want anything ,decide range
dbms_output.put_line('Hello'); this msg actually shows what we want to display
in loop

examples
1)

declare
a number(2);
begin
for a in 1..5 loop
dbms_output.put_line(a);
end loop;
end;
=========================================
2)
declare
a number(2);
begin
for a in 1..5 loop
dbms_output.put_line('a');
end loop;
end;
==============================================
3)

declare
a number(2);
begin
for a in 1..5 loop
dbms_output.put_line('Hello');
end loop;
end;

==============================================

declare
n number(2);
begin
for n in 1..3 loop
dbms_output.put_line('Welcome');
end loop;
end;
===================================================================================
=======================================
===================================================================================
========================================

2] basic loop or exit loop

// Diffrent method to write loop


syntex
loop
exit when condition
end loop

example 1)

declare
n number := 1; // variable declare
begin
loop // loop st
exit when n>5; // exit when condition
dbms_output.put_line('Welcome'); // condition
dbms_output.put_line('Welcome'); // Display condition
n:=n+1; // condition n=1 then 1=1=2 till 5 cause our condition is if
n>5 then exit
end loop;
end;

// To print 1 to 5 number
declare
n number := 1;
begin
loop
exit when n>5;
dbms_output.put_line(n);
n:=n+1;
end loop;
end;

// to print hello 5 times


declare
n number := 1;
begin
loop
exit when n>5;
dbms_output.put_line('Hello');
n:=n+1;
end loop;
end;

declare
n number := 1;
begin
loop
exit when n>5;
dbms_output.put_line('Hello');
n:=n+2;
end loop;
end;

===============================================
// to print even number between 2 to 20
declare
n number:=2;
begin
loop
exit when n>20;
dbms_output.put_line(n);
n:=n+2;
end loop;
end;

=========================================================
// To print even number as per user said value

declare
n number:=2;
i number:=1;
x number(2);
begin
x:=&x;
loop
exit when i>x;
dbms_output.put_line(n);
n:=n+2;
i:=i+1;
end loop;
end;
=============================================================================
// to print odd number between 1 to 20

declare
n number:=1;
begin
loop
exit when n>20;
dbms_output.put_line(n);
n:=n+2;
end loop;
end;

======================================================================

// To print odd number as per user value if user said 10 then print first 10 odd
number
// x for user taken number ,c for count ,n for logic

declare
n number:=1;
c number:=1;
x number(2);
begin
x:=&x;
loop
exit when c>x;
dbms_output.put_line(n);
n:=n+2;
c:=c+1;
end loop;
end;

==============================================================
// To print sum of 1 to 10 number

declare
n number:=1;
s number:=0;
begin
loop
exit when n>10;
dbms_output.put_line(n);
s:=s+n;
n:=n+1;
end loop;
dbms_output.put_line(s);
end;

// output message out side of end loop


==============================================================
declare
n number:=1;
s number:=0;
begin
loop
exit when n>10;
dbms_output.put_line(n);
s:=s+n;
n:=n+1;
dbms_output.put_line(s);
end loop;
end;

// output message within end loop

============================================================
// print number upto 5

declare
n number := 1;
begin
loop
exit when n>5;
dbms_output.put_line(n);
n:=n+1;
end loop;
end;
==============================================================
// take number from user and print upto that number

declare
n number := 1;
x number(2);
begin
x:=&x;
loop
exit when n>x;
dbms_output.put_line(n);
n:=n+1;
end loop;
end;

==================================================================================
Date:=2-mar-2023

set serveroutput on

// To print 1 to 10 in reverse

declare
n number := 10;
begin
loop
exit when n<1;
dbms_output.put_line(n);
n:=n-1;
end loop;
end;
=============================================================================
// To print reverse take value from user

declare
x number(2);
begin
x:=&x;
loop
exit when x<1;
dbms_output.put_line(x);
x:=x-1;
end loop;
end;

===================================================================

// Factorial To print multiplication of number

Declare
n number(2);
f number:=1;
begin
n:=&n;
loop
exit when n<1;
dbms_output.put_line(n);
f:=f*n;
n:=n-1;
end loop;
dbms_output.put_line(f);
end;

// Factorial

declare
n number(2);
f number:=1;
i number:=1;
begin
n:=&n;
loop
exit when i>n;
dbms_output.put_line(i);
f:=f*i;
i:=i+1;
end loop;
dbms_output.put_line(f);
end;

===================================================================================
==================
Date:=8-mar-2023
set serveroutput on

// To print 1 to 10 in reverse

declare
n number := 10;
begin
loop
exit when n<1;
dbms_output.put_line(n);
n:=n-1;
end loop;
end;
=============================================================================
// To print reverse take value from user

declare
x number(2);
begin
x:=&x;
loop
exit when x<1;
dbms_output.put_line(x);
x:=x-1;
end loop;
end;

===================================================================

// Factorial To print multiplication of number

Declare
n number(2);
f number:=1;
begin
n:=&n;
loop
exit when n<1;
dbms_output.put_line(n);
f:=f*n;
n:=n-1;
end loop;
dbms_output.put_line(f);
end;

// Factorial

declare
n number(2);
f number:=1;
i number:=1;
begin
n:=&n;
loop
exit when i>n;
dbms_output.put_line(i);
f:=f*i;
i:=i+1;
end loop;
dbms_output.put_line(f);
end;

// To create table of number given by user

declare
n number(3);
i number:=1;
t number(5);
begin
n:=&n;
loop
exit when i>10;
t:=n*i;
dbms_output.put_line(n || '*' || i || '=' || t);
i:=i+1;
end loop;
end;

// To print first 20 elements of fibonacci series


// here we print first two number as fn and sn as 0 and 1

declare
fn number(5):=0;
sn number(5):=1;
t number(5);
c number(5):=3;
begin
dbms_output.put_line(fn);
dbms_output.put_line(sn);
loop
exit when c>20;
t:=fn+sn;
dbms_output.put_line(t);
c:=c+1;
fn:=sn;
sn:=t;
end loop;
end;

// second method of fibonnaci direct take value -1+1=0 and so on

declare
fn number(5):=-1;
sn number(5):=1;
t number(5);
c number(5):=1;
begin
loop
exit when c>20;
t:=fn+sn;
dbms_output.put_line(t);
c:=c+1;
fn:=sn;
sn:=t;
end loop;
end;

You might also like