ORACLE PL/SQL packages
ORACLE
An Oracle PL/SQL package is a schema object that logically combines related PL/SQL types, variables, constants, cursors, procedures and functions for use as a single unit.
Oracle PL/SQL Packages
➔ It is used to create a modular environment in Oracle database.
➔ Because all related parts are combined as a unit, database objects are easy to maintain.
➔ This is a basic building block for creating modular applications on an Oracle database.
➔ PL/SQL can hide implementation details by separating the package specification from the body. That is, the package body can be changed without recompiling the calling programs.
➔ A package has two parts, a specification and a body. The package specification is mandatory, the package body can be required or optional depending on the package specification.
➔ A package consists of two parts: a specification and a body
Syntax (package specification)
CREATE OR REPLACE PACKAGE my_package AS
PROCEDURE my_procedure(x NUMBER);
FUNCTION my_function(x NUMBER, y NUMBER) RETURN NUMBER;
/* Other declarations */
END my_package;
Syntax (package body)
CREATE OR REPLACE PACKAGE BODY my_package AS
PROCEDURE my_procedure(x NUMBER) IS
BEGIN
/* The procedure Implementation code here */
END my_procedure;
FUNCTION my_function(x NUMBER, y NUMBER) RETURN NUMBER IS
BEGIN
/* The function functional code goes here */
END my_function;
/* Other code goes here if needed */
END my_package;
Using package in PL/SQL code
DECLARE
result NUMBER;
BEGIN
/* Call a procedure from the package */
my_package.my_procedure(10);
/* Call a function from the package */
result := my_package.my_function(20, 30);
END;
Oracle PL/SQL package Example
/* Enable display output */
SET SERVEROUTPUT ON;
/* PL/SQL package specification */
CREATE OR REPLACE PACKAGE my_package AS
*/ Procedure */
PROCEDURE add_num(x NUMBER, y NUMBER, z OUT NUMBER);
/* Function */
FUNCTION multiply_num(x NUMBER, y NUMBER) RETURN NUMBER;
END my_package;
/
/* PL/SQL package body */
CREATE OR REPLACE PACKAGE BODY my_package AS
PROCEDURE add_num(a NUMBER, b NUMBER, c OUT NUMBER) IS
BEGIN
c := a + b;
END add_num;
FUNCTION multiply_num(pa NUMBER, pb NUMBER) RETURN NUMBER IS
BEGIN
RETURN pa * pb;
END multiply_num;
END my_package;
/
/* Using PL/SQL package */
DECLARE
/* Declare variables */
sum_result NUMBER;
multipy_result NUMBER;
BEGIN
my_package.add_num(10, 20, sum_result);
DBMS_OUTPUT.PUT_LINE('Summation is: ' || sum_result);
multipy_result := my_package.multiply_num(10, 20);
DBMS_OUTPUT.PUT_LINE('Multiplication is: ' || multipy_result);
END;
/
Copy the code and try it out practically in your learning environment.
Output
Summation is: 30
Multiplication is: 200