Question

Explain function in PL/SQL with example.

Answer 1

Of course. Here is a detailed explanation of functions in PL/SQL, including their purpose, syntax, a complete example, and a comparison with procedures.

---

What is a PL/SQL Function?

A PL/SQL Function is a named, reusable block of code (a subprogram) that performs a specific task and must return a single value.

Think of it as a small, specialized calculator. You give it some inputs (called parameters), it performs a calculation or some logic, and it gives you back one specific answer.

The most important characteristic of a function is that it returns a value. This allows it to be used directly within SQL statements (like SELECT or WHERE), which is something a procedure cannot do.

Key Characteristics of a Function

• Returns a Value: It must have a RETURN clause in its definition and must execute a RETURN statement to pass a value back to the caller.
• Can be Called from SQL: Because it returns a value, a function can be used in SELECT, WHERE, and HAVING clauses, just like built-in SQL functions (e.g., UPPER, ROUND).
• Has Parameters: It can accept zero or more input parameters (IN mode is the default and most common).
• Reusable: It is stored in the database and can be called by any user with the necessary permissions, promoting code reuse and modularity.

---

Syntax of a PL/SQL Function

Here is the basic structure for creating a function:

`sql
CREATE [OR REPLACE] FUNCTION function_name (

parameter1_name [IN] datatype,
parameter2_name [IN] datatype,
...

)
RETURN return_datatype -- Specifies the data type of the value the function will return
IS

-- (Optional) Declaration of local variables, cursors, etc.
local_variable_name datatype;

BEGIN

-- Executable statements (the logic of the function)
-- ...

-- The function MUST have at least one RETURN statement
RETURN value_to_be_returned; 

[EXCEPTION]

-- (Optional) Exception handling block
WHEN OTHERS THEN
    -- Handle errors
    RETURN some_error_value; -- Or re-raise the exception

END function_name;
/
`
CREATE OR REPLACE: A common practice. It creates the function if it doesn't exist, or overwrites it if it already exists.
RETURN return_datatype: This is mandatory and defines the data type of the single value the function will send back.
* RETURN value_to_be_returned: This statement stops the function's execution and passes the specified value back to the calling environment.

---

Complete Example: Calculating a Final Price

Let's create a function that takes a price and a tax rate as input and returns the final price including tax.

Step 1: Create the Function

This function, named calculate_final_price, will accept two numbers (p_price and p_tax_rate) and return a number.

`sql
CREATE OR REPLACE FUNCTION calculate_final_price (

p_price    IN NUMBER,
p_tax_rate IN NUMBER

)
RETURN NUMBER
IS

-- No local variables are needed for this simple calculation

BEGIN

-- Check for invalid inputs to make the function more robust
IF p_price IS NULL OR p_tax_rate IS NULL OR p_price < 0 OR p_tax_rate < 0 THEN
    RETURN NULL; -- Return NULL if inputs are invalid
END IF;

-- Calculate the final price and return it, rounded to 2 decimal places
RETURN ROUND(p_price * (1 + p_tax_rate), 2);

END calculate_final_price;
/
`
After running this code, the function is compiled and stored in the database, ready to be used.

Step 2: Use the Function

Now we can call our function in different ways.

A. Calling the Function in a SELECT Statement

This is the most powerful way to use a function. Let's imagine we have a Products table.

`sql
-- Create a sample table
CREATE TABLE Products (

ProductID   NUMBER PRIMARY KEY,
ProductName VARCHAR2(100),
ListPrice   NUMBER(10, 2)

);

-- Insert some data
INSERT INTO Products VALUES (101, 'Laptop', 1200);
INSERT INTO Products VALUES (102, 'Keyboard', 75);
INSERT INTO Products VALUES (103, 'Mouse', 25);
COMMIT;
`

Now, let's use our function to get the final price for each product with a tax rate of 8% (0.08).

`sql
SELECT

ProductName,
ListPrice,
calculate_final_price(ListPrice, 0.08) AS FinalPrice -- Calling our function!

FROM

Products;

`

Result:
| PRODUCTNAME | LISTPRICE | FINALPRICE |
| :--- | :--- | :--- |
| Laptop | 1200 | 1296 |
| Keyboard | 75 | 81 |
| Mouse | 25 | 27 |

B. Calling the Function in a PL/SQL Block

You can also use the function within another PL/SQL program to assign its result to a variable.

`sql
SET SERVEROUTPUT ON;

DECLARE

v_item_price      NUMBER := 500;
v_sales_tax       NUMBER := 0.05;
v_total_cost      NUMBER;

BEGIN

-- Assign the function's return value to our local variable
v_total_cost := calculate_final_price(v_item_price, v_sales_tax);

DBMS_OUTPUT.PUT_LINE('The total cost for the item is: ' || v_total_cost);

END;
/
**Result:**
The total cost for the item is: 525
`

---

Function vs. Procedure: The Key Difference

| Feature | Function | Procedure |
| :--- | :--- | :--- |
| Primary Purpose | To perform a calculation and return a single value. | To perform an action or a set of actions. |
| Return Value | Must return exactly one value. | Cannot return a value directly (uses OUT parameters instead). |
| RETURN Keyword | Mandatory in the header (RETURN datatype). | Not allowed in the header. |
| How it's Called | Can be part of an expression or a SQL statement. | Must be called as a standalone statement (EXEC procedure_name();). |
| Usage in SQL | Can be used in SELECT, WHERE, HAVING clauses. | Cannot be used directly in SQL statements. |