Scribd
Upload
7 views18 pages

PL/SQL Basic Programs and Examples

The document contains various PL/SQL code blocks demonstrating basic programming concepts such as loops, conditionals, and cursor operations. It includes examples for printing numbers, calculating Fibonacci series, handling user input, and performing database operations like insert, update, and delete. Additionally, it covers exception handling techniques for managing errors during database interactions.

Uploaded by

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

PL/SQL Basic Programs and Examples

The document contains various PL/SQL code blocks demonstrating basic programming concepts such as loops, conditionals, and cursor operations. It includes examples for printing numbers, calculating Fibonacci series, handling user input, and performing database operations like insert, update, and delete. Additionally, it covers exception handling techniques for managing errors during database interactions.

Uploaded by

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

Pl/sql Blocks

(Basic Programs)

wap that print number when loop reach to value > 10.
___________________________________________________________________________

declare
i number :=1;
begin
loop
dbms_output.put_line(i);
i :=i+2;
exit when i > 10;
end loop;
dbms_output.put_line(‘After end of loop:’||i);
end;
/
//wap to display Fibonacci series.
--------------------------------------------------------------------------------------------------------------------------

declare
f1 number(3);
f2 number(3);
f3 number(3);
n number(3);
begin
f1 :=0;
f2 :=1;
f3 :=0;
n :=1;
while n <=10
loop
dbms_output.put_line(f3);
f1 :=f2;
f2 :=f3;
f3 :=f1+f2;
n := n+1;
end loop;
end;/

By: Dr. Megha D Rana


Vivekanand College for BCA
//wap that print number when loop reach to value > 10.

declare
i number :=1;

begin
loop
i :=i+2;
exit when i > 10;
end loop;
dbms_output.put_line(i);
end;
/

//wap that print 1 to 10.

declare
i number :=1;
begin
while(i<=10)
loop
dbms_output.put_line(i);
i := i+1;
end loop;
end;

//wap that print odd numbers between 1 to 10.

declare
i number :=1;

By: Dr. Megha D Rana


Vivekanand College for BCA
begin
loop
dbms_output.put_line(i);
i :=i+2;
exit when i > 10;
end loop;
end;
/
//wap that print your name five times.

declare
i number;
begin
i :=1;
while (i <= 5)
loop
dbms_output.put_line('Dipika');
i :=i+1;
end loop;
end;
/
wap that print number is odd or even
declare
no number;
begin
no:=&no;
if mod(no,2)=0 then
dbms_output.put_line(' no is even');
else
dbms_output.put_line(' no is odd');
end if;
end;
/

By: Dr. Megha D Rana


Vivekanand College for BCA
____________________________________________________________________
//wap that print ename and salary if choice is yes else error message.

______________________________________________________________________
declare
name varchar(15);
salary number(7);
choice char(1);
Begin
name := 'Rahul Dravid';
Salary := 25000;
choice :='y';
if choice = 'y' then
dbms_output.put_line(name ||' ' || salary);
else
dbms_output.put_line('no data found');
end if;
end;
/
_____________________________________________________________________
//wap that print 1 to 10.
____________________________________________________________________
declare
i number :=1;
begin
while(i<=10)
loop
dbms_output.put_line(i);
i := i+1;
end loop;
end;

By: Dr. Megha D Rana


Vivekanand College for BCA
_______________________________________________________________________
//wap that print odd numbers between 1 to 10.

_______________________________________________________________________
declare
i number :=1;
begin
loop
dbms_output.put_line(i);
i :=i+2;
exit when i > 10;
end loop;
end;
/

//wap that print your name five times.


____________________________________________________________________
declare
i number;
begin
i :=1;
while (i <= 5)
loop
dbms_output.put_line('Dipika');
i :=i+1;
end loop;
end;
/
___________________________________________________________________________
wap that print number is odd or even
___________________________________________________________________________

By: Dr. Megha D Rana


Vivekanand College for BCA
declare
no number;

begin
no:=&no;
if mod(no,2)=0 then
dbms_output.put_line(' no is even');
else
dbms_output.put_line(' no is odd');
end if;
end;
/
_______________________________________________________________________
wap that accept eid from user and print salary.
Declare
E_name [Link]%type;
E_city [Link]%type;
sal [Link]%type;
begin
select ename,city,salary into E_name,E_city,sal from emp where eid='&eid';
dbms_output.put_line(E_name);
dbms_output.put_line(E_city);
dbms_output.put_line(sal);
end;
/
_____________________________________________________________________
declare
x number;
y number;
z number;
begin

By: Dr. Megha D Rana


Vivekanand College for BCA
x:=&x;
y:=&y;

z:=x+y;
dbms_output.put_line('value of z is:'||z);
end;
/
______________________________________________________________________
declare
sno number(5);
sname varchar2(10);
begin
sno:=&sno;
sname:='&sname';
dbms_output.put_line('stud rollno is:'||sno);
dbms_output.put_line('stud name is:'||sname);
end;
/

Create student table here. Student(s_id,sname,sub1,sub2,sub3,total)


declare
s_id varchar2(5);
sname varchar2(10);
sub1 number(5);
sub2 number(5);
sub3 number(5);
total number(5);
begin
s_id:='&s_id';
sname:='&sname';
sub1:=&sub1;
sub2:=&sub2;

By: Dr. Megha D Rana


Vivekanand College for BCA
sub3:=&sub3;
total:=sub1+sub2+sub3;

insert into student values (s_id,sname,sub1,sub2,sub3,total);


end;

Oracle Transaction
___________________________________________________________________________
DECLARE
id emp_mst.eid%TYPE;
BEGIN
SAVEPOINT s1;
UPDATE emp_mst SET eid=109
WHERE ename = 'sachin';
savepoint s2;
update emp_mst set salary=salary+500
where eid=109;
ROLLBACK to savepoint s2;
END;

/
________________________________________________________________________
declare
bal number(10);
begin
insert into emp values(&eid,'&ename','&city',&salary);
update emp set salary=salary+500;
savepoint s1;
insert into emp values(&eid,'&ename','&city',&salary);
update emp set salary=salary+1000 where eid=1001;
select sum(salary) into bal from emp;

By: Dr. Megha D Rana


Vivekanand College for BCA
if(bal>20000) then
rollback to savepoint s1;

end if;
commit;
end;
/

Cursor
__________________________________________________________________________
Consider the table emp. Write an pl/sql block to accept e_id from user and update emp
name. Display appropriate message using sql%found based on existence of emp. Else
display emp does not exists message.
___________________________________________________________________________
set serveroutput on;

begin
update emp_mst set ename=’&ename’ where eid=&eid;
if SQL%FOUND then
dbms_output.put_line('record update sucessfully');
else
dbms_output.put_line('id is not found');
end if;
end;
/

__________________________________________________________________________
Consider the table emp. Write an pl/sql that update emp salary by 1000. Display
appropriate message using sql%found based on updation of emp salary.
___________________________________________________________________________

DECLARE
var_rows number(5);
BEGIN
UPDATE emp_mst
SET salary = salary + 1000
Where city=’bombay’;
IF SQL%NOTFOUND then
dbms_output.put_line('None of the salaries where updated');
ELSIF SQL%FOUND then
var_rows := SQL%ROWCOUNT;
dbms_output.put_line('Salaries for ' || var_rows || 'employees are updated');
END IF;
END;
/

By: Dr. Megha D Rana


Vivekanand College for BCA
___________________________________________________________________________
Write an pl/sql block that accept dept_id from user. calculate and returns the sum of
salary of department.
___________________________________________________________________________

declare
msal number;
begin
select sum(salary) into msal from emp_mst; where did=&did;

dbms_output.put_line(msal);
end;
/

Write an pl/sql block that accept emp_id from user and perform delete operation on it.
Generate the number of rows affected by delete operation.

declare
row_del number;
begin
delete from emp_mst where eid =’&eid';

row_del := SQL%ROWCOUNT;
dbms_output.put_line(row_del);
end;
/

Write an pl/sql block that accept city from user and display information of employee who
lived in that city.

Declare
-- location varchar2(10);
cursor cur (location varchar) is select * from emp_mst where city=location;
begin
location :=’&location’;
open cur;
for r1 in cur(location)
loop
dbms_out.put_line([Link]||[Link]||[Link]);
end loop;
close cur;
end;
/

Declare
Cursor c1 is select * from emp_mst where city =’&city’;
Begin

By: Dr. Megha D Rana


Vivekanand College for BCA
For r1 in c1
Loop
Dbms_output.put_line([Link]||[Link]||[Link]||[Link]);
End loop;
End;
/

___________________________________________________________________________
Consider the emp table. Write an pl/sql block using explicit cursor that will display
emp_id, name and salary.
___________________________________________________________________________

DECLARE

e_id emp_mst.eid%type;
e_name emp_mst.ename%type;
sal emp_mst.salary%type;
CURSOR cur_emp is
SELECT eid, ename, salary FROM emp_mst;

BEGIN
OPEN cur_emp;
LOOP
FETCH cur_emp into e_id, e_name, sal;
EXIT WHEN cur_emp%notfound;
dbms_output.put_line(e_id || ' ' || e_name || ' ' || sal);
END LOOP;
CLOSE cur_emp;
END;
/

Write an pl/sql block which accept dept_id from user display department information.
___________________________________________________________________________
declare
cursor c1 is select * from emp_mst
where d_id='&d_id';
begin
for r1 in c1
loop
dbms_output.put_line(r1.e_id || ' ' ||[Link] || ' ' ||r1. d_id||' '||[Link]);
end loop;
end;
/

declare
cursor c1 is select * from emp_mst

By: Dr. Megha D Rana


Vivekanand College for BCA
where eid=&eid;
begin
for r1 in c1
loop
dbms_output.put_line([Link] || ' ' ||[Link] || ' '||[Link]);
end loop;
end;
/

Write pl/sql block for display employee record whose salary is more than 15000 using
emplicit cursor (%found,%isopen)

declare
cursor x is select eid,salary from emp_mst where salary>10000;
veno emp_mst.eid%type;
vsal emp_mst.salary%type;
begin
open x;
if x%isopen then
loop
fetch x into veno,vsal;
exit when x%notfound;
dbms_output.put_line(' eid'||veno||' salary'||vsal);
end loop;
end if;
close x;
end;
/
___________________________________________________________________________
Write pl/sql block for display employee record whose salary is more than 5000 and also
increase salary with 500 , using emplicit cursor (%rowcount)

declare
cursor x is select eid,salary from emp_mst where salary>5000;
veno emp_mst.eid%type;
vsal emp_mst.salary%type;
y number(4);
begin
open x;
if x%isopen then
loop
fetch x into veno,vsal;
exit when x%notfound;
update emp_mst set salary=salary+500 where eid=veno;
y:=x%rowcount;
if y>0 then
dbms_output.put_line('eid '||veno||' salary'||vsal);

By: Dr. Megha D Rana


Vivekanand College for BCA
end if;
end loop;
end if;
end;
/

Write pl/sql block that accept employee id from user and display its records.

declare
cursor c1 is select * from emp_mst
where eid=&eid;
begin
for r1 in c1
loop
dbms_output.put_line([Link] || ' ' ||[Link] || ' ' ||r1. city||' '||[Link]);
end loop;
end;
/

Exception Handling
___________________________________________________________________________
wapsb which insert record in emp table. if user insert duplicate record then display
appropriate message.
___________________________________________________________________________
begin

insert into emp_mst values('&eid','&ename','&city',&salary);


dbms_output.put_line('Record inserted sucessfully');
exception
when dup_val_on_index then
dbms_output.put_line('entered duplicate value');
end;
/
___________________________________________________________________________
/* wapsb that accept eid and print salary of that emp. if eid is not available in the table
then display proper message */
___________________________________________________________________________
declare

By: Dr. Megha D Rana


Vivekanand College for BCA
emp_id number := &emp_id;
sal number(7);

begin
select salary into sal from emp_mst where eid=emp_id;
dbms_output.put_line(sal);
exception
when no_data_found then
dbms_output.put_line('invalid eid');
end;
/
_______________________________________________________________________
/* wapsb that raises an exception when Select statment return too many rows */
__________________________________________________________________________
DECLARE
tmp varchar(20);
BEGIN
SELECT ename into tmp from emp_mst;
dbms_output.put_line(tmp);
EXCEPTION
WHEN too_many_rows THEN
dbms_output.put_line('error trying to SELECT too many rows');
end;
/
___________________________________________________________________________
/* wapsb that raises an exception when user input invalid datatype */
_________________________________________________________________________
DECLARE
tmp number;
BEGIN
SELECT ename into tmp from emp_mst where ename='&ename';

By: Dr. Megha D Rana


Vivekanand College for BCA
dbms_output.put_line('the employee name is '||tmp);
EXCEPTION

WHEN value_error THEN


dbms_output.put_line('Data type mis match,Change data type of tmp');
END;
/
___________________________________________________________________________
_
/* wapsb that raises an exception when number is divided by zero */
__________________________________________________________________________
DECLARE
a int:=10;
b int:=0;

ans int;
BEGIN
ans:=a/b;
dbms_output.put_line('the result is'||ans);
Exception
WHEN zero_divide THEN
dbms_output.put_line('divided by zero please check the values');
dbms_output.put_line('the value of a is '||a);
dbms_output.put_line('the value of b is '||b);
END;

/
__________________________________________________________________________
/* wapsb that generate user define exception while inserting record in tmp table.
if salary is less than 5000 then do not allow to insert into table and raised error message.
*/
___________________________________________________________________________
create table tmp
(eno number(5) primary key,

By: Dr. Megha D Rana


Vivekanand College for BCA
sal number(7));
______________

declare
less_sal exception;
eno number;
sal number;
begin
eno := &eno;
sal := &sal;
if sal < 5000 then
raise less_sal;
else
insert into tmp values(eno,sal);
end if;
exception
when less_sal then
dbms_output.put_line('balance must be greater than 5000');
end;
/
_________________________________________________________________________
/*wapsb that accept eid from user if enter eid is invalid it raised an exception */
__________________________________________________________________________
DECLARE
eid [Link]%type := &eid;
salary [Link]%type;
exp_invalid EXCEPTION;
BEGIN
IF eid <= 0 THEN
RAISE exp_invalid;
ELSE

By: Dr. Megha D Rana


Vivekanand College for BCA
SELECT sal into salary FROM tmp WHERE eno = eid;
DBMS_OUTPUT.PUT_LINE ('No: '|| eid);

DBMS_OUTPUT.PUT_LINE ('sal: ' || salary);


END IF;
EXCEPTION
WHEN exp_invalid THEN
dbms_output.put_line('ID must be greater than zero!');
WHEN no_data_found THEN
dbms_output.put_line('No emp found');
WHEN others THEN
dbms_output.put_line('Error!');
END;
/
_________________________________________________________________________
declare
dup EXCEPTION;
PRAGMA EXCEPTION_INIT(dup,-00001);
begin
insert into tmp values(&eno,&sal);
dbms_output.put_line('Record inserted sucessfully');
exception
when dup then
dbms_output.put_line('entered duplicate value');
end;
/
_________________________________________________________________________
/* wapsb that allow to insert value in emp table. if user enter wrong data generate proper
error message using pragma exception */
___________________________________________________________________________
declare

By: Dr. Megha D Rana


Vivekanand College for BCA
invalid_record_level exception;
pragma exception_init(invalid_record_level,-2290);

begin
insert into emp values('&eid','&ename','&city','&did',&salary,&ph_no);
exception when invalid_record_level then
dbms_output.put_line('Record inserted is invalid');
end;
/

Function and Procedure

create or replace procedure x is


begin
dbms_output.put_line('This is an example of procedure');
end;
/
execute x;
______________________________________________________________________
create or replace function y return varchar2 is
begin
return ('this is an example of function');
end;
/
select y from dual;
_______________________________________________________________________
write procedure that accept eid and return its detils.
________________________________________________________________________
create or replace procedure pro_emp(peno IN varchar) is
eno varchar(5);
sal number(20,2);
begin

By: Dr. Megha D Rana


Vivekanand College for BCA

You might also like