Uncategorized

Oracle Stored Function

Oracle Stored Function





Oracle Stored Function similar with Oracle Stored Procedure, but Stored Function must be 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 a 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 place in here.

You can call function like that:

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

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

If your want set input parameter 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 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 do not need input value. You can do that:

CREATE OR REPLACE FUNCTION FUNCTION_NAME RETURN VARCHAR2 
AS