Oracle Stored Function similar to Oracle Stored Procedure, but Stored Function must return a value. Stored Procedure cannot return a value.

Oracle Stored Function format:

CREATE OR REPLACE FUNCTION FUNCTION_NAME(
    args1 NUMBER,
    args2 VARCHAR2 ) RETURN VARCHAR2 
AS
    localVar1 NUMBER := 300;
    localVar2 VARCHAR2(20) := 'Hello World';
BEGIN
    DBMS_OUTPUT.PUT_LINE('args1: ' || args1 || ' args2: ' || args2 || ' localVar1: ' || localVar1 || ' localVar2: ' || localVar2);
    RETURN localVar2;
END FUNCTION_NAME;

FUNCTION_NAME is a function name. As you like, but do not include space.

args1 and args2 is an input parameter, RETURN VARCHAR2 is your function will return which type of value.

localVar1 and localVar2 only used for this function. Cannot use localVar1 and localVar2 out of this function.

Between BEGIN and END FUNCTION_NAME; is a function main body. All auction must be placed in here.

You can call a function like that:

DECLARE
    x VARCHAR2(20);
BEGIN
    x := FUNCTION_NAME(100, 'Hello World');
    DBMS_OUTPUT.PUT_LINE(x);
END;

FUNCTION_NAME function returns a VARCHAR2 parameter. Then, print returned value.

If you want to set the input parameter to have a default value, you can do that:

CREATE OR REPLACE PROCEDURE procedure_name(
    args1 NUMBER := 100,
    args2 VARCHAR2(20) := 'Hello World' )

You can call the function like that:

DECLARE
    x VARCHAR2(20);
BEGIN
    x := procedure_name();
    DBMS_OUTPUT.PUT_LINE(x);
    x := procedure_name(150);
    DBMS_OUTPUT.PUT_LINE(x);

    x:= procedure_name(200, 'abcd');
    DBMS_OUTPUT.PUT_LINE(x);
END;

If your function does not need input value. You can do that:

CREATE OR REPLACE FUNCTION FUNCTION_NAME RETURN VARCHAR2 
AS