creating procedures in oracle plsql

Oracle PLSQL PROCEDURE is a named block like PLSQL Functions which can perform one or more actions.

Using Oracle PLSQL PROCEDURE we can achieve complex business needs.

Oracle PLSQL syntax to create a PROCEDURE is:

CREATE [OR REPLACE] PROCEDURE [schema.] procedure_name
[( parameter_1 [IN] [OUT] parameter_data_type_1,
parameter_2 [IN] [OUT] parameter_data_type_2,…
parameter_N [IN] [OUT] parameter_data_type_N )]
[AUTHID DEFINER | CURRENT_USER]
IS
— declaration_statements
BEGIN
— executable_statements
return {return_data_type};
[EXCEPTION
— the exception-handling statements]
END [procedure_name];

Let’s understand the above PLSQL PROCEDURE creation syntax in detail:

For ease of understanding let’s divide the PROCEDURE creation syntax in 2 parts.

1. PROCEDURE Head

2. PROCEDURE Body

PLSQL PROCEDURE Head:-

All the code before the “IS” keyword is called the PROCEDURE head or signature.

Various parts of PLSQL PROCEDURE Head are:

a. Schema: This is an optional parameter and defines the schema name in which the procedure will be created.

If omitted, it defaults to the schema of the current user.

If we specify a different user then, the other user must have the privileges to create a procedure in his/her schema.

b. Name: The NAME parameter defines the name of the procedure.

The name of a procedure should preferably start with a verb to make it more meaningful.

c. Parameters: The parameters are optional. These will be required to pass and receive values from a PLSQL procedure.

There are 3 styles of passing parameters.

IN, OUT and IN OUT

IN:This is the default style of parameter in PLSQL procedure.
We use the IN mode whenever we want the parameter to be read only i.e. we cannot change the value of the parameter in the PLSQL procedure.

The parameter defined as IN will behave as constant type inside the PLSQL procedure.

We can assign a default value to the IN type of parameter. Oracle PLSQL also allows us to make the IN as optional.
OUT: The parameters labeled as OUT returns the values to the calling subprogram or subroutines.
A default value cannot be assigned to OUT parameter hence we cannot make it optional.

We have to assign a value to OUT parameter before we exit the procedure or the value of the OUT parameter will be NULL.

While calling a procedure with OUT parameters, we have to make sure than we pass variables for the corresponding OUT parameters.
IN OUT: In this mode the actual parameter is passed to the PLSQL procedure with initial values and then within the PLSQL procedure the value of the parameter may get changed or reassigned.
The IN OUT parameter is finally returned to the calling subroutine.

d. AUTHID: This is also an optional parameter and it defines whether the procedure will execute with the privileges of the CREATOR / DEFINER of the procedure or with that of the CURRENT_USER privileges.

PLSQL PROCEDURE’s body:-

Everything after the “IS” keyword is called as the body of the procedure.

The procedure’s body conatins the declaration of variables in the declaration section, the code to be executed in the executable_statements section and the code to handle any exception in the exception handeling section.

The declaration and exception handling sections are optional in PLSQL procedure body.

We must have at least one executable statement in the executable statement section of the PLSQL procedure body.

The execution section is the one where we have to write the business logic for implementing the solution.

RETURN statement is also found in PLSQL procedure, but here it serves a different purpose than it serves in PLSQL Function.

The RETURN statement in PLSQL procedure is used to discontinue the execution of the procedure further and return the control to the calling subroutine.

Example of a PLSQL PROCEDURE:

Suppose we have a table named ‘employee’ as shown below:

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support
105	Emp E	32000	Sales	10
106	Emp F	40000	Sales	10
107	Emp G	12000	Sales	10
108	Emp H	12000	Sales

Let’s assume that we want to create a PLSQL PROCEDURE in which we will pass the ‘employee_id’ and ‘salary’ and the PLSQL PROCEDURE will update the record of the employee having the ‘empooyee_id’ using Oracle SQL UPDATE statement.

We can achieve the same as:

CREATE OR REPLACE PROCEDURE update_employee_salary
  (emp_id_in IN NUMBER
   ,salary_in IN NUMBER )
IS
BEGIN
  UPDATE employee
  SET salary = salary_in;
  WHERE employee_id = emp_id_in;
END update_employee_salary;

Above we have created a PLSQL procedure named ‘update_employee_salary’ which can take two parameters ‘employee_id’ and ‘salary’ and update the ‘employee’ table with the details.

Calling PLSQL Procedure:

A PLSQL PROCEDURE can be called using the EXEC or EXECUTE Statement:

Syntax to call a PROCEDURE using EXEC or EXECUTE statement is:

EXEC procedure_name(parameters);

EXECUTE procedure_name(parameters);

Suppose we want to update the salary of ‘employee_id = 101’ from 10000 to 15000 using update_employee_salary procedure.

Let’s call update_employee_salary procedure using EXEC statement.

EXEC update_employee_salary(101,15000);

Now if we query the ‘employee’ table as:

SELECT *
FROM employee;

We will get the following output:

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	15000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support
105	Emp E	32000	Sales	10
106	Emp F	40000	Sales	10
107	Emp G	12000	Sales	10
108	Emp H	12000	Sales

Here we can see that we have successfully updated the record for ‘employee_id = 101’ using PLSQL Procedure.