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;

2. Write the Function to seprate out the  right character position of string .e.g. select RIGHT(‘DBMS-II’,3) 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

Emp_No
Name
Salary
01
Riya
8000
02
Rima
8000
03
Ajay
8000
04
Raj
8000

 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

Popular posts from this blog

SQL QUERY FOR PRIMARY AND FOREIGN KEY

SQL QUERY

SQL QUERY practical-5

Practicals of RollBack, Commit and Cursor programs

Programs Based on Exception Handling and Trigger.