Function in Oracle

Pl/sql functions:-

Scenario:-

When you are required to write a block which will always return you some value depending on your logic written. For this you can use stored function which always returns a value.

What is pl/sql function?

A function is a named PL/SQL Block which is similar to a procedure. The major difference between a procedure and a function is, a function must always return a value, but a procedure may or may not return a value. Second thing is, we can use functions in select statement, update statement or in anonymous block whereas we cannot use procedure in select statement, update statement.

Syntax of Function:

CREATE [OR REPLACE] FUNCTION FUNCTION_NAME
    [ (PARAMETER [,PARAMETER]) ]
    RETURN RETURN_DATATYPE
IS | AS
    [DECLARATION_SECTION]
BEGIN
    EXECUTABLE_SECTION
[EXCEPTION
    EXCEPTION_SECTION]
END [FUNCTION_NAME];

Here:-

RETURN clause specifies that data type you are going to return from the function.

As said above when you create a procedure or function, you may define parameters. There are three types of parameters that can be declared:

1. IN - The parameter can be referenced by the procedure or function. The value of the parameter cannot be overwritten by the procedure or function.
2. OUT - The parameter cannot be referenced by the procedure or function, but the value of the parameter can be overwritten by the procedure or function.
3. IN OUT - The parameter can be referenced by the procedure or function and the value of the parameter can be overwritten by the procedure or function.

Let’s have an example of a function:-

Above you have created function successfully.

How to execute function:-

You cannot execute function same like a procedure. Hence it would be wrong if you execute it like:-

EXEC FUNCTION_NAME [(PARAMETER_NAME, PARAMETER_NAME)];   ------- THIS WILL THROW AN ERROR.

You can execute function by many different ways like:

·         Using select statement

·         Using block

·         Using bind variable

·         Using dbms_output .put_line package

·         Using select statement:-

SELECT FUNC_EMP_DETAIL FROM DUAL;

·         Using block:-

DECLARE

V_VARIABLE VARCHAR2(10);

BEGIN

V_VARIABLE:=FUNC_EMP_DETAIL;

DBMS_OUTPUT.PUT_LINE('EMPLOYEE NAME IS '||V_VARIABLE||'.');

END;

Using bind variable:-

VARIABLE V_VARIABLE VARCHAR2 (10);

BEGIN

: V_VARIABLE:=FUNC_EMP_DETAIL;

END;

PRINT V_VARIABLE;

·         using dbms_output.put_line package

EXEC DBMS_OUTPUT.PUT_LINE (FUNC_EMP_DETAIL);

You can drop function like shown below:-

DROP FUNCTION FUNC_EMP_DETAIL;

Creating Procedure