19.Functions

Function : Function is a program which returns always only one value.
And it also stores in the datbase, so we can call or reuse any where

Syntax: CREATE OR REPLACE FUNCTION FUNCTION_NAME (PARAMETER1 DATA TYPE, PARAMETER2 DATA TYPE….) return return_type is

Advantages : Function can be called using select or any dml operations like isnert,update,delete etc.,

Example: Develop a function to return sum of any 2 nos given

create or replace function sumS2(a number, b number) return number is
c number;
begin
c:=a+b;
return(c);
end;

SQL> @ filename.sql
Function created

SQL> select sums2(10,20) from dual;

30

Example2 : Create a function to return the tax

create or replace function tax(p_eno number) return number is

ln_sal number;
ln_tax number;

begin
select sal into ln_sal from emp where eno=p_eno;
if ln_sal<10000 then
ln_tax:=0;
else
ln_tax:=2*ln_sal/100;
end if;

return(ln_tax);
end;

SQL> @ filename.sql
function created

using above function

SQL> select tax(115) from dual;

TAX(115)

   562

SQL> select tax(eno) from emp_new;

TAX(ENO)

   562
   284
   280
   500
   600
  1182

SQL> select eno,ename,sal, tax(eno) from emp_new;

   ENO ENAME             SAL   TAX(ENO)

---

   115 CHANDU          25500        562
   105 kumar           12200        284
   111 RAM             12000        280
   112 RAMESH          25000        500
   113 KRISHNA         30000        600
   114 SHEKAR          56500       1182

Using above function while updating records

SQL> alter table emp_new add (tax number);

Table altered.

SQL> select * from emp_new;

   ENO ENAME             SAL JOB        DOJ          DEPT_ID DAY               MGR        TAX

---

   115 CHANDU          25500 manager    10-MAR-12        222
   105 kumar           12200                             555
   111 RAM             12000 manager    12-DEC-14        222
   112 RAMESH          25000 B          29-DEC-14        333
   113 KRISHNA         30000 C          03-JAN-14        555
   114 SHEKAR          56500 manager    01-JAN-14        555

6 rows selected.

SQL> update emp_new set tax=tax(eno);

6 rows updated.

SQL> select * from emp_new;

   ENO ENAME             SAL JOB        DOJ          DEPT_ID DAY               MGR        TAX

---

   115 CHANDU          25500 manager    10-MAR-12        222                              562
   105 kumar           12200                             555                              284
   111 RAM             12000 manager    12-DEC-14        222                              280
   112 RAMESH          25000 B          29-DEC-14        333                              500
   113 KRISHNA         30000 C          03-JAN-14        555                              600
   114 SHEKAR          56500 manager    01-JAN-14        555                             1182

6 rows selected.

Task: Create a function to return the bank balance

Bank table: banks

Acno    Amt Type
----------------------
10001   10000   d
10001   5000    w
10002   25000   d
10001   1000    w
10002   14000   d

Output:

select bank_bal(10001) from dual;
4000

— Function to show bank balance

create or replace function bank_bal(p_acno number) return number is
tot_dep number;
tot_wit number;
bal number;
begin

select sum(amount) 
into   tot_dep
    from   banks 
    where  ttype='d' 
     and   acno = p_acno;


select sum(amount) 
into   tot_wit
    from   banks 
    where  ttype='w' 
     and   acno = p_acno;

bal:=tot_dep - tot_wit;

return(bal);

end;

SQL> @ filename.sql
function created

SQL> select bank_bal(10001) from dual;

SQL> select acno,bank_bal(acno) from banks group by acno;

Share this post: on Twitter on Facebook on Google+