Parameterize IN Clause PL/SQL

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.

Parameterize IN Clause PL/SQL

• Parameters in PL/SQL can contain the IN, OUT, IN OUT clause. IN clause represents that argument value will be passed from the outside. IN clause is used in parameters of both PL/SQL Parameterized Function and PL/SQL Parameterized Procedure.
• It is considered the default mode to pass a parameter. The actual Parameter is passed as a reference (i.e. parameter refers to the same memory location) using the IN clause.
  the
• It allows to make the queries flexible and code becomes easier to maintain. Parameters with IN clause allow to pass values as per requirements.
• The parameter of the function or procedure contains the variable first then the IN clause and then the data type of the variable.

Syntax:

DECLARE                    (optional section)
--Declaration section
--Declare variables and subprograms.
 BEGIN                         (required section)
-- Queries
 --Execution section
EXCEPTION                (optional section)
--Exception section 
END;

Explanation:

The PL/SQL block structure consists of three essential sections:

1. DECLARE Section (Optional): This section is used for declaring variables and subprograms that will be utilized in the subsequent execution section.
2. BEGIN Section (Required): The core of the PL/SQL block, where queries and statements are placed to execute actions or operations.
3. EXCEPTION Section (Optional): If included, this section addresses exception handling, providing a mechanism to manage errors that may arise during the block’s execution.

The entire structure is encapsulated between the keywords DECLARE and BEGIN, with an optional EXCEPTION section for error handling, culminating with the END keyword marking the conclusion of the block.

Procedure with Parameterize IN Clause

• Procedure are the named PL/SQL block. It can be parameterized or not,depends on the requirements.Procedure are used to perform the action again and again.
• For each call procedure argument can be different.It helps to break a program into reusable modules and increase the performance.If a parameter is declared in the procedure without any of the IN, OUT, IN OUT clause then it is considered as IN by default.
• It is used to take value from outside.

Syntax:

CREATE OR REPLACE PROCEDURE procedure_name(
   variable1 IN datatype,
    variable2 OUT datatype
) 
IS 
BEGIN 
    -- query statements to perform action
END;
/
SET SERVEROUTPUT ON;
DECLARE
variable3 datatype :=value_of_varible;
variable4 datatype :=value_of_varible;
BEGIN
procedure_name( variable3,variable4 );
DBMS_OUTPUT.PUT_LINE('    Procedure answer:       '      ||      variable4) ;
END;
/

Explanation: In the above PL/SQL code it defines a stored procedure named “procedure_name” with two parameters: “variable1” (input) and “variable2” (output). The procedure’s purpose is to execute specific query statements within its body.

In an anonymous block, variables “variable3” and “variable4” are declared, assigned initial values, and used as arguments to invoke the procedure. The DBMS_OUTPUT.PUT_LINE statement displays the result obtained from “variable4.” This structured syntax illustrates the creation, invocation, and output handling of a parameterized PL/SQL procedure within an Oracle database.

Example 1: Prameterized Procedure to Multiply User Defined Number by 2 Using a Parameter

Suppose we have to manage a customer database in SQL Server. The original table named customers is designed without an identity column. Our goal is to alter this table by adding an identity column named customer_id which serve as the primary key. The existing customer_id column will be dropped, and the new identity column should be created with an incremental seed starting from 1. Additionally, insert sample data into the table and demonstrate the modifications by displaying the updated table. Finally, we will add a new customer record to ensure the functionality of the identity column.

Query:

CREATE OR REPLACE PROCEDURE ProcFirst(
    a IN NUMBER,
    ans OUT NUMBER
) 
IS 
BEGIN 
    ans := a * 2; 
END;
/
--second half
SET SERVEROUTPUT ON;
-- EXECUTE THE PROCEDURE
DECLARE


 first_var number;
 result number;
BEGIN
 DBMS_OUTPUT.PUT_LINE('Enter the value of first_var ' );
 first_var:=&first_var;
 ProcFirst(first_var,result);
 DBMS_OUTPUT.PUT_LINE('Ans  i.e first_var * 2 = ' || first_var ||' *  2  = ' || result);
END;
/

Output:

Procedure with Parameterize IN Clause

Explanation: In first half, Parameterized Procedure is declared with IN and OUT clause. BEGIN section of the procedure contains the action.In second half ,variables are declared and begin section contains call to Procedure.

Variables declared in second half are passed as arguments (i.e actual parameter) to the procedure. First argument is passed as reference as it is declared with IN clause.Second argument is declared with OUT clause hence it is used to print the result of the action through DBMS_OUTPUT.PUT_LINE(‘ ‘);

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:

Function with Parameterize IN Clause

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.

Conclusion

Parameterize IN clause in PL/SQL allows to pass the value from outside. It is considered as default mode to pass a parameter. Parameters are passed as reference using IN clause.By considering the syntax ,developers can use Parameterize IN clause to develop flexible and reusable code.