How to use PL/SQL Procedures In SQL
Using the PL/SQL procedure is the structured way to the encapsulate the series of SQL and PL/SQL statements into reusable units of the code. Procedures can be accept the input parameters, perform the operations and it returns output values. These are mainly useful for the modularizing the code, improving the maintainability and promoting the code reuse.
Let us take same employee table to retrieve the average salary for the specific department.
Step 1: Create or Replace the procedure
CREATE OR REPLACE PROCEDURE CalculateAvgSalary(
department_id IN NUMBER,
avg_salary OUT NUMBER
)
AS
BEGIN
SELECT AVG(salary)
INTO avg_salary
FROM employee
WHERE DepartmentID = department_id;
END;
/
Explanation:
- We can create the PL/SQL procedure and named as CalculateAvgSalary with the two parameters such as department_id and avg_salary.
- In the Procedure, we use the SQL query to calculate the average salary of the employee which is related to the specific department.
- The query result is stores in the avg_salary output parameter with the help of INTO clause.
Step 2: Calling the Procedure
DECLARE
v_avg_salary NUMBER;
BEGIN
CalculateAvgSalary(1, v_avg_salary);
DBMS_OUTPUT.PUT_LINE('Average Salary for Department 1: ' || v_avg_salary);
END;
/
For call the procedure and retrieve the average salary for the specific department, you can execute the above PL/SQL block.
Output:
Output
In the above output, average salary for Department 1 is 5000.
How to Get Counts of all Tables in a Schema in PL/SQL?
In Database Management System, it is essential to retrieve the statistical information about tables with the schema. Whether it is for monitoring the database health, optimizing the performance, or simply understanding the data structures having access to row counts of the tables can be more valuable.