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:

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(‘ ‘);

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.