Function with Parameterize IN Clause

• Function are the named PL/SQL block.It can be parameterized or not,depends on the requirements.Function are used to perform the action and return a value.
• For each call function argument can be different which makes it flexible.It helps to break a program into manageable modules and increase the performance.

  If a parameter is declared in the function without IN clause then it is considered as IN by default.

• It is used to take value from outside.OUT and IN OUT are not used for function parameter as they return multiple values.Function should must have return type.

Syntax:

CREATE OR REPLACE FUNCTION function_name(
   variable1 IN datatype
) 
RETURN datatype
IS 
   variable2 datatype;
BEGIN 
   -- query statements to perform action and assign result to variable2
  
   RETURN variable2;
END;
/


SET SERVEROUTPUT ON;
DECLARE
   variable3 datatype := value_of_varible;
   variable4 datatype;
BEGIN
   variable4 := function_name(variable3);
   DBMS_OUTPUT.PUT_LINE('Function answer: ' || variable4);
END;
/

Explanation: The provided PL/SQL code defines a stored function named “function_name” with one input parameter, “variable1,” and a return type of “datatype.” Inside the function, a local variable “variable2” is declared to store the result of query statements within the function’s body. In an anonymous block, variables “variable3” and “variable4” are declared, where “variable3” is assigned an initial value.

The function is then invoked with “variable3” as an argument, and the result is stored in “variable4.” The DBMS_OUTPUT.PUT_LINE statement displays the output obtained from “variable4.” This example demonstrates the creation, invocation, and output handling of a parameterized PL/SQL function in an Oracle database

Example 1: Prameterized Function to Multiply User-Defined Number by 17 Using a Parameter

Suppose our task to create and test a PL/SQL function named FuncFirst. The function takes a numerical input parameter a and returns a value that is the result of multiplying a by 17. In the first half, define the function with appropriate parameter types, perform the multiplication operation, and return the result.

In the second half, utilize a PL/SQL block to execute the function. We will allow the user to input a value for first_var, call the FuncFirst function with this input, and display the result using the DBMS_OUTPUT.PUT_LINE statement. Ensure that the function works as intended and provides the correct multiplication result for the given input.

Query:

CREATE OR REPLACE FUNCTION FuncFirst(
    a IN NUMBER
) 
RETURN NUMBER
IS 
    ans NUMBER;
BEGIN 
    ans := a * 17; 
    RETURN ans;
END;
/
--second half
SET SERVEROUTPUT ON;
-- EXECUTE THE FUNCTION
DECLARE
    first_var NUMBER;
    result NUMBER;
BEGIN
    DBMS_OUTPUT.PUT_LINE('Enter the value of first_var ' );
    first_var := &first_var;
    result := FuncFirst(first_var);
    DBMS_OUTPUT.PUT_LINE('Ans i.e first_var * 17 = ' || first_var ||' *  17  = ' || result);
END;
/

Output:

Explanation: In first half, Parameterized Function is declared with IN clause and with return type.In second half, variable is declared and begin section contains call to Parameterized Function. Variable declared in second half is passed as argument (i.e actual parameter) to the function. Return type of the function is number.

Operation is performed and the answer is returned by the function which is assigned to the variable.This variable is printed in the output.

PL/SQL stands for Procedural Language/ Structured Query Language. It has block structure programming features.PL/SQL supports SQL queries. It also supports the declaration of the variables, control statements, Functions, Records, Cursor, Procedure, and Triggers.

PL/SQL contains a declaration section, execution section, and exception-handling section. Declare and exception handling sections are optional. Every PL/SQL query contains BEGIN, and END keywords. We can nest blocks in PL/SQL. It supports anonymous blocks and named blocks. Anonymous blocks are not stored in the database while named blocks are stored.

In this article, we will cover the Parameterize IN clause in PL/SQL. IN clause is used to test expressions in SQL which the PL/SQL also supports.IN clause is used in the Procedure and Function parameter. Procedure and Function come under the named block.IN clause is considered the default mode to pass a parameter.