What is a Procedure in an Oracle Database?

Procedure in an Oracle Database

The Oracle database contains strong, reusable bits of computer logic called stored procedures. In essence, they are named pieces of PL/SQL code or even statements in Java or C that carry out particular operations. Instead of having it dispersed over several apps, this approach enables you to centralise application logic and business rules within the database. This centralisation has a number of noteworthy advantages:

1. Reusability and Consistency: Once a procedure is developed, it can be called by several applications, guaranteeing that business rules are implemented uniformly over all interfaces. Application development is made simpler and code redundancy is decreased as a result.
2. Improved Performance: Compiles stored procedures and stores them in the database in their compiled version. By lowering parsing and compilation overhead, Oracle’s pre-compiled code is executed when a procedure is called, greatly enhancing efficiency. Performance can also be significantly improved in client-server or three-tier architectures by shifting complex processing from the client application to the database server.
3. Enhanced Security: Procedures can serve as “gatekeepers” to the underlying data tables, which improves security. You can only provide users the EXECUTE privilege on a procedure rather than giving them direct access to tables. The process, which is owned by a distinct schema, is then granted the privileges required to carry out DML operations, guaranteeing data integrity and allowing for more precise access control.
4. Reduced Network Traffic: Encapsulating complex tasks into a single stored procedure eliminates the need for several SQL statements delivered by a client application. By doing this, network traffic is decreased because there are fewer round trips between the client and the server.
5. Maintainability: Updates and modifications are simpler to distribute and manage when business logic is centrally located. Every application that uses a procedure is immediately updated when it is modified.
6. Procedural Language/Structured Query Language, or PL/SQL, is the procedural extension for SQL and the Oracle relational database developed by Oracle Corporation. Programs written in PL/SQL are organised into blocks, which usually include sections for declarations, executable commands, and optional exception handling. “Named block” is a sort of stored procedure.

CREATE PROCEDURE Syntax

A stored procedure is created using the CREATE PROCEDURE statement. The procedure name, arguments, and PL/SQL block that makes up the procedure’s body are all defined in the basic syntax:

CREATE [OR REPLACE] PROCEDURE [schema.]procedure_name
    [(parameter_name [IN | OUT | IN OUT] DATATYPE [, ...])]
[AUTHID {CURRENT_USER | DEFINER}]
{IS | AS}
    [<declarations>]
BEGIN
    <procedure_body>
[EXCEPTION
    <exception_handling_statements>]
END [procedure_name];
/

Let’s examine each of the essential elements:

• OR REPLACE: If the procedure is already in place, you can use this optional clause to recreate it without having to DROP it first. The CREATE statement won’t work if you leave out OR REPLACE and the procedure is already in place.
• [schema.]procedure_name: Indicates the procedure’s name, optionally qualified by the schema to which it belongs. Your current schema is used to generate the procedure if the schema is left out.
• parameter_name [IN | OUT | IN OUT] DATATYPE: Here, you specify the parameters that the process will take in and return. A datatype, a mode (IN, OUT, or IN OUT), and a name are assigned to each parameter.
• AUTHID {CURRENT_USER | DEFINER}: The procedure’s “execution rights” are established by the AUTHID {CURRENT_USER | DEFINER} clause.
  • DEFINER (default): The procedure runs with the owner’s (the definer’s) privileges. This is helpful when a user only has the ability to EXECUTE the procedure and not the underlying tables, allowing for regulated access to data.
  • CURRENT_USER: The user who calls the procedure (the invoker) has the privileges to carry it out.
• IS | AS: These terms can be used interchangeably to introduce the procedure body’s PL/SQL block. AS essentially takes the role of the DECLARE keyword that would be used in an anonymous block when constructing a standalone process.
• <declarations>: This optional section specifies constants, local variables, and cursors that are only utilised inside the procedure.
• BEGIN … END: This is the required Executable Commands portion, which includes the PL/SQL and SQL statements that carry out the logic of the procedure. At least one executable statement must be present in a procedure.
• EXCEPTION …: This optional section outlines exception handlers for handling potential execution issues.

Parameter Modes: IN, OUT, and IN OUT

In order to transfer data between a calling application and a stored procedure, parameters are essential. Three parameter modes can be used with PL/SQL:

1. IN Parameters
   • The procedure receives values from the IN parameters.
   • Within the process, they function similarly to constants; their values cannot be assigned or altered.
   • If no mode is given, IN is the default mode.
   • A literal, constant, variable, or expression can be the actual parameter that is provided.
2. OUT Parameters
   • OUT parameters are used to return values to the calling environment from the method.
   • During the procedure, an OUT parameter functions similarly to an uninitialised variable; it is worthless until the procedure is successfully finished. Any assignments are made to a local copy, which, after successful completion, is moved to the actual parameter.
   • Oracle requires a receptacle to hold the outgoing value, hence the actual parameter that corresponds to an OUT formal parameter must be a variable rather than a constant, literal, or expression.
3. IN OUT Parameters
   • The IN OUT parameters enable you to return (perhaps altered) values from the procedure as well as send values into it.
   • An IN OUT parameter functions within the procedure like an initialised variable; its initial value is changeable and used.
   • For an IN OUT formal parameter, the actual parameter must likewise be a variable.

NOCOPY Hint: Oracle usually creates two copies of the OUT and IN OUT parameters: one when the procedure is called and another when it returns. This is especially true when working with large data structures like collections or records. By asking the PL/SQL compiler to transmit the argument by reference rather than by value, the NOCOPY hint can increase speed by preventing these copying processes.

Example: Employee Salary Adjustment Procedure

Let’s create a simple EMPLOYEES table and then a stored procedure to adjust an employee’s salary based on an adjustment factor. This procedure will demonstrate the use of IN, IN OUT, and OUT parameters. The IN OUT parameter will also include a simple business rule to cap the adjustment factor.

First, we’ll create the EMPLOYEES table and insert some sample data:

-- Create the EMPLOYEES table
CREATE TABLE EMPLOYEES (
    EMPLOYEE_ID    NUMBER(6) PRIMARY KEY,
    FIRST_NAME     VARCHAR2(20),
    LAST_NAME      VARCHAR2(25) NOT NULL,
    EMAIL          VARCHAR2(25) NOT NULL UNIQUE,
    PHONE_NUMBER   VARCHAR2(20),
    HIRE_DATE      DATE NOT NULL,
    JOB_ID         VARCHAR2(10) NOT NULL,
    SALARY         NUMBER(8,2) NOT NULL
);

-- Insert sample values
INSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY)
VALUES (100, 'Steven', 'King', 'SKING', '515.123.4567', TO_DATE('17-JUN-2003', 'DD-MON-YYYY'), 'AD_PRES', 24000);

INSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY)
VALUES (101, 'Neena', 'Kochhar', 'NKOCHHAR', '515.123.4568', TO_DATE('21-SEP-2005', 'DD-MON-YYYY'), 'AD_VP', 17000);

INSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY)
VALUES (102, 'Lex', 'De Haan', 'LDEHAAN', '515.123.4569', TO_DATE('13-JAN-2001', 'DD-MON-YYYY'), 'AD_VP', 17000);

COMMIT;

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY FROM EMPLOYEES ORDER BY EMPLOYEE_ID;

Initial Table Data:

EMPLOYEE_ID FIRST_NAME LAST_NAME      SALARY
----------- ---------- -------------- ----------
        100 Steven     King             24000.00
        101 Neena      Kochhar          17000.00
        102 Lex        De Haan          17000.00

Now, let’s create the ADJUST_EMPLOYEE_SALARY procedure:

CREATE OR REPLACE PROCEDURE ADJUST_EMPLOYEE_SALARY (
    p_employee_id       IN EMPLOYEES.EMPLOYEE_ID%TYPE,
    p_adjustment_factor IN OUT NUMBER,  -- Input factor, can be modified and returned
    p_calculated_salary OUT EMPLOYEES.SALARY%TYPE  -- Output new salary
)
AS
    v_current_salary EMPLOYEES.SALARY%TYPE;
    v_max_factor     CONSTANT NUMBER := 1.10; -- Maximum allowed adjustment factor (10% increase)
BEGIN
    -- Retrieve current salary for the employee
    SELECT SALARY
    INTO v_current_salary
    FROM EMPLOYEES
    WHERE EMPLOYEE_ID = p_employee_id;

    -- Implement a business rule for the adjustment factor (IN OUT parameter)
    -- If the requested factor is too high, cap it at v_max_factor
    IF p_adjustment_factor > v_max_factor THEN
        DBMS_OUTPUT.PUT_LINE('Warning: Requested adjustment factor ' || p_adjustment_factor || ' exceeds maximum allowed (' || v_max_factor || '). Capping at ' || v_max_factor);
        p_adjustment_factor := v_max_factor; -- Modify the IN OUT parameter
    END IF;

    -- Calculate the new salary
    p_calculated_salary := v_current_salary * p_adjustment_factor;

    -- Update the employee's salary in the table
    UPDATE EMPLOYEES
    SET SALARY = p_calculated_salary
    WHERE EMPLOYEE_ID = p_employee_id;

    COMMIT;

    DBMS_OUTPUT.PUT_LINE('Employee ' || p_employee_id || ': Salary updated from ' || v_current_salary || ' to ' || p_calculated_salary || ' using actual factor ' || p_adjustment_factor);

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Error: Employee ' || p_employee_id || ' not found.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);
        ROLLBACK;
END ADJUST_EMPLOYEE_SALARY;
/

Executing the Procedure and Viewing Output

To see the output from DBMS_OUTPUT.PUT_LINE statements within the PL/SQL procedure, you must enable SERVEROUTPUT in your SQL*Plus or SQL Developer session:

SET SERVEROUTPUT ON SIZE 1000000; -- Set buffer size to handle potentially large output

-- Declare variables to hold OUT and IN OUT parameter values
DECLARE
    v_emp_id        EMPLOYEES.EMPLOYEE_ID%TYPE := 100;
    v_factor_in_out NUMBER := 1.15; -- Initial requested adjustment factor (15% increase)
    v_new_salary    EMPLOYEES.SALARY%TYPE;
BEGIN
    DBMS_OUTPUT.PUT_LINE('--- Before Procedure Call ---');
    DBMS_OUTPUT.PUT_LINE('Initial requested factor: ' || v_factor_in_out);

    -- Execute the procedure
    ADJUST_EMPLOYEE_SALARY(
        p_employee_id       => v_emp_id,
        p_adjustment_factor => v_factor_in_out,
        p_calculated_salary => v_new_salary
    );

    DBMS_OUTPUT.PUT_LINE('--- After Procedure Call ---');
    DBMS_OUTPUT.PUT_LINE('Final adjustment factor (returned by procedure): ' || v_factor_in_out);
    DBMS_OUTPUT.PUT_LINE('Calculated new salary (returned by procedure): ' || v_new_salary);
END;
/

-- Verify the persistent change in the table
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY FROM EMPLOYEES WHERE EMPLOYEE_ID = 100;

-- Another call, with a factor within limits
DECLARE
    v_emp_id        EMPLOYEES.EMPLOYEE_ID%TYPE := 101;
    v_factor_in_out NUMBER := 1.05; -- 5% increase
    v_new_salary    EMPLOYEES.SALARY%TYPE;
BEGIN
    DBMS_OUTPUT.PUT_LINE('--- Before Procedure Call (Employee 101) ---');
    DBMS_OUTPUT.PUT_LINE('Initial requested factor: ' || v_factor_in_out);

    ADJUST_EMPLOYEE_SALARY(
        p_employee_id       => v_emp_id,
        p_adjustment_factor => v_factor_in_out,
        p_calculated_salary => v_new_salary
    );

    DBMS_OUTPUT.PUT_LINE('--- After Procedure Call (Employee 101) ---');
    DBMS_OUTPUT.PUT_LINE('Final adjustment factor (returned by procedure): ' || v_factor_in_out);
    DBMS_OUTPUT.PUT_LINE('Calculated new salary (returned by procedure): ' || v_new_salary);
END;
/

-- Verify all salaries
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY FROM EMPLOYEES ORDER BY EMPLOYEE_ID;

Example Output:

--- Before Procedure Call ---
Initial requested factor: 1.15
Warning: Requested adjustment factor 1.15 exceeds maximum allowed (1.1). Capping at 1.1
Employee 100: Salary updated from 24000 to 26400 using actual factor 1.1
--- After Procedure Call ---
Final adjustment factor (returned by procedure): 1.1
Calculated new salary (returned by procedure): 26400

PL/SQL procedure successfully completed.

EMPLOYEE_ID FIRST_NAME LAST_NAME      SALARY
----------- ---------- -------------- ----------
        100 Steven     King             26400.00

--- Before Procedure Call (Employee 101) ---
Initial requested factor: 1.05
Employee 101: Salary updated from 17000 to 17850 using actual factor 1.05
--- After Procedure Call (Employee 101) ---
Final adjustment factor (returned by procedure): 1.05
Calculated new salary (returned by procedure): 17850

PL/SQL procedure successfully completed.

EMPLOYEE_ID FIRST_NAME LAST_NAME      SALARY
----------- ---------- -------------- ----------
        100 Steven     King             26400.00
        101 Neena      Kochhar          17850.00
        102 Lex        De Haan          17000.00

In this example, for EMPLOYEE_ID 100, we initially requested a 15% increase (v_factor_in_out = 1.15). However, due to the business rule within the procedure, this factor was capped at 10% (v_max_factor = 1.10), and the p_adjustment_factor (an IN OUT parameter) was updated to 1.10 before being used in the calculation and returned to the calling block. The p_calculated_salary (an OUT parameter) correctly received the new salary. For EMPLOYEE_ID 101, the requested factor was within limits, so it was used as is.

Stored procedures are a fundamental part of building robust, maintainable, and high-performance applications with Oracle Database, offering a modular approach to database programming.