PL/SQL Function

 PL/SQL Function 

     Functions are PLSQL BLocks, used for calculation. PL/SQL functions create using CREATE FUNCTION statement.

 PL/SQL Functions Syntax:-  

CREATE [OR REPLACE] FUNCTION [SCHEMA.]function_name 
        [ (parameter [,parameter]) ]
        RETURN return_datatype
 IS | AS
 [declaration_section
     variable declarations;
     constant declarations;
 ]
 BEGIN
     [executable_section
         PL/SQL executable block;
     ]
 [EXCEPTION]
         [exception_section
         PL/SQL Exception block
         ]
 END [function_name];

 PL/SQL Function Example:-  

   Example1:- Create Function that take employee number and return their name.

CREATE or REPLACE FUNCTION fun_get_emp_name( 
	in_empno in number
)
RETURN varchar2
IS
    name varchar2(20);
BEGIN
    select ename 
	into name 
	from emp 
	where empno = in_empno;
    return name;
END;

Function created.

 Function Calling:-  

  1) Call in select statement.

 select fun_get_emp_name(101) from dual;

  2) Call in PLSQL Program

 declare
    vname varchar2(20);
begin
    vname:=fun_get_emp_name(101);
    dbms_output.put_line('Ename is '||vname);
end;

 Drop Function:-  

  We can drop PL/SQL function using DROP FUNCTION statements.

  Syntax:

 DROP FUNCTION function_name;

  Example:

 DROP FUNCTION fun_get_emp_name; 

  After running above statement the pl/sql function fun_get_emp_name will get dropped.