I am learning Oracle database programming.
Now I am learning how to get and pass parameter through functions.
But I cannot pass multiple parameter through Pl-sql functions.
At a time one can be passed.
Help me to pass multiple parameter through functions.
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.
