Procedures, Functions, and Packages

For code reuse and software modularization, using procedure/function and package is very common.

What are procedures, functions, and packages?

Why do we need the procedures, functions, and packages?

•      They can be stored in the database and run when appropriate.
•      They share many of the same properties in the other languages like C
        -- more reusable, more manageable, more readable, more reliable.

Types of procedures or functions
Local procedures/functions (local subprograms),
Stored procedures/functions (stored subprograms)

                                                                  Creating a Procedure

General Syntax for Stored Procedure or Local Procedure
[CREATE [OR REPLACE]] PROCEDURE procedure_name [(argument [{IN | OUT | IN OUT}]
                                                                                                     argument_type, …)] {IS | AS}
Procedure_body;

Stored procedure is a procedure stored in a database (e.g., If a procedure is stored in a database, it becomes like a library function).

Local procedure is not stored in a database but used within a program (e.g., a typical procedure that can be defined within a program like in C/C++). If you omit CREATE OR REPLACE, the PROCEDURE becomes a local procedure.

• Procedure_body is a PL/SQL block (BEGIN…END section)
• Creating a procedure is a DDL operation -- so implicit COMMIT is done
• Either IS or AS can be used
• Parameter mode (Ada style of call-by-value or reference): IN (read-only), OUT (write-only value is ignored and NOT    allowed at RHS value), IN OUT (read-write), default is IN mode.
• Through parameters, procedure can return values
• Note that there is no DECLARE keyword used.

Constraints on Formal Parameters

• It is illegal to constrain parameters with a length, precision, and scale, e.g., VARCHAR2(20), NUMBER(2) except for    anchored types such as %TYPE
• Parameter can have default values using DEFAULT or :=                        -- in this case, you can omit certain argument.

=>   If there are no parameters in the procedure, no parenthesis is needed in the procedure head.

Defining local procedures

• Local subprograms are defined in the declarative section.
• The keyword CREATE [OR REPLACE] is omitted, and start with PROCEDURE keyword.

How to call a procedure

procedure_name(arguments);                          -- within the PL/SQL block as an executable statement
apply_discount(new_company_id, 0.15);        -- 15% discount

=>    If the procedure does not have any parameters, then you must call the procedure without any parameters: e.g.,         display_store_summary; -- no parenthesis needed

An example PL/SQL block that declares a Local Procedure

DECLARE

PROCEDURE apply_discount (company_id IN company.company_id%TYPE, discount_in IN NUMBER) IS
min_discount CONSTANT NUMBER := .05;
max_discount CONSTANT NUMBER := .25;
invalid_discount EXCEPTION;

BEGIN
                IF discount_in BETWEEN min_discount AND max_discount THEN
                    UPDATE item
                    SET item_amount := item_amount*(1-discount_in);
                    WHERE EXISTS (SELECT 'x' FROM order
                                         WHERE order.order_id = item.order_id AND
                                                         order.company_id=company_id_in);

                     IF SQL%ROWCOUNT = 0 THEN
                          RAISE NO_DATA_FOUND;
                    END IF;
              ELSE
                   RAISE invalid_discount;
              END IF;

EXCEPTION
           WHEN invalid_discount THEN
                     DBMS_OUTPUT.PUT_LINE('The specified discount is invalid.');
           WHEN NO_DATA_FOUND THEN
                     DBMS_OUTPUT.PUT_LINE('No orders in the system for company:' || TO_CHAR(company_id_in));
END apply_discount;
BEGIN
apply_discount(‘c001’, 0.3); -- a local procedure call
END;

Debugging Procedures

SQL*Plus SHOW ERRORS command will display all of the errors (e.g., line and column number for each error as well as text error message) associated with the most recently created procedural object. This command will check the USER_ERRORS data dictionary view for the errors associated with the most recent compilation attempt for that procedural object.