TYPES OF PARAMETERS

How do we pass Parameters in Procedure and Functions?

In PL/SQL, we can pass parameters to procedures and functions in three ways.

1) IN type parameter: These types of parameters are used to send values to stored procedures.
2) OUT type parameter: These types of parameters are used to get values from stored procedures. This is similar to a return type in functions.
3) IN OUT parameter: These types of parameters are used to send values and get values from stored procedures.

NOTE: If a parameter is not explicitly defined a parameter type, then by default it is an IN type parameter.

1) IN parameter: We can pass values to the stored procedure through these parameters or variables. This type of parameter is read only parameter. We can assign the value of IN type parameter to a variable or use it in a query, but we cannot change its value inside the procedure. The General syntax to create an IN-parameter is

 CREATE [OR REPLACE] PROCEDURE Procedure_Name ( 

 Pparam_name1 IN DataYype, Param_name12 IN DataType ... )

• Param_name1, Param_name2... are unique parameter names.
• Datatype - Defines the Datatype of the variable.
• IN - is optional, by default it is a IN type parameter.

2) OUT Parameter:

The OUT parameters are used to send the OUTPUT from a Procedure or a Function. This is a write-only parameter i.e., we cannot pass values to OUT parameters while executing the stored procedure, but we can assign values to OUT parameter inside the stored procedure and the calling program can receive this output value.

The General syntax to create an OUT-parameter is

CREATE [OR REPLACE] PROCEDURE Procedure_Name (Param_name OUT Datatype) 

The parameter should be explicitly declared as OUT parameter. 

3) IN OUT Parameter:

The IN-OUT parameter allows us to pass values into a procedure and get output values from the procedure. This parameter is used if the value of the IN parameter can be changed in the calling program.

By using IN OUT parameter, we can pass values into a parameter and return a value to the calling program using the same parameter. But this is possible only if the value passed to the procedure and output value have a same datatype. This parameter is used if the value of the parameter will be changed in the procedure.

The General syntax to create an IN-OUT parameter is

CREATE [OR REPLACE] PROCEDURE Procedure_Name (Param_name IN OUT Datatype)

The below examples show how to create stored procedures using the above three types of parameters.

Using IN and OUT Parameters:

Let’s create a procedure which gets the name of the employee when the employee id is passed.

 CREATE OR REPLACE PROCEDURE Emp_Name (EID IN NUMBER, Emp_Name OUT VARCHAR)

 IS

 BEGIN

    SELECT Name INTO Emp_Name

    FROM Emp_Test WHERE ID = EID;

 END;

Let’s execute above procedure from a PL/SQL Block.

SQL> SELECT * FROM EMP_TEST;

        ID NAME           SALARY STATUS         DEPTNO  MANAGERID
---------- ---------- ---------- ---------- ---------- ----------
       102 Ankit            8000 Active             20        101
       104 Nikhil          69000 Active             10        101
       105 Rajan           18000 Active             20        112
       107 Karan          101000 Active             20        112
       110 Sajal           88000 Active             10        101
       103 Ravi            45000 InActive           30        104
       106 Surya           67000 Active             30        104
       108 Sam             99000 InActive           20        105
       109 Jack            77000 Active             10        106

9 rows selected.

SQL>  CREATE OR REPLACE PROCEDURE Emp_Name (EID IN NUMBER, Emp_Name OUT VARCHAR)
  2   IS
  3   BEGIN
  4      SELECT Name INTO Emp_Name
  5      FROM Emp_Test WHERE ID = EID;
  6   END;
  7  /

Procedure created.

SQL> DECLARE
  2    EmpName Varchar2(20);
  3    CURSOR C Is SELECT ID FROM EMP_TEST;
  4   BEGIN
  5   FOR Rec in C
  6   LOOP
  7     Emp_Name(Rec.ID, EmpName);
  8     Dbms_Output.Put_line('Employee Name : ' || EmpName || '  Employee ID :  ' || TO_CHAR(Rec.id));
  9   END LOOP;
 10   END;
 11  /
Employee Name : Ankit  Employee ID :  102
Employee Name : Nikhil  Employee ID :  104
Employee Name : Rajan  Employee ID :  105
Employee Name : Karan  Employee ID :  107
Employee Name : Sajal  Employee ID :  110
Employee Name : Ravi  Employee ID :  103
Employee Name : Surya  Employee ID :  106
Employee Name : Sam  Employee ID :  108
Employee Name : Jack  Employee ID :  109

PL/SQL procedure successfully completed.

In the above PL/SQL Block 

·       We are creating a Cursor which contains the Employee ID.

·       We are calling the Procedure ‘Emp_Name’, we are passing the ‘ID’ as IN parameter and ‘EmpName’ as OUT parameter.

·       We are displaying the id and the employee name which we got from the procedure ‘Emp_Name’.

Using IN-OUT parameter in Procedures:
CREATE OR REPLACE PROCEDURE Emp_Salary_Increase
 (Emp_id IN EMP_TEST.ID%Type, Salary_inout IN OUT EMP_TEST.Salary%Type) 
 IS 
    Tmp_sal number; 
 BEGIN 
    SELECT Salary INTO Tmp_sal FROM Emp_Test
    WHERE ID = Emp_id; 
   IF Tmp_sal Between 10000 and 20000 THEN 
      Salary_inout := tmp_sal * 1.2; 
   ELSIF tmp_sal Between 20000 and 30000 THEN 
      Salary_inout := tmp_sal * 1.3; 
   ELSIF tmp_sal > 30000 THEN 
      Salary_inout := tmp_sal * 1.4; 
   END IF; 
END;
The below PL/SQL block shows how to execute the above 'Emp_salary_increase' procedure.
DECLARE 
    CURSOR updated_sal is 
    SELECT ID,salary FROM Emp_Test; 
    Pre_sal number; 
 BEGIN 
   FOR emp_rec IN updated_sal LOOP 
       Pre_sal := emp_rec.salary; 
       Emp_salary_increase(emp_rec.ID, emp_rec.salary); 
       Dbms_Output.Put_Line('The salary of ' || emp_rec.ID || 
                ' increased from '|| pre_sal || ' to '||emp_rec.salary);
   END LOOP; 
 END;

SQL> CREATE OR REPLACE PROCEDURE Emp_Salary_Increase
  2   (Emp_id IN EMP_TEST.ID%Type, Salary_inout IN OUT EMP_TEST.Salary%Type)
  3   IS
  4      Tmp_sal number;
  5   BEGIN
  6      SELECT Salary
  7      INTO Tmp_sal
  8      FROM Emp_Test
  9      WHERE ID = Emp_id;
 10     IF Tmp_sal Between 10000 and 20000 THEN
 11        Salary_inout := tmp_sal * 1.2;
 12     ELSIF tmp_sal Between 20000 and 30000 THEN
 13        Salary_inout := tmp_sal * 1.3;
 14     ELSIF tmp_sal > 30000 THEN
 15        Salary_inout := tmp_sal * 1.4;
 16     END IF;
 17   END;
 18  /

Procedure created.

SQL>  DECLARE
  2      CURSOR updated_sal is
  3      SELECT ID,salary FROM Emp_Test;
  4      Pre_sal number;
  5   BEGIN
  6     FOR emp_rec IN updated_sal LOOP
  7         Pre_sal := emp_rec.salary;
  8         Emp_salary_increase(emp_rec.ID, emp_rec.salary);
  9         Dbms_Output.Put_Line('The salary of ' || emp_rec.ID ||
 10                  ' increased from '|| pre_sal || ' to '||emp_rec.salary);
 11     END LOOP;
 12   END;
 13  /
The salary of 102 increased from 8000 to 8000
The salary of 104 increased from 69000 to 96600
The salary of 105 increased from 18000 to 21600
The salary of 107 increased from 101000 to 141400
The salary of 110 increased from 88000 to 123200
The salary of 103 increased from 45000 to 63000
The salary of 106 increased from 67000 to 93800
The salary of 108 increased from 99000 to 138600
The salary of 109 increased from 77000 to 107800

PL/SQL procedure successfully completed.

RELATED TOPICS
How to create PACKAGES in ORACLE and call it's Methods?

How we overload PACKAGE and what are the advantages?

What is the Difference between SP and Function?











Get involved and leave your Comments in the Box Below. The more people get involved, the more we all benefit.

So, leave your thoughts before you leave the page.