PL-SQL Programs…

· Program 1:-

Write a PL/SQL block to find the maximum number from given three numbers.

declare

a number;

b number;

c number;

begin

a:=&a;

b:=&b;

c:=&c;

if (a>b and a>c) then

dbms_output.put_line('a is maximum ' || a);

elsif (b>a and b>c) then

dbms_output.put_line('b is maximum ' || b);

else

dbms_output.put_line('c is maximum ' || c);

end if;

end;

· Program 2:-

Write a PL/SQL block to find the sum of first 100 natural nos.

declare

a number:=0;

begin

for i in 1..100

loop

a:=a+i;

end loop;

dbms_output.put_line('The sum of 100 natural nos is = '||a);

end;

· Program 3:-

Write a PL/SQL block to find the sum of first 100 odd nos. and even nos)

declare

odd number:=0;

even number:=0;

i number;

begin

for i in 1..100

loop

if(i mod 2 = 0) then

even:=even+i;

else

odd:=odd+i;

end if;

end loop;

dbms_output.put_line('The Sum of 100 even nos is ' || even);

dbms_output.put_line('The Sum of 100 odd nos is ' || odd);

end;

· Program 4:-

Write a PL/SQL block to display the Information of given student on following table Stud (sno, sname, address, city).

è Table Creation…

create table stud(

sno number primary key,

sname char(15),

addr varchar(30),

city char(15));

insert into stud values(1,'hiral','2,krishna society','Mehsana.');

insert into stud values(2,'pinky','4,Kalyaneshwer society','Mehsana.');

insert into stud values(3,'Dhruvi','24,Pushpavati society','Mehsana');

è Program…

declare

no number;

n number;

name char(15);

add varchar(50);

c char(15);

begin

n:=&n;

select sno,sname,addr,city into no,name,add,c from stud where sno=n;

dbms_output.put_line('The Sno is ' || no);

dbms_output.put_line('The Sname is ' || name);

dbms_output.put_line('The address is ' || add);

dbms_output.put_line('The city is ' || c);

end;

· Program 5:-

Write a PL/SQL block for preparing a Net Salary, given employee on following table

Emp (eno, ename, address, city)

Salary (eno, basic, da, hra, it)

Net_Salary (eno, total_allowance, total_deduction, netpay)

Notes : D.A. = 59% of basic , H.R.A. = 500, I.T. = 2% of basic

Total_Allowance = Basic + D.A. + H.R.A., Total_Deduction = I.T.

Netpay = Total_Allowance – Total_Deduction.

è Table Creation…

create table emp(

eno number primary key,

ename char(15),

addr varchar(30),

city char(15));

insert into emp values(1,'hiral','2,krishna society','Mehsana.');

insert into emp values(2,'pinky','4,Kalyaneshwer society','Mehsana.');

insert into emp values(3,'Dhruvi','24,Pushpavati society','Mehsana');

create table salary(

eno number references emp,

basic number(10,2),

da number(10,2) default NULL,

hra number(10,2) default 500,

it number(10,2) default NULL);

insert into salary(eno,basic) values(1,20000);

insert into salary(eno,basic) values(2,30000);

insert into salary(eno,basic) values(3,40000);

update salary set da=basic*0.59,it=basic*0.02;

create table netsal(

eno number references emp,

totalallow number(10,2),

totalded number(10,2),

netpay number(10,2));

è Program…

declare

no number;

n number;

d number(10,2);

b number(10,2);

h number(10,2);

i number(10,2);

ta number(10,2);

td number(10,2);

np number(10,2);

begin

n:=&n;

select eno,basic,da,hra,it into no,b,d,h,i from salary where eno=n;

ta:=b+d+h;

td:=i;

np:=ta-td;

insert into netsal values(no,ta,td,np);

end;

· Program 6:-

Write a PL/SQL block to raise the salary by 20% of given employee on following table.

Emp_Salary (eno, ename, city, salary)

è Table Creation…

eno number primary key,

ename char(15),

city char(15),

sal number(10,2));

insert into empsal values(1,'Hiral','Mehsana',20000);

insert into empsal values(2,'Pinkey','Mehsana',15000);

insert into empsal values(3,'Dhruvi','Mehsana',10000);

è Program…

declare

n number;

s number(10,2);

begin

n:=&n;

--select sal into s from empsal where eno=n;

update empsal set sal=sal+(sal*0.20) where eno=n;

end;

Write a function to check whether the given number is prime or not.

set serveroutput on

create or replace function prime(n in number) return varchar2 is

i number(10);

pr number(10);

res varchar2(10);

begin

pr:=1;

for i in 2..n/2 loop

if mod(n,i)=0 then

pr:=0;

end if;

end loop;

if pr=1 then

res:='prime';

else

res:='Not Prime';

end if;

return res;

end;

To run:= select prime(5) from dual;

12. Write a function to find the sum of digits of accepted nos.

Pra12;

set serveroutput on

create or replace function sm(no in number)return number as

i number(4);

sm number(4);

rem number(4);

num number(4);

begin

num:=no;

sm:=0;

i:=0;

while i<=num+1

loop

rem:=mod(num,10);

dbms_output.put_line(rem);

sm:=sm+rem;

num:=num/10;

i:=i+1;

end loop;

return sm;

end;

Pra12a;

set serveroutput on

declare

nos number(10);

ans number(3);

begin

nos:=&nos;

ans:=sm(nos);

dbms_output.put_line('Sum of Digit is '||ans);

end;

27. Write a Trigger on insert to convert the name into capital letters.

create or replace trigger t2 before insert or update on stud1 for each row

declare

oper varchar2(10);

begin

oper:=:new.sname;

:new.sname:=upper(oper);

end;

28. Write a Trigger to ckeck the Pincode is exactly six degit or not.

create table citypin(

pin number(10));

insert into citypin values(&pin);

SQL> select * from citypin;

PIN

---------

2434355

3435465

122

123235467

set serveroutput on

create or replace trigger tri28 before insert on citypin for each row

declare

tpin citypin.pin%type;

begin

tpin:=:new.pin;

if length(to_char(tpin))!=6 then

raise_application_error(-20002,'Your Pincode is Invalid');

end if;

end;

29. Write a trigger that check the mark is not zero or negative.

create table astd(mark number(3));

insert into astd values(&mark);

SQL> select * from astd;

MARK

---------

23

0

800

0

0

set serveroutput on

create or replace trigger tri29 before insert on astd for each row

declare

tmark astd.mark%type;

begin

tmark:=:new.mark;

if tmark>700 or tmark<0 then

raise_application_error(-20003,'Invalid Mark');

end if;

end;

30. Write a trigger that check the student_id must be start with ‘M’.

create table student_id(sno varchar2(10));

insert into student_id values(‘&sno’)

SQL> select * from student_id;

SNO

---------

1

2

3

4

set serveroutput on

create or replace trigger tri30 before insert on student_id for each row

declare

tsno student_id.sno%type;

begin

tsno:=:new.sno;

dbms_output.put_line(tsno);

if tsno not like 'M%' then

raise_application_error(-20005,'Invalid Number');

end if;

end;