How to use a function in Oracle PL/SQL?

What is a Function in Oracle PL/SQL


Function :

The PL/SQL Function is very identical to PL/SQL Procedure.The big dissension between procedure and a function, a function essential always return a value, but procedure may or may not return a value.

 

 Functions is also used to solve a particular task like procedure. Function always returns a value.Oracle provided us a facility to create our own functions to solve any task.

  How To Create a function 

 Syntax:

Create or replace function p1 (formal parameters)
return datatype
is
variable,cursor, user_defined exception 
begin
......
return expression; // Do not use dbms_output.put_line()
exception
.......
end;
_________________________________________________________________________________
Execution:

Method 1: Select function_name from dual;

Method 2: By anonymous block
          Syntex:
          declare
          variable datatype(size);
          begin
          variable:=function_name;
          dbms_output.put_line(variable);
          end;

  Examples of Oracle PL/SQL Function

Example: 1:

Create or replace function f1 (a varchar2)
return varchar2
is
b varchar2 (20);
begin
select a into b from dual;
return b;
end ;
/
EXECUTION

1) Select f1('hi') from dual;
   Output:  hi

2) by anonymous block
  declare
  z varchar2(20);
  begin
  z: =f1 ('hi');
  dbms_output.put_Line(z);
  end;
  /
 Output: hi
_________________________________________________________________________________
3)Function for Even or Odd

create or replace function f112(a number)
return varchar2
is
begin
if mod(a,2)=0 then
return 'Even';
else
return 'Odd';
end if;
end;
_________________________________________________________________________________
2)LOWER FUNCTION:

Create or replace function f1 (a varchar2)
return varchar2
eis
v_lower varchar2(20);
begin
select lower (a) into v_lower from dual;
return v_lower;
end;
_________________________________________________________________________________
3) GREATEST FUNCTION

create or replace function c1(a number,b number,c number,d number)
return number
is
v_gr number(10);
begin
select greatest(a,b,c,d) into v_gr from dual;
return v_gr;
end;
_________________________________________________________________________________
4) MAXIMUM FUNCTION 

create or replace function c12
return number
is
v_max number(10);
begin
select max(sal) into v_max from emp;
return v_max;
end;

execution: select f12() from dual;
________________________________________________________________________________

5)create a function to enter name and print salary and deptno if salary is greater then average salary then return 1 else 0. 

create or replace function f12(v_ename varchar2)
return number
is
v_sal number(10);
v_deptno number(10);
v_avg number(10);
begin
select sal,deptno into v_sal,v_deptno from emp where ename=v_ename;
dbms_output.put_line('salary is '||v_sal||' '||'and deptno is '||v_deptno);
select avg(sal) into v_avg from emp where deptno=v_deptno;
dbms_output.put_line('avg sal is '||v_avg);
if v_sal>v_avg then
return 1;
else
return 0;
end if;
end;
/

6) out parameter: this mode is use to return more then one value from function using out parameter. this out parameter function is not executed by select statement.

program: write a pl/sql function passing deptno in parameter and return dname and location from dept table by using out parameter.

create or replace function d1(v_deptno in number,v_dname out varchar2, v_loc out varchar2)
return varchar2
is
cursor c1 is select * from dept where deptno=v_deptno;
i dept%rowtype;
begin
open c1;
loop
fetch c1 into i;
exit when c1%notfound;
v_dname:=i.dname;
v_loc:=i.loc;
return v_dname;
end loop;
close c1;
end;
/

_________________________________________________________________________________

Execution:

begin
:a:=f08(10,:b,:c);
end;
_________________________________________________________________________________
print b c;

_____________________________________
DML commands are used in functions
_____________________________________
 program:
________
write a stored function passing empno into in parameter then delete that empno record from emp table.
create or replace function f12(v_empno in number)
return number
is
v_count number(10);
begin
delete from emp where empno=v_empno;
v_count:=sql%rowcount;
return v_count;
end;

EXECUTION:

declare
a number(10);
begin
a:=f12(7934);
dbms_output.put_line('Total record deleted is' ||a);
end;
/


Example :-

Create a function of punching if the time is less then 08:00 then return day shift else night shift.

create or replace function f12
return varchar2
is 
begin
if(to_char(sysdate,'hh24-MI-SS')<='08:00') then
return 'DAY_SHIFT';
else
return 'NIGHT_SHIFT';
end if;
end;

/

EXECUTION

Select f12() from Dual;

WM_CONCAT(): oracle 11g introduced WM_CONCAT() FUNCTION. it is used to retrieve column values in comma saparated method we can also use this function in place of group function.

Syntax:

WM_CONCAT(COLUMN_NAME);


EXAMPLE

1)SELECT WM_CONCAT(ENAME) FROM EMP;

2)SELECT DEPT_NO, WM_CONCAT(ENAME) FROM EMP GROUP BY DEPT_NO;