Packages and Their Needs

Packages and Their Needs

PL/SQL packages are a way to organize and encapsulate related procedures, functions, variables, triggers, and other PL/SQL items into a single item. Packages provide a modular approach to write and maintain the code. It makes it easy to manage large codes....

PL/SQL Package

A PL/SQL package consists of two parts:...

Example 1

-- Enable the display of server output SET SERVEROUTPUT ON; -- Create a PL/SQL package specification CREATE OR REPLACE PACKAGE math_operations AS -- Procedure to add two numbers with an output parameter PROCEDURE add_numbers(x NUMBER, y NUMBER, result OUT NUMBER); -- Function to multiply two numbers FUNCTION multiply_numbers(x NUMBER, y NUMBER) RETURN NUMBER; END math_operations; / -- Create the body of the math_operations package CREATE OR REPLACE PACKAGE BODY math_operations AS -- Implementation of the add_numbers procedure PROCEDURE add_numbers(x NUMBER, y NUMBER, result OUT NUMBER) IS BEGIN result := x + y; END add_numbers; -- Implementation of the multiply_numbers function FUNCTION multiply_numbers(x NUMBER, y NUMBER) RETURN NUMBER IS BEGIN RETURN x * y; END multiply_numbers; END math_operations; / -- PL/SQL block to test the math_operations package DECLARE -- Declare variables to store results sum_result NUMBER; product_result NUMBER; BEGIN -- Call the procedure and pass output parameter math_operations.add_numbers(5, 7, sum_result); -- Display the result of the add_numbers procedure DBMS_OUTPUT.PUT_LINE('Sum Result: ' || sum_result); -- Call the function and retrieve the result product_result := math_operations.multiply_numbers(3, 4); -- Display the result of the multiply_numbers function DBMS_OUTPUT.PUT_LINE('Product Result: ' || product_result); END; /...

Example 2

-- Enable the display of server output SET SERVEROUTPUT ON; -- Create a PL/SQL package specification for employee_operations CREATE OR REPLACE PACKAGE employee_operations AS -- Procedure to calculate annual salary PROCEDURE calculate_annual_salary(monthly_salary NUMBER, annual_salary OUT NUMBER); -- Function to get the full name of an employee FUNCTION get_full_name(first_name VARCHAR2, last_name VARCHAR2) RETURN VARCHAR2; END employee_operations; / -- Create the body of the employee_operations package CREATE OR REPLACE PACKAGE BODY employee_operations AS -- Implementation of the calculate_annual_salary procedure PROCEDURE calculate_annual_salary(monthly_salary NUMBER, annual_salary OUT NUMBER) IS BEGIN -- Assuming 12 months in a year for simplicity annual_salary := monthly_salary * 12; END calculate_annual_salary; -- Implementation of the get_full_name function FUNCTION get_full_name(first_name VARCHAR2, last_name VARCHAR2) RETURN VARCHAR2 IS BEGIN -- Concatenate the first and last names RETURN first_name || ' ' || last_name; END get_full_name; END employee_operations; / -- PL/SQL block to test the employee_operations package DECLARE -- Declare variables annual_salary_result NUMBER; full_name_result VARCHAR2(100); BEGIN -- Call the procedure to calculate annual salary employee_operations.calculate_annual_salary(5000, annual_salary_result); -- Display the result of the calculate_annual_salary procedure DBMS_OUTPUT.PUT_LINE('Annual Salary: ' || annual_salary_result); -- Call the function to get the full name full_name_result := employee_operations.get_full_name('Chetan', 'Singh'); -- Display the result of the get_full_name function DBMS_OUTPUT.PUT_LINE('Full Name: ' || full_name_result); END; /...

Conclusion

In this article, we see how PL/SQL package is useful in maintaining the modularity in the codebases. PL/SQL packages help in grouping up the related objects such as variables, constants, cursor, exceptions, functions etc. Packages improve the modularity, security and reusability of the code and it improves the overall functionality of the database application....