Practical of Function and Procedure Bases pl/sql
PRACTICAL-9
Aim :- FUNCTION AND PROCEDURE BASED PL/SQL PROGRAMS
1. Create a
Function which will work as addition e.g select ADDITION(10,10) from dual;
Ans:- create or replace function addition(a1
in number,a2 in number)
return number is
ans number;
begin
ans:=a1+a2;
return ans;
end;
/
Select
addition(3,4) from dual;
Ans:- create or replace function right1(ch in
varchar2,num number)
return varchar2 is
str varchar2(30);
l number(10);
begin
select length(ch) into l from dual;
select substr(ch,l-num+1,num) into
str from dual;
return str;
end;
/
select right1('UVPCE',3) from dual;
3. Create a
Function that is Work as Calculator. Use Operator like ‘+’,’-’,’*’,’/’.
Ans:- create or replace function calculator(a
in number,b in number,d in varchar2)
return number is
ans number(30);
begin
if d='+' then
ans:=a+b;
return ans;
elsif d='-' then
ans:=a-b;
return ans;
elsif d='*' then
ans:=a*b;
return ans;
elsif d='/' then
ans:=trunk(a,b);
return ans;
else
dbms_output.put_line('invalid
operator');
end if;
end;
/
select calculator(10,10,'*') from dual;
4. Create a
Procedure that update the salary of employee Using following formula :
Sal+(Percentage/100) Pass two parameter values : Name & Percentage
Table: Salary
|
|||||||||||||||
create table salarys(emp_no number(10),name
varchar2(20),salary
number(10));
insert
into salarys values(01,'riya',8000);
insert
into salarys values(02,'rima',8000);
insert
into salarys values(03,'ajay',8000);
insert
into salarys values(04,'raj',8000);
|
Ans :- create or replace procedure updt is
N
varchar2(20);
percentage
number(10);
sal
number(20);
p
number(20);
begin
N:='&N';
percentage:=&percentage;
select
salary into sal from salary where name=N;
p:=sal+sal*trunc(percentage,100);
update
salary set salary=p where name=N;
end;
/
select right1('UVPCE',3) from dual;
Comments
Post a Comment