Experiment No 25: Create functions for given database

 Experiment No 25: Create functions for given database

Result:
The PL/SQL functions were successfully created and executed, returning the expected values. The experiment demonstrated correct use of function structure, local variables, and modular coding, achieving the objective of implementing functions on the database.

---

Practical Questions and Answers

1. What is the difference between Function and Procedure?

Feature	Function	Procedure
Purpose	Performs a task and returns a single value	Performs a task but may not return a value
Return Type	Must have a RETURN type	No RETURN type
Usage in SQL	Can be used in SQL statements (e.g., SELECT, WHERE)	Cannot be directly used in SQL statements
Call Method	Called in expressions or PL/SQL blocks	Called as a standalone statement
Example	Calculating salary, tax, discount	Updating records, inserting data

---

2. Write syntax for creating and replacing function in Oracle

-- Creating or Replacing a Function
CREATE OR REPLACE FUNCTION function_name
    (parameter1 [IN | OUT | IN OUT] datatype, parameter2 datatype, ...)
RETURN return_datatype
IS
    -- Optional declaration section
    variable1 datatype;
    variable2 datatype;
BEGIN
    -- Function logic
    -- SQL/PLSQL statements
    RETURN return_value;
EXCEPTION
    -- Optional exception handling
    WHEN exception_name THEN
        -- Handle exception
END function_name;

Example: Function to calculate bonus for an employee based on salary

CREATE OR REPLACE FUNCTION calc_bonus (p_salary NUMBER)
RETURN NUMBER
IS
    v_bonus NUMBER;
BEGIN
    v_bonus := p_salary * 0.10;  -- 10% bonus
    RETURN v_bonus;
END calc_bonus;

---

Exercise

1: PL/SQL function to compute and return the maximum of two values

-- Function to find maximum of two numbers
CREATE OR REPLACE FUNCTION max_of_two (
    p_num1 NUMBER,
    p_num2 NUMBER
) RETURN NUMBER
IS
    v_max NUMBER;
BEGIN
    IF p_num1 > p_num2 THEN
        v_max := p_num1;
    ELSE
        v_max := p_num2;
    END IF;
    RETURN v_max;
END max_of_two;
/

Example Execution:

DECLARE
    v_result NUMBER;
BEGIN
    v_result := max_of_two(45, 78); -- Compare 45 and 78
    DBMS_OUTPUT.PUT_LINE('Maximum value is: ' || v_result);
END;
/

Expected Output:

Maximum value is: 78

---

2: PL/SQL function to calculate factorial of a given number

-- Function to calculate factorial
CREATE OR REPLACE FUNCTION factorial (
    p_num NUMBER
) RETURN NUMBER
IS
    v_fact NUMBER := 1;
BEGIN
    IF p_num = 0 OR p_num = 1 THEN
        RETURN 1;
    ELSE
        FOR i IN 2..p_num LOOP
            v_fact := v_fact * i;
        END LOOP;
    END IF;
    RETURN v_fact;
END factorial;
/

Example Execution:

DECLARE
    v_result NUMBER;
BEGIN
    v_result := factorial(5); -- Calculate 5!
    DBMS_OUTPUT.PUT_LINE('Factorial of 5 is: ' || v_result);
END;
/

Expected Output:

Factorial of 5 is: 120

---