Uncategorized

Oracle Stored Procedure

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