Lesson 1

This module discusses creating procedures using PL/SQL. Procedures are more versatile than functions. While a function returns a single value, a procedure can return no value, or multiple values using outgoing parameters. A function has severe restrictions against modifying database data, while a procedure is allowed to modify data. During this lesson, you will create a procedure and then add a call to a function within that procedure.

• Module Objectives By the end of this module, you will know how to:
  1. Distinguish between a function and a procedure
  2. Create a procedure to calculate a percentage and an average for a given sales transaction
  3. List capabilities and restrictions of calling other procedures
  4. Add a function to a procedure
  You also will use a simulation to build your own procedure for the House-O-Pets course project.
  The next lesson describes a procedure and how it differs from a function.

One of the biggest challenges in PL/SQL programming is finding ways to reduce the complexity of our environment. When faced with a massive problem to solve, the mind is likely to recoil in horror. Where do I start? How can I possibly find a way through that jungle of requirements and features? A human being is not a massively parallel computer. Even the most talented programmers have trouble keeping track of more than seven tasks (plus or minus two) at one time. We need to break down huge, intimidating projects into smaller, more manageable components, and then further decompose those components into individual programs with an understandable scope. We can then figure out how to build and test those programs, after which we can construct a complete application from these building blocks. Whether you use top-down design or some other methodology, there is absolutely no doubt that you will find your way to a high-quality and easily maintainable application by modularizing your code into procedures, functions, and object types.

• Procedure Declaration: A procedure is a subprogram that can take parameters and be called. Generally, you use a procedure to perform an action. A procedure has two parts:
  1. the specification and
  2. the body.
  The specification (spec for short) begins with the keyword PROCEDURE and ends with the 1) procedure name or 2) a parameter list.
  Parameter declarations are optional. Procedures that take no parameters are written without parentheses. The procedure body begins with the keyword IS (or AS) and ends with the keyword END followed by an optional procedure name.
  The procedure body has three parts:
  1. an optional declarative part,
  2. an executable part,
  3. and an optional exception-handling part.
  The declarative part contains declarations of types, cursors, constants, variables, exceptions, and subprograms. These items are local and cease to exist when you exit the procedure. The executable part contains statements that assign values, control execution, and manipulate Oracle data. The exception-handling part contains handlers that deal with exceptions raised during execution. Note that the procedure declaration in a PL/SQL block or package is not the same as creating a procedure in SQL.

Here's an example of an Oracle procedure with all three parts:

CREATE OR REPLACE PROCEDURE calculate_discount (
    p_item_id IN NUMBER,
    p_discount_rate IN NUMBER,
    p_updated_price OUT NUMBER
) AS
    -- Declarative Part (Optional)
    v_original_price NUMBER;  
BEGIN
    -- Executable Part 
    SELECT price 
    INTO v_original_price
    FROM inventory
    WHERE item_id = p_item_id;

    -- Error: What if the item doesn't exist?
    IF v_original_price IS NULL THEN
        RAISE_APPLICATION_ERROR(-20001, 'Item not found');
    END IF;

    p_updated_price := v_original_price * (1 - p_discount_rate);

    -- Exception-Handling Part (Optional)
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Item not found in inventory.');
        p_updated_price := NULL; -- Indicate failure
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Unexpected error occurred.');
        RAISE; -- Re-raise the error for further handling
END calculate_discount;
/

Explanation:

1. Declarative Part: We declare a variable `v_original_price` to temporarily hold the item's price retrieved from the database.
2. Executable Part:
   • Query: Fetches the original price of the item based on the provided `p_item_id`.
   • Error Check: Raises a custom error (`RAISE_APPLICATION_ERROR`) if the item isn't found (original price is NULL).
   • Calculation: Calculates the discounted price and stores it in the output parameter `p_updated_price`.
3. Exception-Handling Part
   • NO_DATA_FOUND: Handles the case where the item doesn't exist, sets the output price to NULL, and provides a log message.
   • OTHERS: Catches any other unexpected errors, logs a message, and re-raises the error to signal that something went wrong.