Function

It is pretty much similar to Pl/Sql procedure and it is a named PL/Sql block. The key difference between procedure and Function is that a Function always return a value where as procedure may or may not.

Three possible ways to pass the parameter to function:-

i)                    IN type parameter: to send values to function. We cannot change its value inside the function.

ii)                  OUT type parameter: to get values from function. This is similar to a return type in function.

iii)                IN OUT parameter: to send values and get values from function. This parameter is used if the value of the IN parameter can be changed in the calling program and after change to return the same.

Syntax:-

                Create [or Replace] FUNCTION func_name [Parameter’s list]

RETURN return_data_type

                IS

                                Declaration part

                Begin

                                Execution part

                                RETURN return_var

                Exception

                                Exception part

RETURN return_var

                End;

Create [or Replace]:-

                If we are writing as above it means that function will be created if no other function with the same name exists otherwise the existing function will be replaced by the current one. If you want to create the function only and in future even you think no need of function change may require then no need to write [or Replace] part.

Note: It is always a good practice to write both the things together.

[Parameter’s list]:-

                This simply shows the number of parameters which function is looking for as mentioned above of three categories (IN, OUT, IN OUT).

RETURN return_data_type:-

                Before ‘IS’ part the definition given for Return is all about the return type of the function. The return type may be any valid Oracle data-type, for e.g. varchar, Integer etc.

RETURN return_var:-

                As told earlier that Function always return a value, that is what Execution part and Exception part have Return statement and this value must be of the same data-type as mentioned in RETURN return_data_type.

Let us have an example of Function as below:-

create or replace FUNCTION SF_Test_func (emp_id  IN VARCHAR2,

        address     OUT VARCHAR2,

        status      OUT INTEGER)

RETURN Date

BEGIN

DBMS_OUTPUT.put_line ('inside');  

SELECT address ad, status s, joining_date jd FROM emp

              WHERE EMP_ID = emp_id;

address := ad;

status := s;

return jd;

DBMS_OUTPUT.put_line ('outside');  

END SF_Test_func;         

After successful creation of Function we need to execute to achieve our goal. We can execute as below:-

                From SQL prompt: - joining_date :=  function_name

Note: - Since Function returns a value that is why written as above.

The above created function can be called from SQL prompt as a Pl/Sql block as below:-

--Activate client processing of dbms_output buffer

SET SERVEROUTPUT ON

--Anonymous block, declare variable, call procedure, print resulting value

Declare

                ad VARCHAR2;

        st INTEGER;

        jd Date;

Begin

        Jd := SF_Test_func(101, ad, st);

        Dbms_output.put_line(ad);

        Dbms_output.put_line(st);

Dbms_output.put_line(jd);

End; /

Note: - Here slash / indicates that Pl/Sql block is done and ready to execute.