Write a PL/SQL program to demonstrate Functions in advanced database
Sure! Here's an example of a PL/SQL program that demonstrates the usage of functions in an advanced database scenario:
```sql
-- Create a table to store employee data
CREATE TABLE employees (
id NUMBER,
name VARCHAR2(100),
salary NUMBER
);
-- Insert some sample employee data
INSERT INTO employees VALUES (1, 'John Doe', 5000);
INSERT INTO employees VALUES (2, 'Jane Smith', 6000);
INSERT INTO employees VALUES (3, 'Mike Johnson', 7000);
-- Create a function to calculate the average salary of all employees
CREATE OR REPLACE FUNCTION calculate_avg_salary RETURN NUMBER IS
total_salary NUMBER := 0;
avg_salary NUMBER;
BEGIN
-- Calculate the total salary of all employees
FOR emp IN (SELECT salary FROM employees) LOOP
total_salary := total_salary + emp.salary;
END LOOP;
-- Calculate the average salary
avg_salary := total_salary / (SELECT COUNT(*) FROM employees);
-- Return the average salary
RETURN avg_salary;
END;
-- Call the function and display the average salary
DECLARE
average_salary NUMBER;
BEGIN
average_salary := calculate_avg_salary();
DBMS_OUTPUT.PUT_LINE('Average Salary: ' || average_salary);
END;
/
```
In this program, we first create a table called "employees" to store employee data. Then, we insert some sample employee records into the table.
Next, we create a PL/SQL function called "calculate_avg_salary" to calculate the average salary of all employees. The function iterates through all the employee salaries and calculates the total salary. Then, it divides the total salary by the number of employees to get the average salary. Finally, the function returns the average salary.
In the main block of the program, we call the "calculate_avg_salary" function and store the result in a variable called "average_salary". We then use the DBMS_OUTPUT.PUT_LINE procedure to display the average salary on the console.
When you run this program, it will output the average salary of all employees, which in this case would be 6000.