Let’s dive into PL/SQL procedures step by step, covering the basics and providing examples along the way.

1. Understanding PL/SQL Procedures:
   PL/SQL procedures are named blocks of code that can be stored and executed in a database. They encapsulate a series of SQL and PL/SQL statements, allowing you to perform specific tasks or operations. Procedures improve code reusability, modularity, and maintainability. They can have parameters, accept inputs, and return outputs.
2. Creating a PL/SQL Procedure:
   To create a PL/SQL procedure, follow these steps:

Step 1: Begin by writing the CREATE PROCEDURE statement, specifying the procedure name and optional parameters.
Step 2: Define the procedure body using the BEGIN and END keywords.
Step 3: Write the necessary SQL and PL/SQL statements within the procedure body.
Step 4: Optionally, include parameters to accept inputs and return outputs.

Here’s an example of a simple PL/SQL procedure that inserts a new customer into a “Customers” table:

CREATE OR REPLACE PROCEDURE AddCustomer(
    p_CustomerName IN VARCHAR2,
    p_CustomerEmail IN VARCHAR2,
    p_CustomerPhone IN VARCHAR2
) AS
BEGIN
    INSERT INTO Customers(Name, Email, Phone)
    VALUES (p_CustomerName, p_CustomerEmail, p_CustomerPhone);

    COMMIT;
END;
/

In the above example, the procedure “AddCustomer” takes three input parameters: CustomerName, CustomerEmail, and CustomerPhone. It inserts a new customer into the “Customers” table using the provided values.

3. Executing a PL/SQL Procedure:
   To execute a PL/SQL procedure, you can use the EXECUTE or CALL statement followed by the procedure name and any necessary input parameter values.

Example:

EXECUTE AddCustomer('John Doe', 'johndoe@example.com', '1234567890');

In the above example, we execute the “AddCustomer” procedure by passing the required input values.

4. Modifying a PL/SQL Procedure:
   To modify an existing PL/SQL procedure, you can use the CREATE OR REPLACE PROCEDURE statement followed by the updated procedure definition.

Example:

CREATE OR REPLACE PROCEDURE AddCustomer(
    p_CustomerName IN VARCHAR2,
    p_CustomerEmail IN VARCHAR2,
    p_CustomerPhone IN VARCHAR2,
    p_CustomerAddress IN VARCHAR2
) AS
BEGIN
    INSERT INTO Customers(Name, Email, Phone, Address)
    VALUES (p_CustomerName, p_CustomerEmail, p_CustomerPhone, p_CustomerAddress);

    COMMIT;
END;
/

In the above example, we modify the “AddCustomer” procedure to include an additional input parameter, CustomerAddress. We then update the INSERT statement accordingly.

5. Calling a PL/SQL Procedure with Output Parameters:
   PL/SQL procedures can also have output parameters that return values to the caller. To define an output parameter, use the OUT or IN OUT keywords.

Example:

CREATE OR REPLACE PROCEDURE GetCustomerName(
    p_CustomerID IN NUMBER,
    p_CustomerName OUT VARCHAR2
) AS
BEGIN
    SELECT Name INTO p_CustomerName
    FROM Customers
    WHERE CustomerID = p_CustomerID;
END;
/

In the above example, the “GetCustomerName” procedure takes a CustomerID as input and returns the corresponding customer name as an output parameter.

To call a procedure with output parameters, you can use the following syntax:

DECLARE
    v_CustomerName VARCHAR2(50);
BEGIN
    GetCustomerName(1, v_CustomerName);
    DBMS_OUTPUT.PUT_LINE('Customer Name: ' || v_CustomerName);
END;
/

6. Dropping a PL/SQL Procedure:
   To remove a PL/SQL procedure, you can use the DROP PROCEDURE statement followed by the procedure name.

Example:

DROP PROCEDURE AddCustomer;

In the above example, we drop the “AddCustomer” procedure from the database.

Conclusion:
PL/SQL procedures are powerful tools for organizing and executing SQL and PL/SQL code within a database. By following the steps outlined above, you can create, execute, modify, and drop procedures in your PL/SQL development. Procedures enhance code reusability, encapsulation, and maintainability, making them an essential part of building robust database applications.