PL/SQL Function

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

SQL>CREATE or REPLACE FUNCTION fun_emp_name(no in number)
RETURN varchar2
IS
    name varchar2(20);
BEGIN
    select ename into name from emp where empno = no;
    return name;
END;
/

Function created.

Function Calling:-
1) Call in select statement
select fun_emp_name(7369) from dual;

2) Call in PLSQL Program
 
declare
    vname varchar2(20);
begin
    vname:=fun_emp_name(7369);
    dbms_output.put_line('Ename is '||vname);
end;
/

OR

SQL>DECLARE
    no number :=&no;
    name varchar2(20);
BEGIN
    name := fun1(no);
    dbms_output.put_line('Name:'||'   '||name);
end;
/


Drop Function:-We can drop PL/SQL function using DROP FUNCTION statements.

Syntax

DROP FUNCTION function_name;

Example
SQL>DROP FUNCTION fun_emp_name;

Function dropped.