Function and Stored Procedure in PL/SQL : 6

Function

A function is a subprogram that returns a single value and can be used in SQL queries or PL/SQL blocks.

✅ Returns a value using the RETURN statement.
✅ Can be used in SQL queries.
✅ Can accept parameters (optional).
✅ Used for calculations and returning values.

Example of a Function

CREATE OR REPLACE FUNCTION get_annual_salary(emp_id NUMBER) 
RETURN NUMBER 
IS 
   monthly_salary NUMBER;
   annual_salary NUMBER;
BEGIN 
   SELECT salary INTO monthly_salary FROM employees WHERE employee_id = emp_id;
   annual_salary := monthly_salary * 12;
   RETURN annual_salary;
END get_annual_salary;

Calling a Function

SELECT get_annual_salary(101) FROM dual;

DECLARE
  salary NUMBER;
BEGIN
  salary := get_annual_salary(101);
  DBMS_OUTPUT.PUT_LINE('Annual Salary: ' || salary);
END;

Stored Procedure

A stored procedure is a subprogram that performs an action but does not return a value directly.

✅ Does not return a value directly.
✅ Can accept parameters (IN, OUT, IN OUT).
✅ Used for executing business logic, updating records, or performing tasks.
✅ Cannot be used directly in SQL queries.

Example of a Procedure

CREATE OR REPLACE PROCEDURE increase_salary(
   emp_id NUMBER, 
   increment_percentage NUMBER
) 
IS 
BEGIN 
   UPDATE employees 
   SET salary = salary + (salary * increment_percentage / 100) 
   WHERE employee_id = emp_id;

   COMMIT; 
END increase_salary;

Calling a Procedure

BEGIN
   increase_salary(101, 10); -- Increases salary by 10% for employee 101
END;

EXEC increase_salary(101, 10);

Parameters in Functions and Procedures

• IN Parameter: Used to pass values into the subprogram.

• OUT Parameter: Used to return values from a procedure. We can also use it in function but can’t use that function within sql query.

• INOUT Parameter: Used to pass values into and out of a procedure.IN OUT modifies the variable inside the procedure and updates its value.

CREATE OR REPLACE PROCEDURE increase_salaries(
    v_salary_increase IN OUT NUMBER,
    v_department_id IN PLS_INTEGER,
    v_affected_employee_count OUT NUMBER
)
IS
   v_sal_inc NUMBER := 0;
BEGIN
   v_affected_employee_count := 0;

   UPDATE employees
   SET salary = salary * v_salary_increase
   WHERE department_id = v_department_id
   RETURNING COUNT(*) INTO v_affected_employee_count;

   COMMIT;
   v_salary_increase := v_sal_inc / v_affected_employee_count;
END increase_salaries;

Named - Mixed Notations and Default option

Default Values for Parameters

• Default values allow calling a procedure without specifying all parameters.

• Syntax options for specifying default values:

CREATE OR REPLACE PROCEDURE print_message(msg IN VARCHAR2 DEFAULT 'Default Message') IS
BEGIN
    DBMS_OUTPUT.PUT_LINE(msg);
END;

CREATE OR REPLACE PROCEDURE print_message(msg IN VARCHAR2 := 'Default Message') IS
BEGIN
    DBMS_OUTPUT.PUT_LINE(msg);
END;

EXEC print_message('Hello!');  -- Prints "Hello!"
EXEC print_message;             -- Prints "Default Message"
EXEC print_message(NULL);       -- Prints nothing (NULL is explicitly passed)

CREATE OR REPLACE PROCEDURE add_job(
    job_id IN VARCHAR2,
    job_title IN VARCHAR2,
    min_salary IN NUMBER DEFAULT 1000,
    max_salary IN NUMBER DEFAULT NULL)
IS
BEGIN
    INSERT INTO jobs VALUES (job_id, job_title, min_salary, max_salary);
    DBMS_OUTPUT.PUT_LINE('The job ' || job_title || ' is inserted!');
END;

Named Notation

• Named notation allows passing parameters in any order by explicitly naming them.

EXEC add_job(job_id => 'IT_DIR5', job_title => 'IT Director', max_salary => 10000);

Mixed Notation

• A combination of positional and named notation.

• Rules:

  • Positional parameters must come first.

  • Named parameters must follow positional ones.

EXEC add_job('IT_DIR6', 'IT Director', max_salary => 15000);

-- Incorrect Usage
EXEC add_job(min_salary => 2000, 'IT Director'); -- ERROR

Local Subprograms in PL/SQL

Subprograms, such as procedures and functions, help reduce code duplication by allowing us to reuse logic across different parts of a program. These subprograms are usually stored in the database at the schema level, making them stored procedures or stored functions. However, in some cases, keeping these subprograms permanently in the database may not be necessary or useful.

Drawbacks of Stored Subprograms

1. Unnecessary Storage - Some subprograms are only needed for specific jobs and won’t be used again after the task is completed.

2. Schema Clutter - Storing every subprogram at the schema level can create unnecessary clutter, making it difficult to find relevant procedures and functions.

What Are Local Subprograms?

Local subprograms are subprograms declared inside another subprogram or inside an anonymous block. These subprograms exist only during the execution of the surrounding block and are automatically deleted afterward.

Benefits of Local Subprograms

1. Avoids Schema Clutter - Since they are not stored in the database, local subprograms reduce unnecessary functions and procedures in the schema.

2. Reduces Code Duplication - Allows reuse of logic within the same block or subprogram.

3. Improves Readability - Helps organize the code better by grouping related logic together.

4. No Extra Privileges Needed - Unlike stored procedures, local subprograms do not require additional privileges to be executed within the same block.

Limitations of Local Subprograms

1. Limited Scope - Local subprograms are accessible only within the block where they are declared. They cannot be accessed outside that block.

2. Declaration Order Matters - If a function is called inside a procedure, it must be declared before the procedure to avoid execution errors.

Creating a Local Subprogram

Unlike stored procedures, local subprograms do not use the CREATE OR REPLACE command. Instead, they are defined directly inside the declaration section of an anonymous block or another subprogram.

DECLARE
    -- Function to retrieve an employee record
    FUNCTION get_emp(emp_num employees.employee_id%TYPE)
    RETURN employees%ROWTYPE IS
        emp employees%ROWTYPE;
    BEGIN
        SELECT * INTO emp FROM employees WHERE employee_id = emp_num;
        RETURN emp;
    END get_emp;

    -- Procedure to insert high-paid employees
    PROCEDURE insert_high_paid_emp(emp_id employees.employee_id%TYPE) IS
        emp employees%ROWTYPE;
    BEGIN
        emp := get_emp(emp_id); -- Fetch employee record
        INSERT INTO emps_high_paid VALUES emp; -- Insert into table
    END insert_high_paid_emp;
BEGIN
    -- Loop through employees and insert those with salary > 15000
    FOR r_emp IN (SELECT * FROM employees) LOOP
        IF r_emp.salary > 15000 THEN
            insert_high_paid_emp(r_emp.employee_id);
        END IF;
    END LOOP;
END;

Position of Local Subprograms in Declaration Area

• Local subprograms must be declared last in the declaration section.

• You cannot declare variables after defining a subprogram.

• Example of an incorrect order:

DECLARE
    FUNCTION get_emp(emp_num employees.employee_id%TYPE) RETURN employees%ROWTYPE;
    emp_id NUMBER;  -- This will cause an error!
BEGIN
    NULL;
END;

• The correct order is to declare emp_id before defining get_emp.

Overloading Subprograms in PL/SQL

Overloading works within packages and local subprograms but not in standalone subprograms.

• Standalone subprograms: Each function or procedure in a schema must have a unique name.

• Package or local subprograms: Multiple functions with the same name can exist as long as their parameter lists differ.

Rules for Overloading

To successfully overload a subprogram, the functions/procedures must have:

• Different number of parameters

• Different parameter data types

• Different parameter order (if types differ)

However, the following restrictions apply:

• Functions cannot be overloaded solely by changing the return type.

• Data types that belong to the same family (e.g., NUMBER vs. INT) cannot be used for overloading.

• Incorrect overloading results in runtime errors rather than compile-time errors.

How the Database Determines Which Function to Execute

1. Searches the innermost scope first, such as local subprograms.

2. Checks for an exact match based on parameters.

3. If multiple matches exist, it resolves based on parameter data types.

4. If no match is found, it searches outer scopes (e.g., packages, global schema functions).

Example:

DECLARE
    emp_rec employees%ROWTYPE;
    FUNCTION get_emp (emp_email employees.email%TYPE) RETURN employees%ROWTYPE IS
    v_emp employees%ROWTYPE;
    BEGIN
      SELECT * INTO v_emp FROM employees WHERE email = emp_email;
      RETURN v_emp;
    END get_emp;

    FUNCTION get_emp (f_name employees.first_name%TYPE, l_name VARCHAR2) RETURN employees%ROWTYPE IS
      v_emp employees%ROWTYPE;
    BEGIN
      SELECT * INTO v_emp FROM employees WHERE first_name = f_name AND last_name = l_name;
      RETURN v_emp;
    END get_emp;
BEGIN
  emp_rec := get_emp('john.doe@example.com');  -- Using Email
  emp_rec := get_emp('John', 'Doe');  -- Using First and Last Name
END;

Handling Exceptions in Functions and Procedures

• Exception handling in functions and procedures follows the same approach as in anonymous blocks.

• The EXCEPTION section must be placed before the END keyword.

create or replace function get_emp(emp_num employees.employee_id%type) return employees%rowtype is
  emp employees%rowtype;
 begin
   select * into emp from employees where employee_id = emp_num;
   return emp;
 exception
  when no_data_found then
    dbms_output.put_line('There is no employee with the id '|| emp_num);
    raise no_data_found;
  when others then
    dbms_output.put_line('Something unexpected happened!.');
 return null;
 end;

Introduction to Table Functions

Table functions are functions that return collections of rows, which can be queried like a regular database table. These functions return either a nested table or a varray, allowing SQL queries to treat the output as a standard table.

There are two types of table functions:

1. Regular Table Functions

2. Pipelined Table Functions

Regular Table Functions

• A regular table function creates the entire collection in memory and returns it only after the full collection is created.

• This approach can be problematic for large datasets as it can consume excessive Program Global Area (PGA) memory.

• Even if only a few rows are required, the function must complete processing the entire collection before returning results.

• Recommended when dealing with small datasets.

Example Implementation

1. Define an object type.

CREATE TYPE t_days AS OBJECT (
    v_date DATE,
    v_day_number INT
);

2. Define a nested table type.

CREATE TYPE t_days_tab AS TABLE OF t_days;

3. Create the function.

CREATE OR REPLACE FUNCTION f_get_days(
    p_start_date DATE,
    p_day_number INT
) RETURN t_days_tab IS
v_days t_days_tab := t_days_tab();
BEGIN
    FOR i IN 1..p_day_number LOOP
        v_days.EXTEND;
        v_days(i) := t_days(
            p_start_date + i,
            TO_NUMBER(TO_CHAR(p_start_date + i, 'DDD'))
        );
    END LOOP;
    RETURN v_days;
END;

4. Querying the function

SELECT * FROM TABLE(f_get_days(SYSDATE, 50));
-- Or
SELECT * FROM (SYSDATE, 50);

• From Oracle 12.2 onwards, the TABLE keyword is optional.

Pipelined Table Functions

• Pipelined functions return rows as soon as they are created rather than waiting for the entire collection to be built.

• This method avoids excessive memory usage and speeds up retrieval for large datasets.

• Recommended for large datasets and ETL operations in data warehousing.

Key Differences from Regular Table Functions:

• Uses PIPELINED keyword in function declaration.

• Does not return a nested table; instead, each row is output immediately using PIPE ROW.

• A RETURN; statement is still required but does not return any collection.

Example Implementation

1. Define an object type.

2. Define a nested table type.

3. Create the function.

CREATE OR REPLACE FUNCTION f_get_days_piped(
    p_start_date DATE,
    p_day_number INT
) RETURN t_days_tab PIPELINED IS
BEGIN
    FOR i IN 1..p_day_number LOOP
        PIPE ROW(t_days(
            p_start_date + i,
            TO_NUMBER(TO_CHAR(p_start_date + i, 'DDD'))
        ));
    END LOOP;
    RETURN;
END;

4. Querying the function:

SELECT * FROM TABLE(f_get_days_piped(SYSDATE, 50));
-- Or
SELECT * FROM f_get_days_piped(SYSDATE, 50);

Comparison