Procedures and Functions

Early developers viewed their programs as merely lists of command lines. Their focus was on sequencing instructions in order to accomplish tasks under intention. Organizing code lines into program units received less attention, leading to programs that prioritized functionality over maintainability and reusability. These programs, often referred to as "spaghetti code," became tangled and difficult to understand as they grew in complexity.

A turning point came in 1968 with the introduction of structured programming. In his influential paper, Go To Statement Considered Harmful, Edsger W. Dijkstra argued against the overuse of GO TO statements and pointed out that frequent "jumps" obscure the overall program flow and logic. To address this, Dijkstra proposed an alternative approach where programs are decomposed into smaller, self-contained subprograms, such as procedures and functions, each with well-defined interfaces. Once compiled, these subprograms can be invoked in another program, facilitating reusability and modularity.

In database programming using PL/SQL language, any serious project involves creating and maintaining procedures and functions. Indeed, an anonymous block is rarely used for unit testing. Unlike anonymous blocks which have to be parsed and compiled each time they are submitted, PL/SQL subprograms can be invoked by their given names without needing to be recompiled. 

Two major types of PL/SQL subprograms are:

• Procedures: Named code blocks designed to perform a specific task through a predefined sequence of instructions. They can accept arguments for execution and can be invoked independently to execute the defined task.
• Functions: Similar to procedures, these are also named PL/SQL blocks. However, unlike procedures, PL/SQL functions must return single output values and are typically used in SQL statements.

PL/SQL Procedures

The standard textbook definition of PL/SQL procedures is that they "do something," as opposed to functions, which "return a value." Procedures are ideal for defining tasks that you expect to be done repeatedly. They may or may not return a value, but the focus is on executing tasks rather than on the output value. For example, consider a database designed for managing customer orders. In this scenario, the database should repeatedly insert a row every time a new order is placed and update the inventory levels accordingly. The sequence of related tasks can be grouped together under a specific name, say proc_place_new_order, and invoked whenever a new order is made. This approach of handling tasks is more structured, reusable, and maintainable, compared to relying on anonymous blocks.

The basic syntax to create a procedure is as follows:

CREATE OR REPLACE PROCEDURE procedure_name (
    parameter1 [IN | OUT | IN OUT] data_type [:= default_value]
    parameter2 [IN | OUT | IN OUT] data_type [:= default_value]
    ...
)
{AS | IS}
    -- Declare variables and/or constants
BEGIN  

    -- Statements to run

[EXCEPTION WHEN -- Exception handling section]

END [procedure_name];

Where:

• CREATE OR REPLACE PROCEDURE: Used to create a new procedure or update an existing one. While you can create one without using OR REPLACE, including it allows you to recompile the procedure with the same name after making modifications. So, in practice, the CREATE keyword is generally used in conjunction with OR REPLACE for both creating and maintaining purpose.
• Parameters: Placeholders for the arguments that will be passed and/or returned at runtime. If you specify a parameter with no keyword, it defaults to an input parameter.
• IN: Specifies an input parameter, which provides values at runtime. 
• OUT: Specifies an output parameter, used to return values from the procedure.
• IN OUT: Specifies a parameter that serves both as an input to and an output from the procedure.

To further illustrate, let's create proc_place_new_order mentioned earlier:

In the CO schema, the shipments table has five columns: shipment_id, store_id, customer_id, delivery_address, and shipment_status. The p_update_shipping_stat procedure takes one parameter parm_shipment_id and update the shipment_status column value based on its current value.

This procedure can be recompiled after modification.

Invoking Procedures

Now, let's run the procedure. Currently, the shipment_status of the shipment_id = 586 is 'SHIPPED':

However, after running the procedure, we see that the shipment_status value has been updated to 'IN-TRANSIT':

Note that you can also use EXEC keyword instead of EXECUTE.

In PL/SQL procedures, parameters can be passed using two primary methods: positional and named parameters. Both methods provide flexibility in how values are supplied to procedures, but they differ in their approach and usage.

Positional parameters are the most straightforward method for passing arguments to a procedure. When using positional parameters, you provide the values in the exact order in which the parameters are defined in the procedure. This method relies on the sequence of arguments to match the sequence of parameters. For example:

In this example, the incorrect order of the arguments results in values being inserted incorrectly. This issue is especially problematic when dealing with procedures that have many parameters, as maintaining the correct order of arguments can become challenging. 

To avoid such issues, you can pass named parameters. Named parameters allow you to specify the parameter names explicitly in the procedure call, regardless of their order. This method enhances readability and reduces errors by making it clear which value is being assigned to which parameter. For example:

You can also mix both methods, but all positional parameters must precede any named parameters. Otherwise, Oracle will throw an error. For example:

due to the wrong order of the arguments, the values are inserted incorrectly. Particularly, when there are too many parameters, it could be challenging to maintain the exact order of the arguments.

Once compiled, a procedure can be invoked from within another code block by calling its name. For example:

Observe that the p_hello_world procedure is invoked from within an anonymous code block and executed as intended. However, unlike functions, PL/SQL procedures cannot be embedded in an SQL statement.

Procedures with IN and IN OUT Parameters

Although procedures can output values using OUT or IN OUT keywords, they do not directly store a value in a return variable as functions do. Procedures and functions have distinct roles: functions are meant to return a value, while procedures are intended to modify the state of the system or database. Generally, it's not considered best practice to use procedures for returning values, as blurring these roles can lead to confusion. The only exception would be

PL/SQL Functions

A function is a subprogram that takes parameters, performs specific operations on them, and returns a result. In PL/SQL, you can define a function using the following general syntax:

CREATE OR REPLACE FUNCTION function_name (parameter1 data_type, parameter2 data_type, ...)
    RETURN data_type
IS[AS]
    -- Declare variables and constants for the executable section
BEGIN
    -- Statements to execute

RETURN output;
[EXCEPTION -- Exception-handling section]
END function_name;

Where:

• CREATE OR REPLACE FUNCTION: Technically, you can create a function using CREATE FUNCTION. However, CREATE OR REPLACE FUNCTION allows you to recompile the function after updates.
• Parameters: Values that will be passed into the function. In PL/SQL functions, parameters should be specified as parameter_name data_type pairs.
• RETURN Data Type: Defines data type of the returning values.
• RETURN Output: Specifies the object containing the returning value.

For example:

Once a function is compiled, the function can be called in another PL/SQL block or SELECT statement. For example:

Note that even if the function doesn't take any parameters, you cannot omit the parentheses. For example:

L