Procedural in PL/SQL (Procedural Language/Structured Query Language) is a named block of code that performs a specific task. Procedures are stored in the database and can be executed whenever needed. They can accept parameters, execute SQL statements, and contain procedural logic to perform operations such as data manipulation and business logic implementation.

Detailed explanation of each type of procedure in Oracle SQL and PL/SQL, including their definitions and examples.

1. Standalone Procedures

Definition: Standalone procedures are independent subprograms that can be created, stored, and executed in the database. They do not belong to any package and can be called from any PL/SQL block or SQL statement.

Example:

CREATE OR REPLACE PROCEDURE greet_user AS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello, World!');
END greet_user;

Explanation:

The CREATE OR REPLACE PROCEDURE statement defines a new procedure named greet_user.

Inside the procedure, the DBMS_OUTPUT.PUT_LINE function prints "Hello, World!" to the output console.

This procedure can be called from any PL/SQL block or even from SQL*Plus, provided that output is enabled.

Execution:

BEGIN
greet_user;
END;
/

1. Stored Procedures

Definition: Stored procedures are subprograms that are compiled and stored in the database. They can accept parameters and perform operations such as data manipulation (INSERT, UPDATE, DELETE) and retrieval.

Example:

CREATE OR REPLACE PROCEDURE get_employee_details(emp_id IN NUMBER) AS
emp_name VARCHAR2(100);
BEGIN
SELECT name INTO emp_name FROM employees WHERE id = emp_id;
DBMS_OUTPUT.PUT_LINE('Employee Name: ' || emp_name);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No employee found with ID: ' || emp_id);
END get_employee_details;

Explanation:

This procedure, get_employee_details, takes an employee ID (emp_id) as input.

It retrieves the employee's name from the employees table and prints it.

An exception handler is included to handle cases where no employee exists with the given ID.

Execution:

BEGIN
get_employee_details(101); -- Pass an employee ID
END;
/

1. Package Procedures

Definition: Package procedures are defined within PL/SQL packages, which are collections of related procedures and functions. Packages help in encapsulating data and procedures, promoting reusability and maintainability.

Example:

CREATE OR REPLACE PACKAGE employee_pkg AS
PROCEDURE get_employee_details(emp_id IN NUMBER);
END employee_pkg;

CREATE OR REPLACE PACKAGE BODY employee_pkg AS
PROCEDURE get_employee_details(emp_id IN NUMBER) AS
emp_name VARCHAR2(100);
BEGIN
SELECT name INTO emp_name FROM employees WHERE id = emp_id;
DBMS_OUTPUT.PUT_LINE('Employee Name: ' || emp_name);
END get_employee_details;
END employee_pkg;

Explanation:

The package employee_pkg declares a procedure get_employee_details that will be defined in the package body.

The package body contains the implementation of the procedure, which retrieves and displays the employee's name based on the provided ID.

Execution:

BEGIN
employee_pkg.get_employee_details(101); -- Call the procedure from the package
END;
/

1. Parameterized Procedures

Definition: Parameterized procedures accept parameters that allow for the dynamic input of data when the procedure is called. Parameters can be input (IN), output (OUT), or both (IN OUT).

Example:

CREATE OR REPLACE PROCEDURE update_employee_salary(emp_id IN NUMBER, new_salary IN NUMBER) AS
BEGIN
UPDATE employees SET salary = new_salary WHERE id = emp_id;
COMMIT;
DBMS_OUTPUT.PUT_LINE('Salary updated for employee ID: ' || emp_id);
END update_employee_salary;

Explanation:

This procedure update_employee_salary takes two parameters: emp_id and new_salary.

It updates the salary of the employee with the given ID and commits the changes to the database.

Execution:

BEGIN
update_employee_salary(101, 60000); -- Update the salary of employee ID 101
END;
/

1. Overloaded Procedures

Definition: Overloaded procedures allow the same procedure name to be used for multiple procedures, as long as their parameter lists differ in type or number. This is useful for creating more flexible APIs.

Example:

CREATE OR REPLACE PROCEDURE print_message(msg IN VARCHAR2) AS
BEGIN
DBMS_OUTPUT.PUT_LINE('Message: ' || msg);
END print_message;

CREATE OR REPLACE PROCEDURE print_message(num IN NUMBER) AS
BEGIN
DBMS_OUTPUT.PUT_LINE('Number: ' || num);
END print_message;

Explanation:

Two procedures are defined with the same name print_message, but they differ in parameter types (one takes a string, and the other takes a number).

This allows for flexible calls to the same procedure name with different data types.

Execution:

BEGIN
print_message('Hello, World!'); -- Calls the first procedure
print_message(12345); -- Calls the second procedure
END;
/

1. Recursive Procedures

Definition: Recursive procedures are those that call themselves, either directly or indirectly. This technique is commonly used for problems that can be broken down into smaller subproblems, such as calculating factorials or Fibonacci numbers.

Example:

CREATE OR REPLACE PROCEDURE factorial(n IN NUMBER, result OUT NUMBER) AS
BEGIN
IF n = 1 THEN
result := 1;
ELSE
factorial(n - 1, result);
result := result * n;
END IF;
END factorial;

Explanation:

This procedure computes the factorial of a number n. If n is 1, it sets the result to 1; otherwise, it calls itself with n - 1 and multiplies the result by n.

The result parameter is marked as OUT, allowing the caller to receive the computed factorial.

Execution:

DECLARE
fact NUMBER;
BEGIN
factorial(5, fact); -- Calculate the factorial of 5
DBMS_OUTPUT.PUT_LINE('Factorial: ' || fact);
END;
/

1. Dynamic Procedures

Definition: Dynamic procedures utilize dynamic SQL to construct and execute SQL statements at runtime. This is useful for scenarios where the exact SQL statement cannot be determined until runtime.

Example:

CREATE OR REPLACE PROCEDURE dynamic_query(tab_name IN VARCHAR2) AS
v_sql VARCHAR2(1000);
v_count NUMBER;
BEGIN
v_sql := 'SELECT COUNT(*) FROM ' || tab_name; -- Construct SQL dynamically
EXECUTE IMMEDIATE v_sql INTO v_count; -- Execute the dynamic SQL
DBMS_OUTPUT.PUT_LINE('Number of rows in ' || tab_name || ': ' || v_count);
END dynamic_query;

Explanation:

This procedure dynamic_query takes a table name as input, constructs a SQL query to count the number of rows in that table, and executes it using EXECUTE IMMEDIATE.

The result is printed to the console.

Execution:

BEGIN
dynamic_query('employees'); -- Call the procedure with the table name
END;
/

Summary

Each type of procedure serves a distinct purpose and can be employed in various scenarios to enhance code organization, reusability, and maintainability in Oracle SQL and PL/SQL applications. These procedures help manage business logic, manipulate data, and provide structured access to database operations.