PL/SQL Procedures

• They are named PL/SQL blocks thus they can be reused because they are stored into the database as a database object.
• It is a self-contained subprogram that is meant to do some specific tasks.
• A stored procedure does not return any value. 

Types of PL/SQL Procedures

PL/SQL Procedure ranges from standalone routines to PL SQL packages and functions. The common types are:-

Stored Procedures

Stored Procedures are a powerful feature in database management systems that allow you to encapsulate a set of SQL and/or PL/SQL statements into a single, reusable unit.

Anonymous Blocks
These are unnamed PL/SQL blocks that can be used to group a set of statements. They are not stored in the database and are typically used for ad hoc tasks.

Functions
Functions in PL/SQL procedure are named blocks of code that can take input parameters, perform specific tasks, and return a single value.

Procedures with Parameters
These are procedures that accept input parameters, allowing you to pass values to the procedure when it’s called.

Nested Procedures
Nested procedures are a programming concept where one procedure (function or method) is defined within another procedure. This can offer several benefits for code organization and encapsulation.

Functions of PL/SQL Procedures
PL/SQL procedure is like a stored program that can perform various tasks and calculations inside an Oracle database. Here are some of the key functions of procedures in PL/SQL:

Modularity
Procedures promote a modular approach to help you work on programming step by step. You can divide big tasks into smaller parts. This makes it easier to create, test, and handle your code.
Improved Performance
The procedures boost performance in applications by reducing the code transmitted between the app and the database server. This is particularly helpful in apps that rely heavily on network connections.
Parameter Modes
In procedures, you can use different parameter modes like IN, OUT, and IN OUT. These modes let you send data into a procedure, get results from it, or even do both, which gives you flexibility while working with data manipulation.
Encapsulation of Business Logic
Procedures aren’t just for databases; they can also contain complex business rules, simplifying their implementation and maintaining the database.

Benefits of PL/SQL Procedures

Code Abstraction
Procedures are like tools that help simplify complicated steps and methods in code. They create an easier way for the rest of the program to interact with those complex parts. This makes the code easier to read and understand because it separates different tasks and focuses on making each part clear.
Version Control
Storing procedures in a database lets you use source control tools to easily track and manage different versions. This ensures you have a clear record of changes and the ability to undo them if needed.
Performance Optimization
Using procedures can help improve performance. You can use them to do things like storing often-used data, cutting down on repetitive calculations, and reducing the back-and-forth of the database.

Encapsulation of Legacy Code
If your app works with old systems or databases, putting the interactions into procedures can protect the rest of the app from dealing with the complexities of outdated technology.
Ease of Maintenance
When you need to make changes, just changing how one procedure works only impacts that procedure itself. This makes it easier to fix issues and maintain things without causing problems elsewhere.

Syntax
CREATE [OR REPLACE] PROCEDURE procedure_name [(Parameter–List)]
IS | AS
Declare statements
BEGIN
Executable statements
END procedure_name;
/

Examples of PL/SQL Procedure

1. Simple PL/SQL Procedure

CREATE OR REPLACE PROCEDURE print_message IS
BEGIN
DBMS_OUTPUT.PUT_LINE(‘WELCOME TO SIRIAM!’);
END;
/
This method is commonly used for troubleshooting and keeping records. For example, in a complicated PL/SQL program, you might use it to display messages showing how the program is progressing or to record error messages for later analysis.

2. PL/SQL Procedure with Cursors
   CREATE OR REPLACE PROCEDURE process_employees IS
   CURSOR employee_cursor IS
   SELECT employee_id, first_name, last_name FROM employees;
   BEGIN
   FOR emp_rec IN employee_cursor LOOP
   DBMS_OUTPUT.PUT_LINE(‘Employee ID: ‘ || emp_rec.employee_id);
   DBMS_OUTPUT.PUT_LINE(‘Name: ‘ || emp_rec.first_name || ‘ ‘ || emp_rec.last_name);
   END LOOP;
   END;
   /
   This method is often used to get and work with data from the employee database in a HR system. It’s handy for getting employee’s info, like making lists or doing big data tasks.
3. PL/SQL Procedure for Updating Data
   CREATE OR REPLACE PROCEDURE update_salary
   (
   emp_id IN NUMBER,
   new_salary IN NUMBER
   ) IS
   BEGIN
   UPDATE employee_data
   SET salary = new_salary
   WHERE employee_id = emp_id;
   END;
   /
   The procedure makes sure everything stays accurate and up-to-date data. In a system for managing employees, this procedure is used to change an employee’s salary when they get a raise or change positions.

Conclusion
PL/SQL procedures are essential for efficient database app development. By categorizing based on function, leveraging benefits, and exploring its examples, developers create modular, reusable, and optimized code. With experience, advanced techniques enhance the ability to build strong, high-performing applications.