Passing parameters in functions in Pl-SQL

Passing parameters in functions in Pl-SQL

Hi Mus,

As a beginner you should keep these things in mind about functions: Functions can be called from SQL which are considered expressions and MUST return a value, they can return data in OUT and IN OUT parameters.

The return statement in a function returns control to the calling program and returns the results of the function. Parameters are of three types IN (used as input only), OUT (initially NULL) and IN OUT (may or may not have an initial value).

There are two different types of functions:

Traditional Function:

CREATE OR REPLACE FUNCTION <function_name> [(input/output variable  declarations)]

[AUTHID <CURRENT USER | DEFINER>] <IS|AS>

                    [declaration block]

BEGIN

                    <PL/SQL block WITH RETURN statement>

                    RETURN <return_value>;

[EXCEPTION

                    EXCEPTION block]

                    RETURN <return_value>;

END;

Pipelined Table Function

CREATE OR REPLACE FUNCTION <function_name> [(input/output variable declarations)] RETURN return_type

[AUTHID <CURRENT USER | DEFINER>] [<AGGREGATE | PIPELINED>] <IS|USING>

                    [declaration block]

BEGIN

                    <PL/SQL block WITH RETURN statement>

                    PIPE ROW <RETURN type>;

                    RETURN;

[EXCEPTION

                    EXCEPTION block]

                    PIPE ROW <RETURN type>;

                    RETURN;

END;

Now for passing multiple parameters through functions about how to declare and call function having multiple parameters. Check this example.

CREATE OR REPLACE FUNCTION multi_para_func (p_name IN VARCHAR2 := 'Zorian', p_address IN VARCHAR2 := '11 Garden Street', p_inout_parameter IN OUT NUMBER, p_out_parameter  OUT DATE )

RETURN VARCHAR2 AS v_a_variable VARCHAR2(20);

BEGIN

IF p_name = ' Zorian' THEN RETURN -1;

END IF;

v_a_variable := 99;

p_inout_parameter := v_a_variable;

p_out_parameter  := SYSDATE;

RETURN v_a_variable;

END;

Call to  function:

DECLARE v_employee VARCHAR2(20) := 'Mat';

v_number NUMBER := 22;

v_date DATE;

v_variable VARCHAR2(20);

BEGIN

v_variable :=  multi_para_func (p_name => v_employee, p_inout_parameter => v_number, p_out_parameter  => v_date );

DBMS_OUTPUT.PUT_LINE(v_variable || ', ' || v_employee || ', ' || to_Char(v_number) || ', ' || to_char(v_date) );

v_variable := multi_para_func (p_inout_parameter => v_number, p_out_parameter  => v_date);

DBMS_OUTPUT.PUT_LINE(v_variable || ', ' || v_employee || ', ' || to_Char(v_number) || ', ' || to_char(v_date) );

END;

This way you can relate method of passing multiple parameters to function in Oracle database programming.