Procedures and Functions in PL/SQL

Procedures and Functions in PL/SQL

Introduction:

What is PL/SQL?

PL/SQL is Oracle's procedural extension to SQL. It allows you to write code that includes both SQL statements and procedural constructs such as loops, conditions, and error handling.

What are Procedures and Functions?

• Procedures:
  Procedures are named blocks of code that perform one or more specific actions. They do not return a value directly but can modify database records, perform calculations, or control the flow of execution.
• Functions:

Functions are similar to procedures but are designed to return a single value. They are often used for calculations or processing data and can be called directly within SQL statements.

Advantages

Advantages of Using Procedures and Functions

• Modularity:
  Code is divided into smaller, manageable pieces, making it easier to understand, maintain, and reuse.
• Reusability:
  Once written, procedures and functions can be used multiple times throughout the application.
• Performance:
  By executing code on the server, you reduce the amount of data transferred between the client and server.
• Security:
  They help encapsulate business logic and can restrict direct access to the underlying data.

Syntax and Examples

Procedure Syntax

Basic Syntax:

CREATE [OR REPLACE] PROCEDURE procedure_name (parameter_list)

IS

BEGIN

   -- SQL and PL/SQL statements

END procedure_name;

/

Normal Example:

Imagine you want to add a new employee to an "employees" table. The procedure would look like this:

CREATE OR REPLACE PROCEDURE add_employee (

   p_emp_id   IN NUMBER,

   p_name     IN VARCHAR2,

   p_salary   IN NUMBER

)

IS

BEGIN

   INSERT INTO employees (emp_id, name, salary)

   VALUES (p_emp_id, p_name, p_salary);

   COMMIT;

END add_employee;

/

• Explanation:
  The procedure add_employee accepts three parameters (employee ID, name, and salary) and inserts a new record into the employees table.

Real-Time Example:

In a payroll system, an administrator may use a procedure to add employee records after collecting data from various sources. This reduces manual errors and speeds up the data entry process.

Function Syntax

Basic Syntax:

CREATE [OR REPLACE] FUNCTION function_name (parameter_list)

RETURN return_datatype

IS

BEGIN

   -- SQL and PL/SQL statements

   RETURN value;

END function_name;

/

Normal Example:

Suppose you need to calculate the annual salary from a monthly salary. The function would look like this:

CREATE OR REPLACE FUNCTION calculate_annual_salary (

   p_monthly_salary IN NUMBER

)

RETURN NUMBER

IS

BEGIN

   RETURN p_monthly_salary * 12;

END calculate_annual_salary;

/ 

• Explanation:
  The function calculate_annual_salary takes a monthly salary as input and returns the computed annual salary by multiplying the monthly salary by 12.

 

Real-Time Example:

In a business application, the function calculate_annual_salary can be used within SQL queries to dynamically compute annual salaries from stored monthly salary data. For instance:

SELECT emp_id, name, salary, calculate_annual_salary(salary) AS annual_salary

FROM employees;

• This query provides a quick view of each employee's annual salary, aiding in budgeting and financial analysis. 

Key Points to Remember

• Procedures perform actions but do not return a value directly.
• Functions always return a single value and can be used in SQL queries.
• Both are stored in the database, which makes them faster to execute and easier to manage.
• They help enforce business rules and maintain data integrity by encapsulating the logic within the database.

Summary

• Definition:
  Procedures and functions are essential components of PL/SQL, used to organize and reuse code efficiently.
• Syntax:
• Procedures are defined with CREATE OR REPLACE PROCEDURE and do not return a value.
• Functions are defined with CREATE OR REPLACE FUNCTION and include a RETURN clause.
• Examples:
• A procedure to insert employee records.
• A function to calculate annual salary from monthly salary.
• Real-Time Usage:
  They are widely used in applications like payroll systems, financial analysis, and data management to streamline operations and ensure consistency.