Procedure in PL/SQL

PL/SQL (Procedural Language/Structured Query Language) is a powerful extension of SQL used to manage complex database logic. One of the core features of PL/SQL is the procedure, which

allows developers to group related SQL and PL/SQL statements into a single block of code that can be executed as needed. Procedures in PL/SQL are stored in the database, providing reusable and maintainable code that enhances performance and modularity. In this article, we will explore PL/SQL procedure definition, dive into the process of creating procedures in PL/SQL, discuss the PL/SQL procedure syntax, and outline the benefits of using procedures. Additionally, we will examine the difference between functions and procedures and provide practical examples and tables to enhance your understanding of procedures in PL/SQL.

Introduction to Procedures in PL/SQL

A procedure in PL/SQL is a named block of code designed to execute a particular task. After its creation, the procedure can be invoked, or called from other blocks of PL/SQL, stored procedures, or even directly from SQL statements. Procedures for developers help avoid the repetition of code, promote code reuse, and allow the client applications to reduce loads going to networks between the client application and the database.

Such procedures are usually applied to run business logic, enforce data integrity, manage various tasks, or interact with database objects like tables and views.

PL/SQL Procedure Definition

A PL/SQL procedure is a subprogram that performs an action and optionally accepts parameters. Unlike functions, procedures do not necessarily return a value. Procedures are particularly useful for tasks that involve modifying data in the database, executing complex queries, or performing operations on tables, all without returning a result to the caller.

Key points of a PL/SQL procedure:

• It is a named block of code.
• Can take parameters (IN, OUT, IN OUT) to pass values.
• Does not return a value (unlike functions).
• Encapsulates complex logic into reusable code.

Creating Procedures in PL/SQL

To write a procedure in PL/SQL, you use the CREATE PROCEDURE statement followed by the name of your procedure, optional parameters, and the body of your procedure. A procedure created can then be executed using the EXEC command or through an anonymous block.

Syntax for Creating a Procedure in PL/SQL

The basic syntax for defining a procedure in PL/SQL is as follows:

CREATE OR REPLACE PROCEDURE procedure_name (parameter_list)
IS
  -- Declaration section
BEGIN
  -- Execution section
  -- Code logic goes here
EXCEPTION
  -- Exception handling section
  WHEN exception_name THEN
    -- Handling exception
END;

Components of a PL/SQL Procedure

Component	Description
procedure_name	The name of the procedure.
parameter_list	(Optional) Parameters for passing values into and out of the procedure.
IS/AS	Specifies the start of the procedure definition.
BEGIN	The start of the executable section, where the logic resides.
EXCEPTION	(Optional) The section where exceptions (errors) are handled.
END;	Marks the end of the procedure.

Example of Creating a Procedure in PL/SQL

CREATE OR REPLACE PROCEDURE update_salary (p_emp_id IN NUMBER, p_increment IN NUMBER)
IS
BEGIN
  -- Update employee salary by a given increment
  UPDATE employees
  SET salary = salary + p_increment
  WHERE emp_id = p_emp_id;

  DBMS_OUTPUT.PUT_LINE('Salary updated for employee: ' || p_emp_id);
END;

create a procedure called update_salary that takes as input parameters an employee ID, p_emp_id and the amount that is going to increase his salary, p_increment. This procedure updates the salary of an employee for a message to be printed.

PL/SQL Procedure Syntax

Procedure Syntax. Such flexibility in syntax of procedures allows an author to define both parameterised and parameterised procedures as well as with exception handling. It will be nice here to make critical pieces of the PL/SQL procedure syntax:

Parameter Modes in PL/SQL Procedures

Parameter Mode	Description
IN	The parameter is passed into the procedure but cannot be modified within the procedure.
OUT	The parameter is used to return a value to the caller, and its value is set within the procedure.
IN OUT	The parameter can both be passed in and modified within the procedure.

You can define parameters in procedures to make them more flexible and dynamic. This allows procedures to accept input values, process them, and return results.

Example: Procedure with Parameters

CREATE OR REPLACE PROCEDURE get_employee_info (p_emp_id IN NUMBER, p_name OUT VARCHAR2, p_salary OUT NUMBER)
IS
BEGIN
  -- Retrieve employee details based on emp_id
  SELECT name, salary
  INTO p_name, p_salary
  FROM employees
  WHERE emp_id = p_emp_id;
END;

In this example, the get_employee_info procedure takes an input parameter p_emp_id and two output parameters p_name and p_salary, which return the employee’s name and salary.

Difference Between Functions and Procedures in PL/SQL

While both procedures and functions are subprograms in PL/SQL, there are fundamental differences between the two:

Feature	Procedure	Function
Return Value	Does not return a value.	Returns a value.
Purpose	Used to perform an action or task.	Used to compute and return a value.
Call in SQL Statements	Cannot be called directly in a SQL statement.	Can be called directly within SQL statements.
Mandatory RETURN Statement	Does not require a RETURN statement.	Requires a RETURN statement to return a value.

Example of a Function vs. Procedure

Function Example:

CREATE OR REPLACE FUNCTION get_total_salary (p_emp_id IN NUMBER) RETURN NUMBER
IS
  v_total_salary NUMBER;
BEGIN
  -- Calculate total salary
  SELECT salary + bonus
  INTO v_total_salary
  FROM employees
  WHERE emp_id = p_emp_id;

  RETURN v_total_salary;
END;

Procedure Example:

CREATE OR REPLACE PROCEDURE update_employee_bonus (p_emp_id IN NUMBER, p_bonus IN NUMBER)
IS
BEGIN
  -- Update employee bonus
  UPDATE employees
  SET bonus = p_bonus
  WHERE emp_id = p_emp_id;
END;

In this example, the function get_total_salary returns a numeric value, while the procedure update_employee_bonus simply performs an action without returning any value.

Example: Creating and Executing a PL/SQL Procedure

Now let’s see a full example that shows how to create, execute, and manage a PL/SQL procedure.

Example: Updating Employee Salaries

CREATE OR REPLACE PROCEDURE adjust_salary (p_emp_id IN NUMBER, p_percent IN NUMBER)
IS
BEGIN
  -- Update employee salary by a percentage
  UPDATE employees
  SET salary = salary * (1 + p_percent / 100)
  WHERE emp_id = p_emp_id;

  DBMS_OUTPUT.PUT_LINE('Salary adjusted for employee: ' || p_emp_id);
END;

Executing the Procedure

BEGIN
  -- Execute the adjust_salary procedure
  adjust_salary(101, 10);  -- Increase employee 101's salary by 10%
END;

In this exercise, we define a procedure called adjust_salary that increases an employee’s salary by a specified percentage. The procedure should accept an employee ID as well as the percentage increase for this action. Finally, we call the procedure using an anonymous block.

Error Handling in PL/SQL Procedures

One of the important things related to procedures in PL/SQL is error handling. It is this EXCEPTION section of a procedure which actually enables developers to capture and handle errors efficiently.

Error Handling in a Procedure Example

CREATE OR REPLACE PROCEDURE safe_salary_update (p_emp_id IN NUMBER, p_increment IN NUMBER)
IS
  insufficient_funds EXCEPTION;
BEGIN
  -- Update salary with error handling
  IF p_increment < 0 THEN
    RAISE insufficient_funds;
  END IF;

  UPDATE employees
  SET salary = salary + p_increment
  WHERE emp_id = p_emp_id;

  DBMS_OUTPUT.PUT_LINE('Salary updated successfully.');
EXCEPTION
  WHEN insufficient_funds THEN
    DBMS_OUTPUT.PUT_LINE('Error: Cannot decrease salary.');
END;

In this example, the procedure safe_salary_update handles a custom exception insufficient_funds, which is raised when the p_increment is negative.

Managing Parameters in Procedures

PL/SQL procedures support different types of parameters: IN, OUT, and IN OUT. The use of these parameters allows developers to pass values to procedures and receive results.

Parameter Type	Description
IN	Passes values into the procedure (read-only).
OUT	Returns values from the procedure (write-only).
IN OUT	Passes values into and out of the procedure (read-write).

Example: Procedure with IN and OUT Parameters

CREATE OR REPLACE PROCEDURE calculate_bonus (p_emp_id IN NUMBER, p_bonus OUT NUMBER)
IS
BEGIN
  -- Calculate bonus for an employee
  SELECT salary * 0.1
  INTO p_bonus
  FROM employees
  WHERE emp_id = p_emp_id;
END;

In this example, the procedure calculate_bonus takes an employee ID as input and calculates the bonus, which is returned through the p_bonus OUT parameter.

Advantages of Procedure in PL/SQL

A procedure is named blocks of codes that perform an action, something that could be used universally in the application. They have several benefits that make PL/SQL code more modular, maintainable, and efficient. Here are some of the main advantages of using procedures in PL/SQL:

1. Modularity

Procedures allow programmers to break large tasks into smaller, more manageable items. The modular nature of the approach helps in the development, testing, and later maintenance of code. A purpose for each procedure will give the code overall functionality a structured approach.

2. Reusability

Once a procedure is defined, it can be called many times in a variety of PL/SQL blocks or applications. This reduces redundant coding and brings the same consistency in the application; when a change is applied to the procedure, it will reflect where the procedure is called.

3. Maintainability

Procedures make code more maintainable since it separates specific tasks. When a need arises for modification or debugging, a developer may only focus on one procedure without affecting other parts of the code, making changes easier to handle over time.

4. Encapsulation of Logic

Procedures encapsulate the logic required for the execution of specific tasks. This makes the implementation details of complex scenarios opaque to developers. It makes the user interface simple because users may call procedures without knowing what lies in their implementation.

5. Parameterise

Procedures can take in parameters, and the procedures can work on an input of different data items. This feature allows dynamic execution based on the arguments provided, and hence, the procedures become more flexible and adaptable to varying scenarios.

6. Improved Performance

Procedures reduce the amount of data that needs to be transmitted between the database and application and thus improve performance. Since procedures are run on the database server, they minimise network traffic and can make use of the server’s processing power for handling data more efficiently.

7. Transaction Control

Procedures can hold transaction control statements (such as COMMIT and ROLLBACK), so that programmers can manage database transactions in a much better way. Such capability ensures data integrity since it permits multiple related operations to be held within a single transactional context.

8. Error Handling

Procedures can have error handling constructs using exception handling concepts. This facilitates error management with the central help of the procedure, so that developers can handle exceptions graciously and make the application much more robust.

9. Readability

Structures are best created, where the same code is organised into procedures. Correctly named procedures provide context to what they do, helping others to understand the purpose and flow of codes quickly.

10. Separation of Concerns

Procedures separate different aspects of application logic. The isolation of a specific functionality in procedures ensures the kinds of related codes are grouped together and less dependent on another, and thus the overall clearer code is produced.

11. Easy Testing and Debugging

Testing single procedures rather than large code blocks is relatively easier. Developers can easily test the behaviour of every procedure. It simplifies the process of debugging and assures that specific tasks work correctly before integrating these tasks into the larger application.

Disadvantages of Procedure in PL/SQL

While procedures in PL/SQL offer many advantages, they also come with certain drawbacks that can impact the development and execution of PL/SQL code. Understanding these disadvantages is crucial for making informed design choices in database applications. Here are some key disadvantages of using procedures in PL/SQL:

1. Complexity in Management

As the number of procedures increases, managing them can become complex. Developers need to keep track of numerous procedures, their dependencies, and interactions with other components. This can lead to challenges in maintaining an organized codebase.

2. Overhead of Context Switching

When procedures are called, there is a context switch between the application and the database server. This overhead can impact performance, especially if procedures are called frequently or if they involve complex operations that could be handled more efficiently within a single execution context.

3. Limited Control Over Execution Flow

Procedures execute a predefined sequence of operations, which may limit flexibility in certain scenarios. If the business logic requires dynamic decision-making based on varying conditions, procedures may not provide the necessary adaptability without significant restructuring.

4. Difficulty in Debugging

Debugging procedures can be challenging, particularly when they are complex or contain multiple nested calls. Isolating errors or understanding the flow of execution may require additional effort, making it harder to identify and resolve issues quickly.

5. Dependency on Database Server

Procedures execute on the database server, which means they are subject to the server’s performance and resource constraints. Any server-side issues can affect the execution of procedures, potentially leading to delays or failures in application functionality.

6. Increased Development Time

Designing and implementing procedures may increase initial development time, especially when extensive testing and debugging are required. Developers need to invest time in creating well-structured procedures, which could delay project timelines.

7. Limited Parameter Flexibility

While procedures can accept parameters, the type and number of parameters are fixed at the time of definition. This limitation may reduce the flexibility needed for handling varying input scenarios, requiring developers to create multiple procedures for different use cases.

8. Potential for Performance Bottlenecks

If procedures are not optimized or if they perform inefficient operations, they can become performance bottlenecks. Poorly designed procedures can lead to slow execution times and degrade overall application performance.

9. Lack of Inheritance and Polymorphic

PL/SQL does not support object-oriented programming features like inheritance and polymorphic within procedures. This absence can limit the ability to create reusable and extensible code structures that are common in object-oriented programming paradigms.