Jun 28, 2010

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


0 comments:

Post a Comment

Twitter Delicious Facebook Digg Google Favorites More

 
Design by Free WordPress Themes | Bloggerized by Lasantha - Premium Blogger Themes | Best Hostgator Coupon Code