Creating a Stored Procedure or Function in an Oracle Database

A stored procedure or function can be created with no parameters, IN parameters, OUT parameters, or IN/OUT parameters. There can be many parameters per stored procedure or function.

An IN parameter is a parameter whose value is passed into a stored procedure/function module. The value of an IN parameter is a constant; it can't be changed or reassigned within the module.

An OUT parameter is a parameter whose value is passed out of the stored procedure/function module, back to the calling PL/SQL block. An OUT parameter must be a variable, not a constant. It can be found only on the left-hand side of an assignment in the module. You cannot assign a default value to an OUT parameter outside of the module's body. In other words, an OUT parameter behaves like an uninitialized variable.

An IN/OUT parameter is a parameter that functions as an IN or an OUT parameter or both. The value of the IN/OUT parameter is passed into the stored procedure/function and a new value can be assigned to the parameter and passed out of the module. An IN/OUT parameter must be a variable, not a constant. However, it can be found on both sides of an assignment. In other words, an IN/OUT parameter behaves like an initialized variable.

Note: IN is the default mode for parameter

syntax:

// Create procedure myproc with no parameters
CREATE OR REPLACE PROCEDURE myproc
AS
BEGIN
INSERT INTO oracle_table VALUES('string 1');
END;

// Create procedure myprocin with an IN parameter named x.
CREATE OR REPLACE PROCEDURE myprocin(
x VARCHAR,
y IN OUT VARCHAR
)
AS
BEGIN
INSERT INTO oracle_table VALUES(x, y);
y := 'outvalue';
END;


// Create a function named myfunc which returns a VARCHAR value;
// the function has no parameter
CREATE OR REPLACE FUNCTION myfunc
RETURN VARCHAR
AS
BEGIN
RETURN 'a returned string'
END;

// Create a function named myfuncinout that returns a VARCHAR value;
// the function has an IN/OUT parameter named x. As an IN parameter, the value of x is defined in the calling PL/SQL block before it is passed in myfuncinout function. As an OUT parameter, the new value of x, ‘x value||outvalue', is also returned to the calling PL/SQL block when the execution of the function ends.

CREATE OR REPLACE FUNCTION myfuncinout
(x IN OUT VARCHAR)
RETURN VARCHAR
AS
BEGIN
x:= x||'outvalue';
RETURN 'a returned string'
END;