In the previous tutorial we have discussed how to create stored procedure without parameters. But sometimes it may happen that you will need to create a stored procedure which accepts parameters. After all, these subroutines are there to help you in getting the solution of your problem in the easiest way possible. Thus today in this blog we will learn how to create stored procedures with parameters in Oracle Database

For those who are new to PL/SQL Programming and wondering what is stored procedure? Don’t worry I have done a separate blog for you explaining the fundamental theory of the stored procedure. You can check that blog here.

So let’s see the demonstration of how to create PL/SQL stored procedure with parameters!

Step 1: Create the header of the stored procedure

In the header of the procedure we define its signature.

CREATE OR REPLACE PROCEDURE emp_sal
(dep_id NUMBER, sal_raise NUMBER) 
IS

The header is pretty similar to the one which we saw in the last tutorial except that this time our procedure is accepting parameters which are dep_id and sal_raise of NUMBER datatype.

Step 2: Create the execution section of the stored procedure

In the execution section we write all the executable statements which define the working of the stored procedure.

BEGIN
  UPDATE employees SET salary = salary * sal_raise WHERE department_id = dep_id;
END;
/

For a better understanding I have tried to make this code as simple as possible. In the execution section we only have one DML statement which is UPDATE. Using this we update the salary column of employee table.

You can write the business logic like this then wrap them up into a procedure and call them in your app when needed. This will give you more control on your app. It will also save you from writing the same code again and again.

This procedure will accept two parameters which is the department id and the numeric value for salary raise. First parameter which is the dep_id, is used to determine the ID of the department. The second parameter which is sal _ raise will become the multiplication factor in the salary raise.

For the more in-depth understanding of the same, please watch the video tutorial on my YouTube channel. There I have explained every single line and keyword of the above stored procedure in detail.

Let’s combine all the above chunks of code into a single one named PL/SQL unit.

Stored Procedure for Department Wide Salary Raise

CREATE OR REPLACE PROCEDURE emp_sal( dep_id NUMBER, sal_raise NUMBER) 
IS
BEGIN
  UPDATE emp SET salary = salary * sal_raise WHERE department_id = dep_id;
END;
/

Once you have successfully created your stored procedure, next you have to call it in your program or code. In the last tutorial I showed you different ways of calling a procedure in oracle database. You can refer to that tutorial for the same.

That is the tutorial on how to create PL/SQL stored procedure with parameters in Oracle Database. Please do Share it on your social media and help others in learning.

That’s it for today. Have a great day!