Procedures are named PL/SQL blocks that perform a specific action. Unlike functions, procedures do not necessarily return a value, although they can modify data and provide output through parameters. They are ideal for encapsulating business logic, performing DML operations (INSERT, UPDATE, DELETE), or interacting with the database in a controlled manner. Using PL/SQL procedures helps you create modular and maintainable Oracle applications.
Example 1: Basic Oracle PL/SQL Procedure
-- This procedure simply displays a greeting message.
CREATE OR REPLACE PROCEDURE greet_user
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello from your first PL/SQL procedure!'); -- Outputting a message
END;
/
Explanation
This example demonstrates the most basic form of a PL/SQL procedure. The CREATE OR REPLACE PROCEDURE statement is used to define or redefine a procedure named greet_user. The IS keyword signifies the start of the declaration section (though none is needed here). The BEGIN and END keywords delineate the executable section, where DBMS_OUTPUT.PUT_LINE is used to print a string to the console. This procedure doesn't take any inputs or return any values; it simply executes a predefined action. This is a common starting point for PL/SQL beginners.
Example 2: Oracle PL/SQL Procedure with IN Parameter
-- This procedure greets a user by their provided name.
CREATE OR REPLACE PROCEDURE greet_person (
p_name IN VARCHAR2 -- IN parameter to accept the person's name
)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello, ' || p_name || '!'); -- Concatenating the greeting with the provided name
END;
/
Explanation
This example introduces an IN parameter in an Oracle PL/SQL procedure. The greet_person procedure accepts a single parameter named p_name of type VARCHAR2. The IN keyword specifies that this parameter is an input parameter, meaning its value is passed into the procedure and cannot be modified within the procedure. This is essential for passing data to your PL/SQL subprograms.
Example 3: Oracle PL/SQL Procedure with OUT Parameter
-- This procedure calculates the area of a rectangle and returns it through an OUT parameter.
CREATE OR REPLACE PROCEDURE calculate_rectangle_area (
p_length IN NUMBER, -- Input parameter for length
p_width IN NUMBER, -- Input parameter for width
p_area OUT NUMBER -- OUT parameter to return the calculated area
)
IS
BEGIN
p_area := p_length * p_width; -- Calculating the area and assigning it to the OUT parameter
END;
/
Explanation
Here, we demonstrate an OUT parameter in an Oracle PL/SQL procedure. The calculate_rectangle_area procedure takes p_length and p_width as IN parameters. The calculated p_area is then assigned to the p_area parameter, which is declared as OUT NUMBER. This means the value of p_area is passed out of the procedure to the calling environment after the procedure completes. This is a common method for returning results from PL/SQL procedures.
Example 4: Oracle PL/SQL Procedure with IN OUT Parameter
-- This procedure increments a counter by a specified amount using an IN OUT parameter.
CREATE OR REPLACE PROCEDURE increment_counter (
p_counter IN OUT NUMBER, -- IN OUT parameter for the counter
p_increment IN NUMBER -- IN parameter for the increment value
)
IS
BEGIN
p_counter := p_counter + p_increment; -- Modifying the IN OUT parameter
END;
/
Explanation
This example showcases an IN OUT parameter in an Oracle PL/SQL procedure. The increment_counter procedure takes p_counter as an IN OUT NUMBER parameter, meaning its initial value is passed into the procedure, and its modified value is passed out of the procedure. The p_increment parameter is an IN parameter. This type of parameter is useful when you need to modify an existing value and return the updated value, making your PL/SQL code more dynamic.
Example 5: Calling an Oracle PL/SQL Procedure from another PL/SQL Block
-- This anonymous block demonstrates calling the previously created procedures.
DECLARE
v_calculated_area NUMBER; -- Variable to hold the OUT parameter value
v_my_counter NUMBER := 10; -- Initial value for the IN OUT parameter
BEGIN
-- Calling the basic procedure
DBMS_OUTPUT.PUT_LINE('--- Calling greet_user ---');
greet_user;
-- Calling the procedure with an IN parameter
DBMS_OUTPUT.PUT_LINE('--- Calling greet_person ---');
greet_person('Alice');
-- Calling the procedure with OUT parameters
DBMS_OUTPUT.PUT_LINE('--- Calling calculate_rectangle_area ---');
calculate_rectangle_area(10, 5, v_calculated_area);
DBMS_OUTPUT.PUT_LINE('Area: ' || v_calculated_area);
-- Calling the procedure with IN OUT parameters
DBMS_OUTPUT.PUT_LINE('--- Calling increment_counter ---');
DBMS_OUTPUT.PUT_LINE('Initial counter: ' || v_my_counter);
increment_counter(v_my_counter, 5);
DBMS_OUTPUT.PUT_LINE('Updated counter: ' || v_my_counter);
END;
/
Explanation
This example demonstrates how to call Oracle PL/SQL procedures from an anonymous PL/SQL block. Each previously defined procedure (greet_user, greet_person, calculate_rectangle_area, and increment_counter) is invoked. For procedures with OUT or IN OUT parameters, a local variable (e.g., v_calculated_area, v_my_counter) is declared to receive the returned or modified value. This is a fundamental concept for PL/SQL programming and building complex database applications.
Parameters (IN, OUT, IN OUT)
Parameters are crucial for making your PL/SQL procedures flexible and reusable. They allow you to pass data into a procedure, receive data back from a procedure, or both. Understanding the different parameter modes is key to writing effective and robust PL/SQL code.
IN Parameters:
Used to pass values into a procedure.
The procedure can read the value but cannot modify it.
They are the default parameter mode if not explicitly specified.
Think of them as input variables for your PL/SQL subprogram.
OUT Parameters:
Used to pass values out of a procedure to the calling environment.
The procedure can assign a value to an OUT parameter, and this value will be visible to the caller.
The initial value of an OUT parameter is ignored when the procedure is called.
These are essential for returning data from PL/SQL procedures.
IN OUT Parameters:
Used to pass values into a procedure and also pass modified values out of the procedure.
The procedure can read the initial value and also assign a new value to the parameter.
They are useful when you need to update an existing variable.
This provides bi-directional communication with your PL/SQL routine.
Example 1: Procedure with IN Parameters (Demonstrating Default Mode)
-- This procedure takes two numbers and displays their sum.
CREATE OR REPLACE PROCEDURE display_sum (
p_num1 NUMBER, -- IN is the default mode, so no explicit IN needed
p_num2 IN NUMBER -- Explicitly specifying IN for clarity
)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('The sum of ' || p_num1 || ' and ' || p_num2 || ' is: ' || (p_num1 + p_num2));
END;
/
Explanation
This example highlights that IN is the default parameter mode in Oracle PL/SQL procedures. p_num1 is implicitly an IN parameter, while p_num2 is explicitly declared as IN. Both parameters serve as inputs to the procedure, which then calculates and displays their sum. This demonstrates a common pattern for passing arguments to PL/SQL subprograms.
Example 2: Procedure Modifying Data Using IN Parameter (Not Allowed)
-- This procedure attempts to modify an IN parameter, which will result in an error.
CREATE OR REPLACE PROCEDURE try_to_modify_in_param (
p_value IN NUMBER
)
IS
BEGIN
-- p_value := p_value + 1; -- This line would cause a PL/SQL compilation error (PLS-00363)
DBMS_OUTPUT.PUT_LINE('The value is: ' || p_value);
END;
/
Explanation
This example intentionally demonstrates an attempt to modify an IN parameter. If the commented line p_value := p_value + 1; were uncommented, the Oracle PL/SQL compiler would raise a PLS-00363: expression 'P_VALUE' cannot be used as an assignment target error. This reinforces the concept that IN parameters are read-only within the procedure. This is a crucial distinction for PL/SQL developers.
Example 3: Procedure with Multiple OUT Parameters
-- This procedure calculates various statistics for a list of numbers.
CREATE OR REPLACE PROCEDURE calculate_stats (
p_num_list IN SYS.ODCINUMBERLIST, -- Input: a list of numbers
p_sum OUT NUMBER, -- Output: sum of numbers
p_avg OUT NUMBER, -- Output: average of numbers
p_count OUT NUMBER -- Output: count of numbers
)
IS
BEGIN
-- Initialize OUT parameters
p_sum := 0;
p_avg := 0;
p_count := 0;
-- Iterate through the list to calculate sum and count
FOR i IN 1..p_num_list.COUNT LOOP
p_sum := p_sum + p_num_list(i);
p_count := p_count + 1;
END LOOP;
-- Calculate average if count is greater than 0
IF p_count > 0 THEN
p_avg := p_sum / p_count;
END IF;
END;
/
Explanation
This example shows a PL/SQL procedure utilizing multiple OUT parameters to return several calculated values. The calculate_stats procedure takes a collection of numbers (SYS.ODCINUMBERLIST) as input and computes the sum, average, and count of these numbers, returning each result through dedicated OUT parameters. This is a powerful way to retrieve multiple results from a single PL/SQL call.
Example 4: Procedure with IN OUT Parameter for State Management
-- This procedure simulates a simple counter, incrementing it and indicating if a limit is reached.
CREATE OR REPLACE PROCEDURE manage_counter_with_limit (
p_current_count IN OUT NUMBER, -- Current count, updated by the procedure
p_limit IN NUMBER, -- The upper limit for the counter
p_limit_reached OUT BOOLEAN -- Indicates if the limit has been reached
)
IS
BEGIN
p_current_count := p_current_count + 1; -- Increment the counter
IF p_current_count >= p_limit THEN
p_limit_reached := TRUE; -- Set flag if limit is reached
ELSE
p_limit_reached := FALSE;
END IF;
END;
/
Explanation
This example demonstrates a practical use of an IN OUT parameter for state management within PL/SQL. The manage_counter_with_limit procedure takes p_current_count as IN OUT, incrementing it on each call. It also uses an OUT parameter p_limit_reached to inform the caller if a predefined limit has been met. This pattern is common in database programming for managing sequences or workflow states.
Example 5: Calling Procedures with Different Parameter Modes and Handling Outputs
-- This anonymous block demonstrates calling procedures with various parameter modes and handling their outputs.
DECLARE
v_total_sum NUMBER;
v_average_val NUMBER;
v_item_count NUMBER;
v_my_number NUMBER := 7; -- Initial value for IN OUT
v_limit_flag BOOLEAN;
TYPE number_array IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
l_numbers number_array;
BEGIN
-- Populate the number list
l_numbers(1) := 10;
l_numbers(2) := 20;
l_numbers(3) := 30;
l_numbers(4) := 40;
l_numbers(5) := 50;
DBMS_OUTPUT.PUT_LINE('--- Calling display_sum (IN Parameters) ---');
display_sum(100, 200);
DBMS_OUTPUT.PUT_LINE('--- Calling calculate_stats (OUT Parameters) ---');
calculate_stats(l_numbers, v_total_sum, v_average_val, v_item_count);
DBMS_OUTPUT.PUT_LINE('Sum: ' || v_total_sum || ', Average: ' || v_average_val || ', Count: ' || v_item_count);
DBMS_OUTPUT.PUT_LINE('--- Calling manage_counter_with_limit (IN OUT and OUT Parameters) ---');
DBMS_OUTPUT.PUT_LINE('Initial number: ' || v_my_number);
FOR i IN 1..5 LOOP
manage_counter_with_limit(v_my_number, 10, v_limit_flag);
DBMS_OUTPUT.PUT_LINE('After call ' || i || ': Number = ' || v_my_number || ', Limit Reached = ' || CASE WHEN v_limit_flag THEN 'TRUE' ELSE 'FALSE' END);
IF v_limit_flag THEN
DBMS_OUTPUT.PUT_LINE('Limit reached, stopping simulation.');
EXIT;
END IF;
END LOOP;
END;
/
Explanation
This comprehensive example demonstrates the calling of Oracle PL/SQL procedures that utilize IN, OUT, and IN OUT parameters. It showcases how to declare variables to capture OUT and IN OUT values. The manage_counter_with_limit call is enclosed in a loop to simulate multiple increments, effectively demonstrating how IN OUT parameters can maintain state across calls within the same session. This is essential for advanced PL/SQL programming.
Calling Procedures
Calling procedures in Oracle PL/SQL involves executing the defined code block. You can call procedures from anonymous PL/SQL blocks, other procedures, functions, triggers, or even from SQL statements (with certain limitations for procedures that perform DML). The way you call a procedure depends on its parameter list.
Example 1: Calling a Procedure with No Parameters
-- This anonymous block calls the simple greet_user procedure.
BEGIN
DBMS_OUTPUT.PUT_LINE('Calling procedure with no parameters:');
greet_user; -- Executing the greet_user procedure
END;
/
Explanation
This example demonstrates the simplest form of calling a PL/SQL procedure: invoking one that does not require any parameters. You simply write the procedure's name followed by a semicolon. This is the starting point for understanding PL/SQL procedure execution.
Example 2: Calling a Procedure with Positional Notation
-- This anonymous block calls greet_person using positional notation for its IN parameter.
BEGIN
DBMS_OUTPUT.PUT_LINE('Calling procedure with positional notation:');
greet_person('Charlie'); -- Passing 'Charlie' as the first (and only) parameter
END;
/
Explanation
This example illustrates positional notation when calling an Oracle PL/SQL procedure. The value 'Charlie' is passed as the first (and only) argument, corresponding to the p_name parameter defined in the greet_person procedure. The order of the arguments must match the order of the parameters in the procedure's definition. This is a common and straightforward method for passing arguments to PL/SQL subprograms.
Example 3: Calling a Procedure with Named Notation
-- This anonymous block calls calculate_rectangle_area using named notation.
DECLARE
v_area NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE('Calling procedure with named notation:');
calculate_rectangle_area(p_width => 7, p_length => 12, p_area => v_area); -- Using named notation for clarity and flexibility
DBMS_OUTPUT.PUT_LINE('Calculated area (using named notation): ' || v_area);
END;
/
Explanation
This example demonstrates named notation for calling an Oracle PL/SQL procedure. Instead of relying on parameter order, you explicitly specify the parameter name followed by => and then its value (e.g., p_width => 7). This offers greater readability, especially for procedures with many parameters, and makes the call less susceptible to errors if the parameter order in the procedure definition changes. This is highly recommended for robust PL/SQL development.
Example 4: Calling a Procedure with Mixed Notation
-- This anonymous block calls a hypothetical procedure using mixed notation (positional then named).
-- (Assuming a procedure defined as: PROCEDURE mixed_params(p_val1 IN NUMBER, p_val2 IN VARCHAR2, p_val3 OUT BOOLEAN))
CREATE OR REPLACE PROCEDURE mixed_params(
p_val1 IN NUMBER,
p_val2 IN VARCHAR2,
p_val3 OUT BOOLEAN
)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Val1: ' || p_val1 || ', Val2: ' || p_val2);
p_val3 := (p_val1 > 10);
END;
/
DECLARE
v_result BOOLEAN;
BEGIN
DBMS_OUTPUT.PUT_LINE('Calling procedure with mixed notation:');
mixed_params(15, p_val2 => 'Mixed', p_val3 => v_result); -- Positional for first, named for others
DBMS_OUTPUT.PUT_LINE('Result (from mixed notation): ' || CASE WHEN v_result THEN 'TRUE' ELSE 'FALSE' END);
END;
/
Explanation
This example illustrates mixed notation when calling Oracle PL/SQL procedures. You can use a combination of positional and named notation, but all positional arguments must come before any named arguments. In this example, 15 is passed positionally for p_val1, while p_val2 and p_val3 are passed using named notation. This can sometimes be useful for procedures with a few leading parameters that are always passed in a specific order, followed by optional or less frequently used parameters.
Example 5: Calling a Procedure from a SQL Context (using CALL)
-- You cannot directly call procedures with OUT/IN OUT parameters from a simple SQL SELECT.
-- However, you can call them from SQL*Plus or SQL Developer using EXECUTE or CALL.
-- Example using EXECUTE in SQL*Plus or SQL Developer:
-- EXEC greet_user;
-- EXEC greet_person('David');
-- For procedures with OUT/IN OUT parameters, you'd typically need a PL/SQL block in SQL*Plus/SQL Developer:
DECLARE
v_my_area NUMBER;
BEGIN
calculate_rectangle_area(20, 10, v_my_area);
DBMS_OUTPUT.PUT_LINE('Area calculated from SQL context: ' || v_my_area);
END;
/
-- Calling a procedure from a SQL statement is generally limited to those that return a value
-- (i.e., functions or procedures that are called via a wrapper function)
-- or for DML/DDL procedures that don't return values.
-- The `CALL` statement (introduced in Oracle 10g) can execute PL/SQL procedures from SQL.
-- This is often used for batch processing or when procedures are exposed as table functions.
-- Example of CALL statement (for a procedure that doesn't return anything):
-- CALL greet_user();
-- CALL greet_person('Eve');
Explanation
This example demonstrates calling Oracle PL/SQL procedures from a SQL context. While direct calls from SELECT statements are primarily for functions, procedures can be invoked using the EXECUTE command in SQL*Plus/SQL Developer or the CALL statement (available since Oracle 10g) directly from SQL. For procedures with OUT or IN OUT parameters, you typically need to embed the procedure call within a PL/SQL block to handle the output variables. This distinction is important for integrating PL/SQL with SQL queries.