PL/SQL – Part 5

FUNCTIONS

• Function is a named block of code that performs a specific task and returns a value.
• It can take zero or more input parameters and must return a single value.
• Functions are similar to procedures, but they must return a value to the caller.

Structure for creating function:

CREATE [OR REPLACE] FUNCTION function_name
    (parameter1 datatype1, parameter2 datatype2, ..., parameterN datatypeN)
RETURN return_datatype
IS
    -- Declaration section
    variable_declarations;

BEGIN
    -- Execution section
    -- Perform operations using parameters and variables
    -- Return a value using the RETURN statement
    RETURN value_to_return;
END function_name;
/

• RETURN return_datatype: This specifies the data type of the value that the function will return.
• RETURN value_to_return;: This statement is used to return a value from the function. The value must match the data type specified in the RETURN clause.

Ex:

Create Function:

create function find_salary(p_emp_id number)
return number
is
l_salary number;
begin
select salary into l_salary from xx1195_emp where emp_id = p_emp_id;
return l_salary;
end;

Execute Function: Wrong Method

begin
find_salary(102);
end;
/

• If we call a function but don’t use its result, we get this error.
• We are calling the find_salary function within a PL/SQL block, but we’re not doing anything with the return value. We’re just invoking the function and not capturing or using its return value anywhere in our code. When we execute this block, Oracle PL/SQL treats it similar to a procedure call.

Execute Function: Method 1

BEGIN
    DBMS_OUTPUT.PUT_LINE('Salary for employee 102: ' || find_salary(102));
END;
/

• Here We’re using the return value of the find_salary function within a PL/SQL expression.

Execute Function: Method 2

declare
l_salary number;
begin
l_salary := find_salary(101);
end;
/

Execute Function: Method 3

select find_salary(101) from dual;
/

---

Ex:1

CREATE OR REPLACE FUNCTION calculate_discount(
    p_price IN NUMBER,
    p_discount_rate IN NUMBER
) RETURN NUMBER
IS
    l_discounted_price NUMBER;
BEGIN
    l_discounted_price := p_price - (p_price * p_discount_rate / 100);
    
    RETURN l_discounted_price;
END calculate_discount;
/

DECLARE
    l_price NUMBER := 100;
    l_discount_rate NUMBER := 10;
    l_discounted_price NUMBER;
BEGIN
    -- Call the function and assign the return value to a variable
    l_discounted_price := calculate_discount(l_price, l_discount_rate);
    
    DBMS_OUTPUT.PUT_LINE('Discounted Price: ' || l_discounted_price);
END;
/

---

Ex:2

A function returns more than a single value, we can use an approach such as returning a record (also known as a row) or a collection. (collection will see later)

CREATE OR REPLACE FUNCTION get_employee_details(
    p_employee_id IN NUMBER
) RETURN employees%ROWTYPE
IS
    l_employee employees%ROWTYPE;
BEGIN
    -- Retrieve employee details based on the employee ID
    SELECT * INTO l_employee
    FROM employees
    WHERE employee_id = p_employee_id;
    
    -- Return the retrieved employee details
    RETURN l_employee;
END get_employee_details;
/

DECLARE
    l_employee_record employees%ROWTYPE;
BEGIN
    -- Call the function and capture the returned record
    l_employee_record := get_employee_details(100); 
    
    -- Display the details of the employee
    DBMS_OUTPUT.PUT_LINE('Employee ID: ' || l_employee_record.employee_id);
    DBMS_OUTPUT.PUT_LINE('First Name: ' || l_employee_record.first_name);
    DBMS_OUTPUT.PUT_LINE('Last Name: ' || l_employee_record.last_name);
    DBMS_OUTPUT.PUT_LINE('Salary: ' || l_employee_record.salary);
END;
/

---

Ex:3

We can use cursors within functions. Cursors are often used in functions to retrieve and process multiple rows of data returned by a query. Here’s an example of how we can use a cursor within a function:

CREATE OR REPLACE FUNCTION get_employee_names 
RETURN SYS_REFCURSOR
IS
    l_cursor SYS_REFCURSOR;
BEGIN
    OPEN l_cursor FOR
        SELECT first_name || ' ' || last_name AS full_name
        FROM employees;
    
    RETURN l_cursor;
END get_employee_names;
/

DECLARE
    l_emp_cursor SYS_REFCURSOR;
    l_full_name VARCHAR2(100);
BEGIN
    l_emp_cursor := get_employee_names;
    
    LOOP
        FETCH l_emp_cursor INTO l_full_name;
        EXIT WHEN l_emp_cursor%NOTFOUND;
        
        DBMS_OUTPUT.PUT_LINE('Employee Name: ' || l_full_name);
    END LOOP;
END;
/

• Cursor: (https://rajiilearning.wordpress.com/2024/03/08/pl-sql-part-3/)
  • l_emp_cursor is a cursor variable that holds the result set returned by the function get_employee_names. This result set contains rows of data, with each row representing an employee’s full name.
• FETCH Statement:
  • FETCH l_emp_cursor INTO l_full_name;
  • The FETCH statement tells Oracle to take the next row from the cursor (l_emp_cursor) and retrieve its data.
  • The INTO l_full_name part says to put the value from the retrieved row into the variable l_full_name.
  • So, each time this statement is executed, it moves to the next row in the cursor’s result set and stores the value of the full_name column (or whatever column is being returned) into the variable l_full_name.
• Loop:
  • This FETCH statement is used within a loop, so that it can be executed repeatedly until all rows in the result set have been processed.
  • In each iteration of the loop, the FETCH statement retrieves the next row of data from the cursor and stores it in the variable l_full_name.
  • The loop continues until there are no more rows to fetch from the cursor.
• So, the FETCH statement is a way to access the rows of data returned by a cursor, one row at a time, and process them as needed.

---

Delete Function:

DROP FUNCTION function_name;

• After executing this statement, that function will be permanently deleted from the database, and it cannot be recovered

---

Procedure Vs Function

Feature	Procedure	Function
Purpose	Used for performing actions	Used for computing and returning values
Return Value	Does not explicitly return a value	Must explicitly return a single value
Usage	Performs tasks like inserting data, updating records, etc.	Computes (calculates) values based on input parameters. Insert, Update, Delete, Merge cannot do
Return Type	Does not specify a return type	Must specify a return type
Call Syntax	Called directly within PL/SQL code	Called as part of an expression or assignment statement
Parameters	Can have IN, OUT, or IN OUT parameters.	Can have IN parameters, but not OUT or IN OUT parameters.

Comparison of Procedures and Functions in Oracle PL/SQL

---