Jun 28, 2010

Oracle Stored Procedure






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




Oracle Stored Procedure format:
CREATE OR REPLACE PROCEDURE PROCEDURE_NAME(
    args1 NUMBER,
    args2 VARCHAR2(20) )
AS
    localVar1 NUMBER := 300;
    localVar2 VARCHAR(20) := 'Hello World';
BEGIN
    DBMS_OUTPUT.PUT_LINE('args1: ' || args1 || ' args2: ' || args2 || ' localVar1: ' || localVar1 || ' localVar2: ' || localVar2);
END PROCEDURE_NAME;

PROCEDURE_NAME is a procedure name. As you like, but do not include space.

args1 and args2 is a input parameter.

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

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

Calling Stored Procedure:
BEGIN
    PROCEDURE_NAME(100, 'Hello World');
END;


Call procedure to do something.

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:
BEGIN
    PROCEDURE_NAME();
    PROCEDURE_NAME(150);
    PROCEDURE_NAME(200, 'abcd');
END;


If your procedure do not need input value. You can do that:
CREATE OR REPLACE PROCEDURE PROCEDURE_NAME
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